这篇主要是实现了实体类的数据库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类似的元编程的示例已经能够完整的运行起来了。由于是示例,在以上的所有方法都并未采取异常处理的机制,如果有兴趣的朋友,可以根据代码进一步完善的。 http://blog.csdn.net/hehuicong/article/details/40378217