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