DALBase 直接操作实体类《增删该查》
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Model;
using System.Reflection;
using MySql.Data.MySqlClient;
namespace DAL
{
public class DALBase<T> where T : ModelBase
{
/// <summary>
/// 获取主键名字
/// </summary>
/// <returns></returns>
private static string GetKeyName()
{
Type t = typeof(T);
object[] obj = t.GetCustomAttributes(true);
if (obj.Length < 1)
{
throw new Exception("模型类上没有标注主键特性");
}
string str = (obj[0] as KeyInfoAttribute).KeyName;
return str;
}
public static bool Delete(ModelBase bas) {
Type t = bas.GetType();
string tableName = t.Name;
string sql = "delete from " + tableName + "where";
foreach (PropertyInfo p in t.GetProperties()) {
if (p.GetValue(bas, null) != null) {
sql += p.Name + "=" + "@" + p.Name;
}
}
return false;
}
public static bool Delete(object id)
{
Type t = typeof(T);
string tableName = t.Name;
string sql = string.Format("delete from {0} where {1}=@{1}", tableName, GetKeyName());
List<MySqlParameter> ls = new List<MySqlParameter>() {
new MySqlParameter("@"+GetKeyName(),id)
};
return SQLHelp.CUD(sql, ls) > 0 ? true : false;
}
public static bool Update(ModelBase bas)
{
List<MySqlParameter> ls = new List<MySqlParameter>();
//得到主键
string keyName = GetKeyName();
Type t = bas.GetType();
string tableName = t.Name;
StringBuilder sb = new StringBuilder();
sb.Append("update " + tableName + " set ");
string where = "";
foreach (PropertyInfo p in t.GetProperties())
{
if (p.GetValue(bas, null) != null)
{
if (keyName.ToUpper() == p.Name.ToUpper())
{
where = " where " + p.Name + " = " + "@" + p.Name;
}
else
{
sb.Append(p.Name);
sb.Append(" = ");
sb.Append("@" + p.Name);
sb.Append(",");
}
MySqlParameter ps = new MySqlParameter("@" + p.Name, p.GetValue(bas, null));
ls.Add(ps);
}
}
sb.Remove(sb.Length - 1, 1);
sb.Append(where);
if (where == "")
{
throw new Exception("主键没有赋值");
}
return SQLHelp.CUD(sb.ToString(), ls) > 0 ? true : false;
}
public static bool Insert(ModelBase bas)
{
List<MySqlParameter> list = new List<MySqlParameter>();
//获得类型, typeof(类型)
Type t = bas.GetType();
string tableName = t.Name;
StringBuilder sb = new StringBuilder();
sb.Append("insert into " + tableName + " (");
StringBuilder sb1 = new StringBuilder();
foreach (PropertyInfo p in t.GetProperties())
{
//判断属性是否赋值？只有赋值的属性才拼接到SQL语句里面
if (p.GetValue(bas, null) != null)
{
sb.Append(p.Name);
sb.Append(",");
sb1.Append("@" + p.Name);
sb1.Append(",");
MySqlParameter ps = new MySqlParameter("@" + p.Name, p.GetValue(bas, null));
list.Add(ps);
}
}
sb.Remove(sb.Length - 1, 1);
sb.Append(") values (");
sb1.Remove(sb1.Length - 1, 1);
sb1.Append(")");
sb.Append(sb1);
return SQLHelp.CUD(sb.ToString(), list) > 0 ? true : false;
// sb.Append(" values (");
/* // insert into (userName,userPwd) values('admin','123')
Type t = bas.GetType();
string tableName = t.Name;
StringBuilder sb = new StringBuilder();
sb.Append("insert into (");
//sb.Append(tableName);
foreach (PropertyInfo p in t.GetProperties())
{
if (p.GetValue(bas, null) != null)
{
sb.Append(p.Name);
sb.Append(",");
}
}
sb.Append(" values ");*/
}
public static List<T> Selects(string sql)
{
List<T> ls = new List<T>();
// 反射 得到类名
Type t = typeof(T);
// string sql = string.Format("select * from {0}", t.Name);
using (MySqlDataReader sd = SQLHelp.SelectReader(sql))
{
while (sd.Read())
{
//创建对象，并且给对象赋值
// Student s = new Student(){Stuid=sd["stuid"]}
T t1 = Activator.CreateInstance<T>();
foreach (PropertyInfo p in t1.GetType().GetProperties())
{
if (sd[p.Name] != DBNull.Value)
{
p.SetValue(t1, sd[p.Name], null);
}
else
{
p.SetValue(t1, null, null);
}
}
ls.Add(t1);
//反射属性
// s.StuID = sd["stuid"]
}
}
return ls;
}
/// <summary>
/// 查询所有的方法
/// </summary>
/// <returns></returns>
public static List<T> Selects()
{
List<T> ls = new List<T>();
// 反射 得到类名
Type t = typeof(T);
string sql = string.Format("select * from {0}", t.Name);
using (MySqlDataReader sd = SQLHelp.SelectReader(sql))
{
while (sd.Read())
{
//创建对象，并且给对象赋值
// Student s = new Student(){Stuid=sd["stuid"]}
T t1 = Activator.CreateInstance<T>();
foreach (PropertyInfo p in t1.GetType().GetProperties())
{
if (sd[p.Name] != DBNull.Value)
{
p.SetValue(t1, sd[p.Name], null);
}
else
{
p.SetValue(t1, null, null);
}
}
ls.Add(t1);
//反射属性
// s.StuID = sd["stuid"]
}
}
return ls;
}
/// <summary>
/// 插入修改历史记录
/// </summary>
/// <param name="bas">实体</param>
/// <param name="uid">用户id</param>
public static bool History(ModelBase bas,int uid)
{
List<string> stringList = new List<string>();
string strsql = "";
try
{
string keyName = GetKeyName();//主键名
Type t = bas.GetType();//当前实体
string tableName = t.Name;//实体名称既表名
int tableID = 0;//主键值
foreach (PropertyInfo p in t.GetProperties())
{
if (keyName.ToUpper() == p.Name.ToUpper())
tableID = Convert.ToInt32(p.GetValue(bas, null));//获取主键值
}
T t1 = Activator.CreateInstance<T>();
string sql = string.Format("select * from {0} where {1}={2}", tableName, keyName, tableID);
using (MySqlDataReader sd = SQLHelp.SelectReader(sql))
{
while (sd.Read())
{
foreach (PropertyInfo p in t1.GetType().GetProperties())
{
if (sd[p.Name] != DBNull.Value)
p.SetValue(t1, sd[p.Name], null);
else
p.SetValue(t1, null, null);
}
}
}
foreach (PropertyInfo pNew in t.GetProperties())
{
foreach (PropertyInfo pOld in t1.GetType().GetProperties())
{
if (pNew.Name == pOld.Name)
{
if (Convert.ToString(pNew.GetValue(bas, null)) != Convert.ToString(pOld.GetValue(t1, null)))
{
strsql = string.Format("INSERT INTO ser_operationrecord(`TableId`,`TableName`,`old`,`new`,`UId`,`CreationTime`)VALUES ('{0}','{1}','{2}','{3}','{4}','{5}')", tableID, tableName, Convert.ToString(pOld.GetValue(t1, null)), Convert.ToString(pNew.GetValue(bas, null)), uid, DateTime.Now);
stringList.Add(strsql);
}
}
}
}
if (stringList.Count > 0)
return SQLHelp.ExecuteSqlTran(stringList);
else
return true;
}
catch (Exception)
{
return false;
}
}
/// <summary>
/// 根据ID查询指定实体
/// </summary>
/// <param name="id">实体ID</param>
/// <returns></returns>
public static T SelectById(int id )
{
T t1 = Activator.CreateInstance<T>();
Type t = typeof(T);
string keyName = GetKeyName();//实体主键
string tableName = t.Name;//实体名称既表名
string sql = string.Format("select * from {0} where {1}={2}", tableName, keyName, id);
using (MySqlDataReader sd = SQLHelp.SelectReader(sql))
{
while (sd.Read())
{
foreach (PropertyInfo p in t1.GetType().GetProperties())
{
if (sd[p.Name] != DBNull.Value)
{
p.SetValue(t1, sd[p.Name], null);
}
else
{
p.SetValue(t1, null, null);
}
}
}
}
return t1;
}
}
}
