/// <summary>
/// The bit that does it all.
/// </summary>
private void ScriptUmbraco()
{
if ( this.InvokeRequired )
{
MethodInvoker invoker = new MethodInvoker( this.ScriptUmbraco );
this.Invoke( invoker );
return;
}
// Some starting variables
bool scriptDb = this.checkBoxCreate.Checked;
bool filePerTable = this.checkBoxSingleFile.Checked;
bool setDateFormat = this.checkBoxDateFormat.Checked;
string path = this.textBoxOutputDir.Text;
string sourceDsn = this.textBoxSource.Text;
bool errors = false;
// Clear the log
this.ClearLog();
//
// Delete Step1,Step2,Step3
//
try
{
File.Delete( string.Format( @"{0}\{1}", path, "Step1.sql" ) );
File.Delete( string.Format( @"{0}\{1}", path, "Step2.sql" ) );
File.Delete( string.Format( @"{0}\{1}", path, "Step3.sql" ) );
}
catch ( IOException e )
{
this.Log( e );
errors = true;
}
// This holds the entire insert statement
StringBuilder insertStmt = new StringBuilder();
this.toolStripStatusLabel1.Text = "Scripting objects";
//
// Script the drop/create objects if asked for
//
if ( scriptDb )
{
try
{
StreamWriter writer = new StreamWriter( string.Format( @"{0}\{1}", path, "Step1.sql" ), false, Encoding.Unicode );
writer.Write(this.GetCreateObjectsSql());
writer.WriteLine( "" );
writer.WriteLine("/* CREATE VIEWS */");
writer.Write(this.GetCreateViewsSql());
writer.Close();
}
catch ( IOException e )
{
this.Log( e );
errors = true;
}
}
// Return if the cancel button is pushed
if ( this._cancel )
return;
// Default the dateformat
if ( setDateFormat )
insertStmt.AppendLine( string.Format( "SET DATEFORMAT {0}", this.textBoxDateFormat.Text ) );
try
{
using ( SqlConnection sourceConnection = new SqlConnection( sourceDsn ) )
{
// Open the source
sourceConnection.Open();
SqlCommand insertCommand = new SqlCommand();
// Loop through all tables we have
for ( int i = 0; i < _tableNames.Count; i++ )
{
// Return if the cancel button is pushed
if ( this._cancel )
return;
//
// Set up the statements to use
//
string tableName = _tableNames[i].ToString();
string sql = string.Format( "SELECT * FROM {0}", tableName );
string identityOn = string.Format( "SET IDENTITY_INSERT [{0}] ON", tableName );
string identityOff = string.Format( "SET IDENTITY_INSERT [{0}] OFF", tableName );
//
// Check for log tables etc. and ignore if set
//
if ( ( tableName == "umbracoLog" || tableName == "umbracoUserLogins" || tableName == "umbracoStatSession" ) &&
this.checkBoxIgnoreLogTable.Checked )
{
continue;
}
this.toolStripStatusLabel1.Text = string.Format( "Getting data from {0}", tableName );
Application.DoEvents();
//
// Grab column names for this table
//
SqlCommand command = new SqlCommand( sql, sourceConnection );
SqlDataAdapter adapter = new SqlDataAdapter( command );
DataSet ds = new DataSet();
adapter.Fill( ds );
string colNames = "";
for ( int n = 0; n < ds.Tables[0].Columns.Count; n++ )
{
colNames += "[" + ds.Tables[0].Columns[n].ColumnName + "],";
}
// Remove the last comma
colNames = colNames.Remove( colNames.Length - 1 );
//
// Work out if the table needs SET_IDENTITY on/off
//
bool identity = false;
if ( this._identityTables.Contains( tableName ) )
identity = true;
// Get the number of rows in the table, so the toolstrip
// can give an indication of progress
SqlCommand countCommand = new SqlCommand( string.Format( "SELECT COUNT(*) FROM {0}", tableName ), sourceConnection );
int rowCount = (int) countCommand.ExecuteScalar();
int j = 0;
// Append ON if it has an identity
if ( identity && rowCount > 0 )
insertStmt.AppendLine( identityOn );
//
// Go through all rows
//
SqlDataReader reader = command.ExecuteReader();
while ( reader.Read() )
{
// Return if the cancel button is pushed
if ( this._cancel )
return;
int cols = reader.FieldCount;
StringBuilder colVals = new StringBuilder();
this.toolStripStatusLabel1.Text = string.Format( "Getting data from {0} {1}/{2}", tableName, j, rowCount );
Application.DoEvents();
//
// Loop through all columns and grab their values
//
for ( int n = 0; n < cols; n++ )
{
string comma = ",";
// Remove the last comma
if ( n == cols - 1 )
comma = "";
StringBuilder valBuilder = new StringBuilder();
if ( reader[n].GetType() == typeof( Boolean ) )
{
if ( (bool) reader[n] )
valBuilder.Append( "1" );
else
valBuilder.Append( "0" );
}
else if ( reader[n] == DBNull.Value )
{
valBuilder.Append( "NULL" );
}
else
{
valBuilder.AppendFormat( "'{0}'", reader[n].ToString().Replace( "'", "''" ) );
}
colVals.AppendFormat( "{0}{1}", valBuilder.ToString(), comma );
}
// Add to the big statement
insertStmt.AppendFormat( "INSERT INTO {0} ({1}) VALUES ({2})\r\n", tableName, colNames, colVals.ToString() );
j++;
}
// Append OFF if it has an identity
if ( identity && rowCount > 0 )
insertStmt.AppendLine( identityOff );
// Close the reader explicitly
reader.Close();
// Save to disk
if ( filePerTable && insertStmt.Length > 0 )
{
// Delete any existing
File.Delete( string.Format( @"{0}\{1}.sql", path, tableName ) );
try
{
StreamWriter writer = new StreamWriter( string.Format( @"{0}\{1}.sql", path, tableName ), false, Encoding.Unicode );
writer.Write( insertStmt.ToString() );
writer.Close();
// Reset the script buffer
insertStmt = new StringBuilder();
}
catch ( IOException e )
{
this.Log( e );
}
}
}
}
}
catch ( SqlException e )
{
this.Log( e );
errors = true;
}
if ( !errors )
{
//
// Save to single data file
//
if ( !filePerTable )
{
try
{
StreamWriter writer = new StreamWriter(string.Format( @"{0}\{1}", path, "Step2.sql" ),false,Encoding.Unicode );
writer.Write( insertStmt.ToString() );
writer.Close();
}
catch ( Exception e )
{
this.Log( e );
errors = true;
}
}
//
// Script the foreign keys
//
if ( scriptDb )
{
this.toolStripStatusLabel1.Text = "Saving data file...";
Application.DoEvents();
try
{
StreamWriter writer = new StreamWriter( string.Format( @"{0}\{1}", path, "Step3.sql" ), false, Encoding.Unicode );
writer.Write( this.GetCreateKeysSql() );
writer.Close();
}
catch ( IOException e )
{
this.Log( e );
errors = true;
}
}
}
this.buttonBackup.Text = "Backup";
this.buttonBackup.Enabled = true;
this._cancel = false;
// Recycle the builder
insertStmt = new StringBuilder();
this.toolStripStatusLabel1.Text = "Done";
//
// Show the log window if there were errors
//
if ( errors )
{
DialogResult result = MessageBox.Show("The backup completed but with errors. It is advised that you don't run any of the scripts unless you expected the errors.\n\n Do you want to see the error log?","Error", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
if ( result == DialogResult.Yes )
{
FormErrorlog formLog = new FormErrorlog(this._log);
formLog.Show();
}
}
}
/// <summary>
/// The bit that does it all.
/// </summary>
private void ScriptUmbraco()
{
if (this.InvokeRequired)
{
MethodInvoker invoker = new MethodInvoker(this.ScriptUmbraco);
this.Invoke(invoker);
return;
}
// Some starting variables
bool scriptDb = this.checkBoxCreate.Checked;
bool filePerTable = this.checkBoxSingleFile.Checked;
bool setDateFormat = this.checkBoxDateFormat.Checked;
string path = this.textBoxOutputDir.Text;
string sourceDsn = this.textBoxSource.Text;
bool errors = false;
// Clear the log
this.ClearLog();
//
// Delete Step1,Step2,Step3
//
try
{
File.Delete(string.Format(@"{0}\{1}", path, "Step1.sql"));
File.Delete(string.Format(@"{0}\{1}", path, "Step2.sql"));
File.Delete(string.Format(@"{0}\{1}", path, "Step3.sql"));
}
catch (IOException e)
{
this.Log(e);
errors = true;
}
// This holds the entire insert statement
StringBuilder insertStmt = new StringBuilder();
this.toolStripStatusLabel1.Text = "Scripting objects";
//
// Script the drop/create objects if asked for
//
if (scriptDb)
{
try
{
StreamWriter writer = new StreamWriter(string.Format(@"{0}\{1}", path, "Step1.sql"), false, Encoding.Unicode);
writer.Write(this.GetCreateObjectsSql());
writer.WriteLine("");
writer.WriteLine("/* CREATE VIEWS */");
writer.Write(this.GetCreateViewsSql());
writer.Close();
}
catch (IOException e)
{
this.Log(e);
errors = true;
}
}
// Return if the cancel button is pushed
if (this._cancel)
{
return;
}
// Default the dateformat
if (setDateFormat)
{
insertStmt.AppendLine(string.Format("SET DATEFORMAT {0}", this.textBoxDateFormat.Text));
}
try
{
using (SqlConnection sourceConnection = new SqlConnection(sourceDsn))
{
// Open the source
sourceConnection.Open();
SqlCommand insertCommand = new SqlCommand();
// Loop through all tables we have
for (int i = 0; i < _tableNames.Count; i++)
{
// Return if the cancel button is pushed
if (this._cancel)
{
return;
}
//
// Set up the statements to use
//
string tableName = _tableNames[i].ToString();
string sql = string.Format("SELECT * FROM {0}", tableName);
string identityOn = string.Format("SET IDENTITY_INSERT [{0}] ON", tableName);
string identityOff = string.Format("SET IDENTITY_INSERT [{0}] OFF", tableName);
//
// Check for log tables etc. and ignore if set
//
if ((tableName == "umbracoLog" || tableName == "umbracoUserLogins" || tableName == "umbracoStatSession") &&
this.checkBoxIgnoreLogTable.Checked)
{
continue;
}
this.toolStripStatusLabel1.Text = string.Format("Getting data from {0}", tableName);
Application.DoEvents();
//
// Grab column names for this table
//
SqlCommand command = new SqlCommand(sql, sourceConnection);
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataSet ds = new DataSet();
adapter.Fill(ds);
string colNames = "";
for (int n = 0; n < ds.Tables[0].Columns.Count; n++)
{
colNames += "[" + ds.Tables[0].Columns[n].ColumnName + "],";
}
// Remove the last comma
colNames = colNames.Remove(colNames.Length - 1);
//
// Work out if the table needs SET_IDENTITY on/off
//
bool identity = false;
if (this._identityTables.Contains(tableName))
{
identity = true;
}
// Get the number of rows in the table, so the toolstrip
// can give an indication of progress
SqlCommand countCommand = new SqlCommand(string.Format("SELECT COUNT(*) FROM {0}", tableName), sourceConnection);
int rowCount = (int)countCommand.ExecuteScalar();
int j = 0;
// Append ON if it has an identity
if (identity && rowCount > 0)
{
insertStmt.AppendLine(identityOn);
}
//
// Go through all rows
//
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
// Return if the cancel button is pushed
if (this._cancel)
{
return;
}
int cols = reader.FieldCount;
StringBuilder colVals = new StringBuilder();
this.toolStripStatusLabel1.Text = string.Format("Getting data from {0} {1}/{2}", tableName, j, rowCount);
Application.DoEvents();
//
// Loop through all columns and grab their values
//
for (int n = 0; n < cols; n++)
{
string comma = ",";
// Remove the last comma
if (n == cols - 1)
{
comma = "";
}
StringBuilder valBuilder = new StringBuilder();
if (reader[n].GetType() == typeof(Boolean))
{
if ((bool)reader[n])
{
valBuilder.Append("1");
}
else
{
valBuilder.Append("0");
}
}
else if (reader[n] == DBNull.Value)
{
valBuilder.Append("NULL");
}
else
{
valBuilder.AppendFormat("'{0}'", reader[n].ToString().Replace("'", "''"));
}
colVals.AppendFormat("{0}{1}", valBuilder.ToString(), comma);
}
// Add to the big statement
insertStmt.AppendFormat("INSERT INTO {0} ({1}) VALUES ({2})\r\n", tableName, colNames, colVals.ToString());
j++;
}
// Append OFF if it has an identity
if (identity && rowCount > 0)
{
insertStmt.AppendLine(identityOff);
}
// Close the reader explicitly
reader.Close();
// Save to disk
if (filePerTable && insertStmt.Length > 0)
{
// Delete any existing
File.Delete(string.Format(@"{0}\{1}.sql", path, tableName));
try
{
StreamWriter writer = new StreamWriter(string.Format(@"{0}\{1}.sql", path, tableName), false, Encoding.Unicode);
writer.Write(insertStmt.ToString());
writer.Close();
// Reset the script buffer
insertStmt = new StringBuilder();
}
catch (IOException e)
{
this.Log(e);
}
}
}
}
}
catch (SqlException e)
{
this.Log(e);
errors = true;
}
if (!errors)
{
//
// Save to single data file
//
if (!filePerTable)
{
try
{
StreamWriter writer = new StreamWriter(string.Format(@"{0}\{1}", path, "Step2.sql"), false, Encoding.Unicode);
writer.Write(insertStmt.ToString());
writer.Close();
}
catch (Exception e)
{
this.Log(e);
errors = true;
}
}
//
// Script the foreign keys
//
if (scriptDb)
{
this.toolStripStatusLabel1.Text = "Saving data file...";
Application.DoEvents();
try
{
StreamWriter writer = new StreamWriter(string.Format(@"{0}\{1}", path, "Step3.sql"), false, Encoding.Unicode);
writer.Write(this.GetCreateKeysSql());
writer.Close();
}
catch (IOException e)
{
this.Log(e);
errors = true;
}
}
}
this.buttonBackup.Text = "Backup";
this.buttonBackup.Enabled = true;
this._cancel = false;
// Recycle the builder
insertStmt = new StringBuilder();
this.toolStripStatusLabel1.Text = "Done";
//
// Show the log window if there were errors
//
if (errors)
{
DialogResult result = MessageBox.Show("The backup completed but with errors. It is advised that you don't run any of the scripts unless you expected the errors.\n\n Do you want to see the error log?", "Error", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
if (result == DialogResult.Yes)
{
FormErrorlog formLog = new FormErrorlog(this._log);
formLog.Show();
}
}
}
