Solved

Generic Data Access Component???

Posted on 2003-11-19
11
322 Views
Last Modified: 2011-09-20
The following URL details an article regarding the build of a generic data access component using factory patterns
http://www.vbdotnetheaven.com/Code/Jun2003/2043.asp

The data access component seems to allow the use of providers like SqlClient and OleDB. I believe the idea is that you can extend this to support other providers by adding the additional provider types within each of the share arrays _connectionTypes() , _commandTypes(), _dataAdapterTypes() and _dataParameterTypes()
i.e.
Private Shared _connectionTypes() As Type = {GetType(OleDbConnection), GetType(SqlConnection), GetType(OtherProviderType)}

I have added the new Oracle provider (System.Data.OracleClient) rather than using OleDB to access Oracle stored procedures. I have used this and all is good although i've come across a problem regarding the calling of stored procedures which return special data provider types i.e. Cursor object from the PLSQL Stored procedure for example.

An idea how you could call a PLSQL stored procedure returning cursor object(s) using this generic data access component via the oracle provider (System.Data.OracleClient)??

One of the functions within the component is CreateDataParameter i.e.
Public Function CreateDataParameter(parameterName As String, dataType As DbType) As IDbDataParameter

which expects a parameter of type DbType. Unfortunately the DbType does not have an similar OracleClientType.Cursor type to specify when creating the parameter object.

Can anyone give me a coded solution to this??
Many Thanks!
Hung
0
Comment
Question by:smlo88
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 15

Accepted Solution

by:
SRigney earned 25 total points
Comment Utility
I think you'll have to rewrite the generic portion to handle more, or else just use a more specific Data Access Component.  DbType is used because it conforms to both OleDBConnection and SqlConnection.  Now you want to use a type that is no longer consistent with those.  You could create your own enumeration to deal with types and re-implement all of the existing DbType, plus add your new Cursor, but then you have the problem that if you try connecting to anything other than Oracle that there is a type of Cursor which will not be handled properly.   Thus you really need an Oracle specific DAC.  You can build it based on the existing generic one, but add the Oracle specific functionality where needed.
0
 
LVL 22

Assisted Solution

by:_TAD_
_TAD_ earned 25 total points
Comment Utility


As I see it, you have two choices:

1) Do as SRigney suggests and re-write your data access component

2) Edit your stored procedure.


While doing suggestion 1 might be a good long term solution, I think redoing your stored procedure to return a query instead of cursor might be a faster/easier solution.


A cursor really is just a subset of data.  Why not simply (as part of your stored procedure) create a temp table.

Then your final steps just before ending your stored procedure will be to:

1) for each row in cursor insert into tempTbl
2) Select * from tempTbl

This is what I do in SQL server to avoid returning arrays of data, I just return a query of the tempTable I have created.


0
 

Author Comment

by:smlo88
Comment Utility
SRigney:

I believe that the DbType conforms to Sql, oledb and oracle types.
The following url details a table and shows you what Dbtypes to use depending on the provider that you are using:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconusingparameterswithdataadapters.asp

Although, in saying this....it seems to only cover basic types. Specific provider types such as Cursor from OracleClient provider are not detailed.
I have managed to modify the generic data access component to acommodate the new OracleClient provider. I have been successful in calling a PLSQL stored procedure that takes basic input parameter types and output a number of PLSQL binary tables i.e. a recordset. This has been done using the OracleClient provider via the generic component.

But rather than using PLSQL tables to return a recordset we can use a single cursor object as an output parameter within the stored procedure. Coding the stored procedure is far quicker and easier....All you do is open the defined cursor and assign it to the defined output cursor parameter. Done...


_TAD_:

You suggestion in changing the stored procedure is specific for SQL Server is it not? I know in SQL Server the only way of return a recordset of data is to do a select * from table/joined tables. But i dont believe you can do this in Oracle using a PLSQL stored proc. To get a recordset out of an Oracle stored proc, you need to use either:

1) PLSQL Binary tables as output parameters
2) A cursor object as output parameter      

These are the only possible options within Oracle as i understand it.
And as i describe above, using a single cursor output parameter is far quicker and easier to implement.....within the PLSQL stored proc, you open the defined cursor and simply assign it to the output cursor parameter and thats it done.

0
 
LVL 15

Expert Comment

by:SRigney
Comment Utility
I understand that Oracle uses the standard types for input parameters, but as you state yourself, it has the ability to return a Cursor type.  Microsoft SQL cannot return a cursor type, thus if you modify your generic DAC to handle a type of Cursor and try connecting to MicrosoftSQL then it will not work.  

You probably need to modify
Public Function CreateDataParameter(parameterName As String, dataType As DbType) As IDbDataParameter

to use Oracle specific objects rather than the basic DbType.  

You are at the point that many people get to.  Do I write my code generic so it can be ported, or do I write me code specific so I can get things done the most efficiently.  Up until now you have been using generic, but if you want to deal with a Cursor, then you MUST write your code to be specific to Oracle.  Access and MSSQL cannot handle this type.  Look for the corresponding Oracle object that lists all types.  There's probably some kind of enumeration within the Oracle object that deals with all of the types including cursor.  Use it instead of DbType.
0
 
LVL 22

Expert Comment

by:_TAD_
Comment Utility


ack...

I'm afraid you are correct.  I was pretty sure I had returned a select statement from a stored procedure in Oracle (like I do with SQL server), but after looking in some old code I am returning a cursor object(Oracle 7.3.4 and VB 6).

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:smlo88
Comment Utility
SRigney:

Can i just confirm, if we modify the function
Public Function CreateDataParameter(parameterName As String, dataType As DbType) As IDbDataParameter

to make use of the OracleType rather than DbType then we will also be returning the OracleDbDataParameter instead of the IDbDataParameter.
And if this is the case, we wont be able to use any of the existing generic methods such as CreateCommand......as this returns an IDdCommand object.  The CreateParameter method of the IDbCommand object would expect an IDbDataParameter rather than the OracleDbDataParameter.

Therefore, none of the methods within the generic DAC would be of any use as these all return interface types. We would moreorless need to rewrite all of the generic methods to be specific to the OracleClient provider i.e. create connection, command, dataadaptor and parameter....
Is this what you are saying? So we cant do what i was hoping for, all within the same generic DAC. Its pretty poor if this is the case :(

0
 
LVL 22

Expert Comment

by:_TAD_
Comment Utility
I don't think so...

If you use your WinCV.exe and query that class you should be able to see whether the class interfaces IDBCommand or not.

OleDbCommand Interfaces IDBCommand.... there for a statement like:

IDBCommand iComm = New OleDbCommand(<params>);  

will be a valid command and will compile properly.

0
 
LVL 22

Expert Comment

by:_TAD_
Comment Utility

If you haven't used WinCV.exe before you can find it in your visual studio directory (it comes with the .Net framework).

here is where I found it on my computer:
C:\Program Files\Microsoft Visual Studio .NET 2003\SDK\v1.1\Bin\WinCV.exe


Open up this application and where it says <search string>  type in the name of the class.


This will list all dependecies methods, properties, interfaces, etc.
0
 
LVL 15

Expert Comment

by:SRigney
Comment Utility
If TADs suggestion doesn't show that IDbDataParameter is supported by the Oracle object, then yes you'll have to have everything be Oracle specific and rewrite the entire class.  I know it stinks, but it is what I was saying all along.
0
 
LVL 96

Expert Comment

by:Bob Learned
Comment Utility
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Split: SRigney {http:#9782288} & _TAD_ {http:#9783473}

Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

TheLearnedOne
EE Cleanup Volunteer
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Flash (http://en.wikipedia.org/wiki/Adobe_Flash) has evolved over the years to where it has become a masterful tool for displaying content screen.  It has excellent layout placement, UI precision as well as rendering capabilities. This, along with t…
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

772 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

10 Experts available now in Live!

Get 1:1 Help Now