C#操作数据库常⽤的SqlDbHelper asp 项⽬基本上都是有数据库服务⽀持的，这就需要有⼀个⽐较常⽤的类⽀持⽂件。闲话不多说，直接上代码  1using System;
2using System.Collections.Generic;
3using System.Linq;
4using System.Web;
5using System.Data.SqlClient;
6using System.Data;
7using System.Configuration;
8
9namespace ImportExcel
10 {
11public class SqlDbHelper
12    {
13///<summary>
14///连接字符串
15///</summary>
16public 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>
24public static int ExecuteNonQuery(string safeSql)
25        {
26using (SqlConnection Connection = new SqlConnection(connectionString))
27            {
28                Connection.Open();
29                SqlTransaction trans = Connection.BeginTransaction();
30try
31                {
32                    SqlCommand cmd = new SqlCommand(safeSql, Connection);
33                    cmd.Transaction = trans;
34
35if (Connection.State != ConnectionState.Open)
36                    {
37                        Connection.Open();
38                    }
39int result = cmd.ExecuteNonQuery();
40                    trans.Commit();
41return result;
42                }
43catch
44                {
45                    trans.Rollback();
46return0;
47                }
48            }
49        }
50
51///<summary>
52///对数据库执⾏增、删、改命令
53///</summary>
54///<param name="sql">T-Sql语句</param>
55///<param name="values">参数数组</param>
56///<returns>受影响的记录数</returns>
57public static int ExecuteNonQuery(string sql, SqlParameter[] values)
58        {
59using (SqlConnection Connection = new SqlConnection(connectionString))
60            {
61                Connection.Open();
62                SqlTransaction trans = Connection.BeginTransaction();
63try
64                {
65                    SqlCommand cmd = new SqlCommand(sql, Connection);
66                    cmd.Transaction = trans;
67                    cmd.Parameters.AddRange(values);
68if (Connection.State != ConnectionState.Open)
69                    {
70                        Connection.Open();
71                    }
72int result = cmd.ExecuteNonQuery();
73                    trans.Commit();
74return result;
75                }
76catch (Exception ex)
77                {
78                    trans.Rollback();
79return0;
80                }
81            }
82        }
83#endregion
84
85#region ExecuteScalar命令
86///<summary>
87///查询结果集中第⼀⾏第⼀列的值
88///</summary>
89///<param name="safeSql">T-Sql语句</param>
90///<returns>第⼀⾏第⼀列的值</returns>
91public static int ExecuteScalar(string safeSql)
92        {
93using (SqlConnection Connection = new SqlConnection(connectionString))
94            {
95if (Connection.State != ConnectionState.Open)
96                    Connection.Open();
97                SqlCommand cmd = new SqlCommand(safeSql, Connection);
98int result = Convert.ToInt32(cmd.ExecuteScalar());
99return result;
100            }
101        }
102
103///<summary>
104///查询结果集中第⼀⾏第⼀列的值
105///</summary>
106///<param name="sql">T-Sql语句</param>
107///<param name="values">参数数组</param>
108///<returns>第⼀⾏第⼀列的值</returns>
109public static int ExecuteScalar(string sql, SqlParameter[] values)
110        {
111using (SqlConnection Connection = new SqlConnection(connectionString))
112            {
113if (Connection.State != ConnectionState.Open)
114                    Connection.Open();
115                SqlCommand cmd = new SqlCommand(sql, Connection);
116                cmd.Parameters.AddRange(values);
117int result = Convert.ToInt32(cmd.ExecuteScalar());
118return 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>
130public static SqlDataReader ExecuteReader(string safeSql, SqlConnection Connection)
131        {
132if (Connection.State != ConnectionState.Open)
133                Connection.Open();
134            SqlCommand cmd = new SqlCommand(safeSql, Connection);
135            SqlDataReader reader = cmd.ExecuteReader();
136return 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>
146public static SqlDataReader ExecuteReader(string sql, SqlParameter[] values, SqlConnection Connection) 147        {
148if (Connection.State != ConnectionState.Open)
149                Connection.Open();
150            SqlCommand cmd = new SqlCommand(sql, Connection);
151            cmd.Parameters.AddRange(values);
152            SqlDataReader reader = cmd.ExecuteReader();
153return 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>
165public static DataTable ExecuteDataTable(CommandType type, string safeSql, params SqlParameter[] values) 166        {
167using (SqlConnection Connection = new SqlConnection(connectionString))
168            {
169if (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);
176return ds.Tables[0];
177            }
178        }
179
180///<summary>
181///执⾏指定数据库连接对象的命令，指定存储过程参数，返回DataTable
182///</summary>
183///<param name="safeSql">T-Sql语句</param>
184///<returns>结果集DataTable</returns>
185public static DataTable ExecuteDataTable(string safeSql)
186        {
187using (SqlConnection Connection = new SqlConnection(connectionString))
188            {
189if (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);
194try
195                {
196                    da.Fill(ds);
197                }
198catch (Exception ex)
199                {
200
201                }
202return 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>
212public static DataTable ExecuteDataTable(string sql, params SqlParameter[] values)
213        {
214using (SqlConnection Connection = new SqlConnection(connectionString))
215            {
216if (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);
sqltransaction什么意思222                SqlDataAdapter da = new SqlDataAdapter(cmd);
223                da.Fill(ds);
224return 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>
237public static DataSet GetDataSet(string safeSql, string tabName, params SqlParameter[] values)
238        {
239using (SqlConnection Connection = new SqlConnection(connectionString))
240            {
241if (Connection.State != ConnectionState.Open)
242                    Connection.Open();
243                DataSet ds = new DataSet();
244                SqlCommand cmd = new SqlCommand(safeSql, Connection);
245
246if (values != null)
247                    cmd.Parameters.AddRange(values);
248
249                SqlDataAdapter da = new SqlDataAdapter(cmd);
250try
251                {
252                    da.Fill(ds, tabName);
253                }
254catch (Exception ex)
255                {
256
257                }
258return 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>
270public static int ExecureData(DataSet ds, string strTblName)
271        {
272try
273            {
274//创建⼀个数据库连接
275using (SqlConnection Connection = new SqlConnection(connectionString))
276                {
277if (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
289return myAdapter.Update(ds, strTblName);  //更新ds数据
290                }
291
292            }
293catch (Exception err)
294            {
295throw err;
296            }
297        }
298
299#endregion
300    }
301 }

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。