/// <summary>
/// 获得链接的配置或语句。
/// </summary>
/// <param name="nameOrSql">表名、视图名、存储过程名</param>
/// <returns></returns>
public static string GetConn(string nameOrSql, out string fixName)
{
string firstTableName = null;
string conn = null;
fixName = nameOrSql;
int index = nameOrSql.LastIndexOf(')');
if (nameOrSql.IndexOf(' ') == -1)//单表。
{
#region 单表
if (nameOrSql.IndexOf('.') > -1) //dbname.tablename
{
string[] items = nameOrSql.Split('.');
conn = items[0] + "Conn";
fixName = items[items.Length - 1];
}
else
{
firstTableName = nameOrSql;
}
#endregion
}
else if (index == -1)
{
//sql 语句
firstTableName = GetFirstTableNameFromSql(nameOrSql);
fixName = SqlCreate.SqlToViewSql(nameOrSql);//Sql修正为视图
}
else// 视图
{
string viewSQL = nameOrSql;
string startSql = viewSQL.Substring(0, index + 1); //a部分
viewSQL = viewSQL.Substring(index + 1).Trim(); //b部分。ddd.v_xxx
if (viewSQL.Contains(".") && !viewSQL.Contains(" ")) //修改原对像
{
string[] items = viewSQL.Split('.');
fixName = startSql + " " + items[items.Length - 1];
conn = items[0] + "Conn";
}
else
{
firstTableName = GetFirstTableNameFromSql(startSql);
}
}
if (!string.IsNullOrEmpty(firstTableName))
{
TableInfo info = GetTableInfoByName(firstTableName);
if (info != null && info.Parent != null)
{
if (nameOrSql == firstTableName)
{
fixName = info.Name;
}
conn = info.Parent.ConnName;
}
}
return(conn);
}
public static string GetSql(DataBaseType 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 != DataBaseType.Oracle)//第一页（oracle时 rownum 在排序条件为非数字时，和row_number()的不一样，会导致结果差异，所以分页统一用row_number()。）
{
switch (dalType)
{
case DataBaseType.Access:
case DataBaseType.MsSql:
case DataBaseType.Sybase:
case DataBaseType.Txt:
case DataBaseType.Xml:
return(string.Format(top1Pager, "top " + pageSize + " " + columns, tableName, where));
//case DalType.Oracle:
// return string.Format(top1Pager, columns, tableName, "rownum<=" + pageSize + " and " + where);
case DataBaseType.SQLite:
case DataBaseType.MySql:
case DataBaseType.PostgreSQL:
return(string.Format(top1Pager, columns, tableName, where + " limit " + pageSize));
}
}
else
{
switch (dalType)
{
case DataBaseType.Access:
case DataBaseType.MsSql:
case DataBaseType.Sybase:
int leftNum = rowCount % pageSize;
int pageCount = leftNum == 0 ? rowCount / pageSize : rowCount / pageSize + 1; //页数
if (pageIndex == pageCount && dalType != DataBaseType.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);
if (dalType != DataBaseType.MsSql) //mssql是用rownumber，不用反转，
{
where = ReverseOrderBy(where, primaryKey); //事先反转一次。
}
topN = rowCount - max; //取后面的
int rowStartTemp = rowCount - rowEnd;
rowEnd = rowCount - rowStart + 1; //网友反馈修正（数据行要+1）
rowStart = rowStartTemp + 1; //网友反馈修正（数据行要+1）
}
break;
case DataBaseType.Txt:
case DataBaseType.Xml:
return(string.Format(top1Pager, columns, tableName, where + " limit " + pageSize + " offset " + pageIndex));
}
}
switch (dalType)
{
case DataBaseType.MsSql:
case DataBaseType.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 == DataBaseType.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 DataBaseType.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 DataBaseType.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 DataBaseType.SQLite:
case DataBaseType.MySql:
case DataBaseType.PostgreSQL:
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()));
}
/// <summary>
/// 获得最优（可能会切换数据库）链接的配置或语句。
/// </summary>
/// <param name="nameOrSql">表名、视图名、存储过程名</param>
/// <returns></returns>
public static string GetConn(string nameOrSql, out string fixName, string priorityConn, out string dbName)
{
string firstTableName = null;
string conn = null;
nameOrSql = nameOrSql.Trim();
fixName = nameOrSql;
dbName = string.Empty;
if (nameOrSql.IndexOf(' ') == -1)//单表。
{
#region 单表
if (nameOrSql.IndexOf('.') > -1) //dbname.tablename
{
string[] items = nameOrSql.Split('.');
dbName = items[0];
conn = dbName + "Conn";
fixName = SqlFormat.NotKeyword(items[items.Length - 1]);
}
else
{
firstTableName = SqlFormat.NotKeyword(nameOrSql);
}
#endregion
}
else
{
if (nameOrSql[0] == '(') // 视图
{
int index = nameOrSql.LastIndexOf(')');
string viewSQL = nameOrSql;
string startSql = viewSQL.Substring(0, index + 1); //a部分
viewSQL = viewSQL.Substring(index + 1).Trim(); //b部分。ddd.v_xxx
if (viewSQL.Contains(".") && !viewSQL.Contains(" ")) //修改原对像
{
string[] items = viewSQL.Split('.');
fixName = startSql + " " + items[items.Length - 1];
conn = items[0] + "Conn";
}
else
{
firstTableName = GetFirstTableNameFromSql(startSql);
}
}
else
{
//sql 语句
firstTableName = GetFirstTableNameFromSql(nameOrSql);
fixName = SqlCreate.SqlToViewSql(nameOrSql);//Sql修正为视图
}
}
if (!string.IsNullOrEmpty(firstTableName))
{
TableInfo info = GetTableInfoByName(firstTableName, priorityConn);
if (info != null && info.DBInfo != null)
{
if (nameOrSql == firstTableName)
{
fixName = info.Name;
}
conn = info.DBInfo.ConnName;
}
}
return(string.IsNullOrEmpty(conn) ? priorityConn : conn);
}
public static MDataColumn GetColumns(string tableName, ref DbBase dbHelper)
{
tableName = Convert.ToString(SqlCreate.SqlToViewSql(tableName));
DalType dalType = dbHelper.dalType;
tableName = SqlFormat.Keyword(tableName, dbHelper.dalType);
string key = GetSchemaKey(tableName, dbHelper.DataBase, dbHelper.dalType);
if (CacheManage.LocalInstance.Contains(key))//缓存里获取
{
return(CacheManage.LocalInstance.Get <MDataColumn>(key).Clone());
}
switch (dalType)
{
case DalType.SQLite:
case DalType.MySql:
tableName = SqlFormat.NotKeyword(tableName);
break;
case DalType.Txt:
case DalType.Xml:
tableName = Path.GetFileNameWithoutExtension(tableName); //视图表，带“.“的，会出问题
string fileName = dbHelper.Con.DataSource + tableName + (dalType == DalType.Txt ? ".txt" : ".xml");
MDataColumn mdc = MDataColumn.CreateFrom(fileName);
mdc.dalType = dalType;
return(mdc);
}
MDataColumn mdcs = new MDataColumn();
mdcs.dalType = dbHelper.dalType;
//如果table和helper不在同一个库
DbBase helper = dbHelper.ResetDbBase(tableName);
helper.IsAllowRecordSql = false;//内部系统，不记录SQL表结构语句。
try
{
bool isView = tableName.Contains(" ");//是否视图。
if (!isView)
{
isView = Exists("V", tableName, ref helper);
}
MCellStruct mStruct = null;
SqlDbType sqlType = SqlDbType.NVarChar;
if (isView)
{
string sqlText = SqlFormat.BuildSqlWithWhereOneEqualsTow(tableName);// string.Format("select * from {0} where 1=2", tableName);
mdcs = GetViewColumns(sqlText, ref helper);
}
else
{
mdcs.AddRelateionTableName(SqlFormat.NotKeyword(tableName));
switch (dalType)
{
case DalType.MsSql:
case DalType.Oracle:
case DalType.MySql:
case DalType.Sybase:
#region Sql
string sql = string.Empty;
if (dalType == DalType.MsSql)
{
string dbName = null;
if (!helper.Version.StartsWith("08"))
{
//先获取同义词，检测是否跨库
string realTableName = Convert.ToString(helper.ExeScalar(string.Format(SynonymsName, SqlFormat.NotKeyword(tableName)), false));
if (!string.IsNullOrEmpty(realTableName))
{
string[] items = realTableName.Split('.');
tableName = realTableName;
if (items.Length > 0) //跨库了
{
dbName = realTableName.Split('.')[0];
}
}
}
sql = GetMSSQLColumns(helper.Version.StartsWith("08"), dbName ?? helper.DataBase);
}
else if (dalType == DalType.MySql)
{
sql = GetMySqlColumns(helper.DataBase);
}
else if (dalType == DalType.Oracle)
{
sql = GetOracleColumns();
}
else if (dalType == DalType.Sybase)
{
tableName = SqlFormat.NotKeyword(tableName);
sql = GetSybaseColumns();
}
helper.AddParameters("TableName", tableName, DbType.String, 150, ParameterDirection.Input);
DbDataReader sdr = helper.ExeDataReader(sql, false);
if (sdr != null)
{
long maxLength;
bool isAutoIncrement = false;
short scale = 0;
string sqlTypeName = string.Empty;
while (sdr.Read())
{
short.TryParse(Convert.ToString(sdr["Scale"]), out scale);
if (!long.TryParse(Convert.ToString(sdr["MaxSize"]), out maxLength)) //mysql的长度可能大于int.MaxValue
{
maxLength = -1;
}
else if (maxLength > int.MaxValue)
{
maxLength = int.MaxValue;
}
sqlTypeName = Convert.ToString(sdr["SqlType"]);
sqlType = DataType.GetSqlType(sqlTypeName);
isAutoIncrement = Convert.ToBoolean(sdr["IsAutoIncrement"]);
mStruct = new MCellStruct(mdcs.dalType);
mStruct.ColumnName = Convert.ToString(sdr["ColumnName"]).Trim();
mStruct.OldName = mStruct.ColumnName;
mStruct.SqlType = sqlType;
mStruct.IsAutoIncrement = isAutoIncrement;
mStruct.IsCanNull = Convert.ToBoolean(sdr["IsNullable"]);
mStruct.MaxSize = (int)maxLength;
mStruct.Scale = scale;
mStruct.Description = Convert.ToString(sdr["Description"]);
mStruct.DefaultValue = SqlFormat.FormatDefaultValue(dalType, sdr["DefaultValue"], 0, sqlType);
mStruct.IsPrimaryKey = Convert.ToString(sdr["IsPrimaryKey"]) == "1";
switch (dalType)
{
case DalType.MsSql:
case DalType.MySql:
case DalType.Oracle:
mStruct.IsUniqueKey = Convert.ToString(sdr["IsUniqueKey"]) == "1";
mStruct.IsForeignKey = Convert.ToString(sdr["IsForeignKey"]) == "1";
mStruct.FKTableName = Convert.ToString(sdr["FKTableName"]);
break;
}
mStruct.SqlTypeName = sqlTypeName;
mStruct.TableName = SqlFormat.NotKeyword(tableName);
mdcs.Add(mStruct);
}
sdr.Close();
if (dalType == DalType.Oracle && mdcs.Count > 0) //默认没有自增概念，只能根据情况判断。
{
MCellStruct firstColumn = mdcs[0];
if (firstColumn.IsPrimaryKey && firstColumn.ColumnName.ToLower().Contains("id") && firstColumn.Scale == 0 && DataType.GetGroup(firstColumn.SqlType) == 1 && mdcs.JointPrimary.Count == 1)
{
firstColumn.IsAutoIncrement = true;
}
}
}
#endregion
break;
case DalType.SQLite:
#region SQlite
if (helper.Con.State != ConnectionState.Open)
{
helper.Con.Open();
}
DataTable sqliteDt = helper.Con.GetSchema("Columns", new string[] { null, null, tableName });
if (!helper.isOpenTrans)
{
helper.Con.Close();
}
int size;
short sizeScale;
string dataTypeName = string.Empty;
foreach (DataRow row in sqliteDt.Rows)
{
object len = row["NUMERIC_PRECISION"];
if (len == null)
{
len = row["CHARACTER_MAXIMUM_LENGTH"];
}
short.TryParse(Convert.ToString(row["NUMERIC_SCALE"]), out sizeScale);
if (!int.TryParse(Convert.ToString(len), out size)) //mysql的长度可能大于int.MaxValue
{
size = -1;
}
dataTypeName = Convert.ToString(row["DATA_TYPE"]);
if (dataTypeName == "text" && size > 0)
{
sqlType = DataType.GetSqlType("varchar");
}
else
{
sqlType = DataType.GetSqlType(dataTypeName);
}
//COLUMN_NAME,DATA_TYPE,PRIMARY_KEY,IS_NULLABLE,CHARACTER_MAXIMUM_LENGTH AUTOINCREMENT
mStruct = new MCellStruct(row["COLUMN_NAME"].ToString(), sqlType, Convert.ToBoolean(row["AUTOINCREMENT"]), Convert.ToBoolean(row["IS_NULLABLE"]), size);
mStruct.Scale = sizeScale;
mStruct.Description = Convert.ToString(row["DESCRIPTION"]);
mStruct.DefaultValue = SqlFormat.FormatDefaultValue(dalType, row["COLUMN_DEFAULT"], 0, sqlType); //"COLUMN_DEFAULT"
mStruct.IsPrimaryKey = Convert.ToBoolean(row["PRIMARY_KEY"]);
mStruct.SqlTypeName = dataTypeName;
mStruct.TableName = SqlFormat.NotKeyword(tableName);
mdcs.Add(mStruct);
}
#endregion
break;
case DalType.Access:
#region Access
DataTable keyDt, valueDt;
string sqlText = SqlFormat.BuildSqlWithWhereOneEqualsTow(tableName);// string.Format("select * from {0} where 1=2", tableName);
OleDbConnection con = new OleDbConnection(helper.Con.ConnectionString);
OleDbCommand com = new OleDbCommand(sqlText, con);
con.Open();
keyDt = com.ExecuteReader(CommandBehavior.KeyInfo).GetSchemaTable();
valueDt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, SqlFormat.NotKeyword(tableName) });
con.Close();
con.Dispose();
if (keyDt != null && valueDt != null)
{
string columnName = string.Empty, sqlTypeName = string.Empty;
bool isKey = false, isCanNull = true, isAutoIncrement = false;
int maxSize = -1;
short maxSizeScale = 0;
SqlDbType sqlDbType;
foreach (DataRow row in keyDt.Rows)
{
columnName = row["ColumnName"].ToString();
isKey = Convert.ToBoolean(row["IsKey"]); //IsKey
isCanNull = Convert.ToBoolean(row["AllowDBNull"]); //AllowDBNull
isAutoIncrement = Convert.ToBoolean(row["IsAutoIncrement"]);
sqlTypeName = Convert.ToString(row["DataType"]);
sqlDbType = DataType.GetSqlType(sqlTypeName);
short.TryParse(Convert.ToString(row["NumericScale"]), out maxSizeScale);
if (Convert.ToInt32(row["NumericPrecision"]) > 0) //NumericPrecision
{
maxSize = Convert.ToInt32(row["NumericPrecision"]);
}
else
{
long len = Convert.ToInt64(row["ColumnSize"]);
if (len > int.MaxValue)
{
maxSize = int.MaxValue;
}
else
{
maxSize = (int)len;
}
}
mStruct = new MCellStruct(columnName, sqlDbType, isAutoIncrement, isCanNull, maxSize);
mStruct.Scale = maxSizeScale;
mStruct.IsPrimaryKey = isKey;
mStruct.SqlTypeName = sqlTypeName;
mStruct.TableName = SqlFormat.NotKeyword(tableName);
foreach (DataRow item in valueDt.Rows)
{
if (columnName == item[3].ToString()) //COLUMN_NAME
{
if (item[8].ToString() != "")
{
mStruct.DefaultValue = SqlFormat.FormatDefaultValue(dalType, item[8], 0, sqlDbType); //"COLUMN_DEFAULT"
}
break;
}
}
mdcs.Add(mStruct);
}
}
#endregion
break;
}
}
helper.ClearParameters();
}
catch (Exception err)
{
helper.debugInfo.Append(err.Message);
}
finally
{
helper.IsAllowRecordSql = true;//恢复记录SQL表结构语句。
if (helper != dbHelper)
{
helper.Dispose();
}
}
if (mdcs.Count > 0)
{
//移除被标志的列：
string[] fields = AppConfig.DB.HiddenFields.Split(',');
foreach (string item in fields)
{
string field = item.Trim();
if (!string.IsNullOrEmpty(field) & mdcs.Contains(field))
{
mdcs.Remove(field);
}
}
}
if (!CacheManage.LocalInstance.Contains(key))
{
CacheManage.LocalInstance.Add(key, mdcs.Clone());
}
return(mdcs);
}