public static DataTable DbGetDataRow(WindowMetaList windowMetaList, Int32 id, StackPanel editStkPnl)
//Loads a single row from the database into a table for the record for the selected ID
{
string sql = "SELECT * FROM " + windowMetaList.SchemaName + "." + windowMetaList.TableName + " WHERE " + windowMetaList.TableKey + " = @Id";
DataTable winSelectedRowDataTable = new DataTable();
NpgsqlCommand winSelectedRowSql = new NpgsqlCommand
{
CommandText = sql,
CommandType = CommandType.Text,
Connection = windowMetaList.ApplicationDb
};
winSelectedRowSql.Parameters.AddWithValue("@Id", id);
windowMetaList.ApplicationDb.Open();
try
{
NpgsqlDataAdapter winDa = new NpgsqlDataAdapter(winSelectedRowSql);
winDa.Fill(winSelectedRowDataTable);
}
catch (Exception ex)
{
WindowTasks.DisplayError(ex, "Problem Loading data grid row:" + ex.Message, sql);
windowMetaList.ApplicationDb.Close();
}
windowMetaList.ApplicationDb.Close();
return(winSelectedRowDataTable);
}
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 WinDataGridSelectRow(Int32 id, DataGrid winDg, WindowMetaList windowMetaList)
//Selects the row in the data grid for the current id
{
int x = windowMetaList.GridSelectedIndex;
try
{
object item = winDg.Items[x];
winDg.SelectedItem = item;
winDg.ScrollIntoView(item);
winDg.UpdateLayout();
DataGridRow row = (DataGridRow)winDg.ItemContainerGenerator.ContainerFromIndex(x);
row.MoveFocus(new TraversalRequest(FocusNavigationDirection.Next));
}
catch (Exception ex)
{
WindowTasks.DisplayError(ex, "Problem Selecting the DataGrid Row:", null);
}
}
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 DbCreateRecord(Window winNew, WindowMetaList windowMetaList, StackPanel editStkPnl, StackPanel fltStkPnl, DataGrid winDg, Int32 seletedFilter, Dictionary <string, string> controlValues, TextBox tbOffset, TextBox tbFetch, TextBox tbSelectorText)
//Creates a new record in the db
{
List <string> columns = new List <string>();
List <string> columnUpdates = new List <string>();
string sql = string.Empty;
string ctlName = string.Empty;
string ctlType = string.Empty;
FrameworkElement e;
try
{
try
{
foreach (FrameworkElement element in editStkPnl.Children)
{
e = element;
ctlType = e.GetType().Name;
ctlName = e.Name;
if (ctlName != windowMetaList.TableKey & ctlType != "Label" & e.IsEnabled == true)
{
switch (ctlType)
{
case "TextBox":
TextBox tb = (TextBox)editStkPnl.FindName(ctlName);
columns.Add(ctlName);
if (tb.Tag.ToString() != "NUM")
{
columnUpdates.Add("'" + tb.Text.Replace("'", "''") + "'");
}
else
{
if (tb.Text == "")
{
tb.Text = "0";
}
columnUpdates.Add(tb.Text);
}
break;
case "ComboBox":
ComboBox cb = (ComboBox)editStkPnl.FindName(ctlName);
columns.Add(ctlName);
columnUpdates.Add(cb.SelectedValue.ToString());
break;
case "DatePicker":
DatePicker dtp = (DatePicker)editStkPnl.FindName(ctlName);
columns.Add(ctlName);
if (dtp.SelectedDate != null)
{
columnUpdates.Add("'" + Convert.ToDateTime(dtp.SelectedDate).ToString("yyyy-MM-dd") + "'");
}
else
{
columnUpdates.Add("NULL");
}
break;
case "CheckBox":
CheckBox chk = (CheckBox)editStkPnl.FindName(ctlName);
columns.Add(ctlName);
columnUpdates.Add("CAST(" + ((bool)chk.IsChecked ? 1 : 0).ToString() + " AS boolean)");
break;
}
}
}
}
catch (NullReferenceException)
{
WindowTasks.DisplayMessage("Try entering a value for: " + ctlName);
return(false);
}
string csvColumns = "(" + String.Join(",", columns) + ")";
string csvColumnUpdates = " VALUES(" + String.Join(",", columnUpdates) + ")";
sql = "INSERT INTO " + windowMetaList.SchemaName + "." + windowMetaList.TableName + " " + csvColumns + csvColumnUpdates;
NpgsqlCommand dbCreateRecordSql = new NpgsqlCommand
{
CommandText = sql,
CommandType = CommandType.Text,
Connection = windowMetaList.ApplicationDb
};
windowMetaList.ApplicationDb.Open();
dbCreateRecordSql.ExecuteNonQuery();
windowMetaList.ApplicationDb.Close();
return(true);
}
catch (Exception ex)
{
WindowTasks.DisplayError(ex, "Cannot Insert Record:" + ex.Message, sql);
windowMetaList.ApplicationDb.Close();
return(false);
}
}
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);
};
}
public static void DbGetDataGridRows(Window winNew, WindowMetaList windowMetaList, StackPanel editStkPnl, StackPanel fltStkPnl, DataGrid winDg, Int32 selectedFilter, Dictionary <string, string> controlValues, TextBox tbOffset, TextBox tbSelectorText)
//Fills the form data grid with the filter applied
{
DataTable winDt = new DataTable();
string sqlPart;
Int32 sqlParam = windowMetaList.TableId;
string sqlTxt = windowMetaList.TableDml;
//Append filter where clause to the end of DML
if (selectedFilter == 0) //Default filter selected
{
sqlPart = ControlDatabaseSql.TableFilterDefault();
}
else //Custom filter selected
{
sqlParam = selectedFilter;
sqlPart = ControlDatabaseSql.TableFilterSelected();
}
//Set Filter
windowMetaList.TableFilter = WindowDataOps.SubstituteWindowParameters(WindowDataOps.WinDataGridGetBaseSql(sqlPart, sqlParam, windowMetaList), controlValues);
sqlParam = windowMetaList.TableId;
//Set order by
string sqlOrderBy = windowMetaList.TableOrderBy;
//Build where clause with replacement values for |COLUMN_NAME| parameters
sqlTxt = sqlTxt + " WHERE " + windowMetaList.TableFilter;
//Save SQl for counting rows
string sqlCountText = sqlTxt;
//Add Order by
sqlTxt = sqlTxt + " ORDER BY " + sqlOrderBy + " OFFSET " + tbOffset.Text + " ROWS FETCH NEXT " + windowMetaList.PageRowCount + " ROWS ONLY";
try
{
windowMetaList.ControlDb.Open();
windowMetaList.ApplicationDb.Open();
{
//Run the SQL cmd to return SQL that fills DataGrid
NpgsqlCommand execTabSql = windowMetaList.ApplicationDb.CreateCommand();
execTabSql.CommandText = sqlTxt;
//Create an adapter and fill the grid using sql and adapater
NpgsqlDataAdapter winDa = new NpgsqlDataAdapter(execTabSql);
winDa.Fill(winDt);
winDg.ItemsSource = winDt.DefaultView;
//set the page counter
Int32 rowCount = 0;
Int32 chrStart = sqlCountText.IndexOf("SELECT") + 6;
Int32 chrEnd = sqlCountText.IndexOf("FROM");
sqlTxt = sqlCountText.Substring(0, chrStart) + " COUNT(*) " + sqlCountText.Substring(chrEnd);
NpgsqlCommand countRows = new NpgsqlCommand(sqlTxt, windowMetaList.ApplicationDb);
rowCount = Convert.ToInt32(countRows.ExecuteScalar());
Int32 pageSize = Convert.ToInt32(windowMetaList.PageRowCount);
Int32 offSet = Convert.ToInt32(tbOffset.Text);
string pageCount = Convert.ToString((rowCount / pageSize) + 1);
string pageNumber = Convert.ToString((offSet / pageSize) + 1);
tbSelectorText.Text = "Page " + pageNumber + " of " + pageCount;
windowMetaList.ControlDb.Close();
windowMetaList.ApplicationDb.Close();
}
}
catch (Exception ex)
{
WindowTasks.DisplayError(ex, "ERROR in DataGrid SQL:" + ex.Message, sqlTxt);
windowMetaList.ControlDb.Close();
windowMetaList.ApplicationDb.Close();
}
}
public static void WinLoadDataRow(StackPanel editStkPnl, DataTable winSelectedRowDataTable, Dictionary <string, string> controlValues)
//Loads the data editing UI with the values from the row in winSelectedRowDataTable
{
//Loop the Row (Filtered by @Id) and columns of the underlying dataset
string rowCol = null;
string columnName = null;
try
{
foreach (DataRow row in winSelectedRowDataTable.Rows)
{
foreach (DataColumn col in winSelectedRowDataTable.Columns)
{
//Set the value of the control col.Name in the window to the value returned by row[col]
rowCol = row[col].ToString();
columnName = col.ColumnName;
//Determine the Type of control
object obj = editStkPnl.FindName(columnName);
string ctlType = obj.GetType().Name;
//Use Type to work out how to process value;
switch (ctlType)
{
case "TextBox":
TextBox tb = (TextBox)editStkPnl.FindName(columnName);
tb.Text = rowCol;
break;
case "ComboBox":
ComboBox cb = (ComboBox)editStkPnl.FindName(columnName);
if (rowCol != "")
{
cb.SelectedValue = rowCol;
//We set this here because there is no change event we can trigger on a combo box
WinGetControlValue(cb, controlValues);
}
else if (rowCol == "")
{
cb.SelectedValue = null;
}
break;
case "DatePicker":
DatePicker dtp = (DatePicker)editStkPnl.FindName(columnName);
if (rowCol != "")
{
dtp.SelectedDate = Convert.ToDateTime(row[col]);
}
else if (rowCol == "")
{
dtp.SelectedDate = null;
}
break;
case "CheckBox":
CheckBox chk = (CheckBox)editStkPnl.FindName(columnName);
chk.IsChecked = Convert.ToBoolean(row[col]);
break;
}
;
}
}
}
catch (Exception ex)
{
WindowTasks.DisplayError(ex, "Problem Loading the data row:", columnName + ":" + rowCol);
}
}
public static DataTable WinPopulateCombo(ComboBox cb, WindowMetaList windowMetaList, string colname, Dictionary <string, string> controlValues)
//Populates a combo box
{
NpgsqlCommand getColList = new NpgsqlCommand();
NpgsqlCommand getComboRows = new NpgsqlCommand();
DataTable comboDataTable = new DataTable();
string controlName;
string controlLabel;
string controlRowSource;
string controlFilter;
string controlOrderBy;
string controlType;
string controlEnabled;
string controlDefaultvalue;
getColList.CommandText = ControlDatabaseSql.ColumnMetadataForColumn();
getColList.Parameters.AddWithValue("@applicationTableId", windowMetaList.TableId);
getColList.Parameters.AddWithValue("@colname", colname);
getColList.CommandType = CommandType.Text;
getColList.Connection = windowMetaList.ControlDb;
try
{
windowMetaList.ControlDb.Open();
{
NpgsqlDataReader getColListReader = getColList.ExecuteReader();
getColListReader.Read();
controlName = getColListReader["column_name"].ToString();
controlLabel = getColListReader["column_label"].ToString();
controlRowSource = getColListReader["row_source"].ToString();
controlFilter = getColListReader["filter"].ToString();
controlOrderBy = getColListReader["order_by"].ToString();
controlType = getColListReader["window_control_type"].ToString();
controlEnabled = getColListReader["window_control_enabled"].ToString();
controlDefaultvalue = getColListReader["column_default_value"].ToString();
}
windowMetaList.ControlDb.Close();
if (controlOrderBy == string.Empty)
{
controlOrderBy = "\nORDER BY 1";
}
else
{
controlOrderBy = "\nORDER BY " + controlOrderBy;
}
controlRowSource += controlOrderBy;
controlRowSource = WindowDataOps.SubstituteWindowParameters(controlRowSource, controlValues);
getComboRows.CommandText = controlRowSource;
getComboRows.CommandType = CommandType.Text;
getComboRows.Connection = windowMetaList.ApplicationDb;
}
catch (Exception ex)
{
WindowTasks.DisplayError(ex, "ERROR Reading Data:" + ex.Message, getColList.CommandText);
windowMetaList.ControlDb.Close();
}
try
{
windowMetaList.ApplicationDb.Open();
{
NpgsqlDataAdapter comboAdapter = new NpgsqlDataAdapter(getComboRows);
comboAdapter.Fill(comboDataTable);
cb.ItemsSource = comboDataTable.DefaultView;
cb.DisplayMemberPath = comboDataTable.Columns["display_member"].ToString();
cb.SelectedValuePath = comboDataTable.Columns["value_member"].ToString();
}
windowMetaList.ApplicationDb.Close();
return(comboDataTable);
}
catch (Exception ex)
{
WindowTasks.DisplayError(ex, "ERROR Filling Combo:" + ex.Message, getColList.CommandText);
windowMetaList.ApplicationDb.Close();
return(comboDataTable);
}
}