数据库链接类

同学给的发上来方便于以后运用：

Access：

 

代码

1 using System;
2   using System.Data;
3   using System.Configuration;
4   using System.Web;
5   using System.Web.Security;
6   using System.Web.UI;
7   using System.Web.UI.WebControls;
8   using System.Web.UI.WebControls.WebParts;
9 using System.Web.UI.HtmlControls;
10 using System.Text.RegularExpressions;
11 using System.Data.OleDb;
12
13 namespace LB.Data.OleServer
14 {
15 public class OleServer:IDisposable
16 {
17 public string constr = System.Configuration.ConfigurationManager.ConnectionStrings[ " leada_cnConnectionString " ].ToString();
18 // public string constr_en = System.Configuration.ConfigurationManager.ConnectionStrings["leada_enConnectionString"].ToString();
19 public OleDbConnection con ;
20
21 public OleServer()
22 {
23 con = new OleDbConnection(constr);
24 }
25
26 public bool Open()
27 {
28 return true ;
29 }
30
31 public void Close()
32 {
33 if (con.State != ConnectionState.Closed)
34 {
35 con.Close();
36 }
37
38 }
39
40 /// <summary>
41 /// 实现IDisposable接口
42 /// </summary>
43 public void Dispose()
44 {
45 this .Close();
46 GC.SuppressFinalize( true );
47
48 }
49
50 // public OleDbConnection conn()
51 // {
52 // OleServer oleserver = new OleServer(constr);
53 // OleDbConnection con = new OleDbConnection();
54 // }
55
56 /// <summary>
57 /// 准备连接
58 /// </summary>
59 /// <param name="con"></param>
60 public void PrepareCommand( OleDbConnection constr)
61 {
62 if (constr.State != ConnectionState.Open)
63 constr.Open();
64 }
65
66
67 /// <summary>
68 /// 判断是否是SQL语句
69 /// </summary>
70 /// <param name="input"></param>
71 /// <returns></returns>
72 public bool IsSQL( string input)
73 {
74 Regex regex = new Regex( @" \?|select%20|select\s+|insert%20|insert\s+|delete%20|delete\s+|count\(|drop%20|drop\s+|update%20|update\s+ " , RegexOptions.IgnoreCase);
75 return regex.IsMatch(input);
76 }
77
78 /// <summary>
79 /// 返回sql执行影响语句
80 /// </summary>
81 public int Excute( string sql)
82 {
83 OleDbCommand cmd = new OleDbCommand(sql, con);
84 PrepareCommand(con);
85 try
86 {
87 int result = cmd.ExecuteNonQuery();
88 return result;
89 }
90 catch (Exception ex)
91 {
92 throw new Exception(ex.Message);
93 }
94 finally
95 {
96 if (con.State != ConnectionState.Closed) con.Close();
97 }
98 }
99
100
101 public int Excute( string sql, OleDbParameter[] op)
102 {
103 OleDbCommand cmd = new OleDbCommand(sql, con);
104 PrepareCommand(con);
105 try
106 {
107 if (op != null )
108 {
109 for ( int i = 0 ; i < op.Length; i ++ )
110 {
111 cmd.Parameters.Add(op[i]);
112 }
113 }
114 int result = cmd.ExecuteNonQuery();
115 cmd.Parameters.Clear();
116 return result;
117 }
118 catch (Exception ex)
119 {
120 throw new Exception(ex.Message);
121 }
122 finally
123 {
124 if (con.State != ConnectionState.Closed)
125 con.Close();
126 }
127 }
128 /// <summary>
129 /// 返回执行影响记录的第一行中第一字段
130 /// </summary>
131 /// <param name="sql"> sql语句 </param>
132 /// <returns></returns>
133 public object ExcuteScalar( string sql)
134 {
135 OleDbCommand cmd = new OleDbCommand(sql, con);
136 PrepareCommand(con);
137 try
138 {
139 object result = cmd.ExecuteScalar();
140 return result;
141 }
142 catch (Exception odex)
143 {
144 throw new Exception(odex.Message);
145 }
146 finally
147 {
148 if (con.State != ConnectionState.Closed) con.Close();
149 }
150 }
151
152 /// <summary>
153 /// 返回执行影响记录的第一行中第一字段
154 /// </summary>
155 /// <param name="sql"></param>
156 /// <param name="param"></param>
157 /// <returns></returns>
158 public object ExcuteScalar( string sql, OleDbParameter[] op)
159 {
160 OleDbCommand cmd = new OleDbCommand(sql, con);
161 PrepareCommand(con);
162
163 try
164 {
165 if (op != null )
166 {
167 for ( int i = 0 ; i < op.Length; i ++ )
168 {
169 cmd.Parameters.Add(op[i]);
170 }
171 }
172 object result = cmd.ExecuteScalar();
173 cmd.Parameters.Clear();
174 return result;
175 }
176 catch (Exception ex)
177 {
178 throw new Exception(ex.Message);
179 }
180 finally
181 {
182 if (con.State != ConnectionState.Closed) con.Close();
183 }
184 }
185
186 /// <summary>
187 /// 获取数据集
188 /// </summary>
189 /// <param name="sql"></param>
190 /// <returns></returns>
191 public DataSet GetDataSet( string sql)
192 {
193 OleDbCommand cmd = new OleDbCommand(sql, con);
194 DataSet ds = new DataSet();
195 PrepareCommand(con);
196 try
197 {
198 if ( ! IsSQL(sql))
199 {
200 cmd.CommandType = CommandType.StoredProcedure;
201 }
202
203 OleDbDataAdapter dapter = new OleDbDataAdapter(cmd);
204 dapter.Fill(ds);
205
206 dapter.Dispose();
207 return ds;
208 }
209 catch (Exception ex)
210 {
211 throw new Exception(ex.Message);
212 }
213 finally
214 {
215 if (con.State != ConnectionState.Closed) con.Close();
216 }
217 }
218
219
220 /// <summary>
221 /// 获取数据集
222 /// </summary>
223 /// <param name="sql"></param>
224 /// <param name="param"></param>
225 /// <returns></returns>
226 public DataSet GetDataSet( string sql, OleDbParameter[] op)
227 {
228 OleDbCommand cmd = new OleDbCommand(sql, con);
229 DataSet ds = new DataSet();
230 PrepareCommand(con);
231 try
232 {
233 if (op != null )
234 {
235 for ( int i = 0 ; i < op.Length; i ++ )
236 {
237 cmd.Parameters.Add(op[i]);
238 }
239 }
240
241 OleDbDataAdapter dapter = new OleDbDataAdapter(cmd);
242 dapter.Fill(ds);
243
244 cmd.Parameters.Clear();
245 dapter.Dispose();
246 return ds;
247 }
248 catch (Exception ex)
249 {
250 throw new Exception(ex.Message);
251 }
252 finally
253 {
254 if (con.State != ConnectionState.Closed) con.Close();
255 }
256 }
257
258 /// <summary>
259 /// 获取第一列第一行记录
260 /// </summary>
261 /// <param name="sql"></param>
262 /// <returns></returns>
263 public object GetFristRow( string sql)
264 {
265 DataSet ds = GetDataSet(sql);
266 if (ds.Tables[ 0 ].Rows.Count > 0 )
267 {
268 return ds.Tables[ 0 ].Rows[ 0 ];
269 }
270 return null ;
271 }
272
273 /// <summary>
274 /// 获取第一列第一行记录
275 /// </summary>
276 /// <param name="sql"></param>
277 /// <param name="param"></param>
278 /// <returns></returns>
279 public object GetFristRow( string sql, OleDbParameter[] op)
280 {
281 DataSet ds = GetDataSet(sql, op);
282 if (ds.Tables[ 0 ].Rows.Count > 0 )
283 {
284 return ds.Tables[ 0 ].Rows[ 0 ];
285 }
286 return null ;
287 }
288
289
290 public OleDbDataReader ExcuteReader( string sql)
291 {
292 OleDbCommand cmd = new OleDbCommand(sql, con);
293 con.Open();
294 OleDbDataReader odr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
295 return odr;
296 }
297
298 public OleDbDataReader ExcuteReader( string sql, OleDbParameter[] op)
299 {
300 OleDbCommand cmd = new OleDbCommand(sql, con);
301
302 if (op != null )
303 {
304 for ( int i = 0 ; i < op.Length; i ++ )
305 {
306 cmd.Parameters.Add(op[i]);
307 }
308 }
309 con.Open();
310 OleDbDataReader odr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
311 return odr;
312 }
313 }
314 }
315

 

sql：

 

代码

1 using System;
2 using System.Collections.Generic;
3 using System.Text;
4 using System.Data;
5 using System.Data.SqlClient;
6 using System.Text.RegularExpressions;
7
8 namespace NT.Data.SqlServer
9 {
10 public class SqlServer : IDisposable
11 {
12 private string connstr;
13
14 public SqlServer( string str)
15 {
16 this .connstr = str;
17 }
18 /// <summary>
19 /// 打开数据库连接
20 /// </summary>
21 /// <returns></returns>
22 public bool Open()
23 {
24 // if (conn!=null&&conn.State == ConnectionState.Open)
25 // return true;
26 // try
27 // {
28 // conn = new SqlConnection(connstr);
29 // conn.Open();
30 // return true;
31 // }
32 // catch
33 // {
34 // return false;
35 // }
36 return true ;
37
38 }
39
40 /// <summary>
41 /// 关闭连接
42 /// </summary>
43 public void Close()
44 {
45 // if (conn!=null&&conn.State != ConnectionState.Closed)
46 // {
47 // conn.Close();
48 // conn.Dispose();
49 // }
50 // conn = null;
51 }
52
53 /// <summary>
54 /// 实现IDisposable接口
55 /// </summary>
56 public void Dispose()
57 {
58 this .Close();
59 GC.SuppressFinalize( true );
60 }
61
62 /// <summary>
63 /// 执行sql语句,返回影响行数
64 /// </summary>
65 /// <param name="sql"> sql语句 </param>
66 /// <returns></returns>
67 public int Execute( string sql)
68 {
69 using (SqlConnection conn = new SqlConnection(connstr))
70 {
71 PrepareCommand(conn);
72 SqlCommand cmd = new SqlCommand(sql, conn);
73 if ( ! IsSQL(sql))
74 {
75 cmd.CommandType = CommandType.StoredProcedure;
76 }
77 return cmd.ExecuteNonQuery();
78 }
79 }
80
81 /// <summary>
82 /// 执行sql语句,返回影响行数
83 /// </summary>
84 /// <param name="sql"> sql语句 </param>
85 /// <param name="parame"> 参数 </param>
86 /// <returns></returns>
87 public int Execute( string sql, SqlParameter[] parame)
88 {
89 SqlConnection conn = new SqlConnection(connstr);
90 PrepareCommand(conn);
91 SqlCommand cmd = new SqlCommand(sql, conn);
92 try
93 {
94 if ( ! IsSQL(sql))
95 {
96 cmd.CommandType = CommandType.StoredProcedure;
97 }
98 if (parame != null )
99 {
100 for ( int i = 0 ; i < parame.Length; i ++ )
101 {
102 cmd.Parameters.Add(parame[i]);
103 }
104 }
105 int temp = cmd.ExecuteNonQuery();
106 cmd.Parameters.Clear();
107 return temp;
108 }
109 catch (Exception ex)
110 {
111 throw new Exception(ex.Message);
112 }
113 finally
114 {
115 conn.Close();
116 }
117 }
118
119 /// <summary>
120 /// 执行SQL语句,返回第一行第一个字段数据
121 /// </summary>
122 /// <param name="sql"> sql语句 </param>
123 /// <returns> 第一个字段值 </returns>
124 public object ExecuteScalar( string sql)
125 {
126 using (SqlConnection conn = new SqlConnection(connstr))
127 {
128 PrepareCommand(conn);
129 SqlCommand cmd = new SqlCommand(sql, conn);
130 if ( ! IsSQL(sql))
131 {
132 cmd.CommandType = CommandType.StoredProcedure;
133 }
134 return cmd.ExecuteScalar();
135 }
136 }
137
138 /// <summary>
139 /// 执行SQL语句,返回第一行第一个字段数据
140 /// </summary>
141 /// <param name="sql"></param>
142 /// <param name="parame"></param>
143 /// <returns></returns>
144 public object ExecuteScalar( string sql, SqlParameter[] parame)
145 {
146 using (SqlConnection conn = new SqlConnection(connstr))
147 {
148 PrepareCommand(conn);
149 SqlCommand cmd = new SqlCommand(sql, conn);
150 if ( ! IsSQL(sql))
151 {
152 cmd.CommandType = CommandType.StoredProcedure;
153 }
154 if (parame != null )
155 {
156 for ( int i = 0 ; i < parame.Length; i ++ )
157 {
158 cmd.Parameters.Add(parame[i]);
159 }
160 }
161 object temp = cmd.ExecuteScalar();
162 cmd.Parameters.Clear();
163 return temp;
164 }
165 }
166
167 public DataSet GetDataSet( string sql)
168 {
169 using (SqlConnection conn = new SqlConnection(connstr))
170 {
171 SqlCommand cmd = new SqlCommand(sql, conn);
172 DataSet ds = new DataSet();
173 if ( ! IsSQL(sql))
174 {
175 cmd.CommandType = CommandType.StoredProcedure;
176 }
177 SqlDataAdapter dda = new SqlDataAdapter(cmd);
178 dda.Fill(ds);
179 dda.Dispose();
180 return ds;
181 }
182 }
183
184 public DataSet GetDataSet( string sql, SqlParameter[] parame)
185 {
186 using (SqlConnection conn = new SqlConnection(connstr))
187 {
188 SqlCommand cmd = new SqlCommand(sql, conn);
189 DataSet ds = new DataSet();
190 if ( ! IsSQL(sql))
191 {
192 cmd.CommandType = CommandType.StoredProcedure;
193 }
194 if (parame != null )
195 {
196 for ( int i = 0 ; i < parame.Length; i ++ )
197 {
198 cmd.Parameters.Add(parame[i]);
199 }
200 }
201 using (SqlDataAdapter dda = new SqlDataAdapter(cmd))
202 {
203 dda.Fill(ds);
204 cmd.Parameters.Clear();
205 }
206 return ds;
207 }
208 }
209
210 /// <summary>
211 /// get a SqlDataReader.
212 /// </summary>
213 /// <param name="sql"></param>
214 /// <param name="parame"></param>
215 /// <returns></returns>
216 public SqlDataReader executeReader( string sql, SqlParameter[] parame)
217 {
218 SqlConnection conn = new SqlConnection(connstr);
219 SqlCommand cmd = new SqlCommand(sql, conn);
220 if ( ! IsSQL(sql))
221 {
222 cmd.CommandType = CommandType.StoredProcedure;
223 }
224 if (parame != null )
225 {
226 for ( int i = 0 ; i < parame.Length; i ++ )
227 {
228 cmd.Parameters.Add(parame[i]);
229 }
230 }
231 conn.Open();
232 SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
233 return dr;
234 }
235
236 /// <summary>
237 /// get a SqlDataReader.
238 /// </summary>
239 /// <param name="sql"></param>
240 /// <returns></returns>
241 public SqlDataReader executeReader( string sql)
242 {
243 SqlConnection conn = new SqlConnection(connstr);
244 SqlCommand cmd = new SqlCommand(sql, conn);
245 if ( ! IsSQL(sql))
246 {
247 cmd.CommandType = CommandType.StoredProcedure;
248 }
249 conn.Open();
250 SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
251 return dr;
252 }
253
254 /// <summary>
255 /// 获取第一行数据
256 /// </summary>
257 /// <param name="sql"></param>
258 /// <returns></returns>
259 public DataRow GetFirstRow( string sql)
260 {
261 DataSet ds = GetDataSet(sql);
262 if (ds.Tables[ 0 ].Rows.Count > 0 )
263 return ds.Tables[ 0 ].Rows[ 0 ];
264 return null ;
265 }
266 /// <summary>
267 /// 获取第一行数据
268 /// </summary>
269 /// <param name="sql"></param>
270 /// <param name="parame"></param>
271 /// <returns></returns>
272 public DataRow GetFirstRow( string sql, SqlParameter[] parame)
273 {
274 DataSet ds = GetDataSet(sql, parame);
275 if (ds.Tables[ 0 ].Rows.Count > 0 )
276 return ds.Tables[ 0 ].Rows[ 0 ];
277 return null ;
278 }
279 public void PrepareCommand(SqlConnection conn)
280 {
281 if (conn.State != ConnectionState.Open)
282 conn.Open();
283 }
284
285 /// <summary>
286 /// 是否sql语句
287 /// </summary>
288 /// <param name="InPut"></param>
289 /// <returns></returns>
290 public bool IsSQL( string InPut)
291 {
292 Regex regex = new Regex( @" \?|select%20|select\s+|insert%20|insert\s+|delete%20|delete\s+|count\(|drop%20|drop\s+|update%20|update\s+ " , RegexOptions.IgnoreCase);
293 return regex.IsMatch(InPut);
294 }
295
296
297 }
298 }

 

 

 

 

转载于:https://www.cnblogs.com/mrguo/archive/2010/03/17/1688344.html