Suggestion: Strongly typed OracleCommand objects

kevinherring · Post by **kevinherring** » Wed 14 Feb 2007 10:46

Perhaps this already exists and I just dont know about it, but wouldnt it be cool if you could create a class based on a package or procedure at design time, in much the same way you can create strongly typed datasets.

So I would imagine connecting to the database and then selecting a package/procedure which would then generate a class that inherits from the OracleCommand object. This would have the procedure name and parameters etc as strongly typed properties. You could also use constants stored in packages.

Then when you want to use the procedure you just create and instance of the class and use it. Then any type-errors would be caught at design time.

just a thought.

Alexey · Post by **Alexey** » Thu 15 Feb 2007 08:17

Take a look at our Oracle Package Wizard. I think this is what you imagine.

kevinherring · Post by **kevinherring** » Thu 15 Feb 2007 10:10

oh my god! this tool just lost me all my work from yesterday afternoon!

I tried to create a wrapper for a package that was called the same thing as one of my controls and it just overwrote my bloody control and then saved it without even prompting me!! How pissed off am I right now?!?

Anyway, putting the rage I have inside me aside for a second, yes this kind of what I am after but not quite. I think it would be better to create a package class and then procedure classes inside the package. This would make it more flexible and sit better with OO design. Also I would expect to see the constants in the db package in the package class.

Also I get compilation errors with the generated code, it doesnt like this statement:

Code: Select all

If (Parameters("P_CURS").Value = System.DBNull.Value) Then

you should be using 'is' instead of '='

Also if you try to create a package of the same name as something that already exists but is checked in, it doesnt do anything at all (no warning message or anything)

Alexey · Post by **Alexey** » Thu 15 Feb 2007 11:43

We will consider your proposition.
As for bugs you reported, we will fix them after deeper investigation.

Alexey · Post by **Alexey** » Tue 20 Feb 2007 12:13

We have fixed these bugs. Look forward to the next build.

kevinherring · Post by **kevinherring** » Wed 21 Feb 2007 15:19

sorry to revisit this again but I though I would add some more to it.

I think the thing really lacking with .net & oracle is integration with the objects on the database and the developer.
I would really like to be in a situation where you can connect to the database and create .Net objects based on what it finds in the database. So say for example you have a procedure, I would expect a class to created where the input parameters are properties of the class.
What I would also expect is that any application_exceptions in the procedure would be mapped to .net exceptions in the class. I'm not sure whether this could be achieved by examining the code (not sure this is possible thought), or perhaps with some metadata stored in the package comments or something. I hate having to look at the error code to determine what sort of error has occured - very VB 6!

Also it would be great if you could pick up all the constants from a package. I have a lot of situations where I have defined constants in a package and the same in my app. If I make a change to the constants in the package, I have to change all my .net code that relies on it.

I guess that this also applies to custom types (varrays etc) too.

Your oraclepackage wrapper is a step in the right direction but I dont think it goes nearly far enough. For example (and this may be because of my lack of understanding of it, but the help file doesnt have a lot of examples), how do I use a pagereader with one of the procedures?

It would also be good to have some type of settings file to say ' in package a, I am interested in procedure b and c. procedure c is always going to be executed as a datareader'. So there is no need to provide .ExecuteNonQuery and all the other ways you can execute a query because you know they will never be needed. Then you can click on the settings file and select "Refresh oracle objects" and all objects defined in you settings file with be refresh with any changes.

This would also apply to tables (including simple constraints), and roles.

Anyway I just thought I would let you know that there is most definitely a demand for this. .Net is moving much more towards picking up errors at compile time rather than run-time, with its strongly typed datasets and generics/strongly typed arraylists etc, and this would be a big leap forward.
Thanks
Kevin

kevinherring · Post by **kevinherring** » Thu 22 Feb 2007 12:58

ooh and I thought of another thing:
Converting lookup tables to a structure/class would be pretty cool.
eg you have a table called Statuses:

Code: Select all

status_code    |   status_description
----------------------------------------
A              |   Accepted
R              |   Rejected

could be converted into a class:

Code: Select all

Public Class Statuses
Public Const Accepted As String = "A"
Public Const Rejected As String = "R"
End Class

I'm full of good ideas, me!

Alexey · Post by **Alexey** » Fri 23 Feb 2007 07:57

Thanks for your suggestions. We are collecting propositions from all the customers and going to improve and enhance the wizard in future.