Solved

Need a generic function to retrieve Stored Procedure parameters

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

786 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