C# 操作数据库常用的 SqlDbHelper
原博客园转载于 https://www.cnblogs.com/felix-wang/p/6742785.html
1 using System;
2 using System.Collections.Generic;
3 using System.Linq;
4 using System.Web;
5 using System.Data.SqlClient;
6 using System.Data;
7 using System.Configuration;
8
9 namespace ImportExcel
10 {
11 public class SqlDbHelper
12 {
13 /// <summary>
14 /// 连接字符串
15 /// </summary>
16 public static readonly string connectionString = ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;
17
18 #region ExecuteNonQuery命令
19 /// <summary>
20 /// 对数据库执行增、删、改命令
21 /// </summary>
22 /// <param name="safeSql">T-Sql语句</param>
23 /// <returns>受影响的记录数</returns>
24 public static int ExecuteNonQuery(string safeSql)
25 {
26 using (SqlConnection Connection = new SqlConnection(connectionString))
27 {
28 Connection.Open();
29 SqlTransaction trans = Connection.BeginTransaction();
30 try
31 {
32 SqlCommand cmd = new SqlCommand(safeSql, Connection);
33 cmd.Transaction = trans;
34
35 if (Connection.State != ConnectionState.Open)
36 {
37 Connection.Open();
38 }
39 int result = cmd.ExecuteNonQuery();
40 trans.Commit();
41 return result;
42 }
43 catch
44 {
45 trans.Rollback();
46 return 0;
47 }
48 }
49 }
50
51 /// <summary>
52 /// 对数据库执行增、删、改命令
53 /// </summary>
54 /// <param name="sql">T-Sql语句</param>
55 /// <param name="values">参数数组</param>
56 /// <returns>受影响的记录数</returns>
57 public static int ExecuteNonQuery(string sql, SqlParameter[] values)
58 {
59 using (SqlConnection Connection = new SqlConnection(connectionString))
60 {
61 Connection.Open();
62 SqlTransaction trans = Connection.BeginTransaction();
63 try
64 {
65 SqlCommand cmd = new SqlCommand(sql, Connection);
66 cmd.Transaction = trans;
67 cmd.Parameters.AddRange(values);
68 if (Connection.State != ConnectionState.Open)
69 {
70 Connection.Open();
71 }
72 int result = cmd.ExecuteNonQuery();
73 trans.Commit();
74 return result;
75 }
76 catch (Exception ex)
77 {
78 trans.Rollback();
79 return 0;
80 }
81 }
82 }
83 #endregion
84
85 #region ExecuteScalar命令
86 /// <summary>
87 /// 查询结果集中第一行第一列的值
88 /// </summary>
89 /// <param name="safeSql">T-Sql语句</param>
90 /// <returns>第一行第一列的值</returns>
91 public static int ExecuteScalar(string safeSql)
92 {
93 using (SqlConnection Connection = new SqlConnection(connectionString))
94 {
95 if (Connection.State != ConnectionState.Open)
96 Connection.Open();
97 SqlCommand cmd = new SqlCommand(safeSql, Connection);
98 int result = Convert.ToInt32(cmd.ExecuteScalar());
99 return result;
100 }
101 }
102
103 /// <summary>
104 /// 查询结果集中第一行第一列的值
105 /// </summary>
106 /// <param name="sql">T-Sql语句</param>
107 /// <param name="values">参数数组</param>
108 /// <returns>第一行第一列的值</returns>
109 public static int ExecuteScalar(string sql, SqlParameter[] values)
110 {
111 using (SqlConnection Connection = new SqlConnection(connectionString))
112 {
113 if (Connection.State != ConnectionState.Open)
114 Connection.Open();
115 SqlCommand cmd = new SqlCommand(sql, Connection);
116 cmd.Parameters.AddRange(values);
117 int result = Convert.ToInt32(cmd.ExecuteScalar());
118 return result;
119 }
120 }
121 #endregion
122
123 #region ExecuteReader命令
124 /// <summary>
125 /// 创建数据读取器
126 /// </summary>
127 /// <param name="safeSql">T-Sql语句</param>
128 /// <param name="Connection">数据库连接</param>
129 /// <returns>数据读取器对象</returns>
130 public static SqlDataReader ExecuteReader(string safeSql, SqlConnection Connection)
131 {
132 if (Connection.State != ConnectionState.Open)
133 Connection.Open();
134 SqlCommand cmd = new SqlCommand(safeSql, Connection);
135 SqlDataReader reader = cmd.ExecuteReader();
136 return reader;
137 }
138
139 /// <summary>
140 /// 创建数据读取器
141 /// </summary>
142 /// <param name="sql">T-Sql语句</param>
143 /// <param name="values">参数数组</param>
144 /// <param name="Connection">数据库连接</param>
145 /// <returns>数据读取器</returns>
146 public static SqlDataReader ExecuteReader(string sql, SqlParameter[] values, SqlConnection Connection)
147 {
148 if (Connection.State != ConnectionState.Open)
149 Connection.Open();
150 SqlCommand cmd = new SqlCommand(sql, Connection);
151 cmd.Parameters.AddRange(values);
152 SqlDataReader reader = cmd.ExecuteReader();
153 return reader;
154 }
155 #endregion
156
157 #region ExecuteDataTable命令
158 /// <summary>
159 /// 执行指定数据库连接对象的命令,指定存储过程参数,返回DataTable
160 /// </summary>
161 /// <param name="type">命令类型(T-Sql语句或者存储过程)</param>
162 /// <param name="safeSql">T-Sql语句或者存储过程的名称</param>
163 /// <param name="values">参数数组</param>
164 /// <returns>结果集DataTable</returns>
165 public static DataTable ExecuteDataTable(CommandType type, string safeSql, params SqlParameter[] values)
166 {
167 using (SqlConnection Connection = new SqlConnection(connectionString))
168 {
169 if (Connection.State != ConnectionState.Open)
170 Connection.Open();
171 DataSet ds = new DataSet();
172 SqlCommand cmd = new SqlCommand(safeSql, Connection);
173 cmd.CommandType = type;
174 SqlDataAdapter da = new SqlDataAdapter(cmd);
175 da.Fill(ds);
176 return ds.Tables[0];
177 }
178 }
179
180 /// <summary>
181 /// 执行指定数据库连接对象的命令,指定存储过程参数,返回DataTable
182 /// </summary>
183 /// <param name="safeSql">T-Sql语句</param>
184 /// <returns>结果集DataTable</returns>
185 public static DataTable ExecuteDataTable(string safeSql)
186 {
187 using (SqlConnection Connection = new SqlConnection(connectionString))
188 {
189 if (Connection.State != ConnectionState.Open)
190 Connection.Open();
191 DataSet ds = new DataSet();
192 SqlCommand cmd = new SqlCommand(safeSql, Connection);
193 SqlDataAdapter da = new SqlDataAdapter(cmd);
194 try
195 {
196 da.Fill(ds);
197 }
198 catch (Exception ex)
199 {
200
201 }
202 return ds.Tables[0];
203 }
204 }
205
206 /// <summary>
207 /// 执行指定数据库连接对象的命令,指定存储过程参数,返回DataTable
208 /// </summary>
209 /// <param name="sql">T-Sql语句</param>
210 /// <param name="values">参数数组</param>
211 /// <returns>结果集DataTable</returns>
212 public static DataTable ExecuteDataTable(string sql, params SqlParameter[] values)
213 {
214 using (SqlConnection Connection = new SqlConnection(connectionString))
215 {
216 if (Connection.State != ConnectionState.Open)
217 Connection.Open();
218 DataSet ds = new DataSet();
219 SqlCommand cmd = new SqlCommand(sql, Connection);
220 cmd.CommandTimeout = 0;
221 cmd.Parameters.AddRange(values);
222 SqlDataAdapter da = new SqlDataAdapter(cmd);
223 da.Fill(ds);
224 return ds.Tables[0];
225 }
226 }
227 #endregion
228
229 #region GetDataSet命令
230 /// <summary>
231 /// 取出数据
232 /// </summary>
233 /// <param name="safeSql">sql语句</param>
234 /// <param name="tabName">DataTable别名</param>
235 /// <param name="values"></param>
236 /// <returns></returns>
237 public static DataSet GetDataSet(string safeSql, string tabName, params SqlParameter[] values)
238 {
239 using (SqlConnection Connection = new SqlConnection(connectionString))
240 {
241 if (Connection.State != ConnectionState.Open)
242 Connection.Open();
243 DataSet ds = new DataSet();
244 SqlCommand cmd = new SqlCommand(safeSql, Connection);
245
246 if (values != null)
247 cmd.Parameters.AddRange(values);
248
249 SqlDataAdapter da = new SqlDataAdapter(cmd);
250 try
251 {
252 da.Fill(ds, tabName);
253 }
254 catch (Exception ex)
255 {
256
257 }
258 return ds;
259 }
260 }
261 #endregion
262
263 #region ExecureData 命令
264 /// <summary>
265 /// 批量修改数据
266 /// </summary>
267 /// <param name="ds">修改过的DataSet</param>
268 /// <param name="strTblName">表名</param>
269 /// <returns></returns>
270 public static int ExecureData(DataSet ds, string strTblName)
271 {
272 try
273 {
274 //创建一个数据库连接
275 using (SqlConnection Connection = new SqlConnection(connectionString))
276 {
277 if (Connection.State != ConnectionState.Open)
278 Connection.Open();
279
280 //创建一个用于填充DataSet的对象
281 SqlCommand myCommand = new SqlCommand("SELECT * FROM " + strTblName, Connection);
282 SqlDataAdapter myAdapter = new SqlDataAdapter();
283 //获取SQL语句，用于在数据库中选择记录
284 myAdapter.SelectCommand = myCommand;
285
286 //自动生成单表命令，用于将对DataSet所做的更改与数据库更改相对应
287 SqlCommandBuilder myCommandBuilder = new SqlCommandBuilder(myAdapter);
288
289 return myAdapter.Update(ds, strTblName); //更新ds数据
290 }
291
292 }
293 catch (Exception err)
294 {
295 throw err;
296 }
297 }
298
299 #endregion
300 }
301 }
