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);
}
}
}
public void RefreshChanges()
{
Excel.Worksheet activeSheet = null;
Excel.CustomProperty changesProperty = null;
string xml = string.Empty;
string sql = string.Empty;
try
{
activeSheet = ExcelApp.ActiveSheet as Excel.Worksheet;
changesProperty = activeSheet.GetProperty("UncommittedChanges");
lvSheetChanges.Items.Clear();
if (changesProperty != null)
{
lvSheetChanges.Visible = true;
xml = ToSafeXml("<uncommittedchanges>" + changesProperty.Value.ToString() + "</uncommittedchanges>");
XDocument doc = XDocument.Parse(xml);
foreach (var dm in doc.Descendants("row"))
{
ListViewItem item = new ListViewItem(new string[]
{
dm.Attribute("key").Value,
dm.Attribute("column").Value,
dm.Value
});
lvSheetChanges.Items.Add(item);
}
}
sql = activeSheet.DeleteRowsFromTable(this.tableName, true);
var primaryKey = activeSheet.PrimaryKey();
if (!string.IsNullOrEmpty(sql))
{
using (SqlConnection conn = new SqlConnection(dcd.ConnectionString))
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
SqlDataReader dbReader;
conn.Open();
dbReader = cmd.ExecuteReader();
while (dbReader.Read())
{
ListViewItem item = new ListViewItem(new string[]
{
dbReader[primaryKey].ToString(),
primaryKey,
"delete"
});
lvSheetChanges.Items.Add(item);
}
}
conn.Close();
}
}
}
catch (Exception ex)
{
Console.Write(ex.Message);
}
finally
{
//if (activeSheet != null) Marshal.ReleaseComObject(activeSheet);
//if (changesProperty != null) Marshal.ReleaseComObject(changesProperty);
}
}
