|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
|
public static class SQLHelper { static string connString = ConfigurationManager.ConnectionStrings["connString"].ToString(); /// <summary> /// 执行增删改 /// </summary> /// <param name="sql"></param> /// <returns></returns> public static int Update(string sql) { SqlConnection conn = new SqlConnection(connString); SqlCommand cmd = new SqlCommand(sql, conn); try { conn.Open(); return cmd.ExecuteNonQuery(); } catch (Exception ex) { throw new Exception(ex.Message); } finally { conn.Close(); } } /// <summary> /// 执行单一结果查询 /// </summary> /// <param name="sql"></param> /// <returns></returns> public static object GetSingleResult(string sql) { SqlConnection conn = new SqlConnection(connString); SqlCommand cmd = new SqlCommand(sql, conn); try { conn.Open(); return cmd.ExecuteScalar(); } catch (Exception ex) { throw new Exception(ex.Message); } finally { conn.Close(); } } /// <summary> /// 执行一个结果集查询 /// </summary> /// <param name="sql"></param> /// <returns></returns> public static SqlDataReader GetReader(string sql) { SqlConnection conn = new SqlConnection(connString); SqlCommand cmd = new SqlCommand(sql, conn); try { conn.Open(); return cmd.ExecuteReader(CommandBehavior.CloseConnection); } catch (Exception ex) { conn.Close(); throw new Exception(ex.Message); } } /// <summary> /// 执行返回数据集的查询 /// </summary> /// <param name="sql"></param> /// <returns></returns> public static SqlDataAdapter GetDataSet(string sql) { SqlConnection conn = new SqlConnection(connString); SqlCommand cmd = new SqlCommand(sql, conn); //创建适配器对象 SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); try { conn.Open(); da.Fill(ds);//使用数据适配器填充数据集 return da; } catch (Exception ex) { throw new Exception(ex.Message); } finally { conn.Close(); } } } |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
|
/// <summary>/// 启用事务执行多条sql语句/// </summary>/// <param name="sqlList">插入的sql语句</param>/// <returns></returns>public static bool UpdateByTran(List<string> sqlList){ SqlConnection conn = new SqlConnection(connString); SqlCommand cmd = new SqlCommand(); cmd.Connection=conn; try { conn.Open(); cmd.Transaction = conn.BeginTransaction(); foreach (string itemSql in sqlList) { cmd.CommandText = itemSql; cmd.ExecuteNonQuery(); } cmd.Transaction.Commit(); return true; } catch (Exception ex) { if (cmd.Transaction != null) { cmd.Transaction.Rollback(); } throw new Exception("调用事务出错" + ex.Message); } finally { if (cmd.Transaction!=null) { cmd.Transaction = null; } conn.Close(); }} |
对数据库操作的常用方法