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.
LVL 13
RyanProject Engineer, ElectricalAsked:
Who is Participating?
 
LIONKINGConnect With a Mentor Commented:
Check this out... It might give you another option:

http://stackoverflow.com/questions/1631054/using-sqldataadapter-to-insert-a-row
0
 
LIONKINGCommented:
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.
0
 
RyanProject Engineer, ElectricalAuthor Commented:
I was hoping there was some way to utilize the DataAdapter.

From the lack of response, seems that StringBuilder probably is the best solution.
0
 
RyanProject Engineer, ElectricalAuthor Commented:
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

0
 
LIONKINGCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.