1.using System;
2.using System.Collections.Generic;
3.using System.Text;
4.using System.Collections;
5.using System.Data;
6.using System.Data.SqlClient;
7.using System.Configuration;
8.namespace HelloWinForm.DBUtility
9.{
10. class SQLHelper
11. {
12. #region 通用方法
13. // 数据连接池
14. private SqlConnection con;
15. /// <summary>
16. /// 返回数据库连接字符串
17. /// </summary>
18. /// <returns></returns>
19. public static String GetSqlConnection()
20. {
21. String conn = ConfigurationManager.AppSettings["connectionString"].ToString();
22. return conn;
23. }
24. #endregion
25. #region 执行sql字符串
26. /// <summary>
27. /// 执行不带参数的SQL语句
28. /// </summary>
29. /// <param name="Sqlstr"></param>
30. /// <returns></returns>
31. public static int ExecuteSql(String Sqlstr)
32. {
33. String ConnStr = GetSqlConnection();
34. using (SqlConnection conn = new SqlConnection(ConnStr))
35. {
36. SqlCommand cmd = new SqlCommand();
37. cmd.Connection = conn;
38. cmd.CommandText = Sqlstr;
39. conn.Open();
40. cmd.ExecuteNonQuery();
41. conn.Close();
42. return 1;
43. }
44. }
45. /// <summary>
46. /// 执行带参数的SQL语句
47. /// </summary>
48. /// <param name="Sqlstr">SQL语句</param>
49. /// <param name="param">参数对象数组</param>
50. /// <returns></returns>
51. public static int ExecuteSql(String Sqlstr, SqlParameter[] param)
52. {
53. String ConnStr = GetSqlConnection();
54. using (SqlConnection conn = new SqlConnection(ConnStr))
55. {
56. SqlCommand cmd = new SqlCommand();
57. cmd.Connection = conn;
58. cmd.CommandText = Sqlstr;
59. cmd.Parameters.AddRange(param);
60. conn.Open();
61. cmd.ExecuteNonQuery();
62. conn.Close();
63. return 1;
64. }
65. }
66. /// <summary>
67. /// 返回DataReader
68. /// </summary>
69. /// <param name="Sqlstr"></param>
70. /// <returns></returns>
71. public static SqlDataReader ExecuteReader(String Sqlstr)
72. {
73. String ConnStr = GetSqlConnection();
74. SqlConnection conn = new SqlConnection(ConnStr);//返回DataReader时,是不可以用using()的
75. try
76. {
77. SqlCommand cmd = new SqlCommand();
78. cmd.Connection = conn;
79. cmd.CommandText = Sqlstr;
80. conn.Open();
81. return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);//关闭关联的Connection
82. }
83. catch //(Exception ex)
84. {
85. return null;
86. }
87. }
88. /// <summary>
89. /// 执行SQL语句并返回数据表
90. /// </summary>
91. /// <param name="Sqlstr">SQL语句</param>
92. /// <returns></returns>
93. public static DataTable ExecuteDt(String Sqlstr)
94. {
95. String ConnStr = GetSqlConnection();
96. using (SqlConnection conn = new SqlConnection(ConnStr))
97. {
98. SqlDataAdapter da = new SqlDataAdapter(Sqlstr, conn);
99. DataTable dt = new DataTable();
100. conn.Open();
101. da.Fill(dt);
102. conn.Close();
103. return dt;
104. }
105. }
106. /// <summary>
107. /// 执行SQL语句并返回DataSet
108. /// </summary>
109. /// <param name="Sqlstr">SQL语句</param>
110. /// <returns></returns>
111. public static DataSet ExecuteDs(String Sqlstr)
112. {
113. String ConnStr = GetSqlConnection();
114. using (SqlConnection conn = new SqlConnection(ConnStr))
115. {
116. SqlDataAdapter da = new SqlDataAdapter(Sqlstr, conn);
117. DataSet ds = new DataSet();
118. conn.Open();
119. da.Fill(ds);
120. conn.Close();
121. return ds;
122. }
123. }
124. #endregion
125. #region 操作存储过程
126. /// <summary>
127. /// 运行存储过程(已重载)
128. /// </summary>
129. /// <param name="procName">存储过程的名字</param>
130. /// <returns>存储过程的返回值</returns>
131. public int RunProc(string procName)
132. {
133. SqlCommand cmd = CreateCommand(procName, null);
134. cmd.ExecuteNonQuery();
135. this.Close();
136. return (int)cmd.Parameters["ReturnValue"].Value;
137. }
138. /// <summary>
139. /// 运行存储过程(已重载)
140. /// </summary>
141. /// <param name="procName">存储过程的名字</param>
142. /// <param name="prams">存储过程的输入参数列表</param>
143. /// <returns>存储过程的返回值</returns>
144. public int RunProc(string procName, SqlParameter[] prams)
145. {
146. SqlCommand cmd = CreateCommand(procName, prams);
147. cmd.ExecuteNonQuery();
148. this.Close();
149. return (int)cmd.Parameters[0].Value;
150. }
151. /// <summary>
152. /// 运行存储过程(已重载)
153. /// </summary>
154. /// <param name="procName">存储过程的名字</param>
155. /// <param name="dataReader">结果集</param>
156. public void RunProc(string procName, out SqlDataReader dataReader)
157. {
158. SqlCommand cmd = CreateCommand(procName, null);
159. dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
160. }
161. /// <summary>
162. /// 运行存储过程(已重载)
163. /// </summary>
164. /// <param name="procName">存储过程的名字</param>
165. /// <param name="prams">存储过程的输入参数列表</param>
166. /// <param name="dataReader">结果集</param>
167. public void RunProc(string procName, SqlParameter[] prams, out SqlDataReader dataReader)
168. {
169. SqlCommand cmd = CreateCommand(procName, prams);
170. dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
171. }
172. /// <summary>
173. /// 创建Command对象用于访问存储过程
174. /// </summary>
175. /// <param name="procName">存储过程的名字</param>
176. /// <param name="prams">存储过程的输入参数列表</param>
177. /// <returns>Command对象</returns>
178. private SqlCommand CreateCommand(string procName, SqlParameter[] prams)
179. {
180. // 确定连接是打开的
181. Open();
182. //command = new SqlCommand( sprocName, new SqlConnection( ConfigManager.DALConnectionString ) );
183. SqlCommand cmd = new SqlCommand(procName, con);
184. cmd.CommandType = CommandType.StoredProcedure;
185. // 添加存储过程的输入参数列表
186. if (prams != null)
187. {
188. foreach (SqlParameter parameter in prams)
189. cmd.Parameters.Add(parameter);
190. }
191. // 返回Command对象
192. return cmd;
193. }
194. /// <summary>
195. /// 创建输入参数
196. /// </summary>
197. /// <param name="ParamName">参数名</param>
198. /// <param name="DbType">参数类型</param>
199. /// <param name="Size">参数大小</param>
200. /// <param name="Value">参数值</param>
201. /// <returns>新参数对象</returns>
202. public SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value)
203. {
204. return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
205. }
206. /// <summary>
207. /// 创建输出参数
208. /// </summary>
209. /// <param name="ParamName">参数名</param>
210. /// <param name="DbType">参数类型</param>
211. /// <param name="Size">参数大小</param>
212. /// <returns>新参数对象</returns>
213. public SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size)
214. {
215. return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null);
216. }
217. /// <summary>
218. /// 创建存储过程参数
219. /// </summary>
220. /// <param name="ParamName">参数名</param>
221. /// <param name="DbType">参数类型</param>
222. /// <param name="Size">参数大小</param>
223. /// <param name="Direction">参数的方向(输入/输出)</param>
224. /// <param name="Value">参数值</param>
225. /// <returns>新参数对象</returns>
226. public SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
227. {
228. SqlParameter param;
229. if (Size > 0)
230. {
231. param = new SqlParameter(ParamName, DbType, Size);
232. }
233. else
234. {
235. param = new SqlParameter(ParamName, DbType);
236. }
237. param.Direction = Direction;
238. if (!(Direction == ParameterDirection.Output && Value == null))
239. {
240. param.Value = Value;
241. }
242. return param;
243. }
244. #endregion
245. #region 数据库连接和关闭
246. /// <summary>
247. /// 打开连接池
248. /// </summary>
249. private void Open()
250. {
251. // 打开连接池
252. if (con == null)
253. {
254. //这里不仅需要using System.Configuration;还要在引用目录里添加
255. con = new SqlConnection(GetSqlConnection());
256. con.Open();
257. }
258. }
259. /// <summary>
260. /// 关闭连接池
261. /// </summary>
262. public void Close()
263. {
264. if (con != null)
265. con.Close();
266. }
267. /// <summary>
268. /// 释放连接池
269. /// </summary>
270. public void Dispose()
271. {
272. // 确定连接已关闭
273. if (con != null)
274. {
275. con.Dispose();
276. con = null;
277. }
278. }
279. #endregion
280. }
281.}