Objective :
This article is step by step illustated explanation of How to use ADO.Net data service ( Project Astoria ) for CRUD operations on a associated table. After that how to consume that in console client.
For introduction of ADO.NET Data service see my other articles
Here and Here
Step 1:
Create the project
Create a New Project as Web application and give it name as desired. Here name is adosample2
Step 2:
Create entity data model
Description of Database
There are two tables EMP and DEPT with structure shown below. EMP and DEPT table is related on coulmn DeptId . DeptId is primary key in Dept table and related to DeptId coulmn of EMP table.
Right clcik on Project and add new Item. Click on Data tab then select Ado.net Entity Model.
Give any name , here name is dj.edmx.
Here , we are selecting dj database, which is already in our SQL Server.
Up to these steps entity model has been created.
Step 3:
Create Ado.Net data service
Right click on project and add new item. Select ADO.NET Data Service from Web tab. Give name as of desire. Name of service is here DataService.
DataService.svc.cs file will get open. The default generated code will less or more like below.
using System;
using System.Collections.Generic;
using System.Data.Services;
using System.Linq;
using System.ServiceModel.Web;
using System.Web;
namespace associationsample
{
public class DataService : DataService< /* TODO: put your data source class name here */ >
{
// This method is called only once to initialize service-wide policies.
public static void InitializeService(IDataServiceConfiguration2 config)
{
// TODO: set rules to indicate which entity sets and service operations are visible, updatable, etc.
// Examples:
// config.SetEntitySetAccessRule("MyEntityset", EntitySetRights.AllRead);
// config.SetServiceOperationAccessRule("MyServiceOperation", ServiceOperationRights.All);
}
}
}
In Highlighted code above, we need to give data source class name there. In our case data source class name is djEntities. (Check out step 2 for more, if you have not changed any default name during entity model creation, then it would be your database name appended with Entities)
public class DataService : DataService<djEntities>
We need to change access rule also here.
config.SetEntitySetAccessRule("*", EntitySetRights.All);
So the complete code of service class will look like
using System;
using System.Collections.Generic;
using System.Data.Services;
using System.Linq;
using System.ServiceModel.Web;
using System.Web;
namespace associationsample
{
public class DataService : DataService<djEntities>
{
// This method is called only once to initialize service-wide policies.
public static void InitializeService(IDataServiceConfiguration2 config)
{
// TODO: set rules to indicate which entity sets and service operations are visible, updatable, etc.
// Examples:
config.SetEntitySetAccessRule("*", EntitySetRights.All);
config.SetServiceOperationAccessRule("*", ServiceOperationRights.All);
}
}
}
Step 4:
Run the code by pressing F5.
We could see that each entity of database is exposed as URI.
To view all the records of Emp table, we need to append Player in URI like below
http://localhost:1472/DataService.svc/Emp/
Output would be
Creating Client
We could consume ADO.Net data service in any type of client. For example
-
Window application
-
Ajax enabled application
-
Web application
-
Silver Light application.
Here we will consume this service in a console application.
Step 5:
Right click on Solution and add a new Project. And select a Console Application
Step 6:
Add service reference to window application.
To do so Right click on console project (client) and select Add Service Reference. after that click on Discover , it will automatically discover the service reference from the solution. The other way is to do is copy and paste URL from browser while running the ADO Net Data service.
After clicking on Discover, below screen will get appear? After clicking OK, ServiceReference1 could be seen in solution explorer.
so namespaces added are
using System.Data.Services.Client;
using client.ServiceReference1;
Step 8:
Various Data Base operations using ADO.Net Data Service
-
Make object of DataServiceContext and djEntities.
Note : Here entity class is djEntities , in your case it would be name of the Connection string , you given while creating entity model.
DataServiceContext context = new DataServiceContext(new Uri("http://localhost:1472/DataService.svc/")); djEntities ent = new djEntities(new Uri("http://localhost:1472/DataService.svc/"));
Note : After creating Instance of DataServiceContext , if you are getting any error , go to refernce of your project and delete System.Data.Service.Client . This error might come , if we are using ADO.NET 1.5 ctp. Don't forget to add refrence as shown above.
In URI , give URI of the service. To get it Right click on Service and click View in Browser.
-
Displaying using LINQ and ado.net data service
public static void Display()
{
var res = from r in ent.Emp.Expand("Dept") select r;
foreach (Emp e in res)
{
Console.WriteLine(e.Dept.DeptId.ToString() + e.EmpId.ToString() + e.EmpName);
To fetch data in combined table , there is need to use Expand keyword.
-
Inserting into Database
public static void Insert()
{
Emp e = new Emp();
Console.WriteLine(" Enter Name of Employee \n");
e.EmpName = Console.ReadLine();
e.EmpId = Guid.NewGuid();
Console.WriteLine(" Enter Dept Name\n");
string deptname = Console.ReadLine();
Dept d = (from r in ent.Dept where r.DeptName.ToLower()== deptname.ToLower().Trim() select r).First();
ent.AddObject("Emp", e);
e.Dept = d;
ent.SetLink(e, "Dept", d);
ent.SaveChanges();
Console.WriteLine("Record Saved ");
}
Explanation:
Since Emp and Dept table are related to each other on column DeptId . So DeptId is not exposed as property here. Instead of that Dept is exposed.
So we cannot assign deptid directly. We need to insert as explained above. Below are the key line of code, which is doing task of insertion.
ent.AddObject("Emp", e);
e.Dept = d;
ent.SetLink(e, "Dept", d);
ent.SaveChanges();
-
Updating into Database
public static void Update()
{
Guid id = new Guid("16B15365-53A3-425D-BE39-6FE2839523BE");
Emp e = (from r in ent.Emp where r.EmpId == id select r).First();
e.EmpName = " Updated Name";
Guid did = new Guid("5CB4B76F-6379-4203-AD1E-3FA2B91ADCD5");
Dept d = (from r in ent.Dept where r.DeptId==did select r).First();
ent.UpdateObject(e);
e.Dept = d;
ent.SetLink(e,"Dept", d);
ent.SaveChanges();
Console.WriteLine("Record Updated ");
}
Here , first we will query for the record on criteria
-
Deleting From Database
public static void Delete()
{
// Guid to be delete
Guid id = new Guid("16B15365-53A3-425D-BE39-6FE2839523BE");
Emp e = (from r in ent.Emp where r.EmpId == id select r).First();
ent.DeleteObject(e);
ent.SaveChanges();
Console.WriteLine("Record Deleted ");
}
Deletion is same as of normal deletion.
Complete code of Program.cs is as below
Program.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Client.ServiceReference1;
using System.Data.Services.Client;
namespace Client
{
class Program
{
static DataServiceContext context = new DataServiceContext(new Uri("http://localhost:1472/DataService.svc/"));
static djEntities ent = new djEntities(new Uri("http://localhost:1472/DataService.svc/"));
static void Main(string[] args)
{
//Insert();
// Update();
//Delete();
//Display();
Console.Read();
}
public static void Insert()
{
Emp e = new Emp();
Console.WriteLine(" Enter Name of Employee \n");
e.EmpName = Console.ReadLine();
e.EmpId = Guid.NewGuid();
Console.WriteLine(" Enter Dept Name\n");
string deptname = Console.ReadLine();
Dept d = (from r in ent.Dept where r.DeptName.ToLower()== deptname.ToLower().Trim() select r).First();
ent.AddObject("Emp", e);
e.Dept = d;
ent.SetLink(e, "Dept", d);
ent.SaveChanges();
Console.WriteLine("Record Saved ");
}
public static void Update()
{
Guid id = new Guid("16B15365-53A3-425D-BE39-6FE2839523BE");
Emp e = (from r in ent.Emp where r.EmpId == id select r).First();
e.EmpName = " Updated Name";
Guid did = new Guid("5CB4B76F-6379-4203-AD1E-3FA2B91ADCD5");
Dept d = (from r in ent.Dept where r.DeptId==did select r).First();
ent.UpdateObject(e);
e.Dept = d;
ent.SetLink(e,"Dept", d);
ent.SaveChanges();
Console.WriteLine("Record Updated ");
}
public static void Delete()
{
// Guid to be delete
Guid id = new Guid("16B15365-53A3-425D-BE39-6FE2839523BE");
Emp e = (from r in ent.Emp where r.EmpId == id select r).First();
ent.DeleteObject(e);
ent.SaveChanges();
Console.WriteLine("Record Deleted ");
}
public static void Display()
{
var res = from r in ent.Emp.Expand("Dept") select r;
foreach (Emp e in res)
{
Console.WriteLine(e.Dept.DeptId.ToString() + e.EmpId.ToString() + e.EmpName);
}
Console.WriteLine();
}
}
}
Conclusion
This article explained how to perform CRUD operation on associated table.
Happy Coding