反射+泛型实现简单O/RM框架
一、配置appConfig文件，添加数据库连接字符串（以控制台项目+sqlserver数据库为例）
<?xml version="1.0" encoding="utf-8" ?>"v4.0" sku=".NETFramework,Version=v4.8" /> "constr" value="Data Source=127.0.0.1;Initial Catalog=database; User ID=sa;Password=******"/>
二、添加Ado.net Helper类。用于操作连接数据库
////// Ado.Net帮助类 /// 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 ISqlHelperwhere T : class { T GetT(int Id); List Query(string where); int Add(T entity); int Update(T entity); int Delete(T entity); }
四、添加接口具体实现：使用泛型接收不同的实体Model，在通过反射获取实体类具体信息进行增删改查
public class SqServerlHelper: ISqlHelper 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 sqlParameters = new List (); 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 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 list = new List (); 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 sqlParameters = new List (); 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) { SqServerlHelpersqServerlHelper = new SqServerlHelper (); 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 sql = new SqServerlHelper (); var list = sql.Query(" and Id<10 "); //var pro = sql.GetT(4); //Console.WriteLine(pro.ToString()); Console.ReadKey(); }