我的ORM发展史
之所以叫这个名字是因为我也在重复造轮子写了个ORM框架,从08年到现在,随着技术的累计对这其中的一些东西也有些领悟,恰巧今天的推荐头条也是关于ORM的,恰巧本人今天出差比较闲散,于是就忍不住要来献一下丑了.
起初,也就是08年,那会本人才刚从学校毕业,那会只知道PetShop比较出名,业界声誉较好,据说性能可以完胜Java,于是便学习了起来,从此以后在做所有项目必然出现DAL,BLL,Model这3层,由于大多项目根本没有跨数据库的需求,于是里面神马工厂模式,MySqlHelper,OracleHelper就全部丢掉了,唯一留下来的只有光荣的SqlHelper,那时SqlHelper的ExecuteDataReader,ExecuteNonequery,ExecuteDataset是屡试不爽啊.不过人总是懒惰和不安于现状的,后来还是觉得麻烦便萌生了写个工具去生成那些机械的DAL,BLL,Model,说干就干,便有了以下代码
public classs User
{
{
public string Id {get;set;}
public string Name{get;set;}
public string Password{get;set;}
public string Sex{get;set;}
public DateTime Birthday{get;set;}
}
public string Name{get;set;}
public string Password{get;set;}
public string Sex{get;set;}
public DateTime Birthday{get;set;}
}
public class UserDAL
{
pbblic void Insert(User user)
{
SqlParameter[] para = user.ToParameters();
SqlHelper.ExecNonequery(CommandType.StoreProcdure, "InsertUser",para);
}
public void Delete(int id)
{
SqlParameter[] para = id.ToParameters();
SqlHelper.ExecNonequery(CommandType.StoreProcdure, "DeleteUserById",para);
}
public void Update(User user)
{
SqlParameter[] para = user.ToParameters();
SqlHelper.ExecNonequery(CommandType.StoreProcdure, "UpdateUserById",para);
}
public List<User> GetUserList()
{
List<User> userList = new List<User>();
DataReader dr = SqlHelper.ExecDatareaderr(CommandType.StoreProcdure, "GetUserList",null);
return dr.ToList<User>();
}
}
{
pbblic void Insert(User user)
{
SqlParameter[] para = user.ToParameters();
SqlHelper.ExecNonequery(CommandType.StoreProcdure, "InsertUser",para);
}
public void Delete(int id)
{
SqlParameter[] para = id.ToParameters();
SqlHelper.ExecNonequery(CommandType.StoreProcdure, "DeleteUserById",para);
}
public void Update(User user)
{
SqlParameter[] para = user.ToParameters();
SqlHelper.ExecNonequery(CommandType.StoreProcdure, "UpdateUserById",para);
}
public List<User> GetUserList()
{
List<User> userList = new List<User>();
DataReader dr = SqlHelper.ExecDatareaderr(CommandType.StoreProcdure, "GetUserList",null);
return dr.ToList<User>();
}
}
public class UserBLL
{
private readonly UserDAL _userDAL = new UserDAL();
pbblic void Insert(User user)
{
_userDAL.Insert(user);
}
public void Delete(string id)
{
_userDAL.Delete(id);
}
public void Update(User user)
{
_userDAL.Update(user);
}
public List<User> GetUserList()
{
return _userDAL.GetUserList();
}
}
{
private readonly UserDAL _userDAL = new UserDAL();
pbblic void Insert(User user)
{
_userDAL.Insert(user);
}
public void Delete(string id)
{
_userDAL.Delete(id);
}
public void Update(User user)
{
_userDAL.Update(user);
}
public List<User> GetUserList()
{
return _userDAL.GetUserList();
}
}
怎么样,很熟悉吧,不过以上代码都是临时敲的,是伪代码,实际提供的方法可能更多,不过结构跟这个大同小异.工具的原理便是从数据库读出表的信息来,生成存储过程和这3层代码,使用的时候只需要把生成的sql执行一遍,再拷贝代码文件到项目里就行了,如果刚建项目的话,甚至可以连项目文件一起生成,刚写好这个工具的时候的确感觉小有所成啦.
又过了一段时间,突然觉得好像还是很繁琐,比如数据库如果改了一个字段,我就要从新生成,从新执行sql,从新覆盖Model,DAL,BLL,更加致命的是,我没有办法去写一些更上层通用的方法,比如,我写一个表数据查看功能,我就需要在这个页面写很多case
假设这个页面接受参数tablename,我便需要这样写:
switch(tablename)
{
case "User":
UserBLL bll = new UserBLL();
dataGrid.DataSource = bll.GetList();
break;
case "Product":
ProductBLL bll = new ProductBLL();
dataGrid.DataSource = bll.GetList();
break;
case "Log":
LogBLL bll = new LogBLL();
dataGrid.DataSource = bll.GetList();
break;
}
{
case "User":
UserBLL bll = new UserBLL();
dataGrid.DataSource = bll.GetList();
break;
case "Product":
ProductBLL bll = new ProductBLL();
dataGrid.DataSource = bll.GetList();
break;
case "Log":
LogBLL bll = new LogBLL();
dataGrid.DataSource = bll.GetList();
break;
}
很明显同样的代码我需要写很多遍,先不说优不优雅,起码比较麻烦,没达到我们前面说的"人都是懒的"这一目的.我们要怎么改进呢,可能有人会说给BLL加上IBLL,那样可以把case里的dataGrid.DataSource = bll.GetList();这一句话给放到switch块外面.也就是这样
switch(tablename)
{
IBLL bll;
case "User":
bll = new UserBLL();
break;
case "Product":
bll = new ProductBLL();
break;
case "Log":
bll = new LogBLL();
break;
}
dataGrid.DataSource = bll.GetList();
{
IBLL bll;
case "User":
bll = new UserBLL();
break;
case "Product":
bll = new ProductBLL();
break;
case "Log":
bll = new LogBLL();
break;
}
dataGrid.DataSource = bll.GetList();
还有人可能会说用反射,可是这里我们先不说这点,当然这样可以解决问题,我们说上面一种方式,我们需要引入接口,定义IBLL,如下
public interface IBLL<T> where T:class
{
void Insert(T model);
void Delete(string id);
void Update(T model);
List<T> GetList();
}
{
void Insert(T model);
void Delete(string id);
void Update(T model);
List<T> GetList();
}
然后将BLL层这样改
public class UserBLL:IBLL<User>
{
//跟上面的UserBLL一样,此处略
}
{
//跟上面的UserBLL一样,此处略
}
好,收工.可是做好了这步,第一还是没解决,一改数据库就要去执行sql,覆盖DAL,BLL,Model,为了解决这些问题,我决定
1.将存储过程方式改为生成sql方式(要实现这一点我们就的定义很多特性(Attrbute))
2.将BLL层拿掉,因为在这里,没有意义,也就是大家都在说的未了分层而分层,层次显得太过僵硬做作.
3.只生成Model层,DAL定义泛型接口,所有实现走框架(现在才能算框架,以上其实就是代码生成器)
经过改进便有了如下代码:
Model
//------------------------------------------------------------------------------
// <auto-generated>
// This code generated by the tool, do not propose to amend
// Generation time:2012/7/16 18:01:46
// </auto-generated>
//------------------------------------------------------------------------------
using System;
using System.Data;
using System.Runtime.Serialization;
using XDbFramework;
using System.Xml.Serialization;
using System.Diagnostics;
using System.CodeDom.Compiler;
namespace Model
{
[Serializable]
[Table(TableName = "Admin" ,Descripton = "管理员")]
[GeneratedCodeAttribute("System.Xml", "2.0.50727.4927")]
[DebuggerStepThroughAttribute()]
[XmlRootAttribute(Namespace = "http://www.scexin.com/", IsNullable = true)]
[DataContract(Namespace = "http://www.scexin.com/")]
public partial class Model_Admin
{
[Column(KeyType = KeyTypeEnum.PrimaryKey,ColumnName="AdminID",DbType=SqlDbType.Int, Index=0,Description="管理员编号")]
[DataMember(Order = 0)]
public int? AdminID{get;set;}
[Column(ColumnName="Passport",DbType=SqlDbType.VarChar, Index=1,Description="帐号")]
[DataMember(Order = 1)]
public string Passport{get;set;}
[Column(ColumnName="Password",DbType=SqlDbType.VarChar, Index=2,Description="密码")]
[DataMember(Order = 2)]
public string Password{get;set;}
[Column(ColumnName="AddTime",DbType=SqlDbType.DateTime, Index=3,Description="操作时间")]
[DataMember(Order = 3)]
public DateTime? AddTime{get;set;}
}
}
// <auto-generated>
// This code generated by the tool, do not propose to amend
// Generation time:2012/7/16 18:01:46
// </auto-generated>
//------------------------------------------------------------------------------
using System;
using System.Data;
using System.Runtime.Serialization;
using XDbFramework;
using System.Xml.Serialization;
using System.Diagnostics;
using System.CodeDom.Compiler;
namespace Model
{
[Serializable]
[Table(TableName = "Admin" ,Descripton = "管理员")]
[GeneratedCodeAttribute("System.Xml", "2.0.50727.4927")]
[DebuggerStepThroughAttribute()]
[XmlRootAttribute(Namespace = "http://www.scexin.com/", IsNullable = true)]
[DataContract(Namespace = "http://www.scexin.com/")]
public partial class Model_Admin
{
[Column(KeyType = KeyTypeEnum.PrimaryKey,ColumnName="AdminID",DbType=SqlDbType.Int, Index=0,Description="管理员编号")]
[DataMember(Order = 0)]
public int? AdminID{get;set;}
[Column(ColumnName="Passport",DbType=SqlDbType.VarChar, Index=1,Description="帐号")]
[DataMember(Order = 1)]
public string Passport{get;set;}
[Column(ColumnName="Password",DbType=SqlDbType.VarChar, Index=2,Description="密码")]
[DataMember(Order = 2)]
public string Password{get;set;}
[Column(ColumnName="AddTime",DbType=SqlDbType.DateTime, Index=3,Description="操作时间")]
[DataMember(Order = 3)]
public DateTime? AddTime{get;set;}
}
}
SqlAccessor
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Text;
using XDbFramework.Linq;
using System.Linq;
namespace XDbFramework
{
public class SqlAccessor<T> : IDbExceuteAble, IDAL<T> where T : class,new()
{
#region private fileds
private const string InsertSqlFormat = "INSERT INTO [{0}] ({1}) VALUES({2})";
private const string UpdateSqlFormat = "UPDATE [{0}] SET {1} WHERE {2}";
private const string DeleteSqlFormat = "DELETE [{0}] WHERE {1}";
private const string SelectFormat = "SELECT {0} FROM {1}";
private const string SelectByWhereFormat = "SELECT {0} FROM {1} WHERE {2}";
private const string SelectByWherePaginationFormat = @"WITH ORDEREDRESULTS AS
(
SELECT {0}, ROW_NUMBER()
OVER
(
ORDER BY {1}
)
AS ROWNUMBER
FROM [{2}] WHERE {3}
) SELECT {4} FROM ORDEREDRESULTS WHERE ROWNUMBER BETWEEN {5} AND {6}
SELECT COUNT(*) AS [COUNT] FROM [{7}] WHERE {8}
";
private static readonly TableAttribute TableInfo = DalHelper<T>.GetTableInfo();
private ExecNonQuery _execNonQuery = (a, b, c) => SqlHelper.ExecuteNonQuery(a, b, (SqlParameter[])c);
private ExecDataReader _execDataReader = (a, b, c) => SqlHelper.ExecuteReader(a, b, (SqlParameter[])c);
private readonly LinqQueryProvider<T> _linqQueryProvider;
#endregion
#region private methods
private DbExecuteState UpdateWithPredicate(T t, Predicate<ColumnAttribute> predicate = null)
{
var sb = new StringBuilder();
var pk = DalHelper<T>.GetPrimaryKeyInfo(t);
var columList = DalHelper.GetTypeColumns(t);
var uColumns = new UpdateColumns();
foreach (ColumnAttribute col in columList)
{
if (col.ColumnName != pk.ColumnName && (predicate == null || predicate(col)))
{
uColumns.Add(col.ColumnName, col.Value, col.ParameterType);
}
}
var condition = new Query(pk.ColumnName, CompareOperators.Equal, pk.Value, pk.OperatorType);
sb.AppendFormat(UpdateSqlFormat, TableInfo.TableName, uColumns.SqlString, condition.SqlString);
ExecNonQuery(CommandType.Text, sb.ToString(), null);
return DbExecuteState.Succeed;
}
#endregion
#region constructor
public SqlAccessor()
{
_linqQueryProvider = new LinqQueryProvider<T>(this);
}
#endregion
#region public method
public void Insert(T t)
{
var sb = new StringBuilder();
var columns = new StringBuilder();
var columnsParameter = new StringBuilder();
var pk =DalHelper<T>.GetPrimaryKeyInfo();
var columList = DalHelper.GetTypeColumns(t);
var index = 0;
if (!TableInfo.GenreratePK)
{
columList.RemoveAll(c => c.ColumnName ==pk.ColumnName);
}
var paras = new SqlParameter[columList.Count];
foreach (ColumnAttribute col in columList)
{
columns.AppendFormat("[{0}]", col.ColumnName);
columnsParameter.AppendFormat("@p_{0}", col.ColumnName);
if (index != columList.Count - 1)
{
columns.Append(",");
columnsParameter.Append(",");
}
paras[index] = new SqlParameter(string.Format("@p_{0}", col.ColumnName), (SqlDbType)col.DbType, col.FiledLength) { Value = col.Value.GetDbValue() };
index++;
}
sb.Append(string.Format(InsertSqlFormat, TableInfo.TableName, columns.ToString(), columnsParameter.ToString()));
ExecNonQuery(CommandType.Text, sb.ToString(), paras);
var dr = ExecDataReader(CommandType.Text, string.Format("Select * from [{0}] where [{1}] = IDENT_CURRENT('{2}')", TableInfo.TableName, pk.ColumnName, TableInfo.TableName), null);
var insertT = DalHelper<T>.ToEntity(dr, true);
DalHelper<T>.SetPrimaryKeyValue(t, DalHelper<T>.GetPrimaryKeyValue(insertT));
}
public DbExecuteState Delete(object id)
{
T t = new T();
DalHelper<T>.SetPrimaryKeyValue(t, id);
return Delete(t);
}
public DbExecuteState Delete(T t)
{
var sb = new StringBuilder();
var pk = DalHelper<T>.GetPrimaryKeyInfo(t);
sb.AppendFormat(DeleteSqlFormat, TableInfo.TableName, string.Format("{0}=@p_{1}", pk.ColumnName, pk.ColumnName));
var para = new SqlParameter() { ParameterName = "@p_" + pk.ColumnName, Value = pk.Value, SqlDbType = (SqlDbType)pk.DbType };
ExecNonQuery(CommandType.Text, sb.ToString(), new SqlParameter[] { para });
return DbExecuteState.Succeed;
}
public DbExecuteState Update(T t)
{
return UpdateWithPredicate(t);
}
public DbExecuteState UpdateIgnoreNull(T t)
{
return UpdateWithPredicate(t, col => !col.Value.IsDBNull());
}
public DbExecuteState UpdateSingleColumn(T t, string columName, object columValue)
{
DalHelper.SetModelValue(t, columName, columValue);
return UpdateWithPredicate(t, col => col.ColumnName == columName);
}
public DbExecuteState UpdateSingleColumn(object id, string columName, object columValue)
{
T t = new T();
DalHelper<T>.SetPrimaryKeyValue(t, id);
DalHelper.SetModelValue(t, columName, columValue);
return UpdateWithPredicate(t, col => col.ColumnName == columName);
}
public bool Exists(T t)
{
var lst = GetList(t);
return lst != null && lst.Count > 0;
}
public long GetCount()
{
var sb = new StringBuilder();
sb.AppendFormat(SelectFormat, "count(*)", TableInfo.TableName);
var dr = ExecDataReader(CommandType.Text, sb.ToString(), null);
try
{
dr.Read();
return dr[0].ToString().AsInt();
}
finally
{
dr.Close();
dr.Dispose();
}
}
public decimal Sum(Selector<T> selector, string column)
{
return Cacl(selector, string.Format("SUM({0})", column));
}
public decimal Avg(Selector<T> selector, string column)
{
return Cacl(selector, string.Format("AVG({0})", column));
}
private long Cacl(Selector<T> selector, string express)
{
var sb = new StringBuilder();
var condition = selector.Condition;
sb.AppendFormat(SelectByWhereFormat, express, TableInfo.TableName, condition.SqlString);
var dr = ExecDataReader(CommandType.Text, sb.ToString(), null);
try
{
dr.Read();
return dr[0].ToString().AsInt();
}
finally
{
dr.Close();
dr.Dispose();
}
}
public long GetCount(Selector<T> selector)
{
if (selector == null)
return GetCount();
return Cacl(selector, "count(*)");
}
public object GetResult(Selector<T> selector)
{
return GetResult<object>(selector);
}
public TResult GetResult<TResult>(Selector<T> selector)
{
var sb = new StringBuilder();
var condition = selector.Condition;
sb.AppendFormat(SelectByWhereFormat, selector.Colums, TableInfo.TableName, condition.SqlString);
var dr = ExecDataReader(CommandType.Text, sb.ToString(), null);
try
{
dr.Read();
return (TResult)dr[0];
}
finally
{
dr.Close();
dr.Dispose();
}
}
public T GetSingle(T t)
{
var list = GetList(t);
if (list != null && list.Count > 0)
return list[0];
return null;
}
public T GetSingle(object id)
{
var t = new T();
DalHelper<T>.SetPrimaryKeyValue(t, id);
return GetSingle(t);
}
public T GetSingle(Selector<T> selector)
{
var list = GetList(selector);
if (list == null || list.Count <= 0)
return null;
return list[0];
}
public List<T> GetList()
{
var sb = new StringBuilder();
sb.AppendFormat(SelectFormat, "*", TableInfo.TableName);
var dr = ExecDataReader(CommandType.Text, sb.ToString(), null);
var lst = DalHelper<T>.ToList(dr, closeDataReader: true);
return lst;
}
public List<T> GetList(Pagination pagination)
{
return GetList(new Selector<T>() { Pagination = pagination });
}
public List<T> GetList(Selector<T> selector)
{
var pk = DalHelper<T>.GetPrimaryKeyInfo();
var columns = DalHelper.GetTypeColumns<T>();
var sb = new StringBuilder();
var condition = selector.Condition;
string where = condition == null ? string.Empty : condition.SqlString;
where = string.IsNullOrEmpty(where) ? "1=1" : where;
var orderBy = selector.Order == null
? (pk == null ? columns[0].ColumnName : pk.ColumnName)
: selector.Order.ToSqlString(needPredicate: true);
sb.AppendFormat(SelectByWherePaginationFormat,
selector.Colums,
orderBy,
TableInfo.TableName,
where,
selector.Colums,
selector.Pagination.Offset,
selector.Pagination.Offset + selector.Pagination.PageSize,
TableInfo.TableName,
where);
var dr = ExecDataReader(CommandType.Text, sb.ToString(), null);
try
{
var lst = DalHelper<T>.ToList(dr);
if (dr.NextResult())
{
dr.Read();
selector.Pagination.RecordCount = dr[0].ToString().AsInt();
}
return lst;
}
finally
{
dr.Close();
dr.Dispose();
}
}
public List<T> GetList(T t)
{
var sb = new StringBuilder();
var condition = new Selector<T>(t, null, null).Condition;
sb.AppendFormat(SelectByWhereFormat, "*", TableInfo.TableName, condition.SqlString);
var dr = ExecDataReader(CommandType.Text, sb.ToString(), null);
return DalHelper<T>.ToList(dr, closeDataReader: true);
}
public List<T> Where(System.Linq.Expressions.Expression<Func<T, bool>> predicate)
{
IQueryable<T> tList = _linqQueryProvider.Where(predicate);
return tList.ToList();
}
public T Single(System.Linq.Expressions.Expression<Func<T, bool>> predicate)
{
List<T> list = Where(predicate);
if (list != null && list.Count > 0)
return list[0];
throw new XDbException("未找到满足条件的项");
}
public T SingleOrDefault(System.Linq.Expressions.Expression<Func<T, bool>> predicate)
{
List<T> list = Where(predicate);
if (list != null && list.Count > 0)
return list[0];
return default(T);
}
public int Count(System.Linq.Expressions.Expression<Func<T, bool>> predicate)
{
return _linqQueryProvider.Count(predicate);
}
public ExecNonQuery ExecNonQuery
{
get
{
return _execNonQuery;
}
set
{
if (value != null)
_execNonQuery = value;
}
}
public ExecDataReader ExecDataReader
{
get
{
return _execDataReader;
}
set
{
if (value != null)
_execDataReader = value;
}
}
#endregion
public IEnumerator<T> GetEnumerator()
{
return _linqQueryProvider.GetEnumerator();
}
System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator()
{
return _linqQueryProvider.GetEnumerator();
}
public Type ElementType
{
get { return typeof(T); }
}
public System.Linq.Expressions.Expression Expression
{
get { return _linqQueryProvider.Expression; }
}
public IQueryProvider Provider
{
get { return _linqQueryProvider.Provider; }
}
}
}
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Text;
using XDbFramework.Linq;
using System.Linq;
namespace XDbFramework
{
public class SqlAccessor<T> : IDbExceuteAble, IDAL<T> where T : class,new()
{
#region private fileds
private const string InsertSqlFormat = "INSERT INTO [{0}] ({1}) VALUES({2})";
private const string UpdateSqlFormat = "UPDATE [{0}] SET {1} WHERE {2}";
private const string DeleteSqlFormat = "DELETE [{0}] WHERE {1}";
private const string SelectFormat = "SELECT {0} FROM {1}";
private const string SelectByWhereFormat = "SELECT {0} FROM {1} WHERE {2}";
private const string SelectByWherePaginationFormat = @"WITH ORDEREDRESULTS AS
(
SELECT {0}, ROW_NUMBER()
OVER
(
ORDER BY {1}
)
AS ROWNUMBER
FROM [{2}] WHERE {3}
) SELECT {4} FROM ORDEREDRESULTS WHERE ROWNUMBER BETWEEN {5} AND {6}
SELECT COUNT(*) AS [COUNT] FROM [{7}] WHERE {8}
";
private static readonly TableAttribute TableInfo = DalHelper<T>.GetTableInfo();
private ExecNonQuery _execNonQuery = (a, b, c) => SqlHelper.ExecuteNonQuery(a, b, (SqlParameter[])c);
private ExecDataReader _execDataReader = (a, b, c) => SqlHelper.ExecuteReader(a, b, (SqlParameter[])c);
private readonly LinqQueryProvider<T> _linqQueryProvider;
#endregion
#region private methods
private DbExecuteState UpdateWithPredicate(T t, Predicate<ColumnAttribute> predicate = null)
{
var sb = new StringBuilder();
var pk = DalHelper<T>.GetPrimaryKeyInfo(t);
var columList = DalHelper.GetTypeColumns(t);
var uColumns = new UpdateColumns();
foreach (ColumnAttribute col in columList)
{
if (col.ColumnName != pk.ColumnName && (predicate == null || predicate(col)))
{
uColumns.Add(col.ColumnName, col.Value, col.ParameterType);
}
}
var condition = new Query(pk.ColumnName, CompareOperators.Equal, pk.Value, pk.OperatorType);
sb.AppendFormat(UpdateSqlFormat, TableInfo.TableName, uColumns.SqlString, condition.SqlString);
ExecNonQuery(CommandType.Text, sb.ToString(), null);
return DbExecuteState.Succeed;
}
#endregion
#region constructor
public SqlAccessor()
{
_linqQueryProvider = new LinqQueryProvider<T>(this);
}
#endregion
#region public method
public void Insert(T t)
{
var sb = new StringBuilder();
var columns = new StringBuilder();
var columnsParameter = new StringBuilder();
var pk =DalHelper<T>.GetPrimaryKeyInfo();
var columList = DalHelper.GetTypeColumns(t);
var index = 0;
if (!TableInfo.GenreratePK)
{
columList.RemoveAll(c => c.ColumnName ==pk.ColumnName);
}
var paras = new SqlParameter[columList.Count];
foreach (ColumnAttribute col in columList)
{
columns.AppendFormat("[{0}]", col.ColumnName);
columnsParameter.AppendFormat("@p_{0}", col.ColumnName);
if (index != columList.Count - 1)
{
columns.Append(",");
columnsParameter.Append(",");
}
paras[index] = new SqlParameter(string.Format("@p_{0}", col.ColumnName), (SqlDbType)col.DbType, col.FiledLength) { Value = col.Value.GetDbValue() };
index++;
}
sb.Append(string.Format(InsertSqlFormat, TableInfo.TableName, columns.ToString(), columnsParameter.ToString()));
ExecNonQuery(CommandType.Text, sb.ToString(), paras);
var dr = ExecDataReader(CommandType.Text, string.Format("Select * from [{0}] where [{1}] = IDENT_CURRENT('{2}')", TableInfo.TableName, pk.ColumnName, TableInfo.TableName), null);
var insertT = DalHelper<T>.ToEntity(dr, true);
DalHelper<T>.SetPrimaryKeyValue(t, DalHelper<T>.GetPrimaryKeyValue(insertT));
}
public DbExecuteState Delete(object id)
{
T t = new T();
DalHelper<T>.SetPrimaryKeyValue(t, id);
return Delete(t);
}
public DbExecuteState Delete(T t)
{
var sb = new StringBuilder();
var pk = DalHelper<T>.GetPrimaryKeyInfo(t);
sb.AppendFormat(DeleteSqlFormat, TableInfo.TableName, string.Format("{0}=@p_{1}", pk.ColumnName, pk.ColumnName));
var para = new SqlParameter() { ParameterName = "@p_" + pk.ColumnName, Value = pk.Value, SqlDbType = (SqlDbType)pk.DbType };
ExecNonQuery(CommandType.Text, sb.ToString(), new SqlParameter[] { para });
return DbExecuteState.Succeed;
}
public DbExecuteState Update(T t)
{
return UpdateWithPredicate(t);
}
public DbExecuteState UpdateIgnoreNull(T t)
{
return UpdateWithPredicate(t, col => !col.Value.IsDBNull());
}
public DbExecuteState UpdateSingleColumn(T t, string columName, object columValue)
{
DalHelper.SetModelValue(t, columName, columValue);
return UpdateWithPredicate(t, col => col.ColumnName == columName);
}
public DbExecuteState UpdateSingleColumn(object id, string columName, object columValue)
{
T t = new T();
DalHelper<T>.SetPrimaryKeyValue(t, id);
DalHelper.SetModelValue(t, columName, columValue);
return UpdateWithPredicate(t, col => col.ColumnName == columName);
}
public bool Exists(T t)
{
var lst = GetList(t);
return lst != null && lst.Count > 0;
}
public long GetCount()
{
var sb = new StringBuilder();
sb.AppendFormat(SelectFormat, "count(*)", TableInfo.TableName);
var dr = ExecDataReader(CommandType.Text, sb.ToString(), null);
try
{
dr.Read();
return dr[0].ToString().AsInt();
}
finally
{
dr.Close();
dr.Dispose();
}
}
public decimal Sum(Selector<T> selector, string column)
{
return Cacl(selector, string.Format("SUM({0})", column));
}
public decimal Avg(Selector<T> selector, string column)
{
return Cacl(selector, string.Format("AVG({0})", column));
}
private long Cacl(Selector<T> selector, string express)
{
var sb = new StringBuilder();
var condition = selector.Condition;
sb.AppendFormat(SelectByWhereFormat, express, TableInfo.TableName, condition.SqlString);
var dr = ExecDataReader(CommandType.Text, sb.ToString(), null);
try
{
dr.Read();
return dr[0].ToString().AsInt();
}
finally
{
dr.Close();
dr.Dispose();
}
}
public long GetCount(Selector<T> selector)
{
if (selector == null)
return GetCount();
return Cacl(selector, "count(*)");
}
public object GetResult(Selector<T> selector)
{
return GetResult<object>(selector);
}
public TResult GetResult<TResult>(Selector<T> selector)
{
var sb = new StringBuilder();
var condition = selector.Condition;
sb.AppendFormat(SelectByWhereFormat, selector.Colums, TableInfo.TableName, condition.SqlString);
var dr = ExecDataReader(CommandType.Text, sb.ToString(), null);
try
{
dr.Read();
return (TResult)dr[0];
}
finally
{
dr.Close();
dr.Dispose();
}
}
public T GetSingle(T t)
{
var list = GetList(t);
if (list != null && list.Count > 0)
return list[0];
return null;
}
public T GetSingle(object id)
{
var t = new T();
DalHelper<T>.SetPrimaryKeyValue(t, id);
return GetSingle(t);
}
public T GetSingle(Selector<T> selector)
{
var list = GetList(selector);
if (list == null || list.Count <= 0)
return null;
return list[0];
}
public List<T> GetList()
{
var sb = new StringBuilder();
sb.AppendFormat(SelectFormat, "*", TableInfo.TableName);
var dr = ExecDataReader(CommandType.Text, sb.ToString(), null);
var lst = DalHelper<T>.ToList(dr, closeDataReader: true);
return lst;
}
public List<T> GetList(Pagination pagination)
{
return GetList(new Selector<T>() { Pagination = pagination });
}
public List<T> GetList(Selector<T> selector)
{
var pk = DalHelper<T>.GetPrimaryKeyInfo();
var columns = DalHelper.GetTypeColumns<T>();
var sb = new StringBuilder();
var condition = selector.Condition;
string where = condition == null ? string.Empty : condition.SqlString;
where = string.IsNullOrEmpty(where) ? "1=1" : where;
var orderBy = selector.Order == null
? (pk == null ? columns[0].ColumnName : pk.ColumnName)
: selector.Order.ToSqlString(needPredicate: true);
sb.AppendFormat(SelectByWherePaginationFormat,
selector.Colums,
orderBy,
TableInfo.TableName,
where,
selector.Colums,
selector.Pagination.Offset,
selector.Pagination.Offset + selector.Pagination.PageSize,
TableInfo.TableName,
where);
var dr = ExecDataReader(CommandType.Text, sb.ToString(), null);
try
{
var lst = DalHelper<T>.ToList(dr);
if (dr.NextResult())
{
dr.Read();
selector.Pagination.RecordCount = dr[0].ToString().AsInt();
}
return lst;
}
finally
{
dr.Close();
dr.Dispose();
}
}
public List<T> GetList(T t)
{
var sb = new StringBuilder();
var condition = new Selector<T>(t, null, null).Condition;
sb.AppendFormat(SelectByWhereFormat, "*", TableInfo.TableName, condition.SqlString);
var dr = ExecDataReader(CommandType.Text, sb.ToString(), null);
return DalHelper<T>.ToList(dr, closeDataReader: true);
}
public List<T> Where(System.Linq.Expressions.Expression<Func<T, bool>> predicate)
{
IQueryable<T> tList = _linqQueryProvider.Where(predicate);
return tList.ToList();
}
public T Single(System.Linq.Expressions.Expression<Func<T, bool>> predicate)
{
List<T> list = Where(predicate);
if (list != null && list.Count > 0)
return list[0];
throw new XDbException("未找到满足条件的项");
}
public T SingleOrDefault(System.Linq.Expressions.Expression<Func<T, bool>> predicate)
{
List<T> list = Where(predicate);
if (list != null && list.Count > 0)
return list[0];
return default(T);
}
public int Count(System.Linq.Expressions.Expression<Func<T, bool>> predicate)
{
return _linqQueryProvider.Count(predicate);
}
public ExecNonQuery ExecNonQuery
{
get
{
return _execNonQuery;
}
set
{
if (value != null)
_execNonQuery = value;
}
}
public ExecDataReader ExecDataReader
{
get
{
return _execDataReader;
}
set
{
if (value != null)
_execDataReader = value;
}
}
#endregion
public IEnumerator<T> GetEnumerator()
{
return _linqQueryProvider.GetEnumerator();
}
System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator()
{
return _linqQueryProvider.GetEnumerator();
}
public Type ElementType
{
get { return typeof(T); }
}
public System.Linq.Expressions.Expression Expression
{
get { return _linqQueryProvider.Expression; }
}
public IQueryProvider Provider
{
get { return _linqQueryProvider.Provider; }
}
}
}
DataContext
//------------------------------------------------------------------------------
// <auto-generated>
// This code generated by the tool, do not propose to amend.
// Generation time:2012/4/27 9:32:20
// </auto-generated>
//------------------------------------------------------------------------------
using System;
using ExinSoft.Host.Model;
using XDbFramework;
namespace DALFactory
{
public partial class DataContext : IDisposable
{
public IDAL<Model_Account> Account
{
get
{
return _da.CreateDAL<Model_Account>();
}
}
public IDAL<Model_AccountOfReceiptsAndPayments> AccountOfReceiptsAndPayments
{
get
{
return _da.CreateDAL<Model_AccountOfReceiptsAndPayments>();
}
}
public IDAL<Model_AccountSnapshotRepository> AccountSnapshotRepository
{
get
{
return _da.CreateDAL<Model_AccountSnapshotRepository>();
}
}
public IDAL<Model_Admin> Admin
{
get
{
return _da.CreateDAL<Model_Admin>();
}
}
}
}
// <auto-generated>
// This code generated by the tool, do not propose to amend.
// Generation time:2012/4/27 9:32:20
// </auto-generated>
//------------------------------------------------------------------------------
using System;
using ExinSoft.Host.Model;
using XDbFramework;
namespace DALFactory
{
public partial class DataContext : IDisposable
{
public IDAL<Model_Account> Account
{
get
{
return _da.CreateDAL<Model_Account>();
}
}
public IDAL<Model_AccountOfReceiptsAndPayments> AccountOfReceiptsAndPayments
{
get
{
return _da.CreateDAL<Model_AccountOfReceiptsAndPayments>();
}
}
public IDAL<Model_AccountSnapshotRepository> AccountSnapshotRepository
{
get
{
return _da.CreateDAL<Model_AccountSnapshotRepository>();
}
}
public IDAL<Model_Admin> Admin
{
get
{
return _da.CreateDAL<Model_Admin>();
}
}
}
}
以上提供了核心类的实现方式,下面我们来看看调用方式,看是否优雅，框架实现的功能有,普通CRUD,存储过程执行,查询提供两种方式,即普通方式和Linq方式，如：
普通方式:
DataContext.Invoke(context =>
{
var selector = Selector<Model_Admin>
.NewQuery(m => m.AdminID >= 1)//开始一个查询
.Or(m => m.AdminID < 5) //Or连接
.And(m => m.AddTime > new DateTime(2010, 1, 1)) //And连接
.And(m => m.AddTime < new DateTime(2012, 1, 1))
.Page(1, 10) //分页，此处为第一页，每页10条
.Ascending(m => m.AdminID);//按AdminID升序排列
var list = context.Admin.GetList(selector);
});
{
var selector = Selector<Model_Admin>
.NewQuery(m => m.AdminID >= 1)//开始一个查询
.Or(m => m.AdminID < 5) //Or连接
.And(m => m.AddTime > new DateTime(2010, 1, 1)) //And连接
.And(m => m.AddTime < new DateTime(2012, 1, 1))
.Page(1, 10) //分页，此处为第一页，每页10条
.Ascending(m => m.AdminID);//按AdminID升序排列
var list = context.Admin.GetList(selector);
});
Linq方式:
DataContext.Invoke(context =>
{
var r = from a in context.Admin where a.AdminID == 1 select a;
var c = r.Count();
});
{
var r = from a in context.Admin where a.AdminID == 1 select a;
var c = r.Count();
});
存储过程支持:
代码
public class GetServiceReceiptsAndPaymentsResult
{
public int ServiceID { get; set; }
public decimal? sumMoney { get; set; }
}
[DbCommand("GetServiceReceiptsAndPayments")]
public class GetServiceReceiptsAndPayments
{
[DbParameter("AccountID")]
public int? AccountID { get; set; }
[DbParameter("StartTime")]
public DateTime? StartTime { get; set; }
[DbParameter("EndTime")]
public DateTime? EndTime { get; set; }
}
using (var context = new DataContext())
{
var result = context.SearchResultFromProcedure<GetServiceReceiptsAndPaymentsResult, GetServiceReceiptsAndPayments>(new GetServiceReceiptsAndPayments
{
AccountID = 1,
StartTime = new DateTime(2010, 1, 1),
EndTime = new DateTime(2012, 1, 1)
});//传递参数并获取列表
Assert.AreNotEqual(result, null);
}
{
public int ServiceID { get; set; }
public decimal? sumMoney { get; set; }
}
[DbCommand("GetServiceReceiptsAndPayments")]
public class GetServiceReceiptsAndPayments
{
[DbParameter("AccountID")]
public int? AccountID { get; set; }
[DbParameter("StartTime")]
public DateTime? StartTime { get; set; }
[DbParameter("EndTime")]
public DateTime? EndTime { get; set; }
}
using (var context = new DataContext())
{
var result = context.SearchResultFromProcedure<GetServiceReceiptsAndPaymentsResult, GetServiceReceiptsAndPayments>(new GetServiceReceiptsAndPayments
{
AccountID = 1,
StartTime = new DateTime(2010, 1, 1),
EndTime = new DateTime(2012, 1, 1)
});//传递参数并获取列表
Assert.AreNotEqual(result, null);
}
using (DataContext context = new DataContext())
{
long customCode = context.GetSingleValueFormProcedure<long>("CreateMaxOrderWithArea", new { @AreaId = areaid});//参数使用匿名类方式传入，此处表示调用存储过程CreateMaxOrderWithArea并返回一个64位整型的客户编号
}
更多方式:
调用方式
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using ExinSoft.Host.DALFactory;
using ExinSoft.Host.Model;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using XDbFramework;
namespace XDBFrameworkText
{
[TestClass]
public class UnitTest1
{
[TestMethod]
public void InsertTest()
{
string passport = "x" + DateTime.Now.Ticks;
var admin = new Model_Admin { AddTime = DateTime.Now, Passport = passport, Password = "123456" };
using (var context = new DataContext())
{
context.Admin.Insert(admin);
Model_Admin insertedAdmin = context.Admin.GetSingle(new Model_Admin { Passport = passport });
Assert.AreEqual(admin.Passport, insertedAdmin.Passport);
}
}
[TestMethod]
public void UpdateTest()
{
using (var context = new DataContext())
{
Model_Admin admin = context.Admin.GetSingle(new Model_Admin { AdminID = 11 });
admin.Password = "" + DateTime.Now.Ticks;
context.Admin.UpdateSingleColumn(admin, "Password", admin.Password);
Model_Admin admin1 = context.Admin.GetSingle(new Model_Admin { AdminID = 11 });
Assert.AreEqual(admin.Password, admin1.Password);
}
}
[TestMethod]
public void DeleteTest()
{
using (var context = new DataContext())
{
var admin = new Model_Admin { AdminID = 17 };
context.Admin.Delete(admin);
Model_Admin admin1 = context.Admin.GetSingle(new Model_Admin { AdminID = 17 });
Assert.AreEqual(admin1, null);
}
}
[TestMethod]
public void GetSingleTest()
{
using (var context = new DataContext())
{
Model_Admin admin = context.Admin.GetSingle(new Model_Admin { AdminID = 11 });
Assert.AreEqual(admin.AdminID, 11);
}
}
[TestMethod]
public void GetListTest()
{
using (var context = new DataContext())
{
List<Model_Admin> adminList = context.Admin.GetList();
Assert.AreNotEqual(adminList.Count, 0);
}
}
[TestMethod]
public void WhereTest()
{
using (var context = new DataContext())
{
var adminList = context.Admin.Where(m => m.AdminID == 11).ToList();
Assert.AreEqual(adminList[0].AdminID, 11);
}
}
[TestMethod]
public void SingleTest()
{
using (var context = new DataContext())
{
Model_Admin admin = context.Admin.Single(m => m.AdminID == 11);
Assert.AreEqual(admin.AdminID, 11);
}
}
public static readonly string BuyProduct_Code = "1105";
[TestMethod]
public void SingleTest2()
{
using (var context = new DataContext())
{
var server = context.Services.Single(m => m.ServiceCode == BuyProduct_Code);
Assert.AreEqual(server.ServiceID, 10);
}
}
[TestMethod]
public void SingleOrDefaultTest()
{
using (var context = new DataContext())
{
var aid = 11;
Model_Admin admin = context.Admin.SingleOrDefault(m => m.AdminID == aid);
Assert.AreEqual(admin.AdminID, 11);
}
}
[TestMethod]
public void PageTest()
{
using (var context = new DataContext())
{
List<Model_Admin> adminList = context.Admin.GetList(new Selector<Model_Admin>
{
Pagination = new Pagination
{
PageIndex = 1,
PageSize = 2
}
});
Assert.AreEqual(adminList.Count, 2);
}
}
[TestMethod]
public void SelectorTest()
{
using (var context = new DataContext())
{
var selector = new Selector<Model_Admin>
{
MinObj = new Model_Admin
{
AdminID = 1
},
MaxObj = new Model_Admin
{
AdminID = 11
},
Pagination = new Pagination
{
PageIndex = 1,
PageSize = 2
}
};
List<Model_Admin> adminList = context.Admin.GetList(selector);
Assert.AreEqual(selector.Pagination.RecordCount, 9);
}
}
[TestMethod]
public void QueryTest()
{
DataContext.Invoke(context =>
{
var selector = Selector<Model_Admin>
.NewQuery(m => m.AdminID >= 1)
.And(m => m.AdminID < 5)
.And(m => m.AddTime > new DateTime(2010, 1, 1))
.And(m => m.AddTime < new DateTime(2012, 1, 1))
.Page(1, 10)
.Ascending(m => m.AdminID);
var list = context.Admin.GetList(selector);
Assert.AreNotEqual(list, null);
});
}
[TestMethod]
public void LinqTest1()
{
DataContext.Invoke(context =>
{
var r = from a in context.Admin where a.AdminID == 1 select a;
var c = r.Count();
Assert.AreEqual(c, 1);
});
}
[TestMethod]
public void LinqTest2()
{
DataContext.Invoke(context =>
{
var r = from a in context.Admin where a.AdminID == 1 select a;
var list = r.ToList();
Assert.AreNotEqual(list, null);
});
}
[TestMethod]
public void LinqTest3()
{
DataContext.Invoke(context =>
{
var r = from a in context.Admin where a.AdminID == 1 && a.Passport == "admin" select a;
var list = r.ToList();
Assert.AreNotEqual(list, null);
});
}
[TestMethod]
public void LinqTest4()
{
DataContext.Invoke(context =>
{
var r = from a in context.Admin where a.AdminID == 1 || a.Passport.Contains("admin") || a.Password.StartsWith("123") || a.Password.EndsWith("456") select a;
var list = r.ToList();
Assert.AreNotEqual(list, null);
});
}
[TestMethod]
public void LinqTest5()
{
DataContext.Invoke(context =>
{
var r = from a in context.AdminHasRight
where a.AdminID == 1
select a;
var list = r.ToList();
Assert.AreNotEqual(list, null);
});
}
[TestMethod]
public void TransactionTest()
{
using (var context = new DataContext())
{
long count = context.Admin.Count();
var a = DataContextStatic.Recharge.Count(s => s.State == Convert.ToInt32(1));
string passport = "x" + DateTime.Now.Ticks;
context.BeginTransaction();
try
{
context.Admin.Insert(new Model_Admin
{
Passport = passport,
Password = "123456",
AddTime = DateTime.Now
});
context.Admin.Insert(new Model_Admin
{
Passport = passport + "_2",
Password = "123456",
AddTime = DateTime.Now
});
context.CommitTransaction();
}
catch
{
context.RollbackTransaction();
}
Assert.AreEqual(count, context.Admin.GetCount() - 2);
}
}
[TestMethod]
public void ProcTest1()
{
using (var context = new DataContext())
{
context.ExecuteProcedure("ClearingAccount");
}
}
[TestMethod]
public void ProcTest2()
{
using (var context = new DataContext())
{
var result = context.SearchResultFromProcedure<GetServiceReceiptsAndPaymentsResult, GetServiceReceiptsAndPayments>(new GetServiceReceiptsAndPayments
{
AccountID = 1,
StartTime = new DateTime(2010, 1, 1),
EndTime = new DateTime(2012, 1, 1)
});
Assert.AreNotEqual(result, null);
}
}
public void TestTmp()
{
var selector = Selector<Model_AirTicket>
.NewQuery(m => m.OrderID == "123")
.Or(m => m.UIdCard == "123");
var query = Query<Model_AirTicket>
.Where(air => air.AddTime >= new DateTime(2012, 1, 1))
.And(air => air.AddTime < new DateTime(2012, 12, 31));
selector.Condition.Connect(query, LogicOperators.Or);
}
[TestMethod]
public void TestTmp1()
{
var airticket = new
{
ShopID = 1,
IdCard = "456",
OrderId = "",
StartTime = new DateTime(2012, 1, 1),
EndTime = new DateTime(2012, 12, 1)
};
var t = new Model_AirTicket() { ShopID = 123 };
var selector = Selector<Model_AirTicket>
.NewQuery(air => air.ShopID == t.ShopID)
.Or(air => air.UIdCard == airticket.IdCard)
.Or(air => air.OrderID == "789")
.Or(air => air.AddTime >= airticket.StartTime)
.Or(air => air.AddTime < airticket.EndTime);
using (DataContext context = new DataContext())
{
var list = context.AirTicket.GetList(selector);
}
}
[TestMethod]
public void TestTmp2()
{
var airticket = new
{
ShopID = 1,
IdCard = "",
OrderId = "",
StartTime = new DateTime(2012, 1, 1),
EndTime = new DateTime(2012, 12, 1)
};
using (DataContext context = new DataContext())
{
var list = from a in context.AirTicket where a.ShopID == airticket.ShopID select a;
var t = list.ToList();
}
}
[TestMethod]
public void TestCount()
{
var aa = 1;
var a = DataContextStatic.Recharge.Count(s => s.State == Convert.ToInt32(aa));
}
}
public class GetServiceReceiptsAndPaymentsResult
{
public int ServiceID { get; set; }
public decimal? sumMoney { get; set; }
}
[DbCommand("GetServiceReceiptsAndPayments")]
public class GetServiceReceiptsAndPayments
{
[DbParameter("AccountID")]
public int? AccountID { get; set; }
[DbParameter("StartTime")]
public DateTime? StartTime { get; set; }
[DbParameter("EndTime")]
public DateTime? EndTime { get; set; }
}
}
using System.Collections.Generic;
using System.Data;
using System.Linq;
using ExinSoft.Host.DALFactory;
using ExinSoft.Host.Model;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using XDbFramework;
namespace XDBFrameworkText
{
[TestClass]
public class UnitTest1
{
[TestMethod]
public void InsertTest()
{
string passport = "x" + DateTime.Now.Ticks;
var admin = new Model_Admin { AddTime = DateTime.Now, Passport = passport, Password = "123456" };
using (var context = new DataContext())
{
context.Admin.Insert(admin);
Model_Admin insertedAdmin = context.Admin.GetSingle(new Model_Admin { Passport = passport });
Assert.AreEqual(admin.Passport, insertedAdmin.Passport);
}
}
[TestMethod]
public void UpdateTest()
{
using (var context = new DataContext())
{
Model_Admin admin = context.Admin.GetSingle(new Model_Admin { AdminID = 11 });
admin.Password = "" + DateTime.Now.Ticks;
context.Admin.UpdateSingleColumn(admin, "Password", admin.Password);
Model_Admin admin1 = context.Admin.GetSingle(new Model_Admin { AdminID = 11 });
Assert.AreEqual(admin.Password, admin1.Password);
}
}
[TestMethod]
public void DeleteTest()
{
using (var context = new DataContext())
{
var admin = new Model_Admin { AdminID = 17 };
context.Admin.Delete(admin);
Model_Admin admin1 = context.Admin.GetSingle(new Model_Admin { AdminID = 17 });
Assert.AreEqual(admin1, null);
}
}
[TestMethod]
public void GetSingleTest()
{
using (var context = new DataContext())
{
Model_Admin admin = context.Admin.GetSingle(new Model_Admin { AdminID = 11 });
Assert.AreEqual(admin.AdminID, 11);
}
}
[TestMethod]
public void GetListTest()
{
using (var context = new DataContext())
{
List<Model_Admin> adminList = context.Admin.GetList();
Assert.AreNotEqual(adminList.Count, 0);
}
}
[TestMethod]
public void WhereTest()
{
using (var context = new DataContext())
{
var adminList = context.Admin.Where(m => m.AdminID == 11).ToList();
Assert.AreEqual(adminList[0].AdminID, 11);
}
}
[TestMethod]
public void SingleTest()
{
using (var context = new DataContext())
{
Model_Admin admin = context.Admin.Single(m => m.AdminID == 11);
Assert.AreEqual(admin.AdminID, 11);
}
}
public static readonly string BuyProduct_Code = "1105";
[TestMethod]
public void SingleTest2()
{
using (var context = new DataContext())
{
var server = context.Services.Single(m => m.ServiceCode == BuyProduct_Code);
Assert.AreEqual(server.ServiceID, 10);
}
}
[TestMethod]
public void SingleOrDefaultTest()
{
using (var context = new DataContext())
{
var aid = 11;
Model_Admin admin = context.Admin.SingleOrDefault(m => m.AdminID == aid);
Assert.AreEqual(admin.AdminID, 11);
}
}
[TestMethod]
public void PageTest()
{
using (var context = new DataContext())
{
List<Model_Admin> adminList = context.Admin.GetList(new Selector<Model_Admin>
{
Pagination = new Pagination
{
PageIndex = 1,
PageSize = 2
}
});
Assert.AreEqual(adminList.Count, 2);
}
}
[TestMethod]
public void SelectorTest()
{
using (var context = new DataContext())
{
var selector = new Selector<Model_Admin>
{
MinObj = new Model_Admin
{
AdminID = 1
},
MaxObj = new Model_Admin
{
AdminID = 11
},
Pagination = new Pagination
{
PageIndex = 1,
PageSize = 2
}
};
List<Model_Admin> adminList = context.Admin.GetList(selector);
Assert.AreEqual(selector.Pagination.RecordCount, 9);
}
}
[TestMethod]
public void QueryTest()
{
DataContext.Invoke(context =>
{
var selector = Selector<Model_Admin>
.NewQuery(m => m.AdminID >= 1)
.And(m => m.AdminID < 5)
.And(m => m.AddTime > new DateTime(2010, 1, 1))
.And(m => m.AddTime < new DateTime(2012, 1, 1))
.Page(1, 10)
.Ascending(m => m.AdminID);
var list = context.Admin.GetList(selector);
Assert.AreNotEqual(list, null);
});
}
[TestMethod]
public void LinqTest1()
{
DataContext.Invoke(context =>
{
var r = from a in context.Admin where a.AdminID == 1 select a;
var c = r.Count();
Assert.AreEqual(c, 1);
});
}
[TestMethod]
public void LinqTest2()
{
DataContext.Invoke(context =>
{
var r = from a in context.Admin where a.AdminID == 1 select a;
var list = r.ToList();
Assert.AreNotEqual(list, null);
});
}
[TestMethod]
public void LinqTest3()
{
DataContext.Invoke(context =>
{
var r = from a in context.Admin where a.AdminID == 1 && a.Passport == "admin" select a;
var list = r.ToList();
Assert.AreNotEqual(list, null);
});
}
[TestMethod]
public void LinqTest4()
{
DataContext.Invoke(context =>
{
var r = from a in context.Admin where a.AdminID == 1 || a.Passport.Contains("admin") || a.Password.StartsWith("123") || a.Password.EndsWith("456") select a;
var list = r.ToList();
Assert.AreNotEqual(list, null);
});
}
[TestMethod]
public void LinqTest5()
{
DataContext.Invoke(context =>
{
var r = from a in context.AdminHasRight
where a.AdminID == 1
select a;
var list = r.ToList();
Assert.AreNotEqual(list, null);
});
}
[TestMethod]
public void TransactionTest()
{
using (var context = new DataContext())
{
long count = context.Admin.Count();
var a = DataContextStatic.Recharge.Count(s => s.State == Convert.ToInt32(1));
string passport = "x" + DateTime.Now.Ticks;
context.BeginTransaction();
try
{
context.Admin.Insert(new Model_Admin
{
Passport = passport,
Password = "123456",
AddTime = DateTime.Now
});
context.Admin.Insert(new Model_Admin
{
Passport = passport + "_2",
Password = "123456",
AddTime = DateTime.Now
});
context.CommitTransaction();
}
catch
{
context.RollbackTransaction();
}
Assert.AreEqual(count, context.Admin.GetCount() - 2);
}
}
[TestMethod]
public void ProcTest1()
{
using (var context = new DataContext())
{
context.ExecuteProcedure("ClearingAccount");
}
}
[TestMethod]
public void ProcTest2()
{
using (var context = new DataContext())
{
var result = context.SearchResultFromProcedure<GetServiceReceiptsAndPaymentsResult, GetServiceReceiptsAndPayments>(new GetServiceReceiptsAndPayments
{
AccountID = 1,
StartTime = new DateTime(2010, 1, 1),
EndTime = new DateTime(2012, 1, 1)
});
Assert.AreNotEqual(result, null);
}
}
public void TestTmp()
{
var selector = Selector<Model_AirTicket>
.NewQuery(m => m.OrderID == "123")
.Or(m => m.UIdCard == "123");
var query = Query<Model_AirTicket>
.Where(air => air.AddTime >= new DateTime(2012, 1, 1))
.And(air => air.AddTime < new DateTime(2012, 12, 31));
selector.Condition.Connect(query, LogicOperators.Or);
}
[TestMethod]
public void TestTmp1()
{
var airticket = new
{
ShopID = 1,
IdCard = "456",
OrderId = "",
StartTime = new DateTime(2012, 1, 1),
EndTime = new DateTime(2012, 12, 1)
};
var t = new Model_AirTicket() { ShopID = 123 };
var selector = Selector<Model_AirTicket>
.NewQuery(air => air.ShopID == t.ShopID)
.Or(air => air.UIdCard == airticket.IdCard)
.Or(air => air.OrderID == "789")
.Or(air => air.AddTime >= airticket.StartTime)
.Or(air => air.AddTime < airticket.EndTime);
using (DataContext context = new DataContext())
{
var list = context.AirTicket.GetList(selector);
}
}
[TestMethod]
public void TestTmp2()
{
var airticket = new
{
ShopID = 1,
IdCard = "",
OrderId = "",
StartTime = new DateTime(2012, 1, 1),
EndTime = new DateTime(2012, 12, 1)
};
using (DataContext context = new DataContext())
{
var list = from a in context.AirTicket where a.ShopID == airticket.ShopID select a;
var t = list.ToList();
}
}
[TestMethod]
public void TestCount()
{
var aa = 1;
var a = DataContextStatic.Recharge.Count(s => s.State == Convert.ToInt32(aa));
}
}
public class GetServiceReceiptsAndPaymentsResult
{
public int ServiceID { get; set; }
public decimal? sumMoney { get; set; }
}
[DbCommand("GetServiceReceiptsAndPayments")]
public class GetServiceReceiptsAndPayments
{
[DbParameter("AccountID")]
public int? AccountID { get; set; }
[DbParameter("StartTime")]
public DateTime? StartTime { get; set; }
[DbParameter("EndTime")]
public DateTime? EndTime { get; set; }
}
}
生成的代码包括Model和DataContext,其他均为框架实现.
这只是个开篇,框架还在完善中,如果有人感兴趣,我会提供下载.以后我还会讲到ORM中一些常见的概念,比如为什么要有DataContext,它有什么好处,如何跨数据库,优雅的代码是如何演变而来的.感谢你的阅读!
注：好吧，鉴于有人有意见，从“ORM发展史”，改为“我的ORM发展史” 里面跨度的确有些大，因为下班了，不想写，以后再补上吧
