VS连接数据库的通⽤⽅法（SQLMySql）在vs⾥⾯连接数据库的⽅法有很多，这⾥是通⽤的⽅法和基本操作
SQL
1///<summary>
2///数据访问抽象基础类
3/// Copyright (C) Maticsoft
4///</summary>
5public abstract class DbHelperSQL
6 {
7public static int DataType = 1;
8//数据库连接字符串(fig来配置)，多数据库可使⽤DbHelperSQLP来实现.
9public static string connectionString = ConfigurationManager.ConnectionStrings["ReadContext"].ConnectionString;
10public DbHelperSQL()
11 {
12if(DataType==2)
13 connectionString = ConfigurationManager.ConnectionStrings["ReadContextMySql"].ConnectionString;
14 }
15
16#region公⽤⽅法
17///<summary>
18///判断是否存在某表的某个字段
19///</summary>
20///<param name="tableName">表名称</param>
21///<param name="columnName">列名称</param>
22///<returns>是否存在</returns>
23public static bool ColumnExists(string tableName, string columnName)
24 {
25string sql = "select count(1) from syscolumns where [id]=object_id('" + tableName + "') and [name]='" + columnName + "'";
26object res = GetSingle(sql);
27if (res == null)
28 {
29return false;
30 }
31return Convert.ToInt32(res) > 0;
32 }
33public static int GetMaxID(string FieldName, string TableName)
34 {
35string strsql = "select max(" + FieldName + ")+1 from " + TableName;
36object obj = GetSingle(strsql);
37if (obj == null)
38 {
39return1;
40 }
41else
42 {
43return int.Parse(obj.ToString());
44 }
45 }
46public static bool Exists(string strSql)
47 {
48object obj = GetSingle(strSql);
49return ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) ? false : true;
50 }
51///<summary>
52///表是否存在
53///</summary>
54///<param name="TableName"></param>
55///<returns></returns>
56public static bool TabExists(string TableName)
57 {
58string strsql = "select count(*) from sysobjects where id = object_id(N'[" + TableName + "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1";
59//string strsql = "SELECT count(*) FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + TableName + "]') AND type in (N'U')";
60object obj = GetSingle(strsql);
61int cmdresult;
62if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
63 {
64 cmdresult = 0;
65 }
66else
67 {
68 cmdresult = int.Parse(obj.ToString());
69 }
70if (cmdresult == 0)
71 {
72return false;
73 }
74else
75 {
76return true;
77 }
78 }
79public static bool Exists(string strSql, params SqlParameter[] cmdParms)
80 {
81object obj = GetSingle(strSql, cmdParms);
82int cmdresult;
83if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
84 {
85 cmdresult = 0;
86 }
87else
88 {
89 cmdresult = int.Parse(obj.ToString());
90 }
91if (cmdresult == 0)
92 {
93return false;
94 }
95else
96 {
97return true;
98 }
99 }
100#endregion
101
102#region执⾏简单SQL语句
103
104///<summary>
105///执⾏SQL语句，返回影响的记录数
106///</summary>
107///<param name="SQLString">SQL语句</param>
108///<returns>影响的记录数</returns>
109public static int ExecuteSql(string SQLString)
110 {
111using (SqlConnection connection = new SqlConnection(connectionString))
112 {
113using (SqlCommand cmd = new SqlCommand(SQLString, connection))
114 {
115try
117 connection.Open();
118int rows = cmd.ExecuteNonQuery();
119return rows;
120 }
121catch (System.Data.SqlClient.SqlException e)
122 {
123
124 connection.Close();
125throw e;
126 }
127 }
128 }
129 }
130
131public static int ExecuteSqlByTime(string SQLString, int Times)
132 {
133using (SqlConnection connection = new SqlConnection(connectionString))
134 {
135using (SqlCommand cmd = new SqlCommand(SQLString, connection))
136 {
137try
138 {
139 connection.Open();
140 cmd.CommandTimeout = Times;
141int rows = cmd.ExecuteNonQuery();
142return rows;
143 }
144catch (System.Data.SqlClient.SqlException e)
145 {
146
147 connection.Close();
148throw e;
149 }
150 }
151 }
152 }
153///<summary>
154///执⾏多条SQL语句，实现数据库事务。
155///</summary>
156///<param name="SQLStringList">多条SQL语句</param>
157public static int ExecuteSqlTran(List<String> SQLStringList)
158 {
159using (SqlConnection conn = new SqlConnection(connectionString))
160 {
161 conn.Open();
162 SqlCommand cmd = new SqlCommand();
163 cmd.Connection = conn;
164 SqlTransaction tx = conn.BeginTransaction();
165 cmd.Transaction = tx;
166try
167 {
168int count = 0;
169for (int n = 0; n < SQLStringList.Count; n++)
170 {
171string strsql = SQLStringList[n];
172if (strsql.Trim().Length > 1)
173 {
174 cmd.CommandText = strsql;
175 count += cmd.ExecuteNonQuery();
176 }
177 }
178 tx.Commit();
179return count;
180 }
181catch(Exception e)
182 {
183 tx.Rollback();
184return0;
185 }
186 }
187 }
188///<summary>
189///执⾏带⼀个存储过程参数的的SQL语句。
190///</summary>
191///<param name="SQLString">SQL语句</param>
192///<param name="content">参数内容,⽐如⼀个字段是格式复杂的⽂章，有特殊符号，可以通过这个⽅式添加</param>
193///<returns>影响的记录数</returns>
194public static int ExecuteSql(string SQLString, string content)
195 {
196using (SqlConnection connection = new SqlConnection(connectionString))
197 {
198 SqlCommand cmd = new SqlCommand(SQLString, connection);
199 System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText); 200 myParameter.Value = content;
201 cmd.Parameters.Add(myParameter);
202try
203 {
204 connection.Open();
205int rows = cmd.ExecuteNonQuery();
206return rows;
207 }
208catch (System.Data.SqlClient.SqlException e)
209 {
210
211throw e;
212 }
213finally
214 {
215 cmd.Dispose();
216 connection.Close();
217 }
218 }
219 }
220///<summary>
221///执⾏带⼀个存储过程参数的的SQL语句。
222///</summary>
223///<param name="SQLString">SQL语句</param>
224///<param name="content">参数内容,⽐如⼀个字段是格式复杂的⽂章，有特殊符号，可以通过这个⽅式添加</param>
225///<returns>影响的记录数</returns>
226public static object ExecuteSqlGet(string SQLString, string content)
227 {
228using (SqlConnection connection = new SqlConnection(connectionString))
229 {
230 SqlCommand cmd = new SqlCommand(SQLString, connection);
231 System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText); 232 myParameter.Value = content;
233 cmd.Parameters.Add(myParameter);
234try
235 {
236 connection.Open();
237object obj = cmd.ExecuteScalar();
238if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
240return null;
241 }
242else
243 {
244return obj;
245 }
246 }
247catch (System.Data.SqlClient.SqlException e)
248 {
249
250throw e;
251 }
252finally
253 {
254 cmd.Dispose();
255 connection.Close();
256 }
257 }
258 }
259///<summary>
260///向数据库⾥插⼊图像格式的字段(和上⾯情况类似的另⼀种实例)
261///</summary>
262///<param name="strSQL">SQL语句</param>
263///<param name="fs">图像字节，数据库的字段类型为image的情况</param>
264///<returns>影响的记录数</returns>
265public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
266 {
267using (SqlConnection connection = new SqlConnection(connectionString))
268 {
269 SqlCommand cmd = new SqlCommand(strSQL, connection);
270 System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@fs", SqlDbType.Image); 271 myParameter.Value = fs;
272 cmd.Parameters.Add(myParameter);
273try
274 {
275 connection.Open();
276int rows = cmd.ExecuteNonQuery();
277return rows;
278 }
279catch (System.Data.SqlClient.SqlException e)
280 {
281
282throw e;
283 }
284finally
285 {
286 cmd.Dispose();
287 connection.Close();
288 }
289 }
290 }
291
292///<summary>
293///执⾏⼀条计算查询结果语句，返回查询结果（object）。
294///</summary>
295///<param name="SQLString">计算查询结果语句</param>
296///<returns>查询结果（object）</returns>
297public static object GetSingle(string SQLString)
298 {
299using (SqlConnection connection = new SqlConnection(connectionString))
300 {
301using (SqlCommand cmd = new SqlCommand(SQLString, connection))
302 {
303try
304 {
305 connection.Open();
306object obj = cmd.ExecuteScalar();
307if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
308 {
309return null;
310 }
311else
312 {
313return obj;
314 }
315 }
316catch (System.Data.SqlClient.SqlException e)
317 {
318
319 connection.Close();
320throw e;
321 }
322 }
323 }
324 }
325public static object GetSingle(string SQLString, int Times)
326 {
327using (SqlConnection connection = new SqlConnection(connectionString))
328 {
329using (SqlCommand cmd = new SqlCommand(SQLString, connection))
330 {
331try
332 {
333 connection.Open();
334 cmd.CommandTimeout = Times;
335object obj = cmd.ExecuteScalar();
336if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
337 {
338return null;
339 }
340else
341 {
342return obj;
343 }
344 }
345catch (System.Data.SqlClient.SqlException e)
346 {
347
348 connection.Close();
349throw e;
350 }
351 }
352 }
353 }
354///<summary>
355///执⾏查询语句，返回SqlDataReader ( 注意：调⽤该⽅法后，⼀定要对SqlDataReader进⾏Close )
356///</summary>
357///<param name="strSQL">查询语句</param>
358///<returns>SqlDataReader</returns>
359public static SqlDataReader ExecuteReader(string strSQL)
360 {
361 SqlConnection connection = new SqlConnection(connectionString);
362 SqlCommand cmd = new SqlCommand(strSQL, connection);
363try
364 {
365 connection.Open();
366 SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
367return myReader;
368 }
369catch (System.Data.SqlClient.SqlException e)
370 {
371
372throw e;
373 }
374
375 }
376///<summary>
377///执⾏查询语句，返回DataSet
378///</summary>
379///<param name="SQLString">查询语句</param>
380///<returns>DataSet</returns>
381public static DataSet Query(string SQLString)
382 {
383using (SqlConnection connection = new SqlConnection(connectionString))
384 {
385 DataSet ds = new DataSet();
386try
387 {
388 connection.Open();
389 SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
390 command.Fill(ds, "ds");
391 }
392catch (System.Data.SqlClient.SqlException e)
393 {
394 LogHelper.WriteErrorLog(String.Format(@"Query-失败：提交时间：{0}，执⾏SQL语句：{1}", DateTime.Now, SQLString), e); 395throw e;
396 }
397return ds;
398 }
399 }
400public static DataSet Query(string SQLString, int Times)
401 {
402using (SqlConnection connection = new SqlConnection(connectionString))
403 {
404 DataSet ds = new DataSet();
405try
406 {
407 connection.Open();
408 SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
409 command.SelectCommand.CommandTimeout = Times;
410 command.Fill(ds, "ds");
411 }
412catch (System.Data.SqlClient.SqlException e)
413 {
414 LogHelper.WriteErrorLog(String.Format(@"Query-失败：提交时间：{0}，执⾏SQL语句：{1}",DateTime.Now, SQLString), e); 415throw e;
416 }
417return ds;
418 }
419 }
420
421
422
423#endregion
424
425#region执⾏带参数的SQL语句
426
427///<summary>
428///执⾏SQL语句，返回影响的记录数
429///</summary>
430///<param name="SQLString">SQL语句</param>
431///<returns>影响的记录数</returns>
432public static int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)
433 {
434using (SqlConnection connection = new SqlConnection(connectionString))
435 {
436using (SqlCommand cmd = new SqlCommand())
437 {
438try
439 {
440 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
441int rows = cmd.ExecuteNonQuery();
442 cmd.Parameters.Clear();
443return rows;
444 }
445catch (System.Data.SqlClient.SqlException e)
446 {
447throw e;
448 }
449 }
450 }
451 }
452
453
454///<summary>
455///执⾏多条SQL语句，实现数据库事务。
456///</summary>
457///<param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
458public static void ExecuteSqlTran(Hashtable SQLStringList)
459 {
460using (SqlConnection conn = new SqlConnection(connectionString))
461 {
462 conn.Open();
463using (SqlTransaction trans = conn.BeginTransaction())
464 {
465 SqlCommand cmd = new SqlCommand();
466try
467 {
468//循环
469foreach (DictionaryEntry myDE in SQLStringList)
470 {
471string cmdText = myDE.Key.ToString();
472 SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
473 PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
474int val = cmd.ExecuteNonQuery();
475 cmd.Parameters.Clear();
476 }
477 trans.Commit();
478 }
479catch(Exception e)
480 {
481
482 trans.Rollback();
483throw;
484 }
485 }
486 }
487 }
488///<summary>
489///执⾏多条SQL语句，实现数据库事务。
490///</summary>
491///<param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param> 492public static void ExecuteSqlTranWithIndentity(Hashtable SQLStringList)
493 {
494using (SqlConnection conn = new SqlConnection(connectionString))
495 {
496 conn.Open();
497using (SqlTransaction trans = conn.BeginTransaction())
498 {
499 SqlCommand cmd = new SqlCommand();
500try
501 {
502int indentity = 0;
503//循环
504foreach (DictionaryEntry myDE in SQLStringList)
505 {
506string cmdText = myDE.Key.ToString();
507 SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
508foreach (SqlParameter q in cmdParms)
509 {
510if (q.Direction == ParameterDirection.InputOutput)
511 {
512 q.Value = indentity;
513 }
514 }
515 PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
516int val = cmd.ExecuteNonQuery();
517foreach (SqlParameter q in cmdParms)
518 {
519if (q.Direction == ParameterDirection.Output)
520 {
521 indentity = Convert.ToInt32(q.Value);
522 }
523 }
524 cmd.Parameters.Clear();
525 }
526 trans.Commit();
527 }
528catch(Exception e)
529 {
530
531 trans.Rollback();
532throw;
533 }
534 }
535 }
536 }
537///<summary>
538///执⾏⼀条计算查询结果语句，返回查询结果（object）。
539///</summary>
540///<param name="SQLString">计算查询结果语句</param>
541///<returns>查询结果（object）</returns>
542public static object GetSingle(string SQLString, params SqlParameter[] cmdParms)
543 {
544using (SqlConnection connection = new SqlConnection(connectionString))
545 {
546using (SqlCommand cmd = new SqlCommand())
547 {
548try
549 {
550 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
551object obj = cmd.ExecuteScalar();
552 cmd.Parameters.Clear();
553if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
554 {
555return null;
556 }
557else
mysql下载的vs库放在那个文件里558 {
559return obj;
560 }
561 }
562catch (System.Data.SqlClient.SqlException e)
563 {
564
565throw e;
566 }
567 }
568 }
569 }
570
571///<summary>
572///执⾏查询语句，返回SqlDataReader ( 注意：调⽤该⽅法后，⼀定要对SqlDataReader进⾏Close )
573///</summary>
574///<param name="strSQL">查询语句</param>
575///<returns>SqlDataReader</returns>
576public static SqlDataReader ExecuteReader(string SQLString, params SqlParameter[] cmdParms)
577 {
578 SqlConnection connection = new SqlConnection(connectionString);
579 SqlCommand cmd = new SqlCommand();
580try
581 {
582 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
583 SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
584 cmd.Parameters.Clear();
585return myReader;
586 }
587catch (System.Data.SqlClient.SqlException e)
588 {
589
590throw e;
591 }
592// finally
593// {
594// cmd.Dispose();
595// connection.Close();
596// }
597
598 }
599
600///<summary>
601///执⾏查询语句，返回DataSet
602///</summary>
603///<param name="SQLString">查询语句</param>
604///<returns>DataSet</returns>
605public static DataSet Query(string SQLString, params SqlParameter[] cmdParms)
606 {
607using (SqlConnection connection = new SqlConnection(connectionString))
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论