-
April 11th, 2010, 01:29 AM
#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
-
April 15th, 2010, 02:09 PM
#2
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|