/// <summary>
/// 解释EndWith条件
/// </summary>
/// <param name="list"></param>
/// <param name="entityType"></param>
/// <param name="paramName"></param>
/// <param name="type"></param>
/// <param name="lstIndex"></param>
public static string EndWith(Scope scope, ParamList list, Type entityType, string paramName, DbType type, int lstIndex)
{
DBInfo db = EntityInfoManager.GetEntityHandle(entityType).DBInfo;
string paramVal = db.CurrentDbAdapter.FormatValueName(DataAccessCommon.FormatParam(paramName, lstIndex));
string paramKey = db.CurrentDbAdapter.FormatParamKeyName(DataAccessCommon.FormatParam(paramName, lstIndex));
StringBuilder sql = new StringBuilder(500);
ScopeType ctype = scope.ScopeType;
string connectString = DataAccessCommon.GetConnectString(scope);
if (list != null)
{
sql.Append(" ");
sql.Append(connectString);
sql.Append(" (" + db.CurrentDbAdapter.FormatParam(paramName) + " like " + db.CurrentDbAdapter.ConcatString("'%'", paramVal) + ")");
list.AddNew(paramKey, type, scope.Value1);
}
else
{
sql.Append(" ");
sql.Append(connectString);
string curValue = scope.Value1.ToString();
sql.Append(" (" + db.CurrentDbAdapter.FormatParam(paramName) + " like '%" + scope.Value1 + "')");
}
return(sql.ToString());
}
/// <summary>
/// 解释MoreThen条件
/// </summary>
/// <param name="list"></param>
/// <param name="entityType"></param>
/// <param name="paramName"></param>
/// <param name="type"></param>
/// <param name="lstIndex"></param>
public static string MoreThen(Scope scope, ParamList list, Type entityType, string paramName, DbType type, int lstIndex)
{
DBInfo db = EntityInfoManager.GetEntityHandle(entityType).DBInfo;
string paramVal = db.CurrentDbAdapter.FormatValueName(DataAccessCommon.FormatParam(paramName, lstIndex));
string paramKey = db.CurrentDbAdapter.FormatParamKeyName(DataAccessCommon.FormatParam(paramName, lstIndex));
StringBuilder sql = new StringBuilder(500);
ScopeType ctype = scope.ScopeType;
string connectString = DataAccessCommon.GetConnectString(scope);
if (list != null)
{
sql.Append(" ");
sql.Append(connectString);
sql.Append(" (");
sql.Append(db.CurrentDbAdapter.FormatParam(paramName));
sql.Append(" >= ");
sql.Append(paramVal);
sql.Append(")");
list.AddNew(paramKey, type, scope.Value1);
}
else
{
sql.Append(" ");
sql.Append(connectString);
sql.Append(" (");
sql.Append(db.CurrentDbAdapter.FormatParam(paramName));
sql.Append(" >= ");
sql.Append(DataAccessCommon.FormatValue(scope.Value1, type, db));
sql.Append(")");
}
return(sql.ToString());
}
/// <summary>
/// 查询并且返回DataSet(游标分页)
/// </summary>
/// <param name="sql">要查询的SQL语句</param>
/// <param name="objPage">分页对象</param>
/// <param name="oper">数据库对象</param>
/// <param name="curType">映射的实体类型(如果用回数据库的原列名，则此为null)</param>
/// <returns></returns>
public static DataTable QueryDataTable(string sql, PageContent objPage, DataBaseOperate oper, Type curType)
{
DataTable ret = new DataTable();
ParamList lstParams = new ParamList();
lstParams.AddNew("@sql", DbType.AnsiString, sql);
lstParams.AddNew("@currentIndex", DbType.Int32, objPage.GetStarIndex() + 1);
lstParams.AddNew("@pagesize", DbType.Int32, objPage.PageSize);
lstParams.AddNew("@maxRecords", DbType.Int64, objPage.MaxSelectRecords);
IDataReader reader = null;
try
{
InitProc(oper);
reader = oper.Query(ProcName, lstParams, CommandType.StoredProcedure, null);
if (reader.NextResult())//第二个结果集为查询结果
{
if (curType == null)
{
ret = CacheReader.GenerateDataTable(reader, "newDt", false);
}
else
{
ret = CacheReader.GenerateDataTable(reader, "newDt", curType, false);
}
}
if (reader.NextResult())//第三个结果集为总行数
{
if (reader.Read())
{
int totalRecord = reader.GetInt32(0);
objPage.TotalRecords = totalRecord;
//int totalPage = (int)Math.Ceiling((double)objPage.TotalRecords / (double)objPage.PageSize);
//objPage.TotalPage = totalPage;
if (objPage.CurrentPage >= objPage.TotalPage - 1)
{
objPage.CurrentPage = objPage.TotalPage - 1;
}
}
}
}
finally
{
reader.Close();
}
return(ret);
}
/// <summary>
/// 获取所有关系
/// </summary>
/// <param name="oper"> </param>
/// <param name="info"> </param>
/// <param name="childNames">null则查询所有表</param>
/// <returns></returns>
public List <TableRelationAttribute> GetRelation(DataBaseOperate oper, DBInfo info, IEnumerable <string> childNames)
{
string sql = "SELECT t1.CONSTRAINT_NAME,t1.TABLE_NAME, t1.COLUMN_NAME, t1.POSITION_IN_UNIQUE_CONSTRAINT, t1.REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE t1 INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS t2 ON t2.TABLE_SCHEMA = t1.TABLE_SCHEMA AND t2.TABLE_NAME = t1.TABLE_NAME AND t2.CONSTRAINT_NAME = t1.CONSTRAINT_NAME WHERE t1.TABLE_SCHEMA = ?dbName AND t2.CONSTRAINT_TYPE = 'FOREIGN KEY'";
StringBuilder sqlFk = new StringBuilder();
StringBuilder sqlPk = new StringBuilder();
//sql.Append("SELECT constraint_schema,constraint_name,unique_constraint_name,table_name,referenced_table_name FROM `information_schema`.`REFERENTIAL_CONSTRAINTS`;");
sqlFk.Append(sql);
sqlPk.Append(sql);
ParamList lstParam = new ParamList();
lstParam.AddNew("?dbName", DbType.String, oper.DataBaseName);
string childName = Buffalo.DB.DataBaseAdapter.SqlServer2KAdapter.DBStructure.AllInTableNames(childNames);
if (!string.IsNullOrEmpty(childName))
{
sqlFk.Append(" and t1.TABLE_NAME in(" + childName + ")");
}
if (!string.IsNullOrEmpty(childName))
{
sqlPk.Append(" and t1.REFERENCED_TABLE_NAME in(" + childName + ")");
}
List <TableRelationAttribute> lst = new List <TableRelationAttribute>();
using (IDataReader reader = info.DefaultOperate.Query(sqlFk.ToString(), lstParam, null))
{
while (reader.Read())
{
TableRelationAttribute tinfo = new TableRelationAttribute();
tinfo.Name = reader["CONSTRAINT_NAME"] as string;
tinfo.SourceTable = reader["TABLE_NAME"] as string;
tinfo.SourceName = reader["COLUMN_NAME"] as string;
tinfo.TargetTable = reader["REFERENCED_TABLE_NAME"] as string;
tinfo.TargetName = reader["REFERENCED_COLUMN_NAME"] as string;
tinfo.IsParent = true;
lst.Add(tinfo);
}
}
using (IDataReader reader = info.DefaultOperate.Query(sqlPk.ToString(), lstParam, null))
{
while (reader.Read())
{
TableRelationAttribute tinfo = new TableRelationAttribute();
tinfo.Name = reader["CONSTRAINT_NAME"] as string;
tinfo.TargetTable = reader["TABLE_NAME"] as string;
tinfo.TargetName = reader["COLUMN_NAME"] as string;
tinfo.SourceTable = reader["REFERENCED_TABLE_NAME"] as string;
tinfo.SourceName = reader["REFERENCED_COLUMN_NAME"] as string;
tinfo.IsParent = false;
lst.Add(tinfo);
}
}
return(lst);
}
/// <summary>
/// 返回Like的查询字符串
/// </summary>
/// <param name="scope">条件类</param>
/// <param name="list">参数列表</param>
/// <param name="paranName">所属的字段名</param>
/// <param name="type">当前的数据库类型</param>
/// <param name="lstIndex">当前索引的标识未辨别同名字段的参数，可设置为0</param>
/// <param name="entityType">当前实体的类型</param>
/// <param name="connectString">条件连接的字符串</param>
/// <param name="isFreeText">是否全文检索</param>
/// <returns></returns>
public static string GetLikeSql(Scope scope, ParamList list, string paranName, DbType type, int lstIndex, Type entityType, string connectString, bool isFreeText)
{
//string fullName = entityType.FullName + "." + scope.PropertyName;
DBInfo db = EntityInfoManager.GetEntityHandle(entityType).DBInfo;
string ret = " " + connectString;
string paramVal = db.CurrentDbAdapter.FormatValueName(DataAccessCommon.FormatParam(paranName, lstIndex));
string paramKey = db.CurrentDbAdapter.FormatParamKeyName(DataAccessCommon.FormatParam(paranName, lstIndex));
if (isFreeText && !NoiseWord.IsNoiseWord(scope.Value1.ToString()))
{
if (list != null)
{
ret += db.CurrentDbAdapter.FreeTextLike(paranName, paramVal);
list.AddNew(paramKey, type, scope.Value1);
}
else
{
ret += db.CurrentDbAdapter.FreeTextLike(paranName, DataAccessCommon.FormatValue(scope.Value1, type, db));
}
}
else
{
if (list != null)
{
string curValue = scope.Value1.ToString();
curValue = FilterLikeValue(curValue);
ret += " (" + db.CurrentDbAdapter.FormatParam(paranName) + " like " + db.CurrentDbAdapter.ConcatString("'%'", paramVal, "'%'") + ")";
list.AddNew(paramKey, type, curValue);
}
else
{
string curValue = scope.Value1.ToString();
curValue = FilterLikeValue(curValue);
ret += " (" + db.CurrentDbAdapter.FormatParam(paranName) + " like '%" + curValue + "%')";
}
}
return(ret);
}
/// <summary>
/// 查询并且返回集合(游标分页)
/// </summary>
/// <param name="sql">要查询的SQL语句</param>
/// <param name="objPage">分页对象</param>
/// <param name="oper">数据库对象</param>
/// <returns></returns>
public static IDataReader Query(string sql, PageContent objPage, DataBaseOperate oper)
{
ParamList lstParams = new ParamList();
lstParams.AddNew("@sql", DbType.AnsiString, sql);
lstParams.AddNew("@currentIndex", DbType.Int32, objPage.GetStarIndex() + 1);
lstParams.AddNew("@pagesize", DbType.Int32, objPage.PageSize);
lstParams.AddNew("@maxRecords", DbType.Int64, objPage.MaxSelectRecords);
//lstParams.AddNew("@@total", DbType.Int64, 0, ParameterDirection.Output);
IDataReader reader = null;
InitProc(oper);
reader = oper.Query(ProcName, lstParams, CommandType.StoredProcedure, null);
if (reader.NextResult())//第二个结果集为查询记录数
{
if (objPage.IsFillTotalRecords)
{
if (reader.Read())
{
int totalRecord = reader.GetInt32(0);
objPage.TotalRecords = totalRecord;
//int totalPage = (int)Math.Ceiling((double)objPage.TotalRecords / (double)objPage.PageSize);
//objPage.TotalPage = totalPage;
if (objPage.CurrentPage >= objPage.TotalPage - 1)
{
objPage.CurrentPage = objPage.TotalPage - 1;
}
}
}
}
if (reader.NextResult())//第三个结果集为真正记录
{
return(reader);
}
return(null);
}
/// <summary>
/// 获取表信息
/// </summary>
/// <param name="oper"></param>
/// <param name="info"></param>
/// <param name="tableNames"></param>
/// <returns></returns>
public List <DBTableInfo> GetTablesInfo(DataBaseOperate oper, DBInfo info, IEnumerable <string> tableNames)
{
string inTable = Buffalo.DB.DataBaseAdapter.SqlServer2KAdapter.DBStructure.AllInTableNames(tableNames);
string sql = "SELECT t1.TABLE_NAME,t1.COLUMN_NAME,t1.COLUMN_COMMENT, t1.DATA_TYPE, t1.CHARACTER_OCTET_LENGTH, t1.NUMERIC_PRECISION, t1.NUMERIC_SCALE, CASE t1.IS_NULLABLE WHEN 'NO' THEN 0 ELSE 1 END IS_NULLABLE, t1.COLUMN_TYPE,t1.COLUMN_KEY,t1.EXTRA FROM INFORMATION_SCHEMA.COLUMNS t1 where t1.TABLE_SCHEMA = ?dbName";
if (!string.IsNullOrEmpty(inTable))
{
sql += " and t1.TABLE_NAME in(" + inTable + ")";
}
Dictionary <string, DBTableInfo> dicTables = new Dictionary <string, DBTableInfo>();
ParamList lstParam = new ParamList();
lstParam.AddNew("?dbName", DbType.String, oper.DataBaseName);
List <DBTableInfo> tables = GetTableNames(oper, info, tableNames);
foreach (DBTableInfo table in tables)
{
dicTables[table.Name] = table;
table.Params = new List <EntityParam>();
table.RelationItems = new List <TableRelationAttribute>();
}
using (IDataReader reader = oper.Query(sql.ToString(), lstParam, null))
{
while (reader.Read())
{
string tableName = reader["TABLE_NAME"] as string;
if (string.IsNullOrEmpty(tableName))
{
continue;
}
DBTableInfo table = null;
dicTables.TryGetValue(tableName, out table);
if (table == null)
{
continue;
}
FillParam(table, reader);
}
}
List <TableRelationAttribute> lstRelation = GetRelation(oper, info, tableNames);
Buffalo.DB.DataBaseAdapter.SqlServer2KAdapter.DBStructure.FillRelation(dicTables, lstRelation);
return(tables);
}
/// <summary>
/// 填充版本控制的信息
/// </summary>
/// <param name="where"></param>
/// <param name="where"></param>
/// <param name="info"></param>
/// <param name="list"></param>
/// <param name="curValue"></param>
protected internal void FillWhereConcurrency(StringBuilder where,
EntityPropertyInfo info, ParamList list, object curValue, ref int index)
{
string paramValW = EntityInfo.DBInfo.CurrentDbAdapter.FormatValueName(DataAccessCommon.FormatParam(info.ParamName, index));
string paramKeyW = EntityInfo.DBInfo.CurrentDbAdapter.FormatParamKeyName(DataAccessCommon.FormatParam(info.ParamName, index));
index++;
if (DefaultType.IsDefaultValue(curValue))
{
throw new Exception("版本控制字段:" + info.PropertyName + " 必须有当前版本值");
}
where.Append(" and ");
where.Append(EntityInfo.DBInfo.CurrentDbAdapter.FormatParam(info.ParamName));
where.Append("=");
where.Append(paramValW);
list.AddNew(paramKeyW, info.SqlType, curValue);
}
private object FillUpdateConcurrency(StringBuilder sql,
EntityPropertyInfo info, ParamList list, object curValue, ref int index)
{
object newValue = NewConcurrencyValue(curValue);
if (newValue != null)
{
string paramValV = EntityInfo.DBInfo.CurrentDbAdapter.FormatValueName(DataAccessCommon.FormatParam(info.ParamName, index));
string paramKeyV = EntityInfo.DBInfo.CurrentDbAdapter.FormatParamKeyName(DataAccessCommon.FormatParam(info.ParamName, index));
sql.Append(",");
sql.Append(EntityInfo.DBInfo.CurrentDbAdapter.FormatParam(info.ParamName));
sql.Append("=");
sql.Append(paramValV);
list.AddNew(paramKeyV, info.SqlType, newValue);
index++;
}
return(newValue);
}
/// <summary>
/// 重建参数集合
/// </summary>
/// <param name="lstPrm"></param>
/// <returns></returns>
public virtual ParamList RebuildParamList(ref string sql, ParamList lstPrm)
{
ParamList lstRet = new ParamList();
StringBuilder newSql = new StringBuilder();
Dictionary <string, DBParameter> dicPrm = new Dictionary <string, DBParameter>();
foreach (DBParameter prm in lstPrm)
{
dicPrm[prm.ParameterName] = prm;
}
Queue <RebuildParamInfo> queStrPrm = FindAllParams(sql);
DBParameter curPrm = null;
RebuildParamInfo curprmInfo = null;
if (queStrPrm.Count > 0)
{
curprmInfo = queStrPrm.Dequeue();
}
for (int i = 0; i < sql.Length; i++)
{
if (curprmInfo != null && curprmInfo.Index == i)
{
if (dicPrm.TryGetValue(curprmInfo.ParamName, out curPrm))
{
string pName = "P" + lstRet.Count;
DBParameter newPrm = lstRet.AddNew(FormatParamKeyName(pName), curPrm.DbType, curPrm.Value, curPrm.Direction);
newPrm.ValueName = FormatValueName(pName);
newSql.Append(newPrm.ValueName);
i += curprmInfo.ParamName.Length - 1;
if (queStrPrm.Count > 0)
{
curprmInfo = queStrPrm.Dequeue();
}
continue;
}
}
newSql.Append(sql[i]);
}
sql = newSql.ToString();
return(lstRet);
}
/// <summary>
/// 获取所有用户表
/// </summary>
/// <param name="oper"></param>
/// <param name="info"></param>
/// <returns></returns>
private List <DBTableInfo> GetTableNames(DataBaseOperate oper, DBInfo info, IEnumerable <string> tableNames)
{
ParamList lstParam = new ParamList();
lstParam.AddNew("?dbName", DbType.String, oper.DataBaseName);
List <DBTableInfo> lstName = new List <DBTableInfo>();
string inTable = Buffalo.DB.DataBaseAdapter.SqlServer2KAdapter.DBStructure.AllInTableNames(tableNames);
string sql = _sqlTables;
if (!string.IsNullOrEmpty(inTable))
{
sql += " and TABLE_NAME in(" + inTable + ")";
}
using (IDataReader reader = oper.Query(sql, lstParam, null))
{
while (reader.Read())
{
DBTableInfo tableInfo = new DBTableInfo();
tableInfo.Name = reader[0] as string;
string type = reader[1] as string;
if (!string.IsNullOrEmpty(type))
{
if (type.Trim() == "VIEW")
{
tableInfo.IsView = true;
}
}
string comment = reader[2] as string;
tableInfo.Description = comment;
lstName.Add(tableInfo);
}
}
return(lstName);
}
