The concept of Table Per Type (TPT) is that For every
entity it will create new table. For example in below screen there are 3
different entity (Student, Collage Student, School Student) it will create 3
new tables for each entity.
In Table Per Hierarchy we saw that one database table
store all the data for all the entity types.
With this there
is a problem that when we store Collage student entity data into table
then the columns “CollageStudentName” and “CollageStudentbranch” will be left
NULL and Same goes to School student.
For removing
this problem we use Table Per Type inheritance In this we will create 3
tables for each entity.
3 Tables name
is “Student table”, “CollageStudent Table”, “SchoolStudent table” The common
thing in all the tables is that “StudentID” will be Same.
This StudentID
will be primary key in Student table and will be foreign key in
CollageStudent and SchoolStudent table.
|
Create Table Student
(
StudentID int primary key,
FirstName nvarchar(50),
LastName nvarchar(50),
Gender nvarchar(50),
)
GO
Create Table CollageStudents
(
StudentID int foreign key references
Student(StudentID) not null,
CollageName nvarchar(50),
CollageBranch nvarchar(50)
)
GO
Create Table SchoolStudents
(
StudentID int foreign key references
Student(StudentID) not null,
SchoolName nvarchar(50),
SchoolClass nvarchar(50)
)
GO
|
|
Insert into Student values (1, 'Munesh', 'Sharma','Male')
Insert into Student values (2, 'Rahul', 'Sharma','Male')
Insert into Student values (3, 'Sara', 'vilium','Female')
Insert into Student values (4, 'Rani', 'hash','Female')
Insert into Student values (5, 'XYZ', 'ABC','Female')
Insert into Student values (6, 'Anshuman', 'EFG','Male')
Insert into CollageStudents values
(1, 'VIT','IT')
Insert into CollageStudents values
(4, 'MIT','ECE')
Insert into CollageStudents values
(6, 'BTC','Mechenical')
Insert into SchoolStudents values
(2, 'KVM','Seven')
Insert into SchoolStudents values
(3,'Aadharsh','Eight')
Insert into SchoolStudents values
(5, 'Ravat','Tenth')
|
Now go to your application and right click on solution
explorer and select Ado.Net Entity data Modal and select your table and generate
Entity , 3 following entity will generate like this.
Now Delete the association b/w Student entity and
CollageStudent entity and this also automatically delete CollageStudent
navigation property from student entity and student navigation property from
CollageStudent entity.
Same thing you do with SchoolStudent entity.
Now add the inheritance relationship b/w Student entity
and collageStudent entity.
(1)Right click on the
designer surface and click on Add-Inheritance option.
(2)Select Student at base entity and collageStudent as the derived entity.
(3)When you will click on
inheritance your screen will look like that and you put base entity and derived
entity.
(4)Same you do with Student
entity and SchoolStudent Entity means generate inheritance for these entities.
(5)After creating inheritance
b/w these entities your entity modal will look like this.
(6)Lets Understand this with
an example.
|
<div style="font-family:
Arial">
<asp:Button ID="
Button1" runat="server" Text="All Student Information"
onclick="Button1_Click" />
<asp:Button ID="
Button2" runat="server" Text="Collage Student Information"
onclick="Button2_Click" />
<asp:Button ID="
Button3" runat="server" Text="School Student Information"
onclick="Button3_Click" />
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
</div>
|
|
protected void Button1_Click(object sender, EventArgs e)
{
GridView1.DataSource =
ConvertListToDataTable(
studentDBContext.Students.ToList());
GridView1.DataBind();
}
|
|
protected void Button2_Click(object sender, EventArgs e)
{
GridView1.DataSource =
studentDBContext.Students
.OfType<CollageStudent>().ToList();
GridView1.DataBind();
}
|
|
protected void Button3_Click(object sender, EventArgs e)
{
GridView1.DataSource =
studentDBContext.Students
.OfType<SchoolStudent>().ToList();
GridView1.DataBind();
}
|
|
private DataTable ConvertListToDataTable (List<Student> students)
{
DataTable dt = new DataTable();
dt.Columns.Add("ID");
dt.Columns.Add("FirstName");
dt.Columns.Add("LastName");
dt.Columns.Add("Gender");
dt.Columns.Add("SchoolName");
dt.Columns.Add("SchoolClass");
dt.Columns.Add("CollageName");
dt.Columns.Add("CollageBranch");
dt.Columns.Add("Type");
foreach (Student _student in students)
{
DataRow dr = dt.NewRow();
dr["ID"] = _student.ID;
dr["FirstName"] = _student.FirstName;
dr["LastName"] = _student.LastName;
dr["Gender"] = _student.Gender;
if (_student is CollageStudent)
{
dr["CollageName"] = ((CollageStudent) _student). CollageStudentName;
dr["CollageBranch"] = ((CollageStudent) _student).CollageStudentBranch;
dr["Type"] = "CollageStudent";
}
else
{
dr["SchoolName "] = ((SchoolStudent) _student).SchoolStudentName;
dr["SchoolClass
"] = ((SchoolStudent) _student).SchoolStudentBranch;
dr["Type"] = "SchoolStudent";
}
dt.Rows.Add(dr);
}
return dt;
}
}
}
|
Run your application and see the output.
