public override void EndTableLoad(IDbConnection connection, TableSchema table)
{
if (TableHasIdentityColumns(table))
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = @"SET IDENTITY_INSERT " + MakeDdlElementName(table.Name) + @" OFF";
cmd.ExecuteNonQuery();
}
base.EndTableLoad(connection, table);
}
public static void ReloadTableSchema(SqlConnection conn, DBSchema schema, TableSchema table)
{
string[] restrict4 = { null, null, table.Name, "TABLE" };
var dtTables = SqlSchemaFactory.GetSchema(conn, "Tables", restrict4);
if (dtTables.Rows.Count == 0)
{
schema.Tables.Remove(table);
table.Name += "_FANTOM";
table.Columns = new TableColumnSchema[0];
table.Keys = new KeySchema[0];
table.Indexes = new IndexSchema[0];
return;
}
var freshTable = GetTable(conn, dtTables.Rows[0]);
table.Columns = freshTable.Columns;
table.Keys = GetKeys(conn, table).ToArray();
table.Indexes = GetIndexes(conn, table).ToArray();
}
private static TableSchema GetTable(SqlConnection con, DataRow tRow)
{
var eTable = new TableSchema {Name = tRow["TABLE_NAME"].ToString()};
// Columns
string[] restrict3 = { null, null, null };
restrict3[0] = null;
restrict3[1] = null;
restrict3[2] = eTable.Name;
var dtShema = SqlSchemaFactory.GetSchema(con, "Columns", restrict3);
if (dtShema.Rows.Count > 0)
{
eTable.Columns = new TableColumnSchema[dtShema.Rows.Count];
for (var j = 0; j < dtShema.Rows.Count; j++)
{
var cRow = dtShema.Rows[j];
var eColumn = new TableColumnSchema
{
Name = cRow["COLUMN_NAME"].ToString(),
Type = ColumnSchemaToSqlDataType(cRow),
Nullable = Convert.ToBoolean(cRow["IS_NULLABLE"], CultureInfo.InvariantCulture),
DefaultValue = cRow["COLUMN_DEFAULT"].ToString(),
Increment = Convert.ToInt32(cRow["IDENTITY_INCREMENT"], CultureInfo.InvariantCulture),
Seed = Convert.ToInt32(cRow["IDENTITY_SEED"], CultureInfo.InvariantCulture),
AutoIncrement = Convert.ToBoolean(cRow["IS_IDENTITY"], CultureInfo.InvariantCulture),
};
eColumn.DefaultValue = string.IsNullOrEmpty(eColumn.DefaultValue)
? null
: UnBracket.ParseUnBracket(eColumn.DefaultValue);
eTable.Columns[j] = eColumn;
}
}
return eTable;
}
private static List<IndexSchema> GetIndexes(SqlConnection con, TableSchema eTable)
{
var indexes = new List<IndexSchema>();
var aHash = new Dictionary<string, bool>();
string[] restrict4 = {null, null, null, null};
// Indexes
restrict4[0] = null;
restrict4[1] = null;
restrict4[2] = eTable.Name;
restrict4[3] = null;
var dtShema = SqlSchemaFactory.GetSchema(con, "Indexes", restrict4);
for (var i = 0; i < dtShema.Rows.Count; i++)
{
var row = dtShema.Rows[i];
if (Convert.ToBoolean(row["IS_STATISTICS"], CultureInfo.InvariantCulture) ||
Convert.ToBoolean(row["IS_AUTOSTATISTICS"], CultureInfo.InvariantCulture) ||
Convert.ToBoolean(row["IS_HYPOTTETICAL"], CultureInfo.InvariantCulture))
continue;
var cName = row["INDEX_NAME"].ToString();
if (eTable.IsKeyExist(cName, ConstraintType.Unique) ||
eTable.IsKeyExist(cName, ConstraintType.KeyPrimary) ||
eTable.IsKeyExist(cName, ConstraintType.KeyForeign))
continue;
if (aHash.ContainsKey(cName))
continue;
var eIndex = new IndexSchema();
aHash.Add(cName, true);
eIndex.Name = cName;
eIndex.Unique = Convert.ToBoolean(row["IS_UNIQUE"], CultureInfo.InvariantCulture);
eIndex.Clustered = Convert.ToBoolean(row["IS_CLUSTERED"], CultureInfo.InvariantCulture);
//eIndex.isActive = !Convert.ToBoolean(cRow["IS_INACTIVE"], CultureInfo.InvariantCulture);
var dtv = dtShema.DefaultView;
dtv.RowFilter = string.Format("INDEX_NAME = '{0}'", cName);
dtv.Sort = "COLUMN_ORDINAL_POSITION ASC";
var columns = "";
for (var y = 0; y < dtv.Count; y++)
columns += (dtv[y]["COLUMN_NAME"] +
(Convert.ToBoolean(dtv[y]["IS_DESCENDING"], CultureInfo.InvariantCulture) ? " DESC" : "") +
", ");
columns = columns.Remove(columns.Length - 2, 2);
eIndex.Columns = columns;
indexes.Add(eIndex);
}
return indexes;
}
private static bool TableHasIdentityColumns(TableSchema table)
{
return Array.Exists(table.Columns, column => column.AutoIncrement);
}
public override void ReloadTableSchema(IDbConnection conn, DBSchema schema, TableSchema table)
{
MssqlSchemaLoader.ReloadTableSchema((SqlConnection) conn, schema, table);
}
public override string MakeDdlIndexCreate(IndexSchema index, TableSchema table)
{
return
@"CREATE {0} {1} INDEX {2} ON {3} ({4})"
.FormatStr(
index.Unique ? @" UNIQUE" : string.Empty,
index.Clustered ? @" CLUSTERED" : string.Empty,
MakeDdlElementName(index.Name),
MakeDdlElementName(table.Name),
ParseColumnListIndex(index.Columns).JoinStrings(@", "));
}
public override string MakeDdlColumnRename(TableColumnSchema column, string newName, TableSchema table)
{
return string.Format("sp_rename '{0}.{1}', '{2}', 'COLUMN'",
MakeDdlElementName(table.Name), MakeDdlElementName(column.Name), newName);
}
