五dal层数据访问实现 在这里我们使用前一篇文章里实现的数据持久化层和伪SqlMapper对象
实现数据操作
下面我们来看看Dal下核心的Dao如何实现
还记得我们在下面的dao类是怎么实现的吗？没错我们根据一个基类BaseDAO和它的构造函数实现dao的配置加载但是楼猪的实现没有那么复杂和强大本文的实现其实就是通过BaseDAO和构造函数获取数据库连接对象的key初始化一个SqlMapper然后利用SqlMapper对象进行基本的CRUD等等数据操作那么我们如何利用BaseDAO和构造函数就像以前在系列文章里的提到的Dal层下那样进行SqlMapper的初始化呢？
在AdoNetDataaccessMapper下我们定义公共的BaseDAO类 代码
namespace AdoNetDataAccessMapper
{
public abstract class BaseDAO
{
#region PRoperties
public SqlMapper SqlMapper { get; set; }
#endregion
#region Constructor
private BaseDAO()
{
}
/// <summary>
/// SqlMapper属性适用
/// </summary>
/// <param name=mapperName></param>
public BaseDAO(string mapperName)
{
thisSqlMapper = MapperUtillGetMapper(mapperName);
}
#endregion
}
}
初始化SqlMapper的实用类 代码
using System;
using SystemCollectionsGeneric;
using SystemConfiguration;
namespace AdoNetDataAccessMapper
{
using AdoNetDataAccessCoreContract;
using AdoNetDataAccessCoreImplement;
public sealed class MapperUtill
{
#region fields
public static string currentSqlKey = sqlConn;
public static int cmdTimeOut = ;
private static readonly object objSync = new object();
private static readonly IDictionary<string SqlMapper> dictMappers = new Dictionary<string SqlMapper>();
#endregion
#region constructor and methods
private MapperUtill()
{
}
static MapperUtill()
{
try
{
cmdTimeOut = intParse(ConfigurationManagerAppSettings[db_timeOut]);
}
catch
{
cmdTimeOut = ;
}
//实例化SqlDbMapper
for (int i = ; i < ConfigurationManagerConnectionStringsCount; i++)
{
string key = ConfigurationManagerConnectionStrings[i]Name;
string value = ConfigurationManagerConnectionStrings[i]ConnectionString;
CreateMapper(key value cmdTimeOut);
}
}
public static SqlMapper GetSqlMapper(string key)
{
return MapperUtillGetMapper(key);
}
public static SqlMapper GetCurrentSqlMapper()
{
return MapperUtillGetMapper(currentSqlKey);
}
public static void CreateMapper(string connKey string sqlConStr int connTimeOut)
{
IDbOperation operation = new SqlServer(sqlConStr connTimeOut);
SqlMapper mapper = new SqlMapper(operation);
dictMappersAdd(connKeyToUpper()Trim() mapper);//不区分大小写
}
public static SqlMapper GetMapper(string sqlConKey)
{
if (stringIsNullOrEmpty(sqlConKey))
{
throw new Exception(数据库连接字符串主键为空!);
}
sqlConKey = sqlConKeyToUpper();//不区分大小写
SqlMapper mapper = null;
if (dictMappersContainsKey(sqlConKey))
{
mapper = dictMappers[sqlConKey];
}
else
{
throw new Exception(stringFormat(没有{}所对应的数据库连接 sqlConKey));
}
return mapper;
}
/// <summary>
/// 释放所有
/// </summary>
public void Release()
{
foreach (KeyValuePair<string SqlMapper> kv in dictMappers)
{
SqlMapper mapper = kvValue;
if (mapper == null)
{
continue;
}
mapperCurrentDbOperationCloseConnection();
}
dictMappersClear();
}
#endregion
}
}
这个实用类的重要作用就是初始化配置文件里connectionStrings配置节点以获取sql连接对象必须的连接字符串
PersonDao类 下面就是针对具体的Person表的数据操作了
代码
using SystemCollectionsGeneric;
using SystemData;
namespace AdoNetDataAccessDalDao
{
using AdoNetDataAccessDalModel;
using AdoNetDataAccessDalUtility;
using AdoNetDataAccessMapper;
public class PersonDao : BaseDAO
{
public PersonDao()
: base(sqlConn)//sqlConn是<connectionStrings>配置节点的一个name
{
}
public int Insert(string sqlInsert)
{
int id = thisSqlMapperInsert(sqlInsert);
//object obj = thisSqlMapperExecuteScalar(sqlInsert SystemDataCommandTypeText null);
return id;
}
public bool BatchInsert(IList<Person> listModels)
{
int batchSize = ;
int copyTimeOut = ;
DataTable dt = DataTableHelperCreateTable<Person>(listModels);
bool flag = thisSqlMapperBatchInsert(typeof(Person)Name batchSize copyTimeOut dt);
return flag;
}
public int Update(string sqlUpdate)
{
int result = thisSqlMapperUpdate(sqlUpdate);
return result;
}
public IList<Person> SelectPersons(string sqlSelect)
{
IList<Person> listPersons = thisSqlMapperQueryForList<Person>(sqlSelect);
return listPersons;
}
public IDictionary<int Person> SelectDictPersons(string sqlSelect)
{
IDictionary<int Person> dictPersons = thisSqlMapperQueryForDictionary<int Person>(Id sqlSelect);
return dictPersons;
}
public DataTable SelectPersonTable(string sqlSelect)
{
DataTable dt = thisSqlMapperFillDataTable(sqlSelect CommandTypeText null);
return dt;
}
public DataSet SelectPersonDataSet(string sqlSelect)
{
DataSet ds = thisSqlMapperFillDataSet(sqlSelect CommandTypeText null);
return ds;
}
public int Delete(string sqlDelete)
{
int result = thisSqlMapperDelete(sqlDelete);
return result;
}
}
}
到这里一个dao类操作就实现了然后我们按步就班实现对外调用的服务接口在表现层调用吧
六表现层的调用配置文件 代码
<appSettings>
<add key=db_timeOut value=/>
</appSettings>
<connectionStrings>
<add name=sqlConn connectionString=Data Source=\sqlexpress; Initial Catalog=TestDb; User Id=sa; PassWord=;/>
<add name=sqlConnStr connectionString=Data Source=\sqlexpress; Initial Catalog=TestDb; User Id=sa; Password=;/>
<add name=sqlConnStr connectionString=Data Source=\sqlexpress; Initial Catalog=TestDb; User Id=sa; Password=;/>
</connectionStrings>
其中connectionString是必须的如果没有我们无法加载调用可用的SqlMapper
CRUD操作测试 代码
using System;
using SystemCollections;
using SystemCollectionsGeneric;
using SystemData;
namespace OOXXWebApp
{
using AdoNetDataAccessDal;
using AdoNetDataAccessDalModel;
public partial class _Default : SystemWebUIPage
{
protected void Page_Load(object sender EventArgs e)
{
if (!IsPostBack)
{
//增删改查测试
string sqlInsert = INSERT Person (FirstNameLastNameWeightHeight) VALUES( jeffwong) SELECT @@IDENTITY FROM Person(NOLOCK);
string sqlUpdate = UPDATE Person SET Height= WHERE Id=;
string sqlSelect = SELECT TOP * FROM Person(NOLOCK);
string sqlDelete = DELETE Person WHERE Id> AND Id<;
IList<Person> listModels = new List<Person>();
for (int i = ; i < ; i++)
{
Person model = new Person();
modelFirstName = Jeff;
modelLastName = Wong;
modelWeight = ;
modelHeight = ;
listModelsAdd(model);
}
ResponseWrite(Test Beginning<br/>);
int id = ServiceFactoryCreatePersonService()Add(sqlInsert);
ResponseWrite(stringFormat(<br/>Insert and return id:{} id));
bool flag = ServiceFactoryCreatePersonService()BatchInsert(listModels);
ResponseWrite(stringFormat(<br/> Batch Insert {} flag ? succeed : failed));
IList<Person> listPersons = ServiceFactoryCreatePersonService()GetPersons(sqlSelect);
ResponseWrite(stringFormat(<br/>Select pesons and return persons:{} listPersonsCount));
IDictionary<int Person> dictPersons = ServiceFactoryCreatePersonService()GetDictPersons(sqlSelect);
ResponseWrite(stringFormat(<br/>Select pesons and return dictionary persons:{} dictPersonsCount));
DataTable dt = ServiceFactoryCreatePersonService()GetPersonTable(sqlSelect);
ResponseWrite(stringFormat(<br/>Select pesons and return persons:{} dtRowsCount));
DataSet ds = ServiceFactoryCreatePersonService()GetPersonDataSet(sqlSelect);
ResponseWrite(stringFormat(<br/>Select pesons and return persons:{} dsTables[]RowsCount));
int affectNum = ServiceFactoryCreatePersonService()Modify(sqlUpdate);
ResponseWrite(stringFormat(<br/>Update and affect rows :{} affectNum));
affectNum = ;
affectNum = ServiceFactoryCreatePersonService()Remove(sqlDelete);
ResponseWrite(stringFormat(<br/>Delete and affect rows :{} affectNum));
ResponseWrite(<br/><br/>Test End);
}
}
}
}
这个就不用多说了吧表现层写SQL语句调用写好的服务就行了比较不舒服的地方就是SQL语句不得不写在类里面如果自动生成或者独立放在xml下实现可配置的形式那就更好了当然sql语句不是我们讨论的重点您有好的方法可以自己扩展实现更人性化的功能减少书写SQLl语句的工作
七最后对demo工程文件结构进行简单说明 数据持久化层AdoNetDataAccessCore
SqlMapper层AdoNetDataAccessMapper（引用AdoNetDataAccessCore）
具体数据操作使用层AdoNetDataAccessDal（引用AdoNetDataAccessMapper）
表现层AdoNetDataAccessWebApp（引用AdoNetDataAccessDal）
可以看出工程里的文件结构还是很清晰的需要学习的童鞋不妨下载使用试试看吧
