C# ado.net sqlhelper

  1     /// <summary>
  2     /// ado.net方式执行sql语句
  3     /// </summary>
  4     public class SqlHelper
  5     {
  6         /// <summary>
  7         /// 执行sql语句，进行查询操作（推荐）
  8         /// </summary>
  9         /// <param name="sqlStr">sql语句</param>
 10         /// <param name="parm">参集合数</param>
 11         /// <returns>dataset结果集</returns>
 12         public static DataSet ExcuteSqlDataSet(string sqlStr, string conStr, params SqlParameter[] param)
 13         {
 14             try
 15             {
 16                 conStr = string.IsNullOrWhiteSpace(conStr) ? Connection.SqlConnetionStr() : conStr;
 17                 using (SqlConnection conn = new SqlConnection(conStr))
 18                 {
 19                     conn.Open();
 20                     using (SqlCommand cmd = conn.CreateCommand())
 21                     {
 22                         cmd.CommandText = sqlStr;
 23                         if (param != null)
 24                         {
 25                             foreach (var item in param)
 26                             {
 27                                 if (!string.IsNullOrWhiteSpace(item.Value.ToString_()))
 28                                 {
 29                                     cmd.Parameters.Add(item);
 30                                 }
 31                                 else
 32                                 {
 33                                     item.Value = DBNull.Value;
 34                                     cmd.Parameters.Add(item);
 35                                 }
 36                             }
 37                         }
 38                         //cmd.Parameters.AddRange(parm);
 39                         cmd.CommandTimeout = 180;
 40                         SqlDataAdapter adapter = new SqlDataAdapter(cmd);
 41                         DataSet dataset = new DataSet();
 42                         //cmd.Prepare();
 43                         adapter.Fill(dataset);
 44                         cmd.Parameters.Clear();
 45                         return dataset;
 46                     }
 47                 }
 48             }
 49             catch (Exception ex)
 50             {
 51                 throw ex;
 52             }
 53         }
 54 
 55         /// <summary>
 56         /// 执行sql语句，进行查询操作（推荐）
 57         /// </summary>
 58         /// <param name="sqlStr">sql语句</param>
 59         /// <param name="parm">参集合数</param>
 60         /// <returns>dataset结果集</returns>
 61         public static DataSet ExcuteSqlDataSet2(string sqlStr, string conStr, List<SqlParameter> param)
 62         {
 63             try
 64             {
 65                 conStr = string.IsNullOrWhiteSpace(conStr) ? Connection.SqlConnetionStr() : conStr;
 66                 using (SqlConnection conn = new SqlConnection(conStr))
 67                 {
 68                     conn.Open();
 69                     using (SqlCommand cmd = conn.CreateCommand())
 70                     {
 71                         cmd.CommandText = sqlStr;
 72                         if (param != null)
 73                         {
 74                             foreach (var item in param)
 75                             {
 76                                 if (!string.IsNullOrWhiteSpace(item.Value.ToString_()))
 77                                 {
 78                                     cmd.Parameters.Add(item);
 79                                 }
 80                                 else
 81                                 {
 82                                     item.Value = DBNull.Value;
 83                                     cmd.Parameters.Add(item);
 84                                 }
 85                             }
 86                         }
 87                         //cmd.Parameters.AddRange(parm);
 88                         cmd.CommandTimeout = 180;
 89                         SqlDataAdapter adapter = new SqlDataAdapter(cmd);
 90                         DataSet dataset = new DataSet();
 91                         //cmd.Prepare();
 92                         adapter.Fill(dataset);
 93                         cmd.Parameters.Clear();
 94                         return dataset;
 95                     }
 96                 }
 97             }
 98             catch (Exception ex)
 99             {
100                 throw ex;
101             }
102         }
103 
104         /// <summary>
105         /// 执行sql语句，执行增删改操作
106         /// </summary>
107         /// <param name="sql">sql语句</param>
108         /// <param name="param">参数集合</param>
109         /// <returns>受影响条数</returns>
110         public static int ExecuteNonQuery(string sql, string conStr, params SqlParameter[] param)
111         {
112             try
113             {
114                 conStr = string.IsNullOrWhiteSpace(conStr) ? Connection.SqlConnetionStr() : conStr;
115                 int n = -1;
116                 using (SqlConnection con = new SqlConnection(conStr))
117                 {
118                     using (SqlCommand cmd = new SqlCommand(sql, con))
119                     {
120                         if (param != null)
121                         {
122                             foreach (var item in param)
123                             {
124                                 if (!string.IsNullOrWhiteSpace(item.Value.ToString_()))
125                                 {
126                                     cmd.Parameters.Add(item);
127                                 }
128                                 else
129                                 {
130                                     item.Value = DBNull.Value;
131                                     cmd.Parameters.Add(item);
132                                 }
133                             }
134                         }
135                         //cmd.Parameters.AddRange(param);
136                         con.Open();
137                         n = cmd.ExecuteNonQuery();
138                     }
139                 }
140                 return n;
141             }
142             catch (Exception ex)
143             {
144                 throw ex;
145             }
146         }
147 
148         /// <summary>
149         /// 执行sql语句，执行增删改操作
150         /// </summary>
151         /// <param name="sql">sql语句</param>
152         /// <param name="param">参数集合</param>
153         /// <returns>受影响条数</returns>
154         public static int ExecuteNonQuery2(string sql, string conStr, List<SqlParameter> param)
155         {
156             try
157             {
158                 conStr = string.IsNullOrWhiteSpace(conStr) ? Connection.SqlConnetionStr() : conStr;
159                 int n = -1;
160                 using (SqlConnection con = new SqlConnection(conStr))
161                 {
162                     using (SqlCommand cmd = new SqlCommand(sql, con))
163                     {
164                         if (param != null)
165                         {
166                             foreach (var item in param)
167                             {
168                                 if (!string.IsNullOrWhiteSpace(item.Value.ToString_()))
169                                 {
170                                     cmd.Parameters.Add(item);
171                                 }
172                                 else
173                                 {
174                                     item.Value = DBNull.Value;
175                                     cmd.Parameters.Add(item);
176                                 }
177                             }
178                         }
179                         //cmd.Parameters.AddRange(param);
180                         con.Open();
181                         n = cmd.ExecuteNonQuery();
182                     }
183                 }
184                 return n;
185             }
186             catch (Exception ex)
187             {
188                 throw ex;
189             }
190         }
191 
192         /// <summary>
193         /// SqlBulkCopy批量插入数据
194         /// </summary>
195         /// <param name="connectionStr">链接字符串</param>
196         /// <param name="dataTableName">表名</param>
197         /// <param name="sourceDataTable">数据源</param>
198         /// <param name="batchSize">一次事务插入的行数</param>
199         public static void BulkCopy(string connectionStr, string dataTableName, DataTable sourceDataTable, int batchSize = 100000)
200         {
201             connectionStr = string.IsNullOrWhiteSpace(connectionStr) ? Connection.SqlConnetionStr() : connectionStr;
202 
203             using (SqlConnection connection = new SqlConnection(connectionStr))
204             {
205                 using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connectionStr, SqlBulkCopyOptions.UseInternalTransaction))
206                 {
207                     try
208                     {
209                         sqlBulkCopy.DestinationTableName = dataTableName;
210                         sqlBulkCopy.BatchSize = batchSize;
211                         for (int i = 0; i < sourceDataTable.Columns.Count; i++)
212                         {
213                             sqlBulkCopy.ColumnMappings.Add(sourceDataTable.Columns[i].ColumnName, sourceDataTable.Columns[i].ColumnName);
214                         }
215                         sqlBulkCopy.WriteToServer(sourceDataTable);
216                     }
217                     catch (Exception ex)
218                     {
219                         throw ex;
220                     }
221                 }
222             }
223         }
224 
225         /// <summary>
226         /// 执行存储过程，返回DataSet
227         /// </summary>
228         /// <param name="storedProcedure"></param>
229         /// <param name="conStr"></param>
230         /// <param name="param"></param>
231         /// <returns></returns>
232         public static DataSet ExcuteStoredProcedure(string storedProcedure, string conStr, List<SqlParameter> param)
233         {
234             try
235             {
236                 conStr = string.IsNullOrWhiteSpace(conStr) ? Connection.SqlConnetionStr() : conStr;
237 
238                 using (SqlConnection conn = new SqlConnection(conStr))
239                 {
240                     SqlCommand cmd = conn.CreateCommand();
241                     cmd.CommandType = CommandType.StoredProcedure; //指定执行存储过程操作
242                     cmd.CommandText = storedProcedure; //存储过程名称 
243                     if (param != null)
244                     {
245                         foreach (var item in param)
246                         {
247                             if (!string.IsNullOrWhiteSpace(item.Value.ToString_()))
248                             {
249                                 cmd.Parameters.Add(item);
250                             }
251                             else
252                             {
253                                 item.Value = DBNull.Value;
254                                 cmd.Parameters.Add(item);
255                             }
256                         }
257                     }
258                     cmd.CommandTimeout = 180;
259                     //方式一，查询回来的结果需要显示在DataGrid之类的控件上
260                     DataSet ds = new DataSet();
261                     SqlDataAdapter adapter = new SqlDataAdapter(cmd);
262                     adapter.Fill(ds);
263                     return ds;
264                 }
265             }
266             catch (Exception ex)
267             {
268                 throw ex;
269             }
270         }
271     }
View Code
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/PrintY/p/14071169.html