Kunal_Ghate
asked on
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_Demogra phics) 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
I am pretty new to this.
I have a SQL Server stored (called SP_OESS_Get_Client_Demogra
@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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
expanding the GetXmlString Function
Public Function GetXmlString(ByVal connection As SqlConnection) As String
Dim command As SqlCommand = GetSqlCommand(connection)
command.Parameters("@clien tid").Valu e = GetValue(ID)
command.Parameters("@first name").Val ue = GetValue(FirstName)
command.Parameters("@lastn ame").Valu e = GetValue(LastName)
command.Parameters("@middl ename").Va lue = GetValue(MiddleName)
command.Parameters("@SSN") .Value = GetValue(SSN)
command.Parameters("@Birth date").Val ue = GetValue(BirthDate)
command.Parameters("@Gende r").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 )
Public Function GetXmlString(ByVal connection As SqlConnection) As String
Dim command As SqlCommand = GetSqlCommand(connection)
command.Parameters("@clien
command.Parameters("@first
command.Parameters("@lastn
command.Parameters("@middl
command.Parameters("@SSN")
command.Parameters("@Birth
command.Parameters("@Gende
Dim value As String = XmlReaderAdapter.GetString
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(
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.StoredProcedur
command.CommandText = "SP_OESS_Get_Client_Demogr
With command.Parameters
.Add("@RETURN_VALUE", SqlDbType.Int, 4)
command.Parameters(0).Dire
.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
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