Automatically create C# wrapper classes around stored procedures

Question

I've inherited a rather large application that really could use some cleanup. There is data access code littered throughout the application. In code behinds, some in business logic classes, some inline in in classic asp pages.

What I'd like to do is refactor this code, removing all the data access code into a few DAL classes.

All the data access is done through stored procedures (Microsoft SQL 2000). There are 300-400 of them.

It seems like there should be an automated way to analyse the store procedures, and automatically generate c# methods for each of them, mapping the Method parameters to the stored procedure parameters, with a method returning a datatable.

I don't have any experience with ORM products, and I'm not sure if what I'm looking for is a full blown ORM, or just a 3rd party utility that will help generate simple wrappers around the sp calls.

Answer 1

If you have access to .NET Framework 3.5 and Linq to SQL, you can do it very easily, check this video:

LINQ to SQL: Using Stored Procedures

Using existing stored procedures and functions is easy with LINQ. We simply drag the stored procedures onto the O/R mapping surface and call them from the generated Data Context object.

Answer 2

I recommend you get a hold of Code Smith. The product includes a template for ORM and fully supports generating classes from DB Schemas (and I think Procs). You can then Code Gen all the objects you need.

Another option would be to use LINQ to SQL.

Answer 3

dragging and dropping the stored procedures onto a dataset design surface (in .net 2.0 and higher) generates a wrapping function

but if you have a lot of them to do, you might be better off using or writing a simple code generator

two options for this:

1. generate the wrapping code yourself as C# classes/methods
2. generate a dataset.xsd file then open it in visual studio and let the designer generate the classes/methods for you

the latter can be maintained via the dataset design surface, but may be tricky to get generated right (the first time)

Answer 4

My approach would be to think higher level first- create your data access classes and methods the best way you can to fit your needs for your existing or new code base. Then, use the existing procedure calls for your new objects.

I do not think you should consider any form of mass automation for this task.

Answer 5

Similar to Robert's suggestion, we've written our own version of Code Smith.

Our "Code Generator" has two parts: SQL & Classes.

SQL: Will generate the Update, Select & Delete stored procs.

C#: Will generate the classes and save the file as a .cs

We call: sp_MShelpcolumns 'tablename' to get a list of fields and data types and then do a replace.

Its not a perfect solution, but is very effective to getting the first 80% completed

Answer 6

Not for sure, but seems like this is exactly what you were asking for.

http://www.codeproject.com/KB/database/SPGenerator.aspx