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); } }