public static void DbDeleteRecord(WindowMetaList windowMetaList, DataGrid winDg)
//deletes the selected row from the database
{
string sql = string.Empty;
try
{
Int32 selectedRowIdVal = WindowTasks.DataGridGetId(winDg);
//Delete the selected row from db
sql = "DELETE FROM " + windowMetaList.SchemaName + "." + windowMetaList.TableName + " WHERE " + windowMetaList.TableKey + " = @Id";
NpgsqlCommand delRowSql = new NpgsqlCommand
{
CommandText = sql,
CommandType = CommandType.Text,
Connection = windowMetaList.ApplicationDb
};
delRowSql.Parameters.AddWithValue("@Id", selectedRowIdVal);
windowMetaList.ApplicationDb.Open();
delRowSql.ExecuteNonQuery();
windowMetaList.ApplicationDb.Close();
}
catch (Exception ex)
{
WindowTasks.DisplayError(ex, "Cannot Delete Record:" + ex.Message, sql);
windowMetaList.ApplicationDb.Close();
};
}
public static void WinDataGridClicked(WindowMetaList windowMetaList, DataGrid winDg, Int32 selectedRowIdVal, StackPanel editStkPnl, Dictionary <string, string> controlValues)
//gets the id of the row selected and loads the edit fileds with the database values
{
if (selectedRowIdVal == 0)
{
selectedRowIdVal = WindowTasks.DataGridGetId(winDg);
}
try
{
if (selectedRowIdVal != 0)
{
DataTable winSelectedRowDataTable = DbGetDataRow(windowMetaList, selectedRowIdVal, editStkPnl);
WindowDataOps.WinLoadDataRow(editStkPnl, winSelectedRowDataTable, controlValues);
winDg.UpdateLayout();
}
}
catch (Exception ex)
{
WindowTasks.DisplayError(ex, "Problem Loading Data Grid:", null);
}
}
public static Boolean DbUpdateRecord(WindowMetaList windowMetaList, DataGrid winDg, StackPanel editStkPnl)
//updates the database with values in the data edit fields
{
string sql = string.Empty;
try
{
Int32 selectedRowIdVal = WindowTasks.DataGridGetId(winDg);
DataTable winSelectedRowDataTable = WindowDataOps.DbGetDataRow(windowMetaList, selectedRowIdVal, editStkPnl);
Boolean isDirty = false;
foreach (DataRow row in winSelectedRowDataTable.Rows)
{
sql = "UPDATE " + windowMetaList.SchemaName + "." + windowMetaList.TableName + " SET ";
foreach (DataColumn col in winSelectedRowDataTable.Columns)
{
//Build the SQL Statement to update changed values
//Determine the Type of control
object obj = editStkPnl.FindName(col.ColumnName);
string ctlName = obj.GetType().Name;
//Use Type to work out how to process value;
switch (ctlName)
{
case "TextBox":
TextBox tb = (TextBox)editStkPnl.FindName(col.ColumnName);
if (tb.Text.ToString() != row[col].ToString())
{
if (tb.Tag.ToString() != "NUM")
{
sql = sql + col.ColumnName + " = '" + tb.Text.Replace("'", "''") + "', ";
}
else
{
if (row[col].ToString() == "")
{
tb.Text = "0";
}
sql = sql + col.ColumnName + " = " + tb.Text + ", ";
}
isDirty = true;
}
break;
case "ComboBox":
ComboBox cb = (ComboBox)editStkPnl.FindName(col.ColumnName);
if (cb.SelectedValue != null)
{
if (cb.SelectedValue.ToString() != row[col].ToString())
{
sql = sql + col.ColumnName + " = " + cb.SelectedValue + ", ";
isDirty = true;
}
}
break;
case "DatePicker":
DatePicker dtp = (DatePicker)editStkPnl.FindName(col.ColumnName);
if (row[col].ToString() != "" && dtp.SelectedDate != null)
{
if (Convert.ToDateTime(dtp.SelectedDate) != Convert.ToDateTime(row[col]))
{
sql = sql + col.ColumnName + " = '" + Convert.ToDateTime(dtp.SelectedDate).ToString("yyyy-MM-dd") + "', ";
isDirty = true;
}
}
else if (row[col].ToString() == "" && dtp.SelectedDate != null)
{
sql = sql + col.ColumnName + " = '" + Convert.ToDateTime(dtp.SelectedDate).ToString("yyyy-MM-dd") + "', ";
isDirty = true;
}
;
break;
case "CheckBox":
CheckBox chk = (CheckBox)editStkPnl.FindName(col.ColumnName);
if (Convert.ToBoolean(chk.IsChecked) != Convert.ToBoolean(row[col]))
{
sql = sql + col.ColumnName + " = " + Convert.ToBoolean(chk.IsChecked) + ", ";
isDirty = true;
}
;
break;
}
;
}
if (isDirty)
{ //Update the selected record in database
sql = sql.Trim(',', ' ') + " WHERE " + windowMetaList.TableKey + " = @Id";
NpgsqlCommand listItemSaveSql = new NpgsqlCommand
{
CommandText = sql,
CommandType = CommandType.Text,
Connection = windowMetaList.ApplicationDb
};
listItemSaveSql.Parameters.AddWithValue("@Id", selectedRowIdVal);
windowMetaList.ApplicationDb.Open();
listItemSaveSql.ExecuteNonQuery();
windowMetaList.ApplicationDb.Close();
}
;
}
return(true);
}
catch (Exception ex)
{
WindowTasks.DisplayError(ex, "Cannot Save Record:" + ex.Message, sql);
windowMetaList.ApplicationDb.Close();
return(false);
};
}