关于Linq To Sql的效率和设计的问题<<ORM>>
本帖最后由 anzhiqiang_touzi 于 2011-12-22 23:38:56 编辑 快新年了：先祝大家新年好。很久没征求大家的意见了。现在想征求大家的意义和设计思路。
最近在研究ORM架构。看了很多ORM架构。都不是很满意。不是配置复杂就是速度慢得像老牛样。
但是我还是选择了一款"老牛":Linq To SQL
先说说我的设计思路：改进Linq To SQL原始的:增加，修改，删除，提高效率
官方提供的
新增方式:
DataClasses1DataContext ct = new DataClasses1DataContext();
for (int i = 0; i < 10000; i++)
{
Common_Gen m = new Common_Gen();
m.GenId = 1;
m.GenType = "Type";
m.Memo = "Memo";
m.UpdateDate = DateTime.Now;
ct.Common_Gen.InsertOnSubmit(m);
}
ct.SubmitChanges();
/// <summary>
/// 新增数据
/// </summary>
/// <typeparam name="TEntity">实体</typeparam>
/// <param name="table">Table</param>
/// <returns>n</returns>
public static int Add<TEntity>(this Table<TEntity> table, TEntity entity) where TEntity : class
{
DbCommand cmd = table.Context.Connection.CreateCommand();
Type entityType = typeof(TEntity);
var metaTable = table.Context.Mapping.GetTable(entityType);
ReadOnlyCollection<MetaDataMember> dataMembers = metaTable.RowType.DataMembers;
List<object> values = new List<object>();
//自动增长列
string IsDbGeneratedColumnName = null;
StringBuilder sbColumnNames = new StringBuilder();
StringBuilder sbValues = new StringBuilder();
foreach (MetaDataMember mm in dataMembers)
{
if (!mm.IsDbGenerated && mm.DbType != null)
{
sbColumnNames.Append("[" + mm.Name + "],");
sbValues.Append("@" + mm.Name + ",");
DbParameter ps = cmd.CreateParameter();
ps.ParameterName = mm.Name;
ps.Value = entityType.GetProperty(mm.Name).GetValue(entity, null);
cmd.Parameters.Add(ps);
}
if (mm.IsDbGenerated && mm.IsPrimaryKey)
{
IsDbGeneratedColumnName = mm.Name;
}
}
sbColumnNames.Remove(sbColumnNames.Length - 1, 1);
sbValues.Remove(sbValues.Length - 1, 1);
var tableName = "[" + metaTable.TableName + "]";
if (cmd.Connection.State != ConnectionState.Open)
{
cmd.Connection.Open();
}
string CommandText = "";
if (IsDbGeneratedColumnName == null)
{
CommandText = "INSERT INTO " + tableName + "(" + sbColumnNames + ") VALUES(" + sbValues + "); ";
cmd.CommandText = CommandText;
cmd.Transaction = table.Context.Transaction;
int n = cmd.ExecuteNonQuery();
return n;
}
else
{
CommandText = "INSERT INTO " + tableName + "(" + sbColumnNames + ") VALUES(" + sbValues + "); SELECT CONVERT(int,SCOPE_IDENTITY()) AS [value]";
cmd.CommandText = CommandText;
cmd.Transaction = table.Context.Transaction;
object n = cmd.ExecuteScalar();
entityType.GetProperty(IsDbGeneratedColumnName).SetValue(entity, n, null);
return 1;
}
}
//数据访问基类:采用直接提交方式，关闭DataContext对象跟踪
public class TemplateDao<T> : ITemplateDao<T> where T : class
{
public DataContext DataContext { get; set; }
/// <summary>
/// 增加实体
/// </summary>
public virtual void Add(T model)
{
Table<T> table = this.DataContext.GetTable<T>();
table.Add(model);
}
/// <summary>
/// 单表删除数据
/// </summary>
/// <param name="expression">Expression表达式</param>
/// <returns>影响的行数</returns>
public virtual int Delete(Expression<Func<T, bool>> expression)
{
Table<T> table = this.DataContext.GetTable<T>();
return table.Delete(expression);
}
/// <summary>
/// 单表更新数据
/// </summary>
/// <param name="model">实体</param>
public virtual int Update(T model)
{
Table<T> table = this.LoadAll();
return table.Update(model);
}
/// <summary>
/// 单表更新数据
/// </summary>
/// <param name="expression">条件表达式</param>
/// <param name="updater">更新表达式</param>
/// <returns></returns>
public virtual int UpdateWhere(Expression<Func<T, bool>> expression, Expression<Func<T, T>> updater)
{
Table<T> table = this.LoadAll();
return table.UpdateWhere(expression, updater);
}
}
/// <summary>
/// 单表删除数据
/// </summary>
public static int Delete<TEntity>(this Table<TEntity> table, Expression<Func<TEntity, bool>> filter) where TEntity : class
{
DbCommand cmd = table.Context.Connection.CreateCommand();
//获取表名
string tableName = table.Context.Mapping.GetTable(typeof(TEntity)).TableName;
//查询条件表达式转换成SQL的条件语句
ConditionBuilder builder = new ConditionBuilder();
builder.Build(filter.Body);
string sqlCondition = builder.Condition;
//SQL命令
string commandText = string.Format("DELETE FROM {0} WHERE {1}", tableName, sqlCondition);
cmd.CommandText = commandText;
//获取SQL参数数组
List<KeyValuePair<string, object>> args = builder.Arguments;
foreach (KeyValuePair<string, object> arg in args)
{
DbParameter ps = cmd.CreateParameter();
ps.ParameterName = arg.Key;
ps.Value = arg.Value;
cmd.Parameters.Add(ps);
}
if (table.Context.Connection.State != ConnectionState.Open)
{
table.Context.Connection.Open();
}
if (table.Context.Transaction != null)
{
cmd.Transaction = table.Context.Transaction;
}
return cmd.ExecuteNonQuery();
}
