SqlHelper

撸了个什么垃圾代码~_~!

public class DBContext
    {
        private IDbConnection _dbConnection;
        private string _connectionString;
        public DBContext(IConfiguration Configuration)
        {
            //读取配置文件，数据库连接字符串
            _connectionString = Configuration["ConnectionString"];
        }
        private IDbConnection DbConnection
        {
            get
            {
                if (_dbConnection == null)
                {
                    _dbConnection = new SqlConnection(_connectionString);
                }
                return _dbConnection;
            }
        }
        /// <summary>
        /// 打开连接
        /// </summary>
        private void ConnOpen()
        {
            if (DbConnection.State == ConnectionState.Closed)
            {
                DbConnection.Open();
            }
        }
        /// <summary>
        /// 关闭连接
        /// </summary>
        private void ConnClose()
        {
            // 事务未提交时连接不应该被关闭
            if (Committed && DbConnection.State == ConnectionState.Open)
            {
                DbConnection.Close();
            }
        }
        private IDbTransaction DbTransaction { get; set; }
        /// <summary>
        /// 事务是否已被提交
        /// </summary>
        public bool Committed { get; private set; } = true;
        /// <summary>
        /// 开启事务
        /// </summary>
        public void BeginTransaction()
        {
            Committed = false;
            ConnOpen();
            DbTransaction = DbConnection.BeginTransaction();
        }
        /// <summary>
        /// 事务提交
        /// </summary>
        public void CommitTransaction()
        {
            if (!Committed)
            {
                DbTransaction?.Commit();
                Committed = true;

                DbTransaction?.Dispose();
                ConnClose();
            }
        }

        /// <summary>
        /// 事务回滚
        /// </summary>
        public void RollBackTransaction()
        {
            if (!Committed)
            {
                DbTransaction?.Rollback();
                Committed = true;

                DbTransaction?.Dispose();
                ConnClose();
            }
        }

        private void SetCommand(IDbCommand cmd, string sql, object param)
        {
            cmd.CommandText = sql;
            cmd.Connection = DbConnection;
            cmd.Transaction = DbTransaction;
            cmd.CommandType = CommandType.Text;
            if (param != null)
            {
                Type paramType = param.GetType();
                var props = paramType.GetProperties(System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance);

                foreach (var prop in props)
                {
                    object obj = prop.GetValue(param);
                    SqlParameter p = new SqlParameter($"@{prop.Name}", obj ?? DBNull.Value);
                    cmd.Parameters.Add(p);
                }
            }
        }
        // <summary>
        /// 执行Sql 返回受影响的行数
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">参数</param>
        /// <returns></returns>
        public int Execute(string sql, object param = null)
        {
            try
            {
                ConnOpen();
                using (IDbCommand cmd = new SqlCommand())
                {
                    SetCommand(cmd, sql, param);
                    return cmd.ExecuteNonQuery();
                }
            }
            finally
            {
                ConnClose();
            }
        }
        public T ExecuteScalar<T>(string sql, object param = null)
        {
            try
            {
                ConnOpen();
                using (IDbCommand cmd = new SqlCommand())
                {
                    SetCommand(cmd, sql, param);
                    object obj = cmd.ExecuteScalar();
                    return Parse<T>(obj);
                }
            }
            finally
            {
                ConnClose();
            }
        }
        public IEnumerable<T> Query<T>(string sql, object param = null)
        {
            try
            {
                ConnOpen();
                using (IDbCommand cmd = new SqlCommand())
                {
                    SetCommand(cmd, sql, param);
                    IDataReader reader = cmd.ExecuteReader();
                    List<T> list = new List<T>();

                    while (reader.Read())
                    {
                        list.Add(Parse<T>(reader));
                    }
                    return list;
                }
            }
            finally
            {
                ConnClose();
            }
        }
        public T QueryFirst<T>(string sql, object param = null)
        {
            try
            {
                ConnOpen();
                using (IDbCommand cmd = new SqlCommand())
                {
                    SetCommand(cmd, sql, param);
                    IDataReader reader = cmd.ExecuteReader();
                    if (reader.Read())
                    {
                        return Parse<T>(reader);
                    }
                    return default(T);
                }
            }
            finally
            {
                ConnClose();
            }
        }

        private static T Parse<T>(IDataReader reader)
        {
            Type type = typeof(T);
            var typeCode = Type.GetTypeCode(type);
            if (typeCode == TypeCode.String || typeCode == TypeCode.Decimal || typeCode == TypeCode.DateTime || type.IsPrimitive || type.IsEnum || Nullable.GetUnderlyingType(type) != null)
            {
                return Parse<T>(reader[0]);
            }
            T obj = Activator.CreateInstance<T>();
            for (int i = 0; i < reader.FieldCount; i++)
            {
                PropertyInfo prop = type.GetProperty(reader.GetName(i), BindingFlags.Public | BindingFlags.Instance | BindingFlags.IgnoreCase);
                if (prop != null)
                {
                    object value = reader[prop.Name];
                    if (value == null || value is DBNull) continue;
                    value = Parse(prop.PropertyType, value);
                    prop.SetValue(obj, value);
                }
            }
            return obj;
        }
        private static T Parse<T>(object value)
        {
            if (value == null || value is DBNull) return default(T);
            if (value is T) return (T)value;
            var type = typeof(T);
            value = Parse(type, value);
            return (T)value;
        }
        private static object Parse(Type type, object value)
        {
            type = Nullable.GetUnderlyingType(type) ?? type;
            if (type.IsEnum)
            {
                if (value is float || value is double || value is decimal)
                {
                    value = Convert.ChangeType(value, Enum.GetUnderlyingType(type), CultureInfo.InvariantCulture);
                }
                return Enum.ToObject(type, value);
            }
            return Convert.ChangeType(value, type, CultureInfo.InvariantCulture);
        }
    }

 调用方式

//依赖注入
services.AddScoped<DBContext>();

//控制器
private DBContext _db;
public ValuesController(DBContext db)
{
      _db = db;
}
11         public IActionResult Get()
        {
            try
            {
                _db.BeginTransaction();
                int res = _db.Execute("UPDATE [dbo].[Users] SET nickname='transaction update' WHERE Id=@Id", new { Id = 999 });
                //throw new Exception("事务回滚");
                _db.CommitTransaction();
            }
            catch (Exception ex)
            {
                _db.RollBackTransaction();
            }
            //int res = _db.Execute("UPDATE [dbo].[Users] SET nickname='update' WHERE Id=@Id", new { Id = 999 });
            //string email = _db.ExecuteScalar<string>("SELECT email FROM [dbo].[Users] WHERE Id=@Id", new { Id = 999 });
            //User user = _db.QueryFirst<User>("SELECT * FROM [Users] WHERE id=@id", new { Id = 999 });
            string name = _db.QueryFirst<string>("SELECT nickName FROM [Users] WHERE id=@id", new { Id = 999 });
            //int? phoneArea = _db.QueryFirst<int?>("SELECT phoneArea FROM [Users] WHERE id=@id", new { Id = 999 });
            //IEnumerable<User> users = _db.Query<User>("SELECT top 10 * FROM [Users]");
            //IEnumerable<int> ids = _db.Query<int>("SELECT top 10 * FROM [Users] WHERE id < @id", new { Id = 11064 });
            return Ok(name);
        }