Introduction

This article describes the simplest way of how 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 match. If you have no idea about what ADO.NET is, please read my previous post here [1].

Download Sample: CRUDSilverlight.zip [2]

Contents

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

Prerequisites

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

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 ADO.NET Data Service in Visual Studio 2008

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

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

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

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

After creating new ASP.NET 3.5 Ext project, you have to add "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 screenshots for adding ADO.NET Entity Data Model, please check "Creating ADO.NET Entity Data Model" in this article [12].

Step 3: Create “ADO.NET Data Service”

After adding ADO.NET Entity Data Model, you have to add "ADO.NET Data Service" in your project. Then, you have to put the data source class name in first line and uncomment "config.SetResourceContainerAccessRule("MyEntityset", ResourceContainerRights.AllRead);" in 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 specific port of your web application

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

Making Astoria Proxy for Silverlight project

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

• Go to "Visual Studio 2008 Command Prompt". (Note: If you are on Windows Vista, you have to run the commandline with administrator right. Righ-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 "C:\Program Files\Microsoft ASP.NET 3.5 Extensions>" folder.
• Type the following in commandline.[sourcecode language="xml"]"WebDataGen.exe /mode:ClientClassGeneration /outobjectlayer:Products.cs /uri:http://localhost:52799/WebDataService1.svc"[/sourcecode]

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

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

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

Inserting new record in Silverlight

You can create the instance of Products object and set the product name as you like. We don't need to specify the product id since the key is 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 the existing record in Silverlight

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

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 SaveChanges methods) may return an exception even though the operation succeeded 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 SQL database even you got the exception. This is a known-issue that will be fixed in next release.

Retrieving the record from Database in Silverlight

The following code is for retrieving the record from Database. You can also read other 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 the 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 Silverlight project, I chose to use the XML file to store the configuration information. But I think it might be better if we change the extension to config in real project. In this sample, I stored the Service URI in 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 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 the nice UI in my sample. As this article is focusing on CRUD operation, I don'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 comment or suggest. Thanks.

FAQs

1. When I was retrieving the record from database, I got this exception "Data at the root level is invalid. Line 1, position 1.". Why?
   
   The UriString for 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 "Updating the record" section, this is a known-issue that will be fixed in next release. Your record will be updated in 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 create the Entity Model in VS without creating the primary key in Products table. When I tried to insert the new record, I got this error. So, I delete the Entity model and re-create it again. It will solve the problem. I think there would 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 there is no error shown in commandline.
   
   If you are Windows Vista user, you need to run as administrator when you launch the commandline.
5. Where did you store the Service URL in your silverlight project?
   
   As there is no setting or web.config, I stored the connectionstring in Configs.xml that set as "Embedded Resource" in Silverlight.. There is one class called "Configs.cs" that will read the service url string from that xml file.
6. I have other questions that doesn't include in FAQs. So, How can I contact with you?
   
   If you have any question or suggestion, you can drop a comment here [15]. I always read all comments and I will reply as soon as I can.