// Method: Get List
        public List<lwdom_StateName_Model> Get_StateNames_List()
        {
            // building the connection string
            // get the provider, activeStatus database name, and path
            connectionString = PSWkr.G_SQLDatabaseConnectionString;
            string strMsg = "";

            // create needed objects
            SqlConnection connection = new SqlConnection(connectionString);

            // building sql command, chemcode is the Key
            string sqlStatement = "SELECT ID, StateName " +
                "FROM lwdom_StateName " +
                "ORDER BY StateName";

            // create List
            List<lwdom_StateName_Model> dsnMod_List = new List<lwdom_StateName_Model>();

            try
            {
                connection.Open();

                SqlCommand command = new SqlCommand(sqlStatement, connection);
                SqlDataReader reader = command.ExecuteReader();

                // read table, populate model
                while (reader.Read())
                {
                    lwdom_StateName_Model dsnMod = new lwdom_StateName_Model();

                    dsnMod.ID = (reader[0] != DBNull.Value) ? (Int64)reader[0] : 0;
                    dsnMod.StateName = (reader[1] != DBNull.Value) ? (string)reader[1] : "";
                    
                    // add Equipment to List
                    dsnMod_List.Add(dsnMod);
                }

                // close reader, close connection
                reader.Close();
                connection.Close();
                strMsg = "List Complete.";
            }
            catch (Exception errMsg)
            {
                connection.Close();
                strMsg = errMsg.Message.ToString();
                System.Windows.MessageBox.Show(strMsg, "Method: Get_StateNames_List", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Error);
            }

            // return List
            return dsnMod_List;
        }
        // load data model with data from window
        private lwdom_StateName_Model Load_StateName_Model()
        {
            int _id = 0;
            bool result = false;

            lwdom_StateName_Model dsnMod = new lwdom_StateName_Model();

            result = int.TryParse(lblID.Content.ToString(), out _id);
            dsnMod.ID = (result == true) ? _id : 0;
            dsnMod.StateName = txtStateName.Text.Trim();

            // return model
            return dsnMod;
        }
 // display the data
 private void DisplayData(lwdom_StateName_Model dsnMod)
 {
     lblID.Content = dsnMod.ID.ToString();
     txtStateName.Text = dsnMod.StateName;
 }
        // UPDATE Record
        private void buttonUpdate_Click(object sender, RoutedEventArgs e)
        {
            string strMsg = "";
            lwdom_StateName_Model dsnMod = new lwdom_StateName_Model();

            // loads model with data from view
            dsnMod = Load_StateName_Model();

            // add the record
            strMsg = DSNWkr.Update_StateName_rec(dsnMod);

            // display the message
            labelStatus.Content = strMsg;

            // list data
            ListModels_InGrid();

            ResetDisplayFields();
            InitialButtonConfiguration();
        }
        // ADD Record
        private void buttonADD_Click(object sender, RoutedEventArgs e)
        {
            string strMsg = "";
            lwdom_StateName_Model dsnMod = new lwdom_StateName_Model();

            // loads model with data from view
            dsnMod = Load_StateName_Model();

            // add the record
            if (dsnMod.StateName.Trim() != null)
            {
                strMsg = DSNWkr.Add_StateName_Rec(dsnMod);
            }
            else
            {
                strMsg = "ADD Canceled. No data entered.";
                MessageBox.Show(strMsg, "Rate Name", MessageBoxButton.OK, MessageBoxImage.Information);
            }

            // display the message
            labelStatus.Content = strMsg;

            // list data
            ListModels_InGrid();

            ResetDisplayFields();
            InitialButtonConfiguration();
        }
        // Method: get record data based on id
        public lwdom_StateName_Model Get_SpecificStateName_Record(int recID)
        {
            string strMsg = "";

            // get the connection string
            connectionString = PSWkr.G_SQLDatabaseConnectionString;

            // create connection object
            SqlConnection connection = new SqlConnection(connectionString);

            // building sql command
            string sqlStatement = "SELECT ID, StateName " +
                "FROM lwdom_StateName " +
                "WHERE ID=@ID";

            // SqlCommand
            SqlCommand command = new SqlCommand(sqlStatement, connection);

            // Create object base on LW Rates Model (lwrMod)
            lwdom_StateName_Model dsnMod = new lwdom_StateName_Model();

            try
            {
                // open the connection           
                connection.Open();

                command.Parameters.AddWithValue("@ID", recID);

                // execute the reader
                SqlDataReader reader = command.ExecuteReader();

                // populate the invoice list
                if (reader.Read())
                {
                    dsnMod.ID = (reader[0] != DBNull.Value) ? (Int64)reader[0] : 0;
                    dsnMod.StateName = (reader[1] != DBNull.Value) ? (string)reader[1] : "";
                }

                // the close
                reader.Close();
            }
            catch (Exception errMsg)
            {
                connection.Close();
                strMsg = errMsg.Message.ToString();
                System.Windows.MessageBox.Show(strMsg, "Method: Get_SpecificStateName_Record", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Error);
            }

            // the close
            connection.Close();

            // return the Model
            return dsnMod;
        }
        // Reset state name Model
        private lwdom_StateName_Model Reset_StateName_Model()
        {
            lwdom_StateName_Model dsnMod = new lwdom_StateName_Model();

            // reset the model
            dsnMod.ID = 0;
            dsnMod.StateName = "";

            // return the model
            return dsnMod;
        }
        // Method: update record
        public string Update_StateName_rec( lwdom_StateName_Model dsnMod)
        {
            // Method: update selected Hours Master recore 
            // update the database
            string strMsg = "";

            // get the connection string
            connectionString = PSWkr.G_SQLDatabaseConnectionString;

            // create connection object
            SqlConnection connection = new SqlConnection(connectionString);

            // building sql command
            string sqlStatement = "UPDATE lwdom_StateName " +
                "SET StateName=@StateName " +
                "WHERE ID=@ID";

            // SqlCommand
            SqlCommand command = new SqlCommand(sqlStatement, connection);

            try
            {
                // update the database
                connection.Open();

                // use of command.parameters... prevents sql injection
                command.Parameters.AddWithValue("@StateName", dsnMod.StateName);
                //
                command.Parameters.AddWithValue("@ID", dsnMod.ID); // must be in the order of the sqlstatement

                command.ExecuteNonQuery();
                connection.Close();
                strMsg = "Record was updated.";
            }
            catch (Exception err)
            {
                strMsg = err.Message.ToString();
                connection.Close();
                System.Windows.MessageBox.Show(strMsg, "Method: Update_StateName_rec", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Error);
            }

            return strMsg;
        }
        // ADD
        public string Add_StateName_Rec(lwdom_StateName_Model dsnMod)
        {
            // Method: Create new record 
            // update the database
            string strMsg = "";

            // get the connection string
            connectionString = PSWkr.G_SQLDatabaseConnectionString;

            // create connection object
            SqlConnection connection = new SqlConnection(connectionString);

            // building sql command
            string sqlStatement = "INSERT INTO lwdom_StateName (StateName) " +
                "VALUES (@StateName)";

            // SqlCommand
            SqlCommand command = new SqlCommand(sqlStatement, connection);

            try
            {
                connection.Open();
                // Adding parameters for the Insert Command
                command.Parameters.AddWithValue("@StateName", dsnMod.StateName);

                command.ExecuteNonQuery();
                connection.Close();
                strMsg = "Record was added.";
            }
            catch (Exception err)
            {
                strMsg = err.Message.ToString();
                connection.Close();
                System.Windows.MessageBox.Show(strMsg, "Method: Add_StateName_Rec", System.Windows.MessageBoxButton.OK, System.Windows.MessageBoxImage.Error);
            }

            return strMsg;
        }