您的位置:首页 > 教程资讯 > 皇冠体育的网址 > ASP.NET > ASP.NET 数据访问类

ASP.NET 数据访问类

发布于:2016-03-09 11:28:49   分享到:

using?System;
using?System.Data;
using?System.Data.SqlClient;?

namespace?SysClassLibrary
{
///?
///?DataAccess?的摘要说明。
///?数据处理基类,调用方式:DataAccess.DataSet((string)sqlstr);或者DataAccess.DataSet((string)sqlstr,ref?DataSet?ds);?
///?

public?class?DataAccess
{
#region?属性
protected?static?SqlConnection?conn=new?SqlConnection();
protected?static?SqlCommand?comm=new?SqlCommand();
#endregion
public?DataAccess()
{
//init();
}
#region?内部函数?静态皇冠体育的网址|首页中不会执行DataAccess()构造函数

///?
///?打开数据库连接
///?

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);
}
}
}
///?
///?关闭当前数据库连接
///?

private?static?void?closeConnection()
{
if(conn.State?==?ConnectionState.Open)
conn.Close();
conn.Dispose();
comm.Dispose();
}
#endregion
///?
///?执行Sql查询语句
///?

///?传入的Sql语句
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();
}
}

///?
///?执行存储过程
///?

///?存储过程名
///?SqlParameters?集合
public?static?void?ExecutePorcedure(string?procName,SqlParameter[]?coll)
{
try
{
openConnection();
for(int?i=0;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();
}
}

///?
///?执行存储过程并返回数据集
///?

///?存储过程名称
///?SqlParameter集合
///?DataSet?
public?static?void?ExecutePorcedure(string?procName,SqlParameter[]?coll,ref?DataSet?ds)
{
try
{
SqlDataAdapter?da=new?SqlDataAdapter();
openConnection();
for(int?i=0;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();
}
}

///?
///?执行Sql查询语句并返回第一行的第一条记录,返回值为object?使用时需要拆箱操作?->?Unbox
///?

///?传入的Sql语句
///?object?返回值?
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;
}

///?
///?执行Sql查询语句,同时进行事务处理
///?

///?传入的Sql语句
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();
}
}

///?
///?返回指定Sql语句的SqlDataReader,请注意,在使用后请关闭本对象,同时将自动调用closeConnection()来关闭数据库连接
///?皇冠体育的网址|首页关闭数据库连接
///?

///?传入的Sql语句
///?SqlDataReader对象
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;
}
///?
///?返回指定Sql语句的SqlDataReader,请注意,在使用后请关闭本对象,同时将自动调用closeConnection()来关闭数据库连接
///?皇冠体育的网址|首页关闭数据库连接
///?

///?传入的Sql语句
///?传入的ref?DataReader?对象
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();
}
}
}

///?
///?返回指定Sql语句的DataSet
///?

///?传入的Sql语句
///?DataSet
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;
}

///?
///?返回指定Sql语句的DataSet
///?

///?传入的Sql语句
///?传入的引用DataSet对象
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();
}
}
///?
///?返回指定Sql语句的DataTable
///?

///?传入的Sql语句
///?DataTable
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;
}

///?
///?执行指定Sql语句,同时给传入DataTable进行赋值?
///?

///?传入的Sql语句
///?ref?DataTable?dt?
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();
}
}
///?
///?执行带参数存储过程并返回数据集合
///?

///?存储过程名称
///?SqlParameterCollection?输入参数
///?
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;
}
}

}

标签:

ASP NET

关于我们  加入我们  版权声明  商务合作  友情链接  网站地图  站长统计

脚本大全-脚本语言之家-版权所有 

Copyright (C) 2016 jiaoben.net, All Rights Reserved