委托封装Sql

委托封装Sql

1.封装一个泛型的Sql查询方法，通过传入不同Sql和委托

 private T ExcuteSql<T>(string sql, Func<SqlCommand, T> func)
        {
            using (SqlConnection conn = new SqlConnection(StaticConstant.SqlServerConnString))
            {
                using (SqlCommand command = new SqlCommand(sql, conn))
                {
                    conn.Open();
                    SqlTransaction sqlTransaction = conn.BeginTransaction();
                    try
                    {
                        command.Transaction = sqlTransaction;                       
                        T tResult = func.Invoke(command);
                        sqlTransaction.Commit();
                        return tResult;
                    }
                    catch (Exception ex)
                    {
                        sqlTransaction.Rollback();
                        throw;
                    }
                }
            }
        }

2.查询语句

   /// <summary>
        /// 查询单个实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id"></param>
        /// <returns></returns>
        public T Find<T>(int id) where T : BaseModel
        {
            Type type = typeof(T);

            string sql = $"{TSqlHelper<T>.FindSql}{id};";
            T t = null;// (T)Activator.CreateInstance(type);
            Func<SqlCommand, T> func = new Func<SqlCommand, T>(command =>
            {
                SqlDataReader reader = command.ExecuteReader();
                List<T> list = this.ReaderToList<T>(reader);
                T tResult = list.FirstOrDefault();
                return tResult;
            });
            t = this.ExcuteSql<T>(sql, func);

            return t;
        }

        /// <summary>
        /// 查询多个实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public List<T> FindAll<T>() where T : BaseModel
        {
            Type type = typeof(T);
            string sql = TSqlHelper<T>.FindAllSql;
            List<T> list = new List<T>();
            Func<SqlCommand, List<T>> func = command =>
            {
                SqlDataReader reader = command.ExecuteReader();
                List<T> listResult = this.ReaderToList<T>(reader);
                return listResult;
            };          
            list = this.ExcuteSql<List<T>>(sql, func);
            return list;
        }

        /// <summary>
        /// 更新数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        public void Update<T>(T t) where T : BaseModel
        {
            if (!t.Validate<T>())
            {
                throw new Exception("数据不正确");
            }

            Type type = typeof(T);
            var propArray = type.GetProperties().Where(p => !p.Name.Equals("Id"));
            string columnString = string.Join(",", propArray.Select(p => $"[{p.GetColumnName()}]=@{p.GetColumnName()}"));
            var parameters = propArray.Select(p => new SqlParameter($"@{p.GetColumnName()}", p.GetValue(t) ?? DBNull.Value)).ToArray();
            //必须参数化  否则引号？  或者值里面还有引号
            string sql = $"UPDATE [{type.Name}] SET {columnString} WHERE Id={t.Id}";

            Func<SqlCommand, int> func = command =>
            {
                command.Parameters.AddRange(parameters);
                int iResult = command.ExecuteNonQuery();
                return iResult;
            };
            int i = this.ExcuteSql<int>(sql, func);
            if (i == 0)
                throw new Exception("Update数据不存在");
        }

3.Model

 public class BaseModel
    {
        public int Id { get; set; }
    }

 public class Company : BaseModel
    {

        public string Name { get; set; }
        [Column("CreatorId")]
        public int CreateId { get; set; }
        public DateTime CreateTime { get; set; }
        /// <summary>
        /// 必须是可空类型，才能跟数据库对应
        /// </summary>
        public int? LastModifierId { get; set; }
        public DateTime? LastModifyTime { get; set; }
    }

4.特性

 [AttributeUsage(AttributeTargets.Property)]
    public class ColumnAttribute : Attribute
    {
        public ColumnAttribute(string name)
        {
            this._Name = name;
        }

        private string _Name = null;
        public string GetColumnName()
        {
            return this._Name;
        }
    }

  public abstract class AbstractValidateAttribute : Attribute
    {
        public abstract bool Validate(object value);
    }

5.特性扩展

/// <summary>
    /// 属性扩展
    /// </summary>
    public static class AttributeHelper
    {

        public static string GetColumnName(this PropertyInfo prop)
        {
            if (prop.IsDefined(typeof(ColumnAttribute), true))
            {
                ColumnAttribute attribute = (ColumnAttribute)prop.GetCustomAttribute(typeof(ColumnAttribute), true);
                return attribute.GetColumnName();
            }
            else
            {
                return prop.Name;
            }
        }


        public static bool Validate<T>(this T tModel) where T : BaseModel
        {
            Type type = tModel.GetType();
            foreach (var prop in type.GetProperties())
            {
                if (prop.IsDefined(typeof(AbstractValidateAttribute), true))
                {
                    object[] attributeArray = prop.GetCustomAttributes(typeof(AbstractValidateAttribute), true);
                    foreach (AbstractValidateAttribute attribute in attributeArray)
                    {
                        if (!attribute.Validate(prop.GetValue(tModel)))
                        {
                            return false;//表示终止
                            //throw new Exception($"{prop.Name}的值{prop.GetValue(tModel)}不对");
                        }
                    }
                }
            }
            return true;
        }
    }

6.Sql语句

 public class TSqlHelper<T> where T : BaseModel
    {
        static TSqlHelper()
        {
            Type type = typeof(T);
            string columnString = string.Join(",", type.GetProperties().Select(p => $"[{p.GetColumnName()}]"));
            FindSql = $"SELECT {columnString} FROM [{type.Name}] WHERE Id=";
            FindAllSql = $"SELECT {columnString} FROM [{type.Name}];";
        }

        public static string FindSql = null;
        public static string FindAllSql = null;
        //delete  update  insert 
    }

7.静态连接字段

 public class StaticConstant
    {
        /// <summary>
        /// sqlserver数据库连接
        /// </summary>
        public static string SqlServerConnString = ConfigurationManager.ConnectionStrings["SqlConn"].ConnectionString;


    }

8.前端使用

修改App.config文件，添加：

 <connectionStrings>
    <add name="SqlConn" connectionString="Data Source=localhost; Database=MyTest; User ID=sa; Password=123456; MultipleActiveResultSets=True" providerName="System.Data.SqlClient" />
    <!--ConfigurationManager.ConnectionStrings["SQL_DB_CONNECTION"]-->
  </connectionStrings>

 static void Main(string[] args)
        {

            try
            { 
                BaseDAl baseDAl = new BaseDAl();
                Company company = baseDAl.Find<Company>(1);

                List<Company> list = baseDAl.FindAll<Company>();

                company.Name += "1";
                baseDAl.Update(company);

                Console.Read();
            }
            catch (Exception ex)//UI层必须把异常catch住
            {
                Console.WriteLine(ex.Message);
            }
            Console.Read();
        }