Sql增删改查和优化

文件源码https://files.cnblogs.com/files/pilgrim/StudentManage.rar

1、通过正常编写sql语句和顺序写代码

　　正常编写sql语句是常用的方式，也是初学者最易掌握的（比如我）。直接使用sql进行拼装，但当设计多个实体对象时，就需要写多个对应的sql语句，需要使用代码码。这个方法里，我是用了反射和属性字段的方式，来获取和设置相关的对象值。

/// <summary>
/// 正常编写sql语句和顺序写代码
/// </summary>
public class SqlHelper //: IDBHelper
{
    /// <summary>
    /// Sql连接
    /// </summary>
    protected SqlConnection connection = new SqlConnection();

    public SqlHelper()
    {
        SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
        builder.DataSource = @"(LocalDB)\MSSQLLocalDB";
        builder.AttachDBFilename = @"C:\Users\Nigel\Desktop\StudentManage\StudentManage\Database1.mdf";
        builder.IntegratedSecurity = true;
        connection.ConnectionString = builder.ConnectionString;
    }

    /// <summary>
    /// 查询一个对象
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="id"></param>
    /// <returns></returns>
    public virtual T SelectOne<T>(int id) where T : class, IModel, new()
    {
        T t = new T();

        Type type = typeof(T);
        //没有应用特性的情况：此时，表名与类名相同、属性名和数据库字段名相同
        string sql = $"select * from [{type.Name}] where id = {id}";
        try
        {
            connection.Open();
            IDbCommand cmd = connection.CreateCommand();
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            if (reader.Read())
            {
                PropertyInfo[] infos = type.GetProperties();
                foreach (var info in infos)
                {
                    if (info.CanWrite)
                    {
                        //设置对应的值。注意：此处GUID和枚举的赋值有问题
                        info.SetValue(t, reader[info.Name] is DBNull ? null : reader[info.Name]);
                    }
                }
            }
        }
        finally
        {
            connection.Close();
        }
        return t;
    }   
}

2、使用参数化---防注入

　　正常编写sql语句会导致一个问题，那就是sql注入。意味着，其他编程人员可以未通过你的代码，而是在sql语句后面加上他的sql语句，使得数据库发生了注入操作。因此，参数化数据传入，是防止sql注入的有效方式。

public override T SelectOne<T>(int id)
{
    T t = new T();
    Type type = typeof(T);//获取类型
    string sql = $"select * from [{type.Name}] where [id]=@id";
    try
    {
        connection.Open();
        IDbCommand cmd = connection.CreateCommand();
        cmd.CommandText = sql;
        cmd.Parameters.Add(new SqlParameter("@id", id));
        cmd.CommandType = CommandType.Text;
        IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        if(reader.Read())
        {
            PropertyInfo[] properties = type.GetProperties();
            foreach (var prop in properties)
            {
                if(prop.CanWrite)
                {
                    prop.SetValue(t, reader[prop.Name] is DBNull ? null : reader[prop.Name]);
                }
            }
        }
    }
    finally
    {
        connection.Close();
    }

    return t;
}

3、使用特性标记、反射数据库对象和字段

　　前面所有的反射操作，均是基于类名和数据库表名一致、类的属性名和数据库表中的字段一直的情况。那如果不一样呢？又应该如何操作。以前学到了特性，所以这个地方也需要用到特性进行标记。

public override T SelectOne<T>(int id)
{
    T t = new T();
    Type type = typeof(T);
    //应用特性后: 表名与类名，或者属性名与数据库字段名可能不相同，并查询指定的列
    Type attrType = typeof(DbNameAttribute);
    IEnumerable<PropertyInfo> lstPrimaryKey = type.GetProperties().Where(p => p.IsDefined(attrType, true)).Where(p =>
     (p.GetCustomAttribute(attrType) as DbNameAttribute).IsPrimaryKey);
    string primaryKeyName = GetPropertyDBName(lstPrimaryKey.First());
    //使用特性  但不使用参数化
    {
        string sql = string.Format("select {0} from {1} where [{2}]={3}",
              string.Join(",", GetColumnNames(type)), GetTableName(type), primaryKeyName, id);
        try
        {
            connection.Open();

            IDbCommand cmd = connection.CreateCommand();
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            if (reader.Read())
            {
                PropertyInfo[] infos = type.GetProperties();
                foreach (var prop in infos)
                {
                    if (prop.CanWrite)
                    {
                        string dbName = GetPropertyDBName(prop);
                        //设置对应的值。注意：此处GUID和枚举的赋值有问题
                        prop.SetValue(t, reader[dbName] is DBNull ? null : reader[dbName]);
                    }
                }
            }
        }
        finally
        {
            connection.Close();
        }
    }
    //使用特性和参数化
    {
        string sql = string.Format("select {0} from {1} where [{2}]={3}",
              string.Join(",", GetColumnNames(type)), GetTableName(type), primaryKeyName, $"@{primaryKeyName}");
        try
        {
            connection.Open();
            IDbCommand cmd = connection.CreateCommand();
            cmd.CommandText = sql;
            cmd.Parameters.Add(new SqlParameter($"@{primaryKeyName}", id));
            cmd.CommandType = CommandType.Text;
            IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            if(reader.Read())
            {
                PropertyInfo[] infos = type.GetProperties();
                foreach (var prop in infos)
                {
                    if(prop.CanWrite)
                    {
                        string dbName = GetPropertyDBName(prop);
                        //设置对应的值。注意：此处GUID和枚举的赋值有问题
                        prop.SetValue(t, reader[dbName] is DBNull ? null : reader[dbName]);
                    }
                }
            }
        }
        finally
        {
            connection.Close();
        }
    }

    return t;
}

　　反射特性的代码：

/// <summary>
/// 获取表名称
/// </summary>
/// <param name="type">类型</param>
/// <returns>如果有标记就返回标记，如果没有标记则返回类名</returns>
protected string GetTableName(Type type)
{
    Attribute attribute = type.GetCustomAttribute(typeof(DbNameAttribute), true);
    if (attribute != null && attribute is DbNameAttribute)
    {
        string name = (attribute as DbNameAttribute).Name;
        if (string.IsNullOrEmpty(name) == false)
        {
            return name;
        }
    }
    return type.Name;
}

/// <summary>
/// 获实体对应数据库中的列名称
/// </summary>
/// <param name="type">获取的类型</param>
/// <returns>所有被标记的名称和没有被标记的字段名</returns>
protected string[] GetColumnNames(Type type)
{
    PropertyInfo[] propertyInfos = type.GetProperties();
    List<string> names = new List<string>();
    foreach (PropertyInfo property in propertyInfos)
    {
        if (property.CanWrite)//只读取能写入数据的
        {
            names.Add(GetPropertyDBName(property));
        }
    }
    return names.ToArray();
}

/// <summary>
/// 获取属性的数据库名称
/// </summary>
/// <param name="property">属性</param>
/// <returns>数据库名称</returns>
protected string GetPropertyDBName(PropertyInfo property)
{
    string name = string.Empty;
    Attribute attribute = property.GetCustomAttribute(typeof(DbNameAttribute), true);
    if (attribute != null && attribute is DbNameAttribute)
    {
        name = (attribute as DbNameAttribute).Name;
    }
    if (string.IsNullOrEmpty(name))
    {
        return property.Name;
    }
    else
    {
        return name;
    }
}

4、关于其他方法的编写

①直接使用sql语句进行操作

/// <summary>
/// 正常编写sql语句和顺序写代码
/// </summary>
public class SqlHelper //: IDBHelper
{
    /// <summary>
    /// Sql连接
    /// </summary>
    protected SqlConnection connection = new SqlConnection();

    public SqlHelper()
    {
        SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
        builder.DataSource = @"(LocalDB)\MSSQLLocalDB";
        builder.AttachDBFilename = @"C:\Users\Nigel\Desktop\StudentManage\StudentManage\Database1.mdf";
        builder.IntegratedSecurity = true;
        connection.ConnectionString = builder.ConnectionString;
    }

    /// <summary>
    /// 查询一个对象
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="id"></param>
    /// <returns></returns>
    public virtual T SelectOne<T>(int id) where T : class, IModel, new()
    {
        T t = new T();

        Type type = typeof(T);
        //没有应用特性的情况：此时，表名与类名相同、属性名和数据库字段名相同
        string sql = $"select * from [{type.Name}] where id = {id}";
        try
        {
            connection.Open();
            IDbCommand cmd = connection.CreateCommand();
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            if (reader.Read())
            {
                PropertyInfo[] infos = type.GetProperties();
                foreach (var info in infos)
                {
                    if (info.CanWrite)
                    {
                        //设置对应的值。注意：此处GUID和枚举的赋值有问题
                        info.SetValue(t, reader[info.Name] is DBNull ? null : reader[info.Name]);
                    }
                }
            }
        }
        finally
        {
            connection.Close();
        }
        return t;
    }

    /// <summary>
    /// 查询所有对象
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <returns></returns>
    public virtual List<T> SelectAll<T>() where T : class, IModel, new()
    {
        List<T> lstResult = new List<T>();

        Type type = typeof(T);
        //没有应用特性的情况：此时，表名与类名相同、属性名和数据库字段名相同
        string sql = $"select * from [{type.Name}]";
        try
        {
            connection.Open();

            IDbCommand cmd = connection.CreateCommand();
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            PropertyInfo[] infos = type.GetProperties();
            while (reader.Read())
            {
                T t = new T();
                foreach (var info in infos)
                {
                    if (info.CanWrite)
                    {
                        //设置对应的值。注意：此处GUID和枚举的赋值有问题
                        info.SetValue(t, reader[info.Name] is DBNull ? null : reader[info.Name]);
                    }
                }
                lstResult.Add(t);
            }
        }
        finally
        {
            connection.Close();
        }

        return lstResult;
    }


    /// <summary>
    /// 插入一个对象 
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="t"></param>
    /// <returns></returns>
    public virtual int Insert<T>(T t)
    {
        Type type = typeof(T);
        //获取写入的键值
        Dictionary<string, object> keyValues = new Dictionary<string, object>();
        PropertyInfo[] properties = type.GetProperties();
        for (int i = 0; i < properties.Length; i++)
        {
            if (properties[i].CanWrite)
            {
                keyValues.Add(properties[i].Name, properties[i].GetValue(t));
            }
        }
        string sql = $"insert [{type.Name}]({string.Join(",", keyValues.Keys)}) values('{string.Join("','", keyValues.Values)}')";
        int result = 0;
        //SqlTransaction transaction;
        try
        {
            connection.Open();
            //transaction = connection.BeginTransaction();//事务处理
            IDbCommand cmd = connection.CreateCommand();
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            result= cmd.ExecuteNonQuery();
            //transaction.Commit();
        }
        catch(Exception ex)
        {
            //transaction.Rollback();
            throw ex;
        }
        finally
        {
            connection.Close();
        }
        return result;
    }

    /// <summary>
    /// 删除数据
    /// </summary>
    /// <typeparam name="T">需要删除的对象</typeparam>
    /// <param name="t"></param>
    /// <returns></returns>
    public virtual int Delete<T>(T t)
    {
        return 0;
    }
    /// <summary>
    /// 删除数据
    /// </summary>
    /// <param name="id">需要删除的id</param>
    /// <returns>受影响行数</returns>
    public virtual int Delete<T>(int id)
    {
        return 0;
    }

    /// <summary>
    /// 删除数据
    /// </summary>
    /// <param name="id">需要删除的id</param>
    /// <param name="t">更新数据</param>
    /// <returns>受影响行数</returns>
    public virtual int Update<T>(T t)
    {
        return 0;
    }
}

②使用参数化---防注入

/// <summary>
/// 使用参数化的方式进行--防止注入
/// </summary>
public class SqlHelperExtand1: SqlHelper
{
    public override T SelectOne<T>(int id)
    {
        T t = new T();
        Type type = typeof(T);//获取类型
        string sql = $"select * from [{type.Name}] where [id]=@id";
        try
        {
            connection.Open();
            IDbCommand cmd = connection.CreateCommand();
            cmd.CommandText = sql;
            cmd.Parameters.Add(new SqlParameter("@id", id));
            cmd.CommandType = CommandType.Text;
            IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            if(reader.Read())
            {
                PropertyInfo[] properties = type.GetProperties();
                foreach (var prop in properties)
                {
                    if(prop.CanWrite)
                    {
                        prop.SetValue(t, reader[prop.Name] is DBNull ? null : reader[prop.Name]);
                    }
                }
            }
        }
        finally
        {
            connection.Close();
        }

        return t;
    }


    public override List<T> SelectAll<T>()
    {
        return base.SelectAll<T>();
    }

    public override int Insert<T>(T t)
    {
        Type type = t.GetType();
        //获取写入的键值
        Dictionary<string, SqlParameter> keyValues = new Dictionary<string, SqlParameter>();
        PropertyInfo[] properties = type.GetProperties();
        for (int i = 0; i < properties.Length; i++)
        {
            if (properties[i].CanWrite)
            {
                keyValues.Add(properties[i].Name, new SqlParameter($"@{properties[i].Name}", properties[i].GetValue(t)));
            }
        }
        string sql = $"insert [{type.Name}]({string.Join(",", keyValues.Keys)}) values(@{string.Join(",@", keyValues.Keys)})";
        int result = 0; 
        SqlTransaction transaction = null;
        try
        {
            connection.Open();
            //transaction = connection.BeginTransaction();//开启事务
            SqlCommand cmd = connection.CreateCommand();
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.AddRange(keyValues.Values.ToArray());
            result = cmd.ExecuteNonQuery();
            //transaction.Commit();
        }
        catch(Exception ex)
        {
            //transaction.Rollback();
            throw ex;
        }
        finally
        {
            connection.Close();
        }

        return result ;
    }
}

③使用特性标记、反射数据库对象和字段

/// <summary>
/// 当类名与表名，或者属性名与列名不相同时，使用特性进行标记
/// </summary>
public class SqlHelperExtand2 : /*SqlHelper*/ SqlHelperExtand1
{

    public override T SelectOne<T>(int id)
    {
        T t = new T();
        Type type = typeof(T);
        //应用特性后: 表名与类名，或者属性名与数据库字段名可能不相同，并查询指定的列
        Type attrType = typeof(DbNameAttribute);
        IEnumerable<PropertyInfo> lstPrimaryKey = type.GetProperties().Where(p => p.IsDefined(attrType, true)).Where(p =>
         (p.GetCustomAttribute(attrType) as DbNameAttribute).IsPrimaryKey);
        string primaryKeyName = GetPropertyDBName(lstPrimaryKey.First());
        //使用特性  但不使用参数化
        {
            string sql = string.Format("select {0} from {1} where [{2}]={3}",
                  string.Join(",", GetColumnNames(type)), GetTableName(type), primaryKeyName, id);
            try
            {
                connection.Open();

                IDbCommand cmd = connection.CreateCommand();
                cmd.CommandText = sql;
                cmd.CommandType = CommandType.Text;
                IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                if (reader.Read())
                {
                    PropertyInfo[] infos = type.GetProperties();
                    foreach (var prop in infos)
                    {
                        if (prop.CanWrite)
                        {
                            string dbName = GetPropertyDBName(prop);
                            //设置对应的值。注意：此处GUID和枚举的赋值有问题
                            prop.SetValue(t, reader[dbName] is DBNull ? null : reader[dbName]);
                        }
                    }
                }
            }
            finally
            {
                connection.Close();
            }
        }
        //使用特性和参数化
        {
            string sql = string.Format("select {0} from {1} where [{2}]={3}",
                  string.Join(",", GetColumnNames(type)), GetTableName(type), primaryKeyName, $"@{primaryKeyName}");
            try
            {
                connection.Open();
                IDbCommand cmd = connection.CreateCommand();
                cmd.CommandText = sql;
                cmd.Parameters.Add(new SqlParameter($"@{primaryKeyName}", id));
                cmd.CommandType = CommandType.Text;
                IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                if(reader.Read())
                {
                    PropertyInfo[] infos = type.GetProperties();
                    foreach (var prop in infos)
                    {
                        if(prop.CanWrite)
                        {
                            string dbName = GetPropertyDBName(prop);
                            //设置对应的值。注意：此处GUID和枚举的赋值有问题
                            prop.SetValue(t, reader[dbName] is DBNull ? null : reader[dbName]);
                        }
                    }
                }
            }
            finally
            {
                connection.Close();
            }
        }

        return t;
    }


    public override List<T> SelectAll<T>() 
    {
        List<T> lstResult = new List<T>();

        Type type = typeof(T);
        //应用特性后: 表名与类名，或者属性名与数据库字段名可能不相同，并查询指定的列
        string sql = string.Format("select {0} from {1}",
            string.Join(",", GetColumnNames(type)), GetTableName(type));
        try
        {
            connection.Open();

            IDbCommand cmd = connection.CreateCommand();
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            PropertyInfo[] infos = type.GetProperties();
            while (reader.Read())
            {
                T t = new T();
                foreach (var info in infos)
                {
                    if (info.CanWrite)
                    {
                        string name = GetPropertyDBName(info);
                        //设置对应的值。注意：此处GUID和枚举的赋值有问题
                        info.SetValue(t, reader[name] is DBNull ? null : reader[name]);
                    }
                }
                lstResult.Add(t);
            }
        }
        finally
        {
            connection.Close();
        }

        return lstResult;
    }

    #region 参数化查询（应用特性）  --- 防注入


    /// <summary>
    /// 查找某一个特定的对象
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <returns></returns>
    public virtual T SelectObject<T>(T t)
    {
        var KeyInfo = KeyWhere( t);
        Type type = t.GetType();
        string sql = $@"select * from [{GetTableName(type)}] {KeyInfo.Item1}";

        using (SqlCommand cmd = connection.CreateCommand())
        {
            //connection.BeginTransaction();//事务处理
            cmd.CommandText = sql;
            cmd.Parameters.AddRange(KeyInfo.Item2.ToArray());
            connection.Open();
            SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            if (reader.Read())
            {
                foreach (PropertyInfo prop in type.GetProperties())
                {
                    if (prop.CanWrite)
                    {
                        string dbName = GetPropertyDBName(prop);
                        prop.SetValue(t, reader[dbName] is DBNull ? null : reader[dbName]);//这儿还可以映射，特性
                    }
                }
            }
            connection.Close();
        }
        return t;
    }

    /// <summary>
    /// 返回主键条件查询字符串
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="type"></param>
    /// <param name="t"></param>
    /// <returns></returns>
    private Tuple<string, IEnumerable<SqlParameter>> KeyWhere<T>( T t)
    {
        List<string> lstWhere = new List<string>();
        List<SqlParameter> lstParameter = new List<SqlParameter>();
        Type attrType = typeof(DbNameAttribute);
        IEnumerable<PropertyInfo> keysInfo =t.GetType().GetProperties().Where(prop => prop.IsDefined(attrType, true)).Where(p =>
        {
            return (p.GetCustomAttribute(attrType) as DbNameAttribute).IsPrimaryKey;
        });
        foreach (PropertyInfo property in keysInfo)
        {
            DbNameAttribute key = property.GetCustomAttribute(attrType) as DbNameAttribute;
            if (string.IsNullOrEmpty(key.Name))
            {
                lstWhere.Add($@"[{property.Name}] = @{property.Name}");
                lstParameter.Add(new SqlParameter($@"@{property.Name}", property.GetValue(t)));
            }
            else
            {
                lstWhere.Add($@"[{key.Name}] = @{key.Name}");//这儿可以采用传参的形式
                lstParameter.Add(new SqlParameter($@"@{key.Name}", property.GetValue(t)));
            }
            //if (key.IsDefinedKey)
            //{
            //    lstWhere.Add($@"[{key.Name}] = @{key.Name}");//这儿可以采用传参的形式
            //    lstParameter.Add(new SqlParameter($@"@{key.Name}", property.GetValue(t)));
            //}
            //else
            //{
            //    lstWhere.Add($@"[{property.Name}] = @{property.Name}");
            //    lstParameter.Add(new SqlParameter($@"@{property.Name}", property.GetValue(t)));
            //}
        }
        //if(lstWhere.Count>0)
        //{
        //    string strWhere= $@"where {string.Join(" and ", lstWhere)}";
        //    return new Tuple<string, IEnumerable<SqlParameter>>(strWhere, lstParameter);
        //}
        string strWhere = $@"where {string.Join(" and ", lstWhere)}";
        return new Tuple<string, IEnumerable<SqlParameter>>(strWhere, lstParameter);
    }

    #endregion


    public override int Insert<T>(T t)
    {
        Type type = t.GetType();
        Dictionary<string, SqlParameter> keyValues = new Dictionary<string, SqlParameter>();
        PropertyInfo[] properties = type.GetProperties();
        foreach (PropertyInfo prop in properties)
        {
            if(prop.CanWrite)
            {
                string dbName = GetPropertyDBName(prop);
                SqlParameter parameter = new SqlParameter($"@{dbName}", prop.GetValue(t));
                keyValues.Add(dbName, parameter);
            }
        }

        string sql = $"insert [{GetTableName(type)}]({string.Join(",", keyValues.Keys)}) values(@{string.Join(",@", keyValues.Keys)})";
        //SqlTransaction transaction = null;
        int result = 0;
        try
        {
            connection.Open();
            //transaction = connection.BeginTransaction();
            SqlCommand cmd = connection.CreateCommand();
            cmd.CommandText = sql;
            cmd.Parameters.AddRange(keyValues.Values.ToArray());
            // transaction.Commit();
            result = cmd.ExecuteNonQuery();
        }
        catch(Exception ex)
        {
           // transaction.Rollback();
            throw ex;
        }
        finally
        {
            connection.Close();
        }

        return result;
    }


    #region 获取特性的标记

    /// <summary>
    /// 获取表名称
    /// </summary>
    /// <param name="type">类型</param>
    /// <returns>如果有标记就返回标记，如果没有标记则返回类名</returns>
    protected string GetTableName(Type type)
    {
        Attribute attribute = type.GetCustomAttribute(typeof(DbNameAttribute), true);
        if (attribute != null && attribute is DbNameAttribute)
        {
            string name = (attribute as DbNameAttribute).Name;
            if (string.IsNullOrEmpty(name) == false)
            {
                return name;
            }
        }
        return type.Name;
    }

    /// <summary>
    /// 获实体对应数据库中的列名称
    /// </summary>
    /// <param name="type">获取的类型</param>
    /// <returns>所有被标记的名称和没有被标记的字段名</returns>
    protected string[] GetColumnNames(Type type)
    {
        PropertyInfo[] propertyInfos = type.GetProperties();
        List<string> names = new List<string>();
        foreach (PropertyInfo property in propertyInfos)
        {
            if (property.CanWrite)//只读取能写入数据的
            {
                names.Add(GetPropertyDBName(property));
            }
        }
        return names.ToArray();
    }

    /// <summary>
    /// 获取属性的数据库名称
    /// </summary>
    /// <param name="property">属性</param>
    /// <returns>数据库名称</returns>
    protected string GetPropertyDBName(PropertyInfo property)
    {
        string name = string.Empty;
        Attribute attribute = property.GetCustomAttribute(typeof(DbNameAttribute), true);
        if (attribute != null && attribute is DbNameAttribute)
        {
            name = (attribute as DbNameAttribute).Name;
        }
        if (string.IsNullOrEmpty(name))
        {
            return property.Name;
        }
        else
        {
            return name;
        }
    }
    #endregion
}

5、代码优化

　　阅读前面的代码，发现有一部分能够提取出来，因此进行相关优化。部分代码，在第3点拷贝

/// <summary>
/// 优化程序代码，提取公用部分
/// </summary>
public class SqlHelperExtand3 : SqlHelperExtand2
{
    public override T SelectOne<T>(int id)
    {
        T t = new T();
        Type type = typeof(T);
        //应用特性后: 表名与类名，或者属性名与数据库字段名可能不相同，并查询指定的列
        Type attrType = typeof(DbNameAttribute);
        IEnumerable<PropertyInfo> lstPrimaryKey = type.GetProperties().Where(p => p.IsDefined(attrType, true)).Where(p =>
         (p.GetCustomAttribute(attrType) as DbNameAttribute).IsPrimaryKey);
        string primaryKeyName = GetPropertyDBName(lstPrimaryKey.First());
        //使用特性和参数化
        string sql = string.Format("select {0} from {1} where [{2}]={3}",
              string.Join(",", GetColumnNames(type)), GetTableName(type), primaryKeyName, $"@{primaryKeyName}");
        List<SqlParameter> parameters = new List<SqlParameter>()
        {
            new SqlParameter($"@{primaryKeyName}", id),
        };
        Action act = () =>
        {
            Func<IDbCommand, T> func = cmd =>
            {
                IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                if (reader.Read())
                {
                    PropertyInfo[] infos = type.GetProperties();
                    foreach (var prop in infos)
                    {
                        if (prop.CanWrite)
                        {
                            string dbName = GetPropertyDBName(prop);
                            //设置对应的值。注意：此处GUID和枚举的赋值有问题
                            prop.SetValue(t, reader[dbName] is DBNull ? null : reader[dbName]);
                        }
                    }
                }
                return t;
            };
            t = ExecuteCmd(sql, parameters, func);
        };
        act.Invoke();           


        return t;
    }

    public override List<T> SelectAll<T>()
    {
        List<T> lstResult = new List<T>();

        Type type = typeof(T);
        //应用特性后: 表名与类名，或者属性名与数据库字段名可能不相同，并查询指定的列
        string sql = string.Format("select {0} from {1}",
            string.Join(",", GetColumnNames(type)), GetTableName(type));
        Action act = () =>
        {
            Func<IDbCommand, List<T>> func = cmd =>
            {
                List<T> lst = new List<T>();
                IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                PropertyInfo[] infos = type.GetProperties();
                while (reader.Read())
                {
                    T t = new T();
                    foreach (PropertyInfo prop in infos)
                    {
                        if (prop.CanWrite)
                        {
                            string name = GetPropertyDBName(prop);
                            prop.SetValue(t, reader[name] is DBNull ? null : reader[name]);

                        }
                    }
                    lst.Add(t);
                }
                return lst;
            };
            lstResult = ExecuteCmd(sql, null, func);
        };
        act.Invoke();
        return lstResult;
    }

    #region 
    /// <summary>
    /// 查找某一个特定的对象
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <returns></returns>
    public override T SelectObject<T>(T t)
    {
        Type type = t.GetType();
        var KeyInfo = KeyWhere(t);
        string sql = $@"select * from [{GetTableName(type)}] {KeyInfo.Item1}";

        Action action = () =>
        {
            Func<IDbCommand, T> func = cmd =>
            {
                IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                if (reader.Read())
                {
                    foreach (PropertyInfo prop in type.GetProperties())
                    {
                        if (prop.CanWrite)
                        {
                            string dbName = GetPropertyDBName(prop);
                            prop.SetValue(t, reader[dbName] is DBNull ? null : reader[dbName]);//这儿还可以映射，特性
                        }
                    }
                }
                return t;
            };
            t = ExecuteCmd<T>(sql, KeyInfo.Item2, func);
        };
        action.Invoke();
        return t;
    }

    /// <summary>
    /// 返回主键条件查询字符串
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="type"></param>
    /// <param name="t"></param>
    /// <returns></returns>
    private Tuple<string, IEnumerable<SqlParameter>> KeyWhere<T>(T t)
    {
        List<string> lstWhere = new List<string>();
        List<SqlParameter> lstParameter = new List<SqlParameter>();
        Type attrType = typeof(DbNameAttribute);
        IEnumerable<PropertyInfo> keysInfo = t.GetType().GetProperties().
            Where(prop => prop.IsDefined(attrType, true)).Where(p =>
        {
            return (p.GetCustomAttribute(attrType) as DbNameAttribute).IsPrimaryKey;
        });
        foreach (PropertyInfo property in keysInfo)
        {
            DbNameAttribute key = property.GetCustomAttribute(attrType) as DbNameAttribute;
            if (string.IsNullOrEmpty(key.Name))
            {
                lstWhere.Add($@"[{property.Name}] = @{property.Name}");
                lstParameter.Add(new SqlParameter($@"@{property.Name}", property.GetValue(t)));
            }
            else
            {
                lstWhere.Add($@"[{key.Name}] = @{key.Name}");//这儿可以采用传参的形式
                lstParameter.Add(new SqlParameter($@"@{key.Name}", property.GetValue(t)));
            }
            //if (key.IsDefinedKey)
            //{
            //    lstWhere.Add($@"[{key.Name}] = @{key.Name}");//这儿可以采用传参的形式
            //    lstParameter.Add(new SqlParameter($@"@{key.Name}", property.GetValue(t)));
            //}
            //else
            //{
            //    lstWhere.Add($@"[{property.Name}] = @{property.Name}");
            //    lstParameter.Add(new SqlParameter($@"@{property.Name}", property.GetValue(t)));
            //}
        }
        //if(lstWhere.Count>0)
        //{
        //    string strWhere= $@"where {string.Join(" and ", lstWhere)}";
        //    return new Tuple<string, IEnumerable<SqlParameter>>(strWhere, lstParameter);
        //}
        string strWhere = $@"where {string.Join(" and ", lstWhere)}";
        return new Tuple<string, IEnumerable<SqlParameter>>(strWhere, lstParameter);
    }

    #endregion


    public override int Insert<T>(T t)
    {
        Type type = t.GetType();
        Dictionary<string, SqlParameter> keyValues = new Dictionary<string, SqlParameter>();
        PropertyInfo[] properties = type.GetProperties();
        foreach (PropertyInfo prop in properties)
        {
            if (prop.CanWrite)
            {
                string dbName = GetPropertyDBName(prop);
                SqlParameter parameter = new SqlParameter($"@{dbName}", prop.GetValue(t));
                keyValues.Add(dbName, parameter);
            }
        }

        string sql = $"insert [{GetTableName(type)}]({string.Join(",", keyValues.Keys)}) values(@{string.Join(",@", keyValues.Keys)})";
        //SqlTransaction transaction = null;
        int result = 0;
        Action act = () =>
        {
            result = ExecuteCmd(sql, keyValues.Values.ToArray(), cmd=> cmd.ExecuteNonQuery());
        };
        act.Invoke();
        return result;
    }


    public override int Delete<T>(int id)
    {
        string sql = "delect…… where [ID]=" + id;//通过反射等方法构造删除语句
        int result = ExecuteCmd(sql, null, cmd => cmd.ExecuteNonQuery());
        return result;
    }

    public override int Update<T>(T t)
    {
        string sql = "update…… ";//通过反射等方法构造更新语句
        int result = ExecuteCmd(sql,null, cmd => cmd.ExecuteNonQuery());
        return result;
    }


    /// <summary>
    /// 执行语句
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="sql"></param>
    /// <param name="lstParameter"></param>
    /// <param name="func"></param>
    /// <returns></returns>

    private T ExecuteCmd<T>(string sql, IEnumerable<SqlParameter> lstParameter, Func<IDbCommand, T> func)
    {
        //SqlTransaction transaction = null;
        try
        {
            using (SqlCommand cmd = connection.CreateCommand())
            {
                connection.Open();
                //transaction=connection.BeginTransaction();//事务处理
                cmd.CommandText = sql;
                if (lstParameter != null)
                {
                    cmd.Parameters.AddRange(lstParameter.ToArray());
                }
                T t = func.Invoke(cmd);
                return t;
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex);
            throw ex;
        }
        finally
        {
            connection.Close();
        }
    }

}