万能DBHelper类

Code

1 public class DBHelper
2 {
3 public static string strConn = "server=.;database=MyBookShop;uid=sa;pwd=sa";
4
5 /// <summary>
6 /// 查询受影响的行数
7 /// </summary>
8 /// <param name="sql">sql语句</param>
9 /// <returns>int</returns>
10   public static int ExecuteNonquery(string sql)
11 {
12 using (SqlConnection conn =new SqlConnection(strConn))
13 {
14 SqlCommand cmd = new SqlCommand(sql, conn);
15
16 try
17 {
18 conn.Open();
19 return cmd.ExecuteNonQuery();
20
21 }
22 catch
23 {
24
25 throw new Exception("数据库操作出错！");
26 }
27 finally
28 {
29 if (conn.State == ConnectionState.Open)
30 {
31 conn.Close();
32 }
33 }
34 }
35
36 }
37
38 public static int ExecuteNonquery(string sql,params SqlParameter[] arr)
39 {
40 using (SqlConnection conn = new SqlConnection(strConn))
41 {
42 SqlCommand cmd = new SqlCommand(sql, conn);
43 cmd.Parameters.AddRange(arr);
44
45 try
46 {
47 conn.Open();
48 return cmd.ExecuteNonQuery();
49 }
50 catch
51 {
52 throw new Exception("操作数据库出错！");
53 }
54 finally
55 {
56 if (conn.State == ConnectionState.Open)
57 {
58 conn.Close();
59 }
60 }
61 }
62 }
63
64 /// <summary>
65 /// 查询单个值
66 /// </summary>
67 /// <param name="sql"></param>
68 /// <returns></returns>
69 public static object ExecuteScalar(string sql)
70 {
71 using (SqlConnection conn = new SqlConnection(strConn))
72 {
73 SqlCommand cmd = new SqlCommand(sql, conn);
74
75 try
76 {
77 conn.Open();
78 return cmd.ExecuteScalar();
79
80 }
81 catch
82 {
83
84 throw new Exception("数据库操作出错！");
85 }
86 finally
87 {
88 if (conn.State == ConnectionState.Open)
89 {
90 conn.Close();
91 }
92 }
93 }
94 }
95
96 public static object ExecuteScalar(string sql,params SqlParameter[] arr)
97 {
98 using(SqlConnection conn =new SqlConnection(strConn))
99 {
100 SqlCommand cmd = new SqlCommand(sql,conn);
101 cmd.Parameters.AddRange(arr);
102
103 try
104 {
105 conn.Open();
106 return cmd.ExecuteScalar();
107 }
108 catch
109 {
110 throw new Exception("操作数据库出错！");
111 }
112 finally
113 {
114 if (conn.State == ConnectionState.Open)
115 {
116 conn.Close();
117 }
118 }
119 }
120
121 }
122
123 /// <summary>
124 /// 逐行查询
125 /// </summary>
126 /// <param name="sql"></param>
127 /// <returns></returns>
128 public static SqlDataReader ExecuteReader(string sql)
129 {
130 SqlConnection conn = new SqlConnection(strConn);
131
132 SqlCommand cmd = new SqlCommand(sql,conn);
133
134 try
135 {
136 conn.Open();
137 return cmd.ExecuteReader(CommandBehavior.CloseConnection);
138 }
139 catch
140 {
141
142 throw new Exception("操作数据库出错！");
143 }
144
145
146
147
148 }
149
150 public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] arr)
151 {
152 SqlConnection conn = new SqlConnection(strConn);
153
154 SqlCommand cmd = new SqlCommand(sql, conn);
155 cmd.Parameters.AddRange(arr);
156
157 try
158 {
159 conn.Open();
160 return cmd.ExecuteReader(CommandBehavior.CloseConnection);
161 }
162 catch
163 {
164
165 throw new Exception("操作数据库出错！");
166 }
167
168
169
170 }
171
172 /// <summary>
173 /// 查询多条记录
174 /// </summary>
175 /// <param name="sql"></param>
176 /// <returns></returns>
177 public static DataTable Fill(string sql)
178 {
179 SqlDataAdapter sda = new SqlDataAdapter(sql, strConn);
180 DataTable dt = new DataTable("sourse");
181
182 try
183 {
184 sda.Fill(dt);
185 return dt;
186
187 }
188 catch
189 {
190
191 throw new Exception("操作数据库出错！");
192 }
193
194 }
195
196 public static DataTable Fill(string sql,params SqlParameter[] arr)
197 {
198
199 DataTable dt = new DataTable("sourse");
200 SqlDataAdapter sda = new SqlDataAdapter("sql", strConn);
201 sda.SelectCommand.Parameters.AddRange(arr);
202
203 try
204 {
205 sda.Fill(dt);
206 return dt;
207 }
208 catch
209 {
210 throw new Exception("操作数据库出错！");
211 }
212 }
213 }

转载于:https://www.cnblogs.com/ashu123/archive/2010/10/25/ado_3.html