Beispiel #1
0
        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);
        }
Beispiel #2
0
        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();
            };
        }
Beispiel #3
0
        public static Window CreateWindow(WindowMetaList windowMetaList, Int32 applicationTableId)
        {
            Window window = new Window
            {
                Style = (Style)Application.Current.FindResource("winStyle"),
                Title = "Manage " + windowMetaList.TableLabel + " (" + windowMetaList.TableName + ")",
                Name  = windowMetaList.TableName
            };

            window.Activated += new EventHandler((s, e) =>
            {
                windowMetaList = WindowTasks.WinMetadataList(applicationTableId);
            });

            return(window);
        }
Beispiel #4
0
        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);
            }
        }
Beispiel #5
0
        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);
            }
        }
Beispiel #6
0
        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);
            }
        }
Beispiel #7
0
        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);
            };
        }
Beispiel #8
0
        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();
            }
        }
Beispiel #9
0
        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);
            }
        }
Beispiel #10
0
        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);
            }
        }