• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 419
  • Last Modified:

Need a generic function to retrieve Stored Procedure parameters


I have started writing the below function
But i have a problem in what to return  

 Public Shared Function DeriveParameters(ByVal spName As String, _
                                                 ByVal cn As IDbConnection, _
                                                  ByVal provider As EnumsDal.eProviders) As IDbDataParameter

'//This will get the comand depending on the provider
'//so if provider is sql then it will get a sqlcommand
            Dim ocmd As IDbCommand = mydal.GetCommand(provider)

            Try
                With ocmd
                    .Connection = cn
                    .CommandText = spName
                    .CommandType = CommandType.StoredProcedure
                End With
           
                Select Case provider
                    Case EnumsDal.eProviders.ODBC
                        OdbcCommandBuilder.DeriveParameters(CType(ocmd, OdbcCommand))
                    Case EnumsDal.eProviders.SQLClient
                        SqlCommandBuilder.DeriveParameters(CType(ocmd, SqlCommand))
                    Case EnumsDal.eProviders.OLEDB
                        OleDbCommandBuilder.DeriveParameters(CType(ocmd, OleDbCommand))
                    Case EnumsDal.eProviders.Oracle
                        OracleCommandBuilder.DeriveParameters(CType(ocmd, OracleCommand))
                End Select

           Return ?????
            Catch


            Finally
             
                cn.Dispose()
            End Try

 


the scope of the above function is
that i would like to call it and get all the parameters so that i can loop


Code:

'//usually i would do something like this but i want to have a generic function call


Dim readCommand As New SqlCommand()
Dim sqlconn As New SqlConnection("Server=localhost;Initial Catalog=northwind;Uid=sa;PWD=sa")
sqlconn.Open()
readCommand.Connection = sqlconn
readCommand.CommandText = "SalesByCategory"
readCommand.CommandType = CommandType.StoredProcedure


Dim builder As New SqlCommandBuilder()
builder.DeriveParameters(readCommand)

'
Dim Par As SqlParameter
For Each Par In readCommand.Parameters
Console.WriteLine(Par.ParameterName + " " + Par.DbType.ToString() + " " + Par.Direction.ToString())
Next



How can i make a generic routine to achieve the above and how can i call it?
0
Gabriel_Brienza
Asked:
Gabriel_Brienza
  • 3
  • 2
1 Solution
 
Bob LearnedCommented:
I would imagine that you can accomplish by adding a generic Parameter class that you can build a collection of an return to the caller.  This way you can convert the SqlParameter, ODBCParameter, OleDbParameter, and OracleParameter to a generic type.  The difficulty would be in defining generic data types.

Bob
0
 
Gabriel_BrienzaAuthor Commented:
Thanks for your comment
Could you help me in building this generic parameter class.

I will then handle the definition of generic data types.

Thanks
Gabriel
0
 
Bob LearnedCommented:
Actually, you might want to start with something like this:

Implementing a Provider Independent Data Access Layer in .NET
http://www.codeproject.com/vb/net/data_access_layer.asp

Bob
0
 
Gabriel_BrienzaAuthor Commented:
I have looked at the link you mentioned and he actually caches the parameters which is something i wanted to do as well but i dont understand after you retrieved the params

how you loop through them

do you know?

i dont seem to be able to do
For Each Par In readCommand.Parameters
next

any suggestions
0
 
Bob LearnedCommented:
Don't have much time to help you on this one.

Bob
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

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