动态生成SQL语句，对数据操作

这篇主要是实现了实体类的数据库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