SQL Server Stored Procedure operations in VB.net

Imports system. Data. sqlclient
Public class database
Private con as sqlconnection
'// <Summary>
'// Execute the Stored Procedure
'// </Summary>
'// <Param name = "procname"> name of the stored procedure </param>
'// <Returns> return the Stored Procedure return value </returns>
Public Function runproc (byval procname as string) as integer
Dim cmd as sqlcommand
Cmd = createcommand (procname, nothing)
Me. Close ()
Dim returnvalue as integer
Returnvalue = cmd. parameters ("returnvalue"). Value
Return returnvalue
End Function
'// <Summary>
'// Execute the Stored Procedure
'// </Summary>
'// <Param name = "procname"> stored procedure name </param>
'// <Param name = "prams"> parameters required for Stored Procedures </param>
'// <Returns> return the Stored Procedure return value </returns>
Public Function runproc (byval procname as string, byval Params () as sqlparameter) as integer
Dim cmd as sqlcommand
Cmd = createcommand (procname, Params)
Cmd. executenonquery ()
Me. Close ()
Dim returnvalue as integer
Returnvalue = cmd. parameters ("returnvalue"). Value
Return returnvalue
End Function
'// <Summary>
'// Execute the Stored Procedure
'// </Summary>
'// <Param name = "procname"> name of the stored procedure </param>
'// <Param name = "datareader"> return the Stored Procedure return value </param>

Public sub runproc (byval procname as string, byref datareader as sqldatareader)
Dim cmd as sqlcommand = createcommand (procname, nothing)
Datareader = cmd. executereader (system. Data. commandbehavior. closeconnection)
End sub

'// <Summary>
'// Execute the Stored Procedure
'// </Summary>
'// <Param name = "procname"> name of the stored procedure </param>
'// <Param name = "prams"> parameters required for Stored Procedures </param>
'// <Param name = "datareader"> parameters required for Stored Procedures </param>
Public sub runproc (byval procname as string, byval prams as sqlparameter (), byref datareader as sqldatareader)
Dim cmd as sqlcommand = createcommand (procname, prams)
Datareader = cmd. executereader (system. Data. commandbehavior. closeconnection)
End sub
'// <Summary>
'// Create a sqlcommand object to execute the Stored Procedure
'// </Summary>
'// <Param name = "procname"> name of the stored procedure </param>
'// <Param name = "prams"> parameters required for Stored Procedures </param>
'// <Returns> returns the sqlcommand object </returns>

Private function createcommand (byval procname as string, byval prams as sqlparameter () as sqlcommand
Open ()
Dim cmd as sqlcommand = new sqlcommand (procname, con)
Cmd. commandtype = commandtype. storedprocedure
If not (prams is nothing) then
For each parameter as sqlparameter in prams
Cmd. Parameters. Add (parameter)
Next
End if
Cmd. Parameters. Add (New sqlparameter ("returnvalue", sqldbtype. Int, 4, parameterdirection. returnvalue, false, 0, 0, String. Empty, datarowversion. Default, nothing ))
Return cmd
End Function

Private sub open ()
If con is nothing then
Con = new sqlconnection (configurationsettings. receivettings ("constr "))
End if
If con. State = system. Data. connectionstate. Closed then
Con. open ()
End if
End sub

Public sub close ()
If con is nothing = false then
Con. Close ()
End if
End sub

Public sub dispose ()
If con is nothing = false then
Con. Dispose ()
Con = nothing
End if
End sub

'// <Summary>
'// Input parameters
'// </Summary>
'// <Param name = "paramname"> stored procedure name </param>
'// <Param name = "dbtype"> parameter type </param>
'// <Param name = "size"> parameter size </param>
'// <Param name = "value"> parameter value </param>
'// <Returns> New Parameter object </returns>
Public Function makeinparam (byval paramname as string, byval dbtype as sqldbtype, byval size as integer, byval value as object) as sqlparameter
Return makeparam (paramname, dbtype, size, parameterdirection. Input, value)
End Function
'// <Summary>
'// Input the return value Parameter
'// </Summary>
'// <Param name = "paramname"> stored procedure name </param>
'// <Param name = "dbtype"> parameter type </param>
'// <Param name = "size"> parameter size </param>
'// <Returns> New Parameter object </returns>
Public Function makeoutparam (byval paramname as string, byval dbtype as sqldbtype, byval size as integer) as sqlparameter
Return makeparam (paramname, dbtype, size, parameterdirection. Output, nothing)
End Function

'//

' // generate stored procedure parameters
'//
' // stored procedure name
'// parameter type
'/ // parameter size
'// parameter direction
'// parameter value
' // New Parameter object

'// <Summary>
'// Input the return value Parameter
'// </Summary>
'// <Param name = "paramname"> stored procedure name </param>
'// <Param name = "dbtype"> parameter type </param>
'// <Param name = "size"> parameter size </param>
'// <Returns> New Parameter object </returns>
Public Function makereturnparam (byval paramname as string, byval dbtype as sqldbtype, byval size as integer) as sqlparameter
Return makeparam (paramname, dbtype, size, parameterdirection. returnvalue, nothing)
End Function

Public Function makeparam (byval paramname as string, byval dbtype as sqldbtype, byval size as int32, byval direction as parameterdirection, byval value as object) as sqlparameter
Dim Param as sqlparameter
If size> 0 then
Param = new sqlparameter (paramname, dbtype, size)
Else
Param = new sqlparameter (paramname, dbtype)
End if
Param. Direction = direction
If not (Direction = parameterdirection. Output andalso value is nothing) then
Param. value = Value
End if
Return Param
End Function
End Class