Execute a SQL Stored Procedure and process the results [closed]

by

At the top of your .vb file:

Imports System.data.sqlclient

Within your code:

'Setup SQL Command
Dim CMD as new sqlCommand("StoredProcedureName")
CMD.parameters("@Parameter1", sqlDBType.Int).value = Param_1_value

Dim connection As New SqlConnection(connectionString)
CMD.Connection = connection
CMD.CommandType = CommandType.StoredProcedure

Dim adapter As New SqlDataAdapter(CMD)
adapter.SelectCommand.CommandTimeout = 300

'Fill the dataset
Dim DS as DataSet    
adapter.Fill(ds)
connection.Close()   

'Now, read through your data:
For Each DR as DataRow in DS.Tables(0).rows
    Msgbox("The value in Column ""ColumnName1"": " & cstr(DR("ColumnName1")))
next

Now that the basics are out of the way,

I highly recommend abstracting the actual SqlCommand Execution out into a function.

Here is a generic function that I use, in some form, on various projects:

''' <summary>Executes a SqlCommand on the Main DB Connection. Usage: Dim ds As DataSet = ExecuteCMD(CMD)</summary>'''
''' <param name="CMD">The command type will be determined based upon whether or not the commandText has a space in it. If it has a space, it is a Text command ("select ... from .."),''' 
''' otherwise if there is just one token, it's a stored procedure command</param>''''
Function ExecuteCMD(ByRef CMD As SqlCommand) As DataSet
    Dim connectionString As String = ConfigurationManager.ConnectionStrings("main").ConnectionString
    Dim ds As New DataSet()

    Try
        Dim connection As New SqlConnection(connectionString)
        CMD.Connection = connection

        'Assume that it's a stored procedure command type if there is no space in the command text. Example: "sp_Select_Customer" vs. "select * from Customers"
        If CMD.CommandText.Contains(" ") Then
            CMD.CommandType = CommandType.Text
        Else
            CMD.CommandType = CommandType.StoredProcedure
        End If

        Dim adapter As New SqlDataAdapter(CMD)
        adapter.SelectCommand.CommandTimeout = 300

        'fill the dataset
        adapter.Fill(ds)
        connection.Close()

    Catch ex As Exception
        ' The connection failed. Display an error message.
        Throw New Exception("Database Error: " & ex.Message)
    End Try

    Return ds
End Function

Once you have that, your SQL Execution + reading code is very simple:

'----------------------------------------------------------------------'
Dim CMD As New SqlCommand("GetProductName")
CMD.Parameters.Add("@productID", SqlDbType.Int).Value = ProductID
Dim DR As DataRow = ExecuteCMD(CMD).Tables(0).Rows(0)
MsgBox("Product Name: " & cstr(DR(0)))
'----------------------------------------------------------------------'

More Related Contents:

  1. Linq to Sql: Multiple left outer joins
  2. How do I find a stored procedure containing ?
  3. Procedure or function !!! has too many arguments specified
  4. Stored procedure that exports data into csv files only exports to one file
  5. Stored procedure hangs seemingly without explanation
  6. How to use a string variable in a directory path
  7. Multiple INSERT statements vs. single INSERT with multiple VALUES
  8. How do you view ALL text from an ntext or nvarchar(max) in SSMS?
  9. Stored procedure slow when called from web, fast from Management Studio
  10. T-SQL: Export to new Excel file
  11. Declare global variables in Visual Studio 2010 and VB.NET
  12. INSERT INTO vs SELECT INTO
  13. Dynamic button click event handler
  14. SQL Server SELECT into existing table
  15. How to get the last record per group in SQL
  16. SQL Server sp_msforeachtable usage to select only those tables which meet some condition
  17. Option Strict on by default in VB.NET
  18. How can I determine the status of a job?
  19. T-SQL substring – separating first and last name
  20. Convert decimal time to hours and minutes
  21. Nullable type with inline if cannot work together?
  22. Get File Size on FTP Server and put it on a Label
  23. Do clustered indexes have to be unique?
  24. How do I prevent a database trigger from recursing?
  25. Executing SQL Server Agent Job from a stored procedure and returning job result
  26. How Can I Detect and Bound Changes Between Row Values in a SQL Table?
  27. Open a webpage in the default browser
  28. How do I specify the equivalent of volatile in VB.net?
  29. Executable directory where application is running from?
  30. Convert String to Double – VB

Leave a Comment