Summary: Get LLBLGen if you do SQL Server work in .NET. Get LLBLGen Pro if you like LLBLGen.

LLBLGen is an open source code generator that creates a full data-tier to access Microsoft SQL Server databases with the .NET framework. It was the predecessor of LLBLGen Pro, a commercial product. LLBLGen is a great time-saver for small database-heavy ASP.net projects and also an effective gateway drug for LLBLGen Pro.

What LLBLGen Does

LLBLGen generates two files for each database table selected; one is an SQL script (to be run in SQL Query Analyzer, probably) that adds a list of stored procedures to the database; the other file generated is a C# or VB.net class that accesses these procedures and exposes the properties of a table row directly as properties on instances of the class. Actions such as insert, update and delete are then methods of the class.

Specifically, LLBLGen can potentially generate stored procedures and methods for the following database actions:

  • Inserting, updating and deleting records
  • Selecting single records by primary key or 'unique' constraints (Requires that the key or constraint be set on the table)
  • Select using identity fields and GUIDs
  • Select all, or select all by a foreign key relationship (Returns a System.Data.DataSet)
  • Delete all using primary key or a foreign key relationship
  • Update all using a foreign key
All of these are accessible directly through the generates C# class for a given t able, for all relationships. The properties are exposed as System.Data.SqlDbType types, rather than as the native C# or VB.net types. Note that to get any of the relationships, you have to actually create a foreign key relationship in the database - LLBLGen doesn't infer anything from field names. (Time to key those lookup tables!) Furthermore, you are now accessing your SQL database through an inheritable class, so you can extend it to do your evil bidding.

What LLBLGen Is

LLBLGen itself is a .NET program with a simple Windows.Forms interface that walks you through connecting to a database, selecting a catalog, choosing tables to generate and selecting output language/style and directory. The interface is not beautiful or perfect, but it works fine as a generator.

Also, LLBLGen is an Open Source program, which means that if you want to play around with the generator and make it do more than it already does, you're free to do that. It is naturally cost-free and can be downloaded directly from the web site. But if you're going to invest that kind of time, your time will be better spent by buying LLBLGen Pro and experimenting with the Template Studio and the generator SDK that are included.

What LLBLGen Doesn't Do

LLBLGen will not help you with such things as complicated SQL queries or M:N relationships through intermediate tables. (Not directly.) LLBLGen also does not generate a project file, so you have to manually import all of the files, and if you want a .NET library, you'll have to build it yourself.

LLBLGen Is A Very Good, Useful Program

That's the best way to put it. LLBLGen saves you all the time you would have otherwise spent writing boilerplate stored procedures and queries to do basic tasks like updating and deleting. Instead, you can do other more important things with your time, like writing your application logic, or writing the complicated queries and so forth. I say this on my experience as an ASP.net programmer. I used LLBLGen for several months before my employer sprung for a license of LLBLGen Pro, at which point everyone doing ASP.net at the company (which is 5 of us now) started using it. As much as I dislike working in Windows and ASP.NET day in and day out, LLBGen and LLBLGen Pro ease the pain greatly.

I keep yakking about LLBLGen Pro. I'm going to node it separately pretty soon, but don't hold your breath, because it may take awhile. It is a huge step up in abstraction, scope and usefulness from LLBLGen. There are also a host of accompanying tools and gadgets surrounding it that I haven't begun to use.

Note that LLBLGen has a slight advantage over LLBLGen Pro in the arena of speed, for a few reasons. The first is that LLBLGen uses SQL Stored Procedures for its database operation, which are pre-compiled, whereas LLBLGen Pro uses generated SQL which must be compiled by the server each time. (By compiled, I mean parsed. There would be query plan re-use, though.) Secondly, LLBLGen is not a very abstract tool; it only interfaces to a small number of concrete database operations. LLBLGen Pro, on the other hand, is a marvel of abstraction and flexibility. For equivalent tasks, LLBLGen should be expected to outperform LLBLGen Pro. I haven't made measurements of this, though.

Sources:

  • http://www.sd.nl - Solutions Design
  • http://www.llblgen.com - LLBLGen Pro