我的一个数据层基类
我在自己编程序玩的时候,编写了一个专门用作数据层基类的东东。它是在<<ASP.NET WEB 站点高级编程>>这本书P20提供的类DbObject的基础上扩充二来的。我自己感觉虽然功能很简单,但是对付一些小程序还是好像挺好用的,就把它贴出来。
public abstract class DbObject
{
/////////////////////////////////////////////////////////////////////////
/// <summary>
/// 可以根据给定的字串创建数据库连接
/// </summary>
/// <param name="newConnectionString"></param>
public DbObject( string newConnectionString)
{
this.connectionString = newConnectionString;
this.Connection = new SqlConnection(this.connectionString);
}
//数据库连接字串
private string connectionString;
public string ConnectionString
{
get
{
return this.connectionString;
}
}
//数据库连接对象
private SqlConnection Connection;
/////////////////////////////////////////////////////////////////////////////////
/// <summary>
/// 返回一个SQL语句的命令对象,该对象通过一个存储过程作用
/// 该存储过程多用来执行select 语句
/// </summary>
/// <param name="storedProcName">被执行的存储过程名</param>
/// <param name="parameter">该参数中不包含返回值</param>
/// <returns>实例化的命令对象</returns>
private SqlCommand BuildQueryCommand( string storedProcName, IDataParameter[] parameters)
{
SqlCommand command = new SqlCommand( storedProcName, this.Connection);
command.CommandType = CommandType.StoredProcedure;
foreach( SqlParameter parameter in parameters)
{
command.Parameters.Add( parameter);
}
return command;
}
/// <summary>
/// 创建一个命令对象,该命令对象通过一个存储过程起作用
/// 该存储过程只执行非查询语句
/// </summary>
/// <param name="storedProcName">被执行的存储过程名</param>
/// <param name="parameters">该参数中包含一个 int 型的返回值,代表执行的结果/param>
/// <returns>实例化的命令对象</returns>
private SqlCommand BuildIntCommand( string storedProcName, IDataParameter [] parameters)
{
SqlCommand command = BuildQueryCommand( storedProcName, parameters);
command.Parameters.Add( new SqlParameter( "ReturnValue", SqlDbType.Int, 4,
ParameterDirection.ReturnValue, false,
0,
0,
string.Empty, DataRowVersion.Default, null));
return command;
}
///////////////////////////////////////////////////////////////////////////////
/// <summary>
/// 该函数执行一个非查询SQL语句,返回代表成功与否的数字
/// 该函数通过调用BuildIntCommand获得命令对象
/// </summary>
/// <param name="queryText">被执行的SQL语句</param>
/// <param name="rowsAffected">收到影响的行数</param>
/// <returns>一个代表成功与否的数字</returns>
protected int RunSqlOrder(string queryText, out int rowsAffected)
{
int result;
try
{
this.Connection.Open();
SqlCommand command = new SqlCommand(queryText,this.Connection);
rowsAffected = command.ExecuteNonQuery();
result = (int)command.Parameters["ReturnValue"].Value;
}
catch (SqlException sqlExc)
{
throw sqlExc;
}
catch (Exception exc)
{
throw exc;
}
finally
{
this.Connection.Close();
}
return result;
}
/// <summary>
/// 该函数执行一个非查询存储过程,返回代表成功与否的数字
/// 该函数通过调用BuildIntCommand获得命令对象
/// </summary>
/// <param name="storedProcName">被执行的存储过程名</param>
/// <param name="parameters">存储过程的参数</param>
/// <param name="rowsAffected">受影响的数据行数</param>
/// <returns>一个表示执行成功或者失败原因的数字</returns>
protected int RunProcedure(string storedProcName, IDataParameter [] parameters, out int rowsAffected)
{
int result;
try
{
this.Connection.Open();
SqlCommand command = BuildIntCommand(storedProcName, parameters);
rowsAffected = command.ExecuteNonQuery();
result = (int)command.Parameters["ReturnValue"].Value;
}
catch (SqlException sqlExc)
{
throw sqlExc;
}
catch (Exception exc)
{
throw exc;
}
finally
{
this.Connection.Close();
}
return result;
}
////////////////////////////////////////////////////////////////////////////////
/// <summary>
/// 执行一个SQL语句,返回一个DataReader
/// </summary>
/// <param name="queryText">要执行的SQL语句</param>
/// <returns>返回的DataReader</returns>
protected SqlDataReader RunSqlOrder(string queryText)
{
SqlDataReader returnReader;
try
{
this.Connection.Open();
SqlCommand command = new SqlCommand(queryText, this.Connection);
returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (SqlException sqlExc)
{
throw sqlExc;
}
catch (Exception exc)
{
throw exc;
}
return returnReader;
}
/// <summary>
/// 执行一个存储过程,返回一个DataReader
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>返回的DataReader</returns>
protected SqlDataReader RunProcedure( string storedProcName, IDataParameter [] parameters)
{
SqlDataReader returnReader;
try
{
this.Connection.Open();
SqlCommand command = this.BuildQueryCommand(storedProcName, parameters);
command.CommandType = CommandType.StoredProcedure;
returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (SqlException sqlExc)
{
throw sqlExc;
}
catch (Exception exc)
{
throw exc;
}
return returnReader;
}
///////////////////////////////////////////////////////////////////////////////
/// <summary>
/// 执行一个SQL语句,获取一个DataSet,数据存储在该DataSet中指定的数据表名中
/// </summary>
/// <param name="queryText">SQL语句</param>
/// <param name="tableName">指定的DataTable名称</param>
/// <returns>返回获得的DataSet对象</returns>
protected DataSet RunSqlOrder(string queryText, string tableName)
{
DataSet returnDataSet = new DataSet();
try
{
this.Connection.Open();
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
sqlDataAdapter.SelectCommand = new SqlCommand(queryText, this.Connection);
sqlDataAdapter.Fill(returnDataSet, tableName);
}
catch (SqlException sqlExc)
{
throw sqlExc;
}
catch (Exception exc)
{
throw exc;
}
finally
{
this.Connection.Close();
}
return returnDataSet;
}
/// <summary>
/// 执行一个存储过程,获取一个DataSet,数据存储在该DataSet中指定的数据表名中
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程的参数</param>
/// <param name="tableName">指定的DataTable名称</param>
/// <returns>返回获得的DataSet对象</returns>
protected DataSet RunProcedure( string storedProcName, IDataParameter [] parameters, string tableName)
{
DataSet returnDataSet = new DataSet();
try
{
this.Connection.Open();
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
sqlDataAdapter.SelectCommand = BuildQueryCommand(storedProcName, parameters);
sqlDataAdapter.Fill(returnDataSet, tableName);
}
catch (SqlException sqlExc)
{
throw sqlExc;
}
catch (Exception exc)
{
throw exc;
}
finally
{
this.Connection.Close();
}
return returnDataSet;
}
//////////////////////////////////////////////////////////////////////////////
/// <summary>
/// 执行一个存储过程,数据存储在该已存在的指定DataSet的指定的数据表名中
/// </summary>
/// <param name="storedProcName">SQL语句</param>
/// <param name="dataSet">已存在的指定DataSet对象</param>
/// <param name="tableName">指定的DataTable名称</param>
protected void RunSqlOrder(string queryText, DataSet dataSet, string tableName)
{
try
{
this.Connection.Open();
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
sqlDataAdapter.SelectCommand = new SqlCommand(queryText, this.Connection);
sqlDataAdapter.Fill(dataSet, tableName);
}
catch (SqlException sqlExc)
{
throw sqlExc;
}
catch (Exception exc)
{
throw exc;
}
finally
{
this.Connection.Close();
}
}
/// <summary>
/// 执行一个存储过程,数据存储在该已存在的指定DataSet的指定的数据表名中
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程的参数</param>
/// <param name="dataSet">已存在的指定DataSet对象</param>
/// <param name="tableName">指定的DataTable名称</param>
protected void RunProcedure( string storedProcName, IDataParameter [] parameters, DataSet dataSet, string tableName)
{
this.Connection.Open();
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
sqlDataAdapter.SelectCommand = BuildIntCommand( storedProcName, parameters);
sqlDataAdapter.Fill( dataSet, tableName);
this.Connection.Close();
}
{
/////////////////////////////////////////////////////////////////////////
/// <summary>
/// 可以根据给定的字串创建数据库连接
/// </summary>
/// <param name="newConnectionString"></param>
public DbObject( string newConnectionString)
{
this.connectionString = newConnectionString;
this.Connection = new SqlConnection(this.connectionString);
}
//数据库连接字串
private string connectionString;
public string ConnectionString
{
get
{
return this.connectionString;
}
}
//数据库连接对象
private SqlConnection Connection;
/////////////////////////////////////////////////////////////////////////////////
/// <summary>
/// 返回一个SQL语句的命令对象,该对象通过一个存储过程作用
/// 该存储过程多用来执行select 语句
/// </summary>
/// <param name="storedProcName">被执行的存储过程名</param>
/// <param name="parameter">该参数中不包含返回值</param>
/// <returns>实例化的命令对象</returns>
private SqlCommand BuildQueryCommand( string storedProcName, IDataParameter[] parameters)
{
SqlCommand command = new SqlCommand( storedProcName, this.Connection);
command.CommandType = CommandType.StoredProcedure;
foreach( SqlParameter parameter in parameters)
{
command.Parameters.Add( parameter);
}
return command;
}
/// <summary>
/// 创建一个命令对象,该命令对象通过一个存储过程起作用
/// 该存储过程只执行非查询语句
/// </summary>
/// <param name="storedProcName">被执行的存储过程名</param>
/// <param name="parameters">该参数中包含一个 int 型的返回值,代表执行的结果/param>
/// <returns>实例化的命令对象</returns>
private SqlCommand BuildIntCommand( string storedProcName, IDataParameter [] parameters)
{
SqlCommand command = BuildQueryCommand( storedProcName, parameters);
command.Parameters.Add( new SqlParameter( "ReturnValue", SqlDbType.Int, 4,
ParameterDirection.ReturnValue, false,
0,
0,
string.Empty, DataRowVersion.Default, null));
return command;
}
///////////////////////////////////////////////////////////////////////////////
/// <summary>
/// 该函数执行一个非查询SQL语句,返回代表成功与否的数字
/// 该函数通过调用BuildIntCommand获得命令对象
/// </summary>
/// <param name="queryText">被执行的SQL语句</param>
/// <param name="rowsAffected">收到影响的行数</param>
/// <returns>一个代表成功与否的数字</returns>
protected int RunSqlOrder(string queryText, out int rowsAffected)
{
int result;
try
{
this.Connection.Open();
SqlCommand command = new SqlCommand(queryText,this.Connection);
rowsAffected = command.ExecuteNonQuery();
result = (int)command.Parameters["ReturnValue"].Value;
}
catch (SqlException sqlExc)
{
throw sqlExc;
}
catch (Exception exc)
{
throw exc;
}
finally
{
this.Connection.Close();
}
return result;
}
/// <summary>
/// 该函数执行一个非查询存储过程,返回代表成功与否的数字
/// 该函数通过调用BuildIntCommand获得命令对象
/// </summary>
/// <param name="storedProcName">被执行的存储过程名</param>
/// <param name="parameters">存储过程的参数</param>
/// <param name="rowsAffected">受影响的数据行数</param>
/// <returns>一个表示执行成功或者失败原因的数字</returns>
protected int RunProcedure(string storedProcName, IDataParameter [] parameters, out int rowsAffected)
{
int result;
try
{
this.Connection.Open();
SqlCommand command = BuildIntCommand(storedProcName, parameters);
rowsAffected = command.ExecuteNonQuery();
result = (int)command.Parameters["ReturnValue"].Value;
}
catch (SqlException sqlExc)
{
throw sqlExc;
}
catch (Exception exc)
{
throw exc;
}
finally
{
this.Connection.Close();
}
return result;
}
////////////////////////////////////////////////////////////////////////////////
/// <summary>
/// 执行一个SQL语句,返回一个DataReader
/// </summary>
/// <param name="queryText">要执行的SQL语句</param>
/// <returns>返回的DataReader</returns>
protected SqlDataReader RunSqlOrder(string queryText)
{
SqlDataReader returnReader;
try
{
this.Connection.Open();
SqlCommand command = new SqlCommand(queryText, this.Connection);
returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (SqlException sqlExc)
{
throw sqlExc;
}
catch (Exception exc)
{
throw exc;
}
return returnReader;
}
/// <summary>
/// 执行一个存储过程,返回一个DataReader
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>返回的DataReader</returns>
protected SqlDataReader RunProcedure( string storedProcName, IDataParameter [] parameters)
{
SqlDataReader returnReader;
try
{
this.Connection.Open();
SqlCommand command = this.BuildQueryCommand(storedProcName, parameters);
command.CommandType = CommandType.StoredProcedure;
returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (SqlException sqlExc)
{
throw sqlExc;
}
catch (Exception exc)
{
throw exc;
}
return returnReader;
}
///////////////////////////////////////////////////////////////////////////////
/// <summary>
/// 执行一个SQL语句,获取一个DataSet,数据存储在该DataSet中指定的数据表名中
/// </summary>
/// <param name="queryText">SQL语句</param>
/// <param name="tableName">指定的DataTable名称</param>
/// <returns>返回获得的DataSet对象</returns>
protected DataSet RunSqlOrder(string queryText, string tableName)
{
DataSet returnDataSet = new DataSet();
try
{
this.Connection.Open();
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
sqlDataAdapter.SelectCommand = new SqlCommand(queryText, this.Connection);
sqlDataAdapter.Fill(returnDataSet, tableName);
}
catch (SqlException sqlExc)
{
throw sqlExc;
}
catch (Exception exc)
{
throw exc;
}
finally
{
this.Connection.Close();
}
return returnDataSet;
}
/// <summary>
/// 执行一个存储过程,获取一个DataSet,数据存储在该DataSet中指定的数据表名中
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程的参数</param>
/// <param name="tableName">指定的DataTable名称</param>
/// <returns>返回获得的DataSet对象</returns>
protected DataSet RunProcedure( string storedProcName, IDataParameter [] parameters, string tableName)
{
DataSet returnDataSet = new DataSet();
try
{
this.Connection.Open();
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
sqlDataAdapter.SelectCommand = BuildQueryCommand(storedProcName, parameters);
sqlDataAdapter.Fill(returnDataSet, tableName);
}
catch (SqlException sqlExc)
{
throw sqlExc;
}
catch (Exception exc)
{
throw exc;
}
finally
{
this.Connection.Close();
}
return returnDataSet;
}
//////////////////////////////////////////////////////////////////////////////
/// <summary>
/// 执行一个存储过程,数据存储在该已存在的指定DataSet的指定的数据表名中
/// </summary>
/// <param name="storedProcName">SQL语句</param>
/// <param name="dataSet">已存在的指定DataSet对象</param>
/// <param name="tableName">指定的DataTable名称</param>
protected void RunSqlOrder(string queryText, DataSet dataSet, string tableName)
{
try
{
this.Connection.Open();
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
sqlDataAdapter.SelectCommand = new SqlCommand(queryText, this.Connection);
sqlDataAdapter.Fill(dataSet, tableName);
}
catch (SqlException sqlExc)
{
throw sqlExc;
}
catch (Exception exc)
{
throw exc;
}
finally
{
this.Connection.Close();
}
}
/// <summary>
/// 执行一个存储过程,数据存储在该已存在的指定DataSet的指定的数据表名中
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程的参数</param>
/// <param name="dataSet">已存在的指定DataSet对象</param>
/// <param name="tableName">指定的DataTable名称</param>
protected void RunProcedure( string storedProcName, IDataParameter [] parameters, DataSet dataSet, string tableName)
{
this.Connection.Open();
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
sqlDataAdapter.SelectCommand = BuildIntCommand( storedProcName, parameters);
sqlDataAdapter.Fill( dataSet, tableName);
this.Connection.Close();
}