Wednesday, 18 March 2015

Table splitting in entity framework with code first approach


 Table splitting we split one table into two tables.

Step 1: Create a new web application And install Entity framework.

Step 2: Add a class to the project. Give the Name as Student.cs. Write Below code.
namespace EFTableSplitting
{
    public class Student
    {
        public int StudentID { getset; }
        public string FirstName { getset; }
        public string LastName { getset; }
        public string Gender { getset; }

        public StudentContactDetail StudentContactDetail { getset; }
    }
}


Step 3: Add a another class to the project And give the  Name as StudentContactDetail.cs. And write the below code.
namespace EFTableSplitting
{
    public class StudentContactDetail
    {
        public int StudentID { getset; }
        public string Email { getset; }
        public string Mobile { getset; }
        

        public Student Student { getset; }
    }
}


Step 4: Add a class to the project For the DBContext and give the name  it as StudentDBContext.cs. And Write below code.
using System.Data.Entity;
namespace EFTableSplittings
{
    public class StudentDBContext : DbContext
    {
        public DbSet<Student> Student { getset; }

        protected override void OnModelCreating(DbModelBuilder _Modal)
        {
           _Modal.Entity<Student>()
                .HasKey(c => c.StudentID)
                .ToTable("Student");

           _Modal.Entity<StudentContactDetail>()
                .HasKey(c => c.StudentID)
                .ToTable("Student");

           _Modal.Entity<Student>()
                .HasRequired(c => c.StudentContactDetail)
                .WithRequiredPrincipal(c => c.Student);
        }
    }
}


Step 5: Add the database connection string in web.config file.
<connectionStrings>
  <add name="StudentDBContext"
      connectionString="server=.; database=entitysplitting; integrated security=SSPI;"
      providerName="System.Data.SqlClient" />
</connectionStrings>


Step 6: Now Add a web form And pest below code in HTML code portion.
    <asp:Button ID="FullDataWithContactDetail" runat="server" Text="GetStudent Data with contact detail"
        onclick="Button1_Click" />
    <br />
    <asp:Button ID="Button2" runat="server" Text="Get Student Data"
        onclick="Button2_Click" />
    <asp:GridView ID="GridView1" runat="server">
    </asp:GridView>
</div>
</div>


Step 7: Write the below code in code behind section
public partial class WebForm1 : System.Web.UI.Page
{
    private DataTable GetStudentData()
    {
        StudentDBContext studentDBContext = new StudentDBContext();
        List<Student> students = studentDBContext.Student.ToList();

        DataTable dt = new DataTable();
        DataColumn[] columns = { new DataColumn("StudentID"),
                                 new DataColumn("FirstName"),
                                 new DataColumn("LastName"),
                                 new DataColumn("Gender")};

        dt.Columns.AddRange(columns);

        foreach (Student student in students)
        {
            DataRow dr = dt.NewRow();
            dr["StudentID"] = student.StudentID;
            dr["FirstName"] = student.FirstName;
            dr["LastName"] = student.LastName;
            dr["Gender"] = student.Gender;

            dt.Rows.Add(dr);
        }

        return dt;
    }

    private DataTable FullDataWithContactDetails()
    {
        StudentDBContext studentDBContext = new StudentDBContext();
        List<Student> students = studentDBContext.Student
            .Include("StudentContactDetail").ToList();

        DataTable dt = new DataTable();
        DataColumn[] columns = { new DataColumn("StudentID"),
                                 new DataColumn("FirstName"),
                                 new DataColumn("LastName"),
                                 new DataColumn("Gender"),
                                 new DataColumn("Email"),
                                 new DataColumn("Mobile"),
                                 
        dt.Columns.AddRange(columns);

        foreach (Student student in students)
        {
            DataRow dr = dt.NewRow();
            dr["EmployeeID"] = student.StudentID;
            dr["FirstName"] = student.FirstName;
            dr["LastName"] = student.LastName;
            dr["Gender"] = student.Gender;
            dr["Email"] = student.StudentContactDetail.Email;
            dr["Mobile"] = student.StudentContactDetail.Mobile;

            dt.Rows.Add(dr);
        }

        return dt;
    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        
            GridView1.DataSource = FullDataWithContactDetails();
       
        GridView1.DataBind();
    }
  protected void Button2_Click(object sender, EventArgs e)
    {
        GetStudentData();


     }

 }


At this point, run the application. entitysplitting database and Student table should be created by the entity framework.
Step 8: Insert test data using the following SQL script
Insert into Students values ('Munesh', 'Sharma', Male',m@g.com,5555555555)
Insert into Students values ('Rahul', 'Sharma', 'Male',R@g.com,333333333)
Insert into Students values ('Sara', 'vilium', 'Female',S@g.com,111111111)
Insert into Students values ('Mark', 'hash', 'Female',M1@g.com,2222222222)
Insert into Students values ('ABC', 'EFG', 'Male',A@g.com,6666666666)



Step 9:  Run your application and check your data that it is working properly or not.

No comments:

Post a Comment

Paging in html table using javascript

I demonstrated how to use HTML tables on the client for a very simple client-side paging solution. I have heard from several people who p...