DBHelper类.docx
- 文档编号:4086585
- 上传时间:2022-11-27
- 格式:DOCX
- 页数:21
- 大小:20.93KB
DBHelper类.docx
《DBHelper类.docx》由会员分享,可在线阅读,更多相关《DBHelper类.docx(21页珍藏版)》请在冰豆网上搜索。
DBHelper类
1.using System;
2.using System.Collections.Generic;
3.using System.Linq;
4.using System.Text;
5.using System.Data.SqlClient;
6.using System.Collections;
7.using System.Data;
8.using System.Configuration;
9.using System.Web;
10.
11.
12.public sealed class SqlHelper
13.{
14.
15. public static string connectionString = ConfigurationManager.ConnectionStrings["db_JXCconn"].ConnectionString;
16.
17. public SqlHelper()
18. {
19. }
20.
21. #region 公用方法
22. public static int GetMaxID(string FieldName, string TableName)
23. {
24. string strsql = "select max(" + FieldName + ")+1 from " + TableName;
25. object obj = SqlHelper.GetSingle(strsql);
26. if (obj == null)
27. {
28. return 1;
29. }
30. else
31. {
32. return int.Parse(obj.ToString());
33. }
34. }
35.
36. public static bool Exists(string strSql)
37. {
38. object obj = SqlHelper.GetSingle(strSql);
39. int cmdresult;
40. if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
41. {
42. cmdresult = 0;
43. }
44. else
45. {
46. cmdresult = int.Parse(obj.ToString());
47. }
48. if (cmdresult == 0)
49. {
50. return false;
51. }
52. else
53. {
54. return true;
55. }
56. }
57.
58. public static bool Exists(string strSql, params SqlParameter[] cmdParms)
59. {
60. object obj = SqlHelper.GetSingle(strSql, cmdParms);
61. int cmdresult;
62. if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
63. {
64. cmdresult = 0;
65. }
66. else
67. {
68. cmdresult = int.Parse(obj.ToString());
69. }
70. if (cmdresult == 0)
71. {
72. return false;
73. }
74. else
75. {
76. return true;
77. }
78. }
79. #endregion
80.
81. #region 执行简单SQL语句
82. ///
83. /// 执行SQL语句,返回影响的记录数
84. ///
85. /// SQL语句
86. ///
87. public static int ExecuteSql(string SQLString)
88. {
89. using (SqlConnection connection = new SqlConnection(connectionString))
90. {
91. using (SqlCommand cmd = new SqlCommand(SQLString, connection))
92. {
93. try
94. {
95. connection.Open();
96. int rows = cmd.ExecuteNonQuery();
97. return rows;
98. }
99. catch (System.Data.SqlClient.SqlException E)
100. {
101. connection.Close();
102. throw new Exception(E.Message);
103. }
104. }
105. }
106. }
107.
108. ///
109. /// 执行SQL语句,返回影响的记录数 适用于select语句
110. ///
111. /// SQL语句
112. ///
113. public static int ExecuteSql2(string SQLString)
114. {
115. using (SqlConnection connection = new SqlConnection(connectionString))
116. {
117. using (SqlCommand cmd = new SqlCommand(SQLString, connection))
118. {
119. try
120. {
121. connection.Open();
122. int rows = Convert.ToInt32(cmd.ExecuteScalar());
123. return rows;
124. }
125. catch (System.Data.SqlClient.SqlException E)
126. {
127. connection.Close();
128. throw new Exception(E.Message);
129. }
130. }
131. }
132. }
133.
134. ///
135. /// 执行多条SQL语句,实现数据库事务。
136. ///
137. /// 多条SQL语句
138. public static void ExecuteSqlTran(ArrayList SQLStringList)
139. {
140. using (SqlConnection conn = new SqlConnection(connectionString))
141. {
142. conn.Open();
143. SqlCommand cmd = new SqlCommand();
144. cmd.Connection = conn;
145. SqlTransaction tx = conn.BeginTransaction();
146. cmd.Transaction = tx;
147. try
148. {
149. for (int n = 0; n < SQLStringList.Count; n++)
150. {
151. string strsql = SQLStringList[n].ToString();
152. if (strsql.Trim().Length > 1)
153. {
154. cmd.CommandText = strsql;
155. cmd.ExecuteNonQuery();
156. }
157. }
158. tx.Commit();
159. }
160. catch (System.Data.SqlClient.SqlException E)
161. {
162. tx.Rollback();
163. throw new Exception(E.Message);
164. }
165. }
166. }
167.
168. ///
169. /// 执行带一个存储过程参数的的SQL语句。
170. ///
171. /// SQL语句
172. /// 参数内容,比如一个字段是格式复杂的文章,有特殊符号,可以通过这个方式添加
173. ///
174. public static int ExecuteSql(string SQLString, string content)
175. {
176. using (SqlConnection connection = new SqlConnection(connectionString))
177. {
178. SqlCommand cmd = new SqlCommand(SQLString, connection);
179. System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.VarChar);
180.
181. myParameter.Value = content;
182. cmd.Parameters.Add(myParameter);
183. try
184. {
185. connection.Open();
186. int rows = cmd.ExecuteNonQuery();
187. return rows;
188. }
189. catch (System.Data.SqlClient.SqlException E)
190. {
191. throw new Exception(E.Message);
192. }
193. finally
194. {
195. cmd.Dispose();
196. connection.Close();
197. }
198. }
199. }
200.
201. ///
202. /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
203. ///
204. /// SQL语句
205. /// 图像字节,数据库的字段类型为image的情况
206. ///
207. public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
208. {
209. using (SqlConnection connection = new SqlConnection(connectionString))
210. {
211. SqlCommand cmd = new SqlCommand(strSQL, connection);
212. System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@fs", SqlDbType.Binary);
213. myParameter.Value = fs;
214. cmd.Parameters.Add(myParameter);
215. try
216. {
217. connection.Open();
218. int rows = cmd.ExecuteNonQuery();
219. return rows;
220. }
221. catch (System.Data.SqlClient.SqlException E)
222. {
223. throw new Exception(E.Message);
224. }
225. finally
226. {
227. cmd.Dispose();
228. connection.Close();
229. }
230. }
231. }
232.
233. ///
234. /// 执行一条计算查询结果语句,返回查询结果(object)。
235. ///
236. /// 计算查询结果语句
237. ///
238. public static object GetSingle(string SQLString)
239. {
240. using (SqlConnection connection = new SqlConnection(connectionString))
241. {
242. using (SqlCommand cmd = new SqlCommand(SQLString, connection))
243. {
244. try
245. {
246. connection.Open();
247. object obj = cmd.ExecuteScalar();
248. if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
249. {
250. return null;
251. }
252. else
253. {
254. return obj;
255. }
256. }
257. catch (System.Data.SqlClient.SqlException e)
258. {
259. connection.Close();
260. throw new Exception(e.Message);
261. }
262. }
263. }
264. }
265.
266. ///
267. /// 执行查询语句,返回SqlDataReader
268. ///
269. /// 查询语句
270. ///
271. public static SqlDataReader ExecuteReader(string strSQL)
272. {
273. SqlConnection connection = new SqlConnection(connectionString);
274. SqlCommand cmd = new SqlCommand(strSQL, connection);
275. try
276. {
277. connection.Open();
278. SqlDataReader myReader = cmd.ExecuteReader();
279.
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- DBHelper