ORM泛型+反射

      #region 动态查询的方式使用泛型+反射
        //sql语句 select *from student
        public static List<T> Query(string where)
        {
            DataTable tb = new DataTable();
            List<T> list = new List<T>();

            //
            string sql = GetQuerySql();
            sql += where;
            //用反射赋值
            using (SqlConnection connection = new SqlConnection(con))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(command);
                    sqlDataAdapter.Fill(tb);
                    //获取T的类型
                    Type type = typeof(T);
                    //循环循环行
                    for (int i = 0; i < tb.Rows.Count; i++)
                    {
                        //实例化T，每一次都需要实例化new 对象
                        Object obj = Activator.CreateInstance(type);
                        //循环列
                        for (int j = 0; j < tb.Columns.Count; j++)
                        {
                            //获取列的名称 student s_id  GetProperty("s_id")
                            PropertyInfo info = type.GetProperty(tb.Columns[j].ColumnName);//赋值了s_id

                            //判断类型 tb.Columns[j].DataType 获取数据库列的类型

                            #region 类型的判断并赋值
                            //int类型


                            if (tb.Columns[j].DataType == typeof(Int32))
                            {
                                //有没有可能空值？
                                if (tb.Rows[i][j] != null)
                                {
                                    //obj.setValue(info,12);
                                    info.SetValue(obj, int.Parse(tb.Rows[i][j].ToString()), null);
                                }
                                else
                                {
                                    //null值的情况
                                    info.SetValue(obj, 0, null);
                                }
                            }

                            //float类型
                            else if (tb.Columns[j].DataType == typeof(float))
                            {
                                //有没有可能空值？
                                if (tb.Rows[i][j] != null)
                                {
                                    info.SetValue(obj, float.Parse(tb.Rows[i][j].ToString()), null);
                                }
                                else
                                {
                                    //null值的情况
                                    info.SetValue(obj, 0.0f, null);
                                }
                            }

                            //datetime
                            else if (tb.Columns[j].DataType == typeof(DateTime))
                            {
                                //有没有可能空值？
                                if (tb.Rows[i][j] != null)
                                {
                                    info.SetValue(obj, DateTime.Parse(tb.Rows[i][j].ToString()), null);
                                }
                                else
                                {
                                    //null值的情况
                                    info.SetValue(obj, DateTime.Now, null);
                                }
                            }

                            //double
                            else if (tb.Columns[j].DataType == typeof(double))
                            {
                                //有没有可能空值？
                                if (tb.Rows[i][j] != null)
                                {
                                    info.SetValue(obj, double.Parse(tb.Rows[i][j].ToString()), null);
                                }
                                else
                                {
                                    //null值的情况
                                    info.SetValue(obj, 0.00, null);
                                }
                            }
                            else
                            {
                                //string

                                //有没有可能空值？
                                if (tb.Rows[i][j] != null)
                                {
                                    info.SetValue(obj, tb.Rows[i][j].ToString(), null);
                                }
                                else
                                {
                                    //null值的情况
                                    info.SetValue(obj, "", null);
                                }
                            }
                            #endregion



                        }
                        //将object 类型强转对应的类型
                        list.Add((T)obj);//（类型）强制转换
                    }
                }
            }
            return list;
        }
        //获取sql
        public static string GetQuerySql()
        {
            Type type = typeof(T);
            //type.Name获取类的名称
            //无需实例化
            string sql = "select * from " + type.Name + " where 1=1 ";
            return sql;
        }
        #endregion

        #region 动态添加的操作
        public static int Insert(T models)
        {
            int flag = 0;
            //获取sql
            string sql = GetInsertSql(models);
            using (SqlConnection connection = new SqlConnection(con))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    flag = command.ExecuteNonQuery();
                }
            }
            return flag;
        }

        public static string GetInsertSql(T models)
        {
            //已实例化的实体用GetType，如果未实例化的我们需要使用typeof
            Type type = models.GetType();//new 过的对象
            //先获取所有的字段
            PropertyInfo[] info = type.GetProperties();
            //这里是字段
            string field = "";
            //获取值
            string value = "";
            for (int i = 0; i < info.Length; i++)
            {
                //有可能字段没有值，没有值的我们不添加info 是属性[i]第几个属性
                if (info[i].GetValue(models) != null)
                {
                    if (!info[i].Name.Equals("Id"))
                    {
                        //获取字段和值
                        if ((i + 1) == info.Length)//代表最后一个循环不要,
                        {
                            field += info[i].Name;
                            value += "'" + info[i].GetValue(models).ToString() + "'";//为什么没有用类型判断，
                        }
                        else
                        {
                            field += info[i].Name + ",";
                            value += "'" + info[i].GetValue(models).ToString() + "',";//为什么没有用类型判断，
                        }
                    }
                }
            }
            //生成了sql语句
            string sql = "insert into " + type.Name + "(" + field + ") values(" + value + ")";
            return sql;
        }
        #endregion

        #region 动态修改的操作
        public static int Update(T models, string where)
        {
            int flag = 0;
            //获取sql
            string sql = GetUpdateSql(models, where);

            using (SqlConnection connection = new SqlConnection(con))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    flag = command.ExecuteNonQuery();
                }
            }
            return flag;
        }

        public static string GetUpdateSql(T models, string where)
        {
            Type type = models.GetType();
            //获取所有的字段
            string updateStr = "";
            PropertyInfo[] propertyInfos = type.GetProperties();
            for (int i = 0; i < propertyInfos.Length; i++)
            {
                if (propertyInfos[i].GetValue(models) != null)
                {
                    if ((i + 1) == propertyInfos.Length)
                    {
                        updateStr += propertyInfos[i].Name + "='" + propertyInfos[i].GetValue(models) + "'";
                    }
                    else
                    {
                        updateStr += propertyInfos[i].Name + "='" + propertyInfos[i].GetValue(models) + "',";
                    }
                }
            }
           //update biao set ziduan =zhi where userNAME=
            string sql = "update " + type.Name + " set " + updateStr + " where 1=1 " + where;
            return sql;
        }

        #endregion

        #region 动态删除
        public static int Delete(T models, string where)
        {
            int flag = 0;
            //获取sql
            string sql = GetDeleteSql(models, where);

            using (SqlConnection connection = new SqlConnection(con))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    flag = command.ExecuteNonQuery();
                }
            }
            return flag;
        }

        //删除的sql语句
        public static string GetDeleteSql(T models, string where)
        {
            Type type = models.GetType();
            //获取所有的字段

            
            //delete from 表 where
            string sql = "delete from  " + type.Name + "  where 1=1 " + where;
            return sql;
        }

        #endregion

 //查询
        public List<EmpUserInfo> Query(string where)
        {
           return ORMDBhelper<EmpUserInfo>.Query(where); 
        }
        //插入
        public int Insert(EmpUserInfo info)
        {
            return ORMDBhelper<EmpUserInfo>.Insert(info);
        }
        //修改
        public int Update(EmpUserInfo info)
        {
            return ORMDBhelper<EmpUserInfo>.Update(info, " and id="+info.Id.ToString());
        }
        //删除
        public int Delete(EmpUserInfo info)
        {
            return ORMDBhelper<EmpUserInfo>.Delete(info, " and id=" + info.Id.ToString());
        }

EmpUserInfoBll bll = new EmpUserInfoBll();
        // GET: api/Login
        [Route("api/query")]
        [HttpGet]
        public IHttpActionResult Query(string where)
        {
           return Ok(bll.Query(where));
        }
        [HttpPost]
        [Route("api/insert")]
        public int Insert(EmpUserInfo info)
        {
            return bll.Insert(info);
        }
        [HttpPost]
        [Route("api/update")]
        public int Update(EmpUserInfo info)
        {
            return bll.Update(info);
        }
        [HttpPost]
        [Route("api/delete")]
        public int DeletePost(EmpUserInfo info)
        {
            return bll.Delete(info);
        }