这篇主要是实现了实体类的数据库CRUD的操作。
在目前的ORM映射框架中，对于操作数据库的最后一个步骤就是动态生成操作数据库的SQL语句，而这无非也就是利用实体属性中的自定义映射机制来实现的，或者就是直接把实体类的属性名对应表的字段名，在本示例中，就是采用的后者。
第一、为了能够存储动态生成的SQL语句，新建了一个SQL语句的结构类，用来存放SQL语句以及参数：
[Serializable] class SqlStruct { public string SqlString { get; set; } public ParamField[] ParamFields { get; set; } } [Serializable] public class ParamField { public string ParamName { get; set; } public string FieldName{ get; set; } }
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
第二、实现构建SQL操作
根据用户的CRUD操作，动态的构建一个SQL操作语句，并别存入缓存中，以便下次执行相同的操作时直接从缓存中获取，提高性能，在这里新建了一个构建SQL的类：
class SqlGenerator { private SqlGenerator() { } private static SqlGenerator singleton = new SqlGenerator(); public static SqlGenerator Instance() { return singleton; } 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; } 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 } } }; } private string GenerateSimpleSelectSql(dynamic type) { TableMapAttribute attr = Attribute.GetCustomAttribute(type, typeof(TableMapAttribute)) as TableMapAttribute; return new StringBuilder("SELECT * ").Append(" FROM ").Append(attr.TableName).ToString(); } 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 }; } 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 }; } 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 } }; } private string GenerateKey(string tableName, SqlOperationType sqlOperationType) { return new StringBuilder(tableName).Append("__").Append(sqlOperationType.ToString()).ToString(); }
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
- 91
- 92
- 93
- 94
- 95
- 96
- 97
- 98
- 99
- 100
- 101
- 102
- 103
- 104
- 105
- 106
- 107
- 108
- 109
- 110
- 111
- 112
- 113
- 114
- 115
- 116
- 117
- 118
- 119
- 120
- 121
- 122
- 123
- 124
- 125
- 126
- 127
- 128
- 129
- 130
- 131
- 132
- 133
- 134
- 135
- 136
- 137
- 138
- 139
- 140
- 141
- 142
- 143
- 144
- 145
- 146
- 147
- 148
- 149
- 150
- 151
- 152
- 153
- 154
- 155
- 156
- 157
- 158
- 159
- 160
- 161
- 162
- 163
- 164
- 165
- 166
- 167
- 168
- 169
- 170
- 171
- 172
- 173
- 174
- 175
- 176
- 177
- 178
- 179
- 180
- 181
- 182
- 183
- 184
- 185
其中CRUD操作的动作，是通过一个枚举获取的：
public enum SqlOperationType { SimpleSelect, SelectByKey, Insert, Update, Delete}
三、实现BaseActiveRecord的CRUD方法
以上就是动态生成SQL操作语句的工具类了，下面来实现BaseActiveRecord基类中的CRUD操作。
由于在从数据库获取到的数据需要赋值到相应的实体类属性，在基类中新建了一个索引方法，用于给实体类属性赋值：
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); } }
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
利用这个索引，到时我们就可以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 }
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
- 54
- 55
- 56
- 57
- 58
- 59
- 60
- 61
- 62
- 63
- 64
- 65
- 66
- 67
- 68
- 69
- 70
- 71
- 72
- 73
- 74
- 75
- 76
- 77
- 78
- 79
- 80
- 81
- 82
- 83
- 84
- 85
- 86
- 87
- 88
- 89
- 90
四、测试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) }
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
至此，利用C#的动态类型来实现与rails类似的元编程的示例已经能够完整的运行起来了。由于是示例，在以上的所有方法都并未采取异常处理的机制，如果有兴趣的朋友，可以根据代码进一步完善的。
