Detailed explanation of ASP.NET data access class code-C#.Net Tutorial-php.cn

using System; 
using System.Data; 
using System.Data.SqlClient;  
namespace SysClassLibrary 
{ 
/// <summary> 
/// DataAccess 的摘要说明。 
/// <description>数据处理基类,调用方式：DataAccess.DataSet((string)sqlstr);或者DataAccess.DataSet((string)sqlstr,ref DataSet ds); </description> 
/// </summary> 
public class DataAccess 
{ 
#region 属性 
protected static SqlConnection conn=new SqlConnection(); 
protected static SqlCommand comm=new SqlCommand(); 
#endregion 
public DataAccess() 
{ 
//init(); 
} 
#region 内部函数 静态方法中不会执行DataAccess()构造函数 
/// <summary> 
/// 打开数据库连接 
/// </summary> 
private static void openConnection() 
{ 
if (conn.State == ConnectionState.Closed) 
{ 
//SysConfig.ConnectionString 为系统配置类中连接字符串，如："server=localhost;database=databasename;uid=sa;pwd=;" 
conn.ConnectionString = SysConfig.ConnectionString ; 
comm.Connection =conn; 
try 
{ 
conn.Open(); 
} 
catch(Exception e) 
{ 
throw new Exception(e.Message); 
} 
} 
} 
/// <summary> 
/// 关闭当前数据库连接 
/// </summary> 
private static void closeConnection() 
{ 
if(conn.State == ConnectionState.Open) 
conn.Close(); 
conn.Dispose(); 
comm.Dispose(); 
} 
#endregion 
/// <summary> 
/// 执行Sql查询语句 
/// </summary> 
/// <param name="sqlstr">传入的Sql语句</param> 
public static void ExecuteSql(string sqlstr) 
{ 
try 
{ 
openConnection(); 
comm.CommandType =CommandType.Text ; 
comm.CommandText =sqlstr; 
comm.ExecuteNonQuery();  
} 
catch(Exception e) 
{ 
throw new Exception(e.Message); 
} 
finally 
{ 
closeConnection(); 
} 
} 
/// <summary> 
/// 执行存储过程 
/// </summary> 
/// <param name="procName">存储过程名</param> 
/// <param name="coll">SqlParameters 集合</param> 
public static void ExecutePorcedure(string procName,SqlParameter[] coll) 
{ 
try 
{ 
openConnection(); 
for(int i=0;i<coll.Length;i++) 
{ 
comm.Parameters .Add(coll); 
} 
comm.CommandType=CommandType.StoredProcedure ; 
comm.CommandText =procName; 
comm.ExecuteNonQuery(); 
} 
catch(Exception e) 
{ 
throw new Exception(e.Message); 
} 
finally 
{ 
comm.Parameters.Clear(); 
closeConnection(); 
} 
} 
/// <summary> 
/// 执行存储过程并返回数据集 
/// </summary> 
/// <param name="procName">存储过程名称</param> 
/// <param name="coll">SqlParameter集合</param> 
/// <param name="ds">DataSet </param> 
public static void ExecutePorcedure(string procName,SqlParameter[] coll,ref DataSet ds) 
{ 
try 
{ 
SqlDataAdapter da=new SqlDataAdapter(); 
openConnection(); 
for(int i=0;i<coll.Length;i++) 
{ 
comm.Parameters .Add(coll); 
} 
comm.CommandType=CommandType.StoredProcedure ; 
comm.CommandText =procName; 
da.SelectCommand =comm; 
da.Fill(ds); 
} 
catch(Exception e) 
{ 
throw new Exception(e.Message); 
} 
finally 
{ 
comm.Parameters.Clear(); 
closeConnection(); 
} 
} 
/// <summary> 
/// 执行Sql查询语句并返回第一行的第一条记录,返回值为object 使用时需要拆箱操作 -> Unbox 
/// </summary> 
/// <param name="sqlstr">传入的Sql语句</param> 
/// <returns>object 返回值 </returns> 
public static object ExecuteScalar(string sqlstr) 
{ 
object obj=new object(); 
try 
{ 
openConnection(); 
comm.CommandType =CommandType.Text ; 
comm.CommandText =sqlstr; 
obj=comm.ExecuteScalar();  
} 
catch(Exception e) 
{ 
throw new Exception(e.Message); 
} 
finally 
{ 
closeConnection(); 
} 
return obj; 
} 
/// <summary> 
/// 执行Sql查询语句,同时进行事务处理 
/// </summary> 
/// <param name="sqlstr">传入的Sql语句</param> 
public static void ExecuteSqlWithTransaction(string sqlstr) 
{ 
SqlTransaction trans ; 
trans=conn.BeginTransaction(); 
comm.Transaction =trans; 
try 
{ 
openConnection(); 
comm.CommandType =CommandType.Text ; 
comm.CommandText =sqlstr; 
comm.ExecuteNonQuery();  
trans.Commit(); 
} 
catch 
{ 
trans.Rollback(); 
} 
finally 
{ 
closeConnection(); 
} 
} 
/// <summary> 
/// 返回指定Sql语句的SqlDataReader，请注意，在使用后请关闭本对象，同时将自动调用closeConnection()来关闭数据库连接 
/// 方法关闭数据库连接 
/// </summary> 
/// <param name="sqlstr">传入的Sql语句</param> 
/// <returns>SqlDataReader对象</returns> 
public static SqlDataReader dataReader(string sqlstr) 
{ 
SqlDataReader dr=null; 
try 
{ 
openConnection(); 
comm.CommandText =sqlstr; 
comm.CommandType =CommandType.Text ; 
dr=comm.ExecuteReader(CommandBehavior.CloseConnection);  
} 
catch 
{ 
try 
{ 
dr.Close(); 
closeConnection(); 
} 
catch 
{ 
} 
} 
return dr; 
} 
/// <summary> 
/// 返回指定Sql语句的SqlDataReader，请注意，在使用后请关闭本对象，同时将自动调用closeConnection()来关闭数据库连接 
/// 方法关闭数据库连接 
/// </summary> 
/// <param name="sqlstr">传入的Sql语句</param> 
/// <param name="dr">传入的ref DataReader 对象</param> 
public static void dataReader(string sqlstr,ref SqlDataReader dr) 
{ 
try 
{ 
openConnection(); 
comm.CommandText =sqlstr; 
comm.CommandType =CommandType.Text ; 
dr=comm.ExecuteReader(CommandBehavior.CloseConnection);  
} 
catch 
{ 
try 
{ 
if(dr!=null && !dr.IsClosed) 
dr.Close(); 
} 
catch 
{ 
} 
finally 
{ 
closeConnection(); 
} 
} 
} 
/// <summary> 
/// 返回指定Sql语句的DataSet 
/// </summary> 
/// <param name="sqlstr">传入的Sql语句</param> 
/// <returns>DataSet</returns> 
public static DataSet dataSet(string sqlstr) 
{ 
DataSet ds= new DataSet(); 
SqlDataAdapter da=new SqlDataAdapter(); 
try 
{ 
openConnection(); 
comm.CommandType =CommandType.Text ; 
comm.CommandText =sqlstr; 
da.SelectCommand =comm; 
da.Fill(ds); 
} 
catch(Exception e) 
{ 
throw new Exception(e.Message);  
} 
finally 
{ 
closeConnection(); 
} 
return ds; 
} 
/// <summary> 
/// 返回指定Sql语句的DataSet 
/// </summary> 
/// <param name="sqlstr">传入的Sql语句</param> 
/// <param name="ds">传入的引用DataSet对象</param> 
public static void dataSet(string sqlstr,ref DataSet ds) 
{ 
SqlDataAdapter da=new SqlDataAdapter(); 
try 
{ 
openConnection(); 
comm.CommandType =CommandType.Text ; 
comm.CommandText =sqlstr; 
da.SelectCommand =comm; 
da.Fill(ds); 
} 
catch(Exception e) 
{ 
throw new Exception(e.Message);  
} 
finally 
{ 
closeConnection(); 
} 
} 
/// <summary> 
/// 返回指定Sql语句的DataTable 
/// </summary> 
/// <param name="sqlstr">传入的Sql语句</param> 
/// <returns>DataTable</returns> 
public static DataTable dataTable(string sqlstr) 
{ 
SqlDataAdapter da=new SqlDataAdapter(); 
DataTable datatable=new DataTable(); 
try 
{ 
openConnection(); 
comm.CommandType =CommandType.Text ; 
comm.CommandText =sqlstr; 
da.SelectCommand =comm; 
da.Fill(datatable); 
} 
catch(Exception e) 
{ 
throw new Exception(e.Message);  
} 
finally 
{ 
closeConnection(); 
} 
return datatable; 
} 
/// <summary> 
/// 执行指定Sql语句,同时给传入DataTable进行赋值  
/// </summary> 
/// <param name="sqlstr">传入的Sql语句</param> 
/// <param name="dt">ref DataTable dt </param> 
public static void dataTable(string sqlstr,ref DataTable dt) 
{ 
SqlDataAdapter da=new SqlDataAdapter(); 
try 
{ 
openConnection(); 
comm.CommandType =CommandType.Text ; 
comm.CommandText =sqlstr; 
da.SelectCommand =comm; 
da.Fill(dt); 
} 
catch(Exception e) 
{ 
throw new Exception(e.Message);  
} 
finally 
{ 
closeConnection(); 
} 
} 
/// <summary> 
/// 执行带参数存储过程并返回数据集合 
/// </summary> 
/// <param name="procName">存储过程名称</param> 
/// <param name="parameters">SqlParameterCollection 输入参数</param> 
/// <returns></returns> 
public static DataTable dataTable(string procName,SqlParameterCollection parameters) 
{  
SqlDataAdapter da=new SqlDataAdapter(); 
DataTable datatable=new DataTable(); 
try 
{ 
openConnection(); 
comm.Parameters.Clear(); 
comm.CommandType=CommandType.StoredProcedure ; 
comm.CommandText =procName; 
foreach(SqlParameter para in parameters) 
{ 
SqlParameter p=(SqlParameter)para; 
comm.Parameters.Add(p); 
} 
da.SelectCommand =comm; 
da.Fill(datatable); 
} 
catch(Exception e) 
{ 
throw new Exception(e.Message); 
} 
finally 
{ 
closeConnection(); 
} 
return datatable; 
} 
public static DataView dataView(string sqlstr) 
{ 
SqlDataAdapter da=new SqlDataAdapter(); 
DataView dv=new DataView(); 
DataSet ds=new DataSet(); 
try 
{ 
openConnection(); 
comm.CommandType=CommandType.Text; 
comm.CommandText =sqlstr; 
da.SelectCommand =comm; 
da.Fill(ds); 
dv=ds.Tables[0].DefaultView; 
} 
catch(Exception e) 
{ 
throw new Exception(e.Message); 
} 
finally 
{ 
closeConnection(); 
} 
return dv; 
} 
} 
}
Copy after login
The above is the detailed content of Detailed explanation of ASP.NET data access class code. For more information, please follow other related articles on the PHP Chinese website!