c# 2010 Updating SQL Server 2008 R2 with a DataGridView
Posted on 2012-08-14
I would like to be able to update changes I make in a DataGridView back to SQL Server. I can populate a DataGridView using a DataAdaptor. I can then Add rows to the DataGridView but I cannot seem to return those changes (UPDATE, ADD or DELETE) back to SQL Server. I have looked through an awful lot of articles but cannot seem to get the simple code that I need. I believe I may need a CommandBuilder but I just can't quite seem to make it happen.
I was hoping someone could give me some 'Update' button code for my project to make it work.
Here is what I have done so far:
SQL Server 2008 R2
I have a local SQL Server 2008 database called ‘MyWorkers’. In it I created a table called ‘tblPeople’ with a Primary Key and inserted three values:
CREATE TABLE tblPeople
[ID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](50) NULL,
CONSTRAINT [PK_tblPeople] PRIMARY KEY CLUSTERED ([ID] ASC)
Insert into tblPeople(FirstName) VALUES ('Adrian')
Insert into tblPeople(FirstName) VALUES ('Brenda')
Insert into tblPeople(FirstName) VALUES ('Craig')
In Visual Studio 2010 C# > Create a New Windows Form Application
1. Add a TextBox called ‘textBox1’. Set the text to ‘select * from tblPeople’.
2. Add a DataGridView called ‘dataGridView1’.
3. Add a button called ‘Button1’. Set the Text property to ‘Get Data’.
4. Add a button called ‘Button2’. Set the Text property to ‘Update’.
5. I then added code to ‘Button1’ to connect to SQL Server and retrieve the contents of the table into a dataset and then I assign that to dataGridView1. Here is the full code for that:
using System.Data.SqlClient; // Make sure this is included to be able to connect to SQL
public partial class Form1 : Form
private void Form1_Load(object sender, EventArgs e)
private void button1_Click(object sender, EventArgs e)
con = new SqlConnection();
con.ConnectionString = "Data Source=.;Initial Catalog=MyWorkers;Integrated Security=True";
ds = new DataSet();
da = new SqlDataAdapter(textBox1.Text, con);
dataGridView1.DataSource = ds.Tables;
private void button2_Click(object sender, EventArgs e)
// What do I put here ???
Could someone give me some Update code, please?