一个简单的ORM制作(SQL帮助类)

来源：互联网发布：库里16赛季数据编辑：程序博客网时间：2024/06/05 08:33

一个简单的ORM制作大概需要以下几个类：

SQL执行类
CURD操作类
其他酱油类

先从SQL执行类说起，可能会涉及数据库的迁移等问题，所以需要定义一个接口以方便迁移到其他数据库，

ihelper

事务没提供命名，若需要命名可修改为可变参数，IHelper代码如下：

internal interface IHelper:IDisposable    {       int ExecuteQuery(string txt, IEnumerable<IDataParameter> ps, bool issp);//用于执行INSERT,UPDATE       object ExectueScalar(string tex, IEnumerable<IDataParameter> ps, bool issp);//执行COUT()       IDataReader ExectueReader(string tex, IEnumerable<IDataParameter> ps, bool issp);//执行SELECT       void BegTran();//开始事务        void RollBack();//回滚        void Commit();//提交        string CreateSql(string select, string tbname, string where, string orderby, int size, int index);//数据库的不同,产生的SQL也可能不同,所以必须提供不同的生成方案        IDataParameter Cp(string name, object value);//创建参数        string GetIdStr { get; }//获取INSERT产生ID的SQL代码,可能各个数据库不太相同        string ParStr(string name);//参数标示符,比如MSSQL:@NAME, MYSQL:?NAME,ODBC直接返回?无命名    }

以上为我个人能思考到的数据库差异和基础功能，若有其他请大家指教

通用MsSql帮助类代码如下：

class Mssql:IHelper    {        SqlCommand cmd;        SqlConnection con;        SqlTransaction tran;        internal Mssql(string constr)        {            con = new SqlConnection(constr);//test            cmd = new SqlCommand();            cmd.Connection = con;        }        void SetCmd(string str, IEnumerable<IDataParameter> ps, bool issp)        {            cmd.CommandText = str;            cmd.CommandType = issp ? CommandType.StoredProcedure : CommandType.Text;            cmd.Parameters.Clear();            foreach (var n in ps) { cmd.Parameters.Add(n); }        }        void CloseCon(){if (con.State == ConnectionState.Open) { con.Close(); }}        void OpenCon() { if (con.State == ConnectionState.Closed) { con.Open(); } }        int IHelper.ExecuteQuery(string str, IEnumerable<IDataParameter> ps, bool issp)        {            try            {                SetCmd(str, ps, issp);                OpenCon();                return cmd.ExecuteNonQuery();            }            finally{if(tran==null){CloseCon();}}        }        object IHelper.ExectueScalar(string str, IEnumerable<IDataParameter> ps, bool issp)        {            try            {                SetCmd(str, ps, issp);                OpenCon();                return cmd.ExecuteScalar();            }            finally{if(tran==null){CloseCon();}}        }        IDataReader IHelper.ExectueReader(string str, IEnumerable<IDataParameter> ps, bool issp)        {            SetCmd(str, ps, issp);            OpenCon();            return cmd.ExecuteReader(tran==null?CommandBehavior.CloseConnection:CommandBehavior.Default);        }        void IHelper.BegTran() { OpenCon(); tran = con.BeginTransaction(); cmd.Transaction = tran; }        void IHelper.Commit() { if (tran != null) { tran.Commit(); con.Close(); tran = null; } }        void IHelper.RollBack() { if (tran != null) { tran.Rollback(); con.Close(); tran = null; } }        string IHelper.CreateSql(string select, string tbname, string where, string orderby, int size, int index)        {            StringBuilder sb = new StringBuilder();//分页需要排序            sb.Append("SELECT ");            if (index == 1) { sb.Append("TOP " + size + " "); }            sb.Append(select+" FROM ");            if (index > 1){sb.Append("(SELECT ROW_NUMBER() OVER(" + (!string.IsNullOrEmpty(orderby)?"ORDER BY "+orderby:string.Empty) + ") AS ROWID," + select + " FROM ");}            sb.Append(tbname);            sb.Append(!string.IsNullOrEmpty(where) ? " WHERE " + where : string.Empty);            if (index > 1)            {                sb.Append(") AS " + tbname + " WHERE ROWID BETWEEN " + ((index - 1) * size + 1) + " AND " + size * index);            }            else            {                sb.Append(!string.IsNullOrEmpty(orderby) ? " ORDER BY " + orderby : string.Empty);            }            return sb.ToString();        }        IDataParameter IHelper.Cp(string name, object value) { return new SqlParameter(name, value); }        string IHelper.ParStr(string name) { return "@"+name;}        string IHelper.GetIdStr { get { return "SELECT @@IDENTITY"; } }        void IDisposable.Dispose()        {            CloseCon(); con.Dispose(); cmd.Dispose();        }    }

MsSql2005开始支持MARS，默认为关闭状态，也可以重写个支持MARS的Help,默认开启2个连接,一个用于查询一个用于无事物的执行,代码如下：

class MsMars:IHelper//自动开启mars,连接0为查询连接,连接1为执行无事务连接,事务连接为新连接    {        static SqlConnectionStringBuilder ssb;        static SqlConnection[] globalCon;        static object ck = new object();        public MsMars(string constr)        {            if (ssb == null)            {                ssb = new SqlConnectionStringBuilder(constr); ssb.MultipleActiveResultSets = true;            }        }        static SqlCommand GetCmd(int i = 3)        {            if (ssb == null) { throw new Exception("连接未初始化..."); }            if (i >=

) { return new SqlConnection(ssb.ConnectionString).CreateCommand(); }            if (globalCon != null) { return globalCon[i].CreateCommand(); }            lock (ck)            {                if (globalCon != null) { return globalCon[i].CreateCommand(); }                globalCon = new SqlConnection[2] { new SqlConnection(ssb.ConnectionString), new SqlConnection(ssb.ConnectionString) };                globalCon[0].Open(); globalCon[1].Open();             }            return globalCon[i].CreateCommand();        }        SqlCommand cmd;        void SetCmd(string txt, IEnumerable<IDataParameter> ps, bool issp)        {            cmd.Parameters.Clear();            cmd.CommandText = txt;            cmd.CommandType = issp ? CommandType.StoredProcedure : CommandType.Text;            cmd.Parameters.AddRange(ps.ToArray());        }        public int ExecuteQuery(string txt, IEnumerable<IDataParameter> ps, bool issp)        {            cmd = cmd ?? GetCmd(1);            SetCmd(txt, ps, issp);            return cmd.ExecuteNonQuery();        }        public object ExectueScalar(string txt, IEnumerable<IDataParameter> ps, bool issp)        {            cmd = cmd ?? GetCmd(1);            SetCmd(txt, ps, issp);            return cmd.ExecuteScalar();        }        public IDataReader ExectueReader(string txt, IEnumerable<IDataParameter> ps, bool issp)        {            cmd =cmd??GetCmd(0);            SetCmd(txt, ps, issp);            return cmd.ExecuteReader();        }        public void BegTran()        {            cmd = GetCmd();            cmd.Transaction = cmd.Connection.BeginTransaction();        }        public void RollBack()        {            cmd.Transaction.Rollback();            cmd.Connection.Close();cmd.Dispose();        }        public void Commit()        {            cmd.Transaction.Commit();            cmd.Connection.Close(); cmd.Dispose();        }        public string CreateSql(string select, string tbname, string where, string orderby, int size, int index)...        public IDataParameter Cp(string name, object value)...        public string GetIdStr...

public string ParStr(string name)...        public void Dispose()...

0 0