//public static void CheckStringPara(string SQL,string tableName, IDataParameter[] paras,string parameterChar)
//{
// for (int index = 0; index < paras.Length; index++)
// {
// //为字符串类型的参数指定长度 edit at 2012.4.23
// if (paras[index].Value != null && paras[index].Value.GetType() == typeof(string))
// {
// string field = FindFieldNameInSql(SQL, paras[index].ParameterName, parameterChar);
// ((IDbDataParameter)paras[index]).Size = EntityBase.GetStringFieldSize(tableName, field);
// }
// }
//}
/// <summary>
/// 使用SQL查询命令信息 执行数据阅读器查询。注意，查询单行数据不支持存储过程。
/// </summary>
/// <param name="si">SQL查询命令信息</param>
/// <param name="db">数据访问对象</param>
/// <param name="single">是否仅查询单行数据</param>
/// <returns>数据阅读器</returns>
public static IDataReader ExecuteDataReader(SqlInfo si, AdoHelper db, bool single)
{
IDataReader reader = null;
if (si.Parameters != null && si.Parameters.Count > 0)
{
IDataParameter[] paras = GetParameters(si.Parameters, db);
if (single)
{
reader = db.ExecuteDataReaderWithSingleRow(si.SQL, paras);
}
else
{
reader = db.ExecuteDataReader(si.SQL, si.CommandType, paras);
}
}
else
{
if (single)
{
reader = db.ExecuteDataReaderWithSingleRow(si.SQL);
}
else
{
reader = db.ExecuteDataReader(si.SQL, si.CommandType, null);
}
}
return(reader);
}
static void TestPocoQuery()
{
string sql = "SELECT top 100000 UID,Sex,Height,Birthday,Name FROM Table_User";
AdoHelper db = MyDB.Instance;
System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
Console.Write("query by DataSet,begin...");
sw.Start();
DataSet ds = db.ExecuteDataSet(sql);
sw.Stop();
Console.WriteLine("end,used time(ms){0}", sw.ElapsedMilliseconds);
//PocoQuery query = new PocoQuery();
////预热
//List<UserPoco> list1 = query.QueryList<UserPoco>(db.ExecuteDataReader("SELECT top 1 * FROM Table_User"));
sw.Reset();
Console.Write("query by AdoHelper Poco Query,begin...");
sw.Start();
List <UserPoco> list = AdoHelper.QueryList <UserPoco>(db.ExecuteDataReader(sql));
sw.Stop();
Console.WriteLine("end,used time(ms){0}", sw.ElapsedMilliseconds);
sw.Reset();
Console.Write("query by PDF.NET EntityQuery,begin...");
sw.Start();
List <Table_User> list3 = EntityQuery <Table_User> .QueryList(db.ExecuteDataReader(sql));
sw.Stop();
Console.WriteLine("end,used time(ms){0}", sw.ElapsedMilliseconds);
sw.Reset();
Console.Write("query by PDF.NET AdoHelper (handle),begin...");
sw.Start();
//UID,Sex,Height,Birthday,Name
IList <UserPoco> list4 = db.GetList <UserPoco>(reader => {
return(new UserPoco()
{
UID = reader.IsDBNull(0)?0: reader.GetInt32(0),
Sex = reader.IsDBNull(1) ? false : reader.GetBoolean(1),
Height = reader.IsDBNull(2) ? 0 : reader.GetFloat(2),
Birthday = reader.IsDBNull(3) ? default(DateTime) : reader.GetDateTime(3),
Name = reader.IsDBNull(4) ? null : reader.GetString(4)
});
}, sql, null);
sw.Stop();
Console.WriteLine("end,used time(ms){0}", sw.ElapsedMilliseconds);
System.Threading.Thread.Sleep(1000);
}
/// <summary>
/// 通过 父表实例 查找 查询关联的特定子实体类集合 【参照“医生”的QueryListWithChild函数，不足：ParentType应该可以通过 entity获取。。。】
/// </summary>
/// <typeparam name="T"></typeparam>
/// <typeparam name="ParentType"></typeparam>
/// <param name="entity"></param>
/// <returns></returns>
public static List <T> GetChildList <T, ParentType>(this EntityBase entity, AdoHelper db)
where T : EntityBase, new()
where ParentType : EntityBase, new()
{
T child = new T();
string fk = child.GetForeignKey <ParentType>();
if (fk == "")
{
return(null);
}
string sql = "SELECT * FROM " + child.GetTableName() + " WHERE " + fk + " IN ({0})";
List <string> paraNames = new List <string>();
List <IDataParameter> paras = new List <IDataParameter>();
string name = db.GetParameterChar + "P0";
paraNames.Add(name);
paras.Add(db.GetParameter(name, entity[entity.PrimaryKeys[0]]));
//会有2100个参数的限制问题，下期解决
string objSql = string.Format(sql, string.Join(",", paraNames.ToArray()));
IDataReader reader = db.ExecuteDataReader(objSql, CommandType.Text, paras.ToArray());
//如果字段名跟实体类属性名不一致,下面这样使用会有问题,
//return AdoHelper.QueryList<TChild>(reader);//还需要分析到对应的父实体类上
//修改成下面的代码 2014.10.30 感谢 网友 发呆数星星 发现此问题
return(EntityQuery <T> .QueryList(reader, child.GetTableName()));
}
/// <summary>
/// 根据指定的条件，从数据阅读器中赛选出需要的实体对象。本方法适用于数据量不是很大的数据库表，要加强效率，请使用OQL表达式。
/// </summary>
/// <param name="condition">查询条件</param>
/// <returns>实体对象列表</returns>
public static List <T> QueryList(ConditionHandle <T> condition)
{
AdoHelper db = MyDB.Instance;
T entity = new T();
string sql = "SELECT " + string.Join(",", entity.PropertyNames) + " FROM " + entity.TableName;
IDataReader reader = db.ExecuteDataReader(sql);
List <T> list = new List <T>();
using (reader)
{
int fcount = entity.PropertyNames.Length;
while (reader.Read())
{
object[] values = new object[fcount];
reader.GetValues(values);
T t = new T();
t.PropertyNames = entity.PropertyNames;
t.PropertyValues = values;
if (condition(t)) //根据条件过滤
{
list.Add(t);
}
}
}
return(list);
}
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);
}
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);
}
/// <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);
}
//模拟手写DataReader,尝试优化的方式，证明类型化读取器遇到装箱，效率较慢。
private static long EntityQuery2(AdoHelper db, System.Diagnostics.Stopwatch watch)
{
watch.Restart();
string sql = "select UserID, Name, Pwd, RegistedDate from Tb_User1";
//Action<IDataReader, int, object[]> readInt = (r, i, o) => { if (r.IsDBNull(i)) o[i] = DBNull.Value; else o[i] = r.GetInt32(i); };
//Action<IDataReader, int, object[]> readString = (r, i, o) => { if (r.IsDBNull(i)) o[i] = DBNull.Value; else o[i] = r.GetString(i); };
//Action<IDataReader, int, object[]> readDateTime = (r, i, o) => { if (r.IsDBNull(i)) o[i] = DBNull.Value; else o[i] = r.GetDateTime(i); };
//Action<IDataReader, int, object[]>[] readerActions = {
// readInt,readString,readString,readDateTime
//};
string tableName = "";
User entity = new User();
IDataReader reader = db.ExecuteDataReader(sql);
List <User> list = new List <User>();
using (reader)
{
if (reader.Read())
{
int fcount = reader.FieldCount;
string[] names = new string[fcount];
for (int i = 0; i < fcount; i++)
{
names[i] = reader.GetName(i);
}
User t0 = new User();
if (!string.IsNullOrEmpty(tableName))
{
t0.MapNewTableName(tableName);
}
//正式，下面放开
// t0.PropertyNames = names;
//
Action <int, object[]> readInt = (i, o) => { if (reader.IsDBNull(i))
{
o[i] = DBNull.Value;
}
else
{
o[i] = reader.GetInt32(i);
} };
Action <int, object[]> readString = (i, o) => { if (reader.IsDBNull(i))
{
o[i] = DBNull.Value;
}
else
{
o[i] = reader.GetString(i);
} };
Action <int, object[]> readDateTime = (i, o) => { if (reader.IsDBNull(i))
{
o[i] = DBNull.Value;
}
else
{
o[i] = reader.GetDateTime(i);
} };
Action <int, object[]>[] readerActions =
{
readInt, readString, readString, readDateTime
};
//
do
{
User item = (User)t0.Clone(false);
for (int i = 0; i < readerActions.Length; i++)
{
readerActions[i](i, item.PropertyValues);
}
list.Add(item);
} while (reader.Read());
}
}
//return list;
watch.Stop();
Console.WriteLine("EntityQuery2 List (10000 item) 耗时：(ms)" + watch.ElapsedMilliseconds);
return(watch.ElapsedMilliseconds);
}