1 public abstract class SqlHelper
2 {
3 //数据库连接字符串(web.config来配置)，可以动态更改connectionString支持多数据库.
4 public static string connectionString = ConfigurationManager.ConnectionStrings["database"].ConnectionString;
5 public SqlHelper()
6 {
7 //connectionString = @"DATA SOURCE=(local);UID=sa;PWD=sa;DATABASE=db";
8 }
9
10 /// <summary>
11 /// 判断是否存在某表的某个字段
12 /// </summary>
13 /// <param name="tableName">表名称</param>
14 /// <param name="columnName">列名称</param>
15 /// <returns>是否存在</returns>
16 public static bool ColumnExists(string tableName, string columnName)
17 {
18 string sql = "select count(1) from syscolumns where [id]=object_id('" + tableName + "') and [name]='" + columnName + "'";
19 object res = GetSingle(sql);
20 if (res == null)
21 {
22 return false;
23 }
24 return Convert.ToInt32(res) > 0;
25 }
26 public static int GetMaxID(string FieldName, string TableName)
27 {
28 string strsql = "select max(" + FieldName + ")+1 from " + TableName;
29 object obj = SqlHelper.GetSingle(strsql);
30 if (obj == null)
31 {
32 return 1;
33 }
34 else
35 {
36 return int.Parse(obj.ToString());
37 }
38 }
39 public static bool Exists(string strSql)
40 {
41 object obj = SqlHelper.GetSingle(strSql);
42 int cmdresult;
43 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
44 {
45 cmdresult = 0;
46 }
47 else
48 {
49 cmdresult = int.Parse(obj.ToString());
50 }
51 if (cmdresult == 0)
52 {
53 return false;
54 }
55 else
56 {
57 return true;
58 }
59 }
60
61
62 #region 执行简单SQL语句
63 /// <summary>
64 /// 执行SQL语句，返回影响的记录数
65 /// </summary>
66 /// <param name="SQLString">SQL语句</param>
67 /// <returns>影响的记录数</returns>
68 public static int ExecuteSql(string SQLString)
69 {
70 using (SqlConnection connection = new SqlConnection(connectionString))
71 {
72 using (SqlCommand cmd = new SqlCommand(SQLString, connection))
73 {
74 try
75 {
76 connection.Open();
77 int rows = cmd.ExecuteNonQuery();
78 return rows;
79 }
80 catch (System.Data.SqlClient.SqlException e)
81 {
82 connection.Close();
83 throw e;
84 }
85 }
86 }
87 }
88 public static int ExecuteSqlByTime(string SQLString, int Times)
89 {
90 using (SqlConnection connection = new SqlConnection(connectionString))
91 {
92 using (SqlCommand cmd = new SqlCommand(SQLString, connection))
93 {
94 try
95 {
96 connection.Open();
97 cmd.CommandTimeout = Times;
98 int rows = cmd.ExecuteNonQuery();
99 return rows;
100 }
101 catch (System.Data.SqlClient.SqlException e)
102 {
103 connection.Close();
104 throw e;
105 }
106 }
107 }
108 }
109
110 /// <summary>
111 /// 执行多条SQL语句，实现数据库事务。
112 /// </summary>
113 /// <param name="SQLStringList">多条SQL语句</param>
114 public static int ExecuteSqlTran(List<String> SQLStringList)
115 {
116 using (SqlConnection conn = new SqlConnection(connectionString))
117 {
118 conn.Open();
119 SqlCommand cmd = new SqlCommand();
120 cmd.Connection = conn;
121 SqlTransaction tx = conn.BeginTransaction();
122 cmd.Transaction = tx;
123 try
124 {
125 int count = 0;
126 for (int n = 0;
127 n < SQLStringList.Count;
128 n++)
129 {
130 string strsql = SQLStringList[n];
131 if (strsql.Trim().Length > 1)
132 {
133 cmd.CommandText = strsql;
134 count += cmd.ExecuteNonQuery();
135 }
136 }
137 tx.Commit();
138 return count;
139 }
140 catch
141 {
142 tx.Rollback();
143 return 0;
144 }
145 }
146 }
147
148 /// <summary>
149 /// 执行带一个存储过程参数的的SQL语句。
150 /// </summary>
151 /// <param name="SQLString">SQL语句</param>
152 /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
153 /// <returns>影响的记录数</returns>
154 public static int ExecuteSql(string SQLString, string content)
155 {
156 using (SqlConnection connection = new SqlConnection(connectionString))
157 {
158 SqlCommand cmd = new SqlCommand(SQLString, connection);
159 System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);
160 myParameter.Value = content;
161 cmd.Parameters.Add(myParameter);
162 try
163 {
164 connection.Open();
165 int rows = cmd.ExecuteNonQuery();
166 return rows;
167 }
168 catch (System.Data.SqlClient.SqlException e)
169 {
170 throw e;
171 }
172 finally
173 {
174 cmd.Dispose();
175 connection.Close();
176 }
177 }
178 }
179 /// <summary>
180 /// 执行带一个存储过程参数的的SQL语句。
181 /// </summary>
182 /// <param name="SQLString">SQL语句</param>
183 /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
184 /// <returns>影响的记录数</returns>
185 public static object ExecuteSqlGet(string SQLString, string content)
186 {
187 using (SqlConnection connection = new SqlConnection(connectionString))
188 {
189 SqlCommand cmd = new SqlCommand(SQLString, connection);
190 System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);
191 myParameter.Value = content;
192 cmd.Parameters.Add(myParameter);
193 try
194 {
195 connection.Open();
196 object obj = cmd.ExecuteScalar();
197 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
198 {
199 return null;
200 }
201 else
202 {
203 return obj;
204 }
205 }
206 catch (System.Data.SqlClient.SqlException e)
207 {
208 throw e;
209 }
210 finally
211 {
212 cmd.Dispose();
213 connection.Close();
214 }
215 }
216 }
217
218 /// <summary>
219 /// 执行一条计算查询结果语句，返回查询结果（object）。
220 /// </summary>
221 /// <param name="SQLString">计算查询结果语句</param>
222 /// <returns>查询结果（object）</returns>
223 public static object GetSingle(string SQLString)
224 {
225 using (SqlConnection connection = new SqlConnection(connectionString))
226 {
227 using (SqlCommand cmd = new SqlCommand(SQLString, connection))
228 {
229 try
230 {
231 connection.Open();
232 object obj = cmd.ExecuteScalar();
233 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
234 {
235 return null;
236 }
237 else
238 {
239 return obj;
240 }
241 }
242 catch (System.Data.SqlClient.SqlException e)
243 {
244 connection.Close();
245 throw e;
246 }
247 }
248 }
249 }
250 public static object GetSingle(string SQLString, int Times)
251 {
252 using (SqlConnection connection = new SqlConnection(connectionString))
253 {
254 using (SqlCommand cmd = new SqlCommand(SQLString, connection))
255 {
256 try
257 {
258 connection.Open();
259 cmd.CommandTimeout = Times;
260 object obj = cmd.ExecuteScalar();
261 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
262 {
263 return null;
264 }
265 else
266 {
267 return obj;
268 }
269 }
270 catch (System.Data.SqlClient.SqlException e)
271 {
272 connection.Close();
273 throw e;
274 }
275 }
276 }
277 }
278
279 /// <summary>
280 /// 执行查询语句，返回DataSet
281 /// </summary>
282 /// <param name="SQLString">查询语句</param>
283 /// <returns>DataSet</returns>
284 public static DataSet Query(string SQLString)
285 {
286 using (SqlConnection connection = new SqlConnection(connectionString))
287 {
288 DataSet ds = new DataSet();
289 try
290 {
291 connection.Open();
292 SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
293 command.Fill(ds, "ds");
294 }
295 catch (System.Data.SqlClient.SqlException ex)
296 {
297 throw new Exception(ex.Message);
298 }
299 return ds;
300 }
301 }
302 /// <summary>
303 /// 查询并得到数据集DataSet
304 /// </summary>
305 /// <param name="SQLString"></param>
306 /// <param name="Times"></param>
307 /// <returns></returns>
308 public static DataSet Query(string SQLString, int Times)
309 {
310 using (SqlConnection connection = new SqlConnection(connectionString))
311 {
312 DataSet ds = new DataSet();
313 try
314 {
315 connection.Open();
316 SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
317 command.SelectCommand.CommandTimeout = Times;
318 command.Fill(ds, "ds");
319 }
320 catch (System.Data.SqlClient.SqlException ex)
321 {
322 throw new Exception(ex.Message);
323 }
324 return ds;
325 }
326 }
327
328 #endregion
329
330
331
332 }
浙公网安备 33010602011771号