Converting a DataTable to an INSERT script for SQL in C#

SO here is what you need to do;

You can do it one row at a time in to the database.

Mainly this method is just opening a connection to the access db, going through each row in the gridview datasource (if you create the datasource on the fly you might need to recreate the table at the beginning instead of pulling it from the gridview) and then pushing each row in to the table via an insert command in SQL.

Retrieve data

DataTable dtTable = Gridview1.datasource;

Create a connection string

string connString ="";
Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

Open a connection

SqlConnection cnx = new SqlConnection(connString);

To read each row in datatable a reiteration method
For Each iRow as Datarow in dtTable

Specify the SQL string

string strSQL = "INSERT INTO
TestTable(Col1,Col2) VALUES (" + iRow.item("Col1") + "," + iRow.item("Col1") + ")";

'Create a command object

SqlCommand cmd = new SqlCommand();
            cmd.CommandText = strSQL;
            cmd.Connection = cnx;

'Execute Insert

try
            {
                cnx.Open();
                cmd.ExecuteNonQuery();
            }

'Close the db connection
cnx.Close()

Or instead you can do a BULK Insert. By using SQLBulkCopy Class[^]