欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

ADO.NET ORM数据库增删改查封装(工具一)

程序员文章站 2023-10-16 20:49:47
约束 public abstract class BaseModel { public int Id { get; set; } } 连接字符串 public static readonly string Customers = ConfigurationManager.ConnectionStri ......

约束

 

public abstract class basemodel
{
  public int id { get; set; }
}

 

连接字符串

public static readonly string customers = configurationmanager.connectionstrings["customers"].tostring();

通用数据库字符串

public class sqlbuilder<t> where t : basemodel
{
  public static readonly string findsql = null;
  public static readonly string deletesql = null;
  public static readonly string findallsql = null;
  public static readonly string updatesql = null;

  static sqlbuilder()
  {
    type type = typeof(t);
    findsql = $"select {string.join(",", type.getproperties().select(a => $"[{a.name}]")) } from [{type.name}] where id=@id";

    deletesql = $"delete from [{type.name}] where id=@id"; ;
    findallsql = $"select {string.join(",", type.getproperties().select(a => $"[{a.name}]")) } from [{type.name}]";
    updatesql = $"update [{type.name}] set {string.join(",", type.getproperties().where(a => !a.name.equals("id")).select(a => $"[{a.name}]=@ {a.name}"))} where id =@id";
  }
}

一:添加

public bool add<t>(t t) where t : basemodel
{
  type type = typeof(t);
  object ocompany = activator.createinstance(type);
  // id 是自动增长的,sql语句中应该去除id的字段
  // getproperties(bindingflags.public | bindingflags.instance | bindingflags.declaredonly)  过滤掉继承自父类的属性
  string props = string.join(",", type.getproperties().where(p => !p.name.equals("id")).select(a => $"[{a.name}]"));//获取属性名不等于id的所有属性数组
  string paravalues = string.join(",", type.getproperties().where(p => !p.name.equals("id")).select(a => $"@[{a.name}]"));//获取属性名不等于id的所有参数化数组
  string sql = $"insert [{type.name}] ({props}) values({paravalues})";
  var parameters = type.getproperties(bindingflags.public | bindingflags.instance | bindingflags.declaredonly).select(item => new sqlparameter()
  {
    parametername = $"@{item.name}",
    sqlvalue = $"{item.getvalue(t)}"
  });
  //在拼接sql语句的时候,尽管id 是int类型,还是建议大家使用sql语句参数化 (防止sql注入)
  using (sqlconnection connection = new sqlconnection(customers))
  {
    sqlcommand sqlcommand = new sqlcommand(sql, connection);

    sqlcommand.parameters.addrange(parameters.toarray());
    connection.open();
    return sqlcommand.executenonquery() > 0;
  }
}

二:删除

public bool delete<t>(t t) where t : basemodel
{
  type type = t.gettype();
  string sql = sqlbuilder<t>.deletesql;
  //string sql = $"delete from [{type.name}] where id=@id";
  using (sqlconnection connection = new sqlconnection(customers))
  {
    sqlcommand sqlcommand = new sqlcommand(sql, connection);
    sqlcommand.parameters.add(new sqlparameter("@id", t.id));
    connection.open();
    return sqlcommand.executenonquery() > 0;
  }
}

三:修改

public bool update<t>(t t) where t : basemodel
{
  type type = typeof(t);
  object ocompany = activator.createinstance(type);
  //string sql = $"update [{type.name}] set {string.join(",", type.getproperties().where(a => !a.name.equals("id")).select(a => $"[{a.name}]=@ {a.name}"))} where id =@id";
  string sql = sqlbuilder<t>.updatesql;
  var parameters = type.getproperties().select(item => new sqlparameter()
  {
    parametername = $"@{item.name}",
    sqlvalue = $"{item.getvalue(t)}"
  });
  //  在拼接sql语句的时候,尽管id 是int类型,还是建议大家使用sql语句参数化防止sql注入)
  using (sqlconnection connection = new sqlconnection(customers))
  {
    sqlcommand sqlcommand = new sqlcommand(sql, connection);

    sqlcommand.parameters.addrange(parameters.toarray());
    connection.open();
    return sqlcommand.executenonquery() > 0;
  }
}

四:查询

//根据id查询

public t find<t>(int id) where t : basemodel
{
  type type = typeof(t);
  object ocompany = activator.createinstance(type);

  //string sql = $"select {string.join(",", type.getproperties().select(a => $"[{a.name}]")) } from [{type.name}] where id=@id";

  string sql = sqlbuilder<t>.findsql;


  //  在拼接sql语句的时候,尽管id 是int类型,还是建议大家使用sql语句参数化 (防止sql注入)
  using (sqlconnection connection = new sqlconnection(customers))
  {
    sqlcommand sqlcommand = new sqlcommand(sql, connection);
    sqlcommand.parameters.add(new sqlparameter("@id", id));
    connection.open();
    sqldatareader reader = sqlcommand.executereader();
    if (reader.read()) 
    {
      readertolist(type, ocompany, reader);
      return (t)ocompany;
    }
    else
    {
      return null;
    }
  }
}

//查询所有

public list<t> findall<t>() where t : basemodel
{
  type type = typeof(t);
  //string sql = $"select {string.join(",", type.getproperties().select(a => $"[{a.name}]")) } from [{type.name}]";

  string sql = sqlbuilder<t>.findallsql;

  using (sqlconnection connection = new sqlconnection(customers))
  {
    sqlcommand sqlcommand = new sqlcommand(sql, connection);

    connection.open();
    sqldatareader reader = sqlcommand.executereader();
    list<t> datalist = new list<t>();
    while (reader.read()) 
    {
      object ocompany = activator.createinstance(type);
      readertolist(type, ocompany, reader);
      datalist.add((t)ocompany);
    }
  return datalist;
  }
}

//私有函数封装通用代码,引用类型可以不用返回

private static void readertolist(type type, object ocompany, sqldatareader reader)
{
  foreach (var prop in type.getproperties())
  {
    prop.setvalue(ocompany, reader[prop.name] is dbnull ? null : reader[prop.name]);
  }
}