自己写的一个Orm组件，开源一下
刚刚接触.ＮＥＴ ２.０后就听说有ＯＲＭ这个组件，可以把数据库对象映射成对象实体，觉得这个非常有用，所以一直在寻找好的ＯＲＭ组件，Hibernate.NET但一配置麻烦，在asp.net下使用不方便，后来找到听堂开发的ＳＰＬ，但时不时都会有些小问题，后来又找到Ｃastle.net，这个算是比较满意的，但效率真是个大问题，我读入一个有７条记录的表用了６秒钟.最后自己一边找资料，一边学着写，我的想法是开发个一功能简单，速度快的组件，一切以够用为主.这个版本中速度和扩展性还有很大的提升空间,下一个版本我打算加进Trascation功能.
下面我来介绍一个自己开发的一个组件，类不多，只有那么几个.
TableControl，是用来记录表的名称结构和对象映射数据.
MyColumn，是字段的映射.
CacheTableMapping，用于缓存各个表映射的数据.
ObjectEntity,是实体和对象类映射
DataProvider ,为数据库和实体的转换提供方法
Condition,一个简单的自动生成sql语句和参数的类
TableMapping,表映射
ColumnMapping,字段映射
Conn,数据处理类
下载网址:https://files.cnblogs.com/lymph/Common.rar
使用方法:
在你用代码生成器,根据数据库生成对象(或者手工编写),在类和类的相关字段上加上属性,比喻:
[TableMapping("Order")]
class Order
{
int orderid;
string item;
[ColumnMapping("OrderId",true)]//设为标识列
public int OrderId{
get{return orderid;}
set{orderid=value;}
}
[ColumnMapping("Item")]
public string OrderItem{
get{return item;}
set{item=value;}
}
}
Order order=DataProvider<Order>.FindByKey(1);
order.OrderItem="test";
order.UpdateByKey();//更新
order.Create();//新建一条记录
下面是源代码:
TableControl.cs
using System;
using System.Collections.Generic;
using System.Text;
namespace LymSite.Common
{
//字段
public struct MyColumn
{
public MyColumn(string name, string mapname, bool isKey)
{
Name = name;
MapName = mapname;
IsKey = isKey;
}
public string Name;
public bool IsKey;
public string MapName;
}
public class TableControl
{
public string TalbeName = string.Empty;
public string ColumnList = string.Empty;
public MyColumn Key=new MyColumn();
public SortedList<string, MyColumn> Column = new SortedList<string, MyColumn>();
//构造
public TableControl()
{
}
/// <summary>
///
/// </summary>
/// <param name="column">字段名</param>
/// <param name="mapname">映射名</param>
public void AddColumn(string column, string mapname)
{
AddColumn(column, mapname, false);
}
/// <summary>
/// 添加字段
/// </summarybj
/// <param name="column">字段名</param>
/// <param name="mapname">映射名</param>
/// <param name="isKey">是否主标识</param>
public void AddColumn(string column, string mapname, bool isKey)
{
MyColumn col = new MyColumn(column, mapname, isKey);
Column.Add(column, col);
if (isKey)
{
if (string.IsNullOrEmpty(r5.Name))
Key = col;
else
throw new Exception("一个对象不能同时有多个主标识列!");
}
if (!isColInit) AddInList(column);
}
void AddInList(string name)
{
if (string.IsNullOrEmpty(ColumnList))
ColumnList += name;
else
ColumnList += "," + name;
}
bool isColInit = false;
public TableControl(string t, string c)
{
TalbeName = t;
ColumnList = c;
if (c != "")
isColInit = true;
}
}
[System.AttributeUsage(System.AttributeTargets.Class |
System.AttributeTargets.Struct,
AllowMultiple = false) // multiuse attribute
]
public class TableMapping : System.Attribute
{
string name;
public TableMapping(string name)
{
this.name = name;
}
public string GetName()
{
return name;
}
}
[System.AttributeUsage(System.AttributeTargets.Property | System.AttributeTargets.Field,
AllowMultiple = false) // multiuse attribute
]
public class ColumnMapping : System.Attribute
{
string name;
bool isKey = false;
public ColumnMapping(string columnName)
{
this.name = columnName;
}
/// <summary>
/// 数据字段影射
/// </summary>
/// <param name="columnName">字段名</param>
/// <param name="iskey">是否标识列</param>
public ColumnMapping(string columnName, bool iskey)
{
this.name = columnName;
isKey = iskey;
}
public bool IsKey
{
get
{
return isKey;
}
set
{
isKey = value;
}
}
public string ColumnName
{
get
{
return name;
}
set
{
name = value;
}
}
public string GetName()
{
return name;
}
}
}
ObjectMapping.cs
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Collections;
using System.Reflection;
using System.Xml;
using System.Collections.Generic;
namespace LymSite.Common
{
/// <summary>
/// 缓存映射数据
/// </summary>
sealed public class CacheTableMapping
{
static private Hashtable hashTable = new Hashtable();
public static Hashtable CacheTable
{
get { return CacheTableMapping.hashTable; }
set { CacheTableMapping.hashTable = value; }
}
}
/// <summary>
/// 对象与数据库表的映射
/// </summary>
///
public abstract class ObjectEntity<T> where T : new()
{
bool isPersistent=false;
public bool IsPersistent
{
get { return isPersistent; }
set { isPersistent = value; }
}
public ObjectEntity()
{
DataProvider<T>.InitEntity();
}
static ObjectEntity()
{
DataProvider<T>.InitEntity();
}
#region 静态函数
public static int CountByProperty(string name, object value)
{
return DataProvider<T>.CountByProperty(name, value);
}
public static int CountByProperty(Condition list)
{
return DataProvider<T>.CountByProperty(list);
}
/// <summary>
/// 查找对象
/// </summary>
/// <param name="list">Condition</param>
/// <returns>相应的对象</returns>
public static T FindByProperty(Condition list)
{
return DataProvider<T>.FindByProperty(list);
}
public static T FindByKey(object key)
{
return DataProvider<T>.FindByKey(key);
}
public static object FindScalar(string column, Condition list)
{
return DataProvider<T>.FindScalar(column,list);
}
/// <summary>
/// 得到Distinct的字段
/// </summary>
/// <param name="siteid"></param>
/// <returns>数据表</returns>
public static DataTable GetDistinctTable(string column,Condition c)
{
return DataProvider<T>.GetDistinctTable(column,c);
}
public static int DeleteByProperty(string name, object value)
{
return DataProvider<T>.DeleteByProperty(name, value);
}
public static int DeleteByKey(object objVal)
{
return DataProvider<T>.DeleteByProperty(objVal);
}
public static int DeleteByProperty(Condition c)
{
return DataProvider<T>.DeleteByProperty(c);
}
public static DataTable GetTableByProperty(Condition c)
{
return DataProvider<T>.GetTableByProperty(c);
}
public static XmlDocument GetXml(DataTable dt)
{
return DataProvider<T>.GetXml(dt);
}
#endregion
#region 虚拟对外函数
virtual public int DeleteByKey()
{
return DataProvider<T>.DeleteByKey(this);
}
/// <summary>
/// 创建记录
/// </summary>
/// <param name="c">参数</param>
/// <returns>影响行数,0为更新失败</returns>
virtual public int Create()
{
return DataProvider<T>.Create(this);
}
/// <summary>
/// ORM自带更新数据库,自动识别数据字段，但必需有Key字段
/// </summary>
/// <returns>影响行数,0为更新失败</returns>
virtual public int UpdateByKey()
{
return DataProvider<T>.UpdateByKey(this);
}
#endregion
#region 虚拟内部函数
/// <summary>
/// 更新数据库
/// </summary>
/// <param name="upC">更新项</param>
/// <param name="selC">条件项</param>
/// <returns></returns>
virtual protected int Update(Condition upC)
{
return DataProvider<T>.Update(upC);
}
/// <summary>
/// 创建记录
/// </summary>
/// <param name="c">参数</param>
/// <returns>影响行数,0为更新失败</returns>
virtual protected int Create(Condition c)
{
return DataProvider<T>.Create(c,this);
}
//加入对象映射
protected static void AddTable(string classname, string talbe, string column)
{
TableControl t = new TableControl(talbe, column);
MappingInit(classname, talbe, t);
}
#endregion
}
}
DataProvider.cs
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data.Common;
using LymSite.Common;
using System.Xml;
namespace LymSite.Common
{
//提供数据库objectmapping ，解决事务处理问题
class DataProvider<T> where T : new()
{
public DataProvider()
{
}
#region 静态方法
//初始化实体
public static void InitEntity()
{
if (!CacheTableMapping.CacheTable.Contains(typeof(T).Name))
{
Type t = typeof(T);
object[] atts = t.GetCustomAttributes(false);
if (atts.Length > 0)
{
string tablename = "";
if (atts[0] is TableMapping)
tablename = ((TableMapping)atts[0]).GetName();
MappingInit(t.Name, tablename, new TableControl(tablename, ""));
}
}
}
public static int CountByProperty(string name, object value)
{
Condition c = new Condition(name, value);
return CountByProperty(c);
}
public static int CountByProperty(Condition list)
{
string tablename = ((TableControl)CacheTableMapping.CacheTable[typeof(T).Name]).TalbeName;
string queryString = list.CreateSelectSql(tablename, " count(0) ");
return int.Parse(Conn.ExecuteScalar(queryString, list.ToParamsArray).ToString());
}
/// <summary>
/// 查找对象
/// </summary>
/// <param name="list">Condition</param>
/// <returns>相应的对象</returns>
public static T FindByProperty(Condition list)
{
T entity = new T();
Type o = typeof(T);
TableControl table = (TableControl)CacheTableMapping.CacheTable[typeof(T).Name];
string column = "";
column = table.ColumnList;
string queryString = list.CreateSelectSql(table.TalbeName, column);
IDataReader dr = Conn.ExecuteReader(queryString, list.ToParamsArray);
string colname = "";
string typename = "";
object tmpObject;
if (dr.Read())
{
for (int i = 0; i < dr.FieldCount; i++)
{
colname = dr.GetName(i);
if (table.Column.ContainsKey(colname))
{
typename = o.GetProperty(table.Column[colname].MapName).PropertyType.Name;
tmpObject = dr[colname];
if (tmpObject == null || tmpObject.ToString() == "")
{
if (typename == typeof(DateTime).Name)
o.GetProperty(table.Column[colname].MapName).SetValue(entity, default(DateTime), null);
else if (typename == typeof(int).Name)
o.GetProperty(table.Column[colname].MapName).SetValue(entity, default(int), null);
else
o.GetProperty(table.Column[colname].MapName).SetValue(entity, string.Empty, null);
}
else
o.GetProperty(table.Column[colname].MapName).SetValue(entity, tmpObject, null);
}
}
}
dr.Close();
return entity;
}
public static T FindByKey(object key)
{
MyColumn mycol = GetKeyColumn();
Condition c = new Condition();
c.AddWhereOrInsert(mycol.Name, key);
return FindByProperty(c);
}
public static object FindScalar(string column, Condition list)
{
TableControl table = (TableControl)CacheTableMapping.CacheTable[typeof(T).Name];
string sql = list.CreateSelectSql(table.TalbeName, column);
return Conn.ExecuteScalar(sql, list.ToParamsArray);
}
/// <summary>
/// 得到Distinct的字段
/// </summary>
/// <param name="siteid"></param>
/// <returns>数据表</returns>
public static DataTable GetDistinctTable(string column, Condition c)
{
TableControl table = (TableControl)CacheTableMapping.CacheTable[typeof(T).Name];
return Conn.ExecuteDateSet(c.CreateSelectSql(table.TalbeName, " distinct " + column + " "), c.ToParamsArray).Tables[0];
//return GetTableByProperty(new Condition("siteid", siteid));
}
public static int DeleteByProperty(string name, object value)
{
Condition tab = new Condition(name, value);
return DeleteByProperty(tab);
}
public static int DeleteByKey(object objVal)
{
MyColumn mycol = GetKeyColumn();
Condition tab = new Condition(mycol.Name, objVal);
return DeleteByProperty(tab);
}
public static int DeleteByProperty(Condition tab)
{
string queryString = tab.CreateDeleteSql(((TableControl)CacheTableMapping.CacheTable[typeof(T).Name]).TalbeName);
return Conn.ExecuteNonQuery(queryString, tab.ToParamsArray);
}
public static DataTable GetTableByProperty(Condition tab)
{
TableControl tc = ((TableControl)CacheTableMapping.CacheTable[typeof(T).Name]);
string sql = tab.CreateSelectSql(tc.TalbeName, tc.ColumnList);
return Conn.ExecuteDateSet(sql, tab.ToParamsArray).Tables[0];
}
public static XmlDocument GetXml(DataTable dt)
{
string tablename = ((TableControl)CacheTableMapping.CacheTable[typeof(T).Name]).TalbeName;
XmlDocument xmlDoc = new XmlDocument();
XmlDeclaration xn = xmlDoc.CreateXmlDeclaration("1.0", "utf-8", null);
xmlDoc.AppendChild(xn);
XmlElement boot = xmlDoc.CreateElement(tablename);
for (int i = 0; i < dt.Rows.Count; i++)
{
XmlElement note = xmlDoc.CreateElement("Record");
for (int j = 0; j < dt.Columns.Count; j++)
note.SetAttribute(dt.Columns[j].ColumnName, dt.Rows[i][j].ToString());
boot.AppendChild(note);
}
xmlDoc.AppendChild(boot);
return xmlDoc;
}
static public int DeleteByKey(T obj)
{
MyColumn mycol = GetKeyColumn();
Type o = typeof(T);
object objVal = o.GetProperty(mycol.MapName).GetValue(obj, null);
Condition tab = new Condition(mycol.Name, objVal);
return DeleteByProperty(tab);
}
/// <summary>
/// 创建记录
/// </summary>
/// <param name="c">参数</param>
/// <returns>影响行数,0为更新失败</returns>
static public int Create(object obj)
{
Condition c = new Condition();
Type o = typeof(T);
TableControl tc = (TableControl)CacheTableMapping.CacheTable[o.Name];
object objVal;
foreach (KeyValuePair<string, MyColumn> p in tc.Column)
{
if (!p.Value.IsKey)
{
objVal = o.GetProperty(p.Value.MapName).GetValue(obj, null);
c.AddWhereOrInsert(p.Value.Name, objVal);
}
}
return Conn.ExecuteNonQuery(c.CreateInsertSql(tc.TalbeName), c.ToParamsArray);
}
/// <summary>
/// ORM自带更新数据库,自动识别数据字段，但必需有Key字段
/// </summary>
/// <returns>影响行数,0为更新失败</returns>
static public int UpdateByKey(object obj)
{
Condition upC = new Condition();
Type o = typeof(T);
TableControl tc = (TableControl)CacheTableMapping.CacheTable[o.Name];
bool hasKey = false;
object objVal;
foreach (KeyValuePair<string, MyColumn> p in tc.Column)
{
objVal = o.GetProperty(p.Value.MapName).GetValue(obj, null);
if (p.Value.IsKey)
{
hasKey = true;
upC.AddWhereOrInsert(p.Value.Name, objVal);
}
else
upC.AddUpdate(p.Value.Name, objVal);
}
if (hasKey)
return Conn.ExecuteNonQuery(upC.CreateUpdateSql(tc.TalbeName), upC.ToParamsArray);
else
return 0;
// WHERE ([RemoteFAQ].[FaqId] = @FaqId)";
}
/// <summary>
/// 更新数据库
/// </summary>
/// <param name="upC">更新项</param>
/// <param name="selC">条件项</param>
/// <returns></returns>
static public int Update(Condition upC)
{
string tablename = ((TableControl)CacheTableMapping.CacheTable[typeof(T).Name]).TalbeName;
return Conn.ExecuteNonQuery(upC.CreateUpdateSql(tablename), upC.ToParamsArray);
// WHERE ([RemoteFAQ].[FaqId] = @FaqId)";
}
/// <summary>
/// 创建记录
/// </summary>
/// <param name="c">参数</param>
/// <returns>影响行数,0为更新失败</returns>
static public int Create(Condition c,object obj)
{
string tablename = ((TableControl)CacheTableMapping.CacheTable[obj.GetType().Name]).TalbeName;
string queryString = c.CreateInsertSql(tablename);
return Conn.ExecuteNonQuery(queryString, c.ToParamsArray);
}
#endregion
private static MyColumn GetKeyColumn()
{
TableControl tc = (TableControl)CacheTableMapping.CacheTable[typeof(T).Name];
return tc.Key;
/*
foreach (KeyValuePair<string, MyColumn> p in tc.Column)
{
if (p.Value.IsKey)
return p.Value;
}
throw new Exception("类中未定义标识字段!");
*/
}
/// <summary>
/// 初始化字段和对象的映射
/// </summary>
/// <param name="classname">对象名</param>
/// <param name="talbe">表名</param>
/// <param name="tc">映射表</param>
private static void MappingInit(string classname, string talbe, TableControl tc)
{
PropertyInfo[] aryp = typeof(T).GetProperties();
foreach (PropertyInfo p in aryp)
{
object[] atts = p.GetCustomAttributes(false);
foreach (object att in atts)
{
if (att is ColumnMapping)
{
ColumnMapping a = (ColumnMapping)att;
string colname = a.GetName();
bool blkey = a.IsKey;
if (string.IsNullOrEmpty(colname))
tc.AddColumn(p.Name, p.Name);
else
tc.AddColumn(colname, p.Name, blkey);
}
}
}
if (!CacheTableMapping.CacheTable.Contains(classname))
CacheTableMapping.CacheTable.Add(classname, tc);
}
}
}
Condition.cs
using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
namespace LymSite.Common
{
/// <summary>
/// Condition 的摘要说明
/// </summary>
public class Condition
{
//ArrayList arylist = new ArrayList();
private TSQLParams aryUpdate = new TSQLParams();
private TSQLParams arySelect = new TSQLParams();
private TSQLParams arylike = new TSQLParams();
System.Collections.Generic.List<DictionaryEntry> aryOrderby = new System.Collections.Generic.List<DictionaryEntry>();
ArrayList aryParams = new ArrayList();
string paramsChar = "@";
string paramLast = "1";
public Condition()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
/// <summary>
/// 添加where 或insert的参数
/// </summary>
/// <param name="key">字段名</param>
/// <param name="value">值</param>
public Condition(string key, object value)
{
AddWhereOrInsert(key, value);
}
/// <summary>
/// 添加where 或insert的参数，程序会自动把字段名转为参数名
/// </summary>
/// <param name="key">字段名</param>
/// <param name="value">值</param>
public void AddWhereOrInsert(string key, object value)
{
AddParams(key, value, ref arySelect);
arySelect.Add(new DictionaryEntry(key, value));
}
/// <summary>
/// 添加Like的参数，程序会自动把字段名转为参数名
/// </summary>
/// <param name="key">字段名</param>
/// <param name="value">值</param>
public void AddLike(string columnName, string value)
{
AddParams(columnName, value,ref arylike);
arylike.Add(new DictionaryEntry(columnName, value));
}
/// <summary>
/// 添加Update的参数，程序会自动把字段名转为参数名
/// </summary>
/// <param name="key">字段名</param>
/// <param name="value">值</param>
public void AddUpdate(string columnName, object value)
{
AddParams(columnName, value,ref aryUpdate);
aryUpdate.Add(new DictionaryEntry(columnName, value));
}
private bool CheckContains(string key)
{
for(int i=0;i<aryParams.Count;i++)
if(((SqlParameter)aryParams[i]).ParameterName==key)
return true;
return false;
}
public void AddOrderBy(string columnName, string orderkey)
{
aryOrderby.Add(new DictionaryEntry(columnName, orderkey));
}
public DictionaryEntry this[int index]
{
get
{
return arySelect[index];
}
}
public void RemoveWhereAt(int index)
{
arySelect.RemoveAt(index);
}
public ArrayList ToParamsArray
{
get
{
return aryParams;
}
}
/*
private ArrayList CreateParams()
{
if (aryParams.Count == 0)
{
string tmpKey="";
for (int i = 0; i < arySelect.Count; i++)
{
tmpKey=arySelect[i].Key.ToString();
AddParams(arySelect.GetParam(tmpKey), arySelect[i].Value);
}
for (int i = 0; i < aryUpdate.Count; i++)
{
tmpKey = aryUpdate[i].Key.ToString();
AddParams(aryUpdate.GetParam(tmpKey), aryUpdate[i].Value);
}
for (int i = 0; i < arylike.Count; i++)
{
tmpKey = arylike[i].Key.ToString();
AddParams(arylike.GetParam(tmpKey), arylike[i].Value);
}
}
return aryParams;
}*/
private void AddParams(string name,object value,ref TSQLParams list)
{
string paramname="";
if (CheckContains(name))
paramname = name + paramLast;
else
paramname = name;
SqlParameter par = new SqlParameter(paramsChar + paramname, value);
if (value is string) par.Size = ((string)value).Length;//控制长度
aryParams.Add(par);
list.SetParam(name, paramname);
}
public int Count
{
get
{
return arySelect.Count;
}
}
public string CreateUpdateSql(string tablename)
{
//"UPDATE [RemoteFAQ] SET [SiteId]=@SiteId, [Type]=@Type, [question]=@Problem, [answer]=" +
//"@Key WHERE ([RemoteFAQ].[FaqId] = @FaqId)";
System.Text.StringBuilder sql = new System.Text.StringBuilder();
sql.Append( "UPDATE " + tablename + " SET ");
//更新项
string key = aryUpdate[0].Key.ToString();
sql.Append( " [" + key.ToString() + "] = @" + aryUpdate.GetParam(key) + " ");
for (int i = 1; i < aryUpdate.Count; i++)
{
key= aryUpdate[i].Key.ToString();
sql.Append( " ,[" + key + "] = @" + aryUpdate.GetParam(key) + " ");
}
//选择项
sql.Append(" where ");
for (int i = 0; i < arySelect.Count; i++)
{
key = arySelect[i].Key.ToString();
if (i > 0) sql.Append(" and ");
sql.Append( " [" + key + "] = @" + arySelect.GetParam(key) + " ");
}
return sql.ToString();
}
public string CreateSelectSql(string tablename, string column)
{
string queryString = "SELECT " + column + " FROM " + tablename + " WHERE 1=1 ";
string tmpkey = "";
//条件
for (int i = 0; i < arySelect.Count; i++)
{
tmpkey = arySelect[i].Key.ToString();
queryString += " and ([" + tmpkey + "] = @" + arySelect.GetParam(tmpkey) + ")";
}
//相拟
for (int i = 0; i < arylike.Count; i++)
{
if (!string.IsNullOrEmpty(arylike[i].Value.ToString()))
{
tmpkey = arylike[i].Key.ToString();
queryString += " and ([" + tmpkey + "] like '%'+@" + arylike.GetParam(tmpkey) + "+'%')";
}
}
//排序
string orderby = "";
for (int i = 0; i < aryOrderby.Count; i++)
{
//参数值不为空才添加
if (!string.IsNullOrEmpty(aryOrderby[i].Value.ToString()))
{
if (!string.IsNullOrEmpty(orderby))
orderby += ",";
orderby += " " + aryOrderby[i].Key + " " + aryOrderby[i].Value;
}
}
if (!string.IsNullOrEmpty(orderby))
queryString += " order by " + orderby;
return queryString;
}
public string CreateDeleteSql(string tablename)
{
string queryString = "DELETE FROM " + tablename + " WHERE 1=1 ";
for (int i = 0; i < arySelect.Count; i++)
{
queryString += " and ([" + arySelect[i].Key + "] = @" + arySelect[i].Key + ")";
}
return queryString;
}
public string CreateInsertSql(string tablename)
{
System.Text.StringBuilder queryString = new System.Text.StringBuilder();
queryString.Append("INSERT INTO ");
queryString.Append(tablename + " ( " + arySelect[0].Key);
for (int i = 1; i < arySelect.Count; i++)
queryString.Append(" ," + arySelect[i].Key);
queryString.Append(") VALUES (@" + arySelect[0].Key);
for (int i = 1; i < arySelect.Count; i++)
queryString.Append(" ,@" + arySelect[i].Key);
queryString.Append(")");
return queryString.ToString();
}
}
public class TSQLParams : System.Collections.Generic.List<DictionaryEntry>
{
Hashtable hastable = new Hashtable();
public string GetParam(string key)
{
return hastable[key].ToString();
}
/*
public bool ContainKey(string key)
{
for (int i = 0; i < Count; i++)
if (this[i].Key == key)
return true;
return false;
}
*/
public void SetParam(string key, string value)
{
hastable[key] = value;
}
}
sealed public class OrderbyKey
{
public static string Asc = "asc";
public static string Desc = "desc";
}
}
Conn.cs
using System;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Collections;
namespace LymSite.Common
{
/// <summary>
/// Conn 的摘要说明
/// </summary>
public class Conn
{
private static string connectionString = "Data Source=test;Initial Catalog=test;Persist Security Info=True;User ID=sa;Password=";
public static string ConnectionString
{
get { return Conn.connectionString; }
set { Conn.connectionString = value; }
}
//private static SqlConnection connection;
public Conn()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
static Conn()
{
//connection = new System.Data.SqlClient.SqlConnection(connectionString);
}
//创建一个新的连接
public static SqlConnection CreateDb()
{
return new System.Data.SqlClient.SqlConnection(connectionString);
}
public static IDataReader ExecuteReader(string sql, ArrayList list)
{
SqlCommand dbCommand = CreateSqlCommand(sql, list);
IDataReader dr = dbCommand.ExecuteReader(CommandBehavior.CloseConnection);
return dr;
}
public static IDataReader ExecuteReader(string sql)
{
return ExecuteReader(sql, null);
}
public static int ExecuteNonQuerySP(string spName, ArrayList list)
{
SqlCommand dbCommand = CreateSqlCommand(spName, list);
dbCommand.CommandType = CommandType.StoredProcedure;
int i = 0;
try
{
i = dbCommand.ExecuteNonQuery();
}
finally
{
dbCommand.Connection.Close();
}
return i;
}
public static int ExecuteNonQuery(string sql, ArrayList list)
{
SqlCommand dbCommand = CreateSqlCommand(sql, list);
int i = 0;
try
{
i = dbCommand.ExecuteNonQuery();
}
finally
{
dbCommand.Connection.Close();
}
return i;
}
public static object ExecuteScalar(string sql)
{
return ExecuteScalar(sql,null);
}
public static object ExecuteScalar(string sql, ArrayList list)
{
SqlCommand dbCommand = CreateSqlCommand(sql, list);
object obj;
try
{
obj = dbCommand.ExecuteScalar();
}
finally
{
dbCommand.Connection.Close();
}
return obj;
}
public static int ExecuteNonQuery(string queryString)
{
return ExecuteNonQuery(queryString, null);
}
public static DataSet ExecuteDateSet(string sql, ArrayList paramList)
{
return ExecuteDataSet(sql, paramList, false);
}
public static DataSet ExecuteDataSet(string sql, ArrayList paramList,bool isSp)
{
SqlCommand cmd = CreateSqlCommand(sql, paramList);
if (isSp)
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter sqlda = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
sqlda.Fill(ds);
cmd.Connection.Close();
return ds;
}
/// <summary>
/// 创建一个SqlCommand，并打开数据库
/// </summary>
/// <param name="sql"></param>
/// <param name="list"></param>
/// <returns></returns>
private static SqlCommand CreateSqlCommand(string sql, ArrayList list)
{
SqlConnection dbConnection = Conn.CreateDb();
SqlCommand dbCommand = new System.Data.SqlClient.SqlCommand(sql, dbConnection);
if (list != null)
{
foreach (SqlParameter p in list)
{
dbCommand.Parameters.Add(p);
}
}
dbConnection.Open();
return dbCommand;
}
}
}
