菜鸟学习Ado.net笔记一：Ado.net学习之SqlHelper类
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 }
输出：
admin
admin
空值
True
空值
空值
空值
空值
正在学习c#，有什么地方不对或不合适的请指教。
每天学习一点点，每天进步一点点
