Find Code:
All Words
Any of the Words
Exact Phrase
Home
:
Code
:
Forums
:
Submit
:
Mailing List
:
About
:
Contact
Code
All
VB.NET
ASP.NET
C#
VB Classic
ASP Classic
Snippets
Popular
Resources
Submit Code
Forums
Articles
Tips
Links
Books
Contest
Link to us
Execute stored procedure (VB.NET)
Author:
Nikhil
E-mail:
Click to e-mail author
Submitted:
10/4/2004
Version:
VB.NET
Compatibility:
VB.NET
Category:
Databases
Views:
69814
This function will execute any stored procedure written in SQL server2000 and would return the data as a dataset. It takes care of all the input variables and output variables specified in your SP. Output variables are returned as an array of strings which is passed ByRef to this function.
Declarations:
'none
Code:
System.Data.SqlClient.SqlConnection System.Data.SqlClient.SqlDataAdapter System.Data.SqlClient.SqlCommand System.Data.SqlClient.SqlParameter System.Data.SqlClient.SqlCommandBuilder System.Data.SqlClient.SqlException Public Function QueryExcuter(ByVal iParamCount As Integer, _ ByVal arrInputValues() As String, _ ByRef arrOutputParams() As String, _ ByVal sSPName As String, _ ByVal sDSname As String) As DataSet Dim objCon As SqlConnection Dim objAdapter As SqlDataAdapter Dim cmdCommand As New SqlCommand Dim iCount As Integer Dim objDataSet As DataSet Dim objParam As SqlParameter Dim sconnection As String Try sconnection = "data source =;initial catalog = ; user ID=; password = " objAdapter = New SqlDataAdapter objCon = New SqlConnection(sconnection) objCon.Open() objAdapter.SelectCommand = cmdCommand objAdapter.SelectCommand.Connection = objCon objAdapter.SelectCommand.CommandType = CommandType.StoredProcedure objAdapter.SelectCommand.CommandText = sSPName Try SqlCommandBuilder.DeriveParameters(objAdapter.SelectCommand) For iCount = 1 To iParamCount If (objAdapter.SelectCommand.Parameters(iCount).Direction = _ ParameterDirection.Input) Or _ (objAdapter.SelectCommand.Parameters(iCount).Direction = _ ParameterDirection.InputOutput) Then objAdapter.SelectCommand.Parameters(iCount).Value = arrInputValues(iCount - 1) End If Next objDataSet = New DataSet(sDSname) objAdapter.Fill(objDataSet) iCount = 0 For Each objParam In objAdapter.SelectCommand.Parameters If (objParam.Direction = ParameterDirection.Output) Or _ (objParam.Direction = ParameterDirection.InputOutput) Then ReDim Preserve arrOutputParams(iCount) arrOutputParams(iCount) = objParam.Value iCount += 1 End If objParam = New SqlParameter Next Catch ex As SqlException objCon.Close() Throw ex End Try Catch ex As Exception objCon.Close() Throw ex End Try objCon.Close() Return objDataSet End Function
Home
|
Forums
|
Submit
|
Books
|
Mailing List
|
Advertising
|
About
|
Contact
© 2024 A1VBCode. All rights reserved.
Legal disclaimer & terms of use
Privacy statement