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

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

public class SqlHelper
{
    private static string ConnectionStringCustomers = ConfigurationManager.ConnectionStrings["Customers"].ConnectionString;
 
    public T Find<T>(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<T>();
                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 t) where T : BaseModel
    {
        Type type = typeof(T);
        
        string sql = SqlBuilder<T>.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<Company>(1);
helper.Insert<Company>(company1);    

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

/// <summary>
/// 延迟提交式，模拟DBContext
/// </summary>
public class SqlHelperDelay : IDisposable
{
    /// <summary>
    /// 通用主键查询操作
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="id"></param>
    /// <returns></returns>
    public T Find<T>(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<T>.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<SqlCommand> _SqlCommandList = new List<SqlCommand>();
 
    public void Insert<T>(T t) where T : BaseModel, new()
    {
        Type type = t.GetType();
        string sql = SqlBuilder<T>.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();
    }
}