Solved

Need a generic function to retrieve Stored Procedure parameters

Posted on 2004-04-07
7
393 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It seems a simple enough task, yet I see repeated questions asking how to do it: how to pass data between two forms. In this article, I will show you the different mechanisms available for you to do just that. This article is directed towards the .N…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

896 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

12 Experts available now in Live!

Get 1:1 Help Now