Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Need a generic function to retrieve Stored Procedure parameters

Posted on 2004-04-07
7
Medium Priority
?
416 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 200 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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

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…
Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

636 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