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())); }