SELECT, INSERT, UPDATE, DELETE using LINQ to SQL in C#

This article offers a simple example of the use of LINQ TO SQL within Windows Forms in C# and how to perform Select, Insert, Update, and Delete operations on a Datatable.
This example is developed in Visual Studio 2010. First we add a DatagridView and  we declare the ConnectionString

SqlConnection con = new SqlConnection(WindowsFormsApplication12.Properties.Settings.Default.Database1ConnectionString);

Then we add a LINQ to SQL classe to our project:

And drag and drop the DataTable (Employee) from Server Explorer to the LINQ to SQL classe.

 

Now we perform LINQ to SQL statements:

1. SELECT statement:

 In Form Load event we add this code to display DataTable data:

private void Form1_Load(object sender, EventArgs e)
{
    DataClasses1DataContext dc = new DataClasses1DataContext(con);

    var selectQuery =
        from a in dc.GetTable<Employee>()
        select a;
    dataGridView1.DataSource = selectQuery;
}

2. INSERT statement:

In Insert button click event we add this code:

private void InsertButton_Click(object sender, EventArgs e)
{
    //create the new employee
    DataClasses1DataContext dc = new DataClasses1DataContext(con);
    Employee NewEmp = new Employee();
    NewEmp.Code = "2";
    NewEmp.Name = "bbb";
    NewEmp.Address = "bbb";

    //add the new employee to database
    dc.Employees.InsertOnSubmit(NewEmp);

    //save changes to database
    dc.SubmitChanges();

    //rebind datagridview to display the new employee
    var selectQuery =
        from a in dc.GetTable<Employee>()
        select a;
    dataGridView1.DataSource = selectQuery;
}

3. UPDATE statement:

Put this code in UpdateButton click event to perform updating database record:

private void UpdateButton_Click(object sender, EventArgs e)
{
    DataClasses1DataContext dc = new DataClasses1DataContext(con);

    //get employee to update
    Employee employee = dc.Employees.FirstOrDefault(emp => emp.Code.Equals("1"));

    //update employee
    employee.Name = "cccc";
    employee.Address = "cccc";

    //Save changes to Database.
    dc.SubmitChanges();

    //rebind datagridview to display the new employee
    var selectQuery =
        from a in dc.GetTable<Employee>()
        select a;
    dataGridView1.DataSource = selectQuery;
}

4. DELETE statement:

Add this code to DeleteButton click event:

private void DeleteButton_Click(object sender, EventArgs e)
{
    DataClasses1DataContext dc = new DataClasses1DataContext(con);

    //get the employee to delete
    Employee DeleteEmployee = dc.Employees.FirstOrDefault(emp => emp.Code.Equals("1"));

    //delete the employee
    dc.Employees.DeleteOnSubmit(DeleteEmployee);

    //save changes to database
    dc.SubmitChanges();

    //rebind datagridview to display the new employee
    var selectQuery =
        from a in dc.GetTable<Employee>()
        select a;
    dataGridView1.DataSource = selectQuery;
}

Follow this video to understand more: