约束
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]);
}
}
