Silverlight 2 Web Service Part III - Single object Get and Update from database

return product;
}
#endregion

Changing the Web.Config file

We are now going to add the connection string to the Web.Config file. We place it in this file to make it more flexible and to make it easier to alter in the future. Alteration to this file doesn't require that we recompile the solution. Add or alter the <appSettings> tag in the file to contain a key named ProductConnectionString that holds the connection data. DataSource is the name of the server hosting the SQL Server database. Initial Catalog is the name of the database in SQL Server. Integrated Security tells SQL Server that we want to use windows security to access the database.

<appSettings>
<add key ="ProductConnectionString" value="Data Source=localhost;
Initial Catalog=AdventureWorksLT;Integrated Security=True"/>
</appSettings>

Altering the Web Service

Let's start by commenting out or delete the existing content of the GetPtoduct method. Add a new method called GetProduct that takes an integer parameter called ProductID. Within the method call the GetProduct method in the DB class and save the result in a Product field. Return the product field from the method.

[OperationContract]
public Product GetProduct(int ProductID)
{
//Call the Database class
Product product = DB.GetProduct(ProductID);
return product;
}

Build the solution by pressing Ctrl+Shift+B on the keyboard.

Altering the Page.xaml.cs

We will now alter the Button_Click event in the Page.xaml.cs file. Right click on the Button_Click statement in the Button definition in the Page.xaml page and select Navigate to Event Handler in the context menu to jump to the button event handler.
We need to change the asynchronous call to the web service method to take the value entered in to the ProductID textbox. The first thing we need to do is it convert the value from string to integer and then send that value to the GetProduct method.

private void Button_Click(object sender, RoutedEventArgs e)
{
…
proxy.GetProductAsync(Convert.ToInt32(txtProductID.Text));
}

Next we will add error handling displaying a message if the database doesn't contain a product with the desired product id. Navigate to the callback method (proxy_GetProductCompleted) and add the following error handling code after the label assignments.

void proxy_GetProductCompleted(object sender,
GetProductCompletedEventArgs e)
{
…
Try
{
…
lblWeight.Text = product.Weight.ToString();

if (product.ProductNumber == null)
lblProductError.Text = "Product missing!";
else
lblProductError.Text = "";
}
catch { lblErrorMessage.Text = "Error calling Web Service"; }
}

Testing the Web Service

Let's call the GetProduct method from the Silverlight application. Press F5 on the keyboard to run the application and click on the button test the Web Service. Enter a product id in the product id text box and click the button; if the product exists in the database it will be displayed in the data bound controls, else a message will be displayed. See the images at the beginning of this tutorial.

Database update

We have seen how data can be requested and returned from a Web Service method. After altering values in the textboxes we might want to update the values of the product in the database; to do this we need to write a new method in the DB class that uses an Update statement to alter the values in the database. This method will be called from the method in the Web Service that in turn will be called from the Page.xaml.cs code from a button click event.

Adding the UpdateProduct method to the DB class

The first thing we will do in the UpdateProduct method is to set the CurrnetCulture to en-US to ensure that decimals will be treated with the US standard for decimal places; if we don't do this we might get an error returned from the database. (In Sweden for example we use a comma as opposed to a dot in the US.)

We then add a connection passing in the connection stored in the AppSettings. The Update statement is built from the values in the Product object passed to the method as a parameter.

Then we add a Command object with its CommandType set to Text. After opening the connection we call the ExecuteNonQuery method on the Command object to send the update request to the database. ExecuteNonQuery is used when you want to call the database without getting any rows back. The only value returned is the number of rows affected.

Upon completion we close the connection and return a boolean value indication if the update was a success.
Expand the Methods region and write the following code.

public static bool UpdateProduct(Product Product)
{
//Change the culture ti en-US to get uniform
//format on decimal delimiters.
System.Threading.Thread.CurrentThread.CurrentCulture = new
System.Globalization.CultureInfo("en-US");

bool productupdated = false;
if (Product.ProductID == 0) return productupdated;

SqlConnection conn = new SqlConnection(connectionstring);
try
{
string sqlupdate =
"UPDATE SalesLT.Product " +
"SET " +
" [ProductNumber] = '" + Product.ProductNumber + "', " +
" [Name] = '" + Product.Name + "', " +
" [ListPrice] = " + Product.Price + ", " +
" [Weight] = " + Product.Weight + ", " +
" [Size] = " + Product.Size +
" Where ProductID = " + Product.ProductID;

SqlCommand comm = new SqlCommand(sqlupdate, conn);
comm.CommandType = System.Data.CommandType.Text;

conn.Open();

int affected = comm.ExecuteNonQuery();
productupdated = true;
}
catch (Exception exception) { }
finally { conn.Close(); }

return productupdated;
}

Adding the UpdateProduct method to the Web Sevice class

To make the UpdateProduct method available from the Silverlight application we need to write a method in the Web Service class that exposes it to potential callers. Switch to the wsData.svc.cs class and add a method that calls the UpdateProduct in the DB class and returns a boolean stating if it was a successful update or not.

[OperationContract]
public bool UpdateProduct(Product product)
{
bool updated = DB.UpdateProduct(product);
return updated;
}

When you have completed the method you need to update the Web Service reference in the Silverlight application project; to do this you right click on the DataService proxy object in the Solution Explorer and select Update Service Reference in the context menu.

Updating the Silverlight page

We need to add a button that activates the update. Add a new Button control directly underneath the already existing button and set its content to Update Product, also add a Click event named ButtonUpdate_Click. Right click on the event name and select Navigate to Event Handler to switch to the code behind page; this will take you directly to the click event for the button.



The Silverlight application with the newly added Update Product button

The first thing we need to do is to add a proxy object to the Web Service. Next we build the Product object that we want to send to the Web Method. We also need to check that the Weight and Price properties of the Product object is greater than zero; there is a constraint on these fields in the database that states that the values must be greater tat zero. When that has been taken care of we make an asynchronous call to the UpdateProduct method in the Web Service.

private void ButtonUpdate_Click(object sender, RoutedEventArgs e)
{
System.ServiceModel.EndpointAddress endpoint = new
EndpointAddress("http://localhost:" +
HtmlPage.Document.DocumentUri.Port + "/wsData.svc");
wsDataClient proxy = new wsDataClient(new BasicHttpBinding(),
endpoint);
proxy.UpdateProductCompleted += new EventHandler
<UpdateProductCompletedEventArgs>(proxy_UpdateProductCompleted);

proxy.InnerChannel.OperationTimeout = TimeSpan.FromSeconds(30);

Product product = new Product();
product.ProductID = Convert.ToInt32(txtProductID.Text);
product.ProductNumber = lblProductNumber.Text;
product.Name = txtName.Text;
product.Price = Convert.ToDouble(txtPrice.Text);
product.Size = Convert.ToDouble(txtSize.Text);
product.Weight = Convert.ToDouble(txtWeight.Text);

//Due to constraints set in the database we need to add these checks.
if (product.Weight <= 0d)
{
MessageBox.Show("Weight must be greater than 0");
return;
}
if (product.Price <= 0d)
{
MessageBox.Show("Price must be greater than 0");
return;
}

proxy.UpdateProductAsync(product);
}

We also need to write some code in the callback method of the asynchronous call. Locate the proxy_UpdateProductCompleted event method. We need to check the return value of the call to the UpdateProduct method call and display a message stating if the product was successfully updated or not.

void proxy_UpdateProductCompleted(object sender,
UpdateProductCompletedEventArgs e)
{
bool updated = (bool)e.Result;

if(updated)
MessageBox.Show("Product updated successfully.");
else
MessageBox.Show("Could not update product.");
}

Testing the update method

Press F5 on the keyboard to run the application, enter a product id in the product id text box and click the Call WebService button; if the product exists in the database it will be displayed in the data bound controls, else an error message will be displayed. See the images at the beginning of this tutorial. Change one or more of the values in the textboxes and then press the Update Product button. To test the error messages you can put zero (0) in the Weight field, this should display a message box with an error message.




The Silverlight application with altered values before the user clicks the Update Product button



The Silverlight application after the user has clicked the Update Product button



The error message displayed if the Weight field is set to zero (0)