int CopyServerTables(string sourceTable, string targetTable)
{
int copyTableCount = 0, copyTableTotal = 0, copied = 0,
copyRecordCount = SourceCmd.TableCount(sourceTable);
int deleted = targetCmd.ModifyTable("DELETE " + targetTable);
for (copyLoop = 0; copyLoop < 100 && !Abort && !SQLHandler.HasErrors && copyTableCount >= 0 && (copied += copyTableCount) < copyRecordCount;)
{
int targetCount = targetCmd.TableCount(targetTable);
Status(string.Format("SP Copying {0,8:n0} Records to {1} {2}",
copyRecordCount, targetTable,
++copyLoop > 1 ? string.Format("(Pass {0} - {1:n0} Remaining)", copyLoop, copyRecordCount - targetCount)
: ""));
if (copyLoop > 1)
{
copied = targetCount; // A timeout must have occurred. Sync up copied total to actual...
}
SQLHandler.Clear();
CopyServerTables(targetTable, ref copyTableCount, ref copyTableTotal);
if (SQLHandler.HasErrors)
{
Memo("Error", string.Format("Copy {0}Table: {1, 7} Records to {2} WERE NOT COPIED. See errors below -\r\n\r\n{3}",
copyModifier, "ERROR", targetTable, SQLHandler.Errors));
}
}
Status("");
return(copyTableCount < 0 ? copyTableCount : copyTableTotal);
}
int CopyObject(string text, string type)
{
targetObject.ObjectName = sourceObject.ObjectName;
Status(string.Format("Copying {0}: {1}", text, targetObject.SchemaObject));
SQLHandler.Clear();
int size = CopyObject(type);
if (SQLHandler.HasErrors)
{
Memo("Error", string.Format("Copy Schema {0}s: {1, 7} {2} WAS NOT Copied because of the following SQL Errors\r\n\r\n{3}",
text, "ERROR", targetObject.ServerIDObjectName, SQLHandler.Errors));
}
else
{
Memo("Memo", string.Format("Copy Schema {0}s: {1, 7:n0} Bytes {2}", text, size, targetObject.ObjectName));
}
return(size);
}
/// <summary> Retrieves and Executes the script to restore table records
/// <para> . Retrieves the script from the current position in the zip file </para>
/// <para> . Parses and executes the script, record by record </para>
/// <para> . If .restoreIdentity is set, prepends and appends each inserted record text with the necessary SET_IDENTITY_INSERT statements</para>
/// Returns the number of records restored
/// </summary>
/// <returns></returns>
int RestoreTableData()
{
int count = 0, tableCount = 0;
string zipSchema = zipFile.Schema + ".",
targetSchema = TargetSchema + ".",
tablename = TargetSchema + "." + zipFile.Name,
process = "Restore User Tables";
long position = zipFile.Position;
string[] lines = zipFile.InflateLines();
if (lines == null)
{
Memo("Error", string.Format("{0}: {1, 7} Unable to restore table data - {2}\r\n{3,23} Unable to retrieve data content\r\n",
process, "ERROR", tablename, ""));
}
else
{
SQLHandler.Clear();
try
{
string text,
columnNames = lines[0]; // column names in (ColumnName1,...,) format
string identityInsertOn = restoreIdentity ? "SET IDENTITY_INSERT " + tablename + " ON " : "",
identityInsertOff = restoreIdentity && identityInsertOn.Length > 0 ? "SET IDENTITY_INSERT " + tablename + " OFF " : "";
Status("Restoring Tables: " + tablename);
for (int i = 1; i < lines.Length && !Abort;)
{
Status("Restoring Tables: " + tablename + " - " + (lines.Length - i).ToString());
string cmd = "";
for (; i < lines.Length && cmd.Length < 200000; i++)
{
if ((text = lines[i]).Length > 0 && text.IndexOf("System.") < 0)
{
text.Replace(zipSchema, targetSchema);
int ifirst = text.IndexOf('\''); // Look for internal single quotes
if (ifirst >= 0)
{
parseQuotes = false;
for (int paren = RestoreParse(text); paren > 0 && ++i < lines.Length && !Abort;)
{
string txt = lines[i];
paren += RestoreParse(txt);
text += txt;
}
}
cmd += string.Format("{0} INSERT INTO {1} {2} VALUES {3} {4} \r\n",
identityInsertOn, tablename, columnNames, text, identityInsertOff);
count++;
}
}
if (cmd.Length > 0)
{
targetCmd.ModifyTable(cmd);
if (cmd.Length < minCommand)
{
minCommand = cmd.Length;
}
if (cmd.Length > maxCommand)
{
maxCommand = cmd.Length;
}
if (cmd.Length < 1025 && cmd.Length > maxCommand1024)
{
maxCommand1024 = cmd.Length;
}
if (cmd.Length > 1024 && cmd.Length < 2049 && cmd.Length > maxCommand2048)
{
maxCommand2048 = cmd.Length;
}
Thread.Sleep(25); // Sleeping avoids unexplained errors. Probably not handling asynchronous server executions and repsonses properly
}
}
tableCount = targetCmd.TableCount(tablename);
}
finally
{
Status("");
Memo(SQLHandler.HasErrors ? "Error" : "Totals", string.Format("{0}{1}: {2, 7:n0} {3}Records restored to {4}{5}",
SQLHandler.HasErrors ? "\r\n" : "", process,
tableCount, tableCount != count ? "Of " + count.ToString() + " " : "", tablename,
SQLHandler.HasErrors ? " - With Errors\r\n" + SQLHandler.Errors : ""));
}
}
return(count);
}
int CopyTables(DataTable dependencies)
{
Memo("Memo", "Copy Schema Tables: Deleting table content...\r\n");
int count = copyTablesCount = 0;
//
// Delete table content in reverse order of dependency
//
for (int i = dependencies.Rows.Count; i-- > 0;)
{
targetTable.TableName = (string)dependencies.Rows[i]["Name"];
string databaseOwnerTable = targetTable.DatabaseSchemaTable;
if (IsValidTablename(targetTable.TableName) &&
!BypassTables.Contains(targetTable.TableName) && targetCmd.TableExists(databaseOwnerTable)) //.Name))
{
Status("Deleting content: " + databaseOwnerTable);
targetCmd.Execute("DELETE " + databaseOwnerTable);
if (SQLHandler.HasErrors)
{
Memo("Memo", "Copy Schema Tables: ERROR Deleting " + databaseOwnerTable + "\r\n" + SQLHandler.Errors);
copyTablesCount--;
}
}
}
if (copyTablesCount < 0)
{
return(0);
}
//
// Copy tables in ascending order of dependency
//
for (int i = 0; i < dependencies.Rows.Count && !Abort; i++)
{
sourceTable.TableName = (string)dependencies.Rows[i]["Name"];
targetTable.TableName = sourceTable.TableName;
if (!IsValidTablename(sourceTable.TableName) ||
BypassTables.Contains(sourceTable.TableName)) // BypassTables used for testing purposes
{
continue;
}
SQLHandler.Clear();
do
{
SQLHandler.TryAgain = false;
bool okay = CopyTable(CopySourceTable, CopyTargetTable, "Schema ");
if (Abort || SQLHandler.HasErrors)
{
break;
}
if (!SQLHandler.TryAgain && okay)
{
copyTablesCount++;
count++;
}
}while (SQLHandler.TryAgain);
}
return(count);
}
/// <summary>Restores table content for the table specified in restoreObject (.FileName = '[tablename].dat');
/// Restores content found in the data file ([selected path]\Tables\Data\[tablename].dat) and;
/// Returns the number of records restored or -1 if a problem was encountered
/// </summary>
/// <param name="dataPath"></param>
/// <returns></returns>
int RestoreTableData(string dataPath)
{
int count = -1;
string dataName = restoreObject.TableFileName,
tablename = restoreObject.TableName;
if (!File.Exists(dataPath))
{
Memo("Memo", string.Format("Restore User Tables: {0, 7} Unable to restore table data - {1}" +
"\r\n{23, 7} Data content file not found - {3}\r\n",
"ERROR", tablename, "", dataPath));
}
else
{
SQLHandler.Clear();
StreamReader dataFile = File.OpenText(dataPath);
try
{
string text,
columns = dataFile.ReadLine(); // column types in (DataType1...) format
string identityInsertOn = restoreIdentity ? "SET IDENTITY_INSERT " + tablename + " ON " : "",
identityInsertOff = restoreIdentity && identityInsertOn.Length > 0 ? "SET IDENTITY_INSERT " + tablename + " OFF " : "";
Status("Restoring " + tablename);
for (count = 0; !dataFile.EndOfStream && (text = dataFile.ReadLine()).Length > 0 && !Abort;)
{
if (text.IndexOf("System.") < 0)
{
int ifirst = text.IndexOf('\''),
ilast = text.LastIndexOf('\'');
if (ifirst >= 0)
{
for (int paren = RestoreParse(text); paren > 0 && !dataFile.EndOfStream && !Abort;)
{
string txt = dataFile.ReadLine();
paren += RestoreParse(txt);
text += txt;
}
}
targetCmd.ModifyTable(string.Format("{0} INSERT INTO {1} {2} VALUES {3} {4}",
identityInsertOn, tablename, columns, text, identityInsertOff));
count++;
}
}
int tableCount = targetCmd.TableCount(restoreObject.TableName);
Status("");
Memo("Memo", string.Format("{4}Restore User Tables: {0, 7:n0} {1}Records restored to {2}{3}",
tableCount, tableCount != count ? "Of " + count.ToString() + " " : "", restoreObject.TableName,
SQLHandler.HasErrors ? " - With Errors\r\n" + SQLHandler.Errors : "",
SQLHandler.HasErrors ? "\r\n" : ""));
}
finally
{
if (dataFile != null)
{
dataFile.Close();
}
}
}
return(count);
}