菜鸟学习Ado.net笔记一：Ado.net学习之SqlHelper…

2018-06-23 22:48:03来源：未知阅读 ()

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Text;
  4 using System.Data.SqlClient;
  5 using System.Data;
  6 using Microsoft.Win32;
  7 
  8 namespace SqlHelp
  9 {
 10     /// <summary>
 11     /// 定义SqlParameter所需的参数对象
 12     /// </summary>
 13     public class Parameter
 14     {
 15         /// <summary>
 16         /// 参数集合构造函数
 17         /// </summary>
 18         /// <param name="paramname">参数名称</param>
 19         /// <param name="value">参数所对应的对象的值</param>
 20         public Parameter(string paramname, object value)
 21         {
 22             this.ParamName = paramname;
 23             this.Obj = value;
 24         }
 25         /// <summary>
 26         /// 参数名称
 27         /// </summary>
 28         public string ParamName
 29         {
 30             get;
 31             set;
 32         }
 33         /// <summary>
 34         /// 参数名称所对应的对象的值
 35         /// </summary>
 36         public object Obj
 37         {
 38             get;
 39             set;
 40         }
 41     }
 42     /// <summary>
 43     /// SqlHelper  ^_^ !
 44     /// </summary>
 45     public class SqlHelper
 46     {
 47         /// <summary>
 48         /// 连接字符串字段
 49         /// </summary>
 50         private static string connStr;
 51 
 52         /// <summary>
 53         /// SQL连接字符串属性
 54         /// </summary>       
 55         public static string ConnStr
 56         {
 57             get { return SqlHelper.connStr; }
 58             set { SqlHelper.connStr = value; }
 59         }
 60 
 61         private static SqlParameter[] GetSqlParameterToArr(List<Parameter> listP)
 62         {
 63             List<SqlParameter> list = new List<SqlParameter>();
 64             foreach (var item in listP)
 65             {
 66                 list.Add(new SqlParameter(item.ParamName, item.Obj));
 67             }
 68             return list.ToArray();
 69         }
 70 
 71         /// <summary>
 72         /// 执行TSQL 语句并返回受影响的行
 73         /// </summary>
 74         /// <param name="sql">需要执行的sql语句</param>
 75         /// <returns></returns>
 76 
 77         public static int ExecuteNonQuery(string sql)
 78         {
 79             try
 80             {
 81                 using (SqlConnection conn = new SqlConnection(connStr))
 82                 {
 83                     conn.Open();
 84                     using (SqlCommand cmd = conn.CreateCommand())
 85                     {
 86                         cmd.CommandText = sql;
 87                         return cmd.ExecuteNonQuery();
 88                     }
 89                 }
 90             }
 91             catch (Exception ex)
 92             {
 93                 throw new Exception(ex.Message);
 94             }
 95         }
 96 
 97         /// <summary>
 98         /// 执行TSQL 语句并返回受影响的行 
 99         /// </summary>
100         /// <param name="sql">需要执行的sql语句</param>
101         /// <param name="paramList">参数的泛型集合</param>
102         /// <returns></returns>
103         public static int ExecuteNonQuery(string sql, List<Parameter> paramList)
104         {
105             try
106             {
107                 using (SqlConnection conn = new SqlConnection(connStr))
108                 {
109                     conn.Open();
110                     using (SqlCommand cmd = conn.CreateCommand())
111                     {
112                         cmd.CommandText = sql;
113                         cmd.Parameters.AddRange(GetSqlParameterToArr(paramList));
114                         return cmd.ExecuteNonQuery();
115                     }
116                 }
117             }
118             catch (Exception ex)
119             {
120                 throw new Exception(ex.Message);
121             }
122         }
123 
124 
125         /// <summary>
126         /// 执行查询，并返回查询所返回的结果集中第一行的第一列 
127         /// </summary>
128         /// <param name="sql">需要执行的sql语句</param>
129         /// <returns></returns>
130 
131         public static object ExecuteScalar(string sql)
132         {
133             try
134             {
135                 using (SqlConnection conn = new SqlConnection(connStr))
136                 {
137                     conn.Open();
138                     using (SqlCommand cmd = conn.CreateCommand())
139                     {
140                         cmd.CommandText = sql;
141                         return cmd.ExecuteScalar();
142                     }
143                 }
144             }
145             catch (Exception ex)
146             {
147                 throw new Exception(ex.Message);
148             }
149         }
150         /// <summary>
151         /// 执行查询，并返回查询所返回的结果集中第一行的第一列 
152         /// </summary>
153         /// <param name="sql">需要执行的sql语句</param>
154         /// <param name="paramList">参数的泛型集合</param>
155         /// <returns></returns>
156         public static object ExecuteScalar(string sql, List<Parameter> paramList)
157         {
158             try
159             {
160                 using (SqlConnection conn = new SqlConnection(connStr))
161                 {
162                     conn.Open();
163                     using (SqlCommand cmd = conn.CreateCommand())
164                     {
165                         cmd.CommandText = sql;
166                         cmd.Parameters.AddRange(GetSqlParameterToArr(paramList));
167                         return cmd.ExecuteScalar();
168                     }
169                 }
170             }
171             catch (Exception ex)
172             {
173                 throw new Exception(ex.Message);
174             }
175         }
176 
177 
178         /// <summary>
179         /// 返回已经填充结果的DataSet 
180         /// </summary>
181         /// <param name="sql">需要执行的sql语句</param>
182         /// <returns></returns>
183 
184         public static DataSet ExecuteDataSet(string sql)
185         {
186             try
187             {
188                 using (SqlConnection conn = new SqlConnection(connStr))
189                 {
190                     conn.Open();
191                     using (SqlCommand cmd = conn.CreateCommand())
192                     {
193                         cmd.CommandText = sql;
194                         SqlDataAdapter adapter = new SqlDataAdapter(cmd);
195                         DataSet dataset = new DataSet();
196                         adapter.Fill(dataset);
197                         return dataset;
198                     }
199                 }
200             }
201             catch (Exception ex)
202             {
203                 throw new Exception(ex.Message);
204             }
205         }
206 
207         /// <summary>
208         /// 返回已经填充结果的DataSet 
209         /// </summary>
210         /// <param name="sql">需要执行的sql语句</param>
211         /// <param name="paramList">参数的泛型集合</param>
212         /// <returns></returns>
213         public static DataSet ExecuteDataSet(string sql, List<Parameter> paramList)
214         {
215             try
216             {
217                 using (SqlConnection conn = new SqlConnection(connStr))
218                 {
219                     conn.Open();
220                     using (SqlCommand cmd = conn.CreateCommand())
221                     {
222                         cmd.CommandText = sql;
223                         cmd.Parameters.AddRange(GetSqlParameterToArr(paramList));
224                         SqlDataAdapter adapter = new SqlDataAdapter(cmd);
225                         DataSet dataset = new DataSet();
226                         adapter.Fill(dataset);
227                         return dataset;
228                     }
229                 }
230             }
231             catch (Exception ex)
232             {
233                 throw new Exception(ex.Message);
234             }
235         }
236 
237 
238         /// <summary>
239         /// 返回查询结果集所返回的字段值的泛型集合 
240         /// </summary>
241         /// <param name="sql">需要执行的sql语句</param>
242         /// <returns></returns>
243 
244         public static List<object> ExecuteReader(string sql)
245         {
246             List<object> obj = new List<object>();
247             try
248             {
249                 using (SqlConnection conn = new SqlConnection(connStr))
250                 {
251                     conn.Open();
252                     using (SqlCommand cmd = conn.CreateCommand())
253                     {
254                         cmd.CommandText = sql;
255                         using (SqlDataReader reader = cmd.ExecuteReader())
256                         {
257                             while (reader.Read())
258                             {
259                                 for (int i = 0; i < reader.FieldCount; i++)
260                                 {
261                                     obj.Add(reader.IsDBNull(i) ? "空值" : reader.GetValue(i));
262                                 }
263                             }
264                             return obj;
265                         }
266                     }
267                 }
268             }
269             catch (Exception ex)
270             {
271                 throw new Exception(ex.Message);
272             }
273         }
274 
275 
276         /// <summary>
277         /// 返回查询结果集所返回的字段值的泛型集合 
278         /// </summary>
279         /// <param name="sql">需要执行的sql语句</param>
280         /// <param name="paramList">参数的泛型集合</param>
281         /// <returns></returns>
282         public static List<object> ExecuteReader(string sql, List<Parameter> paramList)
283         {
284             List<object> obj = new List<object>();
285             try
286             {
287                 using (SqlConnection conn = new SqlConnection(connStr))
288                 {
289                     conn.Open();
290                     using (SqlCommand cmd = conn.CreateCommand())
291                     {
292                         cmd.CommandText = sql;
293                         cmd.Parameters.AddRange(GetSqlParameterToArr(paramList));
294                         using (SqlDataReader reader = cmd.ExecuteReader())
295                         {
296                             while (reader.Read())
297                             {
298                                 for (int i = 0; i < reader.FieldCount; i++)
299                                 {
300                                     obj.Add(reader.IsDBNull(i) ? "空值" : reader.GetValue(i));
301                                 }
302                             }
303                             return obj;
304                         }
305                     }
306                 }
307             }
308             catch (Exception ex)
309             {
310                 throw new Exception(ex.Message);
311             }
312         }
313 
314 
315         /// <summary>
316         /// 获取SqlServer数据库实例名数组 
317         /// </summary>
318         /// <returns></returns>
319         public static string[] GetInstances()
320         {
321             RegistryKey reg = Registry.LocalMachine.OpenSubKey(@"SOFTWARE\Microsoft\Microsoft SQL Server");
322             string[] instances = (string[])reg.GetValue("InstalledInstances", "");
323             try
324             {
325                 if (instances.Length > 0)
326                 {
327                     for (int i = 0; i < instances.Length; i++)
328                     {
329                         if (instances[i] == "MSSQLSERVER")
330                         {
331                             instances[i] = System.Environment.MachineName;
332                         }
333                         else
334                         {
335                             instances[i] = System.Environment.MachineName + @"\" + instances[i];
336                         }
337                     }
338                 }
339                 return instances;
340             }
341             catch (Exception ex)
342             {
343                 throw new Exception(ex.Message);
344             }
345         }
346     }
347 }

测试：
1、获取实例

窗体拖入ComboBox控件，设置name值为cbx_server

引入SqlHelper

using SqlHelp

窗体load事件加入：

1 cbx_server.Items .AddRange ( GetInstances());

2、执行带参数查询方法

窗体拖入按钮，name为Bt_Test，并且拖入TextBox控件，name值为txt_Param

引入SqlHelper

using SqlHelp

在按钮点击事件中加入：

 1         private void Bt_Test_Click(object sender, EventArgs e)
 2         {
 3             SqlHelper.ConnStr = @"Data Source=localhost;Initial Catalog=UFsystem;Integrated Security=True";
 4             Parameter param = new Parameter("@id", txt_Param.Text);
 5             List<Parameter> list = new List<Parameter>();
 6             list.Add(param);
 7             List<object> obj = SqlHelper.ExecuteReader(@"select * from ua_user where cuser_id=@id", list);
 8             foreach (var item in obj)
 9             {
10                 Console.WriteLine(item);
11             }
12         }