支持多类型数据库的c#数据库模型示例
dataaccess.cs
using system;
using system.collections.generic;
using system.text;
namespace dynamicframework
{
public abstract class dataaccess : marshalbyrefobject
{
protected system.data.common.dbconnection connection;
protected string cnnstr = "";
protected dataaccess()
{
}
public static string connpath = system.windows.forms.application.startuppath + "\\localdb.mdb";
public static dataaccess localdb
{
get
{
return new oleaccess("provider=microsoft.jet.oledb.4.0;data source=" + connpath);
//return new sqlclientaccess("server=localhost;trusted_connection=true;database=restaurantdb");
}
}
public static dataaccess serverdb
{
get
{
//return new oleaccess("provider=microsoft.jet.oledb.4.0;data source=" + connpath);
//if (configs.localconfig.instanct.islocalserver)
//{
//}
//trusted_connection=true;
//return new sqlclientaccess("server=.;database=restaurantdb,uid = sa,pwd =");
return new sqlclientaccess("data source=.;initial catalog=restaurantdb;persist security info=true;user id=sa");
}
}
private system.data.common.dbcommand getcommand(string sql, dictionary<string, object> parameters)
{
system.data.common.dbcommand cmd = connection.createcommand();
cmd.commandtext = sql;
if (parameters != null)
{
foreach (keyvaluepair<string, object> item in parameters)
{
system.data.common.dbparameter parameter = cmd.createparameter();
parameter.parametername = item.key;
parameter.value = item.value;
cmd.parameters.add(parameter);
}
}
return cmd;
}
#region dataaccess command
public int excutecommand(string sql,dictionary<string,object> parameters)
{
using (connection)
{
connection.connectionstring = cnnstr;
connection.open();
return getcommand(sql, parameters).executenonquery();
}
}
public object executescalar(string sql, dictionary<string, object> parameters)
{
using (connection)
{
connection.connectionstring = cnnstr;
connection.open();
return getcommand(sql, parameters).executescalar();
}
}
public object executereader(string sql, dictionary<string, object> parameters)
{
using (connection)
{
connection.connectionstring = cnnstr;
connection.open();
return getcommand(sql, parameters).executereader();
}
}
public system.data.datatable executedatatable(string sql)
{
return executedatatable(sql, null);
}
public system.data.datatable executedatatable(string sql, dictionary<string, object> parameters)
{
using (connection)
{
connection.connectionstring = cnnstr;
connection.open();
return dbhelper.totable(getcommand(sql, parameters).executereader());
}
}
public list<t> excutelist<t>(string sql, dictionary<string, object> parameters)
{
using (connection)
{
connection.connectionstring = cnnstr;
connection.open();
return dbhelper.tolist<t>(getcommand(sql, parameters).executereader());
}
}
public t getentity<t>(string sql, dictionary<string, object> parameters)
{
using (connection)
{
connection.connectionstring = cnnstr;
connection.open();
return dbhelper.toentity<t>(getcommand(sql, parameters).executereader());
}
}
public list<t> excutelist<t>()
{
using (connection)
{
connection.connectionstring = cnnstr;
connection.open();
return dbhelper.tolist<t>(getcommand(string.format("select * from {0}", typeof(t).name), null).executereader());
}
}
public system.data.datatable filldatatable(string sql)
{
return filldatatable(sql, null);
}
public system.data.datatable filldatatable(string sql, dictionary<string, object> parameters)
{
system.data.datatable dt = new system.data.datatable();
fill(dt, getcommand(sql, parameters));
return dt;
}
public int fill(system.data.datatable dt, system.data.common.dbcommand cmd)
{
using (connection)
{
connection.connectionstring = cnnstr;
connection.open();
system.data.common.dbdataadapter adapter = createadapter();
adapter.selectcommand = cmd;
return adapter.fill(dt);
}
}
public int savedatatable(system.data.datatable dt)
{
return savedatatable(dt, dt.tablename);
}
public int savedatatable(system.data.datatable dt, string tablename)
{
return savetable(dt, "select * from " + tablename + " where 1 = 2");
}
public int savetable(system.data.datatable dt, string sql)
{
using (connection)
{
connection.connectionstring = cnnstr;
connection.open();
system.data.common.dbdataadapter adapter = createadapter();
adapter.selectcommand = getcommand(sql, null);
system.data.common.dbcommandbuilder cmdbuild = createcommandbuilder();
cmdbuild.dataadapter = adapter;
cmdbuild.quoteprefix = "[";
cmdbuild.quotesuffix = "]";
return adapter.update(dt);
}
}
public int savedataset(system.data.dataset ds)
{
using (connection)
{
connection.connectionstring = cnnstr;
connection.open();
int updates = 0;
foreach (system.data.datatable item in ds.tables)
{
updates += savedatatable(item);
}
return updates;
}
}
#endregion
internal virtual system.data.common.dbdataadapter createadapter()
{
throw new system.applicationexception("dbdataadapter can not created!");
}
public virtual system.data.common.dbcommandbuilder createcommandbuilder()
{
throw new system.applicationexception("dbcommandbuilder can not created!");
}
}
}
dbhelper.cs
using system;
using system.collections.generic;
using system.text;
namespace dynamicframework
{
public sealed class dbhelper
{
public static list<t> tolist<t>(system.data.idatareader reader)
{
list<t> list = new list<t>();
csla.data.safedatareader sr = new csla.data.safedatareader(reader);
while (sr.read())
{
t t = activator.createinstance<t>();
type entitytype = t.gettype();
for (int i = 0; i < sr.fieldcount; i++)
{
string pname = reader.getname(i);
system.reflection.propertyinfo p = entitytype.getproperty(pname);
if (p != null)
{
p.setvalue(t, getvalue(p,sr,i), null);
}
}
list.add(t);
}
return list;
}
private static object getvalue(system.reflection.propertyinfo p,csla.data.safedatareader sr,int index)
{
if (p.propertytype == typeof(string))
{
return sr.getstring(index);
}
else if (p.propertytype == typeof(int))
{
return sr.getint32(index);
}
else if (p.propertytype == typeof(decimal))
{
return sr.getdecimal(index);
}
else if (p.propertytype == typeof(datetime))
{
return sr.getdatetime(index);
}
else if (p.propertytype == typeof(bool))
{
return sr.getboolean(index);
}
else if (p.propertytype == typeof(double))
{
return sr.getdouble(index);
}
else
{
return sr.getvalue(index);
}
}
public static t toentity<t>(system.data.idatareader reader)
{
csla.data.safedatareader sr = new csla.data.safedatareader(reader);
while (sr.read())
{
t t = activator.createinstance<t>();
type entitytype = t.gettype();
for (int i = 0; i < sr.fieldcount; i++)
{
string pname = reader.getname(i);
system.reflection.propertyinfo p = entitytype.getproperty(pname);
if (p != null)
{
p.setvalue(t, getvalue(p, sr, i), null);
}
}
return t;
}
return default(t);
}
public static list<t> tabletolist<t>(system.data.datatable dt)
{
return tolist<t>(dt.createdatareader());
}
public static system.data.datatable listtotable<t>(ilist<t> list)
{
if (list == null) return null;
system.data.datatable dt = new system.data.datatable(typeof(t).name);
system.reflection.propertyinfo[] props = typeof(t).getproperties();
if (props.length >= 0)
{
for (int column = 0; column < props.length; column++)
{
dt.columns.add(props[column].name, props[column].propertytype);
}
}
foreach (t item in list)
{
system.data.datarow dr = dt.newrow();
foreach (system.data.datacolumn column in dt.columns)
{
dr[column] = item.gettype().getproperty(column.columnname).getvalue(item, null);
}
dt.rows.add(dr);
}
//dt.acceptchanges();
return dt;
}
public static system.data.datatable totable(system.data.idatareader reader)
{
system.data.datatable dt = new system.data.datatable();
dt.load(reader);
return dt;
}
public static void saveentity<t>(t obj)
{
string tb = obj.gettype().name;
string sql = "insert into {0}({1})values({2})";
string fles = "";
string sparam = "";
dictionary<string, object> dicparams = new dictionary<string, object>();
foreach (system.reflection.propertyinfo var in obj.gettype().getproperties())
{
fles += var.name + ",";
sparam += "@" + var.name + ",";
dicparams.add("@" + var.name,var.getvalue(obj, null));
}
sql = string.format(sql, tb, fles.remove(fles.length - 1), sparam.remove(sparam.length - 1));
dataaccess.serverdb.executescalar(sql, dicparams);
}
public static void savelocalentity<t>(t obj)
{
string tb = obj.gettype().name;
string sql = "insert into {0}({1})values({2})";
string fles = "";
string sparam = "";
dictionary<string, object> dicparams = new dictionary<string, object>();
foreach (system.reflection.propertyinfo var in obj.gettype().getproperties())
{
fles += var.name + ",";
sparam += "@" + var.name + ",";
dicparams.add("@" + var.name, var.getvalue(obj, null));
}
sql = string.format(sql, tb, fles.remove(fles.length - 1), sparam.remove(sparam.length - 1));
dataaccess.localdb.executescalar(sql, dicparams);
}
}
#region dataasss == oledb - sqlclient - sqlite
public class oleaccess : dataaccess
{
public oleaccess()
{
connection = new system.data.oledb.oledbconnection();
}
public oleaccess(string connectionstring)
{
connection = new system.data.oledb.oledbconnection(connectionstring);
cnnstr = connectionstring;
}
internal override system.data.common.dbdataadapter createadapter()
{
return new system.data.oledb.oledbdataadapter();
}
public override system.data.common.dbcommandbuilder createcommandbuilder()
{
return new system.data.oledb.oledbcommandbuilder();
}
}
public class sqlclientaccess : dataaccess
{
public sqlclientaccess()
{
connection = new system.data.sqlclient.sqlconnection();
}
public sqlclientaccess(string connectionstring)
{
connection = new system.data.sqlclient.sqlconnection(connectionstring);
cnnstr = connectionstring;
}
internal override system.data.common.dbdataadapter createadapter()
{
return new system.data.sqlclient.sqldataadapter();
}
public override system.data.common.dbcommandbuilder createcommandbuilder()
{
return new system.data.sqlclient.sqlcommandbuilder();
}
}
public class sqliteaccess : dataaccess
{
public sqliteaccess()
{
connection = new system.data.sqlite.sqliteconnection();
}
public sqliteaccess(string connectionstring)
{
connection = new system.data.sqlite.sqliteconnection(connectionstring);
cnnstr = connectionstring;
}
internal override system.data.common.dbdataadapter createadapter()
{
return new system.data.sqlite.sqlitedataadapter();
}
public override system.data.common.dbcommandbuilder createcommandbuilder()
{
return new system.data.sqlite.sqlitecommandbuilder();
}
}
#endregion
}