Database(CRUD operations ) with ASP.NET Web Service in Silverlight

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.

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 Required

• 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

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

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

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

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

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

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

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

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.

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

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

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” dialog will be shown. Please choose “Web Service” item in that dialog.

• 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.)

  1 2 3

  <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.)

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

  [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.
• 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.
• The following dialog will be shown. Click “Discover” button
• (One web service will be shown in “Service:” panel after clicking “Discover” button.) Double-click on that one.
• 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

  1 2 3 4 5 6 7 8 9 10 11

  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.

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

  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.