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:
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.
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.
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.
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.
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
}
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.
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.
"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.
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;
}
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
UpdateObjectfollowed by theSaveChangesmethod) 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.
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);
}
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);
}
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;
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.
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.
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.
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.
If you are a Windows Vista user, you need to run as Administrator when you launch the command line.
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.
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.
| You must Sign In to use this message board. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
