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