Link to home
Start Free TrialLog in
Avatar of Ryan
RyanFlag for United States of America

asked on

Write Unbound DGV to DB

I have code calculating and displaying results to a DataGridView which I'd like to save to database. I'm finding solutions to update results that were populated via a DataAdapter, but this data wasn't queried.

I may have as many as 1000 rows, so I'd rather not Insert line by line (VPN connection can be terrible).  The only bulk method I know for SQL Server 2005 is to  
INSERT INTO MyTable (Field1, Field2)
SELECT Row1Val1, Row1Val2
UNION ALL SELECT Row2Val1, Row2Val2
UNION ALL SELECT Row3Val1, Row3Val2

I don't need to check for duplicates or any validation, just shove it all into a table.
Avatar of Member_2_861731
Member_2_861731
Flag of Canada image

Since you're in SQL 2k5 you can't use table parameters (yet), so I think you're right.
Probably your best bet would be to dynamically create a string with your insert query.

First create the INSERT part, something like:

string strQuery = "INSERT INTO myTable(Field1, ... , FieldN)

Then loop through all the rows of your dgv and append to that string each and every one of your inserts.

Hope it helps.
Avatar of Ryan

ASKER

I was hoping there was some way to utilize the DataAdapter.

From the lack of response, seems that StringBuilder probably is the best solution.
ASKER CERTIFIED SOLUTION
Avatar of Member_2_861731
Member_2_861731
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Ryan

ASKER

That worked with some modifications. I'm hoping it generated a bulk insert command, I don't know how to test for that.  Here's the final code.

		Dim ProjectMain, ProjectSub As String
		ProjectMain = InputBox("Enter Main Project Name", "Main Project")
		If ProjectMain = "" Then Exit Sub
		ProjectSub = InputBox("Enter Sub Project Name", "Sub Project")
		If ProjectSub = "" Then Exit Sub

		Dim dr As DataRow
		Dim cnn As SqlConnection = Globals.cnnFACTS
		Using da As New SqlDataAdapter("SELECT * FROM tblTempUsed WHERE ID = -1", cnn)
			Using dt As New DataTable
				da.Fill(dt)

				For Each r As DataGridViewRow In DataGridView1.Rows
					dr = dt.NewRow()
					With dr
						.Item("ProjectMain") = ProjectMain
						.Item("ProjectSub") = ProjectSub
						.Item("DiskNo") = r.Cells("DiskNo").Value
						.Item("BatchNo") = r.Cells("BatchNo").Value
					End With

					dt.Rows.Add(dr)
				Next

				Dim sqlCB As New SqlCommandBuilder(da)

				da.Update(dt)
			End Using
			cnn.Close()
		End Using

Open in new window

Don't quote me on this, but I don't think it does a bulk insert.
What the data adapter does, is that it executes the InsertCommand that you specify for each row that is marked for insertion.

Glad it helped.