CRUD operations in Siverlight using ADO.NET Data Service

• Download sample - 155 KB

Introduction

This article describes the simplest way to do CRUD (Create, Retrieve, Update, Delete) operations in Silverlight using ADO.NET Data Service (Astoria). There are two ways to communicate with database in Silverlight:

1. Web Service
2. ADO.NET Data Service

I chose ADO.NET Data Service for this article because RESTful Service and RIA (Rich Internet Application) development are the best matches. If you have no idea about what ADO.NET is, please read my previous post here.

Contents

• Prerequisites
• Creating the database in SQL 2005
• Creating ADO.NET Data Service in Visual Studio 2008
• Making an Astoria Proxy for Silverlight project
• Inserting a new record in Silverlight
• Updating an existing record in Silverlight
• Retrieving a record from database in Silverlight
• Deleting a record in Silverlight
• Conclusion
• FAQs

Prerequisites

• Visual Studio 2008
• Microsoft SQL Server 2005 Express Edition with Advanced Services or SQL Express and Management Studio
• ADO.NET Data Services Silverlight Add-On
• ASP.NET 3.5 Extensions Preview (Dec 2007)
• Silverlight 1.1 September Refresh
• ADO.NET Entity Framework Beta 3
• XML Editor QFE
• ADO.NET Entity Framework Tools Dec 07 Community Technology Preview

Creating the database in SQL 2005

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

Step 1: Open SQL Server Management Studio Express:

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

Step 3: Right-click on the Database node from Object Explorer and select "New Database":

Step 4: Type your database name (I named it "MyStore" in this sample) and click the "OK" button:

Step 5: Right-click on the 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 a new table in SQL 2005.

Creating ADO.NET Data Service in Visual Studio 2008

Note: I already explained how to create ADO.NET Data Service in my previous article "Consuming ADO.NET Data Service (Astoria) from Silverlight". (I will use the ASP.NET 3.5 Extensions Web Application in this sample.) If you have already read this post, please skip this section.

Step 1: Create a new ASP.NET 3.5 Extensions Web Application

• Click the “New Project” icon or press “Ctrl+Shift+N”.
• Select “ASP.NET Web Application” and name the application as “CRUDSilverlight”.
• Click the “OK” button.

Step 2: Create “ADO.NET Entity Data Model”

After creating a new ASP.NET 3.5 Ext project, you have to add the "ADO.NET Entity Data Model" to your project. You have to generate the data model from the table that you have created in SQL 2005. If you want to see all the screenshots for adding the ADO.NET Entity Data Model, please check "Creating ADO.NET Entity Data Model" in this article.

Step 3: Create “ADO.NET Data Service”

After adding the ADO.NET Entity Data Model, you have to add the "ADO.NET Data Service" in your project. Then, you have to put the data source class name in the first line and uncomment "config.SetResourceContainerAccessRule("MyEntityset", ResourceContainerRights.AllRead);" in the InitializeService() function. Replace "MyEntityset" with "*" and change "AllRead" to "All".

public class WebDataService1 : WebDataService<MyStoreModel.MyStoreEntities>
{

    // This method is called once during service initialization to allow
    // service-specific policies to be set
    public static void InitializeService(IWebDataServiceConfiguration config)
    {

        // TODO: set rules to indicate which entity sets and service operations are
        // visible, updatable, etc.
        // (for testing purposes use "*" to indicate all entity sets/service
        // operations, but that option should NOT be used in production systems)

        // Example for entity sets (this example uses
        // "AllRead" which allows reads but not writes)
        config.SetResourceContainerAccessRule("*", ResourceContainerRights.All);


        // Example for service operations
        //config.SetServiceOperationAccessRule("*", ServiceOperationRights.All);
    } 

    // Query interceptors, change interceptors and service operations go here
}

Step 4: Change the auto-assign port to a specific port of your web application

Note that this step is very important. We should use a static port to prevent the cross-domain problem. The Web tab is under the Properties window of the project.

Making an Astoria Proxy for the Silverlight project

When I was writing how to consume ADO.NET Data Service in Silverlight, I didn't use Webdatagen.exe to generate the proxy class for Astoria. Bryant pointed me the way to generate the proxy class in this post. (Thanks a lot, Bryant) Thus, we can save our time for duplicating the class between the Silverlight project and the ASP.NET web project.

• Go to the "Visual Studio 2008 Command Prompt". (Note: If you are on Windows Vista, you have to run the command line with Administrator rights. Right-click on the menu and select "Run as Administrator". Otherwise, Webdatagen.exe won't be able to write the generated file on disk.)
• Go to the "C:\Program Files\Microsoft ASP.NET 3.5 Extensions>" folder.
• Type the following in the command line:

"WebDataGen.exe /mode:ClientClassGeneration
/outobjectlayer:Products.cs
/uri:http://localhost:52799/WebDataService1.svc"

Products.cs is the name of the proxy file that we want to generate. http://localhost:52799/WebDataService1.svc is the URL of the ADO.NET Data Service.

Now, check the Products.cs under Microsoft ASP.NET 3.5 Extensions. You can add this proxy file to the Silverlight project.

Okay. We have done the preparation to do CRUD operations in Silverlight using ADO.NET Data Service. Let's take a look at how to do those operations.

Inserting a new record in Silverlight

You can create an instance of the Products object and set the product name as you like. We don't need to specify the product ID since the key is the identity field. As we are adding the data, we will set the MergeOption to AppendOnly.

try{

    MyStoreModel.Products _products = new MyStoreModel.Products();
    _products.ProductName = "Windows Vista";

    MyStoreModel.MyStoreEntities _mystore = 
       new MyStoreModel.MyStoreEntities(_serviceUriString);
    _mystore.MergeOption = Microsoft.Data.WebClient.MergeOption.AppendOnly;
    _mystore.AddObject("Products", _products);
    _mystore.SaveChanges();

    insertTextBlock.Text = "New record has been added successfully." + 
                           " Please check Products table in SQL";
}
catch(Exception ex){
   throw ex;
}

Updating an existing record in Silverlight

The code below is for updating an existing record in the database. We will use the "OverwriteChanges" MergeOption for updating.

try{

    MyStoreModel.MyStoreEntities _mystore = 
      new MyStoreModel.MyStoreEntities(_serviceUriString);
    _mystore.MergeOption = 
      Microsoft.Data.WebClient.MergeOption.OverwriteChanges;


    MyStoreModel.Products _products = new MyStoreModel.Products();
    _products.ProductID = 1;
    _products.ProductName = "Visual Studio 2008 Professional";

    _mystore.AttachObject("Products", _products);
    _mystore.UpdateObject(_products);
    updateTextBlock.Text = "ProductID(#1) has been updated successfully." + 
                           " Please check Products table in SQL";

    _mystore.SaveChanges();

}
catch (Exception ex) {
    Console.WriteLine(ex.Message);
}

Note: The ReadMe.txt of Astoria said ~

Update (calling the UpdateObject followed by the SaveChanges method) may return an exception even though the operation succeeds on the server side.

So, you will get the exception while updating the record, but don't worry about that. Your record will be updated in the SQL database even if you get the exception. This is a known-issue that will be fixed in the next release.

Retrieving a record from the database in Silverlight

The following code is for retrieving the record from the database. You can also the post: "consuming the ADO.NET Data Service", in my blog.

try {


    MyStoreModel.MyStoreEntities _mystore = 
       new MyStoreModel.MyStoreEntities(_serviceUriString);
    _mystore.MergeOption = Microsoft.Data.WebClient.MergeOption.OverwriteChanges;

    Microsoft.Data.WebClient.WebDataQuery<MyStoreModel.Products>
    products = _mystore.CreateQuery<MyStoreModel.Products>(
               "/Products?$orderby=ProductID");

    double top=0;

    foreach (MyStoreModel.Products p in products) {
          TextBlock tbk = new TextBlock();
          tbk.Text = p.ProductName;
    
          tbk.Height = 50;
    
          top += tbk.Height;
    
          tbk.SetValue(TopProperty, top);
    
          view.Children.Add(tbk);
    }
}

catch (Exception ex) {
   Console.WriteLine(ex.Message);
}

Deleting a record in Silverlight

try{

    MyStoreModel.MyStoreEntities _mystore = 
       new MyStoreModel.MyStoreEntities(_serviceUriString);
    _mystore.MergeOption = Microsoft.Data.WebClient.MergeOption.OverwriteChanges;

    MyStoreModel.Products _products = 
       new MyStoreModel.Products();
    _products.ProductID = 1;

    _mystore.AttachObject("Products", _products);
    _mystore.DeleteObject(_products);
    _mystore.SaveChanges();

    deleteTextBlock.Text = "ProductID(#1) has been deleted " + 
               "successfully. Please check Products table in SQL";
}
catch (Exception ex) {
  Console.WriteLine(ex.Message);
}

Storing the configs in XML

As there is no setting or web.config in the Silverlight project, I chose to use an XML file to store configuration information. But I think it might be better if we change the extension to config in the real project. In this sample, I stored the Service URI in the Configs.xml file.

<?xml version="1.0" encoding="utf-8" ?>
<ServiceUriString>http://localhost:52799/WebDataService1.svc</ServiceUriString>

As I don't want to load this XML by using WebRequest, I embedded this XML in the project. The following code is for reading the XML file in Silverlight:

Stream stream = this.GetType().Assembly.GetManifestResourceStream(
                "SilverlightProject1.Configs.xml");
XmlReader reader = XmlReader.Create(stream);
while (reader.Read()) {
  if (reader.IsStartElement()) {
    if ("ServiceUriString" == reader.LocalName) {
       reader.Read();
       string _serviceURL = reader.Value;
       reader = null;
       stream.Dispose();
       return _serviceURL;
    }
  }
} 

reader = null;
stream.Dispose();

return string.Empty;

Conclusion

That's all about doing CRUD operations in Silverlight. Sorry for not having a nice UI in my sample. As this article focuses on CRUD operations, I didn't want to add the complexities of UI code in the sample. I hope you will find it useful, and feel free to let me know if you have any comments or suggestions. Thanks.

FAQs

1. When I was retrieving a record from the database, I got this exception "Data at the root level is invalid. Line 1, position 1.". Why?

The UriString for the Data Service is wrong. Please check whether you have any typo error or not. Another thing is that the UriString is case-sensitive.

2. I'm getting this exception "Operation is not valid due to the current state of the object." while updating the record. Why?

As I wrote in the "Updating the record" section, this is a known-issue that will be fixed in the next release. Your record will be updated in the database, but you will get the exception. Just ignore the exception.

3. "Unable to update the EntitySet 'Products' because it has a DefiningQuery and no <InsertFunction> element exists in the <ModificationFunctionMapping> element to support the current operation"

Just delete the Entity Model in your project and re-create the new one. I faced that problem when I was creating this sample. What I did was I created the Entity Model in VS without creating the primary key in the Products table. When I tried to insert the new record, I got this error. So, I deleted the Entity model and re-created it again. It will solve the problem. I think there might be another good way to do that, but I don't know. It would be great if someone can show me the way.

4. I'm trying to generate the proxy class using WebDataGen.exe, but no file was generated even though there is no error shown in the command line.

If you are a Windows Vista user, you need to run as Administrator when you launch the command line.

5. Where did you store the Service URL in your Silverlight project?

As there is no setting or web.config, I stored the connection string in Configs.xml that is set as "Embedded Resource" in Silverlight. There is a class called "Configs.cs" that will read the service URL string from that XML file.

6. I have other questions that aren't included in the FAQs. How can I contact you?

If you have any questions or suggestions, you can drop a comment in this post. I always read all comments, and I will reply as soon as I can.

Related posts ~

• Consuming the ADO.NET Data Service (Astoria) from Silverlight
• OpenSource Silverlight 2.0 controls and how to use them
• Silverlight 2.0 - How to design the blog theme with Silverlight
• Silverlight Tips/Tricks: Communicating between JavaScript and C#