private void btnApplyChangesToDb_Click(object sender, EventArgs e)
{
Excel.Worksheet sheet = null;
Excel.CustomProperty primaryKeyProperty = null;
string primaryKey = string.Empty;
try
{
if (MessageBox.Show("This will commit the changes to the database. This action cannot be reversed. Are you sure?", "Confirm", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Warning) == DialogResult.Yes)
{
sheet = ExcelApp.ActiveSheet as Excel.Worksheet;
if (sheet != null)
{
primaryKeyProperty = sheet.GetProperty("PrimaryKey");
if (primaryKeyProperty != null)
{
primaryKey = primaryKeyProperty.Value.ToString();
string sql = sheet.ChangesToSql(this.tableName, primaryKey);
sql += Environment.NewLine;
sql += sheet.DeleteRowsFromTable(this.tableName, false);
sql += Environment.NewLine;
sql += sheet.InsertRowsIntoTable(this.tableName);
if (!string.IsNullOrEmpty(sql))
{
using (SqlConnection conn = new SqlConnection(dcd.ConnectionString))
{
SqlCommand cmd = new SqlCommand(sql, conn);
if (conn.State == ConnectionState.Closed)
{
conn.Open();
cmd.ExecuteNonQuery();
}
}
RefreshSheetData();
}
}
}
}
}
finally
{
if (primaryKeyProperty != null)
{
Marshal.ReleaseComObject(primaryKeyProperty);
}
if (sheet != null)
{
Marshal.ReleaseComObject(sheet);
}
}
}
private void btnSaveChangesToFile_Click(object sender, EventArgs e)
{
Excel.Worksheet sheet = null;
Excel.CustomProperty primaryKeyProperty = null;
string primaryKey = string.Empty;
string tableName = string.Empty;
try
{
sheet = ExcelApp.ActiveSheet as Excel.Worksheet;
if (sheet != null)
{
tableName = sheet.Name;
primaryKeyProperty = sheet.GetProperty("PrimaryKey");
if (primaryKeyProperty != null)
{
primaryKey = primaryKeyProperty.Value.ToString();
string sql = sheet.ChangesToSql(tableName, primaryKey);
diagSaveFile.ShowDialog();
if (!string.IsNullOrEmpty(diagSaveFile.FileName))
{
File.WriteAllText(diagSaveFile.FileName, sql);
RefreshSheetData();
}
}
}
}
finally
{
if (primaryKeyProperty != null)
{
Marshal.ReleaseComObject(primaryKeyProperty);
}
if (sheet != null)
{
Marshal.ReleaseComObject(sheet);
}
}
}
