基于存储过程的可扩展性数据访问框架
步骤一.
Web.config
<connectionStrings>
<add name="conn" connectionString="Data Source=7NSURWVGIQLM9YB;Initial Catalog=Techbbs;Persist Security Info=True;User ID=sa;Password=123456;Pooling=False" providerName="System.Data.SqlClient"/>
</connectionStrings>
创建配置信息类BBSConfiguration
获取配置信息的连接数据库字符串和providerName
publicstaticclassBBSConfiguration
{
publicstaticBBSConfiguration()
{
dbConnetctionString=ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
dbProvider=ConfigurationManager.ConnectionStrings["conn"].ProviderName;
}
privatereadonlystaticstringdbConnetctionString;
privatereadonlystaticstringdbProvider;
publicstaticstringDbConnetctionString
{
get
{
returnBBSConfiguration.dbConnetctionString;
}
}
publicstaticstringDbProvider
{
get
{
returndbConnetctionString;
}
}
}
步骤二.
创建通用数据库访问代码类(用到工厂模式,扩展性强)GenericDataAccess
/// <summary>创建基于存储过程的通用数据库Command
///
/// </summary>
/// <returns></returns>
publicstaticDbCommandCreateCommand()
{
//获取数据库组件类型
stringdataProviderName=BBSConfiguration.DbProvider;
//获取数据库连接字符串
stringconnectionString=BBSConfiguration.DbConnetctionString;
//根据数据库组件类型创建数据库工厂
DbProviderFactoryfactory=DbProviderFactories.GetFactory(dataProviderName);
//根据数据工厂创建数据库 连接
DbConnectionconn=factory.CreateConnection();
//根据数据库连接创建Command
DbCommandcmd=conn.CreateCommand();
//设置|Command类型为存储过程
cmd.CommandType=CommandType.StoredProcedure;
returncmd;
}
/// <summary>重写数据库通用ExcuteReader方法
///
/// </summary>
/// <param name="cmd"></param>
/// <returns></returns>
publicstaticDataTableExecuteReader(DbCommandcmd)
{
DataTabledt;
try
{
cmd.Connection.Open();
DbDataReaderda=cmd.ExecuteReader();
dt=newDataTable();
dt.Load(da);
}
catch (Exceptionex)
{
throwex;
}
finally
{
cmd.Connection.Close();
}
returndt;
}
步骤三.
创建业务逻辑类ForumAccess
/// <summary>获取主版块数据
///
/// </summary>
/// <returns></returns>
publicstaticDataTableGetForums()
{
DbCommandcmd=GenericDataAccess.CreateCommand();
cmd.CommandText="GetForms";
returnGenericDataAccess.ExecuteReader(cmd);
}
/// <summary>根据主版块的id获取子版块数据
///
/// </summary>
/// <param name="pid"></param>
/// <returns></returns>
publicstaticDataTableGetSubForumsByPid(stringpid)
{
DbCommandcmd=GenericDataAccess.CreateCommand();
cmd.CommandText="GetSubForums";
DbParameterparam=cmd.CreateParameter();
param.ParameterName="@pid";
param.DbType=DbType.Int32;
param.Value=pid;
cmd.Parameters.Add(param);
returnGenericDataAccess.ExecuteReader(cmd);
}