.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Articles >> Architecture/Pattern >> Post New Resource Bookmark and Share   

 Subscribe to Articles

CRUD Operation on Associated table in ADO.NET Data Service

Posted By:Dhananjay Kumar       Posted Date: December 09, 2009    Points: 25    Category: Architecture/Pattern    URL: http://www.dotnetspark.com  
 

CRUD Operation in Associated table in ADO.Net Data Service

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
1.  Window application
2.  Ajax enabled application
3.  Web application
4.  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.





Name of window project. give name which you given as creating window project.

so namespaces added are
using System.Data.Services.Client;
using client.ServiceReference1;


Step 8:

Various Data Base operations using ADO.Net Data Service
a.  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);

  }

  Console.WriteLine();

  }
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



 Subscribe to Articles

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend