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, dalType);
}
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));
case DataBaseType.DB2:
return(string.Format(top1Pager, columns, tableName, where + " fetch first " + pageSize + " rows only"));
}
}
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 (dalType != DataBaseType.MsSql && (pageCount > 1000 || rowCount > 100000) && pageIndex > pageCount / 2) // 页数过后半段，反转查询
{
//mssql rownumber 的语句
orderBy = GetOrderBy(where, false, primaryKey);
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:
case DataBaseType.DB2: //
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>
/// 返回不包括Where条件的字符串
/// </summary>
/// <returns>结果如:Update tableName set Name=@Name,Value=@Value</returns>
internal string GetUpdateSql(object whereObj)
{
isCanDo = false;
StringBuilder _TempSql = new StringBuilder();
_TempSql.Append("Update " + TableName + " set ");
if (!string.IsNullOrEmpty(updateExpression))
{
_TempSql.Append(SqlCompatible.Format(updateExpression, _action.DataBaseType) + ",");
updateExpression = null;//取完值后清除值。
isCanDo = true;
}
string editTime = GetEditTimeSql();//内部判断该字段没有值才会更新。
if (!string.IsNullOrEmpty(editTime))
{
_TempSql.Append(editTime);//自带尾,号
}
MDataCell cell = null;
for (int i = 0; i < _action.Data.Count; i++)
{
cell = _action.Data[i];
if (cell.Struct.IsPrimaryKey || cell.Struct.IsAutoIncrement)
{
continue;//跳过自增或主键列。
}
if (cell.State > 1 && (cell.Struct.IsCanNull || !cell.IsNull))
{
if (cell.Struct.SqlType == SqlDbType.Timestamp && (_action.DataBaseType == DataBaseType.MsSql || _action.DataBaseType == DataBaseType.Sybase))
{
//更新时间戳不允许更新。
continue;
}
object value = cell.Value;
DbType dbType = DataType.GetDbType(cell.Struct.SqlType.ToString(), _action.DataBaseType);
if (dbType == DbType.String && cell.StringValue == "")
{
if (_action.DataBaseType == DataBaseType.Oracle && !cell.Struct.IsCanNull)
{
value = " ";//Oracle not null 字段，不允许设置空值。
}
if (_action.DataBaseType == DataBaseType.MySql && cell.Struct.MaxSize == 36)
{
value = DBNull.Value;//MySql 的char36 会当成guid处理，不能为空，只能为null。
}
}
_action.dalHelper.AddParameters(_action.dalHelper.Pre + cell.ColumnName, value, dbType, cell.Struct.MaxSize, ParameterDirection.Input);
_TempSql.Append(SqlFormat.Keyword(cell.ColumnName, _action.DataBaseType) + "=" + _action.dalHelper.Pre + cell.ColumnName + ",");
isCanDo = true;
}
}
if (!isCanDo)
{
string err = AppConst.HR + "warn : " + TableName + " can't find the data can be updated!";
Log.Write(err, LogType.Warn);
_action.dalHelper.DebugInfo.Append(err);
}
//switch (_action.dalHelper.dalType)
//{
// case DalType.Oracle:
// case DalType.SQLite:
// _TempSql = _TempSql.Replace("[", "").Replace("]", "");
// break;
// case DalType.MySql:
// _TempSql = _TempSql.Replace("[", "`").Replace("]", "`");
// break;
//}
_TempSql = _TempSql.Remove(_TempSql.Length - 1, 1);
_TempSql.Append(" where " + FormatWhere(whereObj));
return(_TempSql.ToString());
}
private string GetWhereFromObj(object whereObj)
{
if (whereObj == null)
{
return(string.Empty);
}
else if (whereObj is String || (whereObj is ValueType && !(whereObj is Enum)))
{
return(Convert.ToString(whereObj));
}
else if (whereObj is IField)
{
return(SqlFormat.GetIFieldSql(whereObj));
}
MDataCell cell = null;
if (whereObj is Enum)
{
cell = _action.Data[(int)whereObj];
}
else if (whereObj is MDataCell)
{
cell = whereObj as MDataCell;
}
else
{
string propName = MBindUI.GetID(whereObj);
if (!string.IsNullOrEmpty(propName))
{
_action.UI.Get(whereObj, null, null);
cell = _action.Data[propName];
}
}
string where = string.Empty;
if (cell != null)
{
#region 从单元格里取值。
if (cell.IsNullOrEmpty)
{
isCanDo = false;
_action.dalHelper.RecordsAffected = -2;
_action.dalHelper.DebugInfo.Append(AppConst.HR + "error : " + cell.ColumnName + " can't be null" + AppConst.BR);
return("1=2 and " + cell.ColumnName + " is null");
}
switch (_action.dalHelper.DataBaseType)
{
case DataBaseType.Txt:
case DataBaseType.Xml:
switch (DataType.GetGroup(cell.Struct.SqlType))
{
case 1:
case 3:
where = cell.ColumnName + "=" + cell.Value;
break;
default:
where = cell.ColumnName + "='" + cell.Value + "'";
break;
}
break;
default:
where = cell.ColumnName + "=" + _action.dalHelper.Pre + cell.ColumnName;
_action.dalHelper.AddParameters(cell.ColumnName, cell.Value, DataType.GetDbType(cell.Struct.ValueType), cell.Struct.MaxSize, ParameterDirection.Input);
break;
}
#endregion
}
return(where);
}
/// <summary>
/// 获取指定的表架构生成的SQL(Create Table)的说明语句
/// </summary>
internal static string CreateTableDescriptionSql(string tableName, MDataColumn columns, DataBaseType dalType)
{
string result = string.Empty;
switch (dalType)
{
case DataBaseType.MsSql:
case DataBaseType.Oracle:
case DataBaseType.PostgreSQL:
case DataBaseType.MySql:
case DataBaseType.DB2:
StringBuilder sb = new StringBuilder();
foreach (MCellStruct mcs in columns)
{
if (!string.IsNullOrEmpty(mcs.Description))
{
if (dalType == DataBaseType.MsSql)
{
sb.AppendFormat("exec sp_addextendedproperty N'MS_Description', N'{0}', N'user', N'dbo', N'table', N'{1}', N'column', N'{2}';\r\n", mcs.Description, tableName, mcs.ColumnName);
}
else if (dalType == DataBaseType.Oracle || dalType == DataBaseType.DB2)
{
sb.AppendFormat("comment on column {0}.{1} is '{2}';\r\n", tableName.ToUpper(), mcs.ColumnName.ToUpper(), mcs.Description);
}
else if (dalType == DataBaseType.PostgreSQL)
{
sb.AppendFormat("comment on column {0}.{1} is '{2}';\r\n",
SqlFormat.Keyword(tableName, DataBaseType.PostgreSQL), SqlFormat.Keyword(mcs.ColumnName, DataBaseType.PostgreSQL), mcs.Description);
}
}
}
if (dalType == DataBaseType.MsSql) //增加表的描述
{
sb.AppendFormat("exec sp_addextendedproperty N'MS_Description', N'{0}', N'user', N'dbo', N'table', N'{1}';\r\n", columns.Description, tableName);
}
else if (dalType == DataBaseType.Oracle || dalType == DataBaseType.DB2)
{
sb.AppendFormat("comment on table {0} is '{1}';\r\n",
tableName.ToUpper(), columns.Description);
}
else if (dalType == DataBaseType.PostgreSQL)
{
sb.AppendFormat("comment on table {0} is '{1}';\r\n",
SqlFormat.Keyword(tableName, DataBaseType.PostgreSQL), columns.Description);
}
else if (dalType == DataBaseType.MySql)
{
sb.AppendFormat("alter table {0} comment = '{1}';\r\n", SqlFormat.Keyword(tableName, DataBaseType.MySql), columns.Description);
}
result = sb.ToString().TrimEnd(';');
break;
}
return(result);
}
/// <summary>
/// 返回插入的字符串
/// </summary>
/// <returns>结果如:insert into tableName(id,Name,Value) values(@id,@Name,@Value)</returns>
internal string GetInsertSql()
{
isCanDo = false;
StringBuilder _TempSql = new StringBuilder();
StringBuilder _TempSql2 = new StringBuilder();
_TempSql.Append("insert into " + TableName + "(");
_TempSql2.Append(") Values(");
MDataCell primaryCell = _action.Data[_action.Data.Columns.FirstPrimary.ColumnName];
int groupID = DataType.GetGroup(primaryCell.Struct.SqlType);
string defaultValue = Convert.ToString(primaryCell.Struct.DefaultValue);
if (primaryCell.IsNullOrEmpty && (groupID == 4 || (groupID == 0 && (primaryCell.Struct.MaxSize <= 0 || primaryCell.Struct.MaxSize >= 36) &&
(defaultValue == "" || defaultValue == "newid" || defaultValue == SqlValue.Guid))))//guid类型
{
primaryCell.Value = Guid.NewGuid();
}
MDataCell cell = null;
for (int i = 0; i < _action.Data.Count; i++)
{
cell = _action.Data[i];
if (cell.Struct.IsAutoIncrement && !_action.AllowInsertID)
{
continue;//跳过自增列。
}
if (cell.IsNull && !cell.Struct.IsCanNull && cell.Struct.DefaultValue == null)
{
string err = AppConst.HR + string.Format("error : {0} {1} can't be insert null", TableName, cell.ColumnName) + AppConst.BR;
Log.Write(err, LogType.DataBase);
_action.dalHelper.DebugInfo.Append(err);
_action.dalHelper.RecordsAffected = -2;
isCanDo = false;
break;
}
if (cell.State > 0)
{
_TempSql.Append(SqlFormat.Keyword(cell.ColumnName, _action.DataBaseType) + ",");
_TempSql2.Append(_action.dalHelper.Pre + cell.ColumnName + ",");
object value = cell.Value;
DbType dbType = DataType.GetDbType(cell.Struct.SqlType.ToString(), _action.DataBaseType);
if (dbType == DbType.String && cell.StringValue == "")
{
if (_action.DataBaseType == DataBaseType.Oracle && !cell.Struct.IsCanNull)
{
value = " ";//Oracle not null 字段，不允许设置空值。
}
if (_action.DataBaseType == DataBaseType.MySql && cell.Struct.MaxSize == 36)
{
value = DBNull.Value;//MySql 的char36 会当成guid处理，不能为空，只能为null。
}
}
_action.dalHelper.AddParameters(_action.dalHelper.Pre + cell.ColumnName, value, dbType, cell.Struct.MaxSize, ParameterDirection.Input);
isCanDo = true;
}
}
switch (_action.dalHelper.DataBaseType)
{
case DataBaseType.Oracle:
if (!_action.AllowInsertID && DataType.GetGroup(primaryCell.Struct.SqlType) == 1)
{
_TempSql.Append(primaryCell.ColumnName + ",");
_TempSql2.Append(AutoID + ".nextval,");
}
break;
}
string sql = _TempSql.ToString().TrimEnd(',') + _TempSql2.ToString().TrimEnd(',') + ")";
switch (_action.dalHelper.DataBaseType)
{
case DataBaseType.PostgreSQL:
if (primaryCell.Struct.IsAutoIncrement && !_action.AllowInsertID && groupID == 1)
{
string key = Convert.ToString(primaryCell.Struct.DefaultValue);
if (!string.IsNullOrEmpty(key))
{
key = key.Replace("nextval", "currval");
sql = sql + "; select " + key + " as OutPutValue";
}
}
else if (!primaryCell.IsNullOrEmpty)
{
sql += string.Format("; select '{0}' as OutPutValue", primaryCell.Value);
}
break;
case DataBaseType.MsSql:
case DataBaseType.Sybase:
if (primaryCell.Struct.IsAutoIncrement && !_action.AllowInsertID && groupID == 1)
{
if (_action.dalHelper.DataBaseType == DataBaseType.Sybase)
{
sql = sql + " select @@idENTITY as OutPutValue";
}
else if (_action.dalHelper.DataBaseType == DataBaseType.MsSql)
{
sql += " select cast(scope_identity() as bigint) as OutPutValue"; //改成bigint避免转换数据溢出
}
}
else if (!primaryCell.IsNullOrEmpty)
{
sql += string.Format(" select '{0}' as OutPutValue", primaryCell.Value);
}
if (_action.AllowInsertID && !_action.dalHelper.IsOpenTrans && primaryCell.Struct.IsAutoIncrement) //非批量操作时
{
sql = "set identity_insert " + SqlFormat.Keyword(TableName, _action.dalHelper.DataBaseType) + " on " + sql + " set identity_insert " + SqlFormat.Keyword(TableName, _action.dalHelper.DataBaseType) + " off";
}
break;
//if (!(Parent.AllowInsertID && !primaryCell.IsNull)) // 对于自行插入id的，跳过，主操作会自动返回id。
//{
// sql += ((groupID == 1 && (primaryCell.IsNull || primaryCell.ToString() == "0")) ? " select cast(scope_identity() as int) as OutPutValue" : string.Format(" select '{0}' as OutPutValue", primaryCell.Value));
//}
//case DalType.Oracle:
// sql += string.Format("BEGIN;select {0}.currval from dual; END;", Autoid);
// break;
}
return(sql);
}
private static string GetKey(MCellStruct column, DataBaseType dalType, ref List <MCellStruct> primaryKeyList, string version)
{
string key = SqlFormat.Keyword(column.ColumnName, dalType); //列名。
DataGroupType group = DataType.GetGroup(column.SqlType); //数据库类型。
bool isAutoOrPKey = column.IsPrimaryKey || column.IsAutoIncrement; //是否主键或自增列。
if (dalType != DataBaseType.Access || !isAutoOrPKey || !column.IsAutoIncrement)
{
SqlDbType sdt = column.SqlType;
if (sdt == SqlDbType.DateTime && dalType == DataBaseType.MySql && Convert.ToString(column.DefaultValue) == SqlValue.GetDate)
{
sdt = SqlDbType.Timestamp;
}
key += " " + DataType.GetDataType(column, dalType, version);
}
if (isAutoOrPKey)
{
if (column.IsAutoIncrement)
{
if (primaryKeyList.Count == 0 || (!column.IsPrimaryKey && dalType == DataBaseType.MySql))//MySql 的自增必须是主键.
{
column.IsPrimaryKey = true;
primaryKeyList.Insert(0, column);
}
}
switch (dalType)
{
case DataBaseType.Access:
if (column.IsAutoIncrement)
{
key += " autoincrement(1,1)";
}
else // 主键。
{
if (group == DataGroupType.Guid) //主键又是GUID
{
key += " default GenGUID()";
}
}
break;
case DataBaseType.MsSql:
if (column.IsAutoIncrement)
{
key += " IDENTITY(1,1)";
}
else
{
if (group == DataGroupType.Guid) //主键又是GUID
{
key += " Default (newid())";
}
}
break;
case DataBaseType.Oracle:
if (Convert.ToString(column.DefaultValue) == SqlValue.Guid) //主键又是GUID
{
key += " Default (SYS_GUID())";
}
break;
case DataBaseType.Sybase:
if (column.IsAutoIncrement)
{
key += " IDENTITY";
}
else
{
if (group == DataGroupType.Guid) //主键又是GUID
{
key += " Default (newid())";
}
}
break;
case DataBaseType.MySql:
if (column.IsAutoIncrement)
{
key += " AUTO_INCREMENT";
if (!column.IsPrimaryKey)
{
primaryKeyList.Add(column);
}
}
break;
case DataBaseType.SQLite: //sqlite的AUTOINCREMENT不能写在primarykey前,
if (column.IsAutoIncrement)
{
key += " PRIMARY KEY AUTOINCREMENT";
primaryKeyList.Clear(); //如果有自增加，只允许存在这一个主键。
}
break;
case DataBaseType.PostgreSQL:
if (column.IsAutoIncrement && key.EndsWith("int"))
{
key = key.Substring(0, key.Length - 3) + "serial";
}
break;
case DataBaseType.DB2:
if (column.IsAutoIncrement)
{
key += " GENERATED ALWAYS AS IDENTITY";
}
break;
}
key += " NOT NULL";
}
else
{
string defaultValue = string.Empty;
if (Convert.ToString(column.DefaultValue).Length > 0 && group != DataGroupType.Object)//默认值只能是基础类型有。
{
if (dalType == DataBaseType.MySql)
{
if ((group == 0 && (column.MaxSize < 1 || column.MaxSize > 8000)) || (group == DataGroupType.Date && key.Contains("datetime"))) //只能对TIMESTAMP类型的赋默认值。
{
goto er;
}
}
defaultValue = SqlFormat.FormatDefaultValue(dalType, column.DefaultValue, 1, column.SqlType);
if (!string.IsNullOrEmpty(defaultValue))
{
if (dalType == DataBaseType.MySql)
{
defaultValue = defaultValue.Trim('(', ')');
}
key += " Default " + defaultValue;
}
}
er:
if (dalType != DataBaseType.Access)
{
if (dalType == DataBaseType.Sybase && column.SqlType == SqlDbType.Bit)
{
if (string.IsNullOrEmpty(defaultValue))
{
key += " Default 0";
}
key += " NOT NULL";//Sybase bit 不允许为Null
}
else
{
if (dalType == DataBaseType.DB2 && column.IsCanNull)
{
} //db2 不用null
else
{
key += column.IsCanNull ? " NULL" : " NOT NULL";
}
}
}
}
if (!string.IsNullOrEmpty(column.Description))
{
switch (dalType)
{
case DataBaseType.MySql:
key += string.Format(" COMMENT '{0}'", column.Description.Replace("'", "''"));
break;
}
}
return(key + ",");
}
/// <summary>
/// 获取指定的表架构生成的SQL(Alter Table)语句
/// </summary>
public static List <string> AlterTableSql(string tableName, MDataColumn columns, string conn)
{
List <string> sql = new List <string>();
string version = null;
DataBaseType dalType;
using (DalBase helper = DalCreate.CreateDal(conn))
{
helper.ChangeDatabaseWithCheck(tableName);//检测dbname.dbo.tablename的情况
if (!helper.TestConn(AllowConnLevel.Master))
{
helper.Dispose();
return(sql);
}
dalType = helper.DataBaseType;
version = helper.Version;
}
MDataColumn dbColumn = TableSchema.GetColumns(tableName, conn);//获取数据库的列结构
if (dbColumn == null || dbColumn.Count == 0)
{
return(sql);
}
//开始比较异同
List <MCellStruct> primaryKeyList = new List <MCellStruct>();
string tbName = SqlFormat.Keyword(tableName, dalType);
string alterTable = "alter table " + tbName;
foreach (MCellStruct ms in columns)//遍历新的结构
{
string cName = SqlFormat.Keyword(ms.ColumnName, dalType);
if (ms.AlterOp != AlterOp.None)
{
bool isContains = dbColumn.Contains(ms.ColumnName);
AlterOp op = ms.AlterOp;
if ((op & AlterOp.Rename) != 0)
{
op = (AlterOp)(op - AlterOp.Rename);
#region MyRegion Rename
if (!string.IsNullOrEmpty(ms.OldName) && ms.OldName != ms.ColumnName && !isContains)
{
string oName = SqlFormat.Keyword(ms.OldName, dalType);
switch (dalType)
{
case DataBaseType.MsSql:
sql.Add("exec sp_rename '" + tbName + "." + oName + "', '" + ms.ColumnName + "', 'column'");
break;
case DataBaseType.Sybase:
sql.Add("exec sp_rename \"" + tableName + "." + ms.OldName + "\", " + ms.ColumnName);
break;
case DataBaseType.MySql:
sql.Add(alterTable + " change " + oName + " " + GetKey(ms, dalType, ref primaryKeyList, version).TrimEnd(','));
break;
case DataBaseType.Oracle:
sql.Add(alterTable + " rename column " + oName + " to " + cName);
break;
}
isContains = isContains || dbColumn.Contains(ms.OldName);
}
#endregion
}
if (op == AlterOp.Drop)
{
#region MyRegion
if (isContains)
{
switch (dalType)
{
case DataBaseType.MsSql:
case DataBaseType.Access:
case DataBaseType.MySql:
case DataBaseType.Oracle:
if (dalType == DataBaseType.MsSql)
{
sql.Add(@"declare @name varchar(50) select @name =b.name from sysobjects b join syscolumns a on b.id = a.cdefault
where a.id = object_id('" + tableName + "') and a.name ='" + ms.ColumnName + "'if(@name!='') begin EXEC('alter table " + tableName + " drop constraint '+ @name) end");
}
sql.Add(alterTable + " drop column " + cName);
break;
case DataBaseType.Sybase:
sql.Add(alterTable + " drop " + cName);
break;
}
}
#endregion
}
//else if (ms.AlterOp == AlterOp.Rename)
//{
//}
else if (op == AlterOp.AddOrModify)
{
//智能判断
if (isContains) // 存在，则修改
{
string alterSql = SqlFormat.Keyword(ms.ColumnName, dalType) + " " + DataType.GetDataType(ms, dalType, version);
//检测是否相同
MCellStruct dbStruct = dbColumn[ms.ColumnName] ?? dbColumn[ms.OldName];
if (dbStruct.IsCanNull != ms.IsCanNull || dbStruct.SqlType != ms.SqlType || dbStruct.MaxSize != ms.MaxSize || dbStruct.Scale != ms.Scale)
{
string modify = "";
switch (dalType)
{
case DataBaseType.Oracle:
case DataBaseType.Sybase:
modify = " modify ";
break;
case DataBaseType.MySql:
modify = " change " + cName + " ";
break;
case DataBaseType.MsSql:
case DataBaseType.Access:
case DataBaseType.PostgreSQL:
modify = " alter column ";
break;
}
if (ms.IsCanNull != dbStruct.IsCanNull)
{
alterSql += (ms.IsCanNull ? " NULL" : " NOT NULL");
}
sql.Add(alterTable + modify + alterSql);
}
}
else //不在，则添加
{
sql.Add(alterTable + " add " + GetKey(ms, dalType, ref primaryKeyList, version).TrimEnd(','));
if (!string.IsNullOrEmpty(ms.Description))
{
string description = SqlCreateForSchema.GetTableDescriptionSql(tableName, ms, dalType, true);
if (!string.IsNullOrEmpty(description))
{
sql.Add(description);
}
}
}
}
}
}
return(sql);
}
public static MDataColumn GetColumns(string tableName, string conn)
{
string key = GetSchemaKey(tableName, conn);
#region 缓存检测
if (_ColumnCache.ContainsKey(key))
{
return(_ColumnCache[key].Clone());
}
if (!string.IsNullOrEmpty(AppConfig.DB.SchemaMapPath))
{
string fullPath = AppConfig.RunPath + AppConfig.DB.SchemaMapPath + key + ".ts";
if (System.IO.File.Exists(fullPath))
{
MDataColumn columns = MDataColumn.CreateFrom(fullPath);
if (columns.Count > 0)
{
CacheManage.LocalInstance.Set(key, columns.Clone(), 1440);
return(columns);
}
}
}
#endregion
string fixName;
conn = CrossDB.GetConn(tableName, out fixName, conn ?? AppConfig.DB.DefaultConn);
tableName = fixName;
if (conn == null)
{
return(null);
}
MDataColumn mdcs = null;
using (DalBase dbHelper = DalCreate.CreateDal(conn))
{
DataBaseType dalType = dbHelper.DataBaseType;
if (dalType == DataBaseType.Txt || dalType == DataBaseType.Xml)
{
#region 文本数据库处理。
if (!tableName.Contains(" ")) // || tableName.IndexOfAny(Path.GetInvalidPathChars()) == -1
{
tableName = SqlFormat.NotKeyword(tableName); //处理database..tableName;
tableName = Path.GetFileNameWithoutExtension(tableName); //视图表，带“.“的，会出问题
string fileName = dbHelper.Con.DataSource + tableName + (dalType == DataBaseType.Txt ? ".txt" : ".xml");
mdcs = MDataColumn.CreateFrom(fileName);
mdcs.DataBaseType = dalType;
mdcs.DataBaseVersion = dbHelper.Version;
mdcs.Conn = conn;
}
#endregion
}
else
{
#region 其它数据库
mdcs = new MDataColumn();
mdcs.Conn = conn;
mdcs.TableName = tableName;
mdcs.DataBaseType = dalType;
mdcs.DataBaseVersion = dbHelper.Version;
tableName = SqlFormat.Keyword(tableName, dbHelper.DataBaseType);//加上关键字：引号
//如果table和helper不在同一个库
DalBase helper = dbHelper.ResetDalBase(tableName);
helper.IsRecordDebugInfo = false || AppDebug.IsContainSysSql;//内部系统，不记录SQL表结构语句。
try
{
bool isView = tableName.Contains(" ");//是否视图。
if (!isView)
{
isView = CrossDB.Exists(tableName, "V", conn);
}
if (!isView)
{
TableInfo info = CrossDB.GetTableInfoByName(mdcs.TableName, conn);
if (info != null)
{
mdcs.Description = info.Description;
}
}
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 DataBaseType.MsSql:
case DataBaseType.Oracle:
case DataBaseType.MySql:
case DataBaseType.Sybase:
case DataBaseType.PostgreSQL:
case DataBaseType.DB2:
#region Sql
string sql = string.Empty;
if (dalType == DataBaseType.MsSql)
{
#region Mssql
string dbName = null;
if (!helper.Version.StartsWith("08"))
{
//先获取同义词，检测是否跨库
string realTableName = Convert.ToString(helper.ExeScalar(string.Format(MSSQL_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.DataBaseName);
#endregion
}
else if (dalType == DataBaseType.MySql)
{
sql = GetMySqlColumns(helper.DataBaseName);
}
else if (dalType == DataBaseType.Oracle)
{
tableName = tableName.ToUpper(); //Oracle转大写。
//先获取同义词，不检测是否跨库
string realTableName = Convert.ToString(helper.ExeScalar(string.Format(Oracle_SynonymsName, SqlFormat.NotKeyword(tableName)), false));
if (!string.IsNullOrEmpty(realTableName))
{
tableName = realTableName;
}
sql = GetOracleColumns();
}
else if (dalType == DataBaseType.Sybase)
{
tableName = SqlFormat.NotKeyword(tableName);
sql = GetSybaseColumns();
}
else if (dalType == DataBaseType.PostgreSQL)
{
sql = GetPostgreColumns(float.Parse(dbHelper.Version));
}
else if (dalType == DataBaseType.DB2)
{
tableName = SqlFormat.NotKeyword(tableName).ToUpper();
sql = GetDB2Columns();
}
helper.AddParameters("TableName", SqlFormat.NotKeyword(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.DataBaseType);
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 DataBaseType.MsSql:
case DataBaseType.MySql:
case DataBaseType.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 == DataBaseType.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 DataBaseType.SQLite:
#region SQlite
if (helper.Con.State != ConnectionState.Open)
{
helper.Con.Open();
}
DataTable sqliteDt = helper.Con.GetSchema("Columns", new string[] { null, null, SqlFormat.NotKeyword(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 == DBNull.Value)
{
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 DataBaseType.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;
}
}
}
catch (Exception err)
{
Log.Write(err, LogType.DataBase);
//helper.DebugInfo.Append(err.Message);
}
#endregion
}
}
if (mdcs != null && 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);
}
}
#region 缓存设置
if (!_ColumnCache.ContainsKey(key) && mdcs.Count > 0)
{
_ColumnCache.Add(key, mdcs.Clone());
if (!string.IsNullOrEmpty(AppConfig.DB.SchemaMapPath))
{
string folderPath = AppConfig.RunPath + AppConfig.DB.SchemaMapPath;
if (!System.IO.Directory.Exists(folderPath))
{
System.IO.Directory.CreateDirectory(folderPath);
}
mdcs.WriteSchema(folderPath + key + ".ts");
}
}
#endregion
}
return(mdcs);
}
/// <summary>
/// 返回插入的字符串
/// </summary>
/// <returns>结果如:insert into tableName(ID,Name,Value) values(@ID,@Name,@Value)</returns>
internal string GetInsertSql()
{
isCanDo = false;
StringBuilder _TempSql = new StringBuilder();
StringBuilder _TempSql2 = new StringBuilder();
_TempSql.Append("insert into " + TableName + "(");
_TempSql2.Append(") Values(");
MDataCell primaryCell = _action.Data[_action.Data.Columns.FirstPrimary.ColumnName];
int groupID = DataType.GetGroup(primaryCell.Struct.SqlType);
string defaultValue = Convert.ToString(primaryCell.Struct.DefaultValue);
if (primaryCell.IsNullOrEmpty && (groupID == 4 || (groupID == 0 && (primaryCell.Struct.MaxSize <= 0 || primaryCell.Struct.MaxSize >= 36) &&
(defaultValue == "" || defaultValue == "newid" || defaultValue == SqlValue.Guid))))//guid类型
{
primaryCell.Value = Guid.NewGuid();
}
MDataCell cell = null;
for (int i = 0; i < _action.Data.Count; i++)
{
cell = _action.Data[i];
if (cell.Struct.IsAutoIncrement && !_action.AllowInsertID)
{
continue;//跳过自增列。
}
if (cell.IsNull && !cell.Struct.IsCanNull && cell.Struct.DefaultValue == null)
{
_action.dalHelper.debugInfo.Append(AppConst.HR + "error : " + cell.ColumnName + " can't be null" + AppConst.BR);
_action.dalHelper.recordsAffected = -2;
isCanDo = false;
break;
}
if (cell.cellValue.State > 0)
{
_TempSql.Append(SqlFormat.Keyword(cell.ColumnName, _action.DalType) + ",");
_TempSql2.Append(_action.dalHelper.Pre + cell.ColumnName + ",");
object value = cell.Value;
DbType dbType = DataType.GetDbType(cell.Struct.SqlType.ToString(), _action.DalType);
if (_action.DalType == DalType.Oracle && dbType == DbType.String && cell.strValue == "" && !cell.Struct.IsCanNull)
{
value = " ";
}
_action.dalHelper.AddParameters(_action.dalHelper.Pre + cell.ColumnName, value, dbType, cell.Struct.MaxSize, ParameterDirection.Input);
isCanDo = true;
}
}
switch (_action.dalHelper.dalType)
{
case DalType.Oracle:
if (!_action.AllowInsertID && DataType.GetGroup(primaryCell.Struct.SqlType) == 1)
{
_TempSql.Append(primaryCell.ColumnName + ",");
_TempSql2.Append(AutoID + ".nextval,");
}
break;
}
string sql = _TempSql.ToString().TrimEnd(',') + _TempSql2.ToString().TrimEnd(',') + ")";
switch (_action.dalHelper.dalType)
{
case DalType.MsSql:
case DalType.Sybase:
if (primaryCell.Struct.IsAutoIncrement && !_action.AllowInsertID && groupID == 1)
{
if (_action.dalHelper.dalType == DalType.Sybase)
{
sql = sql + " select @@IDENTITY as OutPutValue";
}
else
{
sql += " select cast(scope_Identity() as int) as OutPutValue";
}
}
else if (!primaryCell.IsNullOrEmpty)
{
sql += string.Format(" select '{0}' as OutPutValue", primaryCell.Value);
}
if (_action.AllowInsertID && !_action.dalHelper.isOpenTrans && primaryCell.Struct.IsAutoIncrement) //非批量操作时
{
sql = "set identity_insert " + SqlFormat.Keyword(TableName, _action.dalHelper.dalType) + " on " + sql + " set identity_insert " + SqlFormat.Keyword(TableName, _action.dalHelper.dalType) + " off";
}
break;
//if (!(Parent.AllowInsertID && !primaryCell.IsNull)) // 对于自行插入ID的，跳过，主操作会自动返回ID。
//{
// sql += ((groupID == 1 && (primaryCell.IsNull || primaryCell.ToString() == "0")) ? " select cast(scope_Identity() as int) as OutPutValue" : string.Format(" select '{0}' as OutPutValue", primaryCell.Value));
//}
//case DalType.Oracle:
// sql += string.Format("BEGIN;select {0}.currval from dual; END;", AutoID);
// break;
}
return(sql);
}
internal string GetDeleteToUpdateSql(object whereObj)
{
string editTime = GetEditTimeSql();
return("update " + SqlFormat.Keyword(TableName, _action.dalHelper.DataBaseType) + " set " + editTime + SqlFormat.Keyword(AppConfig.DB.DeleteField, _action.dalHelper.DataBaseType) + "=[#TRUE] where " + FormatWhere(whereObj));
}
internal string GetDeleteSql(object whereObj)
{
return("delete from " + SqlFormat.Keyword(TableName, _action.dalHelper.DataBaseType) + " where " + FormatWhere(whereObj));
}
internal string GetMaxID()
{
switch (_action.dalHelper.DataBaseType)
{
case DataBaseType.Oracle:
return(string.Format("select {0}.currval from dual", AutoID));
default:
//case DalType.MsSql:
//case DalType.Sybase:
//case DalType.MySql:
//case DalType.SQLite:
//case DalType.Access:
//string columnName = _action.Data.Columns.FirstPrimary.ColumnName;
//string tableName = TableName;
////if (_action.dalHelper.DataBaseType == DataBaseType.PostgreSQL)
////{
//columnName = SqlFormat.Keyword(columnName, _action.dalHelper.DataBaseType);
//tableName = SqlFormat.Keyword(tableName, _action.dalHelper.DataBaseType);
// }
return(string.Format("select max({0}) from {1}", SqlFormat.Keyword(_action.Data.Columns.FirstPrimary.ColumnName, _action.dalHelper.DataBaseType), SqlFormat.Keyword(TableName, _action.dalHelper.DataBaseType)));
}
// return (string)Error.Throw(string.Format("GetMaxid:{0} No Be Support Now!", _action.dalHelper.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);
}
/// <summary>
/// 是否存在表或视图
/// </summary>
/// <param name="type">"U"或"V"</param>
/// <param name="name">表名或视图名</param>
public static bool Exists(string type, string name, ref DbBase helper)
{
if (type == "U" && tableCache.Count > 0)
{
string key = "TableCache:" + helper.dalType + "." + helper.DataBase;
if (tableCache.ContainsKey(key))
{
return(tableCache[key].ContainsKey(name));
}
}
int result = 0;
string exist = string.Empty;
helper.IsAllowRecordSql = false;
DalType dalType = helper.dalType;
name = SqlFormat.Keyword(name, helper.dalType);
switch (dalType)
{
case DalType.Access:
try
{
System.Data.OleDb.OleDbConnection con = new System.Data.OleDb.OleDbConnection(helper.Con.ConnectionString);
con.Open();
result = con.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, SqlFormat.NotKeyword(name), "Table" }).Rows.Count;
con.Close();
}
catch (Exception err)
{
Log.WriteLogToTxt(err);
}
break;
case DalType.MySql:
if (type != "V" || (type == "V" && name.ToLower().StartsWith("v_"))) //视图必须v_开头
{
exist = string.Format(ExistMySql, SqlFormat.NotKeyword(name), helper.DataBase);
}
break;
case DalType.Oracle:
exist = string.Format(ExistOracle, (type == "U" ? "TABLE" : "VIEW"), name);
break;
case DalType.MsSql:
exist = string.Format(helper.Version.StartsWith("08") ? Exist2000 : Exist2005, name, type);
break;
case DalType.SQLite:
exist = string.Format(ExistSqlite, (type == "U" ? "table" : "view"), SqlFormat.NotKeyword(name));
break;
case DalType.Sybase:
exist = string.Format(ExistSybase, SqlFormat.NotKeyword(name), type);
break;
case DalType.Txt:
case DalType.Xml:
string folder = helper.Con.DataSource + Path.GetFileNameWithoutExtension(name);
FileInfo info = new FileInfo(folder + ".ts");
result = (info.Exists && info.Length > 10) ? 1 : 0;
if (result == 0)
{
info = new FileInfo(folder + (dalType == DalType.Txt ? ".txt" : ".xml"));
result = (info.Exists && info.Length > 10) ? 1 : 0;
}
break;
}
if (exist != string.Empty)
{
helper.IsAllowRecordSql = false;
result = Convert.ToInt32(helper.ExeScalar(exist, false));
}
return(result > 0);
}
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()));
}
