A Walk-through with Linq to SQL Classes in Asp.Net

Introduction

Linq to SQL Classes template provides Object Relation Diagram among the sql tables, where sql tables are treated as an entity class.By using this features, user can access SQL database with Linq to SQL Classes technology.

I am giving a scenario that how to implement it in asp.net 3.5.

First of all Just Asp.net Project. and add Linq to Sql Classes template like this.

Right click on Project -> Add New Item -> Linq to Sql Classes Template.



Template is added to project.

  

Create Student table by using following script.

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Student](
    [Roll_number] [int] NOT NULL,
    [Fname] [varchar](50) NULL,
    [Lname] [varchar](50) NULL,
    [Teacher_id] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED
(
    [Roll_number] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Student]  WITH CHECK ADD  CONSTRAINT [FK_Student_Teacher] FOREIGN KEY([Teacher_id])
REFERENCES [dbo].[Teacher] ([ID])
GO
ALTER TABLE [dbo].[Student] CHECK CONSTRAINT [FK_Student_Teacher]

Create Teacher Table by using Following Script.
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Teacher](
    [ID] [varchar](50) NOT NULL,
    [Fname] [varchar](50) NULL,
    [Lname] [varchar](50) NULL,
 CONSTRAINT [PK_Teacher] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Teacher]  WITH CHECK ADD  CONSTRAINT [FK_Teacher_Teacher] FOREIGN KEY([ID])
REFERENCES [dbo].[Teacher] ([ID])
GO
ALTER TABLE [dbo].[Teacher] CHECK CONSTRAINT [FK_Teacher_Teacher]

Now, Just Drag and Drop Student and Teacher Table from Server Explorer.

Right Click  on Server Explorer-> Right Click on DataBase-> Click on Refresh-> Fill User Name and  Password of SQL. it will show all the table of your database.

 Now, Right Click on DataClasses1.dbml and open it.

Now, Drag and Drop Student And Teacher Table from Server Explorer on .dbml file.
 

Now, Design your User Interface, Like as.

    Here, SqlDataSource is used to bind Dropdownlist for Teachers.

    Create a Class Named as ComplainRepository.cs and create two method as      GetStudentDetals()   and InsertStudentDetail(Student student,Teacher teacher).


public object GetStudentDetals()
        {
           /*
            * Create a Object of Linq to sql Classes Entity.          
           */
            DataClasses1DataContext context = new DataClasses1DataContext();

           /*
            * Accessing the Data of Student and Teacher table using Linq.
            */
           var stdDetals = from s in context.Students
                            join t in context.Teachers
                                on s.Teacher_id equals t.ID
                            select new
                            {
                                Roll_number = s.Roll_number,
                                Fname = s.Fname,
                                Lname = s.Lname,
                                TeacherName = t.Fname
                            };
           
           /*
            * Returing Object.
            */
           return stdDetals;
        }


        public bool InsertStudentDetail(Student student,Teacher teacher)
        {
            bool retFlag = false;
            try
            {
                /*
                 * Create a Object of Linq to sql Classes Entity.    
                 */
                using (DataClasses1DataContext context = new DataClasses1DataContext())
                {
                    //Create Object of Teacher Entity.
                    Teacher objteacher = new Teacher();


                    //Assigning parameter teacher data to Teacher entity object.
                    objteacher.ID = teacher.ID;
                    objteacher.Fname = teacher.Fname;
                    objteacher.Lname = teacher.Lname;

                    /*
                     * Assign parameter student data to Student entity object.
                     * here, it's because Teacher entity class have a foreign relationship 

                     *with Student table.
                     */
                    objteacher.Students.Add(new Student()
                    {
                        Roll_number = student.Roll_number,
                        Fname = student.Fname,
                        Lname = student.Lname,
                        Teacher_id = student.Teacher_id
                    });

                    /*
                     * Insert data to Teacher and Student Entity.
                     */
                    context.Teachers.InsertOnSubmit(objteacher);
                  
                    /*
                     * Commit the Changes to Entities.
                     */
                    context.SubmitChanges();

                    /*
                     * Set return Flag.
                     */
                    retFlag = true;
                }
            }
            catch (Exception ex)
            {
                retFlag = false;
            }

            /*
             * return bool value.
             */
            return retFlag;
        }


On Click Event of Save Button Write this code.

 //On Save Click event.....
        protected void Button2_Click(object sender, EventArgs e)
        {
            /*
             * Create Object of Both Student and Teacer Entity.
             */
            Student student = new Student();
            Teacher teacher = new Teacher();

            /*
             * Assign values to Student Properties.
             */
            student.Roll_number = Convert.ToInt32(TextBox1.Text);
            student.Fname = TextBox2.Text;
            student.Lname = TextBox3.Text;
            student.Teacher_id = DropDownList1.SelectedValue.ToString();

            /*
             * Assign values to Teacher Properties.
             */
            teacher.ID = TextBox4.Text;
            teacher.Fname = TextBox5.Text;
            teacher.Lname = TextBox6.Text;


            /*
             * Create an Object of ComplainRepository.cs class.
             */
            ComplainRepository cr = new ComplainRepository();
           

            /*
             * Call Insert Function and pass both entity object as parmeter.
             */
            if (cr.InsertStudentDetail(student,teacher))
            {
                Response.Write("<SCRIPT LANGUAGE='JavaScript'> alert('Data Saved')</SCRIPT>");
            }
            else
            {
                Response.Write("<SCRIPT LANGUAGE='JavaScript'> alert('Data Not Saved')</SCRIPT>");
            }
        }



Now, Run your Project and fill the required entries.

    Click on Save Button and Your Data is now Saved.

   Check Your Sql Tables your entries has been saved.

   Happy Coading!