.NET Core 自己封装实现一个 SqlHelper ORM

SqlHelper工具类（实现添加、查询）

    public class SqlHelper
    {
        private static string ConnectionStringCustomers = ConfigurationManager.ConnectionStrings["Customers"].ConnectionString;

        public T Find(int id) where T : BaseModel
        {
            Type type = typeof(T);
            string tableName = type.GetMappingName();
            string columnString = string.Join(",", type.GetProperties().Select(p => $"[{p.GetMappingName()}]"));
            string sql = $@"SELECT {columnString} FROM [{tableName}] WHERE ID = @Id";
            SqlParameter[] sqlParameterList = new SqlParameter[] {
                new SqlParameter("@Id",id)
            };
            using (SqlConnection conn = new SqlConnection(ConnectionStringCustomers))
            {
                SqlCommand command = new SqlCommand(sql, conn);
                command.Parameters.AddRange(sqlParameterList);
                conn.Open();
                var reader = command.ExecuteReader();
                if (reader.Read())
                {
                    T t = Activator.CreateInstance();
                    foreach (var prop in type.GetProperties())
                    {
                        prop.SetValue(t, reader[prop.GetMappingName()] is DBNull ? null : reader[prop.GetMappingName()]);
                    }
                    return t;
                }
                else
                {
                    return default(T);
                }
            }
        }
        public bool Insert(T t) where T : BaseModel
        {
            Type type = typeof(T);
           
            string sql = SqlBuilder.GetSql();

            var sqlParameterList = type.GetProperties().Select(p => new SqlParameter($"@{p.Name}", p.GetValue(t) ?? DBNull.Value)).ToArray();

            using (SqlConnection conn = new SqlConnection(ConnectionStringCustomers))
            {
                SqlCommand command = new SqlCommand(sql, conn);
                command.Parameters.AddRange(sqlParameterList);
                conn.Open();
                int iResult = command.ExecuteNonQuery();
                return iResult == 1;
            }
        }
    }

   使用方法

SqlHelper helper = new SqlHelper();
Company company1 = helper.Find(1);
helper.Insert(company1);    

改进一下实现延迟提交方式，模拟 DBContext

    /// 

    /// 延迟提交式，模拟DBContext
    /// 
    public class SqlHelperDelay : IDisposable
    {
        /// 

        /// 通用主键查询操作
        /// 
        /// 
        /// 
        /// 
        public T Find(int id) where T : BaseModel, new()
        {
            Type type = typeof(T);
            //string columnsString = string.Join(",", type.GetProperties().Select(p => $"[{p.GetMappingName()}]"));
            //string sql = $"SELECT {columnsString} FROM [{type.GetMappingName()}] WHERE ID={id} ";
            string sql = $"{SqlBuilder.GetFindSql()}{id}";
            string connString = SqlConnectionPool.GetConnectionString(SqlConnectionPool.SqlConnectionType.Read);
            Console.WriteLine($"当前查询的字符串为{connString}");
            using (SqlConnection conn = new SqlConnection(connString))
            {
                SqlCommand command = new SqlCommand(sql, conn);
                conn.Open();
                var reader = command.ExecuteReader();
                if (reader.Read())
                {
                    T t = new T();
                    foreach (var prop in type.GetProperties())
                    {
                        string propName = prop.GetMappingName();//查询时as一下，可以省下一轮
                        prop.SetValue(t, reader[propName] is DBNull ? null : reader[propName]);//可空类型  设置成null而不是数据库查询的值
                    }
                    return t;
                }
                else
                {
                    return default(T);
                }
            }
        }

        private IList _SqlCommandList = new List();

        public void Insert(T t) where T : BaseModel, new()
        {
            Type type = t.GetType();
            string sql = SqlBuilder.GetInsertSql();
            var paraArray = type.GetProperties().Select(p => new SqlParameter($"@{p.GetMappingName()}", p.GetValue(t) ?? DBNull.Value)).ToArray();

            SqlCommand command = new SqlCommand(sql);
            command.Parameters.AddRange(paraArray);
            this._SqlCommandList.Add(command);
        }
        public void SaveChange()
        {
            string connString = SqlConnectionPool.GetConnectionString(SqlConnectionPool.SqlConnectionType.Write);
            if (this._SqlCommandList.Count > 0)
            {
                using (SqlConnection conn = new SqlConnection(connString))
                {
                    conn.Open();
                    using (SqlTransaction trans = conn.BeginTransaction())
                    {
                        try
                        {
                            foreach (var command in this._SqlCommandList)
                            {
                                command.Connection = conn;
                                command.Transaction = trans;
                                command.ExecuteNonQuery();
                            }
                            trans.Commit();
                        }
                        catch (Exception)
                        {
                            trans.Rollback();
                            throw;
                        }
                        finally
                        {
                            this._SqlCommandList?.Clear();
                        }
                    }
                }
            }
        }

        public void Dispose()
        {
            this._SqlCommandList?.Clear();
        }
    }