C#：SqlHelper

虽然日常工作中都是调用别人写好的底层，但是要真正学到技术，还是要懂些底层原理，最好是能自己写底层

一、底层

注：引用命名空间

using System.Data;
using System.Data.SqlClient;

  1     public class SqlHelper
  2     {
  3         /// <summary>
  4         /// 数据库连接字符串
  5         /// Data Source=数据库地址;Initial Catalog=数据库名称;Persist Security Info=True;User ID=用户名;Password=密码
  6         /// </summary>
  7         private string _SqlConnectionStr = "";
  8         public string SqlConnectionStr { get { return _SqlConnectionStr; } }
  9         public SqlHelper(string connStr)
 10         {
 11             this._SqlConnectionStr = connStr;
 12         }
 13         #region 单值查询      
 14         public string GetSingle(string sqlStr)
 15         {
 16             using (SqlConnection conn = new SqlConnection(this._SqlConnectionStr))
 17             {
 18                 using (SqlCommand cmd = new SqlCommand(sqlStr, conn))
 19                 {
 20                     try
 21                     {
 22                         conn.Open();
 23                         return String.Format("{0}", cmd.ExecuteScalar());
 24                     }
 25                     catch(SqlException e)
 26                     {
 27                         throw e;
 28                     }
 29                     finally
 30                     {
 31                         conn.Close();
 32                     }
 33                 }
 34             }
 35         }
 36         public string GetSingle(string sqlStr,SqlParameter[] cmdParams)
 37         {
 38             using (SqlConnection conn = new SqlConnection(this._SqlConnectionStr))
 39             {
 40                 using (SqlCommand cmd = new SqlCommand())
 41                 {
 42                     try
 43                     {
 44                         conn.Open();
 45                         cmd.Connection = conn;
 46                         cmd.CommandType = CommandType.Text;
 47                         cmd.CommandText = sqlStr;
 48                         cmd.Parameters.AddRange(cmdParams);
 49                         return String.Format("{0}", cmd.ExecuteScalar());
 50                     }
 51                     catch(SqlException e)
 52                     {
 53                         throw e;
 54                     }
 55                     finally
 56                     {
 57                         conn.Close();
 58                     }
 59                 }
 60             }
 61         }
 62         #endregion
 63 
 64         #region 查询数据集        
 65         public DataSet Query(string sqlStr)
 66         {
 67             using (SqlConnection conn = new SqlConnection(this._SqlConnectionStr))
 68             {
 69                 using (SqlDataAdapter ada = new SqlDataAdapter(sqlStr, conn))
 70                 {
 71                     try
 72                     {
 73                         conn.Open();
 74                         DataSet ds = new DataSet();
 75                         ada.Fill(ds);
 76                         return ds;
 77                     }
 78                     catch(SqlException e)
 79                     {
 80                         throw e;
 81                     }
 82                     finally
 83                     {
 84                         conn.Close();
 85                     }
 86                 }
 87             }
 88         }
 89         public DataSet Query(string sqlStr,SqlParameter[] cmdParams)
 90         {
 91             using (SqlConnection conn = new SqlConnection(this._SqlConnectionStr))
 92             {
 93                 using (SqlCommand cmd = new SqlCommand())
 94                 {
 95                     using (SqlDataAdapter ada = new SqlDataAdapter(cmd))
 96                     {
 97                         try
 98                         {
 99                             conn.Open();
100                             cmd.Connection = conn;
101                             cmd.CommandType = CommandType.Text;
102                             cmd.CommandText = sqlStr;
103                             cmd.Parameters.AddRange(cmdParams);
104 
105                             DataSet ds = new DataSet();
106                             ada.Fill(ds);
107                             return ds;                            
108                         }
109                         catch(SqlException e)
110                         {
111                             throw e;
112                         }
113                         finally
114                         {
115                             conn.Close();
116                         }
117                     }
118                 }
119             }
120         }
121         public DataSet RunProcedure(string procName,SqlParameter[] cmdParams)
122         {
123             using (SqlConnection conn = new SqlConnection(this._SqlConnectionStr))
124             {
125                 using (SqlCommand cmd = new SqlCommand())
126                 {
127                     using (SqlDataAdapter ada = new SqlDataAdapter(cmd))
128                     {
129                         try
130                         {
131                             conn.Open();
132                             cmd.Connection = conn;
133                             cmd.CommandType = CommandType.StoredProcedure;
134                             cmd.CommandText = procName;
135                             cmd.Parameters.AddRange(cmdParams);
136 
137                             DataSet ds = new DataSet();
138                             ada.Fill(ds);
139                             return ds;
140                         }
141                         catch(SqlException e)
142                         {
143                             throw e;
144                         }
145                         finally
146                         {
147                             conn.Close();
148                         }
149                     }
150                 }
151             }
152         }
153         #endregion
154 
155         #region 单表查询        
156         public DataTable GetQueryData(string sqlStr)
157         {
158             DataSet ds = Query(sqlStr);
159             if (ds != null && ds.Tables.Count > 0)
160                 return ds.Tables[0];
161             return null; 
162         }
163         public DataTable GetQueryData(string sqlStr ,SqlParameter[] cmdParams)
164         {
165             DataSet ds = Query(sqlStr, cmdParams);
166             if (ds != null && ds.Tables.Count > 0)
167                 return ds.Tables[0];
168             return null;
169         }
170         public DataTable GetProcData(string procName,SqlParameter[] cmdParams)
171         {
172             DataSet ds = RunProcedure(procName, cmdParams);
173             if (ds != null && ds.Tables.Count > 0)
174                 return ds.Tables[0];
175             return null;
176         }
177         #endregion
178 
179         #region 单行查询       
180         public DataRow GetQueryRecord(string sqlStr)
181         {
182             DataTable dt = GetQueryData(sqlStr);
183             if (dt != null && dt.Rows.Count > 0)
184                 return dt.Rows[0];
185             return null;
186         }
187         public DataRow GetQueryRecord(string sqlStr,SqlParameter[] cmdParams)
188         {
189             DataTable dt = GetQueryData(sqlStr, cmdParams);
190             if (dt != null && dt.Rows.Count > 0)
191                 return dt.Rows[0];
192             return null;
193         }
194         public DataRow GetProcRecord(string procName,SqlParameter[] cmdParams)
195         {
196             DataTable dt = GetProcData(procName, cmdParams);
197             if (dt != null && dt.Rows.Count > 0)
198                 return dt.Rows[0];
199             return null;
200         }
201         #endregion
202 
203         #region 使用完应关闭Reader
204         public SqlDataReader ExecuteReader(string sqlStr)
205         {
206             SqlConnection conn = new SqlConnection(this._SqlConnectionStr);
207             SqlCommand cmd = new SqlCommand(sqlStr, conn);
208             try
209             {
210                 conn.Open();
211                 return cmd.ExecuteReader(CommandBehavior.CloseConnection);
212             }
213             catch(SqlException e)
214             {
215                 throw e;
216             }
217         }
218         public SqlDataReader ExecuteReeder(string sqlStr,SqlParameter[] cmdParams)
219         {
220             SqlConnection conn = new SqlConnection(this._SqlConnectionStr);
221             SqlCommand cmd = new SqlCommand();
222             try
223             {
224                 conn.Open();
225                 cmd.Connection = conn;
226                 cmd.CommandType = CommandType.Text;
227                 cmd.CommandText = sqlStr;
228                 cmd.Parameters.AddRange(cmdParams);
229                 return cmd.ExecuteReader(CommandBehavior.CloseConnection);
230             }
231             catch(SqlException e)
232             {
233                 throw e;
234             }
235         }
236         #endregion
237 
238         #region 执行sql语句        
239         public int ExecuteSql(string sqlStr)
240         {
241             using (SqlConnection conn = new SqlConnection(this._SqlConnectionStr))
242             {
243                 using (SqlCommand cmd = new SqlCommand(sqlStr, conn))
244                 {
245                     try
246                     {
247                         conn.Open();
248                         return cmd.ExecuteNonQuery();
249                     }
250                     catch(SqlException e)
251                     {
252                         throw e;
253                     }
254                     finally
255                     {
256                         conn.Close();
257                     }
258                 }
259             }
260         }
261         public int ExecuteSql(string sqlStr,SqlParameter[] cmdParams)
262         {
263             using (SqlConnection conn = new SqlConnection(this._SqlConnectionStr))
264             {
265                 using (SqlCommand cmd = new SqlCommand())
266                 {
267                     try
268                     {
269                         conn.Open();
270                         cmd.Connection = conn;
271                         cmd.CommandType = CommandType.Text;
272                         cmd.CommandText = sqlStr;
273                         cmd.Parameters.AddRange(cmdParams);
274                         return cmd.ExecuteNonQuery();
275                     }
276                     catch(SqlException e)
277                     {
278                         throw e;
279                     }
280                     finally
281                     {
282                         conn.Close();
283                     }
284                 }
285             }
286         }
287         #endregion
288 
289         #region 执行事务        
290         public int ExecuteSqlTran(List<string> sqlStrList)
291         {
292             using (SqlConnection conn = new SqlConnection(this._SqlConnectionStr))
293             {
294                 using (SqlCommand cmd = new SqlCommand())
295                 {
296                     using (SqlTransaction tran = conn.BeginTransaction())
297                     {
298                         try
299                         {
300                             cmd.Connection = conn;
301                             cmd.CommandType = CommandType.Text;                        
302                             cmd.Transaction = tran;
303                             conn.Open();
304                             int count = 0;
305                             foreach(string sql in sqlStrList)
306                             {
307                                 cmd.CommandText = sql;
308                                 count += cmd.ExecuteNonQuery();
309                             }
310                             tran.Commit();
311                             return count;
312                         }
313                         catch(SqlException e)
314                         {
315                             tran.Rollback();
316                             throw e;
317                         }
318                         finally
319                         {
320                             conn.Close();
321                         }
322                     }
323                 }
324             }
325         }
326         public int ExecuteSqlTran(List<KeyValuePair<string,SqlParameter[]>> sqlStrList)
327         {
328             using (SqlConnection conn = new SqlConnection(this._SqlConnectionStr))
329             {
330                 using (SqlCommand cmd = new SqlCommand())
331                 {
332                     using (SqlTransaction tran = conn.BeginTransaction())
333                     {
334                         try
335                         {
336                             cmd.Connection = conn;
337                             cmd.CommandType = CommandType.Text;
338                             cmd.Transaction = tran;
339                             conn.Open();
340                             int count = 0;
341                             foreach(var item in sqlStrList)
342                             {
343                                 cmd.CommandText = item.Key;
344                                 cmd.Parameters.Clear();
345                                 cmd.Parameters.AddRange(item.Value);
346                                 count += cmd.ExecuteNonQuery();
347                             }
348                             tran.Commit();
349                             return count;
350                         }
351                         catch(SqlException e)
352                         {
353                             tran.Rollback();
354                             throw e;
355                         }
356                         finally
357                         {
358                             conn.Close();
359                         }
360                     }
361                 }
362             }
363         }
364         public int ExecuteProc(string procName,SqlParameter[] cmdParams)
365         {
366             using (SqlConnection conn = new SqlConnection(this._SqlConnectionStr))
367             {
368                 using (SqlCommand cmd = new SqlCommand())
369                 {
370                     try
371                     {
372                         conn.Open();
373                         cmd.Connection = conn;
374                         cmd.CommandType = CommandType.StoredProcedure;
375                         cmd.CommandText = procName;
376                         cmd.Parameters.AddRange(cmdParams);
377                         return cmd.ExecuteNonQuery();
378                     }
379                     catch(SqlException e)
380                     {
381                         throw e;
382                     }
383                     finally
384                     {
385                         conn.Close();
386                     }
387                 }
388             }     
389         }
390         #endregion
391     }

二、工厂模式

 1     public class DbProvider
 2     {
 3         private static string _SqlConnectionStr = null;
 4         public static string SqlConnectionStr
 5         {
 6             get
 7             {
 8                 string connStr = _SqlConnectionStr;
 9                 if (connStr == null)
10                     connStr = "Data Source=(local);Initial Catalog=testDB;Persist Security Info=True;User ID=sa;Password=123456"; //一般这里是读取项目配置文件里的数据库参数来生成连接字符串
11                 return connStr;
12             }
13         }
14         public static SqlHelper SqlServer
15         {
16             get
17             {
18                 return new SqlHelper(SqlConnectionStr);
19             }
20         }
21     }

三、实例使用

1     public class Demo
2     {
3         public DataTable QueryOrderInfo(string id)
4         {
5             string sql = "select * from order where id=@id";
6             SqlParameter[] cmdParams = new SqlParameter[] { new SqlParameter("@id", id) };
7             return DbProvider.SqlServer.GetQueryData(sql, cmdParams);
8         }
9     }