internal void Initialize(SyncDatabaseConfig config, DataSet dataSet)
{
// Connection String
this.cboConnectionString.Items.Clear();
foreach (string connectionString in config.ConnectionStrings)
{
this.cboConnectionString.Items.Add(connectionString);
}
this.cboConnectionString.SelectedIndex = 0;
// DataSet
StringBuilder builder = new StringBuilder();
for (int i = dataSet.Tables.Count - 1; i >= 0; i--)
{
DataTable table = dataSet.Tables[i];
if (!SyncDatabaseConfig.DefaultSheetName.Equals(table.TableName, StringComparison.OrdinalIgnoreCase))
{
builder.AppendLine($"DELETE FROM [{table.TableName}]");
builder.AppendLine($"IF EXISTS (SELECT 1 FROM sys.identity_columns WHERE object_id = object_id('{table.TableName}')) DBCC CHECKIDENT('{table.TableName}', RESEED, 0)");
}
}
this.txtClearSQL.Text = builder.ToString();
this.chkClear.Checked = config.ClearTableBeforeImport;
// Model
(this.panSyncDatabaseAction.Controls.Find($"rdoSyncDatabaseAction{config.Action}", false)[0] as RadioButton).Checked = true;
(this.panSyncDatabaseMode.Controls.Find($"rdoSyncDatabaseMode{config.Mode}", false)[0] as RadioButton).Checked = true;
}
public override bool Launch()
{
if (!this.InputFilePath.EndsWith(".xlsx"))
{
throw new InvalidDataException("Input file should be excel file.");
}
using (FileStream stream = new FileStream(this.InputFilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
this._dataSet = EPPlusHelper.ReadExcelToDataSet(stream);
}
this._config = SyncDatabaseConfig.CreateFromExcel(this.InputFilePath, this._dataSet);
if (this._config == null)
{
throw new InvalidDataException("Could not load config.");
}
if (this._config.ConnectionProvider != "System.Data.SqlClient")
{
throw new InvalidDataException("Only support System.Data.SqlClient for ConnectionProvider currently.");
}
SyncDatabaseExecuteConfig executeConfig = this.LoadExecuteConfig();
if (executeConfig == null)
{
return(false);
}
string script = this.GenerateScript(executeConfig);
if (script == null)
{
return(false);
}
switch (executeConfig.Action)
{
case SyncDatabaseAction.GenerateScript:
{
string outputFilePath = this.GetOutputFilePathByExtension(".sql");
if (this.ProjectItem.ContainingProject.Kind.Equals(Parameters.guidSQLServerDatabaseProject, StringComparison.OrdinalIgnoreCase))
{
System.IO.File.WriteAllText(outputFilePath, script);
}
else
{
this.ProjectItem.DeleteDependentFiles();
System.IO.File.WriteAllText(outputFilePath, script);
this.ProjectItem.Collection.AddFromFile(outputFilePath);
this.ProjectItem.AddDependentFromFiles(outputFilePath);
}
}
break;
case SyncDatabaseAction.ImportDatabase:
{
using (SqlConnection connection = new SqlConnection(executeConfig.ConnectionString))
using (SqlTransaction transcation = connection.BeginTransaction())
using (SqlCommand command = connection.CreateCommand())
{
try
{
command.CommandText = script;
command.Transaction = transcation;
command.ExecuteNonQuery();
transcation.Commit();
System.Windows.Forms.MessageBox.Show("Operation Successfully.");
}
finally
{
transcation.Rollback();
}
}
}
break;
}
return(true);
}
public static SyncDatabaseConfig CreateFromExcel(string excelFilePath, DataSet dataSet)
{
if (excelFilePath == null)
{
throw new ArgumentNullException(nameof(excelFilePath));
}
if (dataSet == null)
{
throw new ArgumentNullException(nameof(dataSet));
}
DataTable table = dataSet.Tables[DefaultSheetName];
if (table == null)
{
return(null);
}
SyncDatabaseConfig config = new SyncDatabaseConfig();
// Action
config.Action = (SyncDatabaseAction)Enum.Parse(typeof(SyncDatabaseAction), Convert.ToString(table.Rows[0]["Action"]));
// ConnectionProvider
config.ConnectionProvider = Convert.ToString(table.Rows[0]["ConnectionProvider"]);
// Source
SyncDatabaseConnectionStringSource connectionStringSource = (SyncDatabaseConnectionStringSource)Enum.Parse(typeof(SyncDatabaseConnectionStringSource), Convert.ToString(table.Rows[0]["ConnectionStringSource"]));
switch (connectionStringSource)
{
case SyncDatabaseConnectionStringSource.Static:
{
config.ConnectionStrings = Convert.ToString(table.Rows[0]["ConnectionStringExpression"])
.Split(new string[] { "\r\n" }, StringSplitOptions.RemoveEmptyEntries)
.ToArray();
}
break;
case SyncDatabaseConnectionStringSource.FileRegex:
{
config.ConnectionStrings = Convert.ToString(table.Rows[0]["ConnectionStringExpression"])
.Split(new string[] { "\r\n" }, StringSplitOptions.RemoveEmptyEntries)
.Select(p =>
{
string[] segments = p.Split('|');
string connectionStringFile = Path.GetFullPath(Path.Combine(Path.GetDirectoryName(excelFilePath), segments[0]));
Match match = Regex.Match(System.IO.File.ReadAllText(connectionStringFile), segments[1]);
if (!match.Success)
{
return(null);
}
Group group = match.Groups["ConnectionString"];
if (!group.Success)
{
return(null);
}
return(group.Value);
})
.ToArray();
}
break;
default:
break;
}
config.ClearTableBeforeImport = Convert.ToBoolean(table.Rows[0]["ClearTableBeforeImport"]);
config.Mode = (SyncDatabaseMode)Enum.Parse(typeof(SyncDatabaseMode), Convert.ToString(table.Rows[0]["ImportMode"]));
return(config);
}
