Adding rows from a datagridview to an sql database

I have a DataGridView which I populate with variables开发者_运维技巧, however I would then Like to add each row to a sql database table.

At the moment I am trying this code when a button is pressed, however I am a bit stuck on what my values should be.... here is my code... Am I on the right track, even though I have just thrown code together.

///////// Add to DataGridView Rows to Table ///////
        foreach( DataGridViewRow row in dgv.Rows){
        SqlCommand Insert = new SqlCommand(@"insert into TestTable(ID, FirstName, LastName) values ("dgv.Cells[1].Value.ToString(), dgv.Cells[2].Value.ToString(),dgv.Cells[3].Value.ToString());
        }

you're on the right track but there are some problems with it.

1. You're exposed to a SQL injection attack. So you should use a parameterized query instead

2. If you change the order of the columns in your grid you'd need to update SQLCommand code. Instead you should loop through your DataSource which should have nice property names.

3. You need to set up a connection and add it to the Command and open and close it (the using statement is a good way to manage the connection lifetime)

4. Creating a SQL Command and setting up its connection is still not enough, you need to execute it as well with Insert.ExecuteNonQuery

5. Also you probably should create your Command outside the loop and the just set the parameters on each iteration rather than creating it each time.

Helpful links

• How to: Bind Data to the Windows Forms DataGridView Control that uses a DataSet and DataAdpater

• How to: Bind Objects to Windows Forms DataGridView Controls Binds a DataGridView to an object data source

• How to: Execute a Parameterized Query describes how to avoid SQL injection attacks using Parameterized queries.