public class MyEntity
{
/// <summary>
/// 需要执行的SQL
/// </summary>
private string MySql;
/// <summary>
/// 表名
/// </summary>
private string TableName;
/// <summary>
/// 列名SQL
/// </summary>
private string ColumnNameSql;
/// <summary>
/// 值SQL
/// </summary>
private string ValueSql;
/**保存老的值***/
private object oldValue;
/// <summary>
/// 条件SQL
/// </summary>
private string ConditionSql;
private string NewValueSql;
private const string str = "server =.;uid=sa;pwd=yw2064142737;database=QiYuan";
private readonly SqlConnection conn = new SqlConnection(str);
public DataTable SelectResult { get; private set; } = new DataTable();
private int Insert<T>(T t)
{
conn.Open();
SqlCommand cmd = new SqlCommand(t.ToString(),conn);
int i = cmd.ExecuteNonQuery();
conn.Close();
return i;
}
private int Delete<T>(T t)
{
conn.Open();
SqlCommand cmd = new SqlCommand(t.ToString(),conn) ;
int i = cmd.ExecuteNonQuery();
conn.Close();
return i;
}
private object MySelect<T>(T t)
{
conn.Open();
SqlCommand cmd = new SqlCommand(t.ToString(),conn) ;
return cmd.ExecuteScalar();
}
private SqlDataReader MyDataSelect<T>(T t)
{
conn.Open();
SqlCommand cmd = new SqlCommand(t.ToString(), conn);
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
private int Update<T>(T t)
{
conn.Open();
SqlCommand cmd = new SqlCommand(t.ToString(),conn);
int i=cmd.ExecuteNonQuery();
conn.Close();
return i;
}
private void ToList(string sql) {
conn.Open();
SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
sda.Fill(this.SelectResult);
conn.Close();
}
public Status status { get; set; }
public enum Status
{
Insert,
Delete,
Select,
Update,
Find
}
public void Entity<T>(T t)
{
Type type = t.GetType();
GetTableName(type);//得到表名
PropertyInfo[] info = type.GetProperties();
GetColumnNameSql(info);//得到列SQL语句
GetValueSql(info, t);//得到值SQL语句
GetCondition();//得到条件
GetNewValueSql(t);//得到更改后的值
}
private void GetTableName(Type type)
{
this.TableName = type.Name;
}
private void GetColumnNameSql(PropertyInfo[] info)
{
StringBuilder sb = new StringBuilder();
for (int i = 0; i < info.Length; i++)
{
if (info[i].IsDefined(typeof(ColumnNameAttribute),true))
{
ColumnNameAttribute columnName = (info[i].GetCustomAttribute(typeof(ColumnNameAttribute))) as ColumnNameAttribute;
sb.Append($"[{columnName.GetName()}],");
}
else
{
sb.Append($"[{info[i].Name}],");
}
}
sb.Remove(sb.Length-1,1);
this.ColumnNameSql = sb.ToString();
}
private void GetValueSql(PropertyInfo[] info,object t)
{
StringBuilder sb = new StringBuilder();
for (int i = 0; i < info.Length; i++)
{
sb.Append($"'{info[i].GetValue(t)}',");
}
sb.Remove(sb.Length - 1, 1);
this.ValueSql = sb.ToString();
}
private void GetCondition()
{
string [] arr = this.ColumnNameSql.Split(',');
string[] arrs = this.ValueSql.Split(',');
StringBuilder sbCondition = new StringBuilder();
sbCondition.Append(" where 1=1");
for (int i = 0; i < arr.Length; i++)
{
if (arrs[i]!=null&&arrs[i]!="''"&&arrs[i].ToString()!=string.Empty)
{
sbCondition.Append($" and {arr[i]}={arrs[i]}");
}
}
this.ConditionSql = sbCondition.ToString();
}
private void GetNewValueSql(object obj)
{
if (obj==null)
{
return;
}
Type type = obj.GetType();
PropertyInfo[] info = type.GetProperties();
StringBuilder sb = new StringBuilder();
sb.Append("set ");
for (int i = 0; i < info.Length; i++)
{
if (info[i].GetValue(obj)!=null&& info[i].GetValue(obj).ToString() != "" && info[i].GetValue(obj).ToString()!=String.Empty)
{
if (info[i].IsDefined(typeof(ColumnNameAttribute),true))
{
ColumnNameAttribute columnName = (info[i].GetCustomAttribute(typeof(ColumnNameAttribute))) as ColumnNameAttribute;
sb.Append($"[{columnName.GetName()}]='{info[i].GetValue(obj)}' ,");
}
else
{
sb.Append($"[{info[i].Name}]='{info[i].GetValue(obj)}' ,");
}
}
}
sb.Remove(sb.Length-1,1);
this.NewValueSql = sb.ToString();
}
private void GetSql()
{
StringBuilder sb = new StringBuilder();
switch (status)
{
case Status.Insert:
sb.Append($"Insert into [{this.TableName}] ({this.ColumnNameSql}) values({this.ValueSql})");
break;
case Status.Delete:
sb.Append($"Delete from [{this.TableName}] {this.ConditionSql}");
break;
case Status.Select:
case Status.Find:
sb.Append($"Select {this.ColumnNameSql} from [{this.TableName}] {this.ConditionSql} ");
break;
case Status.Update:
sb.Append($"Update [{this.TableName}] {this.NewValueSql} {this.ConditionSql} ");
break;
default:
break;
}
this.MySql = sb.ToString();
}
public T Find<T>(Func<T,bool> func) where T:class {
//反射有两种形式 一个参数名 一个泛型的反射 参数名的反射是gettype 泛型的反射就是typeof
Type type = typeof(T);
T t= Activator.CreateInstance(type) as T;// T t=new T();
T old = Activator.CreateInstance(type) as T;
PropertyInfo[] infos= type.GetProperties();
this.Entity<T>(t);
this.status = Status.Select;
this.SaveChanges();
string columnNameval = "";
if (this.SelectResult!=null)
{
foreach (DataRow item in this.SelectResult.Rows)
{
for (int i = 0; i < infos.Length; i++)
{
if (infos[i].IsDefined(typeof(ColumnNameAttribute), true))
{
ColumnNameAttribute columnName = (infos[i].GetCustomAttribute(typeof(ColumnNameAttribute))) as ColumnNameAttribute;
columnNameval = columnName.GetName();
}
else
{
columnNameval = infos[i].Name;
}
object val = item["" + columnNameval + ""] is DBNull ? string.Empty
: item["" + columnNameval + ""];
infos[i].SetValue(t, val);
infos[i].SetValue(old, val);
}
if (func(t))
{
this.oldValue = old;
return t;
}
}
}
return null;
}
public void SaveChanges()
{
if (this.status== Status.Update)
{
if (this.oldValue!=null)
{
Type type = this.oldValue.GetType();
GetValueSql(type.GetProperties(), this.oldValue);//得到值SQL语句
GetCondition();//得到条件
}
}
GetSql();
switch (status)
{
case Status.Insert:
if (this.Insert(MySql)>0)
{
Console.WriteLine("添加成功");
}
break;
case Status.Delete:
if (this.Delete(MySql) > 0)
{
Console.WriteLine("删除成功");
}
break;
case Status.Select:
this.ToList(MySql);
break;
case Status.Find:
this.MyDataSelect(MySql) ;
break;
case Status.Update:
if (this.Update(MySql)>0)
{
Console.WriteLine("修改成功");
}
break;
default:
break;
}
}
}