The Table Assimilator is a freely available tool we created to automatically generate the table and record access code for most of our database access needs.  We've decided to release it as a free download, because nothing quite like it is available out there right now.  It's not the best solution for all situations, but it's rock solid stable, has excellent performance, and very easy to use.

• Currently, it will only generate code in Visual Basic .NET, and only for the .NET Framework v2.0 or higher (since it uses generics).  An older version for the .NET Framework v1.0 is available - e-mail me if you would like a copy.
• It will only work against tables in an MS SQL Server 2000 or MS SQL Server 2005 database, including Express Edition.
• The tables it generates code for must have a primary key defined, and it must be of the SQL datatype bigint (or Long in .NET).
• None of the columns in the tables it generates code for can permit null values.
• Executing SQL scripts using the SMO method only works against a server running SQL Server 2005, use Transactional for SQL Server 2000.

You can download it from our website by clicking here (you will need to register for a free account first).  The most recent version is v2.0.2, released on 9/1/2007.

After running the installer, launch the program by opening your Start menu and clicking on Table Assimilator.

You'll first be asked for connection information - which server to connect to (for example, 127.0.0.1, or localhost\SQLEXPRESS) and which database to open.  You'll also be asked for three names - the name of the table to generate code for, the record class name to generate (an instance of this class will represent a single row in the table) and the table class name to generate (this class will hold your custom queries for the table).

For example, if you have a table named Users, you might want to give a name of User to the record class, and Users to the table class.

Click on Generate Record Class and the code will be generated and copied to your clipboard.  Create a new file to paste this code into, and repeat for the table class.  You'll also probably have to add a reference to your project to the BYO.DBGenCore assembly (which has the required BYO.DBCore namespace).  The Table Assimilator installer should have put this assembly into your GAC for optimal performance, as well as configured Visual Studio's Add Reference dialog to show the BYO.DBGenCore assembly.

More complete documentation is coming soon, but for now...

Configuring connection settings:
  DBSettings.DatabaseInfo = New DBInfo("initial catalog=DATABASE;data source=SERVER;user id=USER;password=PASS")

Basic functionality of DBRecord classes:
  New() - Create a new row (only added to the table upon calling .Save())
  New(long) - Load the row with the specified primary key
  Save() - Save the record to the table
  Delete() - Delete the record from the table

Basic functionality of DBTable classes:
  .Instance - The shared instance of the table controller (where you put custom queries)
  .Controller - The base controller used for loading/saving data

Example of a custom query (should be placed at the end of the generated DBTable class):

Public Function GetCustomersByState(strState As String) As List(Of Customer)
  Dim cmd As SqlCommand = Controller.NewCommand("SELECT * FROM Customers WHERE State = @State")
  cmd.Parameters.Add("@State", SqlDbType.VarChar, 2).Value = strState
  Return Controller.GetMultipleRows(cmd)
End Function

The BYO.DBGenCore assembly contains a second namespace, BYO.DBCodeGen.  You can interface with this class to automate the database code generation process, if needed.

You can also use the DBUtils.ExecuteSQLScript() function to execute SQL scripts in a number of ways.  One uses transactions and breaks apart scripts executing each command as it reaches a line with just the word "GO" on it.  This works for most scripts generated by the SQL Server Management Studio (SQL 2005) or SQL Enterprise Manager (SQL 2000).  Another will execute scripts using SQL Server 2005's SMO library, running them exactly as if they were run from within an SQL Query Analyzer window.