支持多类型数据库的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
}