Solved

Need a generic function to retrieve Stored Procedure parameters

Posted on 2004-04-07
7
392 Views
Last Modified: 2011-04-14

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
Comment
Question by:Gabriel_Brienza
  • 3
  • 2
7 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 10777539
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
 

Author Comment

by:Gabriel_Brienza
ID: 10781706
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
 
LVL 96

Accepted Solution

by:
Bob Learned earned 50 total points
ID: 10783982
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
 

Author Comment

by:Gabriel_Brienza
ID: 10784657
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
 
LVL 96

Expert Comment

by:Bob Learned
ID: 10786143
Don't have much time to help you on this one.

Bob
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

A basic question.. “What is the Garbage Collector?” The usual answer given back: “Garbage collector is a background thread run by the CLR for freeing up the memory space used by the objects which are no longer used by the program.” I wondered …
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now