1.创建一个SQL生成帮助类分别写出以下增删改三个公共方法
数据插入方法
public static int Save(object obj)
{
int n = 0;
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
StringBuilder sql = new StringBuilder();
StringBuilder sqlend = new StringBuilder();
//获取对象的属性数组
PropertyInfo[] pro = obj.GetType().GetProperties();
//主键属性数组
List<PropertyInfo> idlist = GetIdProperty(pro);
//要更新的数据表
string table = FindPropertyInfoValue(obj, "TableName").ToString();
//执行的sql语句
string sqltext = string.Empty;
//INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)
sql.Append("INSERT INTO " + table + "(");
sqlend.Append(" VALUES (");
foreach (PropertyInfo item in pro)
{//拼接sql语句主体
if (item.Name == "TableName")
{
continue;
}
else
{
string columnValue = item.GetValue(obj, null) + "";
if (string.IsNullOrEmpty(columnValue))
{//去掉空属性
continue;
}
if (item.PropertyType == typeof(DateTime))
{//时间属性初始化时未赋值会变为默认最小值
DateTime dt;
DateTime.TryParse(columnValue, out dt);
if (dt <= SqlDateTime.MinValue.Value)
continue;
}
sql.Append(" " + item.Name + ",");
sqlend.Append(" '" + columnValue + "',");
}
}
string start = sql.ToString();
start = start.Substring(0, start.Length - 1) + ")";
string end = sqlend.ToString();
end = end.Substring(0, end.Length - 1) + ")";
sqltext = start + end;
using (SqlCommand cmd = new SqlCommand(sqltext, conn))
{
n = cmd.ExecuteNonQuery();
}
}
return n;
}
数据删除方法
public static int DeleteById(object pdata)
{
int n = 0;//影响的行数
PropertyInfo[] pro = pdata.GetType().GetProperties();//获取传来的对象的属性
List<PropertyInfo> idlist = GetIdProperty(pro);//找到主键属性
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
StringBuilder sql = new StringBuilder();
StringBuilder sqlend = new StringBuilder();
string table = FindPropertyInfoValue(pdata, "TableName").ToString();
string sqltext = string.Empty;
sql.AppendFormat("delete from {0} where", table);
foreach (PropertyInfo item in idlist)
{
sql.Append(" " + item.Name + " = '" + item.GetValue(pdata, null) + "'");
}
sqltext = sql.ToString();
using (SqlCommand cmd = new SqlCommand(sqltext, conn))
{
n = cmd.ExecuteNonQuery();
}
}
return n;
}
数据修改方法
public static int Update(object obj)
{
int n = 0;
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
StringBuilder sql = new StringBuilder();
StringBuilder sqlend = new StringBuilder();
//获取对象的属性数组
PropertyInfo[] pro = obj.GetType().GetProperties();
//主键属性数组
List<PropertyInfo> idlist = GetIdProperty(pro);
//要更新的数据表
string table = FindPropertyInfoValue(obj, "TableName").ToString();
//执行的sql语句
string sqltext = string.Empty;
//UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值 and 列2=某值
sql.Append("UPDATE " + table + " set");
sqlend.Append("WHERE");
//拼接sql语句主体
foreach (PropertyInfo item in pro)
{
if (item.Name == "TableName")
{
continue;
}
else
{
sql.Append(" " + item.Name + "= '" + item.GetValue(obj, null) + "',"+"");
}
}
//根据主键增加定位条件
foreach (PropertyInfo item in idlist)
{
sqlend.Append(" " + item.Name + "= '" + item.GetValue(obj, null) + "'"+" and ");
}
string start = sql.ToString();
start = start.Substring(0, start.Length - 1) + " ";
string end = sqlend.ToString();
end = end.Substring(0, end.Length - 5) + " ";
sqltext = start + end;
using (SqlCommand cmd = new SqlCommand(sqltext, conn))
{
n = cmd.ExecuteNonQuery();
}
}
return n;
}
2.编写 一个数据对象抽象类
生成的数据表永远是动态的所以这里定义成抽象类，便于实体数据类继承和具体实现
public abstract class ObjectData
{
/// <summary>
/// 数据表名
/// </summary>
public abstract string TableName
{
get;set;
}
public virtual bool Delete()
{
if (DAL.GenratesSQLHelp.DeleteById(this) > 0)
{
return true;
}
else
{
return false;
}
}
public virtual bool Save()
{
if (DAL.GenratesSQLHelp.Save(this) > 0)
{
return true;
}
else
{
return false;
}
}
public virtual bool Update()
{
if (DAL.GenratesSQLHelp.Update(this) > 0)
{
return true;
}
else
{
return false;
}
}
}
3.定义一个数据实体类继承数据对象抽象类
public class Empinfo:BLL.ObjectData
{
[KeyFlagAttribute(true)]
public int empid { get; set; }
public string empName { get; set; }
public DateTime empBirth { get; set; }
public string empsex { get; set; }
public string emptel { get; set; }
public int deptid { get; set; }
public override bool Save()
{
return base.Save();
}
public override bool Delete()
{
return base.Delete();
}
public override bool Update()
{
return base.Update();
}
public override T Get<T>(T pobj)
{
return base.Get<T>(pobj);
}
public override string TableName
{
get;set;
}
}
4 .主函数方法内调用执行
static void Main(string[] args)
{
//增
Empinfo emp = new Empinfo();
string table = "Empinfo";
/emp.TableName = table;
emp.empid = 7;
emp.empName = "张峰";
emp.empBirth = DateTime.Now;
emp.empsex = "男";
emp.emptel = "12345678";
emp.deptid = 2;
emp.Save();
//删
Empinfo emp = new Empinfo();
string table = "Empinfo";
emp.TableName = table;
emp.empid = 7;
emp.Delete();
//改
Empinfo emp = new Empinfo();
string table = "Empinfo";
emp.TableName = table;
emp.empName = "张三丰";
emp.empsex = "男";
emp.emptel = "12345678910";
emp.deptid = 1;
emp.empBirth = Convert.ToDateTime("2004-03-05");
emp.deptid = 1;
emp.empid = 6;
emp.Update();
}
