I think it would be a cool feature if I run a code generator that created a class that I could use to call stored procedures with. And that inside that class it uses StrataFrame's connection to the database, etc. This way I dont have to remember the entire name of the stored procedure nor do i have to remember all the parameters.



For Example



Public Class DataAccessLayer



Public Shared ConnectionString As String = System.Configuration.ConfigurationSettings.AppSettings.Get("ConnectionString")





Public Shared Function STIGetDifferentLumberTypes(ByVal @PurchaseOrderID As Integer, ByVal @ProductionID As Integer, ByVal @ReturnID As Integer, ByVal @TransactionID As Integer) As DataView

'Retrieve the parameter set

Dim storedParams() As SqlParameter = New SqlParameter(3) {}

storedParams = SqlHelperParameterCache.GetSpParameterSet(ConnectionString ,"STIGetDifferentLumberTypes")



storedParams(0).Value = @PurchaseOrderID

storedParams(1).Value = @ProductionID

storedParams(2).Value = @ReturnID

storedParams(3).Value = @TransactionID



Dim ds As DataSet



ds = SqlHelper.ExecuteDataset(ConnectionString,CommandType.StoredProcedure,"STIGetDifferentLumberTypes", storedParams)



Return ds.Tables(0).DefaultView

End Function



End Class



Thanks

You might want to check out CodeSmith (http://www.codesmithtools.com). It is a code generator that can code against the schema of a database directly. I.e. it could build a class of your liking for each sproc in the database. You can also use an xml file to define parameters, which allows for some pretty complicated code generation.

Hey Greg, Thanks for the reply. I've downloaded an installed a demo of CodeSmith, the only problem is that is a pretty robust product and I haven't the time to sit down with it an fully evaluate it.



Would you be able to point me in the direction as to how to generate the class I mentioned above using CodeSmith?



Also, if its not too much trouble , is there anything about this program that you really like you think I should look at? It's not too expensive so if it offers me a few features that I find useful it would be worth it to me.



Thanks

Mike,



Yep, it's pretty deep. There are three features that might be of great help:



- This is just .net. I.e. you can do pretty much what ever you can do with .net within this generator.



- Note you can define properties that are string arrays. This could be helpful if you need to generate code for a subset of sprocs (took me a while to figure this out).



- You can use xml files to control the generation, including using an xsd file to define the schema. This can be helpful if the generation requires some really complex input data.



- There is a SchemaExplorer that allows you to "reflect" into a database, using the schema of tables, getting lists of sprocs, etc.



- If you get the professional version, you can use this to build a sproc for a table and build code to access the sproc, at the same time. If you get standard edition, you would need two templates.



See http://www.codesmithtools.com/usersguide/, the Basic Syntax section to get an idea of how to get things done, then see "Driving Templates with Metadata", especially the "Using SchemaExplorer".



There support is pretty good. The developer is fairly available. Not as good as SF, but better than most. In fact somebody has posted a question that is very similar to yours :



http://community.codesmithtools.com/forums/t/6935.aspx

Trent? Ben? Hello?



I really appreciate Greg's advice, but I'm puzzled that I havent heard from you guys.

I could see the benefit of this class being created.  And this may be something that we consider in the next major release of StrataFrame.  Obviously the more things like this that can be done for the developer the stronger the framework becomes.  So I can see your point.  But as for a short term forecast, I do not see this being integrated into the framework.  The next major release of SF will have even more tools to help in app development, some of which may be code generation tools like this.

I'm anxious to hear about those new features. Hope I dont have to wait TOO long!