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
|
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
|
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.
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