封装好的ORM

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Linq;
  4 using System.Web;
  5 
  6 using System.Data;
  7 using System.Data.Common;
  8 using System.Data.SqlClient;
  9 using System.Diagnostics;
 10 
 11 namespace WebApplication.DAL.Provider
 12 {
 13     static class DbExtension
 14     {
 15         public static DbParameter AddWithValue(this DbParameterCollection parameters, string parameterName, string value)
 16         {
 17             return (parameters as SqlParameterCollection).AddWithValue(parameterName, value);
 18         }
 19     }
 20 
 21     class SqlProvider : SqlProviderBase<DbConnection, DbCommand, DbParameter, DbDataReader, DbDataAdapter>, IProvider.ISqlProvider
 22     {
 23         public new string Name { get { return "Sql provider"; } }
 24         public new string Description { get { return "Sql provider class"; } }
 25 
 26         protected override DbConnection DbConnection { get; set; }
 27 
 28         protected override DbCommand DbSelectCommand { get; set; }
 29         protected override DbCommand DbInsertCommand { get; set; }
 30         protected override DbCommand DbUpdateCommand { get; set; }
 31         protected override DbCommand DbDeleteCommand { get; set; }
 32 
 33         protected override DbDataReader DbDataReader { get; set; }
 34         protected override DbDataAdapter DbAdapter { get; set; }
 35 
 36         public override string ConnectionString { get; set; }
 37 
 38         public SqlProvider()
 39         {
 40             ConnectionStringSetting = "BusinessServices";
 41         }
 42         protected override DbConnection CreateDatabaseProvide()
 43         {
 44             //定义连接对象
 45             DbConnection = new SqlConnection(ConnectionString);
 46 
 47             //定义命令主机
 48             DbSelectCommand = new SqlCommand("", DbConnection as SqlConnection);
 49             DbInsertCommand = new SqlCommand("", DbConnection as SqlConnection);
 50             DbUpdateCommand = new SqlCommand("", DbConnection as SqlConnection);
 51             DbDeleteCommand = new SqlCommand("", DbConnection as SqlConnection);
 52 
 53             //将命令主机加入数据适配器
 54             DbAdapter = new SqlDataAdapter(DbSelectCommand as SqlCommand);
 55             DbAdapter.InsertCommand = DbInsertCommand;
 56             DbAdapter.UpdateCommand = DbUpdateCommand;
 57             DbAdapter.DeleteCommand = DbDeleteCommand;
 58 
 59             return DbConnection;
 60         }
 61 
 62         protected override DbParameter CreateDbParameter(string parameterName, object value)
 63         {
 64             return new SqlParameter(parameterName, value);
 65         }
 66 
 67         protected override void SetParameters(DbCommand command, IDictionary<string, object> parameters)
 68         {
 69             command.Parameters.Clear();
 70             if (parameters == null) return;
 71             foreach (KeyValuePair<string, object> Parameter in parameters)
 72                 (command as SqlCommand).Parameters.AddWithValue(Parameter.Key, NullToDBNull(Parameter.Value));
 73         }
 74 
 75         //执行
 76         public virtual int ExecuteNonQuery(string sql)
 77         {
 78             int Result;
 79             using (DbConnection DBConnection = CreateDatabaseProvide())
 80             {
 81                 //CreateDatabaseProvide(DBConnection);
 82                 DBConnection.Open();
 83 
 84                 DbSelectCommand.CommandText = sql;
 85                 DbSelectCommand.CommandType = System.Data.CommandType.Text;
 86 
 87                 //SetParameters(DBCommonCommand, parametersInstance);
 88                 try
 89                 {
 90                     Result = DbSelectCommand.ExecuteNonQuery();
 91                 }
 92                 finally
 93                 {
 94                     if (DBConnection.State == System.Data.ConnectionState.Open) DBConnection.Close();
 95                     DbSelectCommand.Dispose();
 96                 }
 97             }
 98             return Result;
 99         }
100         //执行存储过程
101         public virtual int ExecuteNonQueryByProcedure(System.Collections.Generic.Dictionary<string, object> parametersInstance, string storedProcedureName)
102         {
103             int Result;
104             using (DbConnection DBConnection = CreateDatabaseProvide())
105             {
106                 DBConnection.Open();
107 
108                 DbSelectCommand.CommandText = storedProcedureName;
109                 DbSelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
110 
111                 SetParameters(DbSelectCommand, parametersInstance);
112                 if (DbSelectCommand.Parameters.Contains("@Errcode"))
113                     DbSelectCommand.Parameters["@Errcode"].Direction = ParameterDirection.Output;
114                 if (DbSelectCommand.Parameters.Contains("@ErrMng"))
115                 {
116                     DbSelectCommand.Parameters["@ErrMng"].Size = 500;
117                     DbSelectCommand.Parameters["@ErrMng"].Direction = ParameterDirection.Output;
118                 }
119                 try
120                 {
121                     Result = DbSelectCommand.ExecuteNonQuery();
122                     if (DbSelectCommand.Parameters.Contains("@ErrMng") && DbSelectCommand.Parameters["@ErrMng"].Value.ToString() != "")
123                     {
124                         throw new Exception(DbSelectCommand.Parameters["@ErrMng"].Value.ToString());
125                     }
126                 }
127                 finally
128                 {
129                     if (DBConnection.State == System.Data.ConnectionState.Open) DBConnection.Close();
130                     DbSelectCommand.Dispose();
131                 }
132             }
133             return Result;
134         }
135         //循环执行存储过程，添加了事务
136         public virtual bool ExecuteNonQueryByProcedureList(List<System.Collections.Generic.Dictionary<string, object>> parametersInstance, string storedProcedureName)
137         {
138             SqlConnection conn = new SqlConnection(ConnectionString);
139             conn.Open();
140 
141             using (SqlTransaction tran = conn.BeginTransaction())
142             {
143                 SqlCommand cmd = new SqlCommand(storedProcedureName, conn, tran);
144                 cmd.CommandType = System.Data.CommandType.StoredProcedure;
145                 try
146                 {
147                     foreach (System.Collections.Generic.Dictionary<string, object> ht in parametersInstance)
148                     {
149                         SetParameters(cmd, ht);
150                         cmd.ExecuteNonQuery();
151                     }
152                     tran.Commit();
153                 }
154                 catch (Exception e)
155                 {
156                     tran.Rollback();
157                     return false;
158                 }
159                 finally
160                 {
161                     if (conn.State == System.Data.ConnectionState.Open) conn.Close();
162                     cmd.Dispose();
163                 }
164             }
165             return true;
166         }
167         //执行存储过程返回Table
168         public virtual System.Data.DataTable ExecStoredProcedure(System.Collections.Generic.Dictionary<string, object> parametersInstance, string storedProcedureName)
169         {
170             System.Data.DataSet ds = new System.Data.DataSet();
171             using (DbConnection DBConnection = CreateDatabaseProvide())
172             {
173                 //CreateDatabaseProvide(DBConnection);
174                 DBConnection.Open();
175 
176                 DbSelectCommand.CommandText = storedProcedureName;
177                 DbSelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
178 
179                 //SqlDataAdapter dap = new SqlDataAdapter(DbSelectCommand);
180                 SetParameters(DbSelectCommand, parametersInstance);
181                 //DbSelectCommand.Parameters.Add(new SqlParameter("@Errcode", SqlDbType.Int));
182                 if (DbSelectCommand.Parameters.Contains("@Errcode"))
183                     DbSelectCommand.Parameters["@Errcode"].Direction = ParameterDirection.Output;
184                 if (DbSelectCommand.Parameters.Contains("@ErrMng"))
185                 {
186                     DbSelectCommand.Parameters["@ErrMng"].Size = 500;
187                     DbSelectCommand.Parameters["@ErrMng"].Direction = ParameterDirection.Output;
188                 }
189                 try
190                 {
191                     DbAdapter.Fill(ds, "ReturnTable");
192                     if (DbSelectCommand.Parameters.Contains("@ErrMng") && DbSelectCommand.Parameters["@ErrMng"].Value.ToString() != "")
193                     {
194                         throw new Exception(DbSelectCommand.Parameters["@ErrMng"].Value.ToString());
195                     }
196                 }
197                 finally
198                 {
199                     if (DBConnection.State == System.Data.ConnectionState.Open) DBConnection.Close();
200                     DbSelectCommand.Dispose();
201                 }
202             }
203             if (ds.Tables.Count == 0) return null;
204             return ds.Tables["ReturnTable"];
205         }
206         //执行存储过程返回Table
207         public virtual System.Data.DataSet ExecStoredProcedureDataSet(System.Collections.Generic.Dictionary<string, object> parametersInstance, string storedProcedureName)
208         {
209             System.Data.DataSet ds = new System.Data.DataSet();
210             using (DbConnection DBConnection = CreateDatabaseProvide())
211             {
212                 //CreateDatabaseProvide(DBConnection);
213                 DBConnection.Open();
214 
215                 DbSelectCommand.CommandText = storedProcedureName;
216                 DbSelectCommand.CommandType = System.Data.CommandType.StoredProcedure;
217 
218                 //SqlDataAdapter dap = new SqlDataAdapter(DbSelectCommand);
219                 SetParameters(DbSelectCommand, parametersInstance);
220                 //DbSelectCommand.Parameters.Add(new SqlParameter("@Errcode", SqlDbType.Int));
221                 if (DbSelectCommand.Parameters.Contains("@Errcode"))
222                     DbSelectCommand.Parameters["@Errcode"].Direction = ParameterDirection.Output;
223                 if (DbSelectCommand.Parameters.Contains("@ErrMng"))
224                 {
225                     DbSelectCommand.Parameters["@ErrMng"].Size = 500;
226                     DbSelectCommand.Parameters["@ErrMng"].Direction = ParameterDirection.Output;
227                 }
228                 try
229                 {
230                     DbAdapter.Fill(ds, "ReturnTable");
231                     if (DbSelectCommand.Parameters.Contains("@ErrMng") && DbSelectCommand.Parameters["@ErrMng"].Value.ToString() != "")
232                     {
233                         throw new Exception(DbSelectCommand.Parameters["@ErrMng"].Value.ToString());
234                     }
235                 }
236                 finally
237                 {
238                     if (DBConnection.State == System.Data.ConnectionState.Open) DBConnection.Close();
239                     DbSelectCommand.Dispose();
240                 }
241             }
242             if (ds.Tables.Count == 0) return null;
243             return ds;
244         }
245         //执行SQL语句返回Table
246         public virtual System.Data.DataTable ExecByText(string sqlText)
247         {
248             System.Data.DataSet ds = new System.Data.DataSet();
249 
250             using (DbConnection DBConnection = CreateDatabaseProvide())
251             {
252                 DBConnection.Open();
253 
254                 DbSelectCommand.CommandText = sqlText;
255                 DbSelectCommand.CommandTimeout = 0;
256                 DbSelectCommand.CommandType = System.Data.CommandType.Text;
257 
258                 //SqlDataAdapter dap = new SqlDataAdapter(DbSelectCommand);
259                 //SetParameters(DbSelectCommand, parametersInstance);
260                 try
261                 {
262                     DbAdapter.Fill(ds, "ReturnTable");
263                 }
264                 finally
265                 {
266                     if (DBConnection.State == System.Data.ConnectionState.Open) DBConnection.Close();
267                     DbSelectCommand.Dispose();
268                 }
269             }
270             if (ds.Tables.Count == 0) return null;
271             return ds.Tables["ReturnTable"];
272         }
273         //调用表值函数
274         public virtual System.Data.DataTable RowsetFunction(System.Collections.Generic.Dictionary<string, object> parametersInstance, string cmdText)
275         {
276             System.Data.DataTable DataTableInstance = new System.Data.DataTable("ReturnTable");
277             using (DbConnection DBConnection = CreateDatabaseProvide())
278             {
279                 DBConnection.Open();
280 
281                 DbSelectCommand.CommandText = cmdText;
282                 DbSelectCommand.CommandType = System.Data.CommandType.Text;
283                 SetParameters(DbSelectCommand, parametersInstance);
284                 try
285                 {
286                     DbAdapter.Fill(DataTableInstance);
287                 }
288                 finally
289                 {
290                     if (DBConnection.State == System.Data.ConnectionState.Open) DBConnection.Close();
291                     DbSelectCommand.Dispose();
292                 }
293             }
294             return DataTableInstance;
295         }
296         //调用标量函数
297         public virtual object AggregateFunction(System.Collections.Generic.Dictionary<string, object> parametersInstance, string cmdText)
298         {
299             object ReturnResult;
300             object ReturnValue;
301 
302             using (DbConnection DBConnection = CreateDatabaseProvide())
303             {
304                 //CreateDatabaseProvide(DBConnection);
305                 DBConnection.Open();
306 
307                 DbSelectCommand.CommandText = cmdText;
308                 DbSelectCommand.CommandType = System.Data.CommandType.Text;
309 
310                 SetParameters(DbSelectCommand, parametersInstance);
311                 try
312                 {
313                     ReturnValue = DbSelectCommand.ExecuteScalar();
314                 }
315                 finally
316                 {
317                     if (DBConnection.State == System.Data.ConnectionState.Open) DBConnection.Close();
318                     DbSelectCommand.Dispose();
319                 }
320             }
321             ReturnResult = ReturnValue.ToString();
322             if (ReturnValue == DBNull.Value)
323                 ReturnResult = "";
324 
325             return ReturnResult;
326         }
327         /// <summary>
328         /// 批量插入数据(适用范围为DataTable列和数据表字段一一对应)
329         /// </summary>
330         /// <param name="tableName">数据库表名</param>
331         /// <param name="dtSource">数据源</param>
332         /// <returns>运行时间</returns>
333         public long SqlBulkCopyInsert(string tableName, DataTable dtSource)
334         {
335             Stopwatch stopwatch = new Stopwatch();
336             stopwatch.Start();
337 
338             using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(ConnectionString))
339             {
340                 sqlBulkCopy.DestinationTableName = tableName;
341                 sqlBulkCopy.BatchSize = dtSource.Rows.Count;
342                 try
343                 {
344                     if (dtSource != null && dtSource.Rows.Count != 0)
345                     {
346                         sqlBulkCopy.WriteToServer(dtSource);
347                     }
348                     sqlBulkCopy.Close();
349                     stopwatch.Stop();
350                     return stopwatch.ElapsedMilliseconds;
351                 }
352                 catch (Exception er)
353                 {
354                     sqlBulkCopy.Close();
355                     stopwatch.Stop();
356                     throw er;
357                 }
358             }
359         }
360         /// <summary>
361         /// 批量插入数据
362         /// </summary>
363         /// <param name="tableName">数据库表名</param>
364         /// <param name="dtSource">数据源</param>
365         /// <param name="ColumnsMapping">列映射。key为DataTable列名称；value为表字段</param>
366         /// <returns>运行时间</returns>
367         public long SqlBulkCopyInsert(string tableName, DataTable dtSource, Dictionary<string, string> ColumnsMapping)
368         {
369             if (dtSource == null && dtSource.Rows.Count == 0) throw new Exception("数据源为null或者记录为空");
370             Stopwatch stopwatch = new Stopwatch();
371             stopwatch.Start();
372 
373             using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(ConnectionString))
374             {
375                 sqlBulkCopy.DestinationTableName = tableName;
376                 sqlBulkCopy.BatchSize = dtSource.Rows.Count;
377 
378                 foreach (KeyValuePair<string, string> keyValue in ColumnsMapping)
379                 {
380                     sqlBulkCopy.ColumnMappings.Add(keyValue.Key, keyValue.Value);
381                 }
382                 try
383                 {
384                     sqlBulkCopy.WriteToServer(dtSource);
385                     sqlBulkCopy.Close();
386                     stopwatch.Stop();
387                     return stopwatch.ElapsedMilliseconds;
388                 }
389                 catch (Exception er)
390                 {
391                     sqlBulkCopy.Close();
392                     stopwatch.Stop();
393                     throw er;
394                 }
395             }
396         }
397     }
398 }