Updating multiple records from an import file

[Bill Schanks]

I have a winform app (VB 2005) that allows users to export data to
excel, make updates to the excel file and import the data from that
Excel file and update the database.

My question is: Is it best to do it this way, calling the update
stored procedure for every update? Or should I be loading this data
into a staging table, and if all goes well do the 'Real' Update. Or
put this into a data adapter and update from that? The application
will never update a huge amout of records, maybe 500 at the most. But
I would think this wouldn't scale when I have 5000 Records.

<<Snip>>
Using connection As New SqlConnection(g_sRCT_Conn)
'Get UserID
Dim uid As New SqlCommand("dbo.spoc_Get_UserID",
connection)
uid.CommandType = CommandType.StoredProcedure

'Open conn
connection.Open()

'Return Results
Dim uidResult As New SqlParameter("@UserID",
SqlDbType.Int)
uidResult.Direction = ParameterDirection.Output
uid.Parameters.Add(uidResult)

'Input parms
Dim sUID As String = g_sCurrUserDomain & "\" & g_sCurrUser
uid.Parameters.Add("@PrefID", SqlDbType.VarChar).Value =
sUID

'Exec and get user id
uid.ExecuteNonQuery()
Dim uidID As Integer = CInt(uidResult.Value)
uid.Dispose()

'Get total records to update
.Range("H2").Select()
.Selection.End(Excel.XlDirection.xlDown).Select()
Dim iTotalRecs As Integer = .ActiveCell.Row - 1
.Range("H2").Select()

'Loop thru RecordIDs and get notes
Dim dtUpdate As Date = Now()
Do While .ActiveCell.Text <""
If .ActiveCell.Offset(0, iNOTES_OFFSET).Text <""
Then
'Update records
Dim u As New SqlCommand
("dbo.spoc_ev_ImportUpdate", connection)
u.CommandType = CommandType.StoredProcedure

'Return Value
Dim uResult As New SqlParameter("@Result",
SqlDbType.Int)
uResult.Direction = ParameterDirection.Output
u.Parameters.Add(uResult)

'Input Parms
u.Parameters.Add("@ItemID", SqlDbType.Int).Value =
CInt(.ActiveCell.Text)
u.Parameters.Add("@UserID", SqlDbType.Int).Value =
uidID
u.Parameters.Add("@UpdateDate",
SqlDbType.DateTime).Value = dtUpdate
u.Parameters.Add("@Notes",
SqlDbType.VarChar).Value = .ActiveCell.Offset(0, iNOTES_OFFSET).Text

'Update
u.ExecuteNonQuery()
If Not CInt(uResult.Value) = 0 Then
MessageBox.Show("Error updating RecordID "
& .ActiveCell.Text, _
g_sApp_Name, MessageBoxButtons.OK,
MessageBoxIcon.Error)
iCount = -1
bError = True
Else
iCount += 1
Me.ssStatus.Text = "Updating ... " & iCount _
& " of " & iTotalRecs & " Records"
Application.DoEvents()
End If
End If
.ActiveCell.Offset(1, 0).Select()
Loop
End Using
<<Snip>>

[Rich P]

with all due respect - your operation of transferring data to excel from
your VB2005 app for updating and then returning that data back to your
DB (and I assume it is a sql server DB) seems a little redundant.

I would retrieve data from your DB using a sqlDataAdapter and populate a
local table within your app - display that data in a datagridview
control and perform the update(s) in the datagridview control - then
submit the updates back to the server. Or you could submit updates as
they occur in the datagridview.

Example:

'--Form level vars
dim da As New SqlDataAdapter, ds As New Dataset
dim conn as New SqlConnection
conn.ConnectionString = "Data
Source=yourServer;Database=yourDB;Integrated Security=True"
da.SelectCommand = New sqlCommand
da.SelectCommand.Connection = conn

da.Update = New SqlCommand
da.Update.Connection = conn

Private sub SelectData()
da.SelectCommand.CommandText = "Select * from tbl1 where something =
somethingelse"
da.Fill(ds,"localTbl")
datagridview1.datasource = ds.Tables("localtbl")
...
End Sub

Private Sub UpdateData()
da.UpdateCommand.Parameters.Add("@prmID",sqlDBType .Int, 4, ID)
da.UpdateCommand.parameters.Add(...)
...
da.UpdateComnmand.CommandText = "Update tbl1 Set fld1 = @prm1, fld2 =
@prm2,..., Where ID = @prmID"

da.Update(ds, "localtbl")
End Sub

Private Sub datagridview1_RowLeaveEvent(...) Handles...
'--may have to force updates on the local table "localTbl" with code
dim drF() as dataRow = ds.Tables("localTbl").Select("ID = " &
datagridview1.Rows(e.RowIndex).Cells("ID").Value.T oString
For Each row As DataRow in drF
row.BeginEdit
row("fld1") = datagridview1.Rows(e.rowIndex).Cells("fld1").Value
..
Row.EndEdit
Next

UpdateData()
End Sub
Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Bill Schanks]

The app does bring the data up in a data grid view, and can be updated
directly in the application. One of the requirements is the ability to
export to Excel and bring those updates back via this import. I fought
the feature, but I did not win.

Also, I have read that having SQL in the code is a no-no for
production applications. Rather it should all be in stored procedures.
And in our shop it is much better to have all that control in the
procedures as changes to a DB Server are much simpler than re-
deploying the application.

On Nov 19, 4:56*pm, Rich P <[e-mail]:

with all due respect - your operation of transferring data to excel from
your VB2005 app for updating and then returning that data back to your
DB (and I assume it is a sql server DB) seems a little redundant.

I would retrieve data from your DB using a sqlDataAdapter and populate a
local table within your app - display that data in a datagridview
control and perform the update(s) in the datagridview control - then
submit the updates back to the server. *Or you could submit updates as
they occur in the datagridview.

Example:

'--Form level vars
dim da As New SqlDataAdapter, ds As New Dataset
dim conn as New SqlConnection
conn.ConnectionString = "Data
Source=yourServer;Database=yourDB;Integrated Security=True"
da.SelectCommand = New sqlCommand
da.SelectCommand.Connection = conn

da.Update = New SqlCommand
da.Update.Connection = conn

Private sub SelectData()
da.SelectCommand.CommandText = "Select * from tbl1 where something =
somethingelse"
da.Fill(ds,"localTbl")
datagridview1.datasource = ds.Tables("localtbl")
..
End Sub

Private Sub UpdateData()
da.UpdateCommand.Parameters.Add("@prmID",sqlDBType .Int, 4, ID)
da.UpdateCommand.parameters.Add(...)
..
da.UpdateComnmand.CommandText = "Update tbl1 Set fld1 = @prm1, fld2 =
@prm2,..., Where ID = @prmID"

da.Update(ds, "localtbl")
End Sub

Private Sub datagridview1_RowLeaveEvent(...) Handles...
* *'--may have to force updates on the local table "localTbl" with code
* *dim drF() as dataRow = ds.Tables("localTbl").Select("ID = " &
datagridview1.Rows(e.RowIndex).Cells("ID").Value.T oString
* *For Each row As DataRow in drF
* * *row.BeginEdit
* * *row("fld1") = datagridview1.Rows(e.rowIndex).Cells("fld1").Value
* * *..
* * *Row.EndEdit
* *Next

* *UpdateData()
End Sub

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

[Rich P]

Not completely clear on your effort here. I have several apps which
write data to Excel or read data from Excel. And all of my apps use a
combination of stored procs and inline tsql code (meaning in the app
itself). The inline stuff is just easier to debug - or I have the app
write the tsql based on user input. As for deploying apps - 'Click
Once' is how I deploy all my apps. I can make up to the minute updates
(a user needs something little - turn around time - 1 minute) on an app
and deploy it. The user has the new feature within the minute (I
spoiled the people over at my place).

So - is your problme in writing data to excel from your app or reading
data from excel to your app? Or is it you read the data from excel and
need to push it back to the server? Let me know which of these, and I
am sure I could provide you with a sample how to do it.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Bill Schanks]

The battle over deploying new app versions is frustrating. IT won't
allow self updating (click once or roll your own). They want all
changes to go thru testing and sign-off (I can see there point, but it
is frustrating and can take months).

So I try as much as possible to stay away from inline sql in the app.

And I don't really have a problem, just a question if my way of
importing is as efficient as it can be. I am pushing it to the server
with a stored procedure, but calling that procedure for every record
that is read from the Excel file.

On Nov 20, 12:56*pm, Rich P <[e-mail]:

Not completely clear on your effort here. *I have several apps which
write data to Excel or read data from Excel. *And all of my apps use a
combination of stored procs and inline tsql code (meaning in the app
itself). *The inline stuff is just easier to debug - or I have the app
write the tsql based on user input. *As for deploying apps - 'Click
Once' is how I deploy all my apps. *I can make up to the minute updates
(a user needs something little - turn around time - 1 minute) on an app
and deploy it. *The user has the new feature within the minute (I
spoiled the people over at my place). *

So - is your problme in writing data to excel from your app or reading
data from excel to your app? *Or is it you read the data from excel and
need to push it back to the server? *Let me know which of these, and I
am sure I could provide you with a sample how to do it.

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

[Rich P]

here is a technique I use for reading from Excel using ADO.Net and then
pushing the data to the Server

------------------------------------------------

Private Sub Button2_Click(...) Handles Button2.Click
Dim daOle As OleDbDataAdapter, conn As OleDbConnection
conn = New OleDbConnection

'--connection string to the Excel file

conn.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;data
source=C:\somedir\Test1.xls;Extended Properties=""Excel 8.0;HDR=YES"""

daOle = New OleDbDataAdapter

daOle.SelectCommand = New OleDbCommand
daOle.SelectCommand.Connection = conn
'--put the Excel sheet name in brackets followed by $
daOle.SelectCommand.CommandText = "Select * From [Sheet1$]"
daOle.Fill(ds, "tblExcel")

'--da here is a sqlDataAdapter with a sqlConnection
'--tblEmails is a table on the Server DB
'--get the structure of tblEmails for the local table

Application.DoEvents()
da.SelectCommand.CommandText = "Select * From tblEmails"
da.Fill(ds, "tblEmails_Server")

'--set up the Insert command to push the data retrieved
'--from the Excel file to the Server table - tblEmails

da.InsertCommand.CommandText = "Insert Into tblEmails(RecordID, CoID,
SubscrID, Name) " _
& "Select @RecordID, @CoID, @SubscrID, @Name"
da.InsertCommand.Parameters.Clear()

'--add parameters for the Insert Statement

da.InsertCommand.Parameters.Add("@RecordID", SqlDbType.Int, 4,
"RecordID")
da.InsertCommand.Parameters.Add("@CoID", SqlDbType.VarChar, 50, "CoID")
da.InsertCommand.Parameters.Add("@SubscrID", SqlDbType.VarChar, 50,
"SubscrID")
da.InsertCommand.Parameters.Add("@Name", SqlDbType.VarChar, 50, "Name")

'--use a dataTableReader for the data push to the server
'--tblEmails_Server is the local app table that will be
'--the vehicle for pushing the data to the server

Dim reader As DataTableReader = ds.Tables("tblExcel").CreateDataReader
ds.Tables("tblEmails_Server").Load(reader, LoadOption.Upsert)

'--this is where the actual data push takes place
da.Update(ds, "tblEmails_Server")

dgrv1.DataSource = ds.Tables("tblExcel")
curMgr = CType(Me.BindingContext(ds.Tables("tblExcel")),
CurrencyManager)
tssL2.Text = (curMgr.Position + 1).ToString
tssL3.Text = curMgr.Count.ToString

End Sub

-------------------------------------------------

Rich

*** Sent via Developersdex http://www.developersdex.com ***