TableAccess<T> 源码
此源码为文章“.NET框架下ORM的一个轻量高效替代方案”的组成部分。
此类的基类 ReadonlyTableAccess<T> 源码：http://www.cnblogs.com/wfyfngu/archive/2010/01/28/1658374.html
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Text;
using MySql.Data.MySqlClient;
namespace FunTonn.MySqlDAL.SqlUtility {
public abstract class TableAccess<T> : ReadonlyTableAccess<T> {
/// <summary>
/// 使用指定的实体填充SQL中的参数值用于保存和修改等动作
/// </summary>
/// <param name="entity">已经被填充的数据实体</param>
/// <returns></returns>
abstract internal ColumnParameter[] FillColumnParameters(T entity);
/// <summary>
/// 生成SQL Insert命令
/// </summary>
/// <param name="entity"></param>
/// <param name="parameters"></param>
/// <returns></returns>
virtual protected string BuildInsertSql(T entity, out MySqlParameter[] parameters) {
parameters = null;
ColumnParameter[] cps = FillColumnParameters(entity);
CheckColumnParameter(cps);
StringBuilder sqlFields = new StringBuilder();
StringBuilder sqlValues = new StringBuilder();
ColumnParameter tempParameter = null;
IList<MySqlParameter> sortedParams = new List<MySqlParameter>(cps.Length);
foreach (TableColumn column in TableColumns) {
if (column.IsReadonly)
continue;
tempParameter = GetParameterByColumn(column, cps);
if (tempParameter == null)
throw new ColumnParameterNotSuppliedException(column.Name);
sqlFields.AppendFormat("{0},", tempParameter.Column.Name);
sqlValues.AppendFormat("{0},", tempParameter.Parameter.ParameterName);
System.Diagnostics.Debug.WriteLine(tempParameter.Parameter.ParameterName);
sortedParams.Add(tempParameter.Parameter);
}
parameters = new MySqlParameter[sortedParams.Count];
sortedParams.CopyTo(parameters, 0);
// 生成Insert命令
sqlFields.Remove(sqlFields.Length - 1, 1); // 删除最后一个逗号
sqlValues.Remove(sqlValues.Length - 1, 1); // 删除最后一个逗号
return string.Format(
"insert into {0} ({1}) values ({2})",
TableName,
sqlFields.ToString(),
sqlValues.ToString()
);
}
/// <summary>
/// 生成 SQL Update 命令（针对主键修改）
/// </summary>
/// <param name="primaryKeyValue">主键值</param>
/// <param name="newEntity"></param>
/// <param name="bound"></param>
/// <param name="excluded"></param>
/// <param name="parameters"></param>
/// <returns></returns>
virtual internal string BuildUpdateSql(object primaryKeyValue, T newEntity, ModifyBounds bound, TableColumn[] excluded, out MySqlParameter[] parameters) {
parameters = null;
ColumnParameter[] cps = FillColumnParameters(newEntity);
CheckColumnParameter(cps);
ColumnParameter tempParameter = null;
IList<MySqlParameter> sortedParams = new List<MySqlParameter>(cps.Length);
// 挑选出需要修改的字段
// 注意排除readonly的字段
TableColumnCollection validColumns = GetColumnToUpdate(bound, excluded);
// 开始构建SQL
StringBuilder sqlUpdate = new StringBuilder();
sqlUpdate.AppendFormat("update {0} set ", TableName);
bool columnAdded = false;
foreach (TableColumn column in validColumns) {
if (column.IsReadonly)
continue;
tempParameter = GetParameterByColumn(column, cps);
if (tempParameter == null)
throw new ColumnParameterNotSuppliedException(column.Name);
sqlUpdate.AppendFormat("{0}={1},", tempParameter.Column.Name, tempParameter.Parameter.ParameterName);
sortedParams.Add(tempParameter.Parameter);
if (!columnAdded) columnAdded = true;
}
if (columnAdded) {
sqlUpdate.Remove(sqlUpdate.Length - 1, 1); // 删除最后一个逗号
}
sqlUpdate.AppendFormat(" where {0}=?primaryKeyValue", TablePrimaryColumn.Name);
sortedParams.Add(new MySqlParameter("?primaryKeyValue", primaryKeyValue));
parameters = new MySqlParameter[sortedParams.Count];
sortedParams.CopyTo(parameters, 0);
return sqlUpdate.ToString();
}
/// <summary>
/// 为目标数据表所以经添加的字段快速赋值，
/// 这个值将在执行Save()和Modify()等操作时使用。
/// 使用该方法必须逐个为每一个已经添加的字段赋值，包括被标记为Readonly的字段
/// </summary>
/// <param name="values"></param>
/// <returns></returns>
internal ColumnParameter[] BuildColumnParameter(params object[] values) {
if (values == null)
throw new ArgumentNullException("values");
// 检查传入的值是否和数据表字段数一致
int targetCount = TableColumns.Count;
if (values.Length != targetCount)
throw new ArgumentOutOfRangeException("values", "values 元素个数必须和数据表字段数一致。");
ColumnParameter[] result = new ColumnParameter[targetCount];
for (int i = 0; i < targetCount; i++) {
result[i] = new ColumnParameter(TableColumns[i], CreateMySqlParameter(i, values[i]));
}
return result;
}
/// <summary>
/// 保存一个实体到数据库
/// </summary>
/// <param name="entity"></param>
/// <param name="tran"></param>
virtual internal void Save(T entity, MySqlTransaction tran) {
try {
MySqlParameter[] parameters;
string sql = BuildInsertSql(entity, out parameters);
if (tran == null)
DBUtility.ExecuteNonQuery(CommandType.Text, sql, parameters);
else
DBUtility.ExecuteNonQuery(tran, CommandType.Text, sql, parameters);
} catch (Exception) {
throw;
}
}
/// <summary>
/// 修改具有指定主键值的行。
/// 如果目标数据表没有主键列，将抛出一个异常。
/// </summary>
/// <param name="primaryKeyValue">要修改的目标主键值</param>
/// <param name="newEntity">新的实体</param>
/// /// <param name="tran"></param>
/// <param name="bound">要修改的列的范围（修改所有列或不修改任何列）</param>
/// <param name="excluded">不受修改范围影响的列</param>
/// <returns></returns>
/// <exception cref="System.NotSupportedException">当TablePrimaryColumn为null时引发该异常</exception>
internal int Modify(object primaryKeyValue, T newEntity, MySqlTransaction tran, ModifyBounds bound, params TableColumn[] excluded) {
if (TablePrimaryColumn == null)
throw new NotSupportedException("This operation was NOT supported when TablePrimaryColumn equals null.");
try {
MySqlParameter[] parameters;
string sql = BuildUpdateSql(primaryKeyValue, newEntity, bound, excluded, out parameters);
System.Diagnostics.Trace.WriteLine(sql);
if (tran == null)
return DBUtility.ExecuteNonQuery(CommandType.Text, sql, parameters);
else
return DBUtility.ExecuteNonQuery(tran, CommandType.Text, sql, parameters);
} catch (Exception) {
throw;
}
}
/// <summary>
/// 删除符合条件的记录
/// </summary>
/// <param name="filter"></param>
/// <param name="tran"></param>
/// <returns></returns>
internal int Delete(SearchConditionCollection filter, MySqlTransaction tran) {
try {
// 没有指定任何条件
if (filter.Count < 1)
return 0;
string sql = string.Format(
"delete from {0} where {1}",
TableName, filter.ToString()
);
if (tran == null)
return DBUtility.ExecuteNonQuery(CommandType.Text, sql, null);
else
return DBUtility.ExecuteNonQuery(tran, CommandType.Text, sql, null);
} catch (Exception) {
throw;
}
}
/// <summary>
/// 保存一个实体到数据库
/// </summary>
/// <param name="entity"></param>
public void Save(T entity) {
try {
Save(entity, null);
} catch (Exception) {
throw;
}
}
/// <summary>
/// 使用指定的实体修改具有指定主键值的行
/// </summary>
/// <param name="primaryKeyValue">要修改的目标主键值</param>
/// <param name="newEntity">新的实体</param>
/// <returns></returns>
public int Modify(object primaryKeyValue, T newEntity) {
return Modify(primaryKeyValue, newEntity, null, ModifyBounds.All, null);
}
/// <summary>
/// 删除符合条件的记录
/// </summary>
/// <param name="filter"></param>
/// <returns></returns>
public int Delete(SearchConditionCollection filter) {
return Delete(filter, null);
}
// Privates
private void CheckColumnParameter(ColumnParameter[] waitToCheck) {
if (waitToCheck == null || waitToCheck.Length<1)
throw new ArgumentNullException(
string.Format("Method FillColumnParameters({0}) returns value can NOT be null or empty.", typeof(T))
);
}
private ColumnParameter GetParameterByColumn(TableColumn column, ColumnParameter[] source) {
foreach (ColumnParameter cp in source) {
if (cp.Column.Equals(column))
return cp;
}
return null;
}
private MySqlParameter CreateMySqlParameter(int columnIndex, object value) {
MySqlParameter param = new MySqlParameter();
param.ParameterName = "?" + TableName + "_" + columnIndex.ToString();
if (value == null)
param.Value = DBNull.Value;
else
param.Value = value;
return param;
}
// 获取需要执行更新操作的数据表字段
// 返回的列表中不包括主键但包含只读字段
private TableColumnCollection GetColumnToUpdate(ModifyBounds bound, params TableColumn[] excluded) {
TableColumnCollection validColumns = new TableColumnCollection();
if(excluded == null) excluded = new TableColumn[0];
// 如果默认为所有的字段都不更新，那么被排除的字段就是有效的字段
// 否则，哈哈
if (bound == ModifyBounds.None) {
validColumns.AddRange(excluded);
} else {
TableColumnCollection allColumns = TableColumns; // 所有字段
foreach (TableColumn tc in allColumns) {
if (!((IList)excluded).Contains(tc)) {
validColumns.Add(tc);
}
}
}
// 从有效的字段列表中删除主键
validColumns.Remove(TablePrimaryColumn);
return validColumns;
}
}
}
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Text;
using MySql.Data.MySqlClient;
namespace FunTonn.MySqlDAL.SqlUtility {
public abstract class TableAccess<T> : ReadonlyTableAccess<T> {
/// <summary>
/// 使用指定的实体填充SQL中的参数值用于保存和修改等动作
/// </summary>
/// <param name="entity">已经被填充的数据实体</param>
/// <returns></returns>
abstract internal ColumnParameter[] FillColumnParameters(T entity);
/// <summary>
/// 生成SQL Insert命令
/// </summary>
/// <param name="entity"></param>
/// <param name="parameters"></param>
/// <returns></returns>
virtual protected string BuildInsertSql(T entity, out MySqlParameter[] parameters) {
parameters = null;
ColumnParameter[] cps = FillColumnParameters(entity);
CheckColumnParameter(cps);
StringBuilder sqlFields = new StringBuilder();
StringBuilder sqlValues = new StringBuilder();
ColumnParameter tempParameter = null;
IList<MySqlParameter> sortedParams = new List<MySqlParameter>(cps.Length);
foreach (TableColumn column in TableColumns) {
if (column.IsReadonly)
continue;
tempParameter = GetParameterByColumn(column, cps);
if (tempParameter == null)
throw new ColumnParameterNotSuppliedException(column.Name);
sqlFields.AppendFormat("{0},", tempParameter.Column.Name);
sqlValues.AppendFormat("{0},", tempParameter.Parameter.ParameterName);
System.Diagnostics.Debug.WriteLine(tempParameter.Parameter.ParameterName);
sortedParams.Add(tempParameter.Parameter);
}
parameters = new MySqlParameter[sortedParams.Count];
sortedParams.CopyTo(parameters, 0);
// 生成Insert命令
sqlFields.Remove(sqlFields.Length - 1, 1); // 删除最后一个逗号
sqlValues.Remove(sqlValues.Length - 1, 1); // 删除最后一个逗号
return string.Format(
"insert into {0} ({1}) values ({2})",
TableName,
sqlFields.ToString(),
sqlValues.ToString()
);
}
/// <summary>
/// 生成 SQL Update 命令（针对主键修改）
/// </summary>
/// <param name="primaryKeyValue">主键值</param>
/// <param name="newEntity"></param>
/// <param name="bound"></param>
/// <param name="excluded"></param>
/// <param name="parameters"></param>
/// <returns></returns>
virtual internal string BuildUpdateSql(object primaryKeyValue, T newEntity, ModifyBounds bound, TableColumn[] excluded, out MySqlParameter[] parameters) {
parameters = null;
ColumnParameter[] cps = FillColumnParameters(newEntity);
CheckColumnParameter(cps);
ColumnParameter tempParameter = null;
IList<MySqlParameter> sortedParams = new List<MySqlParameter>(cps.Length);
// 挑选出需要修改的字段
// 注意排除readonly的字段
TableColumnCollection validColumns = GetColumnToUpdate(bound, excluded);
// 开始构建SQL
StringBuilder sqlUpdate = new StringBuilder();
sqlUpdate.AppendFormat("update {0} set ", TableName);
bool columnAdded = false;
foreach (TableColumn column in validColumns) {
if (column.IsReadonly)
continue;
tempParameter = GetParameterByColumn(column, cps);
if (tempParameter == null)
throw new ColumnParameterNotSuppliedException(column.Name);
sqlUpdate.AppendFormat("{0}={1},", tempParameter.Column.Name, tempParameter.Parameter.ParameterName);
sortedParams.Add(tempParameter.Parameter);
if (!columnAdded) columnAdded = true;
}
if (columnAdded) {
sqlUpdate.Remove(sqlUpdate.Length - 1, 1); // 删除最后一个逗号
}
sqlUpdate.AppendFormat(" where {0}=?primaryKeyValue", TablePrimaryColumn.Name);
sortedParams.Add(new MySqlParameter("?primaryKeyValue", primaryKeyValue));
parameters = new MySqlParameter[sortedParams.Count];
sortedParams.CopyTo(parameters, 0);
return sqlUpdate.ToString();
}
/// <summary>
/// 为目标数据表所以经添加的字段快速赋值，
/// 这个值将在执行Save()和Modify()等操作时使用。
/// 使用该方法必须逐个为每一个已经添加的字段赋值，包括被标记为Readonly的字段
/// </summary>
/// <param name="values"></param>
/// <returns></returns>
internal ColumnParameter[] BuildColumnParameter(params object[] values) {
if (values == null)
throw new ArgumentNullException("values");
// 检查传入的值是否和数据表字段数一致
int targetCount = TableColumns.Count;
if (values.Length != targetCount)
throw new ArgumentOutOfRangeException("values", "values 元素个数必须和数据表字段数一致。");
ColumnParameter[] result = new ColumnParameter[targetCount];
for (int i = 0; i < targetCount; i++) {
result[i] = new ColumnParameter(TableColumns[i], CreateMySqlParameter(i, values[i]));
}
return result;
}
/// <summary>
/// 保存一个实体到数据库
/// </summary>
/// <param name="entity"></param>
/// <param name="tran"></param>
virtual internal void Save(T entity, MySqlTransaction tran) {
try {
MySqlParameter[] parameters;
string sql = BuildInsertSql(entity, out parameters);
if (tran == null)
DBUtility.ExecuteNonQuery(CommandType.Text, sql, parameters);
else
DBUtility.ExecuteNonQuery(tran, CommandType.Text, sql, parameters);
} catch (Exception) {
throw;
}
}
/// <summary>
/// 修改具有指定主键值的行。
/// 如果目标数据表没有主键列，将抛出一个异常。
/// </summary>
/// <param name="primaryKeyValue">要修改的目标主键值</param>
/// <param name="newEntity">新的实体</param>
/// /// <param name="tran"></param>
/// <param name="bound">要修改的列的范围（修改所有列或不修改任何列）</param>
/// <param name="excluded">不受修改范围影响的列</param>
/// <returns></returns>
/// <exception cref="System.NotSupportedException">当TablePrimaryColumn为null时引发该异常</exception>
internal int Modify(object primaryKeyValue, T newEntity, MySqlTransaction tran, ModifyBounds bound, params TableColumn[] excluded) {
if (TablePrimaryColumn == null)
throw new NotSupportedException("This operation was NOT supported when TablePrimaryColumn equals null.");
try {
MySqlParameter[] parameters;
string sql = BuildUpdateSql(primaryKeyValue, newEntity, bound, excluded, out parameters);
System.Diagnostics.Trace.WriteLine(sql);
if (tran == null)
return DBUtility.ExecuteNonQuery(CommandType.Text, sql, parameters);
else
return DBUtility.ExecuteNonQuery(tran, CommandType.Text, sql, parameters);
} catch (Exception) {
throw;
}
}
/// <summary>
/// 删除符合条件的记录
/// </summary>
/// <param name="filter"></param>
/// <param name="tran"></param>
/// <returns></returns>
internal int Delete(SearchConditionCollection filter, MySqlTransaction tran) {
try {
// 没有指定任何条件
if (filter.Count < 1)
return 0;
string sql = string.Format(
"delete from {0} where {1}",
TableName, filter.ToString()
);
if (tran == null)
return DBUtility.ExecuteNonQuery(CommandType.Text, sql, null);
else
return DBUtility.ExecuteNonQuery(tran, CommandType.Text, sql, null);
} catch (Exception) {
throw;
}
}
/// <summary>
/// 保存一个实体到数据库
/// </summary>
/// <param name="entity"></param>
public void Save(T entity) {
try {
Save(entity, null);
} catch (Exception) {
throw;
}
}
/// <summary>
/// 使用指定的实体修改具有指定主键值的行
/// </summary>
/// <param name="primaryKeyValue">要修改的目标主键值</param>
/// <param name="newEntity">新的实体</param>
/// <returns></returns>
public int Modify(object primaryKeyValue, T newEntity) {
return Modify(primaryKeyValue, newEntity, null, ModifyBounds.All, null);
}
/// <summary>
/// 删除符合条件的记录
/// </summary>
/// <param name="filter"></param>
/// <returns></returns>
public int Delete(SearchConditionCollection filter) {
return Delete(filter, null);
}
// Privates
private void CheckColumnParameter(ColumnParameter[] waitToCheck) {
if (waitToCheck == null || waitToCheck.Length<1)
throw new ArgumentNullException(
string.Format("Method FillColumnParameters({0}) returns value can NOT be null or empty.", typeof(T))
);
}
private ColumnParameter GetParameterByColumn(TableColumn column, ColumnParameter[] source) {
foreach (ColumnParameter cp in source) {
if (cp.Column.Equals(column))
return cp;
}
return null;
}
private MySqlParameter CreateMySqlParameter(int columnIndex, object value) {
MySqlParameter param = new MySqlParameter();
param.ParameterName = "?" + TableName + "_" + columnIndex.ToString();
if (value == null)
param.Value = DBNull.Value;
else
param.Value = value;
return param;
}
// 获取需要执行更新操作的数据表字段
// 返回的列表中不包括主键但包含只读字段
private TableColumnCollection GetColumnToUpdate(ModifyBounds bound, params TableColumn[] excluded) {
TableColumnCollection validColumns = new TableColumnCollection();
if(excluded == null) excluded = new TableColumn[0];
// 如果默认为所有的字段都不更新，那么被排除的字段就是有效的字段
// 否则，哈哈
if (bound == ModifyBounds.None) {
validColumns.AddRange(excluded);
} else {
TableColumnCollection allColumns = TableColumns; // 所有字段
foreach (TableColumn tc in allColumns) {
if (!((IList)excluded).Contains(tc)) {
validColumns.Add(tc);
}
}
}
// 从有效的字段列表中删除主键
validColumns.Remove(TablePrimaryColumn);
return validColumns;
}
}
}
