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_UserI D",
connection)
uid.CommandType = CommandType.Sto redProcedure

'Open conn
connection.Open ()

'Return Results
Dim uidResult As New SqlParameter("@ UserID",
SqlDbType.Int)
uidResult.Direc tion = ParameterDirect ion.Output
uid.Parameters. Add(uidResult)

'Input parms
Dim sUID As String = g_sCurrUserDoma in & "\" & g_sCurrUser
uid.Parameters. Add("@PrefID", SqlDbType.VarCh ar).Value =
sUID

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

'Get total records to update
.Range("H2").Se lect()
.Selection.End( Excel.XlDirecti on.xlDown).Sele ct()
Dim iTotalRecs As Integer = .ActiveCell.Row - 1
.Range("H2").Se lect()

'Loop thru RecordIDs and get notes
Dim dtUpdate As Date = Now()
Do While .ActiveCell.Tex t <""
If .ActiveCell.Off set(0, iNOTES_OFFSET). Text <""
Then
'Update records
Dim u As New SqlCommand
("dbo.spoc_ev_I mportUpdate", connection)
u.CommandType = CommandType.Sto redProcedure

'Return Value
Dim uResult As New SqlParameter("@ Result",
SqlDbType.Int)
uResult.Directi on = ParameterDirect ion.Output
u.Parameters.Ad d(uResult)

'Input Parms
u.Parameters.Ad d("@ItemID", SqlDbType.Int). Value =
CInt(.ActiveCel l.Text)
u.Parameters.Ad d("@UserID", SqlDbType.Int). Value =
uidID
u.Parameters.Ad d("@UpdateDate" ,
SqlDbType.DateT ime).Value = dtUpdate
u.Parameters.Ad d("@Notes",
SqlDbType.VarCh ar).Value = .ActiveCell.Off set(0, iNOTES_OFFSET). Text

'Update
u.ExecuteNonQue ry()
If Not CInt(uResult.Va lue) = 0 Then
MessageBox.Show ("Error updating RecordID "
& .ActiveCell.Tex t, _
g_sApp_Name, MessageBoxButto ns.OK,
MessageBoxIcon. Error)
iCount = -1
bError = True
Else
iCount += 1
Me.ssStatus.Tex t = "Updating ... " & iCount _
& " of " & iTotalRecs & " Records"
Application.DoE vents()
End If
End If
.ActiveCell.Off set(1, 0).Select()
Loop
End Using
<<Snip>>

[Rich P]

Re: Updating multiple records from an import file

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.Connection String = "Data
Source=yourServ er;Database=you rDB;Integrated Security=True"
da.SelectComman d = New sqlCommand
da.SelectComman d.Connection = conn

da.Update = New SqlCommand
da.Update.Conne ction = conn

Private sub SelectData()
da.SelectComman d.CommandText = "Select * from tbl1 where something =
somethingelse"
da.Fill(ds,"loc alTbl")
datagridview1.d atasource = ds.Tables("loca ltbl")
...
End Sub

Private Sub UpdateData()
da.UpdateComman d.Parameters.Ad d("@prmID",sqlD BType.Int, 4, ID)
da.UpdateComman d.parameters.Ad d(...)
...
da.UpdateComnma nd.CommandText = "Update tbl1 Set fld1 = @prm1, fld2 =
@prm2,..., Where ID = @prmID"

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

Private Sub datagridview1_R owLeaveEvent(.. .) Handles...
'--may have to force updates on the local table "localTbl" with code
dim drF() as dataRow = ds.Tables("loca lTbl").Select(" ID = " &
datagridview1.R ows(e.RowIndex) .Cells("ID").Va lue.ToString
For Each row As DataRow in drF
row.BeginEdit
row("fld1") = datagridview1.R ows(e.rowIndex) .Cells("fld1"). Value
..
Row.EndEdit
Next

UpdateData()
End Sub


Rich

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

[Bill Schanks]

Re: Updating multiple records from an import file

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 <rpng...@aol.co mwrote:

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.Connection String = "Data
Source=yourServ er;Database=you rDB;Integrated Security=True"
da.SelectComman d = New sqlCommand
da.SelectComman d.Connection = conn
>
da.Update = New SqlCommand
da.Update.Conne ction = conn
>
Private sub SelectData()
da.SelectComman d.CommandText = "Select * from tbl1 where something =
somethingelse"
da.Fill(ds,"loc alTbl")
datagridview1.d atasource = ds.Tables("loca ltbl")
..
End Sub
>
Private Sub UpdateData()
da.UpdateComman d.Parameters.Ad d("@prmID",sqlD BType.Int, 4, ID)
da.UpdateComman d.parameters.Ad d(...)
..
da.UpdateComnma nd.CommandText = "Update tbl1 Set fld1 = @prm1, fld2 =
@prm2,..., Where ID = @prmID"
>
da.Update(ds, "localtbl")
End Sub
>
Private Sub datagridview1_R owLeaveEvent(.. .) Handles...
   '--may have to force updates on the local table "localTbl" with code
   dim drF() as dataRow = ds.Tables("loca lTbl").Select(" ID = " &
datagridview1.R ows(e.RowIndex) .Cells("ID").Va lue.ToString
   For Each row As DataRow in drF
     row.BeginEdit
     row("fld1") = datagridview1.R ows(e.rowIndex) .Cells("fld1"). Value
     ..
     Row.EndEdit
   Next
>
   UpdateData()
End Sub
>
Rich
>
*** Sent via Developersdexht tp://www.developersd ex.com***

[Rich P]

Re: Updating multiple records from an import file

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]

Re: Updating multiple records from an import file

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 <rpng...@aol.co mwrote:

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 Developersdexht tp://www.developersd ex.com***

[Rich P]

Re: Updating multiple records from an import file

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 OleDbDataAdapte r, conn As OleDbConnection
conn = New OleDbConnection

'--connection string to the Excel file

conn.Connection String = "provider=Micro soft.Jet.OLEDB. 4.0;data
source=C:\somed ir\Test1.xls;Ex tended Properties=""Ex cel 8.0;HDR=YES"""

daOle = New OleDbDataAdapte r

daOle.SelectCom mand = New OleDbCommand
daOle.SelectCom mand.Connection = conn
'--put the Excel sheet name in brackets followed by $
daOle.SelectCom mand.CommandTex t = "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.DoE vents()
da.SelectComman d.CommandText = "Select * From tblEmails"
da.Fill(ds, "tblEmails_Serv er")

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

da.InsertComman d.CommandText = "Insert Into tblEmails(Recor dID, CoID,
SubscrID, Name) " _
& "Select @RecordID, @CoID, @SubscrID, @Name"
da.InsertComman d.Parameters.Cl ear()

'--add parameters for the Insert Statement

da.InsertComman d.Parameters.Ad d("@RecordID" , SqlDbType.Int, 4,
"RecordID")
da.InsertComman d.Parameters.Ad d("@CoID", SqlDbType.VarCh ar, 50, "CoID")
da.InsertComman d.Parameters.Ad d("@SubscrID" , SqlDbType.VarCh ar, 50,
"SubscrID")
da.InsertComman d.Parameters.Ad d("@Name", SqlDbType.VarCh ar, 50, "Name")

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

Dim reader As DataTableReader = ds.Tables("tblE xcel").CreateDa taReader
ds.Tables("tblE mails_Server"). Load(reader, LoadOption.Upse rt)

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

dgrv1.DataSourc e = ds.Tables("tblE xcel")
curMgr = CType(Me.Bindin gContext(ds.Tab les("tblExcel") ),
CurrencyManager )
tssL2.Text = (curMgr.Positio n + 1).ToString
tssL3.Text = curMgr.Count.To String

End Sub

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

Rich

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