运用委托手动创建ORM代码,CSharp代码库,德仔网

代码语言
代码分类
【CSharp】运用委托手动创建ORM
作者:李朝强 / 发布于2015/5/6/ 750
经常在项目中运用ORM框架，.NET上，除了EF、NHibernate外，还有很多。最近用C#简单的创建了一个ORM框。这要从对Model的认识开始，都知道，对一个对象来说，赋值或取值操作，实际上就是对一个对象的实例变量的操作，即使我们这样写：public string Names{get;set;}，实际上操作的还是实例对象的变量，只是某种形式上的简化而已。在编程的世界里，最长见的数据形式就是key-value，用它几乎可以描述任何对象和数据。例如：属性（属性名（key)=属性值(value)）、json、二维数组（[{key:value},{key:value},.....]）。由于物质的客观实在性，我们在认知事物的过程中，都会对物质进行标识，通俗的讲，也就是给它定义一个名字，而这个名字，非常方便我们进一步去了解事物的本质。在以下的ORM程序设计中，用到了数据字典Dictionary<string,object>，用string来定义数据的名字，用object来存储对象的信息，由此，我把对对象的属性的操作,转化为对数据字典的操作，在数据处理逻辑中，运用数据字典，生成执行SQL。在这里Model统一派生于EntityBase（抽象类）.

	using System;
	using System.Collections.Generic;
	using System.Data;
	using System.Collections;
	using System.Linq;
	using System.Text;
	using LCQ.DataDriver.Event;
	/*-=-=-=-=-=-=-=-=-=-=-=-=-=-=--=-=-=-=-=--=-=--=-=--=-=--=-=--=-=--=-=--=-=--=-=--=-=--=-=--=-=--=-
	*
	*
	* 说 明：实体抽象基类（base)
	* 作 者：李朝强
	* 修 改：
	* 日 期：2015/01/10
	* 备 注：
	*
	*
	* -=-=--=-=--=-=--=-=--=-=--=-=--=-=--=-=--=-=--=-=--=-=--=-=--=-=--=-=--=-=--=-==--=-=--=-=--=-=-*/
	namespace LCQ.DataDriver.Entity
	{
	
	 /// <summary>
	 /// 实体基类
	 /// </summary>
	 [Serializable]
	 public abstract class EntityBase
	 {
	 #region Base Member
	 /// <summary>
	 ///字段和值
	 /// </summary>
	 public Dictionary<string, object> FieldValue { get; protected set; }
	
	 /// <summary>
	 ///主键
	 /// </summary>
	 public List<string> PrimaryKeys { get; protected set; }
	
	 /// <summary>
	 /// 标识列
	 /// </summary>
	 public string Identity { get; protected set; }
	
	 /// <summary>
	 /// 获取实体对应的数据库表名
	 /// </summary>
	 public string TableName { get; protected set; }
	
	 /// <summary>
	 /// 字段大小
	 /// </summary>
	 public Dictionary<string, int> StringFieldSize { get; protected set; }
	 #endregion
	
	 #region Event
	 /// <summary>
	 /// 属性改变事件处理句柄
	 /// </summary>
	 /// <param name="sender"></param>
	 /// <param name="e"></param>
	 public delegate void PropertyChangedHandler(object sender, PropertyEventArg e);
	
	 /// <summary>
	 /// 属性委托处理句柄
	 /// </summary>
	 private PropertyChangedHandler _OnPropertyChanged = null;
	
	 /// <summary>
	 /// 对象属性改变时发生事件
	 /// </summary>
	 public event PropertyChangedHandler OnPropertyChanged
	 {
	 add { _OnPropertyChanged += value; }
	 remove { _OnPropertyChanged -= value; }
	 }
	 #endregion
	
	 #region 构造器
	 /// <summary>
	 /// 构造器
	 /// </summary>
	 public EntityBase()
	 {
	 //初始化数据字典
	 FieldValue = new Dictionary<string, object>();
	 //字段大小
	 StringFieldSize = new Dictionary<string, int>();
	 }
	 #endregion
	
	 #region 索引器
	 /// <summary>
	 /// 获取或设置实体属性
	 /// </summary>
	 /// <param name="name">字段名</param>
	 /// <returns></returns>
	 public object this[string name]
	 {
	 get { return FieldValue[name]; }
	 set { FieldValue[name] = value; }
	 }
	 #endregion
	
	 #region 设置属性值
	 /// <summary>
	 /// 设置属性值
	 /// </summary>
	 /// <param name="name">属性名</param>
	 /// <param name="value">属性值</param>
	 public virtual void SetPropertyValue(string name, object value)
	 {
	 FieldValue[name] = value;
	 //属性改变事件
	 if (_OnPropertyChanged != null)
	 {
	 LCQ.DataDriver.Event.PropertyEventArg e = new PropertyEventArg(name, value);
	 _OnPropertyChanged(this, e);
	 }
	 }
	 #endregion
	
	 #region 获取属性值
	 /// <summary>
	 /// 获取属性值
	 /// </summary>
	 /// <param name="name">属性名</param>
	 /// <returns></returns>
	 protected virtual T GetPropertyValue<T>(string name)
	 {
	 if (!FieldValue.ContainsKey(name) ||
	 null == FieldValue[name] ||
	 FieldValue[name] == DBNull.Value)
	 {
	 return default(T);
	 }
	 return (T)FieldValue[name];
	 }
	 #endregion
	
	 #region //创建实例
	 /// <summary>
	 /// 创建实例
	 /// </summary>
	 /// <typeparam name="T">类型</typeparam>
	 /// <returns></returns>
	 public T CreateInstance<T>()
	 where T : EntityBase, new()
	 {
	 return new T();
	 }
	 #endregion
	
	 #region 克隆对象
	 /// <summary>
	 /// 对象克隆
	 /// </summary>
	 /// <returns></returns>
	 public virtual T Clone<T>()
	 where T : EntityBase, new()
	 {
	 T current = (T)this;
	 T cloneObj = new T();
	 //属性克隆
	 foreach (KeyValuePair<string, object> item in current.FieldValue)
	 {
	 //赋值：字段属性
	 cloneObj.FieldValue[item.Key] = item.Value;
	 }
	 return cloneObj;
	 }
	
	 /// <summary>
	 /// 克隆对象
	 /// </summary>
	 /// <typeparam name="T">类型</typeparam>
	 /// <param name="fset">委托</param>
	 /// <returns></returns>
	 public virtual T Clone<T>(Func<T, T> fset)
	 where T : EntityBase, new()
	 {
	 T model = fset((T)this);
	 return model;
	 }
	 #endregion
	 }
	}
	2. [代码]实体容器
	?
	1
	2
	3
	4
	5
	6
	7
	8
	9
	10
	11
	12
	13
	14
	15
	16
	17
	18
	19
	20
	21
	22
	23
	24
	25
	26
	27
	28
	29
	30
	31
	32
	33
	34
	35
	36
	37
	38
	39
	40
	41
	42
	43
	44
	45
	46
	47
	48
	49
	50
	51
	52
	53
	54
	55
	56
	57
	using System;
	using System.Collections.Generic;
	using System.Data;
	using System.Linq;
	using System.Text;
	/*-=-=-=-=-=-=-=-=-=-=-=-=-=-=--=-=-=-=-=--=-=--=-=--=-=--=-=--=-=--=-=--=-=--=-=--=-=--=-=--=-=--=-
	*
	*
	* 说 明：实体容器（base)
	* 作 者：李朝强
	* 修 改：
	* 日 期：2015/01/10
	* 备 注：
	*
	*
	* -=-=--=-=--=-=--=-=--=-=--=-=--=-=--=-=--=-=--=-=--=-=--=-=--=-=--=-=--=-=--=-==--=-=--=-=--=-=-*/
	namespace LCQ.DataDriver.Entity
	{
	 public class EntityContainer
	 {
	 #region 构造函数
	 /// <summary>
	 /// 构造函数
	 /// </summary>
	 /// <param name="data"></param>
	 public EntityContainer(IDataReader data)
	 {
	 dataReader = data;
	 }
	 #endregion
	
	 #region Member
	 /// <summary>
	 /// 数据指针
	 /// </summary>
	 private IDataReader dataReader = null;
	 #endregion
	
	 #region 方法
	 /// <summary>
	 /// 取值
	 /// </summary>
	 /// <typeparam name="T">类型</typeparam>
	 /// <param name="Name">字段名</param>
	 /// <returns></returns>
	 public T GetValue<T>(string Name)
	 {
	 object value = dataReader[Name];
	 if (null == value || value == DBNull.Value)
	 {
	 return default(T);
	 }
	 return (T)value;
	 }
	 #endregion
	 }
	}
	3. [代码]实体查询类
	?
	1
	2
	3
	4
	5
	6
	7
	8
	9
	10
	11
	12
	13
	14
	15
	16
	17
	18
	19
	20
	21
	22
	23
	24
	25
	26
	27
	28
	29
	30
	31
	32
	33
	34
	35
	36
	37
	38
	39
	40
	41
	42
	43
	44
	45
	46
	47
	48
	49
	50
	51
	52
	53
	54
	55
	56
	57
	58
	59
	60
	61
	62
	63
	64
	65
	66
	67
	68
	69
	70
	71
	72
	73
	74
	75
	76
	77
	78
	79
	80
	81
	82
	83
	84
	85
	86
	87
	88
	89
	90
	91
	92
	93
	94
	95
	96
	97
	98
	99
	100
	101
	102
	103
	104
	105
	106
	107
	108
	109
	110
	111
	112
	113
	114
	115
	116
	117
	118
	119
	120
	121
	122
	123
	124
	125
	126
	127
	128
	129
	130
	131
	132
	133
	134
	135
	136
	137
	138
	139
	140
	141
	142
	143
	144
	145
	146
	147
	148
	149
	150
	151
	152
	153
	154
	155
	156
	157
	158
	159
	160
	161
	162
	163
	164
	165
	166
	167
	168
	169
	170
	171
	172
	173
	174
	175
	176
	177
	178
	179
	180
	181
	182
	183
	184
	185
	186
	187
	188
	189
	190
	191
	192
	193
	194
	195
	196
	197
	198
	199
	200
	201
	202
	203
	204
	205
	206
	207
	208
	209
	210
	211
	212
	213
	214
	215
	216
	217
	218
	219
	220
	221
	222
	223
	224
	225
	226
	227
	228
	229
	230
	231
	232
	233
	234
	235
	236
	237
	238
	239
	240
	241
	242
	243
	244
	245
	246
	247
	248
	249
	250
	251
	252
	253
	254
	255
	256
	257
	258
	259
	260
	261
	262
	263
	264
	265
	266
	267
	268
	269
	270
	271
	272
	273
	274
	275
	276
	277
	278
	279
	280
	281
	282
	283
	284
	285
	286
	287
	288
	289
	290
	291
	292
	293
	294
	295
	296
	297
	298
	299
	300
	301
	302
	303
	304
	305
	306
	307
	308
	309
	310
	311
	312
	313
	314
	315
	316
	317
	318
	319
	320
	321
	322
	323
	324
	325
	326
	327
	328
	329
	330
	331
	332
	333
	334
	335
	336
	337
	338
	339
	340
	341
	342
	343
	344
	345
	346
	347
	348
	349
	350
	351
	352
	353
	354
	355
	356
	357
	358
	359
	360
	361
	362
	363
	364
	365
	366
	367
	368
	369
	370
	371
	372
	373
	374
	375
	376
	377
	378
	379
	380
	381
	382
	383
	384
	385
	386
	387
	388
	389
	390
	391
	392
	using System;
	using System.Collections.Generic;
	using System.Linq;
	using System.Text;
	using System.Collections;
	using System.Data;
	using System.Data.SqlClient;
	/**************************************************************************
	*
	* Author:
	* Date:
	* Description:提供实体查询的类
	* Mark:
	*
	* **********************************************************************/
	namespace LCQ.DataDriver.Entity
	{
	 #region 实体查询
	 /// <summary>
	 /// 实体查询
	 /// </summary>
	 /// <typeparam name="T"></typeparam>
	 public class EntityQuery<T>
	 where T : EntityBase, new()
	 {
	 #region 静态构造器
	 /// <summary>
	 /// 静态构造器
	 /// </summary>
	 static EntityQuery()
	 {
	 _instance = new EntityQuery<T>();
	 }
	 #endregion
	
	 #region Member
	 /// <summary>
	 /// 实例
	 /// </summary>
	 private static EntityQuery<T> _instance = null;
	
	 /// <summary>
	 /// 当前实例
	 /// </summary>
	 public static EntityQuery<T> Instance
	 {
	 get
	 {
	 _instance = _instance ?? new EntityQuery<T>();
	 return _instance;
	 }
	 }
	 #endregion
	
	 #region 方法
	 /// <summary>
	 /// 添加一条记录
	 /// </summary>
	 /// <param name="model">实体</param>
	 /// <returns></returns>
	 public bool Add(T model)
	 {
	 System.Data.SqlClient.SqlParameter[] parameters = new System.Data.SqlClient.SqlParameter[model.FieldValue.Keys.Count];
	 string strCmdText = SQLBuilder.BuildInsertSQL<T>(model, ref parameters);
	 if (!string.IsNullOrEmpty(model.Identity))
	 {
	 strCmdText += "SELECT @@IDENTITY;";
	 }
	 object obj = LCQ.Data.DBHelper.ExcuteScalar(strCmdText,
	 parameters);
	 if (obj == null || obj == DBNull.Value) { return false; }
	 if (!string.IsNullOrEmpty(model.Identity))
	 {
	 model.SetPropertyValue(model.Identity, Convert.ToInt32(obj));
	 }
	 return true;
	 }
	
	 /// <summary>
	 /// 事务：添加一条记录
	 /// </summary>
	 /// <param name="model">实体</param>
	 /// <param name="transaction">事务对象</param>
	 /// <returns></returns>
	 public int Add(T model, IDbTransaction transaction)
	 {
	 System.Data.SqlClient.SqlParameter[] parameters = new System.Data.SqlClient.SqlParameter[model.FieldValue.Keys.Count];
	 string strCmdText = SQLBuilder.BuildInsertSQL<T>(model, ref parameters);
	 if (!string.IsNullOrEmpty(model.Identity))
	 {
	 strCmdText += "SELECT @@IDENTITY;";
	 }
	 //执行事务
	 object obj = LCQ.Data.DBHelper.ExcuteScalarTran(strCmdText,
	 transaction,
	 parameters);
	 if (obj == null || obj == DBNull.Value) { return 0; }
	 int identiy = Convert.ToInt32(obj);
	 if (!string.IsNullOrEmpty(model.Identity))
	 {
	 model.SetPropertyValue(model.Identity, identiy);
	 }
	 return identiy;
	 }
	
	 /// <summary>
	 /// 批量添加
	 /// </summary>
	 /// <param name="models">实体列表</param>
	 /// <returns></returns>
	 public void Add(List<T> models)
	 {
	 SqlConnection connection = LCQ.Data.DBHelper.CreateDBConnectionAndOpen();
	 IDbTransaction tran = null;
	 try
	 {
	 tran = connection.BeginTransaction();
	 for (int i = 0; i < models.Count; i++)
	 {
	 Add(models[i], tran);
	 }
	 tran.Commit();
	 }
	 catch (Exception ex)
	 {
	 tran.Rollback();
	 throw ex;
	 }
	 finally
	 {
	 connection.Close();
	 connection.Dispose();
	 tran.Dispose();
	 }
	 }
	
	 /// <summary>
	 /// 更新记录
	 /// </summary>
	 /// <param name="model"></param>
	 /// <returns></returns>
	 public bool Update(T model)
	 {
	 System.Data.SqlClient.SqlParameter[] parameters = new System.Data.SqlClient.SqlParameter[model.FieldValue.Keys.Count];
	 string strCmdText = SQLBuilder.BuildUpdateSQL<T>(model, ref parameters);
	 int iResult = LCQ.Data.DBHelper.ExcuteNonQuery(strCmdText, parameters);
	 return (iResult > 0);
	 }
	
	 /// <summary>
	 /// 事务：更新
	 /// </summary>
	 /// <param name="model"></param>
	 /// <param name="transaction"></param>
	 /// <returns></returns>
	 public int Update(T model, IDbTransaction transaction)
	 {
	 System.Data.SqlClient.SqlParameter[] parameters = new System.Data.SqlClient.SqlParameter[model.FieldValue.Keys.Count];
	 string strCmdText = SQLBuilder.BuildUpdateSQL<T>(model, ref parameters);
	 int iResult = LCQ.Data.DBHelper.ExcuteNonQueryTran(strCmdText,
	 transaction,
	 parameters);
	 return iResult;
	 }
	
	 /// <summary>
	 /// 删除一条记录
	 /// </summary>
	 /// <param name="model"></param>
	 /// <returns></returns>
	 public int Delete(T model)
	 {
	 string strCmdText = SQLBuilder.BuildDeleteSQL<T>(model);
	 return LCQ.Data.DBHelper.ExcuteNonQuery(strCmdText, model.FieldValue);
	 }
	
	 /// <summary>
	 /// 根据编号，删除记录
	 /// </summary>
	 /// <typeparam name="T2"></typeparam>
	 /// <param name="id">编号</param>
	 /// <returns></returns>
	 public int Delete<T2>(T2 id)
	 {
	 SqlParameter[] parameters = { new SqlParameter("@ID", SqlDbType.Int, 8) };
	 parameters[0].Value = id;
	 T model = new T();
	 string strCmdText = string.Format("DELETE FROM [{0}] WHERE ID=@ID;", model.TableName);
	 int iResult = LCQ.Data.DBHelper.ExcuteNonQuery(strCmdText, parameters);
	 return iResult;
	 }
	
	 /// <summary>
	 /// 批量删除
	 /// </summary>
	 /// <typeparam name="T2"></typeparam>
	 /// <param name="ids"></param>
	 /// <returns></returns>
	 public int Delete<T2>(T2[] ids)
	 {
	 throw new NotImplementedException();
	 }
	
	 /// <summary>
	 /// 获取符合条件的记录数
	 /// </summary>
	 /// <param name="where">条件</param>
	 /// <param name="parameters">参数</param>
	 /// <returns></returns>
	 public int GetRowCount(string where,
	 IEnumerable parameters)
	 {
	 throw new NotImplementedException();
	 }
	
	 /// <summary>
	 /// 获取列表
	 /// </summary>
	 /// <param name="columns">列</param>
	 /// <param name="where">条件</param>
	 /// <param name="orders">排序</param>
	 /// <param name="parameters">参数</param>
	 /// <returns></returns>
	 public List<T> GetList(int top,
	 string columns,
	 string tableName,
	 string where,
	 string orders,
	 IEnumerable parameters)
	 {
	 //SQL:
	 StringBuilder stbCmdText = new StringBuilder();
	 if (top > 0)
	 {
	 stbCmdText.AppendFormat("SELECT TOP({0})", top);
	 }
	 else
	 {
	 stbCmdText.Append("SELECT ");
	 }
	 stbCmdText.Append(columns);
	 stbCmdText.AppendFormat(" FROM {0}", tableName);
	 if (!string.IsNullOrEmpty(where))
	 {
	 if (where.ToLower().TrimStart().StartsWith("and", StringComparison.CurrentCultureIgnoreCase))
	 {
	 stbCmdText.Append(" WHERE 1=1 ");
	 }
	 else
	 {
	 stbCmdText.Append(" WHERE ");
	 }
	 stbCmdText.Append(where);
	 }
	 if (!string.IsNullOrEmpty(orders))
	 {
	 stbCmdText.Append(orders);
	 }
	 //List
	 List<T> list = new List<T>();
	 using (IDataReader dataReader = LCQ.Data.DBHelper.ExcuteDataReader(stbCmdText.ToString(),
	 parameters))
	 {
	 while (dataReader.Read())
	 {
	 T model = new T();//实体
	 for (int i = 0; i < dataReader.FieldCount; i++)
	 {
	 model.SetPropertyValue(dataReader.GetName(i), dataReader[i]);
	 }
	 list.Add(model);
	 }
	 dataReader.Close();
	 }
	 return list;
	 }
	
	 /// <summary>
	 /// 获取数据集
	 /// </summary>
	 /// <param name="columns">列</param>
	 /// <param name="where">条件</param>
	 /// <param name="orders">排序</param>
	 /// <param name="parameters">参数</param>
	 /// <returns></returns>
	 public DataSet GetDataSet(int top,
	 string columns,
	 string tableName,
	 string where,
	 string orders,
	 IEnumerable parameters)
	 {
	 //SQL:
	 StringBuilder stbCmdText = new StringBuilder();
	 if (top > 0)
	 {
	 stbCmdText.AppendFormat("SELECT TOP({0})", top);
	 }
	 else
	 {
	 stbCmdText.Append("SELECT ");
	 }
	 stbCmdText.Append((string.IsNullOrEmpty(columns) ? "*" : columns));
	 stbCmdText.AppendFormat(" FROM {0}", tableName);
	 stbCmdText.Append(" WHERE 1=1 ");
	 if (!string.IsNullOrEmpty(where))
	 {
	 stbCmdText.Append(where);
	 }
	 if (!string.IsNullOrEmpty(orders))
	 {
	 stbCmdText.Append(orders);
	 }
	 DataSet ds = LCQ.Data.DBHelper.Query(stbCmdText.ToString(), parameters);
	 return ds;
	 }
	
	 /// <summary>
	 ///获取实体
	 /// </summary>
	 /// <param name="model"></param>
	 /// <returns></returns>
	 public T Fill(T model)
	 {
	 string strCmdText = "SELECT TOP(1) * FROM [" + model.TableName + "] WHERE 1=1 ";
	 for (int i = 0; i < model.PrimaryKeys.Count; i++)
	 {
	 strCmdText += string.Format(" AND {0}=@{0}", model.PrimaryKeys[i]);
	 }
	 using (IDataReader dataReader = LCQ.Data.DBHelper.ExcuteDataReader(strCmdText, model.FieldValue))
	 {
	 if (dataReader.Read())
	 {
	 //循环设置数据
	 for (int c = 0; c < dataReader.FieldCount; c++)
	 {
	 model.SetPropertyValue(dataReader.GetName(c), dataReader.GetValue(c));
	 }
	 }
	 }
	 return model;
	 }
	
	 /// <summary>
	 /// 【15/04/23】查询实体列表
	 /// </summary>
	 /// <typeparam name="T">实体类别</typeparam>
	 /// <param name="strCmdText">SQL</param>
	 /// <param name="parameters">参数</param>
	 /// <param name="fset">委托：实体赋值</param>
	 /// <returns></returns>
	 public List<T> Query(string strCmdText,
	 IEnumerable parameters,
	 Func<EntityContainer, T> fset)
	 {
	 List<T> list = null;
	 using (IDataReader dataReader = LCQ.Data.DBHelper.ExcuteDataReader(strCmdText, parameters))
	 {
	 //实体容器
	 EntityContainer e = new EntityContainer(dataReader);
	 list = new List<T>();
	 while (dataReader.Read())
	 {
	 //实体初始化
	 T model = fset(e);
	 list.Add(model);
	 }
	 e = null;
	 }
	 return list;
	 }
	
	 /// <summary>
	 /// 验证实体是否存在
	 /// </summary>
	 /// <typeparam name="T2">类型名称</typeparam>
	 /// <param name="model">实体</param>
	 /// <returns></returns>
	 public bool Exists(T model)
	 {
	 string strCmdText = "SELECT COUNT(1) ";
	 strCmdText += string.Format(" FROM [{0}] WHERE 1=1", model.TableName);
	 for (int i = 0; i < model.PrimaryKeys.Count; i++) { strCmdText += string.Format(" AND {0}=@{0}", model.PrimaryKeys[i]); }
	 object value = LCQ.Data.DBHelper.ExcuteScalar(strCmdText, model.FieldValue);
	 int iCount = Convert.ToInt32(value);
	 return (iCount > 0);
	 }
	 #endregion
	 }
	
	 #endregion 实体查询
	}
	4. [代码]属性改变事件参数
	?
	1
	2
	3
	4
	5
	6
	7
	8
	9
	10
	11
	12
	13
	14
	15
	16
	17
	18
	19
	20
	21
	22
	23
	24
	25
	26
	27
	28
	29
	30
	31
	32
	33
	34
	35
	36
	37
	38
	39
	40
	41
	42
	43
	44
	using System;
	using System.Collections.Generic;
	using System.Data;
	using System.Collections;
	using System.Linq;
	using System.Text;
	/*-=-=-=-=-=-=-=-=-=-=-=-=-=-=--=-=-=-=-=--=-=--=-=--=-=--=-=--=-=--=-=--=-=--=-=--=-=--=-=--=-=--=-
	*
	*
	* 说 明：属性改变事件参数
	* 作 者：李朝强
	* 修 改：
	* 日 期：2015/01/10
	* 备 注：
	*
	*
	* -=-=--=-=--=-=--=-=--=-=--=-=--=-=--=-=--=-=--=-=--=-=--=-=--=-=--=-=--=-=--=-==--=-=--=-=--=-=-*/
	namespace LCQ.DataDriver.Event
	{
	 public class PropertyEventArg : EventArgs
	 {
	 /// <summary>
	 /// 获取或设置属性名
	 /// </summary>
	 public string PropertyName { get; set; }
	
	 /// <summary>
	 /// 获取或设置属性值
	 /// </summary>
	 public object PropertyValue { get; set; }
	
	 /// <summary>
	 /// 构造函数
	 /// </summary>
	 /// <param name="PropertyName">属性名</param>
	 /// <param name="PropertyValue">属性值</param>
	 public PropertyEventArg(string __PropertyName,
	 object __PropertyValue)
	 {
	 PropertyName = __PropertyName;
	 PropertyValue = __PropertyValue;
	 }
	 }
	}
	5. [代码]根据实体，生成SQL类
	?
	1
	2
	3
	4
	5
	6
	7
	8
	9
	10
	11
	12
	13
	14
	15
	16
	17
	18
	19
	20
	21
	22
	23
	24
	25
	26
	27
	28
	29
	30
	31
	32
	33
	34
	35
	36
	37
	38
	39
	40
	41
	42
	43
	44
	45
	46
	47
	48
	49
	50
	51
	52
	53
	54
	55
	56
	57
	58
	59
	60
	61
	62
	63
	64
	65
	66
	67
	68
	69
	70
	71
	72
	73
	74
	75
	76
	77
	78
	79
	80
	81
	82
	83
	84
	85
	86
	87
	88
	89
	90
	91
	92
	93
	94
	95
	96
	97
	98
	99
	100
	101
	102
	103
	104
	105
	106
	107
	108
	109
	110
	111
	112
	113
	114
	115
	116
	117
	118
	119
	120
	121
	122
	123
	124
	125
	126
	127
	128
	129
	130
	131
	132
	133
	134
	135
	136
	137
	138
	139
	140
	141
	142
	143
	144
	145
	146
	147
	148
	149
	150
	151
	152
	153
	154
	155
	156
	157
	158
	159
	160
	161
	162
	163
	164
	165
	166
	167
	168
	169
	170
	171
	172
	173
	174
	175
	176
	177
	178
	179
	180
	181
	182
	183
	184
	185
	186
	187
	188
	189
	190
	191
	192
	193
	194
	195
	196
	197
	198
	199
	200
	201
	202
	203
	204
	205
	206
	207
	208
	209
	210
	211
	212
	213
	214
	215
	216
	217
	218
	219
	220
	221
	222
	223
	224
	225
	226
	227
	228
	229
	230
	231
	232
	233
	234
	235
	236
	237
	238
	239
	240
	241
	242
	243
	244
	245
	246
	247
	248
	249
	250
	251
	252
	253
	254
	255
	256
	257
	258
	259
	260
	261
	262
	263
	264
	265
	266
	267
	268
	269
	270
	271
	272
	273
	274
	275
	276
	277
	278
	279
	280
	281
	using System;
	using System.Collections;
	using System.Collections.Generic;
	using System.Linq;
	using System.Text;
	using System.Threading.Tasks;
	/*****************************************************************************
	*
	*
	* 类 名：SQLBuilder
	* 说 明：SQL生成共同类
	* 作 者：李朝强
	*
	*
	* *************************************************************************/
	namespace LCQ.DataDriver
	{
	 public class SQLBuilder
	 {
	 /// <summary>
	 /// 根据迭代类型，生成Insert语句
	 /// </summary>
	 /// <param name="columns">列</param>
	 /// <param name="tablename">表名</param>
	 /// <returns></returns>
	 public static string BuildInsertSQL(IEnumerable columns, string tablename)
	 {
	 //SQL:
	 StringBuilder sql = new StringBuilder();
	 sql.AppendFormat("INSERT INTO {0}", tablename);
	 sql.Append("(");
	
	 StringBuilder stbColumns = new StringBuilder();//列
	 StringBuilder stbParameters = new StringBuilder();//参数值
	 //Hashtable
	 if (columns is Hashtable)
	 {
	 foreach (DictionaryEntry item in columns)
	 {
	 //列
	 stbColumns.Append("[" + item.Key.ToString().TrimStart('@') + "]");
	 stbColumns.Append(",");
	 //值
	 stbParameters.Append("@" + item.Key.ToString().TrimStart('@'));
	 stbParameters.Append(",");
	 }
	 }
	 //Dictionary<string,object>
	 if (columns is Dictionary<string, object>)
	 {
	 foreach (KeyValuePair<string, object> item in columns)
	 {
	 //列
	 stbColumns.Append("[" + item.Key.ToString().TrimStart('@') + "]");
	 stbColumns.Append(",");
	 //值
	 stbParameters.Append("@" + item.Key.ToString().TrimStart('@'));
	 stbParameters.Append(",");
	 }
	 }
	 //移除最后一个逗号
	 stbColumns.Remove(stbColumns.Length - 1, 1);
	 stbParameters.Remove(stbParameters.Length - 1, 1);
	
	 sql.Append(stbColumns);
	 sql.Append(")");
	 //Value
	 sql.Append(" VALUES(");
	 sql.Append(stbParameters);
	 sql.Append(");");
	 return sql.ToString();
	 }
	
	
	 /// <summary>
	 /// 生成更新SQL
	 /// </summary>
	 /// <param name="Columns">更新列</param>
	 /// <param name="tablename">表名</param>
	 /// <returns></returns>
	 public static string BuildUpdateSQL(IEnumerable Columns,
	 IEnumerable Where,
	 string tablename)
	 {
	 StringBuilder sql = new StringBuilder();
	 sql.AppendFormat("UPDATE {0}", tablename);
	 sql.Append(" SET ");
	 if (Columns is Hashtable)
	 {
	 foreach (DictionaryEntry item in Columns)
	 {
	 sql.AppendFormat("{0}=@{1}", item.Key.ToString().TrimStart('@'), item.Key);
	 sql.Append(",");
	 }
	 }
	 sql.Remove(sql.Length - 1, 1);//移除最后一个逗号
	 //条件
	 sql.Append(" WHERE 1=1 ");
	 if (Columns is Dictionary<string, object>)
	 {
	 foreach (KeyValuePair<string, object> item in Where)
	 {
	 sql.AppendFormat(" AND {0}=@{1}", item.Key.ToString().TrimStart('@'), item.Key);
	 }
	 }
	 return sql.ToString();
	 }
	
	 /// <summary>
	 /// 更新
	 /// </summary>
	 /// <param name="strColumnInfo"></param>
	 /// <param name="strWhere"></param>
	 /// <param name="tbName"></param>
	 /// <returns></returns>
	 public static string BuildUpdateSQL(string strColumnInfo,
	 string strWhere,
	 string tbName)
	 {
	 StringBuilder sql = new StringBuilder();
	 sql.AppendFormat("UPDATE {0}", tbName);
	 sql.Append(" SET ");
	 sql.Append(strColumnInfo);
	 sql.AppendFormat(" WHERE {0}", strWhere);
	 return sql.ToString();
	 }
	
	 /// <summary>
	 /// 生成删除语句
	 /// </summary>
	 /// <param name="where">条件</param>
	 /// <param name="tablename">表名</param>
	 /// <returns></returns>
	 public static string BuildDeleteSQL(IEnumerable where, string tablename)
	 {
	 StringBuilder sql = new StringBuilder();
	 sql.AppendFormat("DELETE FROM {0} WHERE 1=1 ", tablename);
	 if (where is Hashtable)
	 {
	 foreach (DictionaryEntry item in where)
	 {
	 sql.AppendFormat(" AND {0}=@{1}", item.Key.ToString().TrimStart('@'), item.Key);
	 }
	 }
	 if (where is Dictionary<string, object>)
	 {
	 foreach (KeyValuePair<string, object> item in where)
	 {
	 sql.AppendFormat(" AND {0}=@{1}", item.Key.TrimStart('@'), item.Key);
	 }
	 }
	 return sql.ToString();
	 }
	
	 /// <summary>
	 /// 生成删除语句
	 /// </summary>
	 /// <param name="strWhere">条件</param>
	 /// <param name="tableName">表名</param>
	 /// <returns></returns>
	 public static string BuildDeleteSQL(string strWhere, string tableName)
	 {
	 StringBuilder sql = new StringBuilder();
	 sql.AppendFormat("DELETE FROM [{0}] WHERE {1}",
	 tableName.Trim(']').Trim('['),
	 strWhere);
	 return sql.ToString();
	 }
	
	 /// <summary>
	 /// 根据实体，生成Insert语句
	 /// </summary>
	 /// <typeparam name="T">类型</typeparam>
	 /// <param name="model">模型</param>
	 /// <returns></returns>
	 public static string BuildInsertSQL<T>(T model,
	 ref System.Data.SqlClient.SqlParameter[] parameters)
	 where T : LCQ.DataDriver.Entity.EntityBase
	 {
	 //SQL:
	 StringBuilder sql = new StringBuilder();
	 sql.AppendFormat("INSERT INTO {0}", model.TableName);
	 sql.Append("(");
	 StringBuilder stbColumns = new StringBuilder();//列
	 StringBuilder stbParameters = new StringBuilder();//参数值
	 //Dictionary<string,object>
	 int i = 0;
	 foreach (KeyValuePair<string, object> item in model.FieldValue)
	 {
	 //列
	 stbColumns.Append("[" + item.Key.ToString() + "]");
	 stbColumns.Append(",");
	 //值
	 stbParameters.Append("@" + item.Key.ToString());
	 stbParameters.Append(",");
	 //Parameters
	 System.Data.SqlClient.SqlParameter Parameters = new System.Data.SqlClient.SqlParameter("@" + item.Key.ToString(), item.Value);
	 if (model.StringFieldSize != null && model.StringFieldSize.ContainsKey(string.Format("{0}_{1}", model.TableName, item.Key)))
	 {
	 Parameters.Size = model.StringFieldSize[string.Format("{0}_{1}", model.TableName, item.Key)];
	 }
	 parameters[i] = Parameters;
	 i++;
	 }
	 //移除最后一个逗号
	 stbColumns.Remove(stbColumns.Length - 1, 1);
	 stbParameters.Remove(stbParameters.Length - 1, 1);
	
	 sql.Append(stbColumns);
	 sql.Append(")");
	 //Value
	 sql.Append(" VALUES(");
	 sql.Append(stbParameters);
	 sql.Append(");");
	 return sql.ToString();
	 }
	
	 /// <summary>
	 /// 根据实体，创建更新语句
	 /// </summary>
	 /// <typeparam name="T">实体类别，必须继承自Model.EntityBase</typeparam>
	 /// <param name="model">实体</param>
	 /// <returns></returns>
	 public static string BuildUpdateSQL<T>(T model,
	 ref System.Data.SqlClient.SqlParameter[] parameters)
	 where T : LCQ.DataDriver.Entity.EntityBase
	 {
	 StringBuilder stbSQL = new StringBuilder();
	 StringBuilder stbWhere = new StringBuilder();
	 stbSQL.Append("UPDATE [" + model.TableName.Trim('[', ']')+ "] SET ");
	 stbWhere.Append(" WHERE 1=1 ");
	 //条件
	 for (int j = 0; j < model.PrimaryKeys.Count; j++)
	 {
	 stbWhere.AppendFormat(" And {0}=@{0}", model.PrimaryKeys[j]);
	 }
	 //列
	 int i = 0;
	 foreach (KeyValuePair<string, object> ColumInfo in model.FieldValue)
	 {
	 //参数
	 System.Data.SqlClient.SqlParameter parameter = new System.Data.SqlClient.SqlParameter("@" + ColumInfo.Key, ColumInfo.Value);
	 parameter.Size = 255;//默认长度
	 if (model.StringFieldSize != null && model.StringFieldSize.ContainsKey(string.Format("{0}_{1}", model.TableName, ColumInfo.Key)))
	 {
	 parameter.Size = model.StringFieldSize[string.Format("{0}_{1}", model.TableName, ColumInfo.Key)];
	 }
	 parameters[i] = parameter;
	 i++;
	 //判断是否是主键
	 if (model.PrimaryKeys.Contains(ColumInfo.Key))
	 {
	 continue;
	 }
	 stbSQL.AppendFormat("{0}=@{0},", ColumInfo.Key);
	 }
	 stbSQL.Remove(stbSQL.Length - 1, 1);
	 //条件
	 return (stbSQL.ToString() + stbWhere.ToString());
	 }
	
	 /// <summary>
	 /// 根据实体删除
	 /// </summary>
	 /// <typeparam name="T">实体类别，必须继承自Model.EntityBase</typeparam>
	 /// <param name="model"></param>
	 /// <returns>实体</returns>
	 public static string BuildDeleteSQL<T>(T model)
	 where T : LCQ.DataDriver.Entity.EntityBase
	 {
	 StringBuilder stbSQL = new StringBuilder();
	 stbSQL.AppendFormat("DELETE FROM [{0}]", model.TableName.Trim(']').Trim('['));
	 stbSQL.Append(" WHERE 1=1 ");
	 for (int i = 0; i < model.PrimaryKeys.Count; i++)
	 {
	 stbSQL.AppendFormat(" AND {0}=@{1}", model.PrimaryKeys[i], model.PrimaryKeys[i]);
	 }
	 return stbSQL.ToString();
	 }
	 }
	}
	6. [代码]实体操作帮助类
	?
	1
	2
	3
	4
	5
	6
	7
	8
	9
	10
	11
	12
	13
	14
	15
	16
	17
	18
	19
	20
	21
	22
	23
	24
	25
	26
	27
	28
	29
	30
	31
	32
	33
	34
	35
	36
	37
	38
	39
	40
	41
	42
	43
	44
	45
	46
	47
	48
	49
	50
	51
	52
	53
	54
	55
	56
	57
	58
	59
	60
	61
	62
	63
	64
	65
	66
	67
	68
	69
	70
	71
	72
	73
	74
	75
	76
	77
	78
	79
	80
	81
	82
	83
	84
	85
	86
	87
	88
	89
	90
	91
	92
	93
	94
	95
	96
	97
	98
	99
	100
	101
	102
	103
	104
	105
	106
	107
	108
	109
	110
	111
	112
	113
	114
	115
	116
	117
	118
	119
	120
	121
	122
	123
	124
	125
	126
	127
	128
	129
	130
	131
	132
	133
	134
	135
	136
	137
	138
	139
	140
	141
	142
	143
	144
	145
	146
	147
	148
	149
	150
	151
	152
	153
	154
	155
	156
	157
	158
	159
	160
	161
	162
	163
	164
	165
	166
	167
	168
	169
	170
	171
	172
	173
	174
	175
	176
	177
	178
	179
	180
	181
	182
	183
	184
	185
	186
	187
	188
	189
	190
	191
	192
	193
	194
	195
	196
	197
	198
	199
	200
	201
	202
	203
	204
	205
	206
	207
	208
	209
	210
	211
	212
	213
	214
	215
	216
	217
	218
	219
	220
	221
	222
	223
	224
	225
	226
	227
	228
	229
	230
	231
	232
	233
	234
	235
	236
	237
	238
	239
	240
	241
	242
	243
	244
	245
	246
	247
	248
	249
	250
	251
	252
	253
	254
	255
	256
	257
	258
	259
	260
	261
	using System;
	using System.Collections.Generic;
	using System.Linq;
	using System.Text;
	using System.Data;
	using System.Collections;
	using LCQ.Data;
	using System.Data.SqlClient;
	/***********************************************************************
	*
	*
	*
	* 说 明：ADO操作辅助类
	*
	* 作 者：李朝强(http://my.oschina.net/lichaoqiang)
	*
	*
	* *******************************************************************/
	namespace LCQ.DataDriver
	{
	 public class AdoHelper
	 {
	
	 /// <summary>
	 /// 数据库回话
	 /// </summary>
	 private static DbSession __DbSessionShareObj = null;
	
	 /// <summary>
	 /// 打开数据库回话
	 /// </summary>
	 public static void OpenSession()
	 {
	 __DbSessionShareObj = __DbSessionShareObj ?? new DbSession();
	 lock (__DbSessionShareObj)
	 {
	 __DbSessionShareObj.OpenSession();//打开数据库回话
	 }
	 }
	
	 /// <summary>
	 /// 提交回话
	 /// </summary>
	 public static void CommitSession()
	 {
	 lock (__DbSessionShareObj)
	 {
	 __DbSessionShareObj.CommitSession();
	 }
	 }
	
	
	 /// <summary>
	 /// 执行SQL，
	 /// </summary>
	 /// <param name="cmdText">SQL</param>
	 /// <param name="parameters">参数</param>
	 /// <returns></returns>
	 public static IDataReader ExcuteDataReader(string cmdText,
	 IEnumerable parameters)
	 {
	 return LCQ.Data.DBHelper.ExcuteDataReader(cmdText, parameters);
	 }
	
	 /// <summary>
	 /// 获取记录数
	 /// </summary>
	 /// <param name="where">条件</param>
	 /// <param name="tableName">表名</param>
	 /// <param name="parameters">参数</param>
	 /// <returns></returns>
	 public static int Count(string where, string tableName, IEnumerable parameters)
	 {
	 return LCQ.Data.DBHelper.Count(where, tableName, parameters);
	 }
	
	 /// <summary>
	 /// 执行SQL，返回数据集
	 /// </summary>
	 /// <param name="strCmdText">SQL</param>
	 /// <param name="parameters">参数（Hashtable、Dictionary、SqlParameter）</param>
	 /// <returns></returns>
	 public static DataSet Query(string strCmdText,
	 IEnumerable parameters)
	 {
	 return LCQ.Data.DBHelper.Query(strCmdText, parameters);
	 }
	
	 /// <summary>
	 /// 执行SQL，返回数据集
	 /// </summary>
	 /// <param name="strCmdText">SQL</param>
	 /// <param name="parameters">参数</param>
	 /// <param name="output">输出参数</param>
	 /// <returns></returns>
	 public static DataSet Query(string strCmdText,
	 IEnumerable parameters,
	 ref Dictionary<string, object> output)
	 {
	 return LCQ.Data.DBHelper.Query(strCmdText,
	 parameters,
	 ref output);
	 }
	
	 /// <summary>
	 /// 执行SQL，返回受影响的记录数
	 /// </summary>
	 /// <param name="strCmdText">SQL</param>
	 /// <param name="parameters">参数</param>
	 /// <returns></returns>
	 public static int ExucteNonQuery(string strCmdText, IEnumerable parameters)
	 {
	 return LCQ.Data.DBHelper.ExcuteNonQuery(strCmdText, parameters);
	 }
	
	 /// <summary>
	 /// 根据条件，删除某张表中的信息
	 /// </summary>
	 /// <param name="tableName">表名</param>
	 /// <param name="where">条件(必选)</param>
	 /// <param name="parameters">参数</param>
	 /// <returns></returns>
	 public static int Delete(string tableName,
	 string where,
	 IEnumerable parameters)
	 {
	 string strCmdText = string.Format("DELETE FROM [{0}] WHERE 1=1 ", tableName);
	 strCmdText += where;
	 int iRowEffect = LCQ.Data.DBHelper.ExcuteNonQuery(strCmdText, parameters);
	 return iRowEffect;
	 }
	
	 /// <summary>
	 /// 更新数据集
	 /// </summary>
	 /// <param name="cmdText">脚本</param>
	 /// <param name="ds">数据集</param>
	 /// <returns></returns>
	 public static int UpdateDataSet(string cmdText,
	 DataSet ds,
	 Action<DataSet> action)
	 {
	 return LCQ.Data.DBHelper.UpdateDataSet(cmdText,
	 ds,
	 action);
	 }
	
	 /// <summary>
	 /// 查询：返回实体列表
	 /// </summary>
	 /// <typeparam name="T">实体类型</typeparam>
	 /// <param name="cmdText">SQL语句</param>
	 /// <param name="parameters">参数</param>
	 /// <param name="fSet">实体初始化委托</param>
	 /// <returns></returns>
	 public static List<T> Query<T>(string cmdText,
	 IEnumerable parameters,
	 Func<IDataReader, T> fSet)
	 where T : new()
	 {
	 IDataReader dataReader = null;
	 List<T> list = new List<T>();
	 using (dataReader = LCQ.Data.DBHelper.ExcuteDataReader(cmdText, parameters))
	 {
	 while (dataReader.Read())
	 {
	 T model = fSet(dataReader);
	 list.Add(model);
	 }
	 }
	 return list;
	 }
	
	 /// <summary>
	 /// 返回列表
	 /// </summary>
	 /// <typeparam name="T"></typeparam>
	 /// <param name="cmdText"></param>
	 /// <param name="parameters"></param>
	 /// <param name="fset"></param>
	 /// <returns></returns>
	 public static List<T> ToList<T>(string cmdText,
	 IEnumerable parameters,
	 Func<IDataReader, T> fSet)
	 {
	 IDataReader dataReader = null;
	 List<T> list = new List<T>();
	 using (dataReader = LCQ.Data.DBHelper.ExcuteDataReader(cmdText, parameters))
	 {
	 while (dataReader.Read())
	 {
	 T model = fSet(dataReader);
	 list.Add(model);
	 }
	 }
	 return list;
	 }
	
	 /// <summary>
	 /// 打开一个数据库事务
	 /// </summary>
	 /// <returns></returns>
	 public static IDbTransaction BeginSingleTran()
	 {
	 SqlConnection connecion = LCQ.Data.DBHelper.CreateDBConnectionAndOpen();
	 SqlTransaction tran = connecion.BeginTransaction();
	 return tran;
	 }
	
	 /// <summary>
	 /// 执行事务：
	 /// </summary>
	 /// <param name="strCmdTex">SQL</param>
	 /// <param name="transaction">关联事务</param>
	 /// <param name="parameters">参数</param>
	 /// <returns></returns>
	 public static int ExcueNonQueryTran(string strCmdTex,
	 IDbTransaction transaction,
	 IEnumerable parameters)
	 {
	 int iResult = LCQ.Data.DBHelper.ExcuteNonQueryTran(strCmdTex,
	 transaction,
	 parameters);
	 return iResult;
	 }
	
	 /// <summary>
	 /// 事务：执行SQL，返回首行首列
	 /// </summary>
	 /// <param name="strCmdTex">SQL</param>
	 /// <param name="transaction">事务</param>
	 /// <param name="parameters">参数</param>
	 /// <returns></returns>
	 public static object ExcuteScalar(string strCmdTex,
	 IDbTransaction transaction,
	 IEnumerable parameters)
	 {
	 object obj = LCQ.Data.DBHelper.ExcuteScalarTran(strCmdTex,
	 transaction,
	 parameters);
	 return obj;
	 }
	
	 /// <summary>
	 /// 是否存在，如果存在，则返回存在记录的ID
	 /// </summary>
	 /// <param name="dictColumns">条件，列</param>
	 /// <param name="TableName">表名</param>
	 /// <returns></returns>
	 public static T Exists<T>(Dictionary<string, object> dictColumns,
	 string TableName)
	 {
	 string strCmdText = string.Format("SELECT TOP(1) ID FROM [{0}] WHERE 1=1 ", TableName);
	 foreach (KeyValuePair<string, object> item in dictColumns) { strCmdText += string.Format(" AND {0}=@{0}", item.Key.TrimStart('@')); }
	 //获取值
	 object value = LCQ.Data.DBHelper.ExcuteScalar(strCmdText, dictColumns);
	 if (null == value || value == DBNull.Value) { return default(T); }
	 return (T)value;
	 }
	 }
	}
	7. [代码]数据库操作帮助类
	?
	1
	2
	3
	4
	5
	6
	7
	8
	9
	10
	11
	12
	13
	14
	15
	16
	17
	18
	19
	20
	21
	22
	23
	24
	25
	26
	27
	28
	29
	30
	31
	32
	33
	34
	35
	36
	37
	38
	39
	40
	41
	42
	43
	44
	45
	46
	47
	48
	49
	50
	51
	52
	53
	54
	55
	56
	57
	58
	59
	60
	61
	62
	63
	64
	65
	66
	67
	68
	69
	70
	71
	72
	73
	74
	75
	76
	77
	78
	79
	80
	81
	82
	83
	84
	85
	86
	87
	88
	89
	90
	91
	92
	93
	94
	95
	96
	97
	98
	99
	100
	101
	102
	103
	104
	105
	106
	107
	108
	109
	110
	111
	112
	113
	114
	115
	116
	117
	118
	119
	120
	121
	122
	123
	124
	125
	126
	127
	128
	129
	130
	131
	132
	133
	134
	135
	136
	137
	138
	139
	140
	141
	142
	143
	144
	145
	146
	147
	148
	149
	150
	151
	152
	153
	154
	155
	156
	157
	158
	159
	160
	161
	162
	163
	164
	165
	166
	167
	168
	169
	170
	171
	172
	173
	174
	175
	176
	177
	178
	179
	180
	181
	182
	183
	184
	185
	186
	187
	188
	189
	190
	191
	192
	193
	194
	195
	196
	197
	198
	199
	200
	201
	202
	203
	204
	205
	206
	207
	208
	209
	210
	211
	212
	213
	214
	215
	216
	217
	218
	219
	220
	221
	222
	223
	224
	225
	226
	227
	228
	229
	230
	231
	232
	233
	234
	235
	236
	237
	238
	239
	240
	241
	242
	243
	244
	245
	246
	247
	248
	249
	250
	251
	252
	253
	254
	255
	256
	257
	258
	259
	260
	261
	262
	263
	264
	265
	266
	267
	268
	269
	270
	271
	272
	273
	274
	275
	276
	277
	278
	279
	280
	281
	282
	283
	284
	285
	286
	287
	288
	289
	290
	291
	292
	293
	294
	295
	296
	297
	298
	299
	300
	301
	302
	303
	304
	305
	306
	307
	308
	309
	310
	311
	312
	313
	314
	315
	316
	317
	318
	319
	320
	321
	322
	323
	324
	325
	326
	327
	328
	329
	330
	331
	332
	333
	334
	335
	336
	337
	338
	339
	340
	341
	342
	343
	344
	345
	346
	347
	348
	349
	350
	351
	352
	353
	354
	355
	356
	357
	358
	359
	360
	361
	362
	363
	364
	365
	366
	367
	368
	369
	370
	371
	372
	373
	374
	375
	376
	377
	378
	379
	380
	381
	382
	383
	384
	385
	386
	387
	388
	389
	390
	391
	392
	393
	394
	395
	396
	397
	398
	399
	400
	401
	402
	403
	404
	405
	406
	407
	408
	409
	410
	411
	412
	413
	414
	415
	416
	417
	418
	419
	420
	421
	422
	423
	424
	425
	426
	427
	428
	429
	430
	431
	432
	433
	434
	435
	436
	437
	438
	439
	440
	441
	442
	443
	444
	445
	446
	447
	448
	449
	450
	451
	452
	453
	454
	455
	456
	457
	458
	459
	460
	461
	462
	463
	464
	465
	466
	467
	468
	469
	470
	471
	472
	473
	474
	475
	476
	477
	478
	479
	480
	481
	482
	483
	484
	485
	486
	487
	488
	489
	490
	491
	492
	493
	494
	495
	496
	497
	498
	499
	500
	501
	502
	503
	504
	505
	506
	507
	508
	509
	510
	511
	512
	513
	514
	515
	516
	517
	518
	519
	520
	521
	522
	523
	524
	525
	526
	527
	528
	529
	530
	531
	532
	533
	534
	535
	536
	537
	538
	539
	540
	541
	542
	543
	544
	545
	546
	547
	548
	549
	550
	551
	552
	553
	554
	555
	556
	557
	558
	559
	560
	561
	562
	563
	564
	565
	566
	567
	568
	569
	570
	571
	572
	573
	574
	575
	576
	577
	578
	579
	580
	581
	582
	583
	584
	585
	586
	587
	588
	589
	590
	591
	592
	593
	594
	595
	596
	597
	598
	599
	600
	601
	602
	603
	604
	605
	606
	607
	608
	609
	610
	611
	612
	613
	614
	615
	616
	617
	618
	619
	620
	621
	622
	623
	624
	625
	626
	627
	628
	629
	630
	631
	632
	633
	634
	635
	636
	637
	638
	639
	640
	641
	642
	643
	644
	645
	646
	647
	648
	649
	650
	651
	652
	653
	654
	655
	656
	657
	658
	659
	660
	661
	662
	663
	664
	665
	666
	667
	668
	669
	670
	671
	672
	673
	674
	675
	676
	677
	678
	679
	680
	681
	682
	683
	684
	685
	686
	687
	688
	689
	690
	691
	692
	693
	694
	695
	696
	697
	698
	699
	700
	701
	702
	703
	704
	705
	706
	707
	708
	709
	710
	711
	712
	713
	714
	715
	716
	717
	718
	719
	720
	721
	722
	723
	724
	725
	726
	727
	728
	729
	730
	731
	732
	733
	734
	735
	736
	737
	738
	739
	740
	741
	742
	743
	744
	745
	746
	747
	748
	749
	750
	751
	752
	753
	754
	755
	756
	757
	758
	759
	760
	761
	762
	763
	764
	765
	766
	767
	768
	769
	770
	771
	772
	773
	774
	775
	776
	777
	778
	779
	780
	781
	782
	783
	784
	785
	786
	787
	788
	789
	790
	791
	792
	793
	794
	795
	796
	797
	798
	799
	800
	801
	802
	803
	804
	805
	806
	807
	808
	809
	810
	811
	812
	813
	814
	815
	816
	817
	818
	819
	820
	821
	822
	823
	824
	825
	826
	827
	828
	829
	830
	831
	832
	833
	834
	835
	836
	837
	838
	839
	840
	841
	842
	843
	844
	using System;
	using System.Collections;
	using System.Collections.Generic;
	using System.Linq;
	using System.Text;
	using System.Threading.Tasks;
	using System.Data;
	using System.Data.SqlClient;
	using System.Diagnostics;
	/***********************************************************************
	*
	*
	*
	* 说 明：数据库操作类
	*
	* 作 者：李朝强(http://my.oschina.net/lichaoqiang)
	*
	*
	* *******************************************************************/
	namespace LCQ.Data
	{
	 public class DBHelper
	 {
	
	 #region　member
	 /// <summary>
	 /// 配置文件Key
	 /// </summary>
	 public const string DBCONNECTION_APPSETTING_KEY = "DBConnection";
	
	 /// <summary>
	 /// 获取链接超时时间
	 /// </summary>
	 public static int Timeout
	 {
	 get
	 {
	 string strTimeOut = System.Configuration.ConfigurationManager.AppSettings["DbTimeOut"];
	 if (!string.IsNullOrEmpty(strTimeOut))
	 {
	 return Convert.ToInt32(strTimeOut);
	 }
	 return 3000;
	 }
	 }
	 /// <summary>
	 /// 获取数据连接字符串
	 /// </summary>
	 public static string DbConnectionString
	 {
	 get
	 {
	 //加密处理
	 return System.Configuration.ConfigurationManager.ConnectionStrings[DBCONNECTION_APPSETTING_KEY].ConnectionString;
	 }
	 }
	 #endregion
	
	 #region 提供Connection、Command对象的操作方法
	 /// <summary>
	 /// 创建DB链接
	 /// </summary>
	 /// <param name="connectionName">配置文件链接名称</param>
	 /// <returns></returns>
	 public static SqlConnection CreateDBConnection(string connectionName = "DBConnection")
	 {
	 //数据库连接字符串
	 string strConnectString = System.Configuration.ConfigurationManager.ConnectionStrings[connectionName].ConnectionString;
	 SqlConnection conn = new SqlConnection(strConnectString);
	 return conn;
	 }
	 /// <summary>
	 /// 创建并打开数据库链接
	 /// </summary>
	 /// <param name="connectionName"></param>
	 /// <returns></returns>
	 public static SqlConnection CreateDBConnectionAndOpen(string connectionName = "DBConnection")
	 {
	 SqlConnection connection = CreateDBConnection(connectionName);
	 if (connection.State != ConnectionState.Open)
	 {
	 connection.Open();
	 }
	 return connection;
	 }
	
	 /// <summary>
	 /// 创建Command对象
	 /// </summary>
	 /// <param name="connection"></param>
	 /// <param name="cmdText"></param>
	 /// <returns></returns>
	 public static IDbCommand CreateDBCommand(SqlConnection connection,
	 string cmdText,
	 SqlParameter[] parameters)
	 {
	 IDbCommand command = new SqlCommand(cmdText, connection);
	 for (int i = 0; parameters != null && i < parameters.Length; i++)
	 {
	 command.Parameters.Add(parameters[i]);
	 }
	 command.CommandTimeout = Timeout;
	 return command;
	 }
	
	 /// <summary>
	 /// 创建Command对象
	 /// </summary>
	 /// <param name="connection">数据库连接对象</param>
	 /// <param name="cmdText">要执行的SQL</param>
	 /// <param name="parameters">参数</param>
	 /// <returns></returns>
	 public static IDbCommand CreateDBCommand(IDbConnection connection,
	 string cmdText,
	 IEnumerable parameters)
	 {
	 IDbCommand command = new SqlCommand(cmdText);
	 command.Connection = connection;
	 if (null != parameters)
	 {
	 if (parameters is Hashtable)
	 {
	 foreach (DictionaryEntry item in parameters)
	 {
	 SqlParameter parameter = new SqlParameter(item.Key.ToString(), item.Value);
	 command.Parameters.Add(parameter);
	 }
	 }
	 else if (parameters is Dictionary<string, object>)
	 {
	 foreach (KeyValuePair<string, object> item in parameters)
	 {
	 SqlParameter parameter = new SqlParameter(item.Key, item.Value);
	 command.Parameters.Add(parameter);
	 }
	 }
	 else if (parameters is SqlParameter[])
	 {
	 foreach (SqlParameter item in parameters)
	 {
	 command.Parameters.Add(item);
	 }
	 }
	 }
	 command.CommandTimeout = Timeout;
	 return command;
	 }
	
	 /// <summary>
	 /// 关闭数据库连接
	 /// </summary>
	 /// <param name="connection"></param>
	 public static void CloseDataBase(IDbConnection connection)
	 {
	 if (null == connection) { return; }
	 connection.Close();
	 connection.Dispose();
	 }
	 /// <summary>
	 /// 关闭读取器
	 /// </summary>
	 /// <param name="dataReader"></param>
	 public static void CloseDataReader(IDataReader dataReader)
	 {
	 if (null == dataReader) { return; }
	 dataReader.Close();
	 dataReader.Dispose();
	 }
	 #endregion
	
	 #region 执行更新、添加、删除操作
	 /// <summary>
	 ///事务：执行SQL
	 /// </summary>
	 /// <param name="cmdText">SQL</param>
	 /// <param name="parameters">参数</param>
	 /// <param name="tran">事务对象</param>
	 public static int ExcuteNonQueryTran(string cmdText,
	 SqlConnection connection,
	 SqlTransaction tran,
	 IEnumerable parameters)
	 {
	 //创建Command对象
	 IDbCommand command = DBHelper.CreateDBCommand(connection,
	 cmdText,
	 parameters);
	 command.Transaction = tran;
	 //执行Command对象
	 int iResult = command.ExecuteNonQuery();
	 command.Dispose();
	 return iResult;
	 }
	
	 /// <summary>
	 /// 执行事务
	 /// </summary>
	 /// <param name="command">Command对象</param>
	 /// <param name="tran">事务</param>
	 /// <returns></returns>
	 public static int ExcuteNonQueryTran(IDbCommand command,
	 IDbTransaction tran)
	 {
	 int iResult = 0;
	 command.Transaction = tran;
	 iResult = command.ExecuteNonQuery();
	 return iResult;
	 }
	
	 /// <summary>
	 /// 事务：执行SQL，返回受影响的记录数
	 /// </summary>
	 /// <param name="cmdText">SQL</param>
	 /// <param name="tran">事务对象</param>
	 /// <param name="parameters">参数</param>
	 /// <returns></returns>
	 public static int ExcuteNonQueryTran(string cmdText,
	 IDbTransaction tran,
	 IEnumerable parameters)
	 {
	 IDbCommand command = DBHelper.CreateDBCommand(tran.Connection,
	 cmdText,
	 parameters);
	 command.Transaction = tran;
	 int iResult = command.ExecuteNonQuery();
	 return iResult;
	 }
	
	 /// <summary>
	 /// 事务：执行SQL，返回首行首列
	 /// </summary>
	 /// <param name="cmdTex">SQL</param>
	 /// <param name="tran">事务</param>
	 /// <param name="parameters">参数</param>
	 /// <returns></returns>
	 public static object ExcuteScalarTran(string cmdText,
	 IDbTransaction tran,
	 IEnumerable parameters)
	 {
	 IDbCommand command = DBHelper.CreateDBCommand(tran.Connection,
	 cmdText,
	 parameters);
	 command.Transaction = tran;
	 object obj = command.ExecuteScalar();
	 return obj;
	 }
	
	 /// <summary>
	 /// 执行SQL语句，返回受影响的记录数
	 /// </summary>
	 /// <param name="cmdText">要执行的SQL</param>
	 /// <param name="parameters">参数</param>
	 /// <returns></returns>
	 public static int ExcuteNonQuery(string cmdText,
	 IEnumerable parameters,
	 string connectionName = null)
	 {
	 int iResult = 0;
	 connectionName = connectionName ?? DBCONNECTION_APPSETTING_KEY;
	 //创建链接
	 SqlConnection connection = CreateDBConnection(connectionName);
	 try
	 {
	 if (connection.State != ConnectionState.Open)
	 {
	 connection.Open();
	 }
	 IDbCommand command = CreateDBCommand(connection, cmdText, parameters);
	 iResult = command.ExecuteNonQuery();
	 command.Dispose();
	 }
	 catch (SqlException ex) { throw ex; }
	 catch (Exception ex) { throw ex; }
	 finally
	 {
	 CloseDataBase(connection);//关闭连接
	 }
	 return iResult;
	 }
	 #endregion
	
	 #region 查询
	 /// <summary>
	 /// 指针：读取数据
	 /// </summary>
	 /// <param name="cmdText">SQL脚本</param>
	 /// <param name="parameters">参数，支持：hashtable dictionary<string,object> sqlparameter[]</param>
	 /// <param name="connectionName"></param>
	 /// <returns></returns>
	 public static IDataReader ExcuteDataReader(string cmdText,
	 IEnumerable parameters,
	 string connectionName = null)
	 {
	 connectionName = connectionName ?? DBCONNECTION_APPSETTING_KEY;
	 IDataReader dataReader = null;
	 //创建Connection对象
	 SqlConnection connect = CreateDBConnection(connectionName);
	 try
	 {
	 //创建Command对象
	 IDbCommand command = CreateDBCommand(connect, cmdText, parameters);
	 command.CommandType = CommandType.Text;
	 if (connect.State != ConnectionState.Open)
	 {
	 connect.Open();
	 }
	 //执行SQL
	 dataReader = command.ExecuteReader();
	 }
	 catch { CloseDataReader(dataReader); CloseDataBase(connect); throw; }
	
	 return dataReader;
	 }
	
	
	 /// <summary>
	 /// 获取首行首列
	 /// </summary>
	 /// <param name="cmdText">要执行的SQL</param>
	 /// <param name="parameters">参数</param>
	 /// <param name="connectionName">数据库连接key</param>
	 /// <returns></returns>
	 public static object GetSingleValue(string cmdText,
	 SqlParameter[] parameters,
	 string connectionName = null)
	 {
	 object obj = null;
	 connectionName = connectionName ?? DBCONNECTION_APPSETTING_KEY;
	 SqlConnection connection = CreateDBConnection(connectionName);
	 try
	 {
	 //打开数据库链接
	 if (connection.State != ConnectionState.Open)
	 {
	 connection.Open();
	 }
	 IDbCommand command = CreateDBCommand(connection, cmdText, parameters);
	 obj = command.ExecuteScalar();
	 command.Parameters.Clear();
	 command.Dispose();
	 }
	 catch (SqlException ex)
	 {
	 throw ex;
	 }
	 catch (Exception ex)
	 {
	 throw ex;
	 }
	 finally
	 {
	 CloseDataBase(connection);//关闭数据库连接
	 }
	 return obj;
	 }
	
	 /// <summary>
	 /// 获取首行首列
	 /// </summary>
	 /// <param name="cmdText">要执行的SQL</param>
	 /// <param name="parameters">参数</param>
	 /// <param name="connnectionName">连接名称</param>
	 /// <returns></returns>
	 public static object GetSingleValue(string cmdText,
	 IEnumerable parameters,
	 string connnectionName = null)
	 {
	 connnectionName = connnectionName ?? DBCONNECTION_APPSETTING_KEY;
	 SqlParameter[] arrParameters = ToSqlParametersCollection(parameters);
	 return GetSingleValue(cmdText, arrParameters, connnectionName);
	 }
	
	 /// <summary>
	 /// 事务：获取首行首列
	 /// </summary>
	 /// <param name="cmdText">要执行SQL</param>
	 /// <param name="parameters">参数</param>
	 /// <param name="connection">连接对象</param>
	 /// <param name="tran">事务</param>
	 /// <returns></returns>
	 public static object ExcuteScalar(string cmdText,
	 SqlParameter[] parameters,
	 SqlConnection connection,
	 SqlTransaction tran)
	 {
	 object obj = null;
	 //打开数据库链接
	 if (connection.State != ConnectionState.Open)
	 {
	 connection.Open();
	 }
	 IDbCommand command = CreateDBCommand(connection,
	 cmdText,
	 parameters);
	 command.Transaction = tran;
	 obj = command.ExecuteScalar();
	 command.Dispose();
	 return obj;
	 }
	
	
	
	 /// <summary>
	 /// 返回首航首列
	 /// </summary>
	 /// <param name="cmdText">SQL</param>
	 /// <param name="parameter">参数</param>
	 /// <returns></returns>
	 public static object ExcuteScalar(string cmdText,
	 IEnumerable parameter)
	 {
	 object obj = null;
	 SqlConnection connection = null;
	 try
	 {
	 connection = CreateDBConnectionAndOpen(DBCONNECTION_APPSETTING_KEY);
	 IDbCommand command = CreateDBCommand(connection,
	 cmdText,
	 parameter);
	 obj = command.ExecuteScalar();
	 command.Parameters.Clear();
	 command.Dispose();
	 }
	 catch (SqlException ex) { throw ex; }
	 catch (Exception ex) { throw ex; }
	 finally
	 {
	 CloseDataBase(connection);//关闭数据库连接
	 }
	 return obj;
	 }
	
	 /// <summary>
	 /// 事务：获取首行首列
	 /// </summary>
	 /// <param name="cmdText">要执行的SQL</param>
	 /// <param name="parameters">参数</param>
	 /// <param name="connection">连接对象</param>
	 /// <param name="tran">事务</param>
	 /// <returns></returns>
	 public static object ExcuteScalar(string cmdText,
	 IEnumerable parameters,
	 SqlConnection connection,
	 SqlTransaction tran)
	 {
	 SqlParameter[] arrSqlparameters = ToSqlParametersCollection(parameters);
	 return ExcuteScalar(cmdText,
	 arrSqlparameters,
	 connection,
	 tran);
	 }
	
	
	 /// <summary>
	 /// 事务：获取记录数
	 /// </summary>
	 /// <param name="cmdText">执行的SQL</param>
	 /// <param name="parameters">参数</param>
	 /// <param name="connection">连接对象</param>
	 /// <param name="tran">事务</param>
	 /// <returns></returns>
	 public static int Count(string where,
	 string tbName,
	 IEnumerable parameters,
	 IDbConnection connection,
	 IDbTransaction tran)
	 {
	 string strCmdText = "SELECT COUNT(1) FROM " + tbName + " WHERE " + where;
	 IDbCommand command = CreateDBCommand(connection,
	 strCmdText,
	 parameters);
	 command.Transaction = tran;
	 object obj = command.ExecuteScalar();
	 return Convert.ToInt32(obj);
	 }
	
	 /// <summary>
	 /// 获取记录数
	 /// </summary>
	 /// <param name="where">条件不带Where</param>
	 /// <param name="tbName">表名</param>
	 /// <param name="parameters">参数</param>
	 /// <returns></returns>
	 public static int Count(string where,
	 string tbName,
	 IEnumerable parameters,
	 string connectionKey = null)
	 {
	 connectionKey = connectionKey ?? DBCONNECTION_APPSETTING_KEY;
	 string strCmdText = "SELECT COUNT(1) FROM " + tbName + " WHERE 1=1 ";
	 if (!string.IsNullOrEmpty(where))
	 {
	 strCmdText += where;
	 }
	 object obj = null;
	 SqlConnection connection = null;
	 try
	 {
	 connection = CreateDBConnectionAndOpen(connectionKey);
	
	 IDbCommand command = CreateDBCommand(connection,
	 strCmdText,
	 parameters);
	 obj = command.ExecuteScalar();
	 command.Parameters.Clear();
	 command.Dispose();
	
	 }
	 catch (SqlException ex)
	 {
	 Debug.Assert(false, ex.Message);
	 throw ex;
	 }
	 catch (Exception ex)
	 {
	 Debug.Assert(false, ex.Message);
	 throw ex;
	 }
	 finally
	 {
	 CloseDataBase(connection);
	 }
	 return Convert.ToInt32(obj);
	 }
	
	 /// <summary>
	 /// 执行查询，返回DataSet
	 /// </summary>
	 /// <param name="cmdText">脚本</param>
	 /// <param name="parameters">参数,hashtable或Dictionary<string,object></param>
	 /// <param name="connectionName">数据库连接名称</param>
	 /// <returns></returns>
	 public static DataSet Query(string cmdText,
	 IEnumerable parameters,
	 string connectionName = "DBConnection")
	 {
	 SqlParameter[] arrSqlParameters = ToSqlParametersCollection(parameters);
	 DataSet ds = new DataSet();
	 SqlConnection connection = CreateDBConnection(connectionName);
	 try
	 {
	 if (connection.State != ConnectionState.Open)
	 {
	 connection.Open();
	 }
	 SqlCommand command = new SqlCommand(cmdText, connection);
	 if (parameters != null && arrSqlParameters.Length > 0)
	 {
	 command.Parameters.AddRange(arrSqlParameters);
	 }
	 SqlDataAdapter adapter = new SqlDataAdapter(command);
	 adapter.Fill(ds, "ds");
	 command.Dispose();
	 adapter.Dispose();
	 }
	 catch (SqlException ex) { throw ex; }
	 catch (Exception ex) { throw ex; }
	 finally
	 {
	 CloseDataBase(connection);
	 }
	 return ds;
	 }
	 /// <summary>
	 /// 执行SQL，返回数据集，输出参数
	 /// </summary>
	 /// <param name="strCmdText"></param>
	 /// <param name="parameter"></param>
	 /// <param name="output"></param>
	 /// <returns></returns>
	 public static DataSet Query(string cmdText,
	 IEnumerable parameters,
	 ref Dictionary<string, object> output)
	 {
	 SqlParameter[] arrSqlParameters = ToSqlParametersCollection(parameters);
	 DataSet ds = new DataSet();
	 SqlConnection connection = CreateDBConnection(DBCONNECTION_APPSETTING_KEY);
	 try
	 {
	 if (connection.State != ConnectionState.Open)
	 {
	 connection.Open();
	 }
	 SqlCommand command = new SqlCommand(cmdText, connection);
	 if (parameters != null && arrSqlParameters.Length > 0)
	 {
	 command.Parameters.AddRange(arrSqlParameters);
	 }
	 //输出参数
	 Dictionary<string, object>.Enumerator Enumerator = output.GetEnumerator();
	 while (Enumerator.MoveNext())
	 {
	 SqlParameter parameter = new SqlParameter(Enumerator.Current.Key, Enumerator.Current.Value);
	 parameter.Direction = ParameterDirection.Output;
	 command.Parameters.Add(parameter);
	 }
	
	 SqlDataAdapter adapter = new SqlDataAdapter(command);
	 adapter.Fill(ds, "ds");
	 command.Dispose();
	 adapter.Dispose();
	 Dictionary<string, object> dictOut = new Dictionary<string, object>();
	 foreach (string key in output.Keys)
	 {
	 dictOut[key] = command.Parameters[key].Value;
	 }
	 output = dictOut;
	 }
	 catch (SqlException ex) { throw ex; }
	 catch (Exception ex) { throw ex; }
	 finally
	 {
	 CloseDataBase(connection);
	 }
	
	 return ds;
	 }
	 #endregion
	
	 #region 存储过程
	 /// <summary>
	 /// 执行过程，返回受影响的记录数
	 /// </summary>
	 /// <param name="procedureName">过程名</param>
	 /// <param name="parameters">参数</param>
	 /// <param name="connectionName">链接名</param>
	 /// <returns></returns>
	 public static int ExcuteNonQueryPro(string procedureName,
	 IEnumerable parameters,
	 string connectionName)
	 {
	 int iResult = 0;
	 SqlConnection connection = null;
	 IDbCommand command = null;
	 try
	 {
	 //创建并打开数据库链接
	 connection = CreateDBConnectionAndOpen(connectionName);
	 command = CreateDBCommand(connection,
	 procedureName,
	 parameters);
	 command.CommandType = CommandType.StoredProcedure;
	 //执行
	 iResult = command.ExecuteNonQuery();
	 }
	 catch (SqlException ex) { throw ex; }
	 catch (Exception ex) { throw ex; }
	 finally
	 {
	 command.Dispose();
	 connection.Close();
	 connection.Dispose();
	 }
	 return iResult;
	 }
	 /// <summary>
	 /// 过程：事务执行增删改
	 /// </summary>
	 /// <param name="procedureName">过程名</param>
	 /// <param name="parameters">参数</param>
	 /// <param name="connection">链接对象</param>
	 /// <param name="tran">事务</param>
	 /// <returns></returns>
	 public static int ExcuteNonQueryProTran(string procedureName,
	 IEnumerable parameters,
	 SqlConnection connection,
	 SqlTransaction tran)
	 {
	 int iResult = 0;
	 IDbCommand command = null;
	 try
	 {
	 command = CreateDBCommand(connection, procedureName, parameters);
	 command.CommandType = CommandType.StoredProcedure;
	 iResult = command.ExecuteNonQuery();
	 }
	 catch (SqlException ex) { throw ex; }
	 catch (Exception ex) { throw ex; }
	 return iResult;
	 }
	
	 /// <summary>
	 /// 过程：执行过程，返回DataSet对象
	 /// </summary>
	 /// <param name="procedureName">过程名</param>
	 /// <param name="parameters">参数</param>
	 /// <param name="connectionName">连接名</param>
	 /// <returns></returns>
	 public static DataSet ExcuteDataSetPro(string procedureName,
	 IEnumerable parameters,
	 string connectionName,
	 string tablename = "0")
	 {
	 DataSet ds = null;
	 SqlConnection connection = null;
	 try
	 {
	 //创建数据库链接
	 connection = CreateDBConnectionAndOpen(connectionName);
	 SqlDataAdapter dataAdapter = new SqlDataAdapter(procedureName, connection);
	 dataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
	 //添加参数
	 dataAdapter.SelectCommand.Parameters.AddRange(ToSqlParametersCollection(parameters));
	 ds = new DataSet();
	 dataAdapter.Fill(ds, tablename);
	 dataAdapter.Dispose();
	 }
	 catch (SqlException ex) { throw ex; }
	 catch (Exception ex) { throw ex; }
	 finally
	 {
	 CloseDataBase(connection);
	 }
	 return ds;
	 }
	
	 /// <summary>
	 /// 过程：读取器
	 /// </summary>
	 /// <param name="procedureName">过程名</param>
	 /// <param name="parameters">参数</param>
	 /// <param name="connectionName">链接名</param>
	 /// <returns></returns>
	 public static IDataReader ExcuteDataReaderPro(string procedureName,
	 IEnumerable parameters,
	 SqlConnection connection)
	 {
	 //创建Command对象
	 IDbCommand command = CreateDBCommand(connection,
	 procedureName,
	 parameters);
	 command.CommandType = CommandType.StoredProcedure;
	 IDataReader dataReader = command.ExecuteReader();
	 return dataReader;
	 }
	
	
	 /// <summary>
	 /// 导入数据
	 /// </summary>
	 /// <param name="connectionName">链接名</param>
	 /// <param name="data">数据源</param>
	 /// <param name="dtTarget">目标</param>
	 /// <param name="DestinationTableName">目标（表名）</param>
	 /// <returns></returns>
	 public static void Import(string connectionName,
	 DataTable data,
	 DataTable dtTarget,
	 string DestinationTableName)
	 {
	 SqlConnection connection = CreateDBConnectionAndOpen(connectionName);
	 //开始事务
	 SqlTransaction tran = connection.BeginTransaction();
	 SqlBulkCopy blk = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, tran);
	 try
	 {
	 blk.BatchSize = 1;
	 blk.BulkCopyTimeout = Timeout;
	 blk.DestinationTableName = DestinationTableName;
	 //按照顺序进行映射
	 for (int i = 0; i < dtTarget.Columns.Count; i++)
	 {
	 //映射
	 blk.ColumnMappings.Add(dtTarget.Columns[i].ColumnName, data.Columns[i].ColumnName);
	 }
	 blk.WriteToServer(data);
	 tran.Commit();//提交事务
	 }
	 catch (SqlException ex) { tran.Rollback(); throw ex; }
	 catch (Exception ex) { tran.Rollback(); throw ex; }
	 finally
	 {
	 blk.Close();
	 tran.Dispose();
	 CloseDataBase(connection);
	 }
	 }
	
	
	 /// <summary>
	 /// 更新数据集
	 /// </summary>
	 /// <param name="strCmdText">查询语句</param>
	 /// <param name="ds">数据集</param>
	 /// <returns></returns>
	 public static int UpdateDataSet(string strCmdText,
	 DataSet ds,
	 Action<DataSet> action)
	 {
	 SqlConnection connection = CreateDBConnection();
	 int iResult = 0;
	 try
	 {
	 if (connection.State != ConnectionState.Open)
	 {
	 connection.Open();
	 }
	 SqlDataAdapter adapter = new SqlDataAdapter(strCmdText, connection);
	 SqlCommandBuilder cmd = new SqlCommandBuilder(adapter);
	 cmd.DataAdapter = adapter;
	 adapter.Fill(ds);
	 action(ds);//添加数据
	 iResult = adapter.Update(ds);
	 adapter.Dispose();
	 }
	 catch (SqlException ex) { throw ex; }
	 catch (Exception ex) { throw ex; }
	 finally
	 {
	 CloseDataBase(connection);
	 }
	 return iResult;
	 }
	 #endregion
	
	 #region 私有成员
	 /// <summary>
	 /// 把Hashtable转换为SqlParameter[]
	 /// </summary>
	 /// <param name="hashParameters">参数：目前支持的类型有SqlParameter、Hashtable、Dictionary<string,object></param>
	 /// <returns></returns>
	 private static SqlParameter[] ToSqlParametersCollection(IEnumerable Parameters)
	 {
	 List<SqlParameter> listParameters = new List<SqlParameter>();
	 if (Parameters is Hashtable)
	 {
	 foreach (DictionaryEntry item in Parameters)
	 {
	 listParameters.Add(new SqlParameter(item.Key.ToString(), item.Value));
	 }
	 return listParameters.ToArray();
	 }
	 if (Parameters is Dictionary<string, object>)
	 {
	 foreach (KeyValuePair<string, object> item in Parameters)
	 {
	 listParameters.Add(new SqlParameter(item.Key, item.Value));
	 }
	 return listParameters.ToArray();
	 }
	 return listParameters.ToArray();
	 }
	 #endregion
	 }
	}
评论列表
本站所提供的代码，版权归原作者所有，若有侵犯作者版权，请与我们联系，我们将立即删除或修改。谢谢!
本站所有代码发布及提供者。
试试其它关键字
同语言下
可能有用的
李朝强贡献的其它代码(10)