CRUD operations in Siverlight using ADO.NET Data Service 14

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.

Download Sample: CRUDSilverlight.zip

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

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 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“. (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

VS 2008 - New ASP.NET 3.5 Extensions Web Application

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.

Products Data Model

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.

Change the auto-assign port to specific port of your web application

Making Astoria Proxy for 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 how to generate the proxy class in this post. (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.
    "WebDataGen.exe /mode:ClientClassGeneration /outobjectlayer:Products.cs /uri:http://localhost:52799/WebDataService1.svc"

    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.

Making Astoria Proxy for Silverlight project

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 in this post. I always read all comments and I will reply as soon as I can.

Related ~

14 thoughts on “CRUD operations in Siverlight using ADO.NET Data Service

  1. Pingback: technology » Blog Archive » CRUD operations in Siverlight using ADO.NET Data Service

  2. Pingback: CRUD operations in Siverlight using ADO.NET Data Service | time management

  3. Pingback: Silverlight Cream for February 11, 2008 -- #195

  4. Pingback: Wöchentliche Rundablage: ASP.NET, ASP.NET 3.5, ASP.NET MVC, Geocoding, WPF, WF, CardSpace, IIS7, LINQ, XNA… | Code-Inside Blog

  5. Pingback: » Daily Bits - February 14, 2008 Alvin Ashcraft’s Daily Geek Bits: Daily links, development, gadgets and raising rugrats.

  6. Reply Riaz Afridi Feb 20,2008 11:29 pm

    Im trying to generate the proxy class usingWebDataGen.exe

    C:\Program Files\Microsoft ASP.NET 3.5 Exten
    ssGeneration /outobjectlayer:Products.cs /urice1.svc
    Microsoft (R) WebDataGen version 3.5.0.0
    Copyright (C) Microsoft Corporation 2007. Al

    Writing object layer file…
    error 7001: Value cannot be null.
    Parameter name: key

    Generation Complete — 1 errors, 0 warnings

  7. Reply Mark Easton Mar 8,2008 4:25 am

    Hi

    Excellent tutorial.
    I am trying to get it to work in Silverlight 2 but no success. Proxy classes created by WebGen seem to have a lot of problems. Can you point me in the right direction?

    I also tried to add a web reference and it discovered the service but gave an error when I tried to add it. It seemed as if it needed the service to be running to successfully add it, but if I had it running the ‘Add Web Referece’ menu option was greyed out

    Thanks.

  8. Reply Michael Sync Mar 8,2008 8:23 pm

    Hello Mark,

    I saw your comment in my blog. Sorry for late reply. I was busy with finding the solution for some people’s problems from Silverlight forum. and also, I’m learning new feature of SL2 too.

    Yes. I’m trying to create one sample for you with Silverlight 2 but the problem is that I’m not able to add Microsoft.Data.WebClient.dll in Silverlight 2 project. As other said, Microsoft.Data.WebClient.dll is not compatible with Silverlight 2. So, we are gonna need to wait a lit. I will update my sample as soon as I got the updated version of MS.Data.WebClient and I will contact you at that time.

  9. Pingback: Michael Sync » Silverlight 2 (beta1) - Database Operations with ASP.NET Web Service in Silverlight 2

  10. Pingback: Michael Sync » ADO.NET Data Service (Astoria) in Silverlight 2 (beta1)

  11. Reply SilverlightFan Nov 28,2008 11:15 pm

    Did you ever get this to work with *shipping* products? There doesn’t seem to be a single working RTM example of a Silverlight form creating or updating real data!

    Microsoft has done a real poor job on documenting this stuff.

  12. Reply Rakesh Feb 5,2009 3:22 pm

    Hi Micheal,
    This is great article. We have created the ADO.NET Data Services for couple of our tables. For one of our table I get following error. I am assuming this is because of response size. Have you ever encountered this sort of issue?
    Please let us know.

    Thanks
    Rakesh

    {System.Xml.XmlException: Unexpected end of file has occurred. The following elements are not closed: feed. Line 6757, position 7.
    at System.Xml.XmlTextReaderImpl.Throw(Exception e)
    at System.Xml.XmlTextReaderImpl.Throw(String res, String arg)
    at System.Xml.XmlTextReaderImpl.Throw(Int32 pos, String res, String arg)
    at System.Xml.XmlTextReaderImpl.ThrowUnclosedElements()
    at System.Xml.XmlTextReaderImpl.ParseEndElement()
    at System.Xml.XmlTextReaderImpl.ParseElementContent()
    at System.Xml.XmlTextReaderImpl.Read()
    at System.Data.Services.Client.MaterializeAtom.MoveNext()
    at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()
    at System.Linq.SystemCore_EnumerableDebugView`1.get_Items()}

  13. Reply Jelana Mar 14,2009 4:33 am

    Best about CRUD and Silverlight i found !!! thanks

  14. Reply lance Sep 1,2009 3:19 pm

    _serviceUriString is not defined in my project, what should it equal and where should it be defined?

Leave a Reply