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 try106 {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 try134 {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 try159 {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 try187 {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 try216 {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 try248 {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 try286 {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 try324 {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 else334 {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#,有什么地方不对或不合适的请指教。
新闻热点
疑难解答