1 using System;
2 using System.Collections.Generic;
3 using System.Linq;
4 using System.Web;
5 using System.Configuration;
6 using System.Data.SqlClient;
7 using System.Data;
8
9 namespace APMS.Test
10 {
11 /// <summary>
12 /// DBHelper.cs
13 /// </summary>
14 public class DBHelper
15 {
16 private static string ConnectionString = ConfigurationManager.AppSettings["DbHelperConnectionString"];
17 private static SqlConnection conn;
18
19 public DBHelper()
20 {
21
22 }
23
24 /// <summary>
25 /// 创建SqlConnection连接
26 /// </summary>
27 /// <returns>返回SqlConnection对象</returns>
28 public static SqlConnection CreateConnection()
29 {
30 conn = new SqlConnection (ConnectionString);
31 conn.Open();
32 return conn;
33 }
34
35 /// <summary>
36 /// 执行存储过程
37 /// </summary>
38 /// <param name="storedProcedure">存储过程语句</param>
39 /// <returns>返回SqlCommand对象</returns>
40 public SqlCommand GetStoredProcCommand(string storedProcedure)
41 {
42 SqlCommand cmd = new SqlCommand (storedProcedure,conn);
43 cmd.CommandType= CommandType.StoredProcedure;
44 return cmd;
45 }
46 /// <summary>
47 /// 执行Sql语句
48 /// </summary>
49 /// <param name="sqlQuery">Sql语句</param>
50 /// <returns>返回SqlCommand对象</returns>
51 public SqlCommand GetSqlStringCommand(string sqlQuery)
52 {
53 SqlCommand cmd = new SqlCommand (sqlQuery,conn);
54 cmd.CommandType= CommandType.Text;
55 return cmd;
56 }
57
58 #region 增加参数
59 /// <summary>
60 /// 增加参数
61 /// </summary>
62 /// <param name="cmd">cmd对象</param>
63 /// <param name="ParametersCollection">SqlParameterCollection数据集合</param>
64 public void AddParameterCollection(SqlCommand cmd, SqlParameterCollection ParametersCollection)
65 {
66 foreach (SqlParameter Parameters in ParametersCollection)
67 {
68 cmd.Parameters.Add(Parameters);
69 }
70 }
71 /// <summary>
72 /// 增加参数
73 /// </summary>
74 /// <param name="cmd">cmd对象</param>
75 /// <param name="parameterName">parameterName</param>
76 /// <param name="sqlType">类型</param>
77 /// <param name="size">大小</param>
78 public void AddInParameter(SqlCommand cmd, string parameterName, SqlDbType sqlType, int size)
79 {
80 SqlParameter parameters = cmd.CreateParameter();
81 parameters.SqlDbType = sqlType;
82 parameters.ParameterName = parameterName;
83 parameters.Size = size;
84 parameters.Direction = ParameterDirection.Input;
85 cmd.Parameters.Add(parameters);
86 }
87
88 public void AddInParameter(SqlCommand cmd, string parameterName, SqlDbType sqlType, object value)
89 {
90 SqlParameter parameters = cmd.CreateParameter();
91 parameters.SqlDbType = sqlType;
92 parameters.ParameterName = parameterName;
93 parameters.Value = value;
94 parameters.Direction = ParameterDirection.Input;
95 cmd.Parameters.Add(parameters);
96 }
97 public void AddInParameter(SqlCommand cmd, string parameterName, SqlDbType sqlType)
98 {
99 SqlParameter parameters = cmd.CreateParameter();
100 parameters.SqlDbType = sqlType;
101 parameters.ParameterName = parameterName;
102 parameters.Direction= ParameterDirection.Input;
103 cmd.Parameters.Add(parameters);
104 }
105 public SqlParameter GetParameter(SqlCommand cmd, string parameterName)
106 {
107 return cmd.Parameters[parameterName];
108 }
109
110 #endregion
111
112 #region 执行
113 /// <summary>
114 /// 执行SqlCommand命令，返回DataSet对象
115 /// </summary>
116 /// <param name="cmd">SqlCommand对象</param>
117 /// <returns>返回DataSet对象</returns>
118 public DataSet ExecuteDataSet(SqlCommand cmd)
119 {
120 SqlDataAdapter da = new SqlDataAdapter (cmd);
121 DataSet ds = new DataSet ();
122 da.Fill(ds);
123 return ds;
124 }
125 /// <summary>
126 /// 执行SqlCommand命令，返回DataTable对象
127 /// </summary>
128 /// <param name="cmd">SqlCommand命令</param>
129 /// <returns>返回DataTable对象</returns>
130 public DataTable ExecuteDataTable(SqlCommand cmd)
131 {
132 SqlDataAdapter da = new SqlDataAdapter (cmd);
133 DataTable dt = new DataTable ();
134 da.Fill(dt);
135 return dt;
136 }
137 /// <summary>
138 /// 执行SqlCommand命令，返回SqlDataReader对象
139 /// </summary>
140 /// <param name="cmd">SqlCommand命令</param>
141 /// <returns>返回SqlDataReader对象</returns>
142 public SqlDataReader ExecuteReader(SqlCommand cmd)
143 {
144 SqlConnection conn = DBHelper.CreateConnection();
145 SqlDataReader reader = cmd.ExecuteReader();
146 return reader;
147 }
148 /// <summary>
149 /// 执行SqlCommand命令，返回Int
150 ///</summary>
151 /// <param name="cmd">SqlCommand命令</param>
152 /// <returns>返回Int</returns>
153 public int ExecuteNonQuery(SqlCommand cmd)
154 {
155 SqlConnection sqlconn = DBHelper.CreateConnection();
156 int ret = cmd.ExecuteNonQuery();
157 sqlconn.Close();
158 return ret;
159 }
160
161 /// <summary>
162 /// 执行SqlCommand命令，返回Object
163 /// </summary>
164 /// <param name="cmd">SqlCommand命令</param>
165 /// <returns>返回Object</returns>
166 public object ExecuteScalar(SqlCommand cmd)
167 {
168 SqlConnection sqlconn = DBHelper.CreateConnection();
169 object ret = cmd.ExecuteScalar();
170 sqlconn.Close();
171 return ret;
172 }
173 #endregion
174
175 /*********************示例****************************************
176 1)直接执行sql语句
177
178 DbHelper db = new DbHelper();
179 DbCommand cmd = db.GetSqlStringCommond("insert t1 (id)values(‘haha‘)");
180 db.ExecuteNonQuery(cmd);
181 2)执行存储过程
182
183 DbHelper db = new DbHelper();
184 DbCommand cmd = db.GetStoredProcCommond("t1_insert");
185 db.AddInParameter(cmd, "@id", DbType.String, "heihei");
186 db.ExecuteNonQuery(cmd);
187 3)返回DataSet
188
189 DbHelper db = new DbHelper();
190 DbCommand cmd = db.GetSqlStringCommond("select * from t1");
191 DataSet ds = db.ExecuteDataSet(cmd);
192 4)返回DataTable
193
194 DbHelper db = new DbHelper();
195 DbCommand cmd = db.GetSqlStringCommond("t1_findall");
196 DataTable dt = db.ExecuteDataTable(cmd);
197 5)输入参数/输出参数/返回值的使用(比较重要哦)
198
199 DbHelper db = new DbHelper();
200 DbCommand cmd = db.GetStoredProcCommond("t2_insert");
201 db.AddInParameter(cmd, "@timeticks", DbType.Int64, DateTime.Now.Ticks);
202 db.AddOutParameter(cmd, "@outString", DbType.String, 20);
203 db.AddReturnParameter(cmd, "@returnValue", DbType.Int32);
204
205 db.ExecuteNonQuery(cmd);
206
207 string s = db.GetParameter(cmd, "@outString").Value as string;//out parameter
208 int r = Convert.ToInt32(db.GetParameter(cmd, "@returnValue").Value);//return value
209
210 6)DataReader使用
211
212 DbHelper db = new DbHelper();
213 DbCommand cmd = db.GetStoredProcCommond("t2_insert");
214 db.AddInParameter(cmd, "@timeticks", DbType.Int64, DateTime.Now.Ticks);
215 db.AddOutParameter(cmd, "@outString", DbType.String, 20);
216 db.AddReturnParameter(cmd, "@returnValue", DbType.Int32);
217
218 using (DbDataReader reader = db.ExecuteReader(cmd))
219 {
220 dt.Load(reader);
221 }
222 string s = db.GetParameter(cmd, "@outString").Value as string;//out parameter
223 int r = Convert.ToInt32(db.GetParameter(cmd, "@returnValue").Value);//return value
224
225
226 7)事务的使用.(项目中需要将基本的数据库操作组合成一个完整的业务流时,代码级的事务是必不可少的哦)
227
228 以上我们好像没有指定数据库连接字符串,大家如果看下DbHelper的代码,就知道要使用它必须在config中配置两个参数,如下:
229
230 pubic void DoBusiness()
231 {
232 using (Trans t = new Trans())
233 {
234 try
235 {
236 D1(t);
237 throw new Exception();//如果有异常,会回滚滴
238 D2(t);
239 t.Commit();
240 }
241 catch
242 {
243 t.RollBack();
244 }
245 }
246 }
247 public void D1(Trans t)
248 {
249 DbHelper db = new DbHelper();
250 DbCommand cmd = db.GetStoredProcCommond("t2_insert");
251 db.AddInParameter(cmd, "@timeticks", DbType.Int64, DateTime.Now.Ticks);
252 db.AddOutParameter(cmd, "@outString", DbType.String, 20);
253 db.AddReturnParameter(cmd, "@returnValue", DbType.Int32);
254
255 if (t == null) db.ExecuteNonQuery(cmd);
256 else db.ExecuteNonQuery(cmd,t);
257
258 string s = db.GetParameter(cmd, "@outString").Value as string;//out parameter
259 int r = Convert.ToInt32(db.GetParameter(cmd, "@returnValue").Value);//return value
260 }
261 public void D2(Trans t)
262 {
263 DbHelper db = new DbHelper();
264 DbCommand cmd = db.GetSqlStringCommond("insert t1 (id)values(‘..‘)");
265 if (t == null) db.ExecuteNonQuery(cmd);
266 else db.ExecuteNonQuery(cmd, t);
267 }
268 <appSettings>
269 <add key="DbHelperProvider" value="System.Data.SqlClient"/>
270 <add key="DbHelperConnectionString" value="Data Source=(local);Initial Catalog=DbHelperTest;Persist Security Info=True;User ID=sa;Password=sa"/>
271 appSettings>* */
272 }
273 }
274
DBHelper.cs
