文章来源:IT工程信息网 http://www.systhinker.com/?viewnews-11676
我们接着上文继续完成我们的ado.net简单实践。
五、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.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.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、配置文件
< 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.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)
可以看出,工程里的文件结构还是很清晰的,需要学习的童鞋不妨下载使用试试看吧。
文章来源:IT工程信息网 http://www.systhinker.com/?viewnews-11676