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