#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); }