[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 48133
  • Last Modified:

Executing SQL Server stored procedure using VB.NET

Hi ,

I am pretty new to this.

I have a SQL Server stored (called SP_OESS_Get_Client_Demographics)  procedure that accepts following inputs:
@clientid varchar(10),
@firstname varchar(20),
@lastname varchar(20),
@middlename varchar(10),
@SSN varchar(11),
@Birthdate smalldatetime,
@Gender varchar(1)

and returns an XML result set. I want to know how I can execute this stored procedure using VB.NET. How to do I code it in VB.NET??

Thanks!

-Kunal
0
Kunal_Ghate
Asked:
Kunal_Ghate
  • 3
3 Solutions
 
ptakjaCommented:
Pretty straight forward. Here you go:

Here's a couple routines I use for this sort of thing:

    '****************************************************************************************
    ' ExecQuery
    ' ABSTRACT: Executes a stored procedure against the Eisemann database and returns
    '   a NEW Dataset with the selected data.
    '
    ' INPUT PARMS:  ProcedureName   Name of Stored Procedure to execute
    '               Parms           Array of SqlParameter objects that will be passed into the
    '                               stored procedure.
    '
    ' RETURNS:      DataSet populated with results from stored procedure execution.
    '
    '****************************************************************************************
    Private Overloads Function ExecQuery(ByVal ProcedureName As String, ByVal Parms As SqlParameter()) As DataSet

        Dim dsDataSet As New DataSet
        ' Configure the SqlCommand object
        With _cmdSqlCommand
            .CommandType = CommandType.StoredProcedure      'Set type to StoredProcedure
            .CommandText = ProcedureName                    'Specify stored procedure to run

            ' Clear any previous parameters from the Command object
            Call .Parameters.Clear()

            ' Loop through parmameter collection adding parameters to the command object
            If Not (Parms Is Nothing) Then
                For Each sqlParm As SqlParameter In Parms
                    _cmdSqlCommand.Parameters.Add(sqlParm)
                Next
            End If
        End With

        ' Configure Adapter to use newly created command object and fill the dataset.
        _adpAdapter.SelectCommand = _cmdSqlCommand
        _adpAdapter.Fill(dsDataSet)

        Return dsDataSet
    End Function

    '****************************************************************************************
    ' ExecNonQuery
    ' ABSTRACT: Executeds a non-query without any parameters.
    '
    ' INPUT PARMS: ProcedureName    stored procedure to execute.
    '
    ' RETURNS:  Integer indicating how many rows were affected by the non-query.
    '
    ' Copyright © 2005 by Corning, Inc.
    '****************************************************************************************
    Private Overloads Function ExecNonQuery(ByVal ProcedureName As String) As Integer
        Return ExecNonQuery(ProcedureName, Nothing)
    End Function


    '****************************************************************************************
    ' ExecNonQuery
    ' ABSTRACT: Executes a non-query stored procedure agains the Eisemann database.
    '
    ' INPUT PARMS:  ProcedureName   Name of stored procedure to execute
    '               Parms           Collection of SqlParameter objects used as arguments for
    '                               the stored procedure.
    '
    ' RETURNS:      An integer containing the number of rows affected by the Stored Procedure.
    '
    ' Copyright © 2005 by Corning, Inc.
    '****************************************************************************************
    Private Overloads Function ExecNonQuery(ByVal ProcedureName As String, ByVal Parms As SqlParameter()) As Integer

        Dim intRowsAffected As Integer

        ' Configure the _cmdSqlCommand object
        With _cmdSqlCommand
            .CommandType = CommandType.StoredProcedure  'Set type to Stored Procedure  
            .CommandText = ProcedureName                'Specify procedure to run

            ' Clear any previous parameters from the command object
            Call .Parameters.Clear()

            ' Loop through parmameter collection, if defined, adding parameters to the command object
            If Not (Parms Is Nothing) Then
                For Each sqlParm As SqlParameter In Parms
                    _cmdSqlCommand.Parameters.Add(sqlParm)
                Next
            End If
        End With
        If _cnnMyConnection.State <> ConnectionState.Open Then
            Call _cnnMyConnection.Open()
        End If
        ' Execute the procedure
        intRowsAffected = _cmdSqlCommand.ExecuteNonQuery()

        Return intRowsAffected                          'Return the number of rows affected by procedure

    End Function

To use these guys you create a sub like this:
Public Function GetDemographics(ClientID as String, FirstName as string, LastName as string, MiddleName as string, SSN as string, Bday as Date, Gender as string) As DataTable

        Dim MyDataSet as DataSet
        Dim Parms(6) As SqlParameter

        Parms(0) = New SqlParameter("@clientid", ClientID)
        Parms(1) = New SqlParameter("@firstname", FirstName)
        Parms(2) = New SqlParameter("@lastname", LastName)
        Parms(3) = New SqlParameter("@middlename", MiddleName)
        Parms(4) = New SqlParameter("@SSN", SSN)
        Parms(5) = New SqlParameter("@Birthdate", Bday)
        Parms(6) = New SqlParameter("@Gender", Gender)

        Return ExecuteQuery("SP_OESS_Get_Client_Demographics", Parms)
End function


You would need to create your SqlConnection object and a SqlDataAdapter object named _adpAdapter in order for this code to work. But this should get you started.
0
 
b1xml2Commented:
use this as your starting point:

Imports System.Xml
Imports System.IO
Imports System.Data.SqlClient
Imports System.Text

Public NotInheritable Class XmlReaderAdapter

    Private Sub New()

    End Sub

    Public Shared Function GetString(ByVal command As SqlCommand, Optional ByVal rootName As String = "root") As String
        Return GetString(command, Encoding.UTF8, rootName)
    End Function

    Public Shared Function GetString(ByVal command As SqlCommand, ByVal encoding As System.Text.Encoding, Optional ByVal rootName As String = "root") As String
        Dim memory As New MemoryStream
        Dim dataReader As XmlReader
        Dim writer As New XmlTextWriter(memory, encoding)
        Dim reader As New StreamReader(memory)
        writer.WriteStartDocument(True)
        writer.WriteStartElement(rootName)
        Try
            command.Connection.Open()
            dataReader = command.ExecuteXmlReader()
            dataReader.Read()
            While dataReader.Read
                writer.WriteRaw(dataReader.ReadOuterXml())
            End While
           
        Catch ex As Exception

        Finally
            If Not dataReader Is Nothing Then
                dataReader.Close()
            End If
        End Try
        writer.WriteEndDocument()
        writer.Flush()
        memory.Position = 0
        Dim value As String = reader.ReadToEnd()
        writer.Close()
        reader.Close()
        Return value
    End Function


End Class
0
 
b1xml2Commented:
this is an example of the class to call the XmlReaderAdapter

Imports System.Data.SqlClient
Imports System.Data

Public NotInheritable Class ClientDemographics


    Private Shared Function GetSqlCommand(ByVal connection As SqlConnection) As SqlCommand
        Dim command As SqlCommand = connection.CreateCommand()
        command.CommandType = CommandType.StoredProcedure
        command.CommandText = "SP_OESS_Get_Client_Demographics"
        With command.Parameters
            .Add("@RETURN_VALUE", SqlDbType.Int, 4)
            command.Parameters(0).Direction = ParameterDirection.ReturnValue
            .Add("@clientid", SqlDbType.VarChar, 20)
            .Add("@firstname", SqlDbType.VarChar, 20)
            .Add("@lastname", SqlDbType.VarChar, 20)
            .Add("@middlename", SqlDbType.VarChar, 10)
            .Add("@SSN", SqlDbType.VarChar, 11)
            .Add("@Birthdate", SqlDbType.SmallDateTime, 4)
            .Add("@Gender", SqlDbType.VarChar, 1)
        End With
        Return command
    End Function

    Public Sub New()
        MyBase.New()
    End Sub

    Public ID As String
    Public FirstName As String
    Public LastName As String
    Public MiddleName As String
    Public SSN As String
    Public BirthDate As DateTime
    Public Gender As String

    Public Function GetXmlString(ByVal connection As SqlConnection) As String
        Return XmlReaderAdapter.GetString(GetSqlCommand(connection))
    End Function

    Private Shared Function GetValue(ByVal item As DateTime) As Object
        Dim value As Object = DBNull.Value
        If DateTime.Compare(item, DateTime.MinValue) > 0 Then
            value = item
        End If
        Return value
    End Function

    Private Shared Function GetValue(ByVal item As String) As Object
        Dim value As Object = DBNull.Value
        If Not item Is Nothing AndAlso item.Length > 0 Then
            value = item
        End If
        Return value
    End Function





End Class



0
 
b1xml2Commented:
expanding the GetXmlString Function


Public Function GetXmlString(ByVal connection As SqlConnection) As String
        Dim command As SqlCommand = GetSqlCommand(connection)
        command.Parameters("@clientid").Value = GetValue(ID)
        command.Parameters("@firstname").Value = GetValue(FirstName)
        command.Parameters("@lastname").Value = GetValue(LastName)
        command.Parameters("@middlename").Value = GetValue(MiddleName)
        command.Parameters("@SSN").Value = GetValue(SSN)
        command.Parameters("@Birthdate").Value = GetValue(BirthDate)
        command.Parameters("@Gender").Value = GetValue(Gender)
        Dim value As String = XmlReaderAdapter.GetString(command)
        command.Dispose()
        Return value
End Function


Thus, you call the class like so:
Dim connection As New SqlConnection(...)
Dim demographics As New ClientDemographics
demographics.ID = ""
...
Dim value As String = demographics.GetXmlString(connection)

0
 
nwhanCommented:
try this...kinda neat..
but you get the logics of how it flows/works...den put it on ur sides

---------------------------------------------------------------------------------
Dim cn As New SqlClient.SqlConnection()
Dim cm As New SqlClient.SqlCommand()

        With cn
            .ConnectionString = "user id=sa;password=;" & _
            "data source=localhost;" & _
            "initial catalog=database_name"
            .Open()
        End With

        With cm
            .CommandText = "stored_procedure"
            .CommandType = CommandType.StoredProcedure
            .Connection = cn
            'output parameters which you wanna shoot to the form from database
            .Parameters.Add("@getout2", SqlDbType.Int)
            .Parameters("@getout2").Direction = ParameterDirection.Output
            'input parameters for condition purpose taken from textbox.text
            .Parameters.Add("@month", textbox.Text)
        End With

        Try
            cm.ExecuteNonQuery()
        Catch exp As SqlClient.SqlException
            MsgBox(exp.Message)
        End Try

        'display output parameter based on input parameter into label1.text
      Label1.Text = cm.Parameters("@getout2").Value
---------------------------------------------------------------------------------

hope it helps....
do let me know if it works...
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now