反射,和委托,写的曾删改查
***********后台代码*********
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ConsoleApplication1.Model;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.Configuration;
namespace ConsoleApplication1.DAL
{
public class DbHelper
{
/// <summary>
/// 根据ID查看
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="id"></param>
/// <returns></returns>
public T GetModel<T>(int id)
{
Type type = typeof(T); //获取传进来对像
string properties = "";
//获取对象的属性,
foreach (PropertyInfo item in type.GetProperties())
{
properties += "[" + item.Name + "],";
}
properties = properties.Remove(properties.Length - 1, 1); //删除多余的逗号
string propertyFirst = type.GetProperties()[0].Name;//获取第一个数据
//链接数据库,得到数据
string sqlStr = "select top 1 " + properties + " from [" + type.Name + "] where [" + propertyFirst + "]=" + id + "";
SqlDataAdapter db = new SqlDataAdapter(sqlStr, new SqlConnection(ConfigurationManager.AppSettings["conn"]));
DataSet ds = new DataSet();
db.Fill(ds);
T t = (T)Activator.CreateInstance(type);//实例化对象
foreach (PropertyInfo item in type.GetProperties())
{
//得到属性的名字
item.SetValue(t, ds.Tables[0].Rows[0][item.Name]);
}
return t;
}
//有条件的查全表
public DataSet GetList<T>(string strWhere)
{
Type type = typeof(T);
StringBuilder str = new StringBuilder();
string properties = "";
foreach (PropertyInfo item in type.GetProperties())
{
properties += "[" + item.Name + "],";
}
properties = properties.Remove(properties.Length - 1, 1);//删除最后多余的逗号
str.Append("select " + properties + " ");
str.Append(" FROM [" + type.Name + "] ");
if (strWhere.Trim() != "")
{
str.Append(" where " + strWhere);
}
SqlDataAdapter sda = new SqlDataAdapter(str.ToString(), new SqlConnection(ConfigurationManager.AppSettings["conn"]));
DataSet ds = new DataSet();
sda.Fill(ds);
return ds;
}
/// <summary>
/// 增加一条数据
/// </summary>
///
public int ADD<T>(T t)
{
Type type = typeof(T); //对象
Type tp = t.GetType();
string val = "";
foreach (PropertyInfo item in tp.GetProperties())
{
val += "'" + tp.GetProperty(item.Name).GetValue(t, null) + "',";
}
val = val.Remove(val.Length - 1, 1);
val = val.Remove(1, 4);
string sqlStr = "insert into " + type.Name + " values(" + val + ");select @@IDENTITY";
SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["conn"]);
SqlCommand cmd = new SqlCommand(sqlStr, conn);
conn.Open();
int flag = Convert.ToInt32(cmd.ExecuteScalar());
conn.Close();
return flag;
}
/// <summary>
/// 修改数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="t"></param>
/// <returns></returns>
public bool Update<T>(T t, int id)
{
StringBuilder strSql = new StringBuilder();
Type type = typeof(T);
strSql.Append("update [" + type.Name + "] set ");
for (int i = 1; i < type.GetProperties().Length; i++)
{
strSql.Append("" + type.GetProperties()[i].Name + "='" + type.GetProperties()[i].GetValue(t) + "',");
}
strSql = strSql.Remove(strSql.Length - 1, 1);
string PropertiesFirst = type.GetProperties()[0].Name;
int PropertiesFirstValue = (int)type.GetProperties()[0].GetValue(t);
strSql.Append(" where " + PropertiesFirst + "=" + id + "");
SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["conn"]);
conn.Open();
SqlCommand cmd = new SqlCommand(strSql.ToString(), conn);
int rows = cmd.ExecuteNonQuery();
conn.Close();
if (rows > 0)
{
return true;
}
else
{
return false;
}
}
/// <summary>
///修改数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="t"></param>
/// <returns></returns>
public bool UpdateData<T>(T t)
{
Type type = typeof(T);
Type t1 = t.GetType();
//获取表中的字段(属性)
string shuxing = "";
foreach (PropertyInfo item in type.GetProperties())
{ shuxing += "" + item.Name + ","; }
shuxing = shuxing.Remove(shuxing.Length - 1, 1);
string [] s = shuxing.Split(',');
//获取t中的值
string val = "";
foreach (PropertyInfo i in t1.GetProperties())
{
val += "'" + t1.GetProperty(i.Name).GetValue(t, null) + "',";
}
val = val.Remove(val.Length - 1, 1);
string [] v = val.Split(',');
//拼接字段
string s1 = "";
for (int i = 1; i < s.Length; i++) { s1 += "" + s[i] + " =" + v[i] + ","; }
s1 = s1.Remove(s1.Length - 1, 1);
StringBuilder str = new StringBuilder();
str.Append("update ["+type.Name+"] set ");
str.Append(""+s1+"");
str.Append(" where "+s[0]+" = "+v[0]+"");
SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["conn"]);
SqlCommand cmd = new SqlCommand(str.ToString(), conn);
conn.Open();
int flag = Convert.ToInt32(cmd.ExecuteNonQuery());
conn.Close();
if (flag > 0) { return true; } else { return false; }
}
/// <summary>
/// 删除
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="ID"></param>
/// <returns></returns
public int Delete<T>(int id)
{
Type type = typeof(T);
string ID = type.GetProperties()[0].Name;
string sql = "delete from " + type.Name + " where " + ID + " = " + id + "";
SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["conn"]);
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
int flag = cmd.ExecuteNonQuery();
conn.Close();
return flag;
}
}
}
********************控制台***************
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ConsoleApplication1.DAL;
using ConsoleApplication1.Model;
using System.Data;
using System.Data.SqlClient;
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
DbHelper db = new DbHelper();
//有条件的查询
// CiemS model = db.GetModel<CiemS>(2);
//查询所有数据
// DataSet ty = db.GetList<CiemS>("");
//添加
//CiemS model = new CiemS();
//model.Ctime = System.DateTime.Now;
//model.CNamw = "张三";
//int id = db.ADD<CiemS>(model);
//删除
// int b = db.Delete<CiemS>(5);
//修改
//CiemS model = new CiemS();
//model.Ctime = System.DateTime.Now;
//bool m=db.Update<CiemS>(model,6);
CiemS n=db.GetModel<CiemS>(3);
n.CNamw = "789";
bool c=db.UpdateData<CiemS>(n);
Console.ReadKey();
}
}
}