前言

領(lǐng)導(dǎo)不讓自己連數(shù)據(jù)庫，只能用他寫的、（我）沒有修改權(quán)限的DII連接（用的dapper），只能在sql語句組合方面玩點(diǎn)花樣了。

自己寫了SQL語句的拼接方法，使用的時(shí)候只需要傳入Model就可以生成對(duì)應(yīng)的SQL并執(zhí)行，目的是簡化重復(fù)寫的sql語句。使用后只需要給方法傳入一些參數(shù)，就可以實(shí)現(xiàn)對(duì)數(shù)據(jù)庫的訪問。

思路

根據(jù)傳入的Model獲取其特性中的tableName（詳細(xì)見上一篇文章），再將Model的屬性名稱作為SQL的執(zhí)行Field，與Where限定條件拼接SQL語句，最后將Modle與Sql一起傳入DII中的對(duì)應(yīng)方法執(zhí)行（實(shí)際上就是Dapper的各種方法）

實(shí)現(xiàn)

    public class DBHelper
    {

        IDatabase dbMysql = new DBMysql();

        //返回單個(gè)數(shù)據(jù)，condiction里面存放where 限定條件
        public string GetData(object condiction, string tableName, string fields)
        {
            string sql = "SELECT ";
            sql += fields;
            sql += $" FROM {tableName} ";
            sql = AddWhereCondiction(sql, condiction);
            return dbMysql.Query<string>(sql, condiction);
        }

        /*返回泛型List，condiction為傳入的Model，根據(jù)該Model獲取其特性中的tableName，
          根據(jù)Model的屬性名稱與Where限定條件拼接SQL語句。最后將Modle與Sql一起傳入DII
        （實(shí)際上就是Dapper的Query）*/
        public List<T> GetDataList<T>(object condiction)
        {
            TableAttribute tableAttribute = (TableAttribute)Attribute.GetCustomAttribute(typeof(T), typeof(TableAttribute));
            string tableName = tableAttribute.tableName;
            string sql = "SELECT ";
            sql = AddModelPrppertyTToField<T>(sql);
            sql += $" FROM {tableName} ";
            sql = AddWhereCondiction(sql, condiction);
            return dbMysql.QueryList<T>(sql, condiction).ToList();
        }

        public T GetData<T>(object condiction)
        {
            TableAttribute tableAttribute = (TableAttribute)Attribute.GetCustomAttribute(typeof(T), typeof(TableAttribute));
            string tableName = tableAttribute.tableName;
            string sql = "SELECT ";
            sql = AddModelPrppertyTToField<T>(sql);
            sql += $" FROM {tableName} ";
            sql = AddWhereCondiction(sql, condiction);
            return dbMysql.Query<T>(sql, condiction);
        }

        public bool InsertData<T>(T model)
        {
            TableAttribute tableAttribute = (TableAttribute)Attribute.GetCustomAttribute(typeof(T), typeof(TableAttribute));
            string tableName = tableAttribute.tableName;
            List<T> insertList = new List<T>() { model };
            string sql = $"INSERT INTO {tableName} (";
            sql = AddModelPrppertyTToField<T>(sql);
            sql += ")  VALUES(";
            sql = AddModelPrppertyTToField<T>(sql, "@");
            sql += ")";
            int insertNumber = dbMysql.Insert(insertList, sql);
            if (insertNumber == 1)
                return true;
            return false;
        }

        public bool UpdateData<T>(T condiction, string whereCondiction)
        {
            TableAttribute tableAttribute = (TableAttribute)Attribute.GetCustomAttribute(typeof(T), typeof(TableAttribute));
            string tableName = tableAttribute.tableName;
            string sql = $"UPDATE {tableName} SET ";
            foreach (var property in typeof(T).GetProperties())
            {
                sql += property.Name + " = @" + property.Name + ", ";
            }
            sql = sql.Substring(0, sql.Length - 2);
            List<string> whereCondictionList = new List<string>() { whereCondiction };
            sql = AddWhereCondiction(sql, whereCondictionList.ToList<string>());
            return dbMysql.Update(sql, condiction);
        }

        public bool UpdateData<T>(T condiction, List<string> whereCondictionList)
        {
            TableAttribute tableAttribute = (TableAttribute)Attribute.GetCustomAttribute(typeof(T), typeof(TableAttribute));
            string tableName = tableAttribute.tableName;
            string sql = $"UPDATE {tableName} SET ";
            foreach (var property in typeof(T).GetProperties())
            {
                sql += property.Name + " = @" + property.Name + ", ";
            }
            sql = sql.Substring(0, sql.Length - 2);
            sql = AddWhereCondiction(sql, whereCondictionList);
            return dbMysql.Update(sql, condiction);
        }
        
        /*將泛型Model中的屬性名稱添加到SQL的執(zhí)行Field中，dynamicData 為自定義變量添加，
        可以在根據(jù)數(shù)據(jù)庫不同在變量Field前增加想要的字符,比如Oracle變量需要添加':'，mysql需
        要添加'@'。*/
        public string AddModelPrppertyTToField<T>(string sql, string dynamicData = "")
        {
            if (dynamicData == "")
            {
                foreach (var property in typeof(T).GetProperties())
                {
                    sql += property.Name + ", ";
                }
            }
            else
            {
                foreach (var property in typeof(T).GetProperties())
                {
                    sql += dynamicData + property.Name + ", ";
                }
            }
            sql = sql.Substring(0, sql.Length - 2);
            return sql;
        }

        //給SQL添加Where限定條件，whereCondiction中為需要限定條件的字段
        public string AddWhereCondiction(string sql, List<string> whereCondiction)
        {
            sql += $" WHERE {whereCondiction[0]} = @{whereCondiction[0]} ";
            whereCondiction.RemoveAt(0);
            if (whereCondiction.Count > 0)
            {
                foreach (var whereLimit in whereCondiction)
                {
                    sql += $" AND {whereLimit} = @{whereLimit} ";
                }
            }
            return sql;
        }

        //給SQL添加Where限定條件，condiction的屬性名稱為限定條件的字段
        public string AddWhereCondiction<T>(string sql,T condiction)
        {
            var properties = condiction.GetType().GetProperties();
            sql += $" WHERE {properties[0].Name} = @{properties[0].Name} ";
            if (typeof(T).GetProperties().Count()>1)
            {
                for(int count = 1;count<properties.Count();count++)
                {
                    sql += $" AND {properties[count].Name} = @{properties[count].Name} ";
                }
            }
            return sql;
        }
    }```