static List <SysColumn> GetTableColumns(SqlConnection sourceConn, string tableName)
{
string sql = string.Format("select syscolumns.name,colorder from syscolumns inner join sysobjects on syscolumns.id=sysobjects.id where sysobjects.xtype='U' and sysobjects.name='{0}' order by syscolumns.colid asc", tableName);
List <SysColumn> columns = new List <SysColumn>();
using (SqlConnection conn = (SqlConnection)((ICloneable)sourceConn).Clone())
{
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
//SqlDataReader rdr = ;
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
SysColumn column = new SysColumn();
column.Name = reader.GetValue(0).ToString();
column.ColOrder = int.Parse(reader.GetValue(1).ToString());
columns.Add(column);
}
}
conn.Close();
}
return(columns);
}
static List <SysColumn> GetTableColumns(SqlConnection sourceConn, string tableName)
{
string sql = string.Format("select * from syscolumns inner join sysobjects on syscolumns.id=sysobjects.id where sysobjects.xtype='U' and sysobjects.name='{0}' order by syscolumns.colid asc", tableName);
List <SysColumn> columns = new List <SysColumn>();
using (SqlConnection conn = (SqlConnection)((ICloneable)sourceConn).Clone())
{
conn.Open();
using (var reader = conn.ExecuteReader(sql))
{
while (reader.Read())
{
SysColumn column = new SysColumn();
column.Name = reader.GetDbValue("name");
column.ColOrder = reader.GetDbValue("colorder");
columns.Add(column);
}
}
conn.Close();
}
return(columns);
}
List <SysColumn> GetTableColumns(string tableName)
{
string sql = "select syscolumns.name,syscolumns.colorder,syscolumns.isnullable,systypes.xusertype,systypes.name as typename from syscolumns inner join systypes on syscolumns.xusertype=systypes.xusertype inner join sysobjects on syscolumns.id = sysobjects.id where sysobjects.xtype = 'U' and sysobjects.name = @TableName order by syscolumns.colid asc";
List <SysColumn> columns = new List <SysColumn>();
using (var reader = this.Session.ExecuteReader(sql, new DbParam("@TableName", tableName)))
{
while (reader.Read())
{
SysColumn column = new SysColumn();
column.Name = GetValue <string>(reader, "name");
column.ColOrder = GetValue <int>(reader, "colorder");
column.XUserType = GetValue <int>(reader, "xusertype");
column.TypeName = GetValue <string>(reader, "typename");
column.IsNullable = GetValue <bool>(reader, "isnullable");
columns.Add(column);
}
reader.Close();
}
return(columns);
}
public static List <SysColumn> GetTableColumns(SqlConnection sourceConn, string tableName)
{
string sql = string.Format("select * from syscolumns inner join sysobjects on syscolumns.id=sysobjects.id where sysobjects.xtype='U' and sysobjects.name='{0}' order by syscolumns.colid asc", tableName);
List <SysColumn> columns = new List <SysColumn>();
using (SqlConnection conn = (SqlConnection)((ICloneable)sourceConn).Clone())
{
conn.Open();
using (var command = conn.CreateCommand())
{
command.CommandText = sql;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
SysColumn column = new SysColumn();
column.Name = reader["name"].ToString();
column.ColOrder = int.Parse(reader["colorder"].ToString());
columns.Add(column);
}
}
}
conn.Close();
}
return(columns);
}
private static List <SysColumn> GetTableColumns(SqlConnection connection, string tableName)
{
string sql = string.Format(@"select * from syscolumns
inner join sysobjects on syscolumns.id == sysobjects.id
where sysobjects.xtype = 'U'
and sysobjects.name = '{0}'
order by syscolumns.colid asc", tableName);
List <SysColumn> columns = new List <SysColumn>();
using (SqlConnection conn = ((ICloneable)connection).Clone() as SqlConnection)
{
conn.Open();
using (var command = new SqlCommand(sql, conn))
{
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
SysColumn column = new SysColumn();
column.Name = reader["name"] as string;
column.ColOrder = Convert.ToInt32(reader["colorder"]);
columns.Add(column);
}
}
}
}
return(columns);
}
public static string DropColumn(SysColumn column)
{
return(string.Join("\n", new string[]
{
$"ALTER TABLE {column.qualified_table_name}",
$"DROP COLUMN [{column.name}]\n"
}));
}
public static string AlterColumn(SysColumn column)
{
return(string.Join("\n", new string[]
{
$"ALTER TABLE {column.qualified_table_name}",
$"ALTER COLUMN [{column.name}] {column.type_definition} {column.nullability}\n"
}));
}
public static string UpdateIfNull(SysColumn column, string value = null)
{
return(string.Join("\n", new string[]
{
$"UPDATE {column.qualified_table_name}",
$"SET {column.name} = { value ?? DefaultValue(column.data_type) }",
$"WHERE {column.name} IS NULL\n"
}));
}
private bool DependsOn(SysConstraint constraint, SysColumn column)
{
if (constraint == null)
{
return(false);
}
else if (constraint.columns.Contains(",") && constraint.columns.Split(",").Contains(column.name))
{
return(true);
}
else if (constraint.columns == column.name)
{
return(true);
}
return(false);
}
/// <summary>
/// 获取数据库表的所有列
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
public static List <SysColumn> GetTableColumns(string tableName)
{
string sql = string.Format(@"select a.name,a.colorder,c.DATA_TYPE,a.isnullable,SUBSTRING(d.text,3,1) defaultValue from
syscolumns a inner join sysobjects b on a.id=b.id
LEFT JOIN dbo.syscomments d ON a.cdefault = d.id
inner join information_schema.columns c on b.name=c.TABLE_NAME and c.COLUMN_NAME=a.name
where b.xtype='U' and b.name='{0}' order by a.colid asc", tableName);
List <SysColumn> columns = new List <SysColumn>();
DataTable dt = SqlCoreHelper.ExecuteDataSetText(sql, null).Tables[0];
foreach (DataRow reader in dt.Rows)
{
SysColumn column = new SysColumn();
column.Name = reader[0].ToString();
column.ColOrder = Convert.ToInt16(reader[1]);
column.Type = reader[2].ToString();
column.IsNull = Convert.ToInt32(reader[3]);
column.Default = reader[4].ToString();
columns.Add(column);
}
return(columns);
}
public ColumnMapping(SysColumn column)
{
this.Column = column;
}
public async Task <ApiResult <string> > UpdateColumn([FromBody] SysColumn parm)
{
return(await _sysColumnService.ModifyAsync(parm));
}
public async Task <ApiResult <string> > AddColumn([FromBody] SysColumn model)
{
return(await _sysColumnService.AddAsync(model));
}
// drop/add/alter any columns
private void PatchColumns(StringBuilder builder)
{
var source = sourceObjects.Keys.ToHashSet();
var target = targetObjects.Keys.ToHashSet();
var intersect = source.Intersect(target).Where(name => sourceTypes[sourceObjects[name]] == Enumerations.GetDescription(SQLTypes.UserTable));
foreach (var tableName in intersect)
{
var sourceId = sourceObjects[tableName];
var targetId = targetObjects[tableName];
var sourceTable = sourceTables[sourceId];
var sourceCols = sourceColumns[sourceId].Where(c => c.generated_always_type == "0");
var targetCols = targetColumns[targetId].Where(c => c.generated_always_type == "0" || string.IsNullOrEmpty(sourceTable.history_table));
var sourceColDictionary = sourceCols.ToDictionary(c => c.name);
var targetColDictionary = targetCols.ToDictionary(c => c.name);
var toDrop = targetCols.Where(c => !sourceColDictionary.ContainsKey(c.name)).ToList();
var toAdd = sourceCols.Where(c => !targetColDictionary.ContainsKey(c.name)).ToList();
var toAlter = sourceCols.Where(source =>
{
if (!targetColDictionary.ContainsKey(source.name))
{
return(false);
}
var target = targetColDictionary[source.name];
return(source.type_definition != target.type_definition);
}).ToList();
if (toDrop.Any() || toAdd.Any() || toAlter.Any())
{
builder.Append($"-- {tableName} --\n");
var targetObjConstraints = targetConstraints.Values.Where(c => c.parent_object_id == targetId);
var primaryKey = targetObjConstraints.Where(c => c.type == Enumerations.GetDescription(SQLTypes.PrimaryKey)).FirstOrDefault();
toDrop.ForEach(column =>
{
builder.Append(Query.DropColumn(column));
builder.Append(Query.BatchSeperator);
});
toAdd.ForEach(column =>
{
if (column.isNullable)
{
builder.Append(Query.AddColumn(column));
builder.Append(Query.BatchSeperator);
}
else
{
var nullable = SysColumn.DeepClone(column);
nullable.is_nullable = "true";
builder.Append(Query.AddColumn(nullable));
builder.Append(Query.BatchSeperator);
builder.Append(Query.UpdateIfNull(column));
builder.Append(Query.BatchSeperator);
toAlter.Add(column);
}
});
toAlter.ForEach(column =>
{
if (DependsOn(primaryKey, column))
{
builder.Append(Query.DropPrimaryKey(primaryKey));
builder.Append(Query.BatchSeperator);
}
builder.Append(Query.AlterColumn(column));
builder.Append(Query.BatchSeperator);
});
}
}
}
public DatabaseImp( string dirName )
{
Name = dirName;
Log = new Log( Name );
Sys = GetSchema( "sys", true, null );
{
SysString = OpenFile( FileType.System, 0 );
SysStringReader = new BinaryReader( SysString );
SysStringWriter = new BinaryWriter( SysString );
IndexFileInfo ii = new IndexFileInfo();
ii.KeyCount = 1;
ii.Types = new DataType[] { DataType.String };
var f = OpenFile( FileType.System, 1 );
SysStringIndex = new IndexFile( f, ii, this, -1 );
}
{
SysBinary = OpenFile( FileType.System, 2 );
SysBinaryReader = new BinaryReader( SysBinary );
SysBinaryWriter = new BinaryWriter( SysBinary );
IndexFileInfo ii = new IndexFileInfo();
ii.KeyCount = 1;
ii.Types = new DataType[] { DataType.Binary };
var f = OpenFile( FileType.System, 3 );
SysBinaryIndex = new IndexFile( f, ii, this, -2 );
}
var cb = new ColBuilder();
cb.Add( "Name", DataType.String );
SysSchema = NewSysTable( 1, "Schema", cb.Get() );
cb.Add( "Schema", DataType.Int );
cb.Add( "Name", DataType.String );
cb.Add( "IsView", DataType.Tinyint );
cb.Add( "Definition", DataType.String );
SysTable = NewSysTable( 2, "Table", cb.Get() );
cb.Add( "Table", DataType.Int );
cb.Add( "Name", DataType.String );
cb.Add( "Type", DataType.Int );
SysColumn = NewSysTable( 3, "Column", cb.Get() );
cb.Add( "Table", DataType.Int );
cb.Add( "Name", DataType.String );
cb.Add( "Modified", DataType.Tinyint );
SysIndex = NewSysTable( 4, "Index", cb.Get() );
cb.Add( "Table", DataType.Int );
cb.Add( "Index", DataType.Int );
cb.Add( "ColId", DataType.Int );
SysIndexCol = NewSysTable( 5, "IndexCol", cb.Get() );
SysColumn.OpenIndexes( IndexInfo.Single( 1, 1 ) );
SysColumnIndex = SysColumn.FindIndex( 1 );
SysIndexCol.OpenIndexes( IndexInfo.Single( 2, 1) );
SysIndexColIndex = SysIndexCol.FindIndex( 1 );
SysSchema.OpenIndexes( IndexInfo.Single( 3, 1 ) );
SysSchemaByName = SysSchema.FindIndex( 1 );
SysTable.OpenIndexes( IndexInfo.Single( 4, 2 ) );
SysTableByName = SysTable.FindIndex( 2 );
if ( SysSchema.RowCount == 0 )
{
IsNew = true;
Sql( "CREATE SCHEMA sys" ); // Note these are created in TableId order.
Sql( "CREATE TABLE sys.Schema( Name string )" );
Sql( "CREATE TABLE sys.Table( Schema int, Name string, IsView tinyint, Definition string )" );
Sql( "CREATE TABLE sys.Column( Table int, Name string, Type int )" );
Sql( "CREATE TABLE sys.Index( Table int, Name string, Modified tinyint )" );
Sql( "CREATE TABLE sys.IndexCol( Table int, Index int, ColId int )" );
Sql( "CREATE INDEX ColumnByTable on sys.Column(Table)" );
Sql( "CREATE INDEX IndexColByTable on sys.IndexCol(Table)" );
Sql( "CREATE INDEX SchemaByName on sys.Schema(Name)" );
Sql( "CREATE INDEX TableByName on sys.Table(Name)" );
Normal = true;
Sql( "CREATE TABLE sys.Function( Schema int, Name string, Definition string )" );
Sql( "CREATE INDEX FunctionByName on sys.Function(Name)" );
Sql( "CREATE TABLE sys.Procedure( Schema int, Name string, Definition string )" );
Sql( "CREATE INDEX ProcedureByName on sys.Procedure(Name)" );
}
RollbackOrCommit();
Normal = true;
} // end DatabaseImp
