///// <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);
}
