Insert, Update, Delete Data in LINQ to SQL Through DataGridView

Today in this article I will show you how to in insert, update, delete, display data in LINQ To SQL through DataGridView.

Start

• Open your Visual Studio and make a new console project with any name.
• Then open Server Explorer and make a new database with some name and make a new table. Add some columns to the table.
• Then open your project and go to to Solution Explorer and right-click on the project and then click Add new item.
• There search LINQ-To-SQL and add this file and press the OK button.
• Then you will see a blank file. On that file you will drag your table in the SQL Server database file onto the LINQ-To-SQL .dbml file extension.

 

Insert

private void SaveButton_Click(object sender, EventArgs e)

{

    StudentDataClasses1DataContext SDCD1 = new StudentDataClasses1DataContext(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=c:\users\ehtesham mehmood\documents\visual studio 2012\Projects\FP1\FP1\abc.mdf;Integrated Security=True;Connect Timeout=30");

    StudentInfo SI = new StudentInfo();

    int rowindex = dataGridView1.CurrentRow.Index; // here rowindex will get through currentrow property of datagridview.

    SI.Id = Convert.ToInt32(dataGridView1.Rows[rowindex].Cells[0].Value);

    SI.Name = Convert.ToString(dataGridView1.Rows[rowindex].Cells[1].Value);

    SI.Marks = Convert.ToInt32(dataGridView1.Rows[rowindex].Cells[2].Value);

    SI.Grade = Convert.ToString(dataGridView1.Rows[rowindex].Cells[3].Value);

    SDCD1.StudentInfos.InsertOnSubmit(SI);//InsertOnSubmit queries will automatic call thats the data context class handle it.

    SDCD1.SubmitChanges();

    MessageBox.Show("Saved");

    rowindex = 0;

} 

Delete

private void button1_Click(object sender, EventArgs e)

{

    int iid = 0;

    StudentDataClasses1DataContext SDCD1 = new StudentDataClasses1DataContext(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=c:\users\ehtesham mehmood\documents\visual studio 2012\Projects\FP1\FP1\abc.mdf;Integrated Security=True;Connect Timeout=30");

    StudentInfo SI = new StudentInfo();

    int rowindex = dataGridView1.CurrentRow.Index; // here rowindex will get through currentrow property of datagridview.

    iid = Convert.ToInt32(dataGridView1.Rows[rowindex].Cells[0].Value);

    var delete = from p in SDCD1.StudentInfos

    where p.Id == iid// match the ecords.

    select p;

    SDCD1.StudentInfos.DeleteAllOnSubmit(delete);// DeleteAllOnSubmit function will call and queries will automatic call thats the data context class handle it.

    SDCD1.SubmitChanges();

    // SI = SDCD1.StudentInfos.Single(c => c.Id == iid);

    rowindex = 0;

    MessageBox.Show("deleted");

    Refresh();

}

Update

private void button2_Click(object sender, EventArgs e)

{

    int iid = 0;

    StudentDataClasses1DataContext SDCD1 = new StudentDataClasses1DataContext(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=c:\users\ehtesham mehmood\documents\visual studio 2012\Projects\FP1\FP1\abc.mdf;Integrated Security=True;Connect Timeout=30");

    StudentInfo SI = new StudentInfo();

    int rowindex = dataGridView1.CurrentRow.Index; // here rowindex will get through currentrow property of datagridview.

    iid = Convert.ToInt32(dataGridView1.Rows[rowindex].Cells[0].Value);

    var update = from s1 in SDCD1.StudentInfos

    where s1.Id == iid

    select s1;

    foreach (var v in update)

    {

        v.Id = Convert.ToInt32(dataGridView1.Rows[rowindex].Cells[0].Value);

        v.Name = Convert.ToString(dataGridView1.Rows[rowindex].Cells[1].Value);

        v.Marks = Convert.ToInt32(dataGridView1.Rows[rowindex].Cells[2].Value);

        v.Grade = Convert.ToString(dataGridView1.Rows[rowindex].Cells[3].Value);

        SDCD1.SubmitChanges(); // here will submitchanges function call and queries will automatic call.

    }

    MessageBox.Show("Updated");

    Refresh();// refresh the data gridview.

}

Display/Refresh

private void Refresh()

{

    StudentDataClasses1DataContext SDCD1 = new StudentDataClasses1DataContext(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=c:\users\ehtesham mehmood\documents\visual studio 2012\Projects\FP1\FP1\abc.mdf;Integrated Security=True;Connect Timeout=30");

    StudentInfo SI = new StudentInfo();

    var query = from q in SDCD1.StudentInfos

    select q;

    dataGridView1.DataSource = query;// Attaching the all data with Datagridview

}

I have also attached the source code so you can download it.