///// <summary> ///// 根据EntityMapSql的全名称 "名称空间名字.SQL名字" 获取映射的SQL语句 ///// </summary> ///// <param name="fullName">EntityMapSql的全名称,格式: "名称空间名字.SQL名字"</param> ///// <returns>映射的SQL语句</returns> //public static string GetMapSql(string fullName) //{ //} /// <summary> /// 执行返回单值的查询,通常用于OQL的Count,Max等查询 /// </summary> /// <param name="oql">查询表达式</param> /// <param name="db">数据访问对象</param> /// <returns>单值</returns> public static object ExecuteScalar(OQL oql, AdoHelper db) { if (oql.Parameters != null && oql.Parameters.Count > 0) { IDataParameter[] paras = GetParameters(oql.Parameters, db); return(db.ExecuteScalar(oql.ToString(), CommandType.Text, paras)); } else { return(db.ExecuteScalar(oql.ToString())); } }
internal static string GetOQLPageSql(OQL oql, AdoHelper db) { string sql = oql.ToString(); string page_sql = null; switch (db.CurrentDBMSType) { case PWMIS.Common.DBMSType.Access: case PWMIS.Common.DBMSType.SqlServer: //可以在这里判断版本,以支持SQLSERVER 2012的分页 case PWMIS.Common.DBMSType.SqlServerCe: //如果含有Order By 子句,则不能使用主键分页 if (oql.haveJoinOpt || sql.IndexOf("order by", StringComparison.OrdinalIgnoreCase) > 0) { page_sql = PWMIS.Common.SQLPage.MakeSQLStringByPage(PWMIS.Common.DBMSType.SqlServer, sql, "", oql.PageSize, oql.PageNumber, oql.PageWithAllRecordCount); } else { //如果是字符串类型的主键,下面的分页可能不准确 if (oql.PageOrderDesc) { page_sql = PWMIS.Common.SQLPage.GetDescPageSQLbyPrimaryKey(oql.PageNumber, oql.PageSize, oql.sql_fields, oql.sql_table, oql.PageField, oql.sql_condition); } else { page_sql = PWMIS.Common.SQLPage.GetAscPageSQLbyPrimaryKey(oql.PageNumber, oql.PageSize, oql.sql_fields, oql.sql_table, oql.PageField, oql.sql_condition); } } break; default: page_sql = PWMIS.Common.SQLPage.MakeSQLStringByPage(db.CurrentDBMSType, sql, "", oql.PageSize, oql.PageNumber, oql.PageWithAllRecordCount); break; } return(page_sql); }
public OQLCompare Comparer(object field, CompareType type, OQL Value) { OQLCompare compare = new OQLCompare(this.LinkedOQL); var tnf = compare.LinkedOQL.TakeOneStackFields(); compare.ComparedFieldName = tnf.SqlFieldName; compare.ComparedType = type; if (type == CompareType.IS || type == CompareType.IsNot) { throw new ArgumentOutOfRangeException("IS 操作符的不支持子查询!"); } else { string childSql = Value.ToString(); if (Value.Parameters.Count > 0) { //先备份SQL语句中的参数名 ,感谢网友 null(yoli799480165) 发现此bug foreach (string key in Value.Parameters.Keys) { childSql = childSql.Replace(key, key + "_C"); } foreach (string key in Value.Parameters.Keys) { var vtnf = Value.Parameters[key]; string paraName = this.LinkedOQL.CreateParameter(vtnf); childSql = childSql.Replace(key + "_C", paraName); } } compare.ComparedParameterName = "\r\n(" + childSql + ")\r\n"; } compare.SqlFunctionFormat = ""; return(compare); }
///// <summary> ///// 根据EntityMapSql的全名称 "名称空间名字.SQL名字" 获取映射的SQL语句 ///// </summary> ///// <param name="fullName">EntityMapSql的全名称,格式: "名称空间名字.SQL名字"</param> ///// <returns>映射的SQL语句</returns> //public static string GetMapSql(string fullName) //{ //} /// <summary> /// 执行返回单值的查询,通常用于OQL的Count,Max等查询 /// </summary> /// <param name="oql">查询表达式</param> /// <param name="db">数据访问对象</param> /// <returns>单值</returns> public static object ExecuteScalar(OQL oql, AdoHelper db) { if (oql.Parameters != null && oql.Parameters.Count > 0) { int fieldCount = oql.Parameters.Count; IDataParameter[] paras = new IDataParameter[fieldCount]; int index = 0; foreach (string name in oql.Parameters.Keys) { paras[index] = db.GetParameter(name, oql.Parameters[name]); index++; } return(db.ExecuteScalar(oql.ToString(), CommandType.Text, paras)); } else { return(db.ExecuteScalar(oql.ToString())); } }
void TestOqlPage() { UserEntity ue = new UserEntity(); OQL q = OQL.From(ue) .Select(ue.ID, ue.Name, ue.Age) .Where(cmp => cmp.Comparer(ue.Age, ">", 20)) .OrderBy(ue.Age) .END; q.Limit(2, 3, true); Console.WriteLine("q:Page SQL is \r\n{0}", q); Console.WriteLine(q.PrintParameterInfo()); //当前测试总记录数5,查询后,OQL会得到总记录数 AdoHelper db = MyDB.GetDBHelperByConnectionName("conn2"); var list = EntityQuery <UserEntity> .QueryList(q, db); Users user = new Users() { NickName = "pdf.net", RoleID = RoleNames.Admin, Age = 20 }; UserRoles roles = new UserRoles() { RoleName = "role1" }; //测试字段直接比较 OQL q00 = OQL.From(user) .Select(user.ID, user.NickName, user.LastLoginIP) .Where(cmp => cmp.Comparer(user.AddTime, "=", user.LastLoginTime)) .OrderBy(o => o.Desc(user.LastLoginTime)) .END; Console.WriteLine("q00:one table and select all fields \r\n{0}", q00); Console.WriteLine(q00.PrintParameterInfo()); string pageSql = SQLPage.MakeSQLStringByPage(DBMSType.SqlServer, q00.ToString(), "", 10, 2, 999); Console.WriteLine("Page SQL"); Console.WriteLine(pageSql); OQL q2 = OQL.From(user) .InnerJoin(roles).On(user.RoleID, roles.ID) .Select(user.RoleID, roles.RoleName) .Where(user.NickName, roles.RoleName) .GroupBy(user.RoleID, roles.RoleName) .OrderBy(user.ID) .END; Console.WriteLine("q2:two table query use join\r\n{0}", q2); Console.WriteLine(q2.PrintParameterInfo()); pageSql = SQLPage.MakeSQLStringByPage(DBMSType.SqlServer, q2.ToString(), "", 10, 2, 999); Console.WriteLine("Page SQL"); Console.WriteLine(pageSql); }
public static void 杂查询() { SODUser user = new SODUser(); OQL q = OQL.From(user) .Select() .Where(cmp => cmp.Property(user.LogName) == "zhangyi" & cmp.Comparer(user.ID, "like", 1)) .END; List <SODUser> users = EntityQuery <SODUser> .QueryList(q); string sql = q.ToString(); sql = q.PrintParameterInfo(); }
void TestOQL() { //假如GoodsBaseInfo 对象的实例info 是长生命周期的,那么应该使用下面的方式使用OQL和OQLCompare GoodsBaseInfo info = new GoodsBaseInfo(); using (OQL q = new OQL(info)) { OQLCompare cmp = new OQLCompare(info); q.Select(info.GoodsName).Where(cmp.Comparer(info.SerialNumber, "=", "123456")); string sql = q.ToString(); cmp.Dispose(); } DisplayMemory(); }
static void TestInChild() { p_hege_detail detailObj = new p_hege_detail(); p_hege phegeObj = new p_hege(); OQL phegeOq = new OQL(phegeObj); phegeOq.Select(phegeObj.id).OrderBy(phegeObj.id, "DESC"); phegeOq.TopCount = 1; OQL detailOq = new OQL(detailObj); detailOq.Select(detailObj.coName, detailObj.coType, detailObj.coMessage) .Where(cmp => cmp.Comparer(detailObj.coType, "=", "Status") & cmp.Comparer(detailObj.hegeID, "in", phegeOq)) // .OrderBy(detailObj.id, "DESC"); Console.WriteLine("SQL:\r\n{0}\r\n,{1}", detailOq.ToString(), detailOq.PrintParameterInfo()); }
/// <summary> /// 执行一个不返回结果集的OQL查询表达式,例如更新,删除实体类的操作。使用自定义的数据访问对象进行操作 /// </summary> /// <param name="oql"></param> /// <param name="db"></param> /// <returns></returns> public static int ExecuteOql(OQL oql, AdoHelper db) { string sql = oql.ToString(); if (oql.Parameters.Count > 0) { IDataParameter[] paras = new IDataParameter[oql.Parameters.Count]; int index = 0; foreach (string key in oql.Parameters.Keys) { paras[index] = db.GetParameter(key, oql.Parameters[key]); index++; } return(db.ExecuteNonQuery(sql, CommandType.Text, paras)); } else { return(db.ExecuteNonQuery(sql)); } }
void TestOqlPage() { Users user = new Users() { NickName = "pdf.net", RoleID = RoleNames.Admin }; UserRoles roles = new UserRoles() { RoleName = "role1" }; //测试字段直接比较 OQL q00 = OQL.From(user) .Select(user.ID, user.NickName, user.LastLoginIP) .Where(cmp => cmp.Comparer(user.AddTime, "=", user.LastLoginTime)) .OrderBy(o => o.Desc(user.LastLoginTime)) .END; Console.WriteLine("q00:one table and select all fields \r\n{0}", q00); Console.WriteLine(q00.PrintParameterInfo()); string pageSql = SQLPage.MakeSQLStringByPage(DBMSType.SqlServer, q00.ToString(), "", 10, 2, 999); Console.WriteLine("Page SQL"); Console.WriteLine(pageSql); OQL q2 = OQL.From(user) .InnerJoin(roles).On(user.RoleID, roles.ID) .Select(user.RoleID, roles.RoleName) .Where(user.NickName, roles.RoleName) .GroupBy(user.RoleID, roles.RoleName) .OrderBy(user.ID) .END; Console.WriteLine("q2:two table query use join\r\n{0}", q2); Console.WriteLine(q2.PrintParameterInfo()); pageSql = SQLPage.MakeSQLStringByPage(DBMSType.SqlServer, q2.ToString(), "", 10, 2, 999); Console.WriteLine("Page SQL"); Console.WriteLine(pageSql); }
static void Main(string[] args) { Console.WriteLine("====**************** PDF.NET SOD 控制台测试程序 **************===="); Assembly coreAss = Assembly.GetAssembly(typeof(AdoHelper));//获得引用程序集 Console.WriteLine("框架核心程序集 PWMIS.Core Version:{0}", coreAss.GetName().Version.ToString()); Console.WriteLine(); Console.WriteLine(" 应用程序配置文件默认的数据库配置信息:\r\n 当前使用的数据库类型是:{0}\r\n 连接字符串为:{1}\r\n 请确保数据库服务器和数据库是否有效且已经初始化过建表脚本(项目下的2个sql脚本文件),\r\n继续请回车,退出请输入字母 Q ." , MyDB.Instance.CurrentDBMSType.ToString(), MyDB.Instance.ConnectionString); Console.WriteLine("=====Power by Bluedoctor,2015.2.8 http://www.pwmis.com/sqlmap ===="); string read = Console.ReadLine(); if (read.ToUpper() == "Q") { return; } //写入10000条日志,有缓存,可能不会写完 Console.WriteLine("测试日志写入10000 条信息..."); CommandLog loger = new CommandLog(); for (int t = 0; t <= 100; t++) { System.Threading.Thread thread = new System.Threading.Thread(new System.Threading.ParameterizedThreadStart(WriteLog)); thread.Name = "thread" + t; thread.Start(loger); } loger.Flush(); Console.WriteLine("按任意键继续"); Console.ReadLine(); EntityUser etu = new EntityUser(); ITable_User itu = etu.AsEntity(); DateTime dtt = itu.Birthday; //测试 AdoHelper的并发能力 //for (int i = 0; i < 100; i++) //{ // System.Threading.Thread t = new System.Threading.Thread( // new System.Threading.ParameterizedThreadStart(TestDataSetAndOQL)); // t.Name = "thread "+i; // t.Start(); //} //测试生成列的脚本 EntityCommand ecmd = new EntityCommand(new Table_User(), new SqlServer()); string table_script = ecmd.CreateTableCommand; Console.Write("1,测试 OpenSession 长连接数据库访问..."); TestDataSetAndOQL(null); Console.WriteLine("OK"); // Console.WriteLine("2,测试OQL 转SQL..."); RoadTeam.Model.CS.TbCsEvent CsEvent = new RoadTeam.Model.CS.TbCsEvent(); CsEvent.EventID = 1; OQL oql = OQL.From(CsEvent) .Select(CsEvent.EventCheck, CsEvent.EventCheckInfo, CsEvent.EventCheckor, CsEvent.EventCheckTime) .Where(CsEvent.EventID) .END; Console.WriteLine(oql.ToString()); Console.WriteLine("-----------------------"); RoadTeam.Model.CS.TbCsEvent CsEvent2 = new RoadTeam.Model.CS.TbCsEvent(); CsEvent.EventID = 1; OQL oql2 = OQL.From(CsEvent2) .Select(true, CsEvent2.EventCheck, CsEvent2.EventCheckInfo, CsEvent2.EventCheckor, CsEvent2.EventCheckTime) .Where(CsEvent2.EventID) .END; Console.WriteLine(oql2.ToString()); Console.WriteLine("-----------------------"); Console.WriteLine("OK"); // Console.Write("3,测试实体类动态增加虚拟属性..."); UserModels um1 = new UserModels(); um1.AddPropertyName("TestName"); um1["TestName"] = 123; int testi = (int)um1["TestName"]; um1["TestName"] = "abc"; string teststr = (string)um1["TestName"]; Console.WriteLine("OK"); // Console.Write("4,测试缓存..."); var cache = PWMIS.Core.MemoryCache <EntityBase> .Default; cache.Add("UserModels", um1); var cacheData = cache.Get("UserModels"); cache.Remove("UserModels"); Console.WriteLine("OK"); // Console.Write("5,测试自动创建实体类数据库表..."); AutoCreateEntityTable <LT_Users>(); AutoCreateEntityTable <LT_UserRoles>(); Console.WriteLine("OK"); Console.WriteLine("------------测试暂时停止,回车继续运行------"); Console.ReadLine(); //return; Console.Write("6,测试实体类的外键查询..."); TestEntityFK(); Console.WriteLine("OK"); Console.Write("7,测试实体类批量插入..."); OqlInTest(); Console.WriteLine("OK"); Console.WriteLine("8,测试SOD POCO实体类性能..."); Console.WriteLine("SELECT top 100000 UID,Sex,Height,Birthday,Name FROM Table_User"); for (int i = 0; i < 10; i++) { Console.WriteLine("-------------Testt No.{0}----------------", i + 1); TestPocoQuery(); } Console.WriteLine("--------OK---------------"); Console.Write("9,测试OQL IN 子查询..."); TestInChild(); Console.WriteLine("OK"); //TestFun(1, 2, 3); Console.WriteLine("10,测试泛型 OQL --GOQL"); TestGOQL(); Console.WriteLine("OK"); Console.WriteLine("11,测试OQL 批量更新(带条件更新)..."); UpdateTest(); Console.WriteLine("OK"); Console.WriteLine("12,测试批量数据插入性能...."); //InsertTest(); Console.WriteLine("13,OQL 自连接..."); OqlJoinTest(); // Console.Write("14,根据接口类型,自动创建实体类测试..."); TestDynamicEntity(); Console.WriteLine("OK"); // Console.WriteLine("15,Sql 格式化查询测试( SOD 微型ORM功能)..."); AdoHelper dbLocal = new SqlServer(); dbLocal.ConnectionString = MyDB.Instance.ConnectionString; //DataSet ds = dbLocal.ExecuteDataSet("SELECT * FROM Table_User WHERE UID={0} AND Height>={1:5.2}", 1, 1.80M); /* * 下面的写法过时 * var dataList = dbLocal.GetList(reader => * { * return new * { * UID=reader.GetInt32(0), * Name=reader.GetString(1) * }; * }, "SELECT UID,Name FROM Table_User WHERE Sex={0} And Height>={0:5.2}",1, 1.60); */ var dataList = dbLocal.ExecuteMapper("SELECT UID,Name FROM Table_User WHERE Sex={0} And Height>={1:5.2}", 1, 1.60) .MapToList(reader => new { UID = reader.GetInt32(0), Name = reader.GetString(1) }); Console.WriteLine("OK"); // Console.Write("16,测试属性拷贝..."); V_UserModels vum = new V_UserModels(); vum.BIGTEAM_ID = 123;//可空属性,如果目标对象不是的话,无法拷贝 vum.REGION_ID = 456; vum.SMALLTEAM_ID = 789; UserModels um = vum.CopyTo <UserModels>(); Console.WriteLine("OK"); // Console.Write("17,测试【自定义查询】的实体类..."); UserPropertyView up = new UserPropertyView(); OQL q11 = new OQL(up); OQLOrder order = new OQLOrder(q11); q11.Select() .Where(q11.Condition.AND(up.PropertyName, "=", "总成绩").AND(up.PropertyValue, ">", 1000)) .OrderBy(order.Asc(up.UID)); AdoHelper db11 = MyDB.GetDBHelperByConnectionName("local"); var result = EntityQuery <UserPropertyView> .QueryList(q11, db11); //下面2行不是必须 q11.Dispose(); Console.WriteLine("OK"); //EntityContainer ec = new EntityContainer(q11); //var ecResult = ec.MapToList(() => { // return new { AAA = ec.GetItemValue<int>(0), BBB = ec.GetItemValue<string>(1) }; //}); ///////////////////////////////////////////////////// Console.WriteLine("18,测试实体类【自动保存】数据..."); TestAutoSave(); /////////////////测试事务//////////////////////////////////// Console.WriteLine("19,测试测试事务..."); TestTransaction(); TestTransaction2(); Console.WriteLine("事务测试完成!"); Console.WriteLine("-------PDF.NET SOD 测试全部完成-------"); Console.ReadLine(); }
public GOQL <T> Print(out string sqlInfo) { sqlInfo = string.Format("SQL:{0}\r\n{1}", currentOQL.ToString(), currentOQL.PrintParameterInfo()); return(this); }
internal static SqlInfo GetSqlInfoFromOQL(OQL oql, AdoHelper db, Type factEntityType, bool single) { string sql = ""; Dictionary <string, TableNameField> Parameters = null; if (oql.EntityMap == PWMIS.Common.EntityMapType.Table || oql.EntityMap == PWMIS.Common.EntityMapType.View) { #region OQL2SqlInfo if (oql.PageEnable && (!single || oql.PageWithAllRecordCount <= 0)) { sql = GetOQLPageSql(oql, db); } else { sql = oql.ToString(); } SqlInfo result = new SqlInfo(sql, oql.Parameters); result.CommandType = CommandType.Text; result.TableName = oql.GetEntityTableName(); return(result); #endregion } else if (oql.EntityMap == PWMIS.Common.EntityMapType.SqlMap) { #region SQLMAP //处理用户查询映射的实体类 if (CommonUtil.CacheEntityMapSql == null) { CommonUtil.CacheEntityMapSql = new Dictionary <string, string>(); } if (!CommonUtil.CacheEntityMapSql.ContainsKey(oql.sql_table)) { string tempView = GetMapSql(factEntityType); CommonUtil.CacheEntityMapSql.Add(oql.sql_table, tempView); } sql = oql.GetMapSQL(CommonUtil.CacheEntityMapSql[oql.sql_table]); //如果用户本身没有初始化参数对象,则这里声明一个 edit at 2012.11.16 Parameters = new Dictionary <string, TableNameField>(); if (oql.InitParameters != null) { foreach (string name in oql.InitParameters.Keys) { TableNameField tnf = new TableNameField(); tnf.FieldValue = oql.InitParameters[name]; Parameters.Add(name, tnf); } } if (oql.Parameters != null && oql.Parameters.Count > 0) { foreach (string name in oql.Parameters.Keys) { Parameters.Add(name, oql.Parameters[name]); } } //这里可能需要特别处理分页 --修改 @芜湖-大枕头 2016.12.15 if (oql.PageEnable && (!single || oql.PageWithAllRecordCount <= 0)) { sql = GetOQLPageSql(oql, db); } else { sql = oql.ToString(); } SqlInfo si = new SqlInfo(sql, Parameters); si.CommandType = CommandType.Text; return(si); #endregion } else { #region StoredProcedure string script = ""; if (CommonUtil.CacheEntityMapSql == null) { CommonUtil.CacheEntityMapSql = new Dictionary <string, string>(); } //获取SQL-MAP脚本 if (CommonUtil.CacheEntityMapSql.ContainsKey(oql.sql_table)) { script = CommonUtil.CacheEntityMapSql[oql.sql_table]; } else { script = GetMapSql(factEntityType); CommonUtil.CacheEntityMapSql.Add(oql.sql_table, script); } //对SQL-MAP格式的参数进行解析 SqlMap.SqlMapper mapper = new PWMIS.DataMap.SqlMap.SqlMapper(); mapper.DataBase = db; //解析存储过程名称 sql = mapper.FindWords(mapper.GetScriptInfo(script), 0, 255); //由于是存储过程,需要特殊处理,调用 FindWords方法 //解析参数 IDataParameter[] paras = mapper.GetParameters(script); if (oql.InitParameters != null && oql.InitParameters.Count > 0) { Parameters = new Dictionary <string, TableNameField>(); try { foreach (IDataParameter para in paras) { string key = para.ParameterName.TrimStart(db.GetParameterChar.ToCharArray()); para.Value = oql.InitParameters[key]; Parameters.Add(key, new TableNameField() { FieldValue = para }); } } catch (KeyNotFoundException exKey) { throw new KeyNotFoundException("'存储过程实体类'的初始化参数中没有找到指定的参数名,请检查参数定义和设置。", exKey); } catch (Exception ex) { throw ex; } } else { if (paras.Length > 0) { throw new Exception("当前'存储过程实体类'需要提供初始化参数,请设置OQL对象的InitParameters属性"); } } SqlInfo si = new SqlInfo(sql, Parameters); si.CommandType = CommandType.StoredProcedure; return(si); #endregion } }
/// <summary> /// 执行OQL查询,统计该查询对应的记录数量(一般用于分页前使用,某些方法会自动调用该方法,请注意方法说明) /// </summary> /// <param name="oql"></param> /// <param name="db"></param> /// <returns></returns> public static object ExecuteOQLCount(OQL oql, AdoHelper db) { string sql = PWMIS.Common.SQLPage.MakeSQLStringByPage(db.CurrentDBMSType, oql.ToString(), "", oql.PageSize, oql.PageNumber, 0); if (oql.Parameters != null && oql.Parameters.Count > 0) { IDataParameter[] paras = GetParameters(oql.Parameters, db); return(db.ExecuteScalar(sql, CommandType.Text, paras)); } else { return(db.ExecuteScalar(sql)); } }
/// <summary> /// 根据OQL查询数据获得DataReader。如果指定single=真,将执行优化的查询以获取单条记录 /// </summary> /// <param name="oql">OQL表达式</param> /// <param name="db">当前数据库访问对象</param> /// <param name="factEntityType">实体类类型</param> /// <param name="single">是否只查询一条记录</param> /// <returns>DataReader</returns> public static IDataReader ExecuteDataReader(OQL oql, AdoHelper db, Type factEntityType, bool single) { string sql = ""; Dictionary <string, object> Parameters = null; //处理用户查询映射的实体类 if (oql.EntityMap == PWMIS.Common.EntityMapType.SqlMap) { if (CommonUtil.CacheEntityMapSql == null) { CommonUtil.CacheEntityMapSql = new Dictionary <string, string>(); } if (!CommonUtil.CacheEntityMapSql.ContainsKey(oql.sql_table)) { string tempView = GetMapSql(factEntityType); CommonUtil.CacheEntityMapSql.Add(oql.sql_table, tempView); } sql = oql.GetMapSQL(CommonUtil.CacheEntityMapSql[oql.sql_table]); //如果用户本身没有初始化参数对象,则这里声明一个 edit at 2012.11.16 Parameters = oql.InitParameters ?? new Dictionary <string, object>(); if (oql.Parameters != null && oql.Parameters.Count > 0) { foreach (string name in oql.Parameters.Keys) { Parameters.Add(name, oql.Parameters[name]); } } } else if (oql.EntityMap == PWMIS.Common.EntityMapType.StoredProcedure) { string script = ""; if (CommonUtil.CacheEntityMapSql == null) { CommonUtil.CacheEntityMapSql = new Dictionary <string, string>(); } //获取SQL-MAP脚本 if (CommonUtil.CacheEntityMapSql.ContainsKey(oql.sql_table)) { script = CommonUtil.CacheEntityMapSql[oql.sql_table]; } else { script = GetMapSql(factEntityType); CommonUtil.CacheEntityMapSql.Add(oql.sql_table, script); } //对SQL-MAP格式的参数进行解析 SqlMap.SqlMapper mapper = new PWMIS.DataMap.SqlMap.SqlMapper(); mapper.DataBase = db; //解析存储过程名称 sql = mapper.FindWords(mapper.GetScriptInfo(script), 0, 255); //由于是存储过程,需要特殊处理,调用 FindWords方法 //解析参数 IDataParameter[] paras = mapper.GetParameters(script); if (oql.InitParameters != null && oql.InitParameters.Count > 0) { try { foreach (IDataParameter para in paras) { string key = para.ParameterName.TrimStart(db.GetParameterChar.ToCharArray()); para.Value = oql.InitParameters[key]; } } catch (KeyNotFoundException exKey) { throw new KeyNotFoundException("'存储过程实体类'的初始化参数中没有找到指定的参数名,请检查参数定义和设置。", exKey); } catch (Exception ex) { throw ex; } } else { if (paras.Length > 0) { throw new Exception("当前'存储过程实体类'需要提供初始化参数,请设置OQL对象的InitParameters属性"); } } return(db.ExecuteDataReader(sql, CommandType.StoredProcedure, paras)); } else { //edit at 2013-2-25 处理非SQLSERVER的参数名前缀问题 if (db is SqlServer) { sql = oql.ToString(); } else { sql = oql.ToString().Replace("@", db.GetParameterChar); } Parameters = oql.Parameters; } //处理实体类分页 2010.6.20 if (oql.PageEnable && !single) { switch (db.CurrentDBMSType) { case PWMIS.Common.DBMSType.Access: case PWMIS.Common.DBMSType.SqlServer: case PWMIS.Common.DBMSType.SqlServerCe: //如果含有Order By 子句,则不能使用主键分页 if (oql.HaveJoinOpt || sql.IndexOf("order by", StringComparison.OrdinalIgnoreCase) > 0) { sql = PWMIS.Common.SQLPage.MakeSQLStringByPage(PWMIS.Common.DBMSType.SqlServer, sql, "", oql.PageSize, oql.PageNumber, oql.PageWithAllRecordCount); } else { if (oql.PageOrderDesc) { sql = PWMIS.Common.SQLPage.GetDescPageSQLbyPrimaryKey(oql.PageNumber, oql.PageSize, oql.sql_fields, oql.sql_table, oql.PageField, oql.sql_condition); } else { sql = PWMIS.Common.SQLPage.GetAscPageSQLbyPrimaryKey(oql.PageNumber, oql.PageSize, oql.sql_fields, oql.sql_table, oql.PageField, oql.sql_condition); } } break; //case PWMIS.Common.DBMSType.Oracle: // sql = PWMIS.Common.SQLPage.MakeSQLStringByPage(PWMIS.Common.DBMSType.Oracle, sql, "", oql.PageSize, oql.PageNumber, oql.PageWithAllRecordCount); // break ; //case PWMIS.Common.DBMSType.MySql: //case PWMIS.Common.DBMSType.SQLite: // sql = PWMIS.Common.SQLPage.MakeSQLStringByPage(PWMIS.Common.DBMSType.MySql, sql, "", oql.PageSize, oql.PageNumber, oql.PageWithAllRecordCount); // break; //case PWMIS.Common.DBMSType.PostgreSQL: // sql = PWMIS.Common.SQLPage.MakeSQLStringByPage(PWMIS.Common.DBMSType.PostgreSQL, sql, "", oql.PageSize, oql.PageNumber, oql.PageWithAllRecordCount); // break; default: sql = PWMIS.Common.SQLPage.MakeSQLStringByPage(db.CurrentDBMSType, sql, "", oql.PageSize, oql.PageNumber, oql.PageWithAllRecordCount); break; } } IDataReader reader = null; if (Parameters != null && Parameters.Count > 0) { int fieldCount = Parameters.Count; IDataParameter[] paras = new IDataParameter[fieldCount]; int index = 0; foreach (string name in Parameters.Keys) { paras[index] = db.GetParameter(name, Parameters[name]); //为字符串类型的参数指定长度 edit at 2012.4.23 if (paras[index].Value != null && paras[index].Value.GetType() == typeof(string)) { string field = FindFieldNameInSql(sql, name, db.GetParameterChar); ((IDbDataParameter)paras[index]).Size = EntityBase.GetStringFieldSize(oql.sql_table, field); } index++; } if (single) { reader = db.ExecuteDataReaderWithSingleRow(sql, paras); } else { reader = db.ExecuteDataReader(sql, CommandType.Text, paras); } } else { if (single) { reader = db.ExecuteDataReaderWithSingleRow(sql); } else { reader = db.ExecuteDataReader(sql); } } return(reader); }
private IDataReader ExecuteDataReader(OQL oql, AdoHelper db) { string sql = ""; sql = oql.ToString(); //处理实体类分页 2010.6.20 if (oql.PageEnable) { //处理分页统前的记录数量统计问题 感谢网友 @成都-小兵 发现此问题 if (oql.PageWithAllRecordCount == 0) { object oValue = EntityQueryAnonymous.ExecuteOQLCount(oql, db); oql.PageWithAllRecordCount = CommonUtil.ChangeType <int>(oValue); } switch (db.CurrentDBMSType) { case PWMIS.Common.DBMSType.Access: case PWMIS.Common.DBMSType.SqlServer: case PWMIS.Common.DBMSType.SqlServerCe: if (oql.haveJoinOpt) { if (oql.PageNumber <= 1) //仅限定记录条数 { sql = "Select Top " + oql.PageSize + " " + sql.Trim().Substring("SELECT ".Length); } else //必须采用复杂分页方案 { //edit at 2012.10.2 oql.PageWithAllRecordCount sql = PWMIS.Common.SQLPage.MakeSQLStringByPage(PWMIS.Common.DBMSType.SqlServer, sql, "", oql.PageSize, oql.PageNumber, oql.PageWithAllRecordCount); } } else { //单表查询的情况 if (oql.PageOrderDesc) { sql = PWMIS.Common.SQLPage.GetDescPageSQLbyPrimaryKey(oql.PageNumber, oql.PageSize, oql.sql_fields, oql.sql_table, oql.PageField, oql.sql_condition); } else { sql = PWMIS.Common.SQLPage.GetAscPageSQLbyPrimaryKey(oql.PageNumber, oql.PageSize, oql.sql_fields, oql.sql_table, oql.PageField, oql.sql_condition); } } break; case PWMIS.Common.DBMSType.Oracle: sql = PWMIS.Common.SQLPage.MakeSQLStringByPage(PWMIS.Common.DBMSType.Oracle, sql, "", oql.PageSize, oql.PageNumber, 999); break; case PWMIS.Common.DBMSType.MySql: sql = PWMIS.Common.SQLPage.MakeSQLStringByPage(PWMIS.Common.DBMSType.MySql, sql, "", oql.PageSize, oql.PageNumber, 999); break; case PWMIS.Common.DBMSType.PostgreSQL: sql = PWMIS.Common.SQLPage.MakeSQLStringByPage(PWMIS.Common.DBMSType.PostgreSQL, sql, "", oql.PageSize, oql.PageNumber, 999); break; default: throw new Exception("实体类分页错误:不支持此种类型的数据库分页。"); } } IDataReader reader = null; if (oql.Parameters != null && oql.Parameters.Count > 0) { int fieldCount = oql.Parameters.Count; IDataParameter[] paras = EntityQueryAnonymous.GetParameters(oql.Parameters, db); //int index = 0; //foreach (string name in oql.Parameters.Keys) //{ // paras[index] = db.GetParameter(name, oql.Parameters[name]); // index++; //} reader = db.ExecuteDataReader(sql, CommandType.Text, paras); } else { reader = db.ExecuteDataReader(sql); } return(reader); }
internal static SqlInfo GetSqlInfoFromOQL(OQL oql, AdoHelper db, Type factEntityType, bool single) { string sql = ""; Dictionary <string, TableNameField> Parameters = null; //处理用户查询映射的实体类 if (oql.EntityMap == PWMIS.Common.EntityMapType.SqlMap) { if (CommonUtil.CacheEntityMapSql == null) { CommonUtil.CacheEntityMapSql = new Dictionary <string, string>(); } if (!CommonUtil.CacheEntityMapSql.ContainsKey(oql.sql_table)) { string tempView = GetMapSql(factEntityType); CommonUtil.CacheEntityMapSql.Add(oql.sql_table, tempView); } sql = oql.GetMapSQL(CommonUtil.CacheEntityMapSql[oql.sql_table]); //如果用户本身没有初始化参数对象,则这里声明一个 edit at 2012.11.16 Parameters = new Dictionary <string, TableNameField>(); if (oql.InitParameters != null) { foreach (string name in oql.InitParameters.Keys) { TableNameField tnf = new TableNameField(); tnf.FieldValue = oql.InitParameters[name]; Parameters.Add(name, tnf); } } if (oql.Parameters != null && oql.Parameters.Count > 0) { foreach (string name in oql.Parameters.Keys) { Parameters.Add(name, oql.Parameters[name]); } } } else if (oql.EntityMap == PWMIS.Common.EntityMapType.StoredProcedure) { string script = ""; if (CommonUtil.CacheEntityMapSql == null) { CommonUtil.CacheEntityMapSql = new Dictionary <string, string>(); } //获取SQL-MAP脚本 if (CommonUtil.CacheEntityMapSql.ContainsKey(oql.sql_table)) { script = CommonUtil.CacheEntityMapSql[oql.sql_table]; } else { script = GetMapSql(factEntityType); CommonUtil.CacheEntityMapSql.Add(oql.sql_table, script); } //对SQL-MAP格式的参数进行解析 SqlMap.SqlMapper mapper = new PWMIS.DataMap.SqlMap.SqlMapper(); mapper.DataBase = db; //解析存储过程名称 sql = mapper.FindWords(mapper.GetScriptInfo(script), 0, 255); //由于是存储过程,需要特殊处理,调用 FindWords方法 //解析参数 IDataParameter[] paras = mapper.GetParameters(script); if (oql.InitParameters != null && oql.InitParameters.Count > 0) { Parameters = new Dictionary <string, TableNameField>(); try { foreach (IDataParameter para in paras) { string key = para.ParameterName.TrimStart(db.GetParameterChar.ToCharArray()); para.Value = oql.InitParameters[key]; Parameters.Add(key, new TableNameField() { FieldValue = para }); } } catch (KeyNotFoundException exKey) { throw new KeyNotFoundException("'存储过程实体类'的初始化参数中没有找到指定的参数名,请检查参数定义和设置。", exKey); } catch (Exception ex) { throw ex; } } else { if (paras.Length > 0) { throw new Exception("当前'存储过程实体类'需要提供初始化参数,请设置OQL对象的InitParameters属性"); } } SqlInfo si = new SqlInfo(sql, Parameters); si.CommandType = CommandType.StoredProcedure; //return db.ExecuteDataReader(sql, CommandType.StoredProcedure, paras); return(si); } else { sql = oql.ToString(); Parameters = oql.Parameters; } if (oql.PageEnable && (!single || oql.PageWithAllRecordCount <= 0)) { switch (db.CurrentDBMSType) { case PWMIS.Common.DBMSType.Access: case PWMIS.Common.DBMSType.SqlServer: case PWMIS.Common.DBMSType.SqlServerCe: //如果含有Order By 子句,则不能使用主键分页 if (oql.haveJoinOpt || sql.IndexOf("order by", StringComparison.OrdinalIgnoreCase) > 0) { sql = PWMIS.Common.SQLPage.MakeSQLStringByPage(PWMIS.Common.DBMSType.SqlServer, sql, "", oql.PageSize, oql.PageNumber, oql.PageWithAllRecordCount); } else { //如果是字符串类型的主键,下面的分页可能不准确 if (oql.PageOrderDesc) { sql = PWMIS.Common.SQLPage.GetDescPageSQLbyPrimaryKey(oql.PageNumber, oql.PageSize, oql.sql_fields, oql.sql_table, oql.PageField, oql.sql_condition); } else { sql = PWMIS.Common.SQLPage.GetAscPageSQLbyPrimaryKey(oql.PageNumber, oql.PageSize, oql.sql_fields, oql.sql_table, oql.PageField, oql.sql_condition); } } break; default: sql = PWMIS.Common.SQLPage.MakeSQLStringByPage(db.CurrentDBMSType, sql, "", oql.PageSize, oql.PageNumber, oql.PageWithAllRecordCount); break; } } SqlInfo result = new SqlInfo(sql, Parameters); result.CommandType = CommandType.Text; result.TableName = oql.sql_table; return(result); }
private IDataReader ExecuteDataReader(OQL oql, AdoHelper db) { string sql = null; //处理实体类分页 2010.6.20 if (oql.PageEnable) { //处理分页统前的记录数量统计问题 感谢网友 @成都-小兵 发现此问题 if (oql.PageWithAllRecordCount == 0) { //网友 if-else 发现以下问题,备注: //这个函数在只有where而没有排序的时候,生成的统计语句的sql字符串的不对,会生成这种sql //发现没有排序,且pagenumber为1的时候,就直接返回了这种select top这个字符串,自然就查不到count了 //其次就是有分页的时候,把字段名转大写了 //都在SQLpage.cs object oValue = EntityQueryAnonymous.ExecuteOQLCount(oql, db); oql.PageWithAllRecordCount = CommonUtil.ChangeType <int>(oValue); if (oql.PageWithAllRecordCount == 0) { return(null); } } #region 面代码已经重构 /* * * switch (db.CurrentDBMSType) * { * case PWMIS.Common.DBMSType.Access: * case PWMIS.Common.DBMSType.SqlServer: * case PWMIS.Common.DBMSType.SqlServerCe: * if (oql.haveJoinOpt) * { * if (oql.PageNumber <= 1) //仅限定记录条数 * { * sql = "Select Top " + oql.PageSize + " " + sql.Trim().Substring("SELECT ".Length); * * } * else //必须采用复杂分页方案 * { * //edit at 2012.10.2 oql.PageWithAllRecordCount * sql = PWMIS.Common.SQLPage.MakeSQLStringByPage(PWMIS.Common.DBMSType.SqlServer, sql, "", oql.PageSize, oql.PageNumber, oql.PageWithAllRecordCount); * * } * } * else * { * //单表查询的情况 * if (oql.PageOrderDesc) * sql = PWMIS.Common.SQLPage.GetDescPageSQLbyPrimaryKey(oql.PageNumber, oql.PageSize, oql.sql_fields, oql.sql_table, oql.PageField, oql.sql_condition); * else * sql = PWMIS.Common.SQLPage.GetAscPageSQLbyPrimaryKey(oql.PageNumber, oql.PageSize, oql.sql_fields, oql.sql_table, oql.PageField, oql.sql_condition); * } * * break; * case PWMIS.Common.DBMSType.Oracle: * sql = PWMIS.Common.SQLPage.MakeSQLStringByPage(PWMIS.Common.DBMSType.Oracle, sql, "", oql.PageSize, oql.PageNumber, 999); * break; * case PWMIS.Common.DBMSType.MySql: * sql = PWMIS.Common.SQLPage.MakeSQLStringByPage(PWMIS.Common.DBMSType.MySql, sql, "", oql.PageSize, oql.PageNumber, 999); * break; * case PWMIS.Common.DBMSType.PostgreSQL: * sql = PWMIS.Common.SQLPage.MakeSQLStringByPage(PWMIS.Common.DBMSType.PostgreSQL, sql, "", oql.PageSize, oql.PageNumber, 999); * break; * default: * throw new Exception("实体类分页错误:不支持此种类型的数据库分页。"); * * } */ #endregion sql = EntityQueryAnonymous.GetOQLPageSql(oql, db); //分页SQL为空,可能分页页码超出可用范围,感谢 @深圳-有事Q我 发现此bug if (sql == string.Empty) { return(null); } } else { sql = oql.ToString(); } IDataReader reader = null; if (oql.Parameters != null && oql.Parameters.Count > 0) { int fieldCount = oql.Parameters.Count; IDataParameter[] paras = EntityQueryAnonymous.GetParameters(oql.Parameters, db); //int index = 0; //foreach (string name in oql.Parameters.Keys) //{ // paras[index] = db.GetParameter(name, oql.Parameters[name]); // index++; //} reader = db.ExecuteDataReader(sql, CommandType.Text, paras); } else { reader = db.ExecuteDataReader(sql); } return(reader); }