ASP.NET 数据访问类

原文地址：http://www.dhxy.com/school/aspnet/2005041811211610628.html 

 1using System;
  2using System.Data.SqlClient;
  3using System.Configuration;
  4
  5namespace test.ClassLibary
  6{
  7    /**//// <summary> 
  8    /// DataAccess 的摘要说明。 
  9    /// <description>数据处理基类,调用方式：DataAccess.DataSet((string)sqlstr);或者DataAccess.DataSet((string)sqlstr,ref DataSet ds); </description> 
 10    /// </summary> 
 11    public class DataAccess 
 12    { 
 13        属性#region 属性 
 14        protected static SqlConnection conn=new SqlConnection(); 
 15        protected static SqlCommand comm=new SqlCommand(); 
 16        #endregion 
 17        public DataAccess() 
 18        { 
 19            //init(); 
 20        } 
 21        内部函数 静态方法中不会执行DataAccess()构造函数#region 内部函数 静态方法中不会执行DataAccess()构造函数 
 22
 23        /**//// <summary> 
 24        /// 打开数据库连接 
 25        /// </summary> 
 26        private static void openConnection() 
 27        { 
 28            if (conn.State == ConnectionState.Closed) 
 29            { 
 30                //SysConfig.ConnectionString 为系统配置类中连接字符串，如："server=localhost;database=databasename;uid=sa;pwd=;" 
 31
 32                conn.ConnectionString = SysConfig.ConnectionString ; 
 33                comm.Connection =conn; 
 34                try 
 35                { 
 36                    conn.Open(); 
 37                } 
 38                catch(Exception e) 
 39                { 
 40                    throw new Exception(e.Message); 
 41                } 
 42            } 
 43        } 
 44        /**//// <summary> 
 45        /// 关闭当前数据库连接 
 46        /// </summary> 
 47        private static void closeConnection() 
 48        { 
 49            if(conn.State == ConnectionState.Open) 
 50                conn.Close(); 
 51            //Dispose()方法用来释放所占资源
 52            conn.Dispose(); 
 53            comm.Dispose(); 
 54        } 
 55        #endregion 
 56        /**//// <summary> 
 57        /// 执行Sql查询语句 
 58        /// </summary> 
 59        /// <param name="sqlstr">传入的Sql语句</param> 
 60        public static void ExecuteSql(string sqlstr) 
 61        { 
 62            try 
 63            { 
 64                openConnection(); 
 65                comm.CommandType =CommandType.Text ; 
 66                comm.CommandText =sqlstr; 
 67                comm.ExecuteNonQuery(); 
 68            } 
 69            catch(Exception e) 
 70            { 
 71                throw new Exception(e.Message); 
 72            } 
 73            finally 
 74            { 
 75                closeConnection(); 
 76            } 
 77        } 
 78
 79        /**//// <summary> 
 80        /// 执行存储过程 
 81        /// </summary> 
 82        /// <param name="procName">存储过程名</param> 
 83        /// <param name="coll">SqlParameters 集合</param> 
 84        public static void ExecutePorcedure(string procName,SqlParameter[] coll) 
 85        { 
 86            try 
 87            { 
 88                openConnection(); 
 89                for(int i=0;i<coll.Length;i++) 
 90                { 
 91                    comm.Parameters .Add(coll[i]); 
 92                } 
 93                comm.CommandType=CommandType.StoredProcedure ; 
 94                comm.CommandText =procName; 
 95                comm.ExecuteNonQuery(); 
 96            } 
 97            catch(Exception e) 
 98            { 
 99                throw new Exception(e.Message); 
100            } 
101            finally 
102            { 
103                comm.Parameters.Clear(); 
104                closeConnection(); 
105            } 
106        } 
107
108        /**//// <summary> 
109        /// 执行存储过程并返回数据集 
110        /// </summary> 
111        /// <param name="procName">存储过程名称</param> 
112        /// <param name="coll">SqlParameter集合</param> 
113        /// <param name="ds">DataSet </param> 
114        public static void ExecutePorcedure(string procName,SqlParameter[] coll,ref DataSet ds) 
115        { 
116            try 
117            { 
118                SqlDataAdapter da=new SqlDataAdapter(); 
119                openConnection(); 
120                for(int i=0;i<coll.Length;i++) 
121                { 
122                    comm.Parameters .Add(coll[i]); 
123                } 
124                comm.CommandType=CommandType.StoredProcedure ; 
125                comm.CommandText =procName; 
126
127                da.SelectCommand =comm; 
128                da.Fill(ds); 
129            } 
130            catch(Exception e) 
131            { 
132                throw new Exception(e.Message); 
133            } 
134            finally 
135            { 
136                comm.Parameters.Clear(); 
137                closeConnection(); 
138            } 
139        } 
140
141        /**//// <summary> 
142        /// 执行Sql查询语句并返回第一行的第一条记录,返回值为object 使用时需要拆箱操作 -> Unbox 
143        /// </summary> 
144        /// <param name="sqlstr">传入的Sql语句</param> 
145        /// <returns>object 返回值 </returns> 
146        public static object ExecuteScalar(string sqlstr) 
147        { 
148            object obj=new object(); 
149            try 
150            { 
151                openConnection(); 
152                comm.CommandType =CommandType.Text ; 
153                comm.CommandText =sqlstr; 
154                obj=comm.ExecuteScalar(); 
155            } 
156            catch(Exception e) 
157            { 
158                throw new Exception(e.Message); 
159            } 
160            finally 
161            { 
162                closeConnection(); 
163            } 
164            return obj; 
165        } 
166
167        /**//// <summary> 
168        /// 执行Sql查询语句,同时进行事务处理 
169        /// </summary> 
170        /// <param name="sqlstr">传入的Sql语句</param> 
171        public static void ExecuteSqlWithTransaction(string sqlstr) 
172        { 
173            SqlTransaction trans ; 
174            trans=conn.BeginTransaction(); 
175            comm.Transaction =trans; 
176            try 
177            { 
178                openConnection(); 
179                comm.CommandType =CommandType.Text ; 
180                comm.CommandText =sqlstr; 
181                comm.ExecuteNonQuery(); 
182                trans.Commit(); 
183            } 
184            catch 
185            { 
186                trans.Rollback(); 
187            } 
188            finally 
189            { 
190                closeConnection(); 
191            } 
192        } 
193
194        /**//// <summary> 
195        /// 返回指定Sql语句的SqlDataReader，请注意，在使用后请关闭本对象，同时将自动调用closeConnection()来关闭数据库连接 
196        /// 方法关闭数据库连接 
197        /// </summary> 
198        /// <param name="sqlstr">传入的Sql语句</param> 
199        /// <returns>SqlDataReader对象</returns> 
200        public static SqlDataReader dataReader(string sqlstr) 
201        { 
202            SqlDataReader dr=null; 
203            try 
204            { 
205                openConnection(); 
206                comm.CommandText =sqlstr; 
207                comm.CommandType =CommandType.Text ; 
208                dr=comm.ExecuteReader(CommandBehavior.CloseConnection); 
209            } 
210            catch 
211            { 
212                try 
213                { 
214                    dr.Close(); 
215                    closeConnection(); 
216                } 
217                catch 
218                { 
219                } 
220            } 
221            return dr; 
222        } 
223        /**//// <summary> 
224        /// 返回指定Sql语句的SqlDataReader，请注意，在使用后请关闭本对象，同时将自动调用closeConnection()来关闭数据库连接 
225        /// 方法关闭数据库连接 
226        /// </summary> 
227        /// <param name="sqlstr">传入的Sql语句</param> 
228        /// <param name="dr">传入的ref DataReader 对象</param> 
229        public static void dataReader(string sqlstr,ref SqlDataReader dr) 
230        { 
231            try 
232            { 
233                openConnection(); 
234                comm.CommandText =sqlstr; 
235                comm.CommandType =CommandType.Text ; 
236                dr=comm.ExecuteReader(CommandBehavior.CloseConnection); 
237            } 
238            catch 
239            { 
240                try 
241                { 
242                    if(dr!=null && !dr.IsClosed) 
243                        dr.Close(); 
244                } 
245                catch 
246                { 
247                } 
248                finally 
249                { 
250                    closeConnection(); 
251                } 
252            } 
253        } 
254
255
256        /**//// <summary> 
257        /// 返回指定Sql语句的DataSet 
258        /// </summary> 
259        /// <param name="sqlstr">传入的Sql语句</param> 
260        /// <returns>DataSet</returns> 
261        public static DataSet dataSet(string sqlstr) 
262        { 
263            DataSet ds= new DataSet(); 
264            SqlDataAdapter da=new SqlDataAdapter(); 
265            try 
266            { 
267                openConnection(); 
268                comm.CommandType =CommandType.Text ; 
269                comm.CommandText =sqlstr; 
270                da.SelectCommand =comm; 
271                da.Fill(ds); 
272            } 
273            catch(Exception e) 
274            { 
275                throw new Exception(e.Message); 
276            } 
277            finally 
278            { 
279                closeConnection(); 
280            } 
281            return ds; 
282        } 
283
284        /**//// <summary> 
285        /// 返回指定Sql语句的DataSet 
286        /// </summary> 
287        /// <param name="sqlstr">传入的Sql语句</param> 
288        /// <param name="ds">传入的引用DataSet对象</param> 
289        public static void dataSet(string sqlstr,ref DataSet ds) 
290        { 
291            SqlDataAdapter da=new SqlDataAdapter(); 
292            try 
293            { 
294                openConnection(); 
295                comm.CommandType =CommandType.Text ; 
296                comm.CommandText =sqlstr; 
297                da.SelectCommand =comm; 
298                da.Fill(ds); 
299            } 
300            catch(Exception e) 
301            { 
302                throw new Exception(e.Message); 
303            } 
304            finally 
305            { 
306                closeConnection(); 
307            } 
308        } 
309        /**//// <summary> 
310        /// 返回指定Sql语句的DataTable 
311        /// </summary> 
312        /// <param name="sqlstr">传入的Sql语句</param> 
313        /// <returns>DataTable</returns> 
314        public static DataTable dataTable(string sqlstr) 
315        { 
316            SqlDataAdapter da=new SqlDataAdapter(); 
317            DataTable datatable=new DataTable(); 
318            try 
319            { 
320                openConnection(); 
321                comm.CommandType =CommandType.Text ; 
322                comm.CommandText =sqlstr; 
323                da.SelectCommand =comm; 
324                da.Fill(datatable); 
325            } 
326            catch(Exception e) 
327            { 
328                throw new Exception(e.Message); 
329            } 
330            finally 
331            { 
332                closeConnection(); 
333            } 
334            return datatable; 
335        } 
336
337        /**//// <summary> 
338        /// 执行指定Sql语句,同时给传入DataTable进行赋值 
339        /// </summary> 
340        /// <param name="sqlstr">传入的Sql语句</param> 
341        /// <param name="dt">ref DataTable dt </param> 
342        public static void dataTable(string sqlstr,ref DataTable dt) 
343        { 
344            SqlDataAdapter da=new SqlDataAdapter(); 
345            try 
346            { 
347                openConnection(); 
348                comm.CommandType =CommandType.Text ; 
349                comm.CommandText =sqlstr; 
350                da.SelectCommand =comm; 
351                da.Fill(dt); 
352            } 
353            catch(Exception e) 
354            { 
355                throw new Exception(e.Message); 
356            } 
357            finally 
358            { 
359                closeConnection(); 
360            } 
361        } 
362        /**//// <summary> 
363        /// 执行带参数存储过程并返回数据集合 
364        /// </summary> 
365        /// <param name="procName">存储过程名称</param> 
366        /// <param name="parameters">SqlParameterCollection 输入参数</param> 
367        /// <returns></returns> 
368        public static DataTable dataTable(string procName,SqlParameterCollection parameters) 
369        { 
370            SqlDataAdapter da=new SqlDataAdapter(); 
371            DataTable datatable=new DataTable(); 
372            try 
373            { 
374                openConnection(); 
375                comm.Parameters.Clear(); 
376                comm.CommandType=CommandType.StoredProcedure ; 
377                comm.CommandText =procName; 
378                foreach(SqlParameter para in parameters) 
379                { 
380                    SqlParameter p=(SqlParameter)para; 
381                    comm.Parameters.Add(p); 
382                } 
383                da.SelectCommand =comm; 
384                da.Fill(datatable); 
385            } 
386            catch(Exception e) 
387            { 
388                throw new Exception(e.Message); 
389            } 
390            finally 
391            { 
392                closeConnection(); 
393            } 
394            return datatable; 
395        } 
396
397        public static DataView dataView(string sqlstr) 
398        { 
399            SqlDataAdapter da=new SqlDataAdapter(); 
400            DataView dv=new DataView(); 
401            DataSet ds=new DataSet(); 
402            try 
403            { 
404                openConnection(); 
405                comm.CommandType=CommandType.Text; 
406                comm.CommandText =sqlstr; 
407                da.SelectCommand =comm; 
408                da.Fill(ds); 
409                dv=ds.Tables[0].DefaultView; 
410            } 
411            catch(Exception e) 
412            { 
413                throw new Exception(e.Message); 
414            } 
415            finally 
416            { 
417                closeConnection(); 
418            } 
419            return dv; 
420        } 
421    } 
422
423
424}
425