private static void ClusterTable(TableInfo table, SqlConnection destcn) { StringBuilder s = new StringBuilder(1024); using (TextWriter writer = new StringWriter(s)) WriteClusterDDL(table, writer); if (s.Length > 0) ExecuteSql(destcn, s.ToString()); }
private static void CreateTable(TableInfo table, SqlConnection destcn) { CreateSchemaIfNecessary(table.Name.Schema, destcn); StringBuilder s = new StringBuilder(1024); using (TextWriter writer = new StringWriter(s)) WriteTableDDL(table, writer); ExecuteSql(destcn, s.ToString()); ClusterTable(table, destcn); }
private void CopyTableData(TableInfo table, SqlConnection sourcecn, SqlConnection destcn) { string columnlist = GenerateColumns(table); string selectsql = String.Format("select {0} from {1}", columnlist, table.Name); using (SqlCommand selectcmd = Command.Sql.Create(sourcecn, selectsql)) using (SqlDataReader dr = selectcmd.ExecuteReader(CommandBehavior.SingleResult | CommandBehavior.SequentialAccess)) using (var copier = GetCopier(destcn, table)) { copier.WriteToServer(dr); } }
private SqlBulkCopy GetCopier(SqlConnection destcn, TableInfo table) { SqlBulkCopy copier = new SqlBulkCopy(destcn, SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.KeepIdentity, null) { BulkCopyTimeout = 0, BatchSize = 10000, DestinationTableName = table.Name.ToString(), NotifyAfter = 1000, }; copier.SqlRowsCopied += this.OnSqlRowsCopied; return copier; }
private static void WriteTableDDL(TableInfo table, TextWriter writer) { writer.WriteLine("CREATE TABLE {0}", table.Name); writer.WriteLine("("); WriteColumnsDDL(table.Columns, writer); writer.WriteLine(); writer.WriteLine(")"); }
private void CopyTable(TableInfo table, SqlConnection sourcecn, SqlConnection destcn) { Console.Write(" " + table.Name.Name + " "); CreateTable(table, destcn); PreCopyTable(table, destcn); CopyTableData(table, sourcecn, destcn); PostCopyTable(table, destcn); }
private static void WriteClusterDDL(TableInfo table, TextWriter writer) { var cluster = table.PrimaryKey; if (cluster == null) return; if (cluster.IsConstraint) { // alter table <table> add constraint <name> {primary key | unique} {clustered | nonclustered} (<columns>) writer.Write("alter table {0} add constraint [{1}]", table.Name, cluster.Name); if (cluster.IsPrimaryKey) writer.Write(" primary key"); else if (cluster.IsUnique) writer.Write(" unique"); writer.Write(cluster.IsClustered ? " clustered" : " nonclustered"); writer.Write(" ("); WritePrimaryKeyColumnsDDL(cluster.Columns, writer); writer.Write(")"); } else { // create [unique] {clustered | nonclustered} index <name> on <table> (<columns>) writer.Write("create"); if (cluster.IsUnique) writer.Write(" unique"); writer.Write(cluster.IsClustered ? " clustered" : " nonclustered"); writer.Write(" index [{1}] on {0}", table.Name, cluster.Name); writer.Write(" ("); WritePrimaryKeyColumnsDDL(cluster.Columns, writer); writer.Write(")"); } }
private static void PreCopyTable(TableInfo table, SqlConnection destcn) { if (table.HasIdentity) ExecuteSql(destcn, String.Format("set IDENTITY_INSERT {0} on", table.Name)); }
private static TableInfo LoadTableInfo(IDictionary<TableName, TableInfo> tables, TableName tablename) { TableInfo table; if (!tables.TryGetValue(tablename, out table)) { table = new TableInfo(tablename); tables.Add(table.Name, table); } return table; }
private static string GenerateColumns(TableInfo table, string format) { StringBuilder s = new StringBuilder(1024); bool needcomma = false; foreach (ColumnInfo column in table.Columns) { if (column.Calculation != null) continue; if (needcomma) s.Append(","); else needcomma = true; if (format == null) s.Append(column.Name); else s.AppendFormat(format, column.Name); } return s.ToString(); }
private static string GenerateColumns(TableInfo table) { return GenerateColumns(table, "[{0}]"); }