We want to alter the appearance of the first row of the page. Instead of just containing a button we want it to contain a TextBox called txtProductID where the user can input a product id as well as a descriptive TextBlock for the TextBox. We also want a TextBlock called lblProductError where we can output an error message if the user inputs a non existing product id. If we insert these controls into a StackPanel we can format the layout more easily.
Start by deleting the existing button control from the page and add a StackPanel in its place; set the Grid.Column and Grid.Row values to zero and let it span over all three columns. Let the StackPanel display its contents in a horizontal orientation and set its margins. Add the other controls to the StackPanel.
</Grid.RowDefinitions>
<StackPanel Grid.Row="0" Grid.Column="0" Grid.ColumnSpan="3"
Orientation="Horizontal" Margin="20, 5, 5, 5" >
<TextBlock FontSize="12" FontWeight="Bold"
VerticalAlignment="Center">Product ID</TextBlock>
<TextBox Name="txtProductID" Text="707" Margin="5, 0, 0, 0"
Height="25" Width="50" TextAlignment="Right"></TextBox>
<Button Content="Call WebService" Width="120" Height="30"
Margin="5, 0, 0, 0" Click="Button_Click"></Button>
<TextBlock Name="lblProductError" FontSize="12"
FontWeight="Bold" Foreground="Red" Margin="10, 0, 0, 0"
VerticalAlignment="Center"></TextBlock>
</StackPanel>
Altering the Product class
Open the Product class and add a property named ProductID. This will make it easier to handle the product when updating its values.
[DataMember()]
public int ProductID
{
get;
set;
}
Adding the database class to the Web Service project
The first step in we will take to make database calls will be to add a class called DB.cs that will handle all database communication.
-
Right click on the WebService.Web project name and select Add-Class.
-
Name the class DB.cs in the Name field and click Add.
![image4.gif]()
Open the DB.cs class and add the following using statements. The first two are so that we can create a ServiceContract so that the data can be serialized and to make the class ASP.NET compatible. The next one makes it possible to call into the Web.Config file and extract information; we will use it to get the connection string for the database connection. The last two are to enable access to database objects.
using System.ServiceModel;
using System.ServiceModel.Activation;
using System.Web.Configuration;
using System.Data.SqlClient;
using System.Data;
Next we need to decorate the DB class with the ServiceContract and AspNetCompatibilityRequirements attributes.
[ServiceContract(Namespace = "")]
[AspNetCompatibilityRequirements(RequirementsMode =
AspNetCompatibilityRequirementsMode.Allowed)]
public class DB
Add a region called Fields and add a private static readonly field of string data type called connectionstring. This field will hold the connection string to the database; we make it static so that the static methods we will write later can call it. A static member can be called on the class directly without first creating an object of the class; a drawback is that a static method only can call static members, hence the static field. A readonly field can only be set once; this is perfcet for us in this cas because we only want to set the connection string once. We will use the WebConfigurationManager to extract the connection string from the Web.Config file.
#region Fields
private static readonly string connectionstring =
WebConfigurationManager.AppSettings["ProductConnectionString"];
#endregion
Adding the GetProduct method
Now add a region called Methods in which we will add a method called GetProduct that return a Product object. In this method we will call the database and return a table containing the requested product. Upon a successful database read we will assign values from the table to a newly created Product object that the method will return.
To connect to an SQL Server database we need a SqlConnection object that takes the connection string we stored in the connectionstring field earlier in its constructor. This object will be the link to the database. We also need a SqlCommand object that performs the database task at hand; in this case it will get the product requested. We also need a DataAdapter to fill the DataTable with the returned data. If the DataTable contains a product after the adapter has tried to fill it with data we will fill a Product object with the product data.
#region Methods
public static Product GetProduct(int ProductID)
{
SqlConnection conn = new SqlConnection(connectionstring);
Product product = new Product();
try
{
SqlCommand comm = new SqlCommand("Select ProductNumber,
Name, ListPrice, Size, Weight From SalesLT.Product Where
ProductID = " + ProductID.ToString(), conn);
comm.CommandType = System.Data.CommandType.Text;
conn.Open();
SqlDataAdapter adapter = new SqlDataAdapter(comm);
DataTable table = new DataTable();
adapter.Fill(table);
if (table.Rows.Count > 0)
{
DataRow row = table.Rows[0];
product.ProductNumber = row["ProductNumber"].ToString();
product.Name = row["Name"].ToString();
try { product.Price = Convert.ToDouble(row["ListPrice"]); }
catch { product.Price = 0d; }
try { product.Size = Convert.ToDouble(row["Size"]); }
catch { product.Size = 0d; }
try { product.Weight = Convert.ToDouble(row["Weight"]); }
catch { product.Weight = 0d; }
try { product.ProductID = Convert.ToInt32(row["ProductID"]); }
catch { product.ProductID = 0; }
}
}
finally { conn.Close(); }
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.
![image5.gif]()
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;