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