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