public static ComboBox CreateFilterCombo(WindowMetaList windowMetaList)
{
//Window Filter - Gets the list of filters for the window based on the underlying database table
ComboBox winFlt = new ComboBox
{
Name = "gridFilter",
Style = (Style)Application.Current.FindResource("winComboBoxStyle")
};
NpgsqlCommand getFltRows = new NpgsqlCommand
{
CommandText = ControlDatabaseSql.TableFilterList()
};
getFltRows.Parameters.AddWithValue("@applicationTableId", windowMetaList.TableId);
getFltRows.CommandType = CommandType.Text;
getFltRows.Connection = windowMetaList.ControlDb;
windowMetaList.ControlDb.Open();
{
NpgsqlDataAdapter fltAdapter = new NpgsqlDataAdapter(getFltRows);
DataTable fltDataTable = new DataTable();
fltAdapter.Fill(fltDataTable);
winFlt.ItemsSource = fltDataTable.DefaultView;
winFlt.DisplayMemberPath = fltDataTable.Columns["display_member"].ToString();
winFlt.SelectedValuePath = fltDataTable.Columns["value_member"].ToString();
}
windowMetaList.ControlDb.Close();
return(winFlt);
}
public static WindowMetaList WinMetadataList(Int32 tabId)
//Returns the list of metadata values for a window
{
NpgsqlConnection controlDb = new NpgsqlConnection(ApplicationEnviroment.ConnectionString("control"));
WindowMetaList metaList = new WindowMetaList
{
//Set the control connections
ControlDb = controlDb
};
//get the table string values
NpgsqlCommand getTab = new NpgsqlCommand
{
CommandText = ControlDatabaseSql.TableMetadata(),
CommandType = CommandType.Text,
Connection = controlDb
};
getTab.Parameters.AddWithValue("@tabId", tabId);
controlDb.Open();
NpgsqlDataReader getTabReader = getTab.ExecuteReader();
getTabReader.Read();
metaList.ApplicationName = getTabReader["application_name"].ToString();
metaList.TableId = Convert.ToInt32(getTabReader["application_table_id"]);
metaList.TableKey = getTabReader["table_key"].ToString();
metaList.TableName = getTabReader["table_name"].ToString();
metaList.TableLabel = getTabReader["table_label"].ToString();
metaList.TableDml = getTabReader["table_dml"].ToString();
metaList.TableOrderBy = getTabReader["table_order_by"].ToString();
metaList.PageRowCount = getTabReader["page_row_count"].ToString();
metaList.SchemaName = getTabReader["schema_name"].ToString();
metaList.SchemaLabel = getTabReader["schema_label"].ToString();
controlDb.Close();
//set the application connection
NpgsqlConnection applicationDb = new NpgsqlConnection(ApplicationEnviroment.ConnectionString(metaList.ApplicationName));
metaList.ApplicationDb = applicationDb;
metaList.Columns = WindowBuildUtils.PopulateColumnMetadata(metaList);
//metaList.Columns.Add(new ColumMetadata { })
return(metaList);
}
public static List <ColumMetadata> PopulateColumnMetadata(WindowMetaList windowMetaList)
{
List <ColumMetadata> columns = new List <ColumMetadata>();
NpgsqlCommand getColList = new NpgsqlCommand
{
CommandText = ControlDatabaseSql.ColumnMetadata()
};
getColList.Parameters.AddWithValue("@applicationTableId", windowMetaList.TableId);
getColList.CommandType = CommandType.Text;
getColList.Connection = windowMetaList.ControlDb;
windowMetaList.ControlDb.Open();
NpgsqlDataReader getColListReader = getColList.ExecuteReader();
if (getColListReader.HasRows)
{
while (getColListReader.Read())
{
ColumMetadata column = new ColumMetadata
{
ColumnName = getColListReader["column_name"].ToString(),
ColumnLabel = getColListReader["column_label"].ToString(),
ColumnRowSource = getColListReader["row_source"].ToString(),
ColumnFilter = getColListReader["filter"].ToString(),
ColumnOrderBy = getColListReader["order_by"].ToString(),
ColumnType = getColListReader["window_control_type"].ToString(),
ColumnEnabled = getColListReader["window_control_enabled"].ToString(),
ColumnDefaultValue = getColListReader["column_default_value"].ToString(),
ColumnRequiredValue = getColListReader["column_required_value"].ToString(),
ColumnDescription = getColListReader["column_description"].ToString(),
ColumnValue = String.Empty
};
columns.Add(column);
}
}
windowMetaList.ControlDb.Close();
return(columns);
}
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);
}
}
