委托封裝Sql

• • 委托封裝Sql
  • 1.封裝一個泛型的Sql查詢方法，通過傳入不同Sql和委托
  • 2.查詢語句
  • 3.Model
  • 4.特性
  • 5.特性擴充
  • 6.Sql語句
  • 7.靜态連接配接字段
  • 8.前端使用

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 == )
                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>();

                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();
        }