Can MyGeneration templates do this?

[MarFarMa]

I haven't downloaded MyGeneration yet. I'm trying to decide if it will help in my current project.

I have a existing (legacy) Oracle schema. I'm building a .Net library to access it. I'm currently hand coding business objects by building an appropriate SQL select, which may include several table joins - potentially including multiple joins of the same table with different alias.

Once I've defined the SQL that defines the object data, I hand code the business object class definition. I load the object data via SQL selects and parameters. (Save will generally be handled by pre-existing stored procedures.) Once I have defined the base object, I extract a public interface.

Once I have the object and the interface, I use a VS2003 Custom Tool to generate a strongly typed collection and an interface to it. ( I've extracted the updating the object data from the datareader - and use the LoadFromReader method of the base business object in loading the collection in the generated collection load. )

Coding the base business object by hand from the SQL has become quite painful. I'd love to be able to generate the object from a template (and replace the CustomTool collection generation at the same time.) But I'm not sure your product will be able to do that.

Make me happy - tell me that I can define a template that will work from my sql statements directly - without having to create database views for each one first.

If views are the only way to generate - can I generate from a view that only ever exists in my development environment - while substituting my SQL in the generated code, so that I won't have to deploy bunches of new views in the schema to go live with the library?

What do you say? Will MyGeneration help release me from my misery?

MarFarMa

[mike.griffin]

MyGeneration can Generate anything -- we have an excellent architecture called dOOdads that can generate your entire data/business objects (that blows the strongly typed datasets away). dOOdads can work off of only stored procedures if you wish.

I suggest you download it and give it a try, you can see dOOdads on our main menu.

[MarFarMa]

I don't think I can use dOOdads for this project.

Point me in the right direction - how do I get started. How do I define the SQL statement and them bring back its schema info from within myGeneration?

I downloaded myGeneration and started reading the tutorials. All the data access examples are table at a time based examples. I need to define objects that get their data from several joined tables.

I want to define an SQL statement to myGeneration, and then have the application connect to the database, then pull back the column schema metadata using IDataReader.GetSchemaTable(). At which point I'd like to update some userMetaData, such as renaming the member with a reasonable name and indicating whether a column can return nulls (allowing mapping to a NullableType rather than a C# native type), etc.

Point me in the right direction - how do I get started. How do I define the SQL statement and them bring back its schema info from within myGeneration?

Pauli

[justin.greenwood]

The Quickstart Guide (below) is an excellent place to start for beginners. It takes you through common sense samples with northwind, and will give you a feel for what dOOdads has to offer.

http://www.mygenerationsoftware.com/dOOdads/doodadsQuickRefCSharp.pdf

When dealing with joins in an API, things can get nasty. In dOOdads, you create views for the join queries you need and then generate dOOdad objects for those views. It works really well, believe it or not.

[MarFarMa]

Justin, thanks for the reply.

So - views. I wish Mike had responded to my actual question, rather than giving me a blyth \"can generate anything\" response. Oh well - question part two:

Can I use a view during generation, but replace the view with the equivalent SQL in the final code? I really don't want to be adding a bunch of views into the schema to support this.

Pauli

[mgnoonan]

You can load your business objects from inline SQL, a stored procedure, a view or a table very easily. But you will need a table or view to provide the schema structure of the business object.

So at a minimum, you'll have to create the views in your dev environment so you can generate your objects. But they will be read-only, as you will be unable to Save without a concrete data source.

Alternatively, if you do need to Save any changes, you could load a table-based object from inline SQL (or a proc, or a view) as long as all of the source table's columns are included in the results.

[justin.greenwood]

An idea for a new dOOdads template could be to enter a query as input to the template, create a view, get the schema info, generate the object, and then drop the view. Just an idea for the future - but believe it or not, if you can use views, they are much better than inline sql. When you use a view, you may or may not get some performance improvement, but you keep the queries in the database. Many similar queries used in the app could be combined into one view and one dOOdads object. There is no downside to using views opposed to inline SQL. Another cool thing is that if you change the view or a table used in the view and then regenerate the dOOdad code, you will get a bunch of compile errors for any removed or changed columns that were referenced in your app. If you use inline SQL you will have all runtime errors. You will love the way that a strongly typed DAL protects you from runtime errors due to schema changes.