1 using System;
2 using System.Data;
3 using System.Data.SqlClient;
4 using System.Configuration;
5 using System.Web.UI.WebControls;
6 //using Shaka.FileOperator;
7 namespace Shaka.Database
8 {
9 /// <summary>
10 /// SQLHelper 的摘要说明。
11 /// </summary>
12 public class SQLHelper
13 {
14 //数据库连接串
15 public string ConnString = ConfigurationSettings.AppSettings["ConnString"];
16 private SqlConnection conn;
17 private SqlCommand cmd;
18 private SqlDataAdapter myAdapter;
19 private DataSet ds;
20 public string filename;
21 public int filesize;
22
23 public SQLHelper()
24 {
25 Open();
26 }
27 /// <summary>
28 /// 打开数据库连接
29 /// </summary>
30 public void Open()
31 {
32 if (conn == null)
33 {
34 conn = new SqlConnection(ConnString);
35 try
36 {
37 conn.Open();
38 }
39 catch(Exception e )
40 {
41 throw e;
42 }
43 }
44 }
45
46 /// <summary>
47 /// 关闭数据库连接
48 /// </summary>
49 public void Close()
50 {
51 if (conn.State == ConnectionState.Open)
52 conn.Close();
53 conn.Dispose();
54 conn = null;
55 }
56
57 /// <summary>
58 /// 创建命令对象,调用存储过程
59 /// </summary>
60 /// <param name="procName">存储过程名称.</param>
61 /// <param name="prams">存储过程参数.</param>
62 /// <returns>返回命令对象.</returns>
63 private SqlCommand CreateCommand(string procName, SqlParameter[] prams)
64 {
65 // 确保连接是打开的
66 Open();
67 cmd = new SqlCommand(procName, conn);
68 cmd.CommandType = CommandType.StoredProcedure;
69
70 // 给存储过程添加参数
71 if (prams != null)
72 {
73 foreach (SqlParameter parameter in prams)
74 cmd.Parameters.Add(parameter);
75 }
76
77 // 返回参数
78 cmd.Parameters.Add(
79 new SqlParameter("ReturnValue", SqlDbType.Int, 4,
80 ParameterDirection.ReturnValue, false, 0, 0,
81 string.Empty, DataRowVersion.Default, null));
82
83 return cmd;
84 }
85
86
87 /// <summary>
88 /// 运行存储过程
89 /// </summary>
90 /// <param name="procName"></param>
91 /// <returns></returns>
92 public int RunProc(string procName)
93 {
94 cmd = CreateCommand(procName, null);
95 cmd.ExecuteNonQuery();
96 return (int)cmd.Parameters["ReturnValue"].Value;
97 }
98
99 /// <summary>
100 /// 运行带参数的存储过程
101 /// </summary>
102 /// <param name="procName">存储过程名称.</param>
103 /// <param name="prams">存储过程参数.</param>
104 /// <returns>返回值.</returns>
105 public int RunProc(string procName, SqlParameter[] prams)
106 {
107 cmd = CreateCommand(procName, prams);
108 cmd.ExecuteNonQuery();
109 return (int)cmd.Parameters["ReturnValue"].Value;
110 }
111
112 /// <summary>
113 /// 运行存储过程
114 /// </summary>
115 /// <param name="procName">存储过程名称.</param>
116 /// <param name="dataReader">返回值.</param>
117 public void RunProc(string procName, out SqlDataReader dataReader)
118 {
119 cmd = CreateCommand(procName, null);
120 dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
121 }
122
123 /// <summary>
124 /// 运行存储过程
125 /// </summary>
126 /// <param name="procName">存储过程名称.</param>
127 /// <param name="prams">存储过程参数.</param>
128 /// <param name="dataReader">返回值.</param>
129 public void RunProc(string procName, SqlParameter[] prams, out SqlDataReader dataReader)
130 {
131 cmd = CreateCommand(procName, prams);
132 dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
133 }
134
135 public SqlDataAdapter GetAdatper(string procName,SqlParameter[] prams)
136 {
137 cmd = CreateCommand(procName, prams);
138 myAdapter = new SqlDataAdapter(cmd);
139 return myAdapter;
140 }
141
142
143 /// <summary>
144 /// 生成存储过程参数
145 /// </summary>
146 /// <param name="ParamName">参数名称.</param>
147 /// <param name="DbType">参数类型.</param>
148 /// <param name="Size">参数大小.</param>
149 /// <param name="Direction">参数方向.</param>
150 /// <param name="Value">参数值.</param>
151 /// <returns>返回新参数.</returns>
152 public SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
153 {
154 SqlParameter param;
155
156 if(Size > 0)
157 param = new SqlParameter(ParamName, DbType, Size);
158 else
159 param = new SqlParameter(ParamName, DbType);
160
161 param.Direction = Direction;
162 if (!(Direction == ParameterDirection.Output && Value == null))
163 param.Value = Value;
164
165 return param;
166 }
167
168
169 /// <summary>
170 /// 生成输入参数
171 /// </summary>
172 /// <param name="ParamName">参数名称.</param>
173 /// <param name="DbType">参数类型.</param>
174 /// <param name="Size">参数大小.</param>
175 /// <param name="Value">参数值.</param>
176 /// <returns>返回新参数.</returns>
177 public SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value)
178 {
179 return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
180 }
181
182 /// <summary>
183 /// 生成输出参数.
184 /// </summary>
185 /// <param name="ParamName">参数名称.</param>
186 /// <param name="DbType">参数类型.</param>
187 /// <param name="Size">参数大小.</param>
188 /// <returns>返回新参数.</returns>
189 public SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size)
190 {
191 return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null);
192 }
193
194 /// <summary>
195 /// 执行单条SQL语句
196 /// </summary>
197 /// <param name="strSql"></param>
198 /// <returns></returns>
199 public bool ExecSql(string strSql)
200 {
201 bool Result = false;
202 Open();
203 cmd = new SqlCommand(strSql,conn);
204 try
205 {
206 cmd.ExecuteNonQuery();
207 Result = true;
208 }
209 catch
210 {
211 }
212 cmd.Dispose();
213 return Result;
214 }
215
216 /// <summary>
217 /// 执行多条语句
218 /// </summary>
219 /// <param name="strSql"></param>
220 /// <returns></returns>
221 public bool ExecSql(string[] strSql)
222 {
223 bool Result = false;
224 if (strSql != null)
225 {
226 Open();
227 cmd = new SqlCommand();
228
229 SqlTransaction tr ;
230 tr = conn.BeginTransaction();
231 cmd.Connection = conn;
232 cmd.Transaction = tr;
233 try
234 {
235 foreach (string Sql in strSql)
236 {
237 cmd.CommandText = Sql;
238 cmd.ExecuteNonQuery();
239 }
240 tr.Commit();
241 Result = true;
242 }
243 catch
244 {
245 tr.Rollback();
246 Close();
247 throw;
248 }
249 }
250 return Result;
251 }
252
253 /// <summary>
254 /// 得到DateSet记录集
255 /// </summary>
256 /// <param name="strSQL"></param>
257 /// <returns></returns>
258 public DataSet GetDateSet(string strSQL)
259 {
260 Open();
261 myAdapter = new SqlDataAdapter(strSQL,conn);
262 ds = new DataSet();
263 myAdapter.Fill(ds);
264 return ds;
265 }
266
267 /// <summary>
268 /// 绑定DataGrid
269 /// </summary>
270 /// <param name="strSQL"></param>
271 /// <param name="mydatagrid"></param>
272 public void BindDataGrid(string strSQL,DataGrid mydatagrid)
273 {
274 ds = GetDateSet(strSQL);
275 mydatagrid.DataSource=ds.Tables[0].DefaultView;
276 mydatagrid.DataBind();
277 }
278
279 /// <summary>
280 /// 得到DataReader
281 /// </summary>
282 /// <param name="strSQL"></param>
283 /// <returns></returns>
284 public SqlDataReader GetReader(string strSQL)
285 {
286 cmd = new SqlCommand(strSQL,conn);
287 return cmd.ExecuteReader();
288 }
289 /// <summary>
290 /// 得到DataReader关闭数据库连接
291 /// </summary>
292 /// <param name="strSQL"></param>
293 /// <returns></returns>
294 public SqlDataReader GetReaderCloseDb(string strSQL)
295 {
296 cmd = new SqlCommand(strSQL,conn);
297 return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
298 }
299
300 /// <summary>
301 /// Get Scalar
302 /// </summary>
303 /// <param name="strSQL"></param>
304 /// <returns></returns>
305 public object GetScalar(string strSQL)
306 {
307 cmd = new SqlCommand(strSQL,conn);
308 return cmd.ExecuteScalar();
309 }
310
311
312 }
313 }
314
315
316
转载于:https://www.cnblogs.com/gaolei/archive/2009/01/01/1366463.html