下而是DbBase类的整理，参考了国外先进的设计法，并结合自身需要作了符合自己需要的数据基类。
声明：本类有一定自身特色，借签使用最好能结合自己程序。本类主要调用SQL SERVER的存储过程，但也能使用SQL命令。如果以ACCESS或主要以SQL 命令为主，最好能稍加修改。
using System;
using System.Data;
using System.Data.SqlClient;
namespace Hellosea.WebModule.Data
{
/// <summary>
/// 数据库的基础类，所有的数据库类均基于该类，为网站的核心程序。
/// 数据库其它类均派生此类。
/// </summary>
public class DbBase
{
private string mConnectionString;
protected SqlConnection DbConnection;
protected CommandType myCommandType; // 用于标志执行SQL或存储过程等
/// <summary>
/// 受保护的只读连接字符串属性
/// 派生类可以继承该属性
/// </summary>
protected string ConnectionString
{
get
{
return mConnectionString;
}
}
/// <summary>
/// 参数化的构造函数，并建立数据库连接
/// </summary>
/// <param name="newConnectionString">数据库连接字符串</param>
public DbBase(string newConnectionString)
{
mConnectionString = newConnectionString;
DbConnection = new SqlConnection(mConnectionString);
myCommandType = CommandType.StoredProcedure;
}
/// <summary>
/// 建立一SqlCommand的原型，这一新的命令通常用于执行一命令，并返回数值
/// </summary>
/// <param name="cmdString">命令SQL或存储过程名</param>
/// <param name="parameters">IDataParameter类的数组</param>
/// <returns>最新的SqlCommand实例</returns>
private SqlCommand BuildIntCommand(string cmdString, IDataParameter[] parameters)
{
SqlCommand command = BuildQueryCommand( cmdString, parameters );
command.Parameters.Add( new SqlParameter( "ReturnValue",
SqlDbType.Int,
4, // Size
ParameterDirection.ReturnValue,
false, // is nullable
0, // byte precision
0, // byte scale
string.Empty,
DataRowVersion.Default,
null ));
return command;
}
/// <summary>
/// 建立一SqlCommand的原型，这一新的命令通常用于把
/// SELECT语句的结果返回给DataSet或DataReader。
/// </summary>
/// <param name="cmdString">SQL命令或存储过程名</param>
/// <param name="parameters">IDataParameter类的数组</param>
/// <returns></returns>
private SqlCommand BuildQueryCommand( string cmdString, IDataParameter[] parameters )
{
SqlCommand command = new SqlCommand( cmdString, DbConnection );
command.CommandType = myCommandType;
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add( parameter );
}
return command;
}
/// <summary>
/// 建立一SqlCommand的原型，这一新的命令通常用于把
/// SELECT语句的结果返回给DataSet或DataReader。
/// </summary>
/// <param name="cmdString">命令SQL或存储过程名</param>
private SqlCommand BuildQueryCommand( string cmdString )
{
SqlCommand command = new SqlCommand( cmdString, DbConnection );
command.CommandType = myCommandType;
return command;
}
/// <summary>
/// 执行SqlCommand命令，返回整型ID号
/// </summary>
/// <param name="cmdString">执行的SQL语句</param>
/// <param name="parameters">IDataParameter类的数组</param>
/// <returns>返回整型ID号</returns>
protected int RunCommand( string cmdString, IDataParameter[] parameters, out int rowsAffected )
{
int result;
if (DbConnection.State == ConnectionState.Closed) DbConnection.Open();
SqlCommand command = BuildIntCommand( cmdString, parameters );
rowsAffected = command.ExecuteNonQuery();
result = (int)command.Parameters["ReturnValue"].Value;
DbConnection.Close();
return result;
}
/// <summary>
/// 执行SqlCommand命令，不返回值
/// </summary>
/// <param name="cmdString">执行的SQL语句</param>
/// <param name="parameters">IDataParameter类的数组</param>
protected void RunCommand( string cmdString, IDataParameter[] parameters )
{
if (DbConnection.State == ConnectionState.Closed) DbConnection.Open();
SqlCommand command = BuildQueryCommand( cmdString, parameters );
command.ExecuteNonQuery();
DbConnection.Close();
}
/// <summary>
/// 执行SqlCommand命令，不返回值
/// </summary>
/// <param name="cmdString">执行的SQL语句</param>
protected void RunCommand( string cmdString )
{
if (DbConnection.State == ConnectionState.Closed) DbConnection.Open();
SqlCommand command = BuildQueryCommand( cmdString );
command.ExecuteNonQuery();
DbConnection.Close();
}
/// <summary>
/// 执行查询，并返回SqlDataReader查询记录
/// </summary>
/// <param name="cmdString">执行的SQL语句</param>
/// <returns>返回SqlDataReader查询记录</returns>
protected SqlDataReader GetDataReader(string cmdString, IDataParameter[] parameters)
{
SqlDataReader returnReader;
if (DbConnection.State == ConnectionState.Closed) DbConnection.Open();
SqlCommand command = BuildQueryCommand( cmdString, parameters );
returnReader = command.ExecuteReader();
//DbConnection.Close();
return returnReader;
}
/// <summary>
/// 执行查询，并返回SqlDataReader查询记录
/// </summary>
/// <param name="cmdString">执行的SQL语句</param>
/// <returns>返回SqlDataReader查询记录</returns>
protected SqlDataReader GetDataReader(string cmdString)
{
SqlDataReader returnReader;
if (DbConnection.State == ConnectionState.Closed) DbConnection.Open();
SqlCommand command = BuildQueryCommand( cmdString );
returnReader = command.ExecuteReader();
//DbConnection.Close();
return returnReader;
}
/// <summary>
/// 执行查询，并返回DataSet查询数据集
/// </summary>
/// <param name="cmdStringName">执行的SQL语句</param>
/// <param name="parameters">IDataParameter类的数组</param>
/// <param name="tableName">要填充数据集的表名</param>
/// <returns>返回DataSet类型数据集</returns>
protected DataSet GetDataSet(string cmdStringName, IDataParameter[] parameters, string tableName )
{
DataSet dataSet = new DataSet();
if (DbConnection.State == ConnectionState.Closed) DbConnection.Open();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = BuildQueryCommand( cmdStringName, parameters );
adapter.Fill( dataSet, tableName );
DbConnection.Close();
return dataSet;
}
/// <summary>
/// 执行查询，并返回DataSet查询数据集
/// </summary>
/// <param name="cmdStringName">执行的SQL语句</param>
/// <param name="tableName">要填充数据集的表名</param>
/// <returns>返回DataSet类型数据集</returns>
protected DataSet GetDataSet(string cmdStringName, string tableName )
{
DataSet dataSet = new DataSet();
if (DbConnection.State == ConnectionState.Closed) DbConnection.Open();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = BuildQueryCommand( cmdStringName );
adapter.Fill( dataSet, tableName );
DbConnection.Close();
return dataSet;
}
/// <summary>
/// 执行查询，把数据填充至指定数据集中
/// </summary>
/// <param name="cmdStringName">执行的SQL语句</param>
/// <param name="tableName">要填充数据集的表名</param>
/// <param name="dataSet">填充数据集</param>
protected void GetDataSet(string cmdStringName, IDataParameter[] parameters, DataSet dataSet, string tableName )
{
if (DbConnection.State == ConnectionState.Closed) DbConnection.Open();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = BuildQueryCommand( cmdStringName, parameters );
adapter.Fill( dataSet, tableName );
DbConnection.Close();
}
/// <summary>
/// 关闭数据库
/// </summary>
protected void CloseConnection()
{
if (DbConnection.State == ConnectionState.Open)
{
DbConnection.Close();
}
}
}
}
