ado.net快速上手实践篇(二)

来源:网络时间:2011-06-23

  五、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)

发表评论

最新评论(共0条)