There are tons of companies that vend data access solutions and frameworks. There are several built into/around Visual Studio. I've used SubSonic, there are others.
Then there are the tools that allow you to create your own code. Automatic Code Generation. Code that writes code. Think Resharper, Codesmith, and others.
My issue with Automatic Code Generation tools is that I generally have to learn another language to have code generated. (sigh) I already work in way too many languages(HTML, VB.NET/ASP.NET, Javascript, T-SQL), I'd like to avoid another. Since I'm pretty good at T-SQL and most of the code that I want is centered around database objects, I thought I'd just write my own.
Too, the frameworks are too much. I work with the Dynamics GP database which has about 2000 tables and 25000 stored procedures. Some of these frameworks want to create database objects (scaffold) the whole deal. Or, even if they offer some sort of throttling, they still loop through all of the objects and it just takes too long. I found myself waiting 10 minutes when I added a stored procedure.
So...
DEVSHED data access is an Automatic Code Generation tool that is based entirely on T-SQL. It writes text files that are VB classes that do all that I need.
Scripts That Write CRUD Stored Procedures
These scripts create stored procedures in SQL for each CRUD type. The scipts use the primary index on the table to determine the parameters.
sp_select _by_ID
sp_insert
sp_update
sp_delete
sp_merge
sp_fields (not really a CRUD) procedure, but in my mind this is the area where it is grouped)
Scripts That Write VB Classes
Create classes for table objects - these scripts create .vb files that act as VB class representations of SQL tables. The classes have properties for each field and NEW, SAVE, UPDATE and DELETE methods.
The main class is sp_CreateVBClassForATable, it uses these helper files:
Scripts That Dump SQL Opbjects To A Text File
sp_SPs - This script writes a .vb text file that forms part of the data access tools that I use in VB.NET. It creates a method for each (or selected) stored procedures in the database.
This code will dump the text of any object that you want to a text file. I use it as a backup, and to easily search a code base for something.
Scripts That Form The Data Access Wrapper For The Enterprise Library DAB
StoredProcedure - this class is called by the methods in sp_SPs
CommandParameter - class to hold SP parameters, used by sp_StoredProcedure
Example of how to use the above two classes
Note:
All the data access is based around the Microsoft Enterprise Library Data Access Block. That code needs to be installed on your machine. Currently we support version 4.1