Tuesday, November 5, 2013

DML Operations with Ado.NET Entity Model

Today I am going to explore the Insert ,update,delete operations using ADO.NET Data Entity Model.
Our main focus is do insert ,update & delete successfully,In latter POST we will try to more consuming topic like transcation.
First we will create 5 tables in our database say TestBed 

create table Course( Id int not null identity(1,1)primary key, Name varchar(50), Duration int)
create table Student( Id int not null identity(1,1) primary key, Name varchar(50), EntryDate datetime)
create table CourseToStudent(Id int not null identity(1,1), CourseId int references Course(Id),StudentId int references Student(Id),StartDate datetime)
create table Course2Student(Id int not null identity(1,1),C2SId int references CourseToStudent(Id))
create table DummyTrail(id int identity(1,1) primary key,CourseName varchar(50),DeletedOn Datetime)
Add some data into them to resemble below stated
 Student:
Id
Name
EntryDate
1
sagar
2013-11-05
2
sangram
2013-11-05
3
sachin
2013-11-05
4
swapnil
2013-11-05
  Course:
Id
Name
Duration
1
C
20
2
C++
30
3
VB 6.0
40
4
VC ++
60
5
C#
60
6
VB.NET
60
7
ASP.NET
60
8
PHP 5.0
40
   CourseToStudent:

Id
CourseId
StudentId
StartDate
1
1
1
2013-11-05
2
1
2
2013-11-05
3
1
3
2013-11-05
4
1
4
2013-11-05
    
   Now we will add Ado.net to entity data model to our brand new console application
To Demonstrate Insert ,update & delete with entity data model.

 I am going to create some static functions for each i.e. for insert ,update & delete.
Code for them is as follows
class Program
    {
   static void Main(string[] args)
   {
   Console.WriteLine("Existing Course Collection is");
   ShowAllCourses();

   Console.WriteLine("Adding Node.js Course");
   AddNodeJs();
   ShowAllCourses();
   Console.WriteLine("Updating Course Name from 'Node.js' to 'NODE JS'");
   UpdateNodeJs();
   ShowAllCourses();
   Console.WriteLine("Removing Course 'NODE JS'");
   //DeleteNodeJs();
   DeleteNodeJs();
   ShowAllCourses();
   Console.ReadKey();
   }

    private static void ShowAllCourses()
    {
      Console.WriteLine("------------------------------------");
      //create DataContext object
      TestBedEntities DataContext = new TestBedEntities();
      var AllCourses = from m in DataContext.Courses
                      select new { CourseId = m.Id, 
                         CourseName = m.Name, 
                         CourseDuration = m.Duration };
      foreach (var course in AllCourses)
      {
        Console.Write("Id: " + course.CourseId);
        Console.Write(", Name: " + course.CourseName);
        Console.WriteLine(", Duration: " + course.CourseDuration);
      }
      Console.WriteLine("--------------------------------");
    }

    //Add New Records
    private static void AddNodeJs()
    {
      TestBedEntities DataContext = new TestBedEntities();
      Course NodeJsCourse = new Course();

     //add new course namely node.js
     NodeJsCourse.Name = "Node.js";
     NodeJsCourse.Duration = 50;
     DataContext.Courses.Add(NodeJsCourse);
     DataContext.SaveChanges();

     //enroll student to node.js course
     CourseToStudent c2s1 = new CourseToStudent 
                    {CourseId = NodeJsCourse.Id, 
                     StartDate = DateTime.Now,StudentId = 2 };
     DataContext.CourseToStudents.Add(c2s1);

     //create a obstable for deleteing
     Course2Student obst = new Course2Student{C2SId=c2s1.Id };
     DataContext.Course2Student.Add(obst);
     DataContext.SaveChanges();
   }

   //update existing record
   private static void UpdateNodeJs()
    {
   TestBedEntities DataContext = new TestBedEntities();
   Course NodejsCourse = DataContext.Courses.
                  FirstOrDefault(m => m.Name=="Node.js");
   if (NodejsCourse != null)
   {
    NodejsCourse.Name = "NODE js";
    DataContext.SaveChanges();
   }
  }
   //delete record
   /*private static void DeleteNodeJs()
     {
  //Data maping object to our database
  TestBedEntities DataContext = new TestBedEntities();
  Course NodeJsCourse = DataContext.Courses.FirstOrDefault(m => m.Name== "NODE js");

  if (NodeJsCourse != null)
  {
    DataContext.Courses.Remove(NodeJsCourse);
    DataContext.SaveChanges();
  }
 }*/

    public static void DeleteNodeJs()
    {
      //Data maping object to our database
       TestBedEntities DataContext = new TestBedEntities();
       Course NodeJsCourse = DataContext.Courses.FirstOrDefault(m => m.Name 
                                                               == "NODE js");

        int NodeJsCourseId = NodeJsCourse.Id;
        CourseToStudent NodeJsCourseStudents = DataContext.CourseToStudents.
             FirstOrDefault(m=>m.CourseId == NodeJsCourseId);
        Course2Student NodeJsCourse2Students = DataContext.Course2Student.
             FirstOrDefault(m => m.C2SId == NodeJsCourseStudents.Id);
        DummyTrail MyDummyTrail = new DummyTrail{CourseName=NodeJsCourse.Name,
             DeletedOn=DateTime.Now};

         if ((NodeJsCourseStudents != null) && (NodeJsCourse != null))
           {
              using (TransactionScope scope = new TransactionScope())
               {
               try
               {
                //first try to delete student in course
                DataContext.DummyTrails.Add(MyDummyTrail);
                DataContext.Course2Student.Remove(NodeJsCourse2Students);
                DataContext.CourseToStudents.Remove(NodeJsCourseStudents);
                            
                //if succeed try to delete course
                DataContext.Courses.Remove(NodeJsCourse);
                DataContext.SaveChanges();
                scope.Complete();
               }
               catch (Exception exp)
               { 
               }
               finally
               {
                scope.Dispose();
               }
              }
           }
        }
    }
Lets explore the methods written above

1) AddNodeJs: This methods Insert database operation it adds new record to course table and then enrolls an existing student to newly added course Course2Student is table which has two columns only one is primary key & other is foreign key from CoursetoStudent table. Purpose of this table is to create an obstacle for directly deleting a record from CoursetoStudent so that we can latter work on something similar to implementing transaction.

2) UpdateNodeJs: This method demonstrate database update operation it first chooses record for Course “Node.js” and update its name to “NODE Js”

3) DeleteNodeJs: This method deletes the record from Course2Student table first then from CourseToStudent table & then Course table ,to satisfy foreign key constraints set on table schema.
     At the same time it insert the deleted record into DummyTrail table Try commenting code for deleteting Course2Students but try to delete from Course2Students it will throw an exception

public static void DeleteNodeJs()
{
//Data maping object to our database
TestBedEntities DataContext = new TestBedEntities();
Course NodeJsCourse = DataContext.Courses.FirstOrDefault(m => m.Name == "NODE js");

int NodeJsCourseId = NodeJsCourse.Id;
CourseToStudent NodeJsCourseStudents = DataContext.CourseToStudents.FirstOrDefault(m=>m.CourseId == NodeJsCourseId);

//Course2Student NodeJsCourse2Students = DataContext.Course2Student.
             FirstOrDefault(m => m.C2SId == NodeJsCourseStudents.Id);

DummyTrail MyDummyTrail = new DummyTrail { CourseName=NodeJsCourse.Name,DeletedOn=DateTime.Now};

if ((NodeJsCourseStudents != null) && (NodeJsCourse != null))
{
using (TransactionScope scope = new TransactionScope())
{
try
{
//first try to delete student in course
DataContext.DummyTrails.Add(MyDummyTrail);
//DataContext.Course2Student.Remove(NodeJsCourse2Students);
DataContext.CourseToStudents.Remove(NodeJsCourseStudents);

//if succeed try to delete course

DataContext.Courses.Remove(NodeJsCourse);

DataContext.SaveChanges();
scope.Complete();
}
catch (Exception exp)
{
}
finally
{
scope.Dispose();
}
}
}
}
    When we comment out code as above no record is inserted into DummyTrail table neither the records from course,course2student or coursetostudent for node.js get deleted.Suppose there is no foreign key constraint then the delete will be simple as below

private static void DeleteNodeJs()
{
    //Data maping object to our database
    TestBedEntities DataContext = new TestBedEntities();
    Course NodeJsCourse = DataContext.Courses.FirstOrDefault(m => m.Name == "NODE js");

      if (NodeJsCourse != null)
     {
          DataContext.Courses.Remove(NodeJsCourse);
          DataContext.SaveChanges();
     }
}

Thanks & Happy Experimenting...

No comments:

Post a Comment