中小系统.net DataAccess数据访问类

#region 引用对象
using System;
using System.Xml ;
using System.Data;
using System.Data.SqlClient;
using System.Web;
#endregion
namespace SysClassLibrary
{
///

1<summary>   
2/// DataAccess 的摘要说明。   
3/// <author>wuchen</author>   
4/// <date>2004-4-12</date>   
5/// <email>[email protected]</email>   
6/// <description>数据处理基类,调用方式：DataAccess.DataSet((string)sqlstr);或者DataAccess.DataSet((string)sqlstr,ref DataSet ds); </description>   
7/// </summary>

public class DataAccess
{

#region 属性

///

1<summary>   
2/// 是否必须关闭数据库连接   
3/// </summary>

public static bool mustCloseConnection
{
get
{
return _mustCloseConnection;
}
set
{
_mustCloseConnection=value;
}
}
///

1<summary>   
2/// 连接字符串   
3/// </summary>

public static string connectionString
{
get
{
if(_connectionString ==string.Empty)
return SysConfig.ConnectionString ;
else
return _connectionString;
}
set
{
_connectionString =value;
}
}
///

1<summary>   
2/// 是否关闭数据库连接   
3/// </summary>

private static bool _mustCloseConnection = true;
private static string _connectionString =string.Empty ;

#endregion

#region 类构造函数

///

1<summary>   
2/// 构造函数   
3/// </summary>

public DataAccess()
{
}

///

1<summary>   
2/// 析构函数，释放相应的对象   
3/// </summary>

~DataAccess()
{
}

#endregion

#region Method

///

1<summary>   
2/// 执行Sql查询语句   
3/// </summary>

///

1<param name="sqlstr"/>

传入的Sql语句
///

1<returns>i </returns>

public static int ExecuteSql(string sqlstr){
int i=0;
using (SqlConnection conn =new SqlConnection(connectionString))
{
SqlCommand comm=new SqlCommand();
comm.Connection =conn;
comm.CommandType =CommandType.Text ;
comm.CommandText =sqlstr;
try
{
conn.Open();
i=comm.ExecuteNonQuery();
}
catch(SqlException e)
{
new ErrorLog().SaveDataAccessError(e);
}
finally
{
conn.Close();
comm.Dispose();
}
}
return i;
}

///

1<summary>   
2/// 执行存储过程   
3/// </summary>

///

1<param name="procName"/>

存储过程名
///

1<param name="coll"/>

SqlParameters 集合
public static void ExecutePorcedure(string procName,SqlParameter[] coll)
{
using (SqlConnection conn =new SqlConnection(connectionString))
{
SqlCommand comm=new SqlCommand();
comm.Connection =conn;
comm.CommandType =CommandType.StoredProcedure ;
ExecutePorcedure(procName,coll,conn,comm);
}
}

public static void ExecutePorcedure(string procName,SqlParameter[] coll,ref DataSet ds)
{
using (SqlConnection conn =new SqlConnection(connectionString))
{
SqlCommand comm=new SqlCommand();
comm.Connection =conn;
comm.CommandType =CommandType.StoredProcedure ;
ExecutePorcedure(procName,coll,conn,comm,ref ds);
}
}

///

1<summary>   
2/// 执行存储过程类   
3/// </summary>

///

1<param name="procName"/>

///

1<param name="coll"/>

///

1<param name="conn"/>

///

1<param name="comm"/>

public static void ExecutePorcedure(string procName,SqlParameter[] coll,SqlConnection conn,SqlCommand comm)
{
if(procName ==null || procName=="")
throw new SqlNullException();
try
{
conn.Open();
for(int i=0;i

  1<coll.length;i++) )="" .add(coll[i]);="" ;="" <summary="" catch(sqlexception="" coll,sqlconnection="" comm,ref="" comm.commandtext="procName;" comm.commandtype="CommandType.StoredProcedure" comm.dispose();="" comm.executenonquery();="" comm.parameters="" comm.parameters.clear();="" conn,sqlcommand="" conn.close();="" conn.open();="" da="new" da.fill(ds);="" da.selectcommand="comm;" dataset="" ds)="" e)="" errorlog().savedataaccesserror(e);="" executeporcedure(string="" finally="" for(int="" i="0;i&lt;coll.Length;i++)" if(procname="null" new="" procname="" procname,sqlparameter[]="" public="" sqldataadapter="" sqldataadapter();="" sqlnullexception();="" static="" throw="" try="" void="" {="" ||="" }="">   
  2/// 执行Sql查询语句并返回第一行的第一条记录,返回值为object 使用时需要拆箱操作 -&gt; Unbox   
  3///    
  4/// <param name="sqlstr"/>传入的Sql语句   
  5/// <returns>object 返回值 </returns>   
  6public static object ExecuteScalar(string sqlstr)   
  7{   
  8if(sqlstr ==null || sqlstr =="")   
  9throw new SqlNullException();   
 10  
 11object obj=new object();   
 12using (SqlConnection conn =new SqlConnection(connectionString))   
 13{   
 14SqlCommand comm=new SqlCommand();   
 15comm.Connection =conn;   
 16comm.CommandType =CommandType.Text;   
 17try   
 18{   
 19conn.Open();   
 20comm.CommandText =sqlstr;   
 21obj=comm.ExecuteScalar();   
 22}   
 23catch(SqlException e)   
 24{   
 25new ErrorLog().SaveDataAccessError(e);   
 26}   
 27finally   
 28{   
 29conn.Close();   
 30comm.Dispose();   
 31}   
 32}   
 33return obj;   
 34} 
 35
 36/// <summary>   
 37/// 执行Sql查询语句,同时进行事务处理   
 38/// </summary>   
 39/// <param name="sqlstr"/>传入的Sql语句   
 40public static void ExecuteSqlWithTransaction(string sqlstr)   
 41{   
 42if(sqlstr ==null || sqlstr =="")   
 43throw new SqlNullException();   
 44  
 45using(SqlConnection conn=new SqlConnection(connectionString))   
 46{   
 47//可以在事务中创建一个保存点，同时回滚到保存点   
 48SqlTransaction trans ;   
 49trans=conn.BeginTransaction();   
 50SqlCommand comm=new SqlCommand();   
 51comm.Connection =conn;   
 52comm.Transaction =trans;   
 53try   
 54{   
 55conn.Open();   
 56comm.CommandType =CommandType.Text ;   
 57comm.CommandText =sqlstr;   
 58comm.ExecuteNonQuery();   
 59trans.Commit();   
 60}   
 61catch   
 62{   
 63trans.Rollback();   
 64}   
 65finally   
 66{   
 67trans.Dispose();   
 68  
 69conn.Close();   
 70comm.Dispose();   
 71}   
 72}   
 73} 
 74
 75/// <summary>   
 76/// 返回指定Sql语句的SqlDataReader，请注意，在使用后请关闭本对象，同时将自动调用closeConnection()来关闭数据库连接   
 77/// 方法关闭数据库连接   
 78/// </summary>   
 79/// <param name="sqlstr"/>传入的Sql语句   
 80/// <returns>SqlDataReader对象</returns>   
 81public static SqlDataReader dataReader(string sqlstr)   
 82{   
 83SqlDataReader _dataReader =null ;   
 84dataReader(sqlstr,ref _dataReader);   
 85return _dataReader;   
 86}   
 87  
 88/// <summary>   
 89/// 返回指定Sql语句的SqlDataReader，请注意，在使用后请关闭本对象，同时将自动调用closeConnection()来关闭数据库连接   
 90/// 方法关闭数据库连接   
 91/// </summary>   
 92/// <param name="sqlstr"/>传入的Sql语句   
 93/// <param name="dr"/>传入的ref DataReader 对象   
 94public static void dataReader(string sqlstr,ref SqlDataReader _dataReader)   
 95{   
 96if(sqlstr ==null || sqlstr =="")   
 97throw new SqlNullException();   
 98try   
 99{   
100SqlConnection conn=new SqlConnection(connectionString);   
101SqlCommand comm=new SqlCommand();   
102comm.Connection =conn;   
103comm.CommandText =sqlstr;   
104comm.CommandType =CommandType.Text ;   
105conn.Open();   
106if(_mustCloseConnection)   
107{   
108_dataReader=comm.ExecuteReader(CommandBehavior.CloseConnection);   
109}   
110else   
111{   
112_dataReader= comm.ExecuteReader();   
113}   
114}   
115catch(SqlException e)   
116{   
117_dataReader =null;   
118//输出错误原因   
119throw e;   
120}   
121}   
122  
123  
124/// <summary>   
125/// 返回指定Sql语句的DataSet   
126/// </summary>   
127/// <param name="sqlstr"/>传入的Sql语句   
128/// <returns>DataSet</returns>   
129public static DataSet dataSet(string sqlstr)   
130{   
131if(sqlstr ==null || sqlstr =="")   
132throw new SqlNullException();   
133DataSet ds= new DataSet();   
134SqlDataAdapter da=new SqlDataAdapter();   
135using (SqlConnection conn=new SqlConnection(connectionString))   
136{   
137SqlCommand comm=new SqlCommand();   
138comm.Connection =conn;   
139try   
140{   
141conn.Open();   
142comm.CommandType =CommandType.Text ;   
143comm.CommandText =sqlstr;   
144da.SelectCommand =comm;   
145da.Fill(ds);   
146}   
147catch(SqlException e)   
148{   
149new ErrorLog().SaveDataAccessError(e);   
150}   
151finally   
152{   
153conn.Close();   
154}   
155}   
156return ds;   
157} 
158
159/// <summary>   
160/// 返回指定Sql语句的DataSet   
161/// </summary>   
162/// <param name="sqlstr"/>传入的Sql语句   
163/// <param name="ds"/>传入的引用DataSet对象   
164public static void dataSet(string sqlstr,ref DataSet ds)   
165{   
166if(sqlstr ==null || sqlstr =="")   
167throw new SqlNullException();   
168using (SqlConnection conn=new SqlConnection(connectionString))   
169{   
170SqlDataAdapter da=new SqlDataAdapter();   
171SqlCommand comm=new SqlCommand();   
172comm.Connection =conn;   
173try   
174{   
175conn.Open();   
176comm.CommandType =CommandType.Text ;   
177comm.CommandText =sqlstr;   
178da.SelectCommand =comm;   
179da.Fill(ds);   
180}   
181catch(SqlException e)   
182{   
183new ErrorLog().SaveDataAccessError(e);   
184}   
185finally   
186{   
187conn.Close();   
188}   
189}   
190}   
191/// <summary>   
192/// 返回指定Sql语句的DataTable   
193/// </summary>   
194/// <param name="sqlstr"/>传入的Sql语句   
195/// <returns>DataTable</returns>   
196public static DataTable dataTable(string sqlstr)   
197{   
198if(sqlstr ==null || sqlstr =="")   
199throw new SqlNullException();   
200SqlDataAdapter da=new SqlDataAdapter();   
201DataTable datatable=new DataTable();   
202using (SqlConnection conn=new SqlConnection(connectionString))   
203{   
204SqlCommand comm=new SqlCommand();   
205comm.Connection =conn;   
206try   
207{   
208conn.Open();   
209comm.CommandType =CommandType.Text ;   
210comm.CommandText =sqlstr;   
211da.SelectCommand =comm;   
212da.Fill(datatable);   
213}   
214catch(SqlException e)   
215{   
216new ErrorLog().SaveDataAccessError(e);   
217}   
218finally   
219{   
220conn.Close();   
221}   
222}   
223  
224return datatable;   
225} 
226
227/// <summary>   
228/// 执行指定Sql语句,同时给传入DataTable进行赋值   
229/// </summary>   
230/// <param name="sqlstr"/>传入的Sql语句   
231/// <param name="dt"/>ref DataTable dt    
232public static void dataTable(string sqlstr,ref DataTable dt)   
233{   
234if(sqlstr ==null || sqlstr =="")   
235throw new SqlNullException();   
236if(dt ==null)   
237dt=new DataTable();   
238SqlDataAdapter da=new SqlDataAdapter();   
239using (SqlConnection conn=new SqlConnection(connectionString))   
240{   
241SqlCommand comm=new SqlCommand();   
242comm.Connection =conn;   
243try   
244{   
245conn.Open();   
246comm.CommandType =CommandType.Text ;   
247comm.CommandText =sqlstr;   
248da.SelectCommand =comm;   
249da.Fill(dt);   
250}   
251catch(SqlException e)   
252{   
253new ErrorLog().SaveDataAccessError(e);   
254}   
255finally   
256{   
257conn.Close();   
258}   
259}   
260}   
261/// <summary>   
262/// 执行带参数存储过程并返回数据集合   
263/// </summary>   
264/// <param name="procName"/>存储过程名称   
265/// <param name="parameters"/>SqlParameterCollection 输入参数   
266/// <returns></returns>   
267public static DataTable dataTable(string procName,SqlParameterCollection parameters)   
268{   
269if(procName ==null || procName =="")   
270throw new SqlNullException();   
271SqlDataAdapter da=new SqlDataAdapter();   
272DataTable datatable=new DataTable();   
273using (SqlConnection conn=new SqlConnection(connectionString))   
274{   
275SqlCommand comm=new SqlCommand();   
276comm.Connection =conn;   
277try   
278{   
279comm.Parameters.Clear();   
280comm.CommandType=CommandType.StoredProcedure ;   
281comm.CommandText =procName;   
282foreach(SqlParameter para in parameters)   
283{   
284SqlParameter p=(SqlParameter)para;   
285comm.Parameters.Add(p);   
286}   
287conn.Open(); 
288
289da.SelectCommand =comm;   
290da.Fill(datatable);   
291}   
292catch(SqlException e)   
293{   
294new ErrorLog().SaveDataAccessError(e);   
295}   
296finally   
297{   
298conn.Close();   
299}   
300}   
301  
302return datatable;   
303}   
304/// <summary>   
305/// DataView   
306/// </summary>   
307/// <param name="sqlstr"/>   
308/// <returns></returns>   
309public static DataView dataView(string sqlstr)   
310{   
311if(sqlstr ==null || sqlstr =="")   
312throw new SqlNullException();   
313SqlDataAdapter da=new SqlDataAdapter();   
314DataView dv=new DataView();   
315DataSet ds=new DataSet();   
316dataSet(sqlstr,ref ds);   
317dv=ds.Tables[0].DefaultView;   
318return dv;   
319} 
320
321#endregion   
322} 
323
324#region 异常类，记录出错信息   
325/// <summary>   
326/// 异常类   
327/// </summary>   
328public class SqlNullException:ApplicationException   
329{   
330/// <summary>   
331/// 构造函数   
332/// </summary>   
333public SqlNullException(){   
334new SqlNullException("DataAccess类中静态成员 参数不能为空。可能是sqlstr =null");   
335} 
336
337/// <summary>   
338/// 重载出错信息   
339/// </summary>   
340/// <param name="message"/>   
341public SqlNullException(string message)   
342{   
343//保存出错信息   
344try   
345{   
346//err.SaveDataAccessError(message);   
347HttpContext.Current.Response.Write(message);   
348}   
349catch   
350{   
351throw;   
352}   
353}   
354/// <summary>   
355/// 重载出错信息   
356/// </summary>   
357/// <param name="e"/>   
358public SqlNullException(SqlException e)   
359{   
360//保存出错信息   
361try   
362{   
363HttpContext.Current.Response.Write(e.Message);   
364//err.SaveDataAccessError(e);   
365}   
366catch   
367{   
368throw;   
369}   
370} 
371
372/// <summary>   
373/// 析构函数   
374/// </summary>   
375~ SqlNullException()   
376{   
377  
378}   
379private ErrorLog err=new ErrorLog();   
380}   
381#endregion 
382
383#region ErrorLog 错误日志捕获   
384/// <summary>   
385/// ErrorLog 的摘要说明。   
386/// </summary>   
387public class ErrorLog   
388{   
389/// <summary>   
390/// ctr   
391/// </summary>   
392public ErrorLog()   
393{   
394//   
395// TODO: 在此处添加构造函数逻辑   
396//   
397} 
398
399/// <summary>   
400/// 数据库访问出错日志   
401/// </summary>   
402/// <param name="e"/>错误信息    
403public void SaveDataAccessError(SqlException e)   
404{   
405//生成的错误行号   
406// int lineNumber = e.LineNumber ;   
407// string message= e.Message;   
408// int number =e.Number;   
409// string procedure=e.Procedure ;   
410// string source=e.Source ;   
411//   
412// string ErrMessage ="LineNumber："+lineNumber.ToString() + " ---- Procedure："+ procedure.ToString() ;   
413// string ErrSource =source ;   
414// string ErrTargetSite ="错误号："+number ;   
415// string Url =HttpContext.Current.Request.UrlReferrer.AbsolutePath ;   
416// string IP = HttpContext.Current.Request.UserHostAddress ;   
417// try   
418// {   
419// SysClassLibrary.DataAccess.mustCloseConnection =true;   
420// SysClassLibrary.DataAccess.ExecuteSql(string.Format("insert into sys_errorLog(ErrMessage,ErrSource,ErrTargetSite,Url,IP)values('{0}','{1}','{2}','{3}','{4}') ",ErrMessage,ErrSource,ErrTargetSite,Url,IP));   
421// }   
422// catch   
423// {   
424// }   
425}   
426/// <summary>   
427/// 数据库访问出错日志   
428/// </summary>   
429/// <param name="message"/>出错信息   
430public void SaveDataAccessError(string message)   
431{   
432//生成的错误行号   
433  
434// string ErrMessage =message;   
435// string ErrSource ="" ;   
436// string ErrTargetSite ="";   
437// string Url =HttpContext.Current.Request.UrlReferrer.AbsolutePath ;   
438// string IP = HttpContext.Current.Request.UserHostAddress ;   
439// try   
440// {   
441// SysClassLibrary.DataAccess.mustCloseConnection =true;   
442// SysClassLibrary.DataAccess.ExecuteSql(string.Format("insert into sys_errorLog(ErrMessage,ErrSource,ErrTargetSite,Url,IP)values('{0}','{1}','{2}','{3}','{4}') ",ErrMessage,ErrSource,ErrTargetSite,Url,IP));   
443// }   
444// catch   
445// {   
446// }   
447}   
448}   
449#endregion   
450}</coll.length;i++)>
Published At
Categories with Web编程
Tagged with
comments powered by Disqus