C#实现较为实用的SQLhelper

OStack程序员社区-中国程序员成长平台 › 门户 › 编程› C++›C++教程

原作者: [db:作者] 来自: [db:来源] 收藏邀请

　　第一次写博客，想不到写什么好b（￣▽￣）d　，考虑的半天决定从sqlhelper开始，sqlhelper对程序员来说就像helloworld一样，很简单却又很重要，helloworld代表着程序员萌新第一次写代码，而sqlhelper则是初次接触数据库（不知道这种说法对不对）。

　　好了不废话了，下面直接上代码（无话可说了）：

  1     public class SQLHelper
  2     {
  3         // 超时时间
  4         private static int Timeout = 1000;
  5         // 数据库名称
  6         public const String BestNet = "BestNet";
  7         //存储过程名称
  8         public const String UserInfoCURD = "UserInfoCURD";
  9         // 数据库连接字符串
 10         private static Dictionary<String, String> ConnStrs = new Dictionary<String, String>();
 11 
 12         /// <summary>
 13         /// SQLServer操作类(静态构造函数)
 14         /// </summary>
 15         static SQLHelper()
 16         {
 17             ConnectionStringSettingsCollection configs = WebConfigurationManager.ConnectionStrings;
 18             foreach (ConnectionStringSettings config in configs)
 19             {
 20                 ConnStrs.Add(config.Name, config.ConnectionString);
 21             }
 22         }
 23 
 24         /// <summary>
 25         /// 获取数据库连接
 26         /// </summary>
 27         /// <param name="database">数据库(配置文件内connectionStrings的name)</param>
 28         /// <returns>数据库连接</returns>
 29         private static SqlConnection GetConnection(string database)
 30         {
 31             if (string.IsNullOrEmpty(database))
 32             {
 33                 throw new Exception("未设置参数：database");
 34             }
 35             if (!ConnStrs.ContainsKey(database))
 36             {
 37                 throw new Exception("未找到数据库：" + database);
 38             }
 39             return new SqlConnection(ConnStrs[database]);
 40         }
 41 
 42         /// <summary>
 43         /// 获取SqlCommand
 44         /// </summary>
 45         /// <param name="conn">SqlConnection</param>
 46         /// <param name="transaction">SqlTransaction</param>
 47         /// <param name="cmdType">CommandType</param>
 48         /// <param name="sql">SQL</param>
 49         /// <param name="parms">SqlParameter数组</param>
 50         /// <returns></returns>
 51         private static SqlCommand GetCommand(SqlConnection conn, SqlTransaction transaction, CommandType cmdType, string sql, SqlParameter[] parms)
 52         {
 53             SqlCommand cmd = new SqlCommand(sql, conn);
 54             cmd.CommandType = cmdType;
 55             cmd.CommandTimeout = Timeout;
 56             if (transaction != null)
 57                 cmd.Transaction = transaction;
 58             if (parms != null && parms.Length != 0)
 59                 cmd.Parameters.AddRange(parms);
 60             return cmd;
 61         }
 62 
 63         /// <summary>
 64         /// 查询数据，返回DataTable
 65         /// </summary>
 66         /// <param name="database">数据库</param>
 67         /// <param name="sql">SQL语句或存储过程名</param>
 68         /// <param name="parms">参数</param>
 69         /// <param name="cmdType">查询类型(SQL语句/存储过程名)</param>
 70         /// <returns>DataTable</returns>
 71         public static DataTable QueryDataTable(string database, string sql, SqlParameter[] parms, CommandType cmdType)
 72         {
 73             if (string.IsNullOrEmpty(database))
 74             {
 75                 throw new Exception("未设置参数：database");
 76             }
 77             if (string.IsNullOrEmpty(sql))
 78             {
 79                 throw new Exception("未设置参数：sql");
 80             }
 81 
 82             try
 83             {
 84                 using (SqlConnection conn = GetConnection(database))
 85                 {
 86                     conn.Open();
 87 
 88                     using (SqlCommand cmd = GetCommand(conn, null, cmdType, sql, parms))
 89                     {
 90                         using (SqlDataAdapter da = new SqlDataAdapter(cmd))
 91                         {
 92                             DataTable dt = new DataTable();
 93                             da.Fill(dt);
 94                             return dt;
 95                         }
 96                     }
 97                 }
 98             }
 99             catch (SqlException ex)
100             {
101                 System.Text.StringBuilder log = new System.Text.StringBuilder();
102                 log.Append("查询数据出错：");
103                 log.Append(ex);
104                 throw new Exception(log.ToString());
105             }
106         }
107 
108         /// <summary>
109         /// 查询数据，返回DataSet
110         /// </summary>
111         /// <param name="database">数据库</param>
112         /// <param name="sql">SQL语句或存储过程名</param>
113         /// <param name="parms">参数</param>
114         /// <param name="cmdType">查询类型(SQL语句/存储过程名)</param>
115         /// <returns>DataSet</returns>
116         public static DataSet QueryDataSet(string database, string sql, SqlParameter[] parms, CommandType cmdType)
117         {
118             if (string.IsNullOrEmpty(database))
119             {
120                 throw new Exception("未设置参数：database");
121             }
122             if (string.IsNullOrEmpty(sql))
123             {
124                 throw new Exception("未设置参数：sql");
125             }
126 
127             try
128             {
129                 using (SqlConnection conn = GetConnection(database))
130                 {
131                     conn.Open();
132 
133                     using (SqlCommand cmd = GetCommand(conn, null, cmdType, sql, parms))
134                     {
135                         using (SqlDataAdapter da = new SqlDataAdapter(cmd))
136                         {
137                             DataSet ds = new DataSet();
138                             da.Fill(ds);
139                             return ds;
140                         }
141                     }
142                 }
143             }
144             catch (SqlException ex)
145             {
146                 System.Text.StringBuilder log = new System.Text.StringBuilder();
147                 log.Append("查询数据出错：");
148                 log.Append(ex);
149                 throw new Exception(log.ToString());
150             }
151         }
152 
153         /// <summary>
154         /// 执行命令获取唯一值(第一行第一列)
155         /// </summary>
156         /// <param name="database">数据库</param>
157         /// <param name="sql">SQL语句或存储过程名</param>
158         /// <param name="parms">参数</param>
159         /// <param name="cmdType">查询类型(SQL语句/存储过程名)</param>
160         /// <returns>获取值</returns>
161         public static object QueryScalar(string database, string sql, SqlParameter[] parms, CommandType cmdType)
162         {
163             if (string.IsNullOrEmpty(database))
164             {
165                 throw new Exception("未设置参数：database");
166             }
167             if (string.IsNullOrEmpty(sql))
168             {
169                 throw new Exception("未设置参数：sql");
170             }
171             try
172             {
173                 using (SqlConnection conn = GetConnection(database))
174                 {
175                     conn.Open();
176 
177                     using (SqlCommand cmd = GetCommand(conn, null, cmdType, sql, parms))
178                     {
179                         return cmd.ExecuteScalar();
180                     }
181                 }
182             }
183             catch (SqlException ex)
184             {
185                 System.Text.StringBuilder log = new System.Text.StringBuilder();
186                 log.Append("处理出错：");
187                 log.Append(ex);
188                 throw new Exception(log.ToString());
189             }
190         }
191 
192         /// <summary>
193         /// 执行命令更新数据
194         /// </summary>
195         /// <param name="database">数据库</param>
196         /// <param name="sql">SQL语句或存储过程名</param>
197         /// <param name="parms">参数</param>
198         /// <param name="cmdType">查询类型(SQL语句/存储过程名)</param>
199         /// <returns>更新的行数</returns>
200         public static int Execute(string database, string sql, SqlParameter[] parms, CommandType cmdType)
201         {
202             if (string.IsNullOrEmpty(database))
203             {
204                 throw new Exception("未设置参数：database");
205             }
206             if (string.IsNullOrEmpty(sql))
207             {
208                 throw new Exception("未设置参数：sql");
209             }
210 
211             //返回(增删改)的更新行数
212             int count = 0;
213 
214             try
215             {
216                 using (SqlConnection conn = GetConnection(database))
217                 {
218                     conn.Open();
219 
220                     using (SqlCommand cmd = GetCommand(conn, null, cmdType, sql, parms))
221                     {
222                         if (cmdType == CommandType.StoredProcedure)
223                             cmd.Parameters.AddWithValue("@RETURN_VALUE", "").Direction = ParameterDirection.ReturnValue;
224 
225                         count = cmd.ExecuteNonQuery();
226 
227                         if (count <= 0)
228                             if (cmdType == CommandType.StoredProcedure)
229                                 count = (int)cmd.Parameters["@RETURN_VALUE"].Value;
230                     }
231                 }
232             }
233             catch (SqlException ex)
234             {
235                 System.Text.StringBuilder log = new System.Text.StringBuilder();
236                 log.Append("处理出错：");
237                 log.Append(ex);
238                 throw new Exception(log.ToString());
239             }
240             return count;
241         }
242 
243         /// <summary>
244         /// 查询数据，返回DataTable
245         /// </summary>
246         /// <param name="database">数据库</param>
247         /// <param name="sql">SQL语句或存储过程名</param>
248         /// <param name="cmdType">查询类型(SQL语句/存储过程名)</param>
249         /// <param name="values">参数</param>
250         /// <returns>DataTable</returns>
251         public static DataTable QueryDataTable(string database, string sql, CommandType cmdType, IDictionary<string, object> values)
252         {
253             SqlParameter[] parms = DicToParams(values);
254             return QueryDataTable(database, sql, parms, cmdType);
255         }
256 
257         /// <summary>
258         /// 执行存储过程查询数据，返回DataSet
259         /// </summary>
260         /// <param name="database">数据库</param>
261         /// <param name="sql">SQL语句或存储过程名</param>
262         /// <param name="cmdType">查询类型(SQL语句/存储过程名)</param>
263         /// <param name="values">参数
264         /// <returns>DataSet</returns>
265         public static DataSet QueryDataSet(string database, string sql, CommandType cmdType, IDictionary<string, object> values)
266         {
267             SqlParameter[] parms = DicToParams(values);
268             return QueryDataSet(database, sql, parms, cmdType);
269         }
270 
271         /// <summary>
272         /// 执行命令获取唯一值(第一行第一列)
273         /// </summary>
274         /// <param name="database">数据库</param>
275         /// <param name="sql">SQL语句或存储过程名</param>
276         /// <param name="cmdType">查询类型(SQL语句/存储过程名)</param>
277         /// <param name="values">参数</param>
278         /// <returns>唯一值</returns>
279         public static object QueryScalar(string database, string sql, CommandType cmdType, IDictionary<string, object> values)
280         {
281             SqlParameter[] parms = DicToParams(values);
282             return QueryScalar(database, sql, parms, cmdType);
283         }
284 
285         /// <summary>
286         /// 执行命令更新数据
287         /// </summary>
288         /// <param name="database">数据库</param>
289         /// <param name="sql">SQL语句或存储过程名</param>
290         /// <param name="cmdType">查询类型(SQL语句/存储过程名)</param>
291         /// <param name="values">参数</param>
292         /// <returns>更新的行数</returns>
293         public static int Execute(string database, string sql, CommandType cmdType, IDictionary<string, object> values)
294         {
295             SqlParameter[] parms = DicToParams(values);
296             return Execute(database, sql, parms, cmdType);
297         }
298 
299         /// <summary>
300         /// 创建参数
301         /// </summary>
302         /// <param name="name">参数名</param>
303         /// <param name="type">参数类型</param>
304         /// <param name="size">参数大小</param>
305         /// <param name="direction">参数方向(输入/输出)</param>
306         /// <param name="value">参数值</param>
307         /// <returns>新参数对象</returns>
308         public static SqlParameter[] DicToParams(IDictionary<string, object> values)
309         {
310             if (values == null) return null;
311 
312             SqlParameter[] parms = new SqlParameter[values.Count];
313             int index = 0;
314             foreach (KeyValuePair<string, object> kv in values)
315             {
316                 SqlParameter parm = null;
317                 if (kv.Value == null)
318  
                       
                    
                     
                      



鲜花




握手




雷人




路过




鸡蛋