The following steps are used to create oData provider with MS WCF data service
Required software:
SQL server 2012~2014
Visual Studio 2015
1. Create Sample NorthWind database in SQL 2012
1.1 download Northwind database from the below link and install
http://www.microsoft.com/en-us/download/details.aspx?id=23654
1.2 open sql server 2012, open file instnwnd.sql, replace following two lines
exec sp_dboption 'Northwind','trunc. log on chkpt.','true'
exec sp_dboption 'Northwind','select into/bulkcopy','true'
with the new line
ALTER DATABASE Northwind SET RECOVERY SIMPLE
1.3 execute the script and verify the northwind database is created.
2. Create Data model
2.0 Create ASP.Net web empty project in VS2015
2.1 right click the solution and select Add new Item, select Data, and ADO.Net entity Data model, named it as Northwind.edmx
2.2 select EF Designer from Database, and choose Northwind database from SQL Server 2012
2.3 select Entity Framework 6.x
2.4 select the checkbox of all tables , deselect views and store procedures
3. Create WCF data service
3.1 right click the solution and select manger NuGet package menu
3.2 Right-click the project and choose Manage NuGet Packages. From the Manage NuGet Packages dialog, make sure Include Prerelease is selected in the top dropdown box. Search for “Microsoft.OData.EntityFrameworkProvider” and install it.
3.3 Add new item from Web->WCF Data service 5.6.4, name it as Northwind.svc
3.2 in Northwind.svc.cs, replace the comment /* TODO: put your data source class name here */ to
System.Data.Services.Providers.EntityFrameworkDataService< NorthwindEntities >
3.3 Add following lines in InitializeService method
config.SetEntitySetAccessRule("*", EntitySetRights.All);
3.4 select Northwind.svc as starting page and F5 to start the app in IE with URL of
http://localhost:54108/Northwind.svc/Customers('ALFKI')/Orders?$filter=OrderID eq 10643
The port port 54108 may be different
3.5 For windows8 and iis 8, you also need to enable ".NET Framework 4.5 Advanced Service" from control panel's Turn Windows Features on or off to allow query on .svc
4. Create client application
4.1 create a .net form applicaton
4.2 select "view" "Other Windows, Data Source" and select service type to add northwind service as NorthwindClient
4.3 add a datagridview control and a button, when button is clicked, run the below code
NorthwindEntities context = new NorthwindEntities(new Uri("http://localhost:54108/Northwind.svc/"));
context.IgnoreMissingProperties = true;
var customer = context.Customers.ToList();
dataGridView1.DataSource = customer;
4.4 run the app and click the button to show the customer list
4.5 Add two textbox for user to input new customer name and id
4.5 Add a button of "New" to create new customer with the following code
NorthwindEntities context = new NorthwindEntities(new Uri("http://localhost:54108/Northwind.svc/"));
context.AddToCustomers(new Customer() { CustomerID = txtID.Text.Trim(), CompanyName = txtName.Text.Trim() });
context.SaveChanges();
4.6 get a id and name, click new button, the customer should be added into database
4.7 add a button of update to update the company name with the below code
string customerId = txtID.Text.Trim();
NorthwindEntities context = new NorthwindEntities(new Uri("http://localhost:54108/Northwind.svc/"));
var customerToChange = (from customer in context.Customers
where customer.CustomerID == customerId
select customer).Single();
customerToChange.CompanyName = txtName.Text.Trim();
customerToChange.ContactName = txtName.Text.Trim();
context.UpdateObject(customerToChange);
context.SaveChanges();
4.8 add a button to delete a customer
string customerId = txtID.Text.Trim();
NorthwindEntities context = new NorthwindEntities(new Uri("http://localhost:54108/Northwind.svc/"));
var customerToChange = (from customer in context.Customers
where customer.CustomerID == customerId
select customer).Single();
context.DeleteObject(customerToChange);
context.SaveChanges();
