这篇主要是实现了实体类的数据库CRUD的操作。
在目前的ORM映射框架中，对于操作数据库的最后一个步骤就是动态生成操作数据库的SQL语句，而这无非也就是利用实体属性中的自定义映射机制来实现的，或者就是直接把实体类的属性名对应表的字段名，在本示例中，就是采用的后者。
第一、为了能够存储动态生成的SQL语句，新建了一个SQL语句的结构类，用来存放SQL语句以及参数：
/// <summary>
/// SQL结构类
/// </summary>
[Serializable]
class SqlStruct
{
public string SqlString { get; set; }
public ParamField[] ParamFields { get; set; }
}
/// <summary>
/// SQL参数
/// </summary>
[Serializable]
public class ParamField
{
public string ParamName { get; set; }
public string FieldName{ get; set; }
}
第二、实现构建SQL操作
根据用户的CRUD操作，动态的构建一个SQL操作语句，并别存入缓存中，以便下次执行相同的操作时直接从缓存中获取，提高性能，在这里新建了一个构建SQL的类：
class SqlGenerator
{
private SqlGenerator() { }
private static SqlGenerator singleton = new SqlGenerator();
public static SqlGenerator Instance()
{
return singleton;
}
/// <summary>
/// 构建CRUD操作SQL语句
/// </summary>
/// <param name="activeRecord"></param>
/// <param name="sqlOperationType"></param>
/// <returns></returns>
public SqlStruct GenerateSql(dynamic activeRecord, SqlOperationType sqlOperationType)
{
SqlStruct sqlStruct;
string key = null;
if (activeRecord is Type)
{
TableMapAttribute attr = Attribute.GetCustomAttribute(activeRecord, typeof(TableMapAttribute)) as TableMapAttribute;
key = GenerateKey(attr.TableName, sqlOperationType);
}
else
{
key = GenerateKey(activeRecord.TableName, sqlOperationType);
}
// 获取缓存
sqlStruct = CacheProxy.GetChchedString(key) as SqlStruct;
if (sqlStruct != null)
{
return sqlStruct;
}
switch (sqlOperationType)
{
case SqlOperationType.SimpleSelect:
sqlStruct = new SqlStruct() { SqlString = GenerateSimpleSelectSql(activeRecord)};
break;
case SqlOperationType.SelectByKey:
sqlStruct = GenerateFindByKeySql(activeRecord);
break;
case SqlOperationType.Insert:
sqlStruct = GenerateInsertSql(activeRecord);
break;
case SqlOperationType.Update:
sqlStruct = GenerateUpdateSql(activeRecord);
break;
case SqlOperationType.Delete:
sqlStruct = GenerateDeleteSql(activeRecord);
break;
default:
sqlStruct = null;
break;
}
// 增加缓存
CacheProxy.CacheObjectForEver(key, sqlStruct);
return sqlStruct;
}
/// <summary>
/// 构建根据主键ID来查询数据的SQL
/// </summary>
/// <param name="activeRecord"></param>
/// <returns></returns>
private SqlStruct GenerateFindByKeySql(dynamic type)
{
TableMapAttribute attr = Attribute.GetCustomAttribute(type, typeof(TableMapAttribute)) as TableMapAttribute;
return new SqlStruct()
{
SqlString = string.Format("SELECT * FROM {0} WHERE {1} = :{1}", attr.TableName, attr.PrimaryKey),
ParamFields = new ParamField[] { new ParamField() { ParamName = ":" + attr.PrimaryKey, FieldName = attr.PrimaryKey } }
};
}
/// <summary>
/// 构建查询SQL语句
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
private string GenerateSimpleSelectSql(dynamic type)
{
TableMapAttribute attr = Attribute.GetCustomAttribute(type, typeof(TableMapAttribute)) as TableMapAttribute;
return new StringBuilder("SELECT * ").Append(" FROM ").Append(attr.TableName).ToString();
}
/// <summary>
/// 构建新增SQL语句
/// </summary>
/// <param name="activeRecord"></param>
/// <returns></returns>
private SqlStruct GenerateInsertSql(dynamic activeRecord)
{
string[] columns = activeRecord.Columns;
string[] parameters = new string[activeRecord.Columns.Length];
ParamField[] paramField = new ParamField[activeRecord.Columns.Length];
for (int i = 0; i < columns.Length; i++)
{
parameters[i] = ":" + columns[i];
paramField[i] = new ParamField() { ParamName = parameters[i], FieldName = columns[i] };
}
return new SqlStruct()
{
SqlString = new StringBuilder("INSERT INTO ").Append(activeRecord.TableName).Append("(").Append(string.Join(",", columns)).Append(") VALUES(").Append(string.Join(",", parameters)).Append(")").ToString(),
ParamFields = paramField
};
}
/// <summary>
/// 构建更新SQL语句
/// </summary>
/// <param name="activeRecord"></param>
/// <returns></returns>
private SqlStruct GenerateUpdateSql(dynamic activeRecord)
{
// 得到所有的列
NameValueCollection allColumns = new NameValueCollection();
for (int i = 0; i < activeRecord.Columns.Length; i++)
{
allColumns.Add(activeRecord.Columns[i], activeRecord.Columns[i]);
}
// 去除主键列
allColumns.Remove(activeRecord.PrimaryKey);
string[] setString = new string[allColumns.Count];
ParamField[] paramField = new ParamField[allColumns.Count + 1];
for (int i = 0; i < allColumns.Count; i++)
{
setString[i] = new StringBuilder(allColumns[i]).Append("=:").Append(allColumns[i]).ToString();
paramField[i] = new ParamField() { ParamName = ":" + allColumns[i], FieldName = allColumns[i] };
}
string whereString = "";
whereString = new StringBuilder(activeRecord.PrimaryKey).Append("=:").Append(activeRecord.PrimaryKey).ToString();
paramField[allColumns.Count ] = new ParamField(){ ParamName=":" + activeRecord.PrimaryKey, FieldName = activeRecord.PrimaryKey};
return new SqlStruct()
{
SqlString = new StringBuilder("UPDATE ").Append(activeRecord.TableName).Append(" SET ").Append(string.Join(",", setString)).Append(" WHERE ").Append(string.Join(" AND ", whereString)).ToString(),
ParamFields = paramField
};
}
/// <summary>
/// 构建删除SQL语句
/// </summary>
/// <param name="activeRecord"></param>
/// <returns></returns>
private SqlStruct GenerateDeleteSql(dynamic activeRecord)
{
string whereString = "";
whereString = new StringBuilder(activeRecord.PrimaryKey).Append("=:").Append(activeRecord.PrimaryKey).ToString();
ParamField paramField = new ParamField() { ParamName = ":" + activeRecord.PrimaryKey, FieldName = activeRecord.PrimaryKey };
return new SqlStruct()
{
SqlString = new StringBuilder("DELETE FROM ").Append(activeRecord.TableName).Append(" WHERE ").Append(string.Join(" AND ", whereString)).ToString(),
ParamFields = new ParamField[] { paramField }
};
}
/// <summary>
/// 创建缓存Key
/// </summary>
/// <param name="tableName"></param>
/// <param name="sqlOperationType"></param>
/// <returns></returns>
private string GenerateKey(string tableName, SqlOperationType sqlOperationType)
{
return new StringBuilder(tableName).Append("__").Append(sqlOperationType.ToString()).ToString();
}
其中CRUD操作的动作，是通过一个枚举获取的：
public enum SqlOperationType { SimpleSelect, SelectByKey, Insert, Update, Delete}三、实现BaseActiveRecord的CRUD方法
以上就是动态生成SQL操作语句的工具类了，下面来实现BaseActiveRecord基类中的CRUD操作。
由于在从数据库获取到的数据需要赋值到相应的实体类属性，在基类中新建了一个索引方法，用于给实体类属性赋值：
/// <summary>
/// 设置或获取属性值
/// </summary>
/// <param name="column">字段名</param>
/// <returns></returns>
public dynamic this[string column]
{
get
{
return this.GetType().GetProperty(column.ToLower()).GetValue(this, null);
}
set
{
PropertyInfo info = this.GetType().GetProperty(column.ToLower());
Type type = info.PropertyType;
object propertyValue;
if (type.Equals(typeof(System.Int32)))
{
propertyValue = (System.Int32.Parse(value));
}
else if (type.Equals(typeof(System.DateTime)))
{
propertyValue = (System.DateTime.Parse(value));
}
else
{
propertyValue = value;
}
this.GetType().GetProperty(column.ToLower()).SetValue(this, propertyValue, null);
}
}
利用这个索引，到时我们就可以auth[“first_name”]=value的形式来赋值了。
基类中的静态方法New的作用是用来创建一个与数据库映射的实体类，其中泛型T是一个实体类型，在这个方法中，创建一个实体类并放入缓存中
public static dynamic New<T>()
{
Type type = typeof(T);
BaseActiveRecord obj = type.Assembly.CreateInstance(type.FullName) as BaseActiveRecord;
return obj.initiation();
}
下面是CRUD对应的方法实现，其中FindById和FindAll用的是静态方法，泛型T是实体类类型。
public void Save()
{
this[PrimaryKey] = GetPrimaryKeyValue();
SqlStruct sqlStruct = SqlGenerator.Instance().GenerateSql(this, SqlOperationType.Insert);
DataHelper helper = DataHelper.Instance();
foreach (ParamField paramField in sqlStruct.ParamFields)
{
helper.AddParameter(paramField.ParamName, this[paramField.FieldName]);
}
helper.ExecuteNonQuery(sqlStruct.SqlString);
}
public void Delete()
{
SqlStruct sqlStruct = SqlGenerator.Instance().GenerateSql(this, SqlOperationType.Delete);
DataHelper helper = DataHelper.Instance();
foreach (ParamField paramField in sqlStruct.ParamFields)
{
helper.AddParameter(paramField.ParamName, this[paramField.FieldName]);
}
helper.ExecuteNonQuery(sqlStruct.SqlString);
}
public void Update()
{
SqlStruct sqlStruct = SqlGenerator.Instance().GenerateSql(this, SqlOperationType.Update);
DataHelper helper = DataHelper.Instance();
foreach (ParamField paramField in sqlStruct.ParamFields)
{
helper.AddParameter(paramField.ParamName, this[paramField.FieldName]);
}
helper.ExecuteNonQuery(sqlStruct.SqlString);
}
public static dynamic FindById<T>(dynamic id)
{
Type type = typeof(T);
SqlStruct sqlStruct = SqlGenerator.Instance().GenerateSql(type, SqlOperationType.SelectByKey);
DataHelper helper = DataHelper.Instance();
foreach (ParamField paramField in sqlStruct.ParamFields)
{
helper.AddParameter(paramField.ParamName, id);
}
DataTable table = helper.GetDataSet(sqlStruct.SqlString).Tables[0];
EntityClassGenerator classGenerator = new EntityClassGenerator();
// 根据Type类型动态构建一个实体
dynamic activeRecord = classGenerator.GenerateEntity(type);
foreach (DataRow row in table.Rows)
{
// 给属性赋值
foreach (string column in activeRecord.Columns)
{
activeRecord[column] = row[column].ToString();
}
break;
}
return activeRecord;
}
public static dynamic FindAll<T>()
{
Type type = typeof(T);
SqlStruct sqlStruct = SqlGenerator.Instance().GenerateSql(type, SqlOperationType.SimpleSelect);
DataHelper helper = DataHelper.Instance();
DataTable table = helper.GetDataSet(sqlStruct.SqlString).Tables[0];
EntityClassGenerator classGenerator = new EntityClassGenerator();
// 根据Type类型动态构建一个实体
dynamic activeRecord;
List<BaseActiveRecord> activeRecords = new List<BaseActiveRecord>();
foreach (DataRow row in table.Rows)
{
// 给属性赋值
activeRecord = classGenerator.GenerateEntity(type);
foreach (string column in activeRecord.Columns)
{
activeRecord[column] = row[column].ToString();
}
activeRecords.Add(activeRecord);
}
return activeRecords;
}
四、测试CRUD操作
在main函数中，编写代码测试数据库的CRUD操作，下面是测试的代码：
static void Main(string[] args)
{
// 新增
dynamic auth = Author.New<Author>();
auth.first_name = "Han";
auth.last_name = "MeiMei";
auth.Save();
int keyValue = auth.id;
dynamic auth1 = Author.New<Author>();
auth1.first_name = "Li";
auth1.last_name = "Lei";
auth1.Save();
// 更新
auth.first_name = "Jim";
auth.last_name = "Green";
auth.Update();
// 根据ID获取
dynamic updateAuth = Author.FindById<Author>(keyValue);
// 获取所有
dynamic allAuth = Author.FindAll<Author>();
// 删除
auth1.Delete();
Console.ReadKey(true);
}
至此，利用C#的动态类型来实现与rails类似的元编程的示例已经能够完整的运行起来了。由于是示例，在以上的所有方法都并未采取异常处理的机制，如果有兴趣的朋友，可以根据代码进一步完善的。
