CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2010
    Posts
    1

    How do I update SQL Database via stored procedures in WPF?

    Please bear with me while i explain my question. I think it will make more sense. Thank you:

    I have a table in SQL server 2005 with three columns CustomerID (integer, identity), Name(varchar(50) and Address(varchar(50). I created four stored procedures for the table (CustomerSelect, CustomerInsert, CustomerDelete and CustomerUpdate).

    In VS 2008, i created a connection string to the table using Settings.settings. I then created a Data Access Class with the following code to enable me sellect and view a customer in the database:

    Code:
    public class CustomerDAL
        {
            private string connectionString = Properties.Settings.Default.BranchAppConnString;
    
            public Branch SelectCustomer(int ID)
            {
                SqlConnection conn = new SqlConnection(connectionString);
                SqlCommand cmd = new SqlCommand("CustomerSelect", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@CustomerID", ID);
                
    
                try
                {
                    conn.Open();
                    SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
    
                    if (reader.Read())
                    {
                        Customer customer = new Customr((string)reader["CustomerName"], (string)reader["address"]);
    
                        return (customer);
                    }
    
                    else
                    {
                        return null;
                    }
    
                }
    
                finally
                {
                    conn.Close();
                }
            }

    Now i created Data Class (Customer) with the properties for the Customer Entity. I also exposed the CustomerDAL class to the application using a partial class. Now this works just fine. I can insert a Customer ID in a text box and click a botton to view the customer.

    My question is: How can i use the same pattern to insert, delete and update the customer table via the corresponding stored procedures?

    Please help me with this. Thanks

  2. #2
    Join Date
    Nov 2007
    Location
    .NET 3.5 / VS2008 Developer
    Posts
    624

    Re: How do I update SQL Database via stored procedures in WPF?

    here are the additions to make...

    Insert
    Code:
            public void InsertCustomer(Customer cust)
            {
                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                      using (SqlCommand cmd = new SqlCommand("CustomerInsert", conn))
                      {
                             cmd.CommandType = CommandType.StoredProcedure;
    
                             // assuming parameters
                             cmd.Parameters.AddWithValue("@CustomerName", cust.Name);
                             cmd.Parameters.AddWithValue("@CustomerAddress", cust.Address);
    
                             conn.Open();
    
                             cmd.ExecuteNonQuery();
                      }
                }
            }

    Update
    Code:
            public void UpdateCustomer(Customer cust)
            {
                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                      using (SqlCommand cmd = new SqlCommand("CustomerUpdate", conn))
                      {
                             cmd.CommandType = CommandType.StoredProcedure;
    
                             // assuming parameters
                             cmd.Parameters.AddWithValue("@CustomerName", cust.Name);
                             cmd.Parameters.AddWithValue("@CustomerAddress", cust.Address);
    
                             conn.Open();
    
                             cmd.ExecuteNonQuery();
                      }
                }
            }
    Delete
    Code:
            // you could either pass in the Customer object, or just the customer ID
            //     for simplicity
            public void DeleteCustomer(int ID)
            {
                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                      using (SqlCommand cmd = new SqlCommand("CustomerDelete", conn))
                      {
                             cmd.CommandType = CommandType.StoredProcedure;
    
                             // assuming parameters
                             cmd.Parameters.AddWithValue("@CustomerID", ID);
    
                             conn.Open();
    
                             cmd.ExecuteNonQuery();
                      }
                }
            }
    ===============================
    My Blog

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured