private bool IsExistsDbNameWithCache(string dbName)
{
try
{
string key = dalType.ToString() + "." + dbName;
if (dbList.ContainsKey(key))
{
return(dbList[key]);
}
bool result = IsExistsDbName(dbName);
dbList.Add(key, result);
return(result);
}
catch
{
return(true);
}
}
private static DalBase GetDalBaseBy(ConnObject co)
{
DalType dalType = co.Master.ConnDalType;
//License.Check(providerName);//¿ò¼ÜÄ£¿éÊÚȨ¼ì²â¡£
switch (dalType)
{
case DalType.MsSql:
return(new MsSqlDal(co));
case DalType.Access:
return(new OleDbDal(co));
case DalType.Oracle:
return(new OracleDal(co));
case DalType.SQLite:
return(new SQLiteDal(co));
case DalType.MySql:
return(new MySQLDal(co));
case DalType.Sybase:
return(new SybaseDal(co));
case DalType.PostgreSQL:
return(new PostgreDal(co));
case DalType.Txt:
case DalType.Xml:
return(new NoSqlDal(co));
}
return((DalBase)Error.Throw(string.Format("GetHelper:{0} No Be Support Now!", dalType.ToString())));
}
public static string GetSql(DalType dalType, string version, int pageIndex, int pageSize, object objWhere, string tableName, int rowCount, string columns, string primaryKey, bool primaryKeyIsIdentity)
{
if (string.IsNullOrEmpty(columns))
{
columns = "*";
}
pageIndex = pageIndex == 0 ? 1 : pageIndex;
string where = SqlFormat.GetIFieldSql(objWhere);
if (string.IsNullOrEmpty(where))
{
where = "1=1";
}
if (pageSize == 0)
{
return(string.Format(top1Pager, columns, tableName, where));
}
if (rowCount > 0)//分页查询。
{
where = SqlCreate.AddOrderBy(where, primaryKey);
}
int topN = pageIndex * pageSize;//Top N 最大数
int max = (pageIndex - 1) * pageSize;
int rowStart = (pageIndex - 1) * pageSize + 1;
int rowEnd = rowStart + pageSize - 1;
string orderBy = string.Empty;
if (pageIndex == 1 && dalType != DalType.Oracle)//第一页（oracle时 rownum 在排序条件为非数字时，和row_number()的不一样，会导致结果差异，所以分页统一用row_number()。）
{
switch (dalType)
{
case DalType.Access:
case DalType.MsSql:
case DalType.Sybase:
return(string.Format(top1Pager, "top " + pageSize + " " + columns, tableName, where));
//case DalType.Oracle:
// return string.Format(top1Pager, columns, tableName, "rownum<=" + pageSize + " and " + where);
case DalType.SQLite:
case DalType.MySql:
return(string.Format(top1Pager, columns, tableName, where + " limit " + pageSize));
}
}
else
{
switch (dalType)
{
case DalType.Access:
case DalType.MsSql:
case DalType.Sybase:
int leftNum = rowCount % pageSize;
int pageCount = leftNum == 0 ? rowCount / pageSize : rowCount / pageSize + 1; //页数
if (pageIndex == pageCount && dalType != DalType.Sybase) // 最后一页Sybase 不支持双Top order by
{
return(string.Format(top2Pager, pageSize + " " + columns, "top " + (leftNum == 0 ? pageSize : leftNum) + " * ", tableName, ReverseOrderBy(where, primaryKey), GetOrderBy(where, false, primaryKey))); //反序
}
if ((pageCount > 1000 || rowCount > 100000) && pageIndex > pageCount / 2) // 页数过后半段，反转查询
{
orderBy = GetOrderBy(where, false, primaryKey);
where = ReverseOrderBy(where, primaryKey); //事先反转一次。
topN = rowCount - max; //取后面的
int rowStartTemp = rowCount - rowEnd;
rowEnd = rowCount - rowStart;
rowStart = rowStartTemp;
}
break;
}
}
switch (dalType)
{
case DalType.MsSql:
case DalType.Oracle:
if (version.StartsWith("08"))
{
goto temtable;
// goto top3;//sql 2000
}
int index = tableName.LastIndexOf(')');
if (index > 0)
{
tableName = tableName.Substring(0, index + 1);
}
string v = dalType == DalType.Oracle ? "" : " v";
string onlyWhere = "where " + SqlCreate.RemoveOrderBy(where);
onlyWhere = SqlFormat.RemoveWhereOneEqualsOne(onlyWhere);
return(string.Format(rowNumberPager, GetOrderBy(where, false, primaryKey), (columns == "*" ? "t.*" : columns), tableName, onlyWhere, v, rowStart, rowEnd));
case DalType.Sybase:
temtable:
if (primaryKeyIsIdentity)
{
bool isOk = columns == "*";
if (!isOk)
{
string kv = SqlFormat.NotKeyword(primaryKey);
string[] items = columns.Split(',');
foreach (string item in items)
{
if (string.Compare(SqlFormat.NotKeyword(item), kv, StringComparison.OrdinalIgnoreCase) == 0)
{
isOk = true;
break;
}
}
}
else
{
columns = "t.*";
index = tableName.LastIndexOf(')');
if (index > 0)
{
tableName = tableName.Substring(0, index + 1);
}
tableName += " t ";
}
if (isOk)
{
return(string.Format(tempTablePagerWithIdentity, DateTime.Now.Millisecond, topN, primaryKey, tableName, where, pageSize, columns, rowStart, rowEnd, orderBy));
}
}
return(string.Format(tempTablePager, DateTime.Now.Millisecond, pageIndex * pageSize + " " + columns, tableName, where, pageSize, rowStart, rowEnd, orderBy));
case DalType.Access:
top3:
if (!string.IsNullOrEmpty(orderBy)) // 反转查询
{
return(string.Format(top4Pager, columns, (rowCount - max > pageSize ? pageSize : rowCount - max), topN, tableName, where, GetOrderBy(where, true, primaryKey), GetOrderBy(where, false, primaryKey), orderBy));
}
return(string.Format(top3Pager, (rowCount - max > pageSize ? pageSize : rowCount - max), columns, topN, tableName, where, GetOrderBy(where, true, primaryKey), GetOrderBy(where, false, primaryKey)));
case DalType.SQLite:
case DalType.MySql:
if (max > 500000 && primaryKeyIsIdentity && Convert.ToString(objWhere) == "" && !tableName.Contains(" ")) //单表大数量时的优化成主键访问。
{
where = string.Format("{0}>=(select {0} from {1} limit {2}, 1) limit {3}", primaryKey, tableName, max, pageSize);
return(string.Format(top1Pager, columns, tableName, where));
}
return(string.Format(top1Pager, columns, tableName, where + " limit " + pageSize + " offset " + max));
}
return((string)Error.Throw("Pager::No Be Support:" + dalType.ToString()));
}
public static string GetSql(DalType dalType, string version, int pageIndex, int pageSize, object objWhere, string tableName, int rowCount, string columns, string primaryKey, bool primaryKeyIsIdentity)
{
if (string.IsNullOrEmpty(columns))
{
columns = "*";
}
pageIndex = pageIndex == 0 ? 1 : pageIndex;
string where = SqlFormat.GetIFieldSql(objWhere);
if (string.IsNullOrEmpty(where))
{
where = "1=1";
}
if (pageSize == 0)
{
return string.Format(top1Pager, columns, tableName, where);
}
if (rowCount > 0)//分页查询。
{
where = SqlCreate.AddOrderBy(where, primaryKey);
}
int topN = pageIndex * pageSize;//Top N 最大数
int max = (pageIndex - 1) * pageSize;
int rowStart = (pageIndex - 1) * pageSize + 1;
int rowEnd = rowStart + pageSize - 1;
string orderBy = string.Empty;
if (pageIndex == 1)//第一页
{
switch (dalType)
{
case DalType.Access:
case DalType.MsSql:
case DalType.Sybase:
return string.Format(top1Pager, "top " + pageSize + " " + columns, tableName, where);
case DalType.Oracle:
return string.Format(top1Pager, columns, tableName, "rownum<=" + pageSize + " and " + where);
case DalType.SQLite:
case DalType.MySql:
return string.Format(top1Pager, columns, tableName, where + " limit " + pageSize);
}
}
else
{
switch (dalType)
{
case DalType.Access:
case DalType.MsSql:
case DalType.Sybase:
int leftNum = rowCount % pageSize;
int pageCount = leftNum == 0 ? rowCount / pageSize : rowCount / pageSize + 1;//页数
if (pageIndex == pageCount && dalType != DalType.Sybase) // 最后一页Sybase 不支持双Top order by
{
return string.Format(top2Pager, pageSize, "top " + (leftNum == 0 ? pageSize : leftNum) + " " + columns, tableName, ReverseOrderBy(where, primaryKey), GetOrderBy(where, false, primaryKey));//反序
}
if ((pageCount > 1000 || rowCount > 100000) && pageIndex > pageCount / 2) // 页数过后半段，反转查询
{
orderBy = GetOrderBy(where, false, primaryKey);
where = ReverseOrderBy(where, primaryKey);//事先反转一次。
topN = rowCount - max;//取后面的
int rowStartTemp = rowCount - rowEnd;
rowEnd = rowCount - rowStart;
rowStart = rowStartTemp;
}
break;
}
}
switch (dalType)
{
case DalType.MsSql:
case DalType.Oracle:
if (version.StartsWith("08"))
{
goto temtable;
// goto top3;//sql 2000
}
int index = tableName.LastIndexOf(')');
if (index > 0)
{
tableName = tableName.Substring(0, index + 1);
}
string v = dalType == DalType.Oracle ? "" : " v";
string onlyWhere = "where " + SqlCreate.RemoveOrderBy(where);
onlyWhere = SqlFormat.RemoveWhereOneEqualsOne(onlyWhere);
return string.Format(rowNumberPager, GetOrderBy(where, false, primaryKey), (columns == "*" ? "t.*" : columns), tableName, onlyWhere, v, rowStart, rowEnd);
case DalType.Sybase:
temtable:
if (primaryKeyIsIdentity)
{
bool isOk = columns == "*";
if (!isOk)
{
string kv = SqlFormat.NotKeyword(primaryKey);
string[] items = columns.Split(',');
foreach (string item in items)
{
if (string.Compare(SqlFormat.NotKeyword(item), kv, StringComparison.OrdinalIgnoreCase) == 0)
{
isOk = true;
break;
}
}
}
else
{
columns = "t.*";
index = tableName.LastIndexOf(')');
if (index > 0)
{
tableName = tableName.Substring(0, index + 1);
}
tableName += " t ";
}
if (isOk)
{
return string.Format(tempTablePagerWithIdentity, DateTime.Now.Millisecond, topN, primaryKey, tableName, where, pageSize, columns, rowStart, rowEnd, orderBy);
}
}
return string.Format(tempTablePager, DateTime.Now.Millisecond, pageIndex * pageSize + " " + columns, tableName, where, pageSize, rowStart, rowEnd, orderBy);
case DalType.Access:
top3:
if (!string.IsNullOrEmpty(orderBy)) // 反转查询
{
return string.Format(top4Pager, (rowCount - max > pageSize ? pageSize : rowCount - max), topN + " " + columns, tableName, where, GetOrderBy(where, true, primaryKey), GetOrderBy(where, false, primaryKey), orderBy);
}
return string.Format(top3Pager, (rowCount - max > pageSize ? pageSize : rowCount - max), topN + " " + columns, tableName, where, GetOrderBy(where, true, primaryKey), GetOrderBy(where, false, primaryKey));
case DalType.SQLite:
case DalType.MySql:
return string.Format(top1Pager, columns, tableName, where + " limit " + pageSize + " offset " + max);
}
return (string)Error.Throw("Pager::No Be Support:" + dalType.ToString());
}
