反射+泛型实现简单O/RM框架

一、配置appConfig文件，添加数据库连接字符串（以控制台项目+sqlserver数据库为例）

---

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.8" />
    </startup>
    <appSettings>
        <add key="constr" value="Data Source=127.0.0.1;Initial Catalog=database; User ID=sa;Password=******"/>
    </appSettings>
</configuration>

---

二、添加Ado.net Helper类。用于操作连接数据库

---

    /// <summary>
    /// Ado.Net帮助类
    /// </summary>
    public class AdoNetHelper
    {
        //私有连接字符串 
        private static string conStr = ConfigurationManager.AppSettings["constr"];
        public static DataTable ExecuteDataTable(string sql, CommandType cmdType, params SqlParameter[] par)
        {
            //datatable对象
            DataTable dt = new DataTable();

            //用于处理非托管对象。某些类型的非托管对象有数量限制或很消耗系统资源。为了及时释放资源，使用using语句可以确保这些资源适当地处置（dispose）
            using (SqlDataAdapter adapter = new SqlDataAdapter(sql, conStr))
            {
                //存储过程赋值
                adapter.SelectCommand.CommandType = cmdType;
                //判断参数不为空 长度>0
                if (par != null)
                {
                    //向数组参数中添加值
                    adapter.SelectCommand.Parameters.AddRange(par);
                }
                //填充数据
                adapter.Fill(dt);
                return dt;//返回值
            }
        }

        public static SqlDataReader ExecuteDataReader(string sql, CommandType cmdType, params SqlParameter[] par)
        {
            //SqlConnection要始终保持打开状态 不能使用using释放资源
            SqlConnection conn = new SqlConnection(conStr);

            using (SqlCommand com = new SqlCommand(sql, conn))
            {
                //存储过程赋值
                com.CommandType = cmdType;

                //判断参数不为空
                if (par != null)
                {
                    //传入参数
                    com.Parameters.AddRange(par);
                }
                try
                {
                    //如果连接状态关闭
                    if (conn.State == ConnectionState.Closed)
                    {
                        //打开连接
                        conn.Open();
                    }
                    //返回结果  参数:当关闭reader时也关闭SqlConnection
                    return com.ExecuteReader(CommandBehavior.CloseConnection);
                }
                catch (Exception)
                {
                    //关闭连接
                    conn.Close();
                    //释放资源
                    conn.Dispose();
                    throw;//抛出异常
                }
            }
        }

        public static int ExecuteNonQuery(string sql, CommandType cmdType, params SqlParameter[] par)
        {
            using (SqlConnection conn = new SqlConnection(conStr))
            {
                using (SqlCommand com = new SqlCommand(sql, conn))
                {
                    com.CommandType = cmdType;

                    if (par != null)
                    {
                        com.Parameters.AddRange(par);
                    }
                    conn.Open();

                    return com.ExecuteNonQuery();
                }
            }
        }

        public static object ExecuteScalar(string sql, CommandType cmdType, params SqlParameter[] par)
        {
            using (SqlConnection conn = new SqlConnection(conStr))
            {
                using (SqlCommand com = new SqlCommand(sql, conn))
                {
                    com.CommandType = cmdType;

                    if (par != null)
                    {
                        com.Parameters.AddRange(par);
                    }
                    conn.Open();

                    return com.ExecuteScalar();
                }
            }
        }

    }

---

 

三、添加SqlHelper接口，使用接口的目的是为了以后可以扩展使用其他数据库

    public interface ISqlHelper<T> where T : class
    {
        T GetT(int Id);

        List<T> Query(string where);

        int Add(T entity);

        int Update(T entity);

        int Delete(T entity);
    }

---

 

四、添加接口具体实现：使用泛型接收不同的实体Model，在通过反射获取实体类具体信息进行增删改查

---

    public class SqServerlHelper<T> : ISqlHelper<T>
        where T : class
    {
        public int Add(T entity)
        {
            Type type = entity.GetType();
            //查询除Id以外所有属性
            var properList = type.GetProperties().Where(s => s.Name != "Id");

            string sql = $@"insert into {type.Name}({string.Join(",", properList.Select(a => a.Name))}) VALUES ({string.Join(",", properList.Select(a => "@" + a.Name))})";

            List<SqlParameter> sqlParameters = new List<SqlParameter>();
            foreach (var prop in properList)
            {
                sqlParameters.Add(new SqlParameter("@" + prop.Name, prop.GetValue(entity)));
            }

            return AdoNetHelper.ExecuteNonQuery(sql, CommandType.Text, sqlParameters.ToArray());
        }

        public int Delete(T entity)
        {
            Type type = entity.GetType();

            string sql = $@"delete from {type.Name} where Id=@Id ";

            SqlParameter[] sqlParameters = new SqlParameter[]
            {
                  new SqlParameter("@Id",type.GetProperty("Id").GetValue(entity))
            };
            return AdoNetHelper.ExecuteNonQuery(sql, CommandType.Text, sqlParameters);
        }

        public T GetT(int Id)
        {
            Type type = typeof(T);
            var properties = type.GetProperties();
            string sql = $@"select {string.Join(",", type.GetProperties().Select(s => s.Name))} from {type.Name} where Id=@Id";
            SqlParameter[] sqlParameters = new SqlParameter[]
            {
               new SqlParameter("@Id",Id)
            };
            var reader = AdoNetHelper.ExecuteDataReader(sql, CommandType.Text, sqlParameters);
            reader.Read();
            object obj = Activator.CreateInstance(type);
            foreach (var prop in type.GetProperties())
            {
                prop.SetValue(obj, reader[prop.Name]);
            }
            return (T)obj;
        }

        public List<T> Query(string where)
        {
            Type type = typeof(T);
            var properties = type.GetProperties();
            string sql = $@"select {string.Join(",", type.GetProperties().Select(s => s.Name))} from {type.Name} where 1=1 " + where;
            //SqlParameter[] sqlParameters = new SqlParameter[]
            //{
            //   new SqlParameter("@Id",Id)
            //};
            var dataTable = AdoNetHelper.ExecuteDataTable(sql, CommandType.Text, null);
            //reader.Read();
            //var list = dataTable.AsEnumerable().ToList();
            List<T> list = new List<T>();
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                object obj = Activator.CreateInstance(type);
                foreach (var prop in type.GetProperties())
                {
                    prop.SetValue(obj, dataTable.Rows[i][prop.Name]);
                }
                list.Add(obj as T);
            }

            return list;
        }

        public int Update(T entity)
        {
            Type type = entity.GetType();
            //查询除Id以外所有属性
            var properList = type.GetProperties().Where(s => s.Name != "Id");
            string sqlSet = "";
            List<SqlParameter> sqlParameters = new List<SqlParameter>();
            foreach (var prop in type.GetProperties())
            {
                if (prop.Name != "Id")
                    sqlSet += prop.Name + "=@" + prop.Name + ",";
                sqlParameters.Add(new SqlParameter("@" + prop.Name, prop.GetValue(entity)));
            }
            string sql = $@"update {type.Name} set ";
            sqlSet = sqlSet.Substring(0, sqlSet.Length - 1);//截掉最后一个','
            sql += sqlSet + " where Id=@Id";


            return AdoNetHelper.ExecuteNonQuery(sql, CommandType.Text, sqlParameters.ToArray());
        }
    }

---

五、调用测试，简单手写的O/RM框架就搭建出来啦~

---

        static void Main(string[] args)
        {
             SqServerlHelper<AdminLogs> sqServerlHelper = new SqServerlHelper<AdminLogs>();
            var log = sqServerlHelper.GetT(2);
            log.AdminId = 1;
            log.CreateTime = DateTime.Now;
            log.Msg = "手写orm测试Update";
            //int id = sqServerlHelper.Add(log);
            //Console.WriteLine(id);
            int id = sqServerlHelper.Update(log);

            SqServerlHelper<Product> sql = new SqServerlHelper<Product>();
            var list = sql.Query(" and Id<10 ");
            //var pro = sql.GetT(4);
            //Console.WriteLine(pro.ToString());
            Console.ReadKey();
        }