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.

LINQ-To-SQL

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;

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;

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()

{

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.