vs 2015 轻量级orm框架（二）付源码及测试用例

dy2017

于 2019-06-19 15:35:21 发布

阅读量185

点赞数

文章标签：数据库orm框架反射反射发出 Emit

本文链接：https://blog.csdn.net/dy2017/article/details/92833191

版权

接上一篇：vs 2015 轻量级orm框架（一）付源码及测试用例
 代码下载代码下载竟然要C币，这真不赖我，所有代码都贴了，自己整理整理也能用

工具类：

namespace Utility
{
    public class DBTool
    {
        /// <summary>
        /// 返回默认是oracle数据库前缀，数据库类型不区分大小写
        /// </summary>
        /// <param name="strDBType">数据库类型</param>
        /// <returns></returns>
        public static string GetParamPrefix(string strDBType = "oracle")
        {
            if (string.Compare(strDBType, "oracle", true) == 0)
                return ":";
            else if (string.Compare(strDBType, "sqlserver", true) == 0)
                return "@";
            else if (string.Compare(strDBType, "sqlite", true) == 0)
                return "";
            else if (string.Compare(strDBType, "mysql", true) == 0)
                return "";
            else return ":";
        }
}

生成sql抽象类，可扩展其他数据库类型做准备

using System;
using System.Collections.Generic;
using System.Reflection;
using System.Reflection.Emit;
using System.Text;
using DBExtensions;

namespace Utility
{
    public abstract class Entity2Sql<T> where T : class, new()
    {
        protected EntityDeital ed;
        protected List<EntityPropInfo> leps;
        protected Func<T, object[]> EmitDelegate;
        protected string DBType = "";

        #region 生成select类型语句
        /// <summary>
        /// 调用此函数前,先调用InitializeMin()
        /// </summary>
        /// <param name="strCondition">sql查询条件</param>
        /// <returns></returns>
        public string CreateSelSql(string strCondition)
        {
            return "select * from " + ed.entityName + " where 1=1" + getCondition(strCondition);
        }

        public sqlParameter CreateSelSqlByParam(string strCondition, Dictionary<string, object> dict)
        {
            return new sqlParameter(CreateSelSql(strCondition), dict, null);
        }

        /// <summary>
        /// 调用此函数前,先调用InitializeMin()
        /// </summary>
        /// <param name="strFields">sql查询字段</param>
        /// <param name="strCondition">sql查询条件</param>
        /// <returns></returns>
        public string CreateSelSql(string strFields, string strCondition)
        {
            if (string.IsNullOrEmpty(strFields))
                throw new Exception("Lack of table fields");

            return "select " + strFields + " from " + ed.entityName + " where 1=1" + getCondition(strCondition);
        }

        public sqlParameter CreateSelSqlByParam(string strFields, string strCondition, Dictionary<string, object> dict)
        {
            return new sqlParameter(CreateSelSql(strFields, strCondition), dict, null);
        }
        #endregion

        #region
        /// <summary>
        /// 调用此函数前,先调用InitializeMin()
        /// </summary>
        /// <param name="strCondition">sql查询条件</param>
        /// <returns></returns>
        public string CreateExistSql(string strCondition)
        {
            return "select count(1) from " + ed.entityName + " where 1=1" + getCondition(strCondition);
        }

        public sqlParameter CreateExistSqlByParam(string strCondition, Dictionary<string, object> dict)
        {
            return new sqlParameter(CreateExistSql(strCondition), dict, null);
        }

        public string CreateDelSql(string strCondition)
        {
            return "delete from " + ed.entityName + " where 1=1" + getCondition(strCondition);
        }

        public sqlParameter CreateDelSqlByParam(string strCondition, Dictionary<string, object> dict)
        {
            return new sqlParameter(CreateDelSql(strCondition), dict, null);
        }
        #endregion

        protected string getCondition(string strCondition)
        {
            if (!string.IsNullOrEmpty(strCondition))
                strCondition = " and " + strCondition;

            return strCondition;
        }


        #region 生成带数据库参数的sql语句
        public abstract bool CreateInsertSqlByParam(T type, out sqlParameter sp); 

        public abstract bool CreateUpdateSqlByParam(T type, string strCondition, Dictionary<string, object> ps, List<DBTypeExt> paramTypes, out sqlParameter sp);
        #endregion

        #region 生成inert、update类型语句

        public string CreateInsertSql(T type)
        {
            StringBuilder sbField = new StringBuilder(256);
            StringBuilder sbVal = new StringBuilder(256);

            int nIndex = 0;
            foreach (var item in EmitDelegate(type))
            {
                if (null != item)
                {
                    sbVal.Append(valFormat(item, nIndex) + ",");
                    sbField.Append(leps[nIndex].PropertyName + ",");
                }
                nIndex++;
            }
            return "insert into " + ed.entityName + " (" + sbField.ToString().TrimEnd(',') + ")" + " values(" + sbVal.ToString().TrimEnd(',') + ")";
        }
        
        public string CreateUpdateSql(T type, string strCondition)
        {
            StringBuilder sbVal = new StringBuilder(256);
            int nIndex = 0;
            foreach (var item in EmitDelegate(type))
            {
                if (item != null)
                {
                    sbVal.Append(" " + leps[nIndex].PropertyName + " = " + valFormat(item, nIndex) + ",");
                }
                nIndex++;
            }
            return "update " + ed.entityName + " set" + sbVal.ToString().TrimEnd(',') + " where " + strCondition;
        }
        #endregion

        protected virtual string valFormat(object v, int nIndex)
        {
            Type type = leps[nIndex].Type;
            string strVal = string.Empty;
            if (typeof(string) == type || typeof(DateTime?) == type || typeof(DateTime) == type)
            {
                strVal = "'" + v + "'";
            }
            else
            {
                strVal = v.ToString();
            }
            return strVal;
        }
        #region 实体结构初始化
        /// <summary>
        /// 没有表字段的初始化
        /// </summary>
        public void InitializeMin()
        {
            SetEntityInfo();
        }

        /// <summary>
        /// 完全初始化
        /// </summary>
        public void InitializeAll()
        {
            SetEntityInfo();
            GetProperties();
        }

        private void GetProperties()
        {
            if (leps == null)
                leps = new List<EntityPropInfo>();
            leps.Clear();

            foreach (var prop in typeof(T).GetProperties(BindingFlags.Instance | BindingFlags.Public))
            {
                if (prop.GetIndexParameters().Length > 0)
                    continue;
                var getMethod = prop.GetGetMethod(false);
                if (getMethod == null)
                    continue;

                var attr = Attribute.GetCustomAttribute(prop, typeof(DBDetailAttribute), true) as DBDetailAttribute;
                bool isColb = false;
                bool isGeom = false;
                if (attr != null)
                {
                    isColb = attr.ClobField;
                    isGeom = attr.GeomField;
                }

                EntityPropInfo epi = new EntityPropInfo(prop, isColb, isGeom);
                leps.Add(epi);
            }
            EmitDelegate = EmitGetter(leps);
        }

        private void SetEntityInfo()
        {
            var attr = Attribute.GetCustomAttribute(typeof(T), typeof(DBEntityAttribute), true) as DBEntityAttribute;
            if (attr == null)
                throw new Exception("Undefined 'DBEntity'attribute");

            ed = new EntityDeital(attr.EntityName, attr.EntityPK, attr.haveLargeField, attr.ParamTypes);
        }

        private Func<T, object[]> EmitGetter(List<EntityPropInfo> lepis)
        {
            Type type = typeof(T);
            var dm = new DynamicMethod("getValue", typeof(object[]), new Type[] { type }, true);
            ILGenerator il = dm.GetILGenerator();

            LocalBuilder list = il.DeclareLocal(typeof(object[]));
            il.Emit(OpCodes.Ldc_I4, lepis.Count);
            il.Emit(OpCodes.Newarr, typeof(object));
            il.Emit(OpCodes.Stloc_S, list);

            for (int i = 0; i < lepis.Count; ++i)
            {
                il.Emit(OpCodes.Ldloc, list);
                il.Emit(OpCodes.Ldc_I4, i);
                il.Emit(OpCodes.Ldarg_0);
                il.Emit(OpCodes.Call, lepis[i].GetMethod);
                if (lepis[i].Type.IsValueType)
                {
                    il.Emit(OpCodes.Box, lepis[i].Type);
                }
                il.Emit(OpCodes.Stelem_Ref);
            }

            il.Emit(OpCodes.Ldloc_S, list);
            il.Emit(OpCodes.Ret);
            return dm.CreateDelegate(typeof(Func<T, object[]>)) as Func<T, object[]>;
        }
        #endregion

        protected struct EntityDeital
        {
            public readonly string entityName;
            public readonly string entityPK;
            public readonly bool haveLargeField;
            public readonly DBTypeExt[] paramTypes;

            public EntityDeital(string name, string pk, bool bl, DBTypeExt[] ParamTypes)
            {
                entityName = name;
                entityPK = pk;
                haveLargeField = bl;
                paramTypes = ParamTypes;
            }
        }

        protected struct EntityPropInfo
        {
            public readonly string PropertyName;
            public readonly Type Type;
            public readonly MethodInfo GetMethod;
            public readonly bool IsClob;
            public readonly bool IsGeom;

            public EntityPropInfo(PropertyInfo prop, bool isColb, bool isGeom)
            {
                PropertyName = prop.Name;
                Type = prop.PropertyType;
                GetMethod = prop.GetGetMethod(false);
                IsClob = isColb;
                IsGeom = isGeom;
            }
        }
    }
}

生成oracle数据库sql语句子类

using System;
using System.Text;
using System.Collections.Generic;
using DBExtensions;


namespace Utility
{
    /// <summary>
    /// 用于生成insert、update语句，附带生成select语句(意义不大)，目前不支持表数据复制
    /// </summary>
    public class Entity2SqlForOrcl<T>:Entity2Sql<T> where T : class, new()
    {
        public Entity2SqlForOrcl()
        {
            DBType = "oracle";
        }

        public override bool CreateInsertSqlByParam(T type, out sqlParameter sp)
        {
            string sql = string.Empty;
            Dictionary<string, object> dict = new Dictionary<string, object>();
            StringBuilder sbFields = new StringBuilder(256);
            StringBuilder sbVals = new StringBuilder(256);

            string strPrefix = DBTool.GetParamPrefix(DBType);
            List<DBTypeExt> types = new List<DBTypeExt>();

            int nI = 0;
            foreach (var item in EmitDelegate(type))
            {
                if (null != item)
                {
                    string strVal = strPrefix + "v_" + leps[nI].PropertyName;
                    types.Add(ed.paramTypes[nI]);

                    sbFields.Append(leps[nI].PropertyName + ",");
                    dict.Add(strVal, item);

                    if (leps[nI].IsGeom)
                    {
                        sbVals.Append("SDO_GEOMETRY( " + strVal + "),");
                    }
                    else
                    {
                        sbVals.Append(strVal + ",");
                    }
                }
                nI++;
            }

            sp = new sqlParameter();
            if (dict.Count > 0)
            {
                sp.sql = "insert into " + ed.entityName + " (" + sbFields.ToString().TrimEnd(',') + ") values(" + sbVals.ToString().TrimEnd(',') + ")";
                sp.dictParams = dict;
                sp.paramTypes = types;
                return true;
            }
            return false;
        }

        public override bool CreateUpdateSqlByParam(T type, string strCondition, Dictionary<string, object> ps, List<DBTypeExt> paramTypes, out sqlParameter sp)
        {
            sp = new sqlParameter();
            if (ps.Count > 0)
            {
                int nI = 0;
                string strPrefix = DBTool.GetParamPrefix(DBType);
                StringBuilder sbVals = new StringBuilder(256);
                Dictionary<string, object> dict = new Dictionary<string, object>();
                List<DBTypeExt> types = new List<DBTypeExt>();
                foreach (var item in EmitDelegate(type))
                {
                    if (null != item)
                    {
                        string strVal = strPrefix + "v_" + leps[nI].PropertyName;
                        if (leps[nI].IsGeom)
                        {
                            sbVals.Append(leps[nI].PropertyName + " = SDO_GEOMETRY( " + strVal + "),");
                        }
                        else
                        {
                            sbVals.Append(leps[nI].PropertyName + " = " + strVal + ",");
                        }
                        dict.Add(strVal, item);
                        types.Add(ed.paramTypes[nI]);
                    }
                    nI++;
                }

                if (dict.Count > 0)
                {
                    types.AddRange(paramTypes);
                    foreach (var item in ps)
                    {
                        dict.Add(item.Key, item.Value);
                    }
                    sp.sql = "update " + ed.entityName + " set " + sbVals.ToString().TrimEnd(',') + " where " + strCondition;
                    sp.dictParams = dict;
                    sp.paramTypes = types;
                    return true;
                }
            }
            return false;
        }


        protected override string valFormat(object v, int nIndex)
        {
            Type type = leps[nIndex].Type;
            string strVal = string.Empty;
            if (leps[nIndex].IsGeom)
                strVal = "SDO_GEOMETRY('" + v + "')";
            else if (typeof(DateTime?) == type || typeof(DateTime) == type)
                strVal = "TO_DATE('" + v + "', 'YYYY-MM-DD HH24:MI:SS')";
            else if (typeof(string) == type)
                strVal = "'" + v + "'";
            else
                strVal = v.ToString();
            return strVal;
        }

        ///WKT格式说明
        //POINT(6 10)
        //LINESTRING(3 4,10 50,20 25)
        //POLYGON((1 1,5 1,5 5,1 5,1 1),(2 2,2 3,3 3,3 2,2 2)) 
        //MULTIPOINT(3.5 5.6, 4.8 10.5)
        //MULTILINESTRING((3 4,10 50,20 25),(-5 -8,-10 -8,-15 -4)) 
        //MULTIPOLYGON(((1 1,5 1,5 5,1 5,1 1),(2 2,2 3,3 3,3 2,2 2)),((6 3,9 2,9 4,6 3))) 
        //GEOMETRYCOLLECTION(POINT(4 6),LINESTRING(4 6,7 10)) 
        //POINT ZM(1 1 5 60)
        //POINT M(1 1 80)
        //POINT EMPTY
        //MULTIPOLYGON EMPTY
    }
}

测试用例
数据库建表脚本

CREATE TABLE TAB_TEST 
(
  COLUMN1 VARCHAR2(50 BYTE) NOT NULL 
, COLUMN2 DATE 
, COLUMN3 NUMBER(*, 0) NOT NULL 
, COLUMN4 FLOAT(126) 
, COLUMN5 CLOB 
, COLUMN6 MDSYS.SDO_GEOMETRY 
, COLUMN7 LONG 
, COLUMN8 VARCHAR2(20 BYTE) 
, COLUMN9 VARCHAR2(20 BYTE) 
, COLUMN10 VARCHAR2(20 BYTE) 
, COLUMN11 VARCHAR2(20 BYTE) 
, COLUMN12 VARCHAR2(20 BYTE) 
, COLUMN13 VARCHAR2(20 BYTE) 
, COLUMN14 VARCHAR2(20 BYTE) 
, COLUMN15 VARCHAR2(20 BYTE) 
, COLUMN16 VARCHAR2(20 BYTE) 
, COLUMN17 VARCHAR2(20 BYTE) 
, COLUMN18 VARCHAR2(20 BYTE) 
, COLUMN19 VARCHAR2(20 BYTE) 
, COLUMN20 VARCHAR2(20 BYTE) 
, CONSTRAINT TAB_TEST_PK PRIMARY KEY 
  (
    COLUMN1 
  , COLUMN3 
  )）

web测试页面
前端：

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="WebDemo.Default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <h1>ORM测试用例</h1>
        <div>
            <label id="lb_il" runat="server"></label><br />
            <label id="lb_ilp" runat="server"></label><br />
            <label id="lb_select" runat="server"></label><br />
            <label id="lb_c" runat="server"></label><br />
            <label id="lb_cu" runat="server"></label><br/>
            <label id="Lb_e" runat="server"></label><br />
            <label id="lb_t" runat="server"></label><br />
            <label id="lb_msg" runat="server"></label><br />
        </div>
    </form>
</body>
</html>

后台：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Text;
using BLL.Model_Reflect;
using Utility;
using DAL;
using DBExtensions;
using System.Diagnostics;

namespace WebDemo
{
    public partial class Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            bool bR = false;
            try
            {
                int nCount = 100000;
                testIL(nCount);
                testILp();
                testilupdatep();
                testCreateSel();
                testC(nCount);
                testUpdate();
                testsel();
                test_Transaction();
                bR = true;
            }
            catch (Exception ex)
            {
                lb_msg.InnerText = ex.Message;
            }
            if (bR)
                lb_msg.InnerText = "完成测试";
        }

        private void test_Transaction()
        {
            StringBuilder sb = new StringBuilder(1000000);
            for (int i = 0; i < 100000; ++i)
            {
                sb.Append("abcdefghij");
            }

            List<sqlParameter> sps = new List<sqlParameter>();
            Entity2SqlForOrcl<TM_TAB_TEST> es = new Entity2SqlForOrcl<TM_TAB_TEST>();
            es.InitializeAll();

            for (int i = 0; i < 2; ++i)
            {
                TM_TAB_TEST tm = new TM_TAB_TEST();
                tm.COLUMN1 = Guid.NewGuid().ToString();
                tm.COLUMN3 = 11;
                tm.COLUMN5 = sb.ToString();//DBTool.ToClob(str);
                tm.COLUMN6 = "MULTIPOLYGON(((1 1,5 1,5 5,1 5,1 1),(2 2,2 3,3 3,3 2,2 2)),((6 3,9 2,9 4,6 3)))";
                sqlParameter sp;
                if (es.CreateInsertSqlByParam(tm, out sp))
                {
                    sps.Add(sp);
                }
            }

            int nErrI = 0;
            string strErr = string.Empty;
            var da = new OracleDataAccess();
            bool bR = da.DB_Transaction(sps, ref nErrI, ref strErr);

            if(bR)
            {
                lb_t.InnerText = "带参数sql语句，以提交事务的方式执行成功";
            }
            else
            {
                lb_t.InnerText = "带参数sql语句，以提交事务的方式执行失败：" + strErr;
            }
        }

        private void testsel()
        {
            Entity2SqlForOrcl<TM_TAB_TEST> es = new Entity2SqlForOrcl<TM_TAB_TEST>();
            es.InitializeMin();

            string s = es.CreateSelSql("COLUMN1,COLUMN2", "COLUMN1='0'");
            string s3 = es.CreateExistSql("COLUMN1='0'");

            var da = new OracleDataAccess();
            da.Query<TM_TAB_TEST>(s);
            da.Query<TM_TAB_TEST>(s3);

            Lb_e.InnerText = "exit sql success";
        }

        private void testUpdate()
        {
            Entity2SqlForOrcl<TM_TAB_TEST> es = new Entity2SqlForOrcl<TM_TAB_TEST>();
            es.InitializeAll();

            TM_TAB_TEST tm = new TM_TAB_TEST();
            tm.COLUMN18 = "90";

            string s = es.CreateUpdateSql(tm, "COLUMN1='0'");
            var da = new OracleDataAccess();
            da.ExecuteNonQuery(s);

            lb_cu.InnerText = "测试生成update sql 成功";
        }

        private void testIL(int nCount)
        {
            List<string> lsqls = new List<string>();
            List<TM_TAB_TEST> lm = new List<TM_TAB_TEST>();
            Entity2SqlForOrcl<TM_TAB_TEST> es = new Entity2SqlForOrcl<TM_TAB_TEST>();
            es.InitializeAll();

            Stopwatch watch = Stopwatch.StartNew();
            watch.Start();
            for (int i = 0; i < nCount; ++i)
            {
                TM_TAB_TEST tm = new TM_TAB_TEST();
                tm.COLUMN1 = i.ToString();
                tm.COLUMN2 = DateTime.Now;
                tm.COLUMN3 = i;
                tm.COLUMN4 = i * 0.1f;
                tm.COLUMN5 = "aa";
                tm.COLUMN7 = i;
                tm.COLUMN8 = i.ToString();
                tm.COLUMN9 = i.ToString();
                tm.COLUMN10 = i.ToString();
                tm.COLUMN11 = i.ToString();
                tm.COLUMN12 = i.ToString();
                tm.COLUMN13 = i.ToString();
                tm.COLUMN14 = i.ToString();
                tm.COLUMN15 = i.ToString();
                tm.COLUMN16 = i.ToString();
                tm.COLUMN17 = i.ToString();
                tm.COLUMN18 = i.ToString();
                tm.COLUMN19 = i.ToString();
                tm.COLUMN20 = i.ToString();
                lm.Add(tm);
                string strSql = es.CreateInsertSql(tm);
                lsqls.Add(strSql);
            }

            watch.Stop();
            string str1 = watch.Elapsed.ToString();
            lb_il.InnerText = "entity2sql create sql 共计：" + lsqls.Count + "条，时间：" + str1;
        }

        private void testILp()
        {
            List<string> lsqls = new List<string>();
            Entity2SqlForOrcl<TM_TAB_TEST> es = new Entity2SqlForOrcl<TM_TAB_TEST>();
            es.InitializeAll();

            int i = 105;
            TM_TAB_TEST tm = new TM_TAB_TEST();
            tm.COLUMN1 = i.ToString();
            tm.COLUMN2 = DateTime.Now;
            tm.COLUMN3 = i;
            tm.COLUMN4 = i * 0.1f;
            tm.COLUMN5 = "aa";
            tm.COLUMN7 = i;
            tm.COLUMN8 = i.ToString();
            tm.COLUMN9 = i.ToString();
            tm.COLUMN10 = i.ToString();
            tm.COLUMN11 = i.ToString();
            tm.COLUMN12 = i.ToString();
            tm.COLUMN13 = i.ToString();
            tm.COLUMN14 = i.ToString();
            tm.COLUMN15 = i.ToString();
            tm.COLUMN16 = i.ToString();
            tm.COLUMN17 = i.ToString();
            tm.COLUMN18 = i.ToString();
            tm.COLUMN19 = i.ToString();
            tm.COLUMN20 = i.ToString();
            sqlParameter sp;
            bool b = es.CreateInsertSqlByParam(tm, out sp);
            //var DA = new OracleDataAccess();
            //int nR = DA.ExecuteNonQuery(sp.sql, sp.dictParams);

            lb_ilp.InnerText = "测试生成带参数sql语句成功";
        }

        private void testCreateSel()
        {
            Entity2SqlForOrcl<TM_TAB_TEST> es = new Entity2SqlForOrcl<TM_TAB_TEST>();
            es.InitializeMin();

            string strPrefix = DBTool.GetParamPrefix();
            string str_cdt = "COLUMN1=" + strPrefix + "col1_cdt and COLUMN3=" + strPrefix + "col3_cdt";
            Dictionary<string, object> dictp = new Dictionary<string, object>();
            dictp.Add(strPrefix + "col1_cdt", "0");
            dictp.Add(strPrefix + "col3_cdt", "0");

            //sqlParameter sp = es.CreateExistSqlByParam(str_cdt, dictp);
            string sql = es.CreateSelSql("COLUMN1,COLUMN2,COLUMN3,COLUMN4,COLUMN5,sdo_util.to_wktgeometry(COLUMN6) AS COLUMN6", "COLUMN1='0' and COLUMN3=0");
            sqlParameter sp = es.CreateSelSqlByParam("COLUMN1,COLUMN2,COLUMN3,COLUMN4,COLUMN5", str_cdt, dictp);

            var DA = new OracleDataAccess();
            List<TM_TAB_TEST> lt = DA.Query<TM_TAB_TEST>(sp.sql, sp.dictParams);
            List<TM_TAB_TEST> lt2 = DA.Query<TM_TAB_TEST>(sql);

            lb_select.InnerText = "测试生成select成功，生成带参数的SQL和普通SQL";
        }

        private void testilupdatep()
        {
            List<string> lsqls = new List<string>();
            Entity2SqlForOrcl<TM_TAB_TEST> es = new Entity2SqlForOrcl<TM_TAB_TEST>();
            es.InitializeAll();

            int i = 0;
            TM_TAB_TEST tm = new TM_TAB_TEST();
            tm.COLUMN1 = i.ToString();
            tm.COLUMN2 = DateTime.Now;
            tm.COLUMN3 = i;
            tm.COLUMN4 = i * 0.1f;
            tm.COLUMN5 = "ccccc";
            tm.COLUMN7 = i;

            sqlParameter sp;
            string strPrefix = DBTool.GetParamPrefix();
            string str_cdt = "COLUMN1=" + strPrefix + "col1_cdt";
            Dictionary<string, object> dictp = new Dictionary<string, object>();
            dictp.Add(strPrefix + "col1_cdt", "0");
            List<DBTypeExt> types = new List<DBTypeExt> { DBTypeExt.orclVarchar2 };
            bool b = es.CreateUpdateSqlByParam(tm, str_cdt, dictp, types, out sp);

            var DA = new OracleDataAccess();
            int nR = DA.ExecuteNonQuery(sp.sql, sp.paramTypes, sp.dictParams);
        }

        private void testC(int nCount)
        {
            List<string> lsqls = new List<string>();
            Stopwatch watch = Stopwatch.StartNew();
            watch.Start();
            for (int i = 0; i < nCount; ++i)
            {
                TM_TAB_TEST tm = new TM_TAB_TEST();
                tm.COLUMN1 = i.ToString();
                tm.COLUMN2 = DateTime.Now;
                tm.COLUMN3 = i;
                tm.COLUMN4 = i * 0.1f;
                tm.COLUMN5 = "aa";
                tm.COLUMN7 = i;
                tm.COLUMN8 = i.ToString();
                tm.COLUMN9 = i.ToString();
                tm.COLUMN10 = i.ToString();
                tm.COLUMN11 = i.ToString();
                tm.COLUMN12 = i.ToString();
                tm.COLUMN13 = i.ToString();
                tm.COLUMN14 = i.ToString();
                tm.COLUMN15 = i.ToString();
                tm.COLUMN16 = i.ToString();
                tm.COLUMN17 = i.ToString();
                tm.COLUMN18 = i.ToString();
                tm.COLUMN19 = i.ToString();
                tm.COLUMN20 = i.ToString();
                string strSql = tm.getInsertSql();
                lsqls.Add(strSql);
            }

            watch.Stop();
            string str3 = watch.Elapsed.ToString();
            lb_c.InnerText = "model直接生成sql共计：" + lsqls.Count + "条，时间：" + str3;
        }
    }
}

model类

using System;
using DBExtensions;
using System.Text;

namespace BLL.Model_Reflect
{
    [DBEntity(EntityName = "TAB_TEST", EntityPK = "COLUMN1,COLUMN3", ParamTypes = new DBTypeExt[]{ DBTypeExt.orclVarchar2,DBTypeExt.orclDate,DBTypeExt.orclInt32, DBTypeExt.orclDouble, DBTypeExt.orclClob,DBTypeExt.orclClob,DBTypeExt.orclInt64, DBTypeExt.orclVarchar2, DBTypeExt.orclVarchar2, DBTypeExt.orclVarchar2, DBTypeExt.orclVarchar2,DBTypeExt.orclVarchar2,DBTypeExt.orclVarchar2,DBTypeExt.orclVarchar2, DBTypeExt.orclVarchar2,DBTypeExt.orclVarchar2,DBTypeExt.orclVarchar2,DBTypeExt.orclVarchar2,DBTypeExt.orclVarchar2,DBTypeExt.orclVarchar2 },haveLargeField = true)]
    [DbResult]
    public class TM_TAB_TEST
    {
        [DbColumn(IsOptional = true)]
        public string COLUMN1 { set; get; }

        [DbColumn(IsOptional = true)]
        public DateTime? COLUMN2 { set; get; }

        [DbColumn(IsOptional = true)]
        public int? COLUMN3 { set; get; }

        [DbColumn(IsOptional = true)]
        public float? COLUMN4 { set; get; }

        [DBDetail(ClobField=true)]
        [DbColumn(IsOptional = true)]
        public string COLUMN5 { set; get; }

        [DBDetail(GeomField = true)]
        [DbColumn(IsOptional = true)]
        public string COLUMN6 { set; get; }

        [DbColumn(IsOptional = true)]
        public long? COLUMN7 { set; get; }

        [DbColumn(IsOptional = true)]
        public string COLUMN8 { set; get; }

        [DbColumn(IsOptional = true)]
        public string COLUMN9 { set; get; }

        [DbColumn(IsOptional = true)]
        public string COLUMN10 { set; get; }

        [DbColumn(IsOptional = true)]
        public string COLUMN11 { set; get; }

        [DbColumn(IsOptional = true)]
        public string COLUMN12 { set; get; }

        [DbColumn(IsOptional = true)]
        public string COLUMN13 { set; get; }

        [DbColumn(IsOptional = true)]
        public string COLUMN14 { set; get; }

        [DbColumn(IsOptional = true)]
        public string COLUMN15 { set; get; }

        [DbColumn(IsOptional = true)]
        public string COLUMN16 { set; get; }

        [DbColumn(IsOptional = true)]
        public string COLUMN17 { set; get; }

        [DbColumn(IsOptional = true)]
        public string COLUMN18 { set; get; }

        [DbColumn(IsOptional = true)]
        public string COLUMN19 { set; get; }

        [DbColumn(IsOptional = true)]
        public string COLUMN20 { set; get; }

        /// <summary>
        /// 此方法只是用于测试，对比生成sql语句速度，无需写
        /// </summary>
        /// <returns></returns>
        public string getInsertSql()
        {
            string sql = string.Empty;
            StringBuilder fields = new StringBuilder(256);
            StringBuilder values = new StringBuilder(256);
           
            if (string.IsNullOrEmpty(COLUMN1))
            {
                fields.Append("COLUMN1,");
                values.Append("'" + COLUMN1 + "',");
            }
            if (COLUMN2.HasValue)
            {
                fields.Append("COLUMN2,");
                values.Append("to_date('" + COLUMN2.Value + "', 'yyyy-MM-dd hh24:mi:ss'),");
            }
            if (COLUMN3.HasValue)
            {
                fields.Append("COLUMN3,");
                values.Append("to_date('" + COLUMN3.Value + "', 'yyyy-MM-dd hh24:mi:ss'),");
            }
            if (COLUMN4.HasValue)
            {
                fields.Append("COLUMN4,");
                values.Append("to_date('" + COLUMN4.Value + "', 'yyyy-MM-dd hh24:mi:ss'),");
            }
            if (string.IsNullOrEmpty(COLUMN5))
            {
                fields.Append("COLUMN5,");
                values.Append("'" + COLUMN5+ "',");
            }
            if (string.IsNullOrEmpty(COLUMN6))
            {
                fields.Append("COLUMN6,");
                values.Append("'" + COLUMN6 + "',");
            }
            if (COLUMN7.HasValue)
            {
                fields.Append("COLUMN7,");
                values.Append(COLUMN7.Value + ",");
            }
            if (string.IsNullOrEmpty(COLUMN8))
            {
                fields.Append("COLUMN8,");
                values.Append("'" + COLUMN8 + "',");
            }
            if (string.IsNullOrEmpty(COLUMN9))
            {
                fields.Append("COLUMN9,");
                values.Append("'" + COLUMN9 + "',");
            }
            if (string.IsNullOrEmpty(COLUMN10))
            {
                fields.Append("COLUMN10,");
                values.Append("'" + COLUMN10 + "',");
            }
            if (string.IsNullOrEmpty(COLUMN11))
            {
                fields.Append("COLUMN11,");
                values.Append("'" + COLUMN11 + "',");
            }
            if (string.IsNullOrEmpty(COLUMN12))
            {
                fields.Append("COLUMN12,");
                values.Append("'" + COLUMN12 + "',");
            }
            if (string.IsNullOrEmpty(COLUMN13))
            {
                fields.Append("COLUMN13,");
                values.Append("'" + COLUMN13 + "',");
            }
            if (string.IsNullOrEmpty(COLUMN14))
            {
                fields.Append("COLUMN14,");
                values.Append("'" + COLUMN14 + "',");
            }
            if (string.IsNullOrEmpty(COLUMN15))
            {
                fields.Append("COLUMN15,");
                values.Append("'" + COLUMN15 + "',");
            }
            if (string.IsNullOrEmpty(COLUMN16))
            {
                fields.Append("COLUMN16,");
                values.Append("'" + COLUMN16 + "',");
            }
            if (string.IsNullOrEmpty(COLUMN17))
            {
                fields.Append("COLUMN17,");
                values.Append("'" + COLUMN17 + "',");
            }
            if (string.IsNullOrEmpty(COLUMN18))
            {
                fields.Append("COLUMN18,");
                values.Append("'" + COLUMN18 + "',");
            }
            if (string.IsNullOrEmpty(COLUMN19))
            {
                fields.Append("COLUMN19,");
                values.Append("'" + COLUMN19 + "',");
            }
            if (string.IsNullOrEmpty(COLUMN20))
            {
                fields.Append("COLUMN20,");
                values.Append("'" + COLUMN20 + "',");
            }
            
            sql = "insert into TAB_TEST (" + fields.ToString().TrimEnd(',') + ") values(" + values.ToString().TrimEnd(',') + ")";
            return sql;
        }
    }
}

这段代码还能更好，希望各位高手指点，不胜感谢

dy2017

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
vs 2015 轻量级orm框架（二）付源码及测试用例

接上一篇：vs 2015 轻量级orm框架（一）付源码及测试用例工具类：namespace Utility{ public class DBTool { /// <summary> /// 返回默认是oracle数据库前缀，数据库类型不区分大小写 /// </summary> /// &l...
复制链接

扫一扫