StrataFrame Forum / StrataFrame Application Framework / Enhancement Requests / Class for calling Stored Procedures... / Latest Posts

RE: Class for calling Stored Procedures...

StarkMike — Tue, 31 Jul 2007 11:59:29 GMT

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

RE: Class for calling Stored Procedures...

Trent L. Taylor — Tue, 31 Jul 2007 10:45:39 GMT

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.

RE: Class for calling Stored Procedures...

StarkMike — Tue, 31 Jul 2007 10:18:42 GMT

Trent? Ben? Hello?

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

RE: Class for calling Stored Procedures...

Greg McGuffey — Thu, 19 Jul 2007 11:19:19 GMT

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 [url]http://www.codesmithtools.com/usersguide/[/url], 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 :D :

[url]http://community.codesmithtools.com/forums/t/6935.aspx[/url]

RE: Class for calling Stored Procedures...

StarkMike — Thu, 19 Jul 2007 08:07:43 GMT

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 :hehe:, 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

RE: Class for calling Stored Procedures...

Greg McGuffey — Wed, 18 Jul 2007 15:16:15 GMT

You might want to check out CodeSmith ([url]http://www.codesmithtools.com[/url]). 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.

Class for calling Stored Procedures...

StarkMike — Wed, 18 Jul 2007 07:24:53 GMT

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