none
How do I convert tight subroutine that executes SQL into one that executes parameterized stored procedures?

    Question

  • In trying to learn vb.net, I have watched a number of instructional videos that have been very helpful.  The code below is from one of those (can't post link).  I have incorporated ExecQuery into a Windows forms application and find it extremely convenient when using SQL statements, but would like to adapt it to execute stored procedures.  It looks like it could be done relatively easily, but I cannot figure out how to pass the parameter(s) to the sub executing the stored procedure, as the number and names of parameters will vary from SP to SP.

    Specifically, I am asking for help converting the ExecQuery sub into an 'ExecSP' sub.

    Imports System.Data.SqlClient

    Public Class SQLControl
        Private DBCon As New SqlConnection("Server=WIZARDS\WIZARDBOX;Database=SQLTutorial;User=tutorial;Pwd=Password1;")
        Private DBCmd As SqlCommand

        ' DB DATA
        Public DBDA As SqlDataAdapter
        Public DBDT As DataTable

        ' QUERY PARAMETERS
        Public Params As New List(Of SqlParameter)

        ' QUERY STATISTICS
        Public RecordCount As Integer
        Public Exception As String

        Public Sub New()
        End Sub

        ' ALLOW CONNECTION STRING OVERRIDE
        Public Sub New(ConnectionString As String)
            DBCon = New SqlConnection(ConnectionString)
        End Sub

        ' EXECUTE QUERY SUB
        Public Sub ExecQuery(Query As String)
            ' RESET QUERY STATS
            RecordCount = 0
            Exception = ""

            Try
                DBCon.Open()

                ' CREATE DB COMMAND
                DBCmd = New SqlCommand(Query, DBCon)

                ' LOAD PARAMS INTO DB COMMAND
                Params.ForEach(Sub(p) DBCmd.Parameters.Add(p))

                ' CLEAR PARAM LIST
                Params.Clear()

                ' EXECUTE COMMAND & FILL DATASET
                DBDT = New DataTable
                DBDA = New SqlDataAdapter(DBCmd)
                RecordCount = DBDA.Fill(DBDT)
            Catch ex As Exception
                ' CAPTURE ERROR
                Exception = "ExecQuery Error: " & vbNewLine & ex.Message
            Finally
                ' CLOSE CONNECTION
                If DBCon.State = ConnectionState.Open Then DBCon.Close()
            End Try
        End Sub

        ' ADD PARAMS
        Public Sub AddParam(Name As String, Value As Object)
            Dim NewParam As New SqlParameter(Name, Value)
            Params.Add(NewParam)
        End Sub

        ' ERROR CHECKING
        Public Function HasException(Optional Report As Boolean = False) As Boolean
            If String.IsNullOrEmpty(Exception) Then Return False
            If Report = True Then MsgBox(Exception, MsgBoxStyle.Critical, "Exception:")
            Return True
        End Function
    End Class

    Saturday, April 22, 2017 12:17 AM

Answers

  • Thank you Karen.  In the time since I posted, I think I answered my own question.  :)

    I created the ExecSP sub from the ExecQuery sub, then added the second line below to change the command object:

     DBCmd = New SqlCommand(SP, DBCon)
     DBCmd.CommandType = CommandType.StoredProcedure

    In the sub that calls the ExecSP sub, I put the following in just before that call:

    DB.AddParam("@AccountID", 1)
    DB.AddParam("@UserID", 1)

    which enabled me to pass multiple parameters to the ExecSP sub, and it worked!

    I will review/study what you posted, and thanks again.

    Saturday, April 22, 2017 1:57 AM

All replies

  • I have working examples but they don't use SqlDataAdapter yet they could easily use SqlTableAdapter or SqlDataAdapter.

    I prefer using SqlConnection/SqlCommand and SqlDataReader for working with data or via Entity Framework. And Entity Framework works with stored procedures too.

    NOTE: One of the things to consider when working with stored procedures is if you don't regen say the TableAdapter or Entity model (be it database or model - and code first is another thing altogether) changes are not reflected in your project code.  

    Read/edit/add/remove

    https://code.msdn.microsoft.com/SQL-stored-procedures-1384f04c?redir=0

    Specifically for images

    https://code.msdn.microsoft.com/INSERT-Image-into-SQL-29dfc8ee?redir=0

    If for the first link, I use data wizards which are not used in the first link we get the following where each stored procedures shows the parameters needed to be passed in.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, April 22, 2017 12:49 AM
    Moderator
  • Thank you Karen.  In the time since I posted, I think I answered my own question.  :)

    I created the ExecSP sub from the ExecQuery sub, then added the second line below to change the command object:

     DBCmd = New SqlCommand(SP, DBCon)
     DBCmd.CommandType = CommandType.StoredProcedure

    In the sub that calls the ExecSP sub, I put the following in just before that call:

    DB.AddParam("@AccountID", 1)
    DB.AddParam("@UserID", 1)

    which enabled me to pass multiple parameters to the ExecSP sub, and it worked!

    I will review/study what you posted, and thanks again.

    Saturday, April 22, 2017 1:57 AM
  • Hi Thomas,

    It seems that you have solved this issue by yourself, please remember to close your thread by marking your post as answer, it is beneficial to the other communities who face then same issue.

    Thanks for your understanding.

    Best Regards,

    Cherry Bu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, April 25, 2017 2:56 AM
    Moderator