예제 #1
0
        public static List <State> GetStates()
        {
            List <State> states = new List <State>();
            State        st; // auxiliary

            // create connection
            using (SqlConnection connection = MMABooksdb.GetConnection())
            {
                // create command
                string query = "select StateCode, StateName from states " +
                               "ORDER BY StateName";
                using (SqlCommand cmd = new SqlCommand(query, connection)) {
                    // run the command and process results
                    connection.Open();
                    using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        while (reader.Read())
                        {
                            // process next record from data reader
                            st           = new State();
                            st.StateCode = reader["StateCode"].ToString();
                            st.StateName = reader["StateName"].ToString();
                            states.Add(st);
                        }
                    } // closes reader and recycles object
                }     // cmd object recycled
            }         // connection object recycled
            return(states);
        }
예제 #2
0
        // delete a customer
        public static bool DeleteCustomer(Customer cust)
        {
            int count = 0;

            using (SqlConnection connection = MMABooksdb.GetConnection())
            {
                string deletestatement = "delete from customers " +
                                         "where CustomerID = @CustomerID " + // to identify record
                                         "and name = @Name " +               // the remaining conditions - for optimistic concurrences
                                         "and address = @Address " +
                                         "and city = @City " +
                                         "and state = @State " +
                                         "and zipcode = @Zipcode";
                using (SqlCommand cmd = new SqlCommand(deletestatement, connection))
                {
                    cmd.Parameters.AddWithValue("@CustomerID", cust.CustomerID);
                    cmd.Parameters.AddWithValue("@Name", cust.Name);
                    cmd.Parameters.AddWithValue("@Address", cust.Address);
                    cmd.Parameters.AddWithValue("@City", cust.City);
                    cmd.Parameters.AddWithValue("@State", cust.State);
                    cmd.Parameters.AddWithValue("@ZipCode", cust.ZipCode);

                    connection.Open();

                    count = cmd.ExecuteNonQuery(); // delete statement
                }
                return(count > 0);
            }
        }// end delete method
예제 #3
0
        // retrieves one customer data
        public static Customer GetCustomer(int custID)
        {
            Customer cust = null;

            using (SqlConnection connection = MMABooksdb.GetConnection())
            {
                string query = "select CustomerId, Name, Address, City, State, ZipCode " +
                               "from customers " +
                               "where CustomerID = @CustomerID";  // query with parameter
                using (SqlCommand cmd = new SqlCommand(query, connection))
                {
                    cmd.Parameters.AddWithValue("@CustomerID", custID);
                    connection.Open();
                    using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
                    {
                        if (reader.Read())
                        {
                            cust            = new Customer();
                            cust.CustomerID = (int)reader["CustomerID"];
                            cust.Name       = reader["Name"].ToString();
                            cust.Address    = reader["Address"].ToString();
                            cust.City       = reader["City"].ToString();
                            cust.State      = reader["State"].ToString();
                            cust.ZipCode    = reader["ZipCode"].ToString();
                        }
                    }
                }
            }
            return(cust);
        } // end getcustomer method
예제 #4
0
        } // end getcustomer method

        // inserts record row into customer table and returns generated customer ID
        public static int AddCustomer(Customer cust)
        {
            int customerID = -1;

            using (SqlConnection connection = MMABooksdb.GetConnection())
            {
                string query = "insert into Customers(Name, Address, City, State, ZipCode) " +
                               "output inserted.CustomerID " +
                               "values(@Name, @Address, @City, @State, @ZipCode)";
                using (SqlCommand cmd = new SqlCommand(query, connection))
                {
                    cmd.Parameters.AddWithValue("@Name", cust.Name);
                    cmd.Parameters.AddWithValue("@Address", cust.Address);
                    cmd.Parameters.AddWithValue("@City", cust.City);
                    cmd.Parameters.AddWithValue("@State", cust.State);
                    cmd.Parameters.AddWithValue("@ZipCode", cust.ZipCode);

                    connection.Open();

                    //cmd.ExecuteNonQuery();
                    customerID = (int)cmd.ExecuteScalar(); //
                    //// retrieve generated customerID
                    //string secondquery = "select ident_current('Customers')";

                    //using(SqlCommand secondcmd = new SqlCommand(secondquery, connection))
                    //{
                    //    connection.Open();
                    //    customerID = Convert.ToInt32(secondcmd.ExecuteScalar());
                    //}
                }
            }
            return(customerID);
        }
예제 #5
0
        }// end delete method

        // update customer
        public static bool UpdateCustomer(Customer oldCust, Customer newCust)
        {
            int count; // number of updated rows

            using (SqlConnection connection = MMABooksdb.GetConnection())
            {
                string updatestatement = "update customers set " +
                                         "name = @newName, " +
                                         "Address = @newAddress, " +
                                         "city = @newCity, " +
                                         "state = @newState, " +
                                         "zipcode = @newZipcode " +
                                         "Where customerID = @oldCustomerID " + // to identify record
                                         "and name = @oldName " +               // the remaining conditions - for optimistic concurrences
                                         "and Address = @oldAddress " +
                                         "and city = @oldCity " +
                                         "and state = @oldState " +
                                         "and zipcode = @oldZipcode";
                using (SqlCommand cmd = new SqlCommand(updatestatement, connection))
                {
                    cmd.Parameters.AddWithValue("@newCustomerID", newCust.CustomerID);
                    cmd.Parameters.AddWithValue("@newName", newCust.Name);
                    cmd.Parameters.AddWithValue("@newAddress", newCust.Address);
                    cmd.Parameters.AddWithValue("@newCity", newCust.City);
                    cmd.Parameters.AddWithValue("@newState", newCust.State);
                    cmd.Parameters.AddWithValue("@newZipCode", newCust.ZipCode);
                    cmd.Parameters.AddWithValue("@oldCustomerID", oldCust.CustomerID);
                    cmd.Parameters.AddWithValue("@oldName", oldCust.Name);
                    cmd.Parameters.AddWithValue("@oldAddress", oldCust.Address);
                    cmd.Parameters.AddWithValue("@oldCity", oldCust.City);
                    cmd.Parameters.AddWithValue("@oldState", oldCust.State);
                    cmd.Parameters.AddWithValue("@oldZipCode", oldCust.ZipCode);

                    connection.Open();

                    count = cmd.ExecuteNonQuery(); // returns number of rows updated
                }
            }
            return(count > 0);
        }