五、dal层数据访问实现

在这里我们使用前一篇文章里实现的数据持久化层和伪SqlMapper对象，实现数据操作。下面我们来看看Dal下核心的Dao如何实现：

还记得我们在IBatis.net下面的dao类是怎么实现的吗？没错，我们根据一个基类BaseDAO和它的构造函数，实现dao的配置加载。但是楼猪的实现没有那么复杂和强大，本文的实现其实就是通过BaseDAO和构造函数获取数据库连接对象的key，初始化一个SqlMapper，然后利用SqlMapper对象进行基本的CRUD等等数据操作。那么我们如何利用BaseDAO和构造函数就像以前在IBatis.net系列文章里的提到的Dal层下那样进行SqlMapper的初始化呢？

1、在AdoNetDataaccess.Mapper下我们定义公共的BaseDAO类

代码

namespace AdoNetDataAccess.Mapper

{

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)

{

this.SqlMapper = MapperUtill.GetMapper(mapperName);

}

#endregion

}

}

2、初始化SqlMapper的实用类

代码

using System;

using System.Collections.Generic;

using System.Configuration;

namespace AdoNetDataAccess.Mapper

{

using AdoNetDataAccess.Core.Contract;

using AdoNetDataAccess.Core.Implement;

public sealed class MapperUtill

{

#region fields

public static string currentSqlKey = "sqlConn";

public static int cmdTimeOut = 15;

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 = int.Parse(ConfigurationManager.AppSettings["db_timeOut"]);

}

catch

{

cmdTimeOut = 15;

}

//实例化SqlDbMapper

for (int i = 0; i < ConfigurationManager.ConnectionStrings.Count; i++)

{

string key = ConfigurationManager.ConnectionStrings[i].Name;

string value = ConfigurationManager.ConnectionStrings[i].ConnectionString;

CreateMapper(key, value, cmdTimeOut);

}

}

public static SqlMapper GetSqlMapper(string key)

{

return MapperUtill.GetMapper(key);

}

public static SqlMapper GetCurrentSqlMapper()

{

return MapperUtill.GetMapper(currentSqlKey);

}

public static void CreateMapper(string connKey, string sqlConStr, int connTimeOut)

{

IDbOperation operation = new SqlServer(sqlConStr, connTimeOut);

SqlMapper mapper = new SqlMapper(operation);

dictMappers.Add(connKey.ToUpper().Trim(), mapper);//不区分大小写

}

public static SqlMapper GetMapper(string sqlConKey)

{

if (string.IsNullOrEmpty(sqlConKey))

{

throw new Exception("数据库连接字符串主键为空!");

}

sqlConKey = sqlConKey.ToUpper();//不区分大小写

SqlMapper mapper = null;

if (dictMappers.ContainsKey(sqlConKey))

{

mapper = dictMappers[sqlConKey];

}

else

{

throw new Exception(string.Format("没有{0}所对应的数据库连接", sqlConKey));

}

return mapper;

}

/// <summary>

/// 释放所有

/// </summary>

public void Release()

{

foreach (KeyValuePair<string, SqlMapper> kv in dictMappers)

{

SqlMapper mapper = kv.Value;

if (mapper == null)

{

continue;

}

mapper.CurrentDbOperation.CloseConnection();

}

dictMappers.Clear();

}

#endregion

}

}

这个实用类的重要作用就是初始化配置文件里connectionStrings配置节点，以获取sql连接对象必须的连接字符串。
3、PersonDao类

下面就是针对具体的Person表的数据操作了：

代码

using System.Collections.Generic;

using System.Data;

namespace AdoNetDataAccess.Dal.Dao

{

using AdoNetDataAccess.Dal.Model;

using AdoNetDataAccess.Dal.Utility;

using AdoNetDataAccess.Mapper;

public class PersonDao : BaseDAO

{

public PersonDao()

: base("sqlConn")//sqlConn是<connectionStrings>配置节点的一个name

{

}

public int Insert(string sqlInsert)

{

int id = this.SqlMapper.Insert(sqlInsert);

//object obj = this.SqlMapper.ExecuteScalar(sqlInsert, System.Data.CommandType.Text, null);

return id;

}

public bool BatchInsert(IList<Person> listModels)

{

int batchSize = 50000;

int copyTimeOut = 60;

DataTable dt = DataTableHelper.CreateTable<Person>(listModels);

bool flag = this.SqlMapper.BatchInsert(typeof(Person).Name, batchSize, copyTimeOut, dt);

return flag;

}

public int Update(string sqlUpdate)

{

int result = this.SqlMapper.Update(sqlUpdate);

return result;

}

public IList<Person> SelectPersons(string sqlSelect)

{

IList<Person> listPersons = this.SqlMapper.QueryForList<Person>(sqlSelect);

return listPersons;

}

public IDictionary<int, Person> SelectDictPersons(string sqlSelect)

{

IDictionary<int, Person> dictPersons = this.SqlMapper.QueryForDictionary<int, Person>("Id", sqlSelect);

return dictPersons;

}

public DataTable SelectPersonTable(string sqlSelect)

{

DataTable dt = this.SqlMapper.FillDataTable(sqlSelect, CommandType.Text, null);

return dt;

}

public DataSet SelectPersonDataSet(string sqlSelect)

{

DataSet ds = this.SqlMapper.FillDataSet(sqlSelect, CommandType.Text, null);

return ds;

}

public int Delete(string sqlDelete)

{

int result = this.SqlMapper.Delete(sqlDelete);

return result;

}

}

}

到这里，一个dao类操作就实现了。然后我们按步就班实现对外调用的服务接口。在表现层调用吧。

六、表现层的调用

1、配置文件

代码

<appSettings>

<add key="db_timeOut" value="5000"/>

</appSettings>

<connectionStrings>

<add name="sqlConn" connectionString="Data Source=.sqlexpress; Initial Catalog=TestDb; User Id=sa; PassWord=123456;"/>

<add name="sqlConnStr1" connectionString="Data Source=.sqlexpress; Initial Catalog=TestDb; User Id=sa; Password=123456;"/>

<add name="sqlConnStr2" connectionString="Data Source=.sqlexpress; Initial Catalog=TestDb; User Id=sa; Password=123456;"/>

</connectionStrings>

其中，connectionString是必须的，如果没有，我们无法加载调用可用的SqlMapper。

2、CRUD操作测试

代码

using System;

using System.Collections;

using System.Collections.Generic;

using System.Data;

namespace OOXXWebApp

{

using AdoNetDataAccess.Dal;

using AdoNetDataAccess.Dal.Model;

public partial class _Default : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

//增删改查测试

string sqlInsert = "INSERT Person (FirstName,LastName,Weight,Height) VALUES( 'jeff','wong',70,180) SELECT @@IDENTITY FROM Person(NOLOCK)";

string sqlUpdate = "UPDATE Person SET Height=178 WHERE Id=1";

string sqlSelect = "SELECT TOP 100 * FROM Person(NOLOCK)";

string sqlDelete = "DELETE Person WHERE Id>10 AND Id<100";

IList<Person> listModels = new List<Person>();

for (int i = 0; i < 500000; i++)

{

Person model = new Person();

model.FirstName = "Jeff";

model.LastName = "Wong";

model.Weight = 70;

model.Height = 180;

listModels.Add(model);

}

Response.Write("Test Beginning......<br/>");

int id = ServiceFactory.CreatePersonService().Add(sqlInsert);

Response.Write(string.Format("<br/>Insert and return id:{0}", id));

bool flag = ServiceFactory.CreatePersonService().BatchInsert(listModels);

Response.Write(string.Format("<br/> Batch Insert {0}", flag "succeed" : "failed"));

IList<Person> listPersons = ServiceFactory.CreatePersonService().GetPersons(sqlSelect);

Response.Write(string.Format("<br/>Select pesons and return persons:{0}", listPersons.Count));

IDictionary<int, Person> dictPersons = ServiceFactory.CreatePersonService().GetDictPersons(sqlSelect);

Response.Write(string.Format("<br/>Select pesons and return dictionary persons:{0}", dictPersons.Count));

DataTable dt = ServiceFactory.CreatePersonService().GetPersonTable(sqlSelect);

Response.Write(string.Format("<br/>Select pesons and return persons:{0}", dt.Rows.Count));

DataSet ds = ServiceFactory.CreatePersonService().GetPersonDataSet(sqlSelect);

Response.Write(string.Format("<br/>Select pesons and return persons:{0}", ds.Tables[0].Rows.Count));

int affectNum = ServiceFactory.CreatePersonService().Modify(sqlUpdate);

Response.Write(string.Format("<br/>Update and affect rows :{0}", affectNum));

affectNum = 0;

affectNum = ServiceFactory.CreatePersonService().Remove(sqlDelete);

Response.Write(string.Format("<br/>Delete and affect rows :{0}", affectNum));

Response.Write("<br/><br/>Test End.");

}

}

}

}

这个就不用多说了吧，表现层写SQL语句调用写好的服务就行了。比较不舒服的地方就是SQL语句不得不写在类里面，如果自动生成或者独立放在xml下实现可配置的形式那就更好了，当然sql语句不是我们讨论的重点，您有好的方法可以自己扩展实现更人性化的功能，减少书写SQLl语句的工作。

七、最后，对demo工程文件结构进行简单说明。

1、数据持久化层AdoNetDataAccess.Core

2、SqlMapper层AdoNetDataAccess.Mapper（引用AdoNetDataAccess.Core）

3、具体数据操作使用层AdoNetDataAccess.Dal（引用AdoNetDataAccess.Mapper）

4、表现层AdoNetDataAccessWebApp（引用AdoNetDataAccess.Dal）