一个简单的ORM制作(SQL帮助类)_.NET_编程开发_程序员俱乐部

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

1. SQL执行类
2. CURD操作类
3. 其他酱油类

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

事务没提供命名，若需要命名可修改为可变参数，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()...

以上代码本人测试通过，Help类只是ORM的一个起步，曾经看过EF的代码想学习下，但微软的代码实在是太高深(恶心),无从下手，所以我还是按照个人的做法来实现自己ORM,，下篇继续讲CURD操作类．读过觉得还不错请留下你的脚印，谢谢