CodeSmith Templates for Oracle

mr_lasseter posted on Friday, November 03, 2006

Anyone know if these exist? I am looking for CSLA 2.1, but any version will do.

Thanks,

Mike

rhoeting replied on Friday, November 03, 2006

Just today, we are currently working on extending the 2.1 C# templates to work with either Oracle and SQL, since our backend will be Oracle. It may be several days. The existing team has done such a good job "refactoring" out the data access segments, we believe it can be done in a clear, non-intrusive way. Once complete, we plan to post them out to the CodePlex site for others to use.

Rob

EaglePower replied on Friday, November 03, 2006

Where are you guys at with the Oracle CSLA 2.1 C# templates? I am starting to work on it too and PL/SQL templates and converting the C# to VB.NET. I would appreciate any help and what to do regarding using stored procs to generate business classes. I am troubleshooting the way it determines Identity or Primary Key fields from the stored procs using the Extended Properties table.

Thanks,
JT

rhoeting replied on Saturday, November 04, 2006

JT, we just started so we're not very far. One of my team members has taken ownership of the problem and is working thru the issues associated with discovering schema object thru an oracle connnection. Why don't you shoot me an email and we can discuss this further offline: rhoeting@gmail.com.

david.wendelken replied on Saturday, November 04, 2006

EaglePower:

Where are you guys at with the Oracle CSLA 2.1 C# templates? I am starting to work on it too and PL/SQL templates and converting the C# to VB.NET. I would appreciate any help and what to do regarding using stored procs to generate business classes. I am troubleshooting the way it determines Identity or Primary Key fields from the stored procs using the Extended Properties table.

If you are trying to generate to Oracle and use the Oracle database to supply data, "Extended Properties" in the Sql Server sense does not exist. There are a huge set of well defined and very complete views to pull schema info from (unlike Sql Server, which has poorly defined and incomplete views).

Don't have Oracle installed at the moment, but the views have names like "All_Tables", "All_Constraints", etc. To get the equivalent of the Sql Server Select statement returning a result set, I think you will have to use a Ref Cursor.

bobhagan replied on Sunday, November 05, 2006

Nonetheless, to use Codesmith you need to create a table called Extended Properties in your schema.
If I remember, there is a script to create this in the OracleSchemaProvider on Eric's site.

On Ref Cursors - I asked about this some time ago here and on asp.net. http://forums.asp.net/thread/1304557.aspx
It appears that they do work in VS2003 but don't with 2005 if you use the MS OracleClient. You seem to have to use the Oracle version. I'm just messing around (prototyping) in 2005 and switched to dynamic queries and haven't explored this further.

Bob Hagan

david.wendelken replied on Sunday, November 05, 2006

I know that oracle released a free "Oracle for .Net" set of classes. Haven't worked with them, but have heard good things about them from my oracle set of friends.

Oracle (www.oracle.com) has a Technology Network section on on its website where you can pretty much download all their tools to use at home to learn with for free.

bobhagan replied on Sunday, November 05, 2006

They have an Oracle Express that parallels SQL Server Express, that is supposed to also be able to be copied from one machine to another. Aimed at small businesses. I just downloaded the Oracle for Net tools. Runs as Oracle Explorer inside VS 03 and 05. So far so good.

Kahn replied on Sunday, November 05, 2006

Personally I would suggest the data access code be removed from the BO classes and put into a separate class i.e. Data Provider with methods for all of the Data Providers (ODBC,SQL,Oracle etc.) and changing from one database to another shouldn't be too difficult. On generating Stored Procs, I have little experience in Oracle so I cant give too much incite but probably a next few months when I have time i will attempt this.

Please post your feedback for the rest of us to learn from.

mr_lasseter replied on Monday, November 06, 2006

I looked at using CodeSmith at first, but opted to use MyGeneration (since I have already written templates for it before and its free). Using MyGeneration there is no need to create an ExtendedProperties table. MyGeneration allows you to create your own meta data that is stored in a file for each table, field, etc...

Also I am generating my procedures as such and I am using the System.Data.OracleClient.dll with a 9i database and everything seems to be working just fine.

-- Create the Package if it hasn't been already
DECLARE n VARCHAR(256);
BEGIN
SELECT OBJECT_NAME INTO n FROM SYS.ALL_OBJECTS WHERE OBJECT_NAME = 'MYGEN' AND OWNER = 'TIMS';
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXECUTE IMMEDIATE 'CREATE PACKAGE TIMS.MYGEN IS TYPE sqlcur IS REF CURSOR; END MYGEN;';
END;
/

CREATE OR REPLACE PROCEDURE "TIMS"."SP_T_BILL_SUMMARY_DETAIL_TYPES_GET"
(
     outCursor OUT TIMS.MYGEN.sqlcur
)
IS
BEGIN
    OPEN outCursor FOR
SELECT
  DETAIL_TYPE,
  DESCRIPTION
FROM T_BILL_SUMMARY_DETAIL_TYPES
;
END;
/