1 //webconfig配置文件
2 <connectionStrings>
3 <add name="ConnectionString" connectionString="Data Source=.;pwd=123456;Initial Catalog=gjfj;User ID=sa" providerName="System.Data.SqlClient"/>
4 </connectionStrings>
5 <appSettings>
6 <add key="ConnectionString" value="server=.;database=gjfj;uid=sa;pwd=123456;"/>
7 </appSettings>
8
9
10 //DBHelper.cs
11 public static class DBHelper
12 {
13 //数据库连接属性,从config配置文件中获取连接字符串connectionString
14 private static string connectionString = ConfigurationManager.AppSettings["ConnectionString"].ToString();//数据库连接字符串
15
16 private static SqlConnection connection;
17 public static SqlConnection Connection
18 {
19
20
21 get
22 {
23 string connectionString = ConfigurationManager.AppSettings["ConnectionString"].ToString();//数据库连接字符串
24 // string connectionString = " server=.;database=ezwell;uid=sa;pwd=123456;";//数据库连接字符串
25
26 if (connection == null)
27 {
28 using (connection = new SqlConnection(connectionString))
29 {
30 connection.Open();
31 }
32 }
33 else if (connection.State == System.Data.ConnectionState.Closed)
34 {
35 connection.Open();
36 }
37 else if (connection.State == System.Data.ConnectionState.Broken)
38 {
39 connection.Close();
40 connection.Open();
41 }
42 return connection;
43 }
44 }
45 /// <summary>
46 /// 关闭数据库连接
47 /// </summary>
48 public static void Close()
49 {
50 ///判断连接是否已经创建
51 if (connection != null)
52 {
53 ///判断连接的状态是否打开
54 if (connection.State == ConnectionState.Open)
55 {
56 connection.Close();
57 }
58 }
59 }
60
61 /// <summary>
62 /// 释放资源
63 /// </summary>
64
65 public static void Dispose()
66 {
67 // 确认连接是否已经关闭
68 if (connection != null)
69 {
70 connection.Dispose();
71 connection = null;
72 }
73 }
74
75 /// <summary>
76 /// 执行无参SQL语句，并返回执行记录数
77 /// </summary>
78 /// <param name="safeSql">sql字符串</param>
79 /// <returns>受影响的行数</returns>
80 public static int ExecuteCommand(string safeSql)
81 {
82 SqlConnection sqlConn = new SqlConnection(connectionString);
83 sqlConn.Open();
84 SqlCommand cmd = new SqlCommand(safeSql, sqlConn);
85 int result = cmd.ExecuteNonQuery();
86 sqlConn.Close();
87 return result;
88 }
89 /// <summary>
90 /// 执行有参SQL语句，并返回执行记录数
91 /// </summary>
92 /// <param name="safeSql">sql字符串</param>
93 /// <param name="values">参数值</param>
94 /// <returns>受影响的行数</returns>
95 public static int ExecuteCommand(string sql, params SqlParameter[] values)
96 {
97 SqlConnection sqlConn = new SqlConnection(connectionString);
98 sqlConn.Open();
99 SqlCommand cmd = new SqlCommand(sql, sqlConn);
100 cmd.Parameters.AddRange(values);
101 int result = cmd.ExecuteNonQuery();
102 sqlConn.Close();
103 return result;
104 }
105 /// <summary>
106 /// 执行无参存储过程，并返回受影响的行数。
107 /// </summary>
108 /// <param name="safeSql">存储过程名</param>
109 /// <returns>受影响的行数</returns>
110 public static int ExecuteProcCommand(string safeSql)
111 {
112 SqlConnection sqlConn = new SqlConnection(connectionString);
113 sqlConn.Open();
114 SqlCommand cmd = new SqlCommand(safeSql, sqlConn);
115 cmd.CommandType = CommandType.StoredProcedure;
116 int result = cmd.ExecuteNonQuery();
117 sqlConn.Close();
118 return result;
119 }
120 /// <summary>
121 /// 执行带参存储过程，并返回受影响的行数。
122 /// </summary>
123 /// <param name="safeSql">存储过程名</param>
124 /// <param name="values">存储过程参数值</param>
125 /// <returns>受影响的行数</returns>
126 public static int ExecuteProcCommand(string safeSql, params SqlParameter[] values)
127 {
128 SqlConnection sqlConn = new SqlConnection(connectionString);
129 sqlConn.Open();
130
131 SqlCommand cmd = new SqlCommand(safeSql, sqlConn);
132 cmd.CommandType = CommandType.StoredProcedure;
133 cmd.Parameters.AddRange(values);
134 int result = cmd.ExecuteNonQuery();
135 sqlConn.Close();
136 return result;
137 }
138 /// <summary>
139 /// 执行无参SQL语句，并返回首行首列数据。
140 /// </summary>
141 /// <param name="safeSql">sql字符串</param>
142 /// <returns>首行首列数据</returns>
143 public static int ExecuteGetScalar(string safeSql)
144 {
145 SqlConnection sqlConn = new SqlConnection(connectionString);
146 sqlConn.Open();
147
148 SqlCommand cmd = new SqlCommand(safeSql, sqlConn);
149 int result = Convert.ToInt32(cmd.ExecuteScalar());
150 sqlConn.Close();
151 return result;
152 }
153 /// <summary>
154 /// 执行有参SQL语句，并返回首行首列数据。
155 /// </summary>
156 /// <param name="safeSql">sql字符串</param>
157 /// <param name="values">参数值</param>
158 /// <returns>首行首列数据</returns>
159 public static int ExecuteGetScalar(string sql, params SqlParameter[] values)
160 {
161 SqlConnection sqlConn = new SqlConnection(connectionString);
162 sqlConn.Open();
163 SqlCommand cmd = new SqlCommand(sql, sqlConn);
164 cmd.Parameters.AddRange(values);
165 int result = Convert.ToInt32(cmd.ExecuteScalar());
166 sqlConn.Close();
167 return result;
168 }
169 /// <summary>
170 /// 执行无参存储过程，并返回首行首列数据。
171 /// </summary>
172 /// <param name="safeSql">存储过程名</param>
173 /// <returns>首行首列数据</returns>
174 public static int ExecuteProcGetScalar(string safeSql)
175 {
176 SqlConnection sqlConn = new SqlConnection(connectionString);
177 sqlConn.Open();
178 SqlCommand cmd = new SqlCommand(safeSql, sqlConn);
179 cmd.CommandType = CommandType.StoredProcedure;
180 int result = Convert.ToInt32(cmd.ExecuteScalar());
181 sqlConn.Close();
182 return result;
183 }
184 /// <summary>
185 /// 执行有参存储过程，并返回首行首列数据。
186 /// </summary>
187 /// <param name="safeSql">存储过程名</param>
188 /// <param name="values">参数值</param>
189 /// <returns>首行首列数据</returns>
190 public static int ExecuteProcGetScalar(string sql, params SqlParameter[] values)
191 {
192 SqlConnection sqlConn = new SqlConnection(connectionString);
193
194 sqlConn.Open();
195 SqlCommand cmd = new SqlCommand(sql, sqlConn);
196 cmd.CommandType = CommandType.StoredProcedure;
197 cmd.Parameters.AddRange(values);
198 int result = Convert.ToInt32(cmd.ExecuteScalar());
199 sqlConn.Close();
200 return result;
201
202 }
203 /// <summary>
204 /// 执行无参SQL语句，并返回SqlDataReader
205 /// </summary>
206 /// <param name="safeSql">sql字符串</param>
207 /// <returns>返回DataReader</returns>
208 public static SqlDataReader ExecuteGetReader(string safeSql)
209 {
210 SqlConnection sqlConn = new SqlConnection(connectionString);
211 {
212 sqlConn.Open();
213 SqlCommand cmd = new SqlCommand(safeSql, sqlConn);
214 try
215 {
216 SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
217 return reader;
218 }
219 catch
220 {
221 sqlConn.Close();
222 throw;
223 }
224
225 }
226 }
227 /// <summary>
228 /// 执行有参SQL语句，并返回SqlDataReader
229 /// </summary>
230 /// <param name="safeSql">sql字符串</param>
231 /// <param name="values">参数值</param>
232 /// <returns>返回DataReader</returns>
233 public static SqlDataReader ExecuteGetReader(string sql, params SqlParameter[] values)
234 {
235 SqlConnection sqlConn = new SqlConnection(connectionString);
236
237 sqlConn.Open();
238 SqlCommand cmd = new SqlCommand(sql, sqlConn);
239 cmd.Parameters.AddRange(values);
240 try
241 {
242 SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
243 return reader;
244 }
245 catch
246 {
247 sqlConn.Close();
248 throw;
249 }
250
251
252 }
253
254 /// <summary>
255 /// 执行无参存储过程，并返回SqlDataReader
256 /// </summary>
257 /// <param name="safeSql">存储过程名</param>
258 /// <returns>返回DataReader</returns>
259 public static SqlDataReader ExecuteProcGetReader(string safeSql)
260 {
261 SqlConnection sqlConn = new SqlConnection(connectionString);
262
263 sqlConn.Open();
264 SqlCommand cmd = new SqlCommand(safeSql, sqlConn);
265 cmd.CommandType = CommandType.StoredProcedure;
266 try
267 {
268 SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
269 return reader;
270 }
271 catch
272 {
273 sqlConn.Close();
274 throw;
275 }
276
277
278 }
279 /// <summary>
280 /// 执行有参存储过程，并返回SqlDataReader
281 /// </summary>
282 /// <param name="safeSql">存储过程名</param>
283 /// <param name="values">参数值</param>
284 /// <returns>返回DataReader</returns>
285 public static SqlDataReader ExecuteProcGetReader(string sql, params SqlParameter[] values)
286 {
287 SqlConnection sqlConn = new SqlConnection(connectionString);
288
289 sqlConn.Open();
290 SqlCommand cmd = new SqlCommand(sql, sqlConn);
291 cmd.CommandType = CommandType.StoredProcedure;
292 cmd.Parameters.AddRange(values);
293
294 try
295 {
296 SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
297
298 return reader;
299 }
300 catch
301 {
302 sqlConn.Close();
303 throw;
304 }
305
306 }
307
308
309 public static SqlDataReader ExecuteProcGetReader2(string sql, params SqlParameter[] values)
310 {
311 string connectionString = ConfigurationManager.AppSettings["HuachenManagerConnectionString"].ToString();//数据库连接字符串
312 SqlConnection connection2 = new SqlConnection(connectionString);
313 connection2.Open();
314
315 SqlCommand cmd = new SqlCommand(sql, connection2);
316 cmd.CommandType = CommandType.StoredProcedure;
317 cmd.Parameters.AddRange(values);
318 try
319 {
320 SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
321
322 return reader;
323 }
324 catch
325 {
326 connection2.Close();
327 throw;
328 }
329 }
330
331
332
333 /// <summary>
334 /// 执行无参存储过程，并返回DataTable对象
335 /// </summary>
336 /// <param name="safeSql">存储过程名</param>
337 /// <returns>返回DataTable</returns>
338 public static DataTable ExecuteProcGetDataTable(string safeSql)
339 {
340 SqlConnection sqlConn = new SqlConnection(connectionString);
341 sqlConn.Open();
342 DataSet ds = new DataSet();
343 SqlCommand cmd = new SqlCommand(safeSql, sqlConn);
344 cmd.CommandType = CommandType.StoredProcedure;
345 SqlDataAdapter da = new SqlDataAdapter(cmd);
346 da.Fill(ds);
347 sqlConn.Close();
348 return ds.Tables[0];
349
350 }
351
352 /// <summary>
353 /// 执行SQL语句，并返回DataTable对象
354 /// </summary>
355 /// <param name="safeSql">SQL语句</param>
356 /// <returns>返回DataTable</returns>
357 public static DataTable ExecuteSqlGetDataTable(string safeSql)
358 {
359 SqlConnection sqlConn = new SqlConnection(connectionString);
360 sqlConn.Open();
361 DataSet ds = new DataSet();
362 SqlCommand cmd = new SqlCommand(safeSql, sqlConn);
363 cmd.CommandType = CommandType.Text;
364 SqlDataAdapter da = new SqlDataAdapter(cmd);
365 da.Fill(ds);
366 sqlConn.Close();
367 return ds.Tables[0];
368
369 }
370
371
372 /// <summary>
373 /// 执行无参存储过程，并返回DataTable对象
374 /// </summary>
375 /// <param name="safeSql">存储过程名</param>
376 /// <param name="values">参数值</param>
377 /// <returns>返回DataTable对象</returns>
378 public static DataTable ExecuteProcGetDataTable(string safeSql, params SqlParameter[] values)
379 {
380 SqlConnection sqlConn = new SqlConnection(connectionString);
381 sqlConn.Open();
382 DataSet ds = new DataSet();
383 SqlCommand cmd = new SqlCommand(safeSql, sqlConn);
384 cmd.CommandType = CommandType.StoredProcedure;
385 cmd.Parameters.AddRange(values);
386 SqlDataAdapter da = new SqlDataAdapter(cmd);
387 da.Fill(ds);
388 sqlConn.Close();
389 return ds.Tables[0];
390
391 }
392 }
393
394 //调用方法
395 public static int BidDoc_Add(BidDoc bidDoc)
396 {
397 procname = "dbo.BidDoc_Add";
398 SqlParameter[] prams = {
399 new SqlParameter("@BidID",SqlDbType.Int),
400 new SqlParameter("@EmployeeID",SqlDbType.Int),
401 new SqlParameter("@EmployeeList",SqlDbType.VarChar,50),
402 new SqlParameter("@SendEmployeeID",SqlDbType.Int)
403
404 };
405
406 prams[0].Value = bidDoc.BidID;
407 prams[1].Value = bidDoc.EmployeeID;
408 prams[2].Value = bidDoc.EmployeeList;
409
410
411
412 return DBHelper.ExecuteProcGetScalar(procname, prams);
413 }
