Today in this article I will show you how to in insert, update, delete, display data in LINQ To SQL through DataGridView.
Start
1.Open your Visual Studio and make a new console project with any name.
2.Then open Server Explorer and make a new database with some name and make a new table. Add some columns to the table.
3.Then open your project and go to to Solution Explorer and right-click on the project and then click Add new item.
4.There search LINQ-To-SQL and add this file and press the OK button.
5.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
}
