Silverlight 2 (beta1) – Database Operations with ASP.NET Web Service in Silverlight 2 106

Update You can download the latest version of sample that works with Silverlight 2 beta2 from this link.

Introduction

This is the step-by-step tutorial for how to do database operations (Create, Retrieve, Update, Delete) with ASP.NET web service (asmx) in Silverlight 2 beta 1.

Background

A few people asked me how to do database operations (CRUD) in Silverlight 2 (beta1). As I have some experiences in using Astoria in Silverlight 1.1 Alpha, I was thinking to use Astoria service in Silverlight 2. Unfortunately, Astoria Client add-on doesn’t work with Silverlight 2 (beta1) and Astoria team said that the next version will be available around the end of April. So, for now, I made this sample with normal ASP.NET web service for those who like to do CRUD operations in Silverlight 2.

Software Needed

  • Silverlight 2 (beta1) (Please read this post if you don’t know where you can get those installers.)
  • Visual Studio 2008
  • SQL 2005 Express with Management Studio

Download : SL2WebSrv.zip (1.45 MB)

Screenshots
Silverlight 2

Fig: Retrieving data from ASP.NET web service and displaying those data in Silverlight Datagrid.

sl2-entry.jpg

Fig: Entry Form for adding new record to database from Silverlight 2 application

Creating the database in SQL 2005

Note: If you already know about how to create the database in SQL 2005, please skip and jump to next section.

Step 1: Open SQL Server Management Studio Express

SQL Server Management Studio Express - FREE edition

Step 2: Connect to SQL Server that you have installed on your local machine

Connect to Server

Step 3: Right-click on Database node from Object Explorer and Select “New Database”

New Database - SQL 2005

Step 4: Type your database name ( I named it “MyStore” in this sample) and Click “OK” button

create-new-database-small.jpg

Step 5: Right-click on Table node of the database that you have created and select “New Table”

New Table

Step 5: Create two columns called “ProductID(INT Identity PK)” and “ProductName”. Name the table “Products”

Structure of Products Table

Okay. That is all about creating new table in SQL 2005.

Creating the Silverlight 2 (beta1) project in Visual Studio 2008

  • Open VS 2008 and Create new Silverlight 2 project.

new-project-thumb.jpg

  • It will ask you whether you want to create ASP.NET project or HTML page to host Silverlight content. Just click “OK” button

add-silverlight-application.jpg

  • You will get two projects (ASP.NET and Silverlight) under one solution.

solution-explorer.jpg

Creating Web Service in ASP.NET project

  • Right-click on ASP.NET project node and Choose “Add New Item” as shown in picture below.

add-new-item.jpg

  • “Add New Item” dialog will be shown. Please choose “Web Service” item in that dialog.

add-new-item-detail-thumb.jpg

  • Give the name “ProductManager.asmx” to this web service and click “OK” button.
  • Go to web.config and Add the connection string as below under <configuration> in web.config. (Note: You have to add your connection string for SQL so that it might not be the same as mine.)
    <connectionStrings>
    <add name="sqlConnectionString" connectionString="Data Source=MICHAELSYNC-PC\SQLEXPRESS;Initial Catalog=MyStore;Integrated Security=True"/>
    </connectionStrings>
    
  • Go to the ProductManager.asmx again. Uncomment [System.Web.Script.Services.ScriptService] at the top of Class
  • Write the four methods for CRUD operations. (I will show the code for retrieving data from SQL in this post. If you want to know about CUD operations, please download the sample and take a look at that asmx file.)
    [WebMethod]
    public string RetrieveProduct(int productId) {
    try {
    SqlConnection _sqlConnection = new SqlConnection();
    _sqlConnection.ConnectionString = ConfigurationManager.ConnectionStrings["sqlConnectionString"].ToString();
    _sqlConnection.Open();SqlDataAdapter da = new SqlDataAdapter();
    da.SelectCommand = new SqlCommand("SELECT * FROM Products WHERE ProductID = " + productId.ToString().Replace("'", "''"), _sqlConnection);
    
    DataSet ds = new DataSet();
    da.Fill(ds);
    
    StringBuilder sb = new StringBuilder();
    sb.Append("<?xml version=\"1.0\" encoding=\"utf-8\" ?>");
    sb.Append("<Products>");
    foreach (DataRow dr in ds.Tables[0].Rows) {
    sb.Append("<Product>");
    sb.Append("<ID>");
    sb.Append(dr[0].ToString());
    sb.Append("</ID>");
    sb.Append("<Name>");
    sb.Append(dr[1].ToString());
    sb.Append("</Name>");
    sb.Append("</Product>");
    }
    sb.Append("</Products>");
    _sqlConnection.Close();
    
    return sb.ToString();
    }
    catch (Exception ex) {
    return string.Empty;
    }
    }
    
  • Go to the propertypad of ASP.NET project and set False to “Use dynamic ports” property.static-port.jpg
  • then, Build ASP.NET project.

Consuming ASP.NET web service in Silverlight 2 (beta1)

  • Right-click on the Reference node of Silverlight project. And choose “Add Service Reference” as shown in picture below.add-service-reference.jpg
  • The following dialog will be shown. Click “Discover” buttondiscover-thumb.jpg
  • (One web service will be shown in “Service:” panel after clicking “Discover” button.) Double-click on that one.discovering-webservice.jpg
  • It will show “ProductManager” service that we created in ASP.NET project
  • Change the namespace to “WebServiceProxy” and hit “OK” button
  • Now, you can start using web service from Silverlight project. I will show you how to retrieve the data from web service.
  • Please take a look at “ListingControl.xaml.cs. You will see the following code that retrieve the data from Web Service
    void ListingControl_Loaded(object sender, RoutedEventArgs e) {WebServiceProxy.ProductManagerSoapClient productMgrSoapClient =
    new SL2WebSrv.WebServiceProxy.ProductManagerSoapClient();
    
    productMgrSoapClient.RetrieveProductsAsync();
    productMgrSoapClient.RetrieveProductsCompleted +=
    new EventHandler<SL2WebSrv.WebServiceProxy.RetrieveProductsCompletedEventArgs>(productMgrSoapClient_RetrieveProductsCompleted);
    }
    void productMgrSoapClient_RetrieveProductsCompleted(object sender, SL2WebSrv.WebServiceProxy.RetrieveProductsCompletedEventArgs e) {
    if (e.Error == null)
    displayData(e.Result);
    }
    

    Note: ListingControl_Loaded is attached in ListingControl constructor. displayData(string) is another function for showing data.

  • I created Product class in Silverlight project too. Because Silverlight doesn’t support binding Datagrid with anonymous type. (You can read about this issue more details in this post.)
  • The following code is for displayData() function.
    void displayData(string xmlContent) {
    try {if (xmlContent != string.Empty) {
    XDocument xmlProducts = XDocument.Parse(xmlContent);
    
    var products = from product in xmlProducts.Descendants("Product")
    select new
    {
    ProductID = Convert.ToInt32(product.Element("ProductId").Value),
    ProductName = (string)product.Element("ProductName").Value
    };
    
    //Bug: http://silverlight.net/forums/t/11147.aspx
    List<Product> productsList = new List<Product>();
    
    foreach (var p in products) {
    Product pdt = new Product { ProductID = p.ProductID, ProductName = p.ProductName };
    productsList.Add(pdt);
    }
    productsDataGrid.ItemsSource = productsList;
    }
    else {
    productsDataGrid.ItemsSource = null;
    }
    
    }
    catch (Exception ex) {
    Console.Write(ex.Message);
    }
    }
    
  • Yes. That’s all about retrieving data from Web Service. If you run that sample, the data from database will be displayed in DataGrid.
  • You can also read the code from EntryControl.xaml.cs file if you want to know how to insert the data.

How to run this sample

First thing that you need to do is that you should attach the database to your SQL express. After that, you have to change the connection string. then, try to run the sample. If you are not seeing anything then please try to uncomment the code for calling web service. (because I’m not sure whether you have the connection problem or not.) then, run it again. You should be able to see the silverlight content as shown in my screenshot except datagrid. If it’s running fine then try to check the connection string again.

Feel free to let me know if you have any problem or suggestion. I hope you will find it useful.

Related ~

106 thoughts on “Silverlight 2 (beta1) – Database Operations with ASP.NET Web Service in Silverlight 2

  1. Reply Siddharth Dahiya Sep 1,2009 1:48 pm

    Hey Michael,

    I need to retrieve data from my MSSQL server for a silverlight App. The problem is that if I do make a webservice and set it up, I will be putting it on the testing server ONLY. When the project is complete and the data is transfered to the deployment server, there is a posibility that the server IP/URL might change. therefore, can you make a suggestion as to how I should retrieve data from the SQL? the server is going to be static so I know the database will be where it is now.

    Also, I also tried to make a blank aspx page that writes the required data to the response stream. But silverlight does not allow to download the contents of the web page.

    Any suggestions?

  2. Reply kannan Feb 25,2010 6:05 am

    pls give me a simple sample application, where i can connect to a database and retrieve data and display in Silverlight Data grid contron in a silverlight application..

  3. Reply Dan W Jul 23,2010 2:45 am

    Hi Michael,

    can this be used to connect to a Sybase db?

    thanks

    Dan

  4. Reply Tomcat Dec 15,2010 12:17 am

    Hi Michael Sync, please tell me where I can find ListingControl.xam.cs? Everything is ok but in the final step, I can’t find that file.

    Quoted from you: “Please take a look at ‚ÄúListingControl.xaml.cs. You will see the following code that retrieve the data from Web Service”…

    Thank you.

  5. Reply Tomcat Dec 15,2010 3:35 am

    Hi Mr. Sync, I have solved my problem. Thank you for your share :)

  6. Reply Baljeet May 3,2012 4:19 am

    Thanks for this valuable share, it helps me a lot in understanding silverlight databinding. Thanks again

Leave a Reply