1
2
3 这周在工作室开始与美工部分合作开发网站，自己写了一个简单的sql参数化查询类
4
5 using System;
6 using System.Collections.Generic;
7 using System.Data;
8 using System.Data.SqlClient;
9 using System.Linq;
10 using System.Web;
11
12 namespace bilibili
13 {
14 public static class sqlHelperWithParameter
15 {
16 //public static readonly String conString =ConfigurationManager.ConnectionStrings["SQLProfileConnString"].ToString();
17 //static string str = @"server=(localdb)\v11.0;Integrated Security=SSPI;database=bilibili";//windows连接字符串
18 static string str = System.Configuration.ConfigurationManager.ConnectionStrings["con"].ToString();
19 /// <summary>
20 /// 连接数据库
21 /// </summary>
22 /// <returns>返回SqlConnection对象</returns>
23 public static SqlConnection GetConnection()
24 {
25 //conn、ConnectionString在web.config
26 //string myStr = ConfigurationManager.AppSettings["ConnectionString"].ToString();
27 ///string str = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
28 SqlConnection myConn = new SqlConnection(str);
29 return myConn;
30 }
31 /// <summary>
32 /// 查询第一行第一列数据（无参）（返回的是什么类型就转换成什么类型）
33 /// </summary>
34 /// <param name="sql"></param>
35 /// <returns>object</returns>
36 public static object GetScalar(string safeSql)
37 {
38 SqlConnection myConn = GetConnection();
39 try
40 {
41 myConn.Open();
42 SqlCommand cmd = new SqlCommand(safeSql, myConn);
43 return cmd.ExecuteScalar();
44 }
45 catch (Exception ex)
46 {
47 throw new Exception(ex.Message);
48 }
49 finally
50 {
51 myConn.Close();
52 }
53 }
54 /// <summary>
55 /// 查询第一行第一列数据（有参）（返回的是什么类型就转换成什么类型）
56 /// </summary>
57 /// <param name="sql"></param>
58 /// <param name="values"></param>
59 /// <returns>object</returns>
60 public static object GetScalar(string safeSql, params SqlParameter[] values)
61 {
62 SqlConnection myConn = GetConnection();//连接数据库
63 try
64 {
65 myConn.Open();
66 SqlCommand cmd = new SqlCommand(safeSql, myConn);
67 cmd.Parameters.AddRange(values);
68 return cmd.ExecuteScalar();
69 }
70 catch (Exception ex)
71 {
72 throw new Exception(ex.Message);
73 }
74 finally
75 {
76 myConn.Close();
77 }
78 }
79 /// <summary>
80 /// 执行一个无参增删查改存储过程
81 /// </summary>
82 /// <param name="procName"></param>
83 /// <returns>返回受影响的行数</returns>
84 public static int ExcuteProc(string procName)
85 {
86 SqlConnection myConn = GetConnection();
87 try
88 {
89 myConn.Open();
90 SqlCommand cmd = new SqlCommand(procName, myConn);
91 return cmd.ExecuteNonQuery();
92 }
93 catch (Exception ex)
94 {
95 throw new Exception(ex.Message);
96 }
97 finally
98 {
99 myConn.Close();
100 }
101 }
102 /// <summary>
103 /// 执行一个有参的增删查改过程
104 /// </summary>
105 /// <param name="procName"></param>
106 /// <param name="values"></param>
107 /// <returns></returns>
108 public static int ExcuteProc(string procName, params SqlParameter[] values)
109 {
110 SqlConnection myConn = GetConnection();
111 try
112 {
113 myConn.Open();
114 SqlCommand cmd = new SqlCommand(procName, myConn);
115 cmd.Parameters.AddRange(values);
116 return cmd.ExecuteNonQuery();
117 }
118 catch (Exception ex)
119 {
120 throw new Exception(ex.Message);
121 }
122 finally
123 {
124 myConn.Close();
125 }
126 }
127 /// <summary>
128 /// 返回一个dataTable(无参数)
129 /// </summary>
130 /// <param name="safeSql"></param>
131 /// <returns>dataTable</returns>
132 public static DataTable GetDataSet(string safeSql)
133 {
134 SqlConnection myConn = GetConnection();
135 DataSet ds = new DataSet();
136 try
137 {
138 myConn.Open();
139 SqlCommand cmd = new SqlCommand(safeSql, myConn);
140 SqlDataAdapter da = new SqlDataAdapter(cmd);
141 da.Fill(ds);
142 return ds.Tables[0];
143 }
144 catch (Exception ex)
145 {
146 throw new Exception(ex.Message);
147 }
148 finally
149 {
150 myConn.Close();
151 }
152 }
153 /// <summary>
154 /// 返回一个Datatable（有参）
155 /// </summary>
156 /// <param name="safeSql"></param>
157 /// <param name="values"></param>
158 /// <returns>dataTable</returns>
159 public static DataTable GetDataSet(string safeSql, params SqlParameter[] values)
160 {
161 SqlConnection myConn = GetConnection();
162 DataSet ds = new DataSet();
163 try
164 {
165 myConn.Open();
166 SqlCommand cmd = new SqlCommand(safeSql, myConn);
167 cmd.Parameters.AddRange(values);
168 SqlDataAdapter da = new SqlDataAdapter(cmd);
169 da.Fill(ds);
170 return ds.Tables[0];
171 }
172 catch (Exception ex)
173 {
174 throw new Exception(ex.Message);
175 }
176 finally
177 {
178 myConn.Close();
179 }
180 }
181 /// <summary>
182 /// 返回一个sqlDataReader(注意要关闭)
183 /// </summary>
184 /// <param name="safeSql"></param>
185 /// <returns>SqlDataReader</returns>
186 public static SqlDataReader GetReader(string safeSql)
187 {
188 SqlConnection myConn = GetConnection();
189 try
190 {
191 myConn.Open();
192 SqlCommand cmd = new SqlCommand(safeSql, myConn);
193 SqlDataReader reader = cmd.ExecuteReader();
194 return reader;
195 }
196 catch (Exception ex)
197 {
198 throw new Exception(ex.Message);
199 }
200 finally
201 {
202 myConn.Close();
203 }
204 }
205 /// <summary>
206 /// 返回一个sqlDataReader(注意要关闭)
207 /// </summary>
208 /// <param name="safeSql"></param>
209 /// <param name="values"></param>
210 /// <returns>sqlDataReader</returns>
211 public static SqlDataReader GetReader(string safeSql,params SqlParameter[] values)
212 {
213 SqlConnection myConn = GetConnection();
214 try
215 {
216 myConn.Open();
217 SqlCommand cmd = new SqlCommand(safeSql, myConn);
218 cmd.Parameters.AddRange(values);
219 SqlDataReader reader = cmd.ExecuteReader();
220 return reader;
221 }
222 catch (Exception ex)
223 {
224 throw new Exception(ex.Message);
225 }
226 finally
227 {
228 myConn.Close();
229 }
230 }
231 }
232 }
233
234 调用例子：
235
236 /// <summary>
237 /// 验证用户是否存在
238 /// </summary>
239 /// <returns></returns>
240 public object DetermineWhetherAuserExists()
241 {
242 string sqlStr = "select * from [admin] where adminName=@name1";
243 SqlParameter[] parameter = new SqlParameter[]
244 {
245 new SqlParameter("@name1",SqlDbType.VarChar){Value=_adminName}
246 };
247 return sqlHelperWithParameter.GetScalar(sqlStr, parameter);
248 }
