#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);
}
浙公网安备 33010602011771号