'Repeating' a Stored procedure

[Shane]

Hi all

Still trying to get my head around VB.Net/ADO.Net and have a question.

I can't work out how to 'process' a stored procedure multiple times.

In other words, in VB6/ADO I would do something like:

For i = 1 to 10
rs.Open "usp_Stored_Procedure '" & i & "'"
....process the results
rs.Close
Next

In essence, the recordset variable has been created and I am simply opening
and closing it a number of times with different input values to the Stored
procedure.

I think that I have started to get my head around 'easy' ADO.Net data
retrieval and can for instance open a data adapter and populate a data
table.

What I can't seem to establish is how to write the equivalent of the above
code in ADO.Net.

My program contains the following

Dim rs As New SqlDataAdapter(strSQL, myConnection)....where strSQL is a
Stored Procedure and accepts a parameter.
Dim rsTable As New Data.DataTable
rs.Fill(rsTable)

.....process the data

....and then I assume that I need to close the DataAdaptor (and Table)? i.e.

rs.Dispose()
rs = Nothing
rsTable.Dispose()
rsTable = Nothing

If I want to repeat this do I contain the whole code above within a loop, so
that the DataAdaptor and DataTable are continually being created, disposed
of and created again i.e. is the following accurate...?

'Start the Loop here
For i = 1 to 10
Dim rs As New SqlDataAdapter(strSQL, myConnection)
Dim rsTable As New Data.DataTable
rs.Fill(rsTable)

.....process the data

rs.Dispose()
rs = Nothing

rsTable.Dispose()
rsTable = Nothing

Next

The dataset will always only contain a single row - is there a better way to
process the above if data is limited to a single row each time?

Very many thanks for your consideration.



Shane Clark

 

[William Vaughn]

This example approaches the problem a bit more rationally. No, you don't
need to "close" the DataTable or DataAdapter (which is not needed as I don't
intend to let ADO.NET handle the Updates. The DataReader approach (with the
DataTable Load method) takes less overhead. It also does not Open and Close
the connection for each loop. I would not worry about disposing objects in
the SqlClient namespace. We've discussed this before. While it's essential
that you close connections (critical), it's not necessary (and in this case
not useful) to Dispose them as you want to reuse the objects (the SqlCommand
and SqlConnection). This is detailed in my book...

hth

'Copyright (c) 2005, 2006, 2007, 2008 Beta V Corporation. All rights
reserved.
' For demonstration purposes only. No warranty of any kind expressed or
implied.
Imports System.Data.SqlClient

Public Class Form1
Dim cn As SqlConnection
Dim cmd As SqlCommand
Sub New()
InitializeComponent()
cn = New SqlConnection(My.Settings.BiblioConnection)
End Sub

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
cmd = New SqlCommand("AuthorsByYearBorn", cn)
cmd.Parameters.Add("@YearHigh", SqlDbType.Int)
cmd.Parameters.Add("@YearLow", SqlDbType.Int).Value = 1900
cmd.CommandType = CommandType.StoredProcedure

Dim dr As SqlDataReader
Dim tb(10) As DataTable, j As Integer = 0
Try
cn.Open()
For i As Integer = 1900 To 1950 Step 5
tb(j) = New DataTable
cmd.Parameters("@YearLow").Value = i
cmd.Parameters("@YearHigh").Value = i + 4
dr = cmd.ExecuteReader
tb(j).Load(dr)
j += 1
Next i
Catch ex As Exception
MsgBox(ex.ToString)
Finally
cn.Close()
End Try
End Sub
End Class--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________

 

[Shane]

Very many thanks for your response.

I will work through the sample logic and keep my fingers crossed!

Thanks again


Shane