1 using System;
2 using System.Collections.Generic;
3 using System.Linq;
4 using System.Text;
5 using System.Data.SqlClient;
6 using System.Data;
7 using System.Configuration;
8
9 namespace ClassLibrary
10 {
11 /// <summary>
12 /// SqlDBHelper帮助类
13 /// </summary>
14 public class SqlDBHelper
15 {
16 public static SqlConnection connection;
17 public static SqlConnection Connection
18 {
19 get
20 {
21 string connectionString = ConfigurationManager.ConnectionStrings["mybookshop"].ConnectionString;
22 if (connection == null)
23 {
24 connection = new SqlConnection(connectionString);
25 connection.Open();
26 }
27 else if (connection.State == System.Data.ConnectionState.Closed)
28 {
29 connection = new SqlConnection(connectionString);
30 connection.Open();
31 }
32 else if (connection.State == System.Data.ConnectionState.Broken)
33 {
34 connection.Close();
35 connection.Open();
36 }
37 return connection;
38 }
39 }
40
41 /// <summary>
42 /// 单个数据增，删，改
43 /// </summary>
44 /// <param name="safeSql"></param>
45 /// <returns></returns>
46 public static int ExecuteCommand(string safeSql)
47 {
48 try
49 {
50 using (SqlCommand cmd = new SqlCommand(safeSql, Connection))
51 {
52 int result = cmd.ExecuteNonQuery();
53 return result;
54 }
55 }
56 catch (SqlException ex)
57 {
58 throw ex;
59 }
60 }
61
62 /// <summary>
63 /// 带多个参数的增，删，改
64 /// </summary>
65 /// <param name="safeSql"></param>
66 /// <param name="values"></param>
67 /// <returns></returns>
68 public static int ExecuteCommand(string safeSql, params SqlParameter[] values)
69 {
70 try
71 {
72 using (SqlCommand cmd = new SqlCommand(safeSql, Connection))
73 {
74 cmd.Parameters.AddRange(values);
75 return cmd.ExecuteNonQuery();
76 }
77 }
78 catch (SqlException ex)
79 {
80 throw ex;
81 }
82 }
83
84 /// <summary>
85 /// 带多个参数的增，删，改
86 /// </summary>
87 /// <param name="safeSql"></param>
88 /// <param name="values"></param>
89 /// <returns></returns>
90 public static int ExecuteCommand(string safeSql, CommandType type, params SqlParameter[] values)
91 {
92 try
93 {
94 using (SqlCommand cmd = new SqlCommand(safeSql, Connection))
95 {
96 cmd.CommandType = type;
97 cmd.Parameters.AddRange(values);
98 cmd.ExecuteNonQuery();
99 return cmd.ExecuteNonQuery();
100 }
101 }
102 catch (SqlException ex)
103 {
104 throw ex;
105 }
106 }
107
108 /// <summary>
109 /// 带多个参数的增，删，改
110 /// </summary>
111 /// <param name="safeSql"></param>
112 /// <param name="values"></param>
113 /// <returns></returns>
114 public static int ExecuteCommand(string safeSql, CommandType type, int index)
115 {
116 try
117 {
118 using (SqlCommand cmd = new SqlCommand(safeSql, Connection))
119 {
120 cmd.CommandType = type;
121 SqlParameter paramOne = new SqlParameter("@rid", SqlDbType.Int);
122 paramOne.Value = index;
123 cmd.Parameters.Add(paramOne);
124 return cmd.ExecuteNonQuery();
125 }
126 }
127 catch (SqlException ex)
128 {
129 throw ex;
130 }
131 }
132
133 /// <summary>
134 /// 查单个值
135 /// </summary>
136 /// <param name="safeSql"></param>
137 /// <returns></returns>
138 public static int GetScalar(string safeSql)
139 {
140 try
141 {
142 using (SqlCommand cmd = new SqlCommand(safeSql, Connection))
143 {
144 int result = Convert.ToInt32(cmd.ExecuteScalar());
145 return result;
146 }
147 }
148 catch (SqlException ex)
149 {
150 throw ex;
151 }
152 }
153
154 /// <summary>
155 /// 带参数的查询语句
156 /// </summary>
157 /// <param name="sql"></param>
158 /// <param name="values"></param>
159 /// <returns></returns>
160 public static int GetScalar(string sql, params SqlParameter[] values)
161 {
162 try
163 {
164 using (SqlCommand cmd = new SqlCommand(sql, Connection))
165 {
166 cmd.Parameters.AddRange(values);
167 int result = Convert.ToInt32(cmd.ExecuteScalar());
168 return result;
169 }
170 }
171 catch (SqlException ex)
172 {
173 throw ex;
174 }
175 }
176
177 /// <summary>
178 /// 带执行类型的ExecuteScalar
179 /// </summary>
180 /// <param name="sql"></param>
181 /// <param name="type"></param>
182 /// <param name="values"></param>
183 /// <returns></returns>
184 public static int GetScalar(string sql, CommandType type, params SqlParameter[] values)
185 {
186 try
187 {
188 using (SqlCommand cmd = new SqlCommand(sql, Connection))
189 {
190 cmd.CommandType = type;
191 cmd.Parameters.AddRange(values);
192 int result = Convert.ToInt32(cmd.ExecuteScalar());
193 return result;
194 }
195 }
196 catch (SqlException ex)
197 {
198 throw ex;
199 }
200 }
201
202 /// <summary>
203 /// 查询表,获取多个记录
204 /// </summary>
205 /// <param name="safeSql"></param>
206 /// <returns></returns>
207 public static SqlDataReader GetReader(string safeSql)
208 {
209 try
210 {
211 using (SqlCommand cmd = new SqlCommand(safeSql, Connection))
212 {
213 SqlDataReader reader = cmd.ExecuteReader();
214 return reader;
215 }
216
217 }
218 catch (SqlException ex)
219 {
220 throw ex;
221 }
222 }
223
224 /// <summary>
225 /// 带参数的-查询表,获取多个记录
226 /// </summary>
227 /// <param name="sql"></param>
228 /// <param name="values"></param>
229 /// <returns></returns>
230 public static SqlDataReader GetReader(string sql, params SqlParameter[] values)
231 {
232 try
233 {
234 using (SqlCommand cmd = new SqlCommand(sql, Connection))
235 {
236 cmd.Parameters.AddRange(values);
237 SqlDataReader reader = cmd.ExecuteReader();
238 return reader;
239 }
240 }
241 catch (SqlException)
242 {
243 throw;
244 }
245 }
246
247 /// <summary>
248 /// 查询表,获取多个记录---语句，类型，参数
249 /// </summary>
250 /// <param name="safeSql"></param>
251 /// <param name="cmdType"></param>
252 /// <param name="values"></param>
253 /// <returns></returns>
254 public static SqlDataReader GetReader(string safeSql, CommandType cmdType, params SqlParameter[] values)
255 {
256 try
257 {
258 using (SqlCommand cmd = new SqlCommand(safeSql, Connection))
259 {
260 cmd.CommandType = cmdType;
261 cmd.Parameters.AddRange(values);
262 SqlDataReader reader = cmd.ExecuteReader();
263 return reader;
264 }
265 }
266 catch (SqlException ex)
267 {
268 throw ex;
269 }
270 }
271
272 /// <summary>
273 /// 返回datatable
274 /// </summary>
275 /// <param name="safeSql"></param>
276 /// <returns></returns>
277 public static DataTable GetDataSet(string safeSql)
278 {
279 DataSet ds = new DataSet();
280 SqlCommand cmd = new SqlCommand(safeSql, Connection);
281 SqlDataAdapter da = new SqlDataAdapter(cmd);
282 da.Fill(ds);
283 return ds.Tables[0];
284 }
285
286 /// <summary>
287 /// 返回dataTable ,带参数使用
288 /// </summary>
289 /// <param name="sql"></param>
290 /// <param name="values"></param>
291 /// <returns></returns>
292 public static DataTable GetDataSet(string sql, params SqlParameter[] values)
293 {
294 DataSet ds = new DataSet();
295 SqlCommand cmd = new SqlCommand(sql, Connection);
296 cmd.Parameters.AddRange(values);
297 SqlDataAdapter da = new SqlDataAdapter(cmd);
298 da.Fill(ds);
299 return ds.Tables[0];
300 }
301 }
302 }
