当前文件路径：AcomStore/DBUtility/SqlHelper.cs 
1
using System;
2using System.Data;
3using System.Data.SqlClient;
4using System.Configuration;
5//该源码下载自www.51aspx.com(５１ａｓｐｘ．ｃｏｍ)
6
7namespace AcomLb.DBUtility
8
...{
9
public class SqlHelper
10
...{
11 private readonly string SqlConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
12 private SqlConnection cn; //创建SQL连接
13 private SqlDataAdapter sda; //创建SQL数据适配器
14 private SqlDataReader sdr; //创建SQL数据读取器
15 private SqlCommand cmd; //创建SQL命令对象
16 private SqlParameter param; //创建SQL参数
17 private DataSet ds; //创建数据集
18 private DataView dv; //创建视图
19
20 /**//// <summary>
21 /// 实例化数据连接字符串
22 /// </summary>
23
/// <returns>返回数据连接字符串</returns>
24 public SqlConnection GetConn()
25 ...{
26 SqlConnection cn = new SqlConnection(SqlConnectionString);
27 return cn;
28 }
29 /**//// <summary>
30 /// 返回数据连接字符串
31 /// </summary>
32 /// <returns></returns>
33 public string GetConnStr()
34 ...{
35 return SqlConnectionString;
36 }
37
38 /**//// <summary>
39 /// 打开数据库连接
40 /// </summary>
41 public void Open()
42 ...{
43 ...#region
44 cn = new SqlConnection(SqlConnectionString);
45 cn.Open();
46 #endregion
47 }
48
49 /**//// <summary>
50 /// 关闭数据库连接
51 /// </summary>
52 public void Close()
53 ...{
54 ...#region
55 if (cn != null)
56 ...{
57 cn.Close();
58 cn.Dispose();
59 }
60 #endregion
61 }
62
63 /**//// <summary>
64 /// 返回DataSet数据集
65 /// </summary>
66 /// <param name="strSql">SQL语句</param>
67 public DataSet GetDs(string strSql)
68 ...{
69 ...#region
70 Open();
71 sda = new SqlDataAdapter(strSql, cn);
72 ds = new DataSet();
73 sda.Fill(ds);
74 Close();
75 return ds;
76 #endregion
77 }
78
79 public DataSet GetDs(string strSql,string TableName)
80 ...{
81 ...#region
82 Open();
83 sda = new SqlDataAdapter(strSql, cn);
84 DataSet Ds = new DataSet();
85 sda.Fill(Ds, TableName);
86 Close();
87 return Ds;
88 #endregion
89 }
90
91 /**//// <summary>
92 /// 添加DataSet表
93 /// </summary>
94 /// <param name="ds">DataSet对象</param>
95 /// <param name="strSql">Sql语句</param>
96 /// <param name="strTableName">表名</param>
97 public void GetDs(DataSet ds, string strSql, string strTableName)
98 ...{
99 ...#region
100 Open();
101 sda = new SqlDataAdapter(strSql, cn);
102 sda.Fill(ds, strTableName);
103 Close();
104 #endregion
105 }
106
107
108 /**//// <summary>
109 /// 返回DataView数据视图
110 /// </summary>
111 /// <param name="strSql">Sql语句</param>
112 public DataView GetDv(string strSql)
113 ...{
114 ...#region
115 dv = GetDs(strSql).Tables[0].DefaultView;
116 return dv;
117 #endregion
118 }
119
120
121 /**//// <summary>
122 /// 获得DataTable对象
123 /// </summary>
124 /// <param name="strSql">SQL语句</param>
125 /// <returns></returns>
126 public DataTable GetTable(string strSql)
127 ...{
128 ...#region
129 return GetDs(strSql).Tables[0];
130 #endregion
131 }
132
133
134 /**//// <summary>
135 /// 获得SqlDataReader对象 使用完须关闭DataReader,关闭数据库连接
136 /// </summary>
137 /// <param name="strSql">sql语句</param>
138 /// <returns></returns>
139 public SqlDataReader GetDataReader(string strSql)
140 ...{
141 ...#region
142 Open();
143 cmd = new SqlCommand(strSql, cn);
144 sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
145 return sdr;
146 #endregion
147 }
148
149
150 /**//// <summary>
151 /// 执行Sql语句
152 /// </summary>
153 /// <param name="strSql"></param>
154 public bool RunSql(string strSql)
155 ...{
156 ...#region
157
158 try
159 ...{
160 Open();
161 cmd = new SqlCommand(strSql, cn);
162 cmd.ExecuteNonQuery();
163 return true;
164 }
165 catch
166 ...{
167 return false;
168 }
169 finally
170 ...{
171 Close();
172 }
173
174 #endregion
175 }
176
177
178 /**//// <summary>
179 /// 执行两条SQL语句，实现数据库事务。
180 /// </summary>
181 /// <param name="SQLString1"></param>
182 /// <param name="SQLString2"></param>
183 public void ExecuteSqlTran(string SQLString1, string SQLString2)
184 ...{
185 执行两条SQL语句，实现数据库事务#region 执行两条SQL语句，实现数据库事务
186 using (SqlConnection connection = GetConn() )
187 ...{
188 connection.Open();
189 SqlCommand cmd = new SqlCommand();
190 cmd.Connection = connection;
191 SqlTransaction tx = connection.BeginTransaction();
192 cmd.Transaction = tx;
193 try
194 ...{
195 cmd.CommandText = SQLString1;
196 cmd.ExecuteNonQuery();
197 cmd.CommandText = SQLString2;
198 cmd.ExecuteNonQuery();
199 tx.Commit();
200 }
201 catch (System.Data.SqlClient.SqlException E)
202 ...{
203 tx.Rollback();
204 throw new Exception(E.Message);
205 }
206 finally
207 ...{
208 cmd.Dispose();
209 connection.Close();
210 }
211 }
212 #endregion
213 }
214
215 /**//// <summary>
216 /// 执行多条SQL语句，实现数据库事务，每条语句以“;”分割。
217 /// </summary>
218 /// <param name="SQLStringList"></param>
219 public void ExecuteSqlTran(string SQLStringList)
220 ...{
221 执行多条SQL语句，实现数据库事务，每条语句以“;”分割#region 执行多条SQL语句，实现数据库事务，每条语句以“;”分割
222 using (SqlConnection conn = GetConn())
223 ...{
224 conn.Open();
225 SqlCommand cmd = new SqlCommand();
226 cmd.Connection = conn;
227 SqlTransaction tx = conn.BeginTransaction();
228 cmd.Transaction = tx;
229 try
230 ...{
231 string[] split = SQLStringList.Split(new Char[] ...{ ';' });
232 foreach (string strsql in split)
233 ...{
234 if (strsql.Trim() != "")
235 ...{
236 cmd.CommandText = strsql;
237 cmd.ExecuteNonQuery();
238 }
239 }
240 tx.Commit();
241 }
242 catch (SqlException E)
243 ...{
244 tx.Rollback();
245 throw new Exception(E.Message);
246 }
247 }
248 #endregion
249 }
250
251 /**//// <summary>
252 /// 执行SQL不返回任何值
253 /// </summary>
254 /// <param name="strSql"></param>
255 public void RunSqlStr(string strSql)
256 ...{
257 执行SQL不返回任何值#region 执行SQL不返回任何值
258 try
259 ...{
260 Open();
261 cmd = new SqlCommand(strSql, cn);
262 cmd.ExecuteNonQuery();
263 }
264 catch
265 ...{
266 throw;
267 }
268 finally
269 ...{
270 Close();
271 }
272 #endregion
273 }
274
275
276 /**//// <summary>
277 /// 执行SQL语句，并返回第一行第一列结果
278 /// </summary>
279 /// <param name="strSql">SQL语句</param>
280 /// <returns></returns>
281 public
