Code Focused

Generating .NET POCO Classes for SQL Query Results

A free tool to reduce coding for developers who like to surface T-SQL database recordsets in a simple collection of customized POCO objects.

• By Joe Kunk
• 12/11/2012

Every so often, I find myself wishing that I had a utility of one type or another, to save me from rewriting code. In many coding sessions, I need to generate a .NET class with appropriately typed, public properties to contain the results of a database query or stored procedure. I finally wrote the utility, and now present it here. It's written in Visual Basic, but generates code for both Visual Basic and C#.

You may be wondering, why not just be happy with the datasets returned from ADO.NET? Or the IEnumerable collections returned from tools such as LINQ to SQL or Entity Framework? If those tools meet your needs, then there's nothing wrong with continuing to use them. I happen to like the efficiency and simplicity of customized Plain Old CLR Objects (POCO) classes to hold data. I override the ToString method to clearly identify each instance for data binding, or reviewing the collection during debugging. I typically add an Export method to format the data as a tab-delimited record for easy offline persistence. Having a POCO class makes it easy to add those methods, or others that you find useful.

Note that the code generated from Object Relational Mapping (ORM) tools such as Entity Framework, LINQ to SQL’s SQLMetal, NHibernate and SubSonic generate code for the schema objects contained with the database--not the results of queries. Regenerating the code due to a database schema change can overwrite your customizations if you're not careful. Managing a custom POCO class can be a better option.

Any hint of dynamic T-SQL queries should ring warning bells for security minded developers. Dynamic queries are vulnerable to SQL injection attacks, one of the most common database attack vectors. That's not a concern for this utility because the dynamic query is only used by the developer during coding to obtain the resulting schema. The dynamic query is not actually executed against the database by the utility. Dynamic T-SQL queries built from non-validated user input should never be included in a production application.

Obtaining the Schema
To generate a POCO class matching a query or stored procedure, use the GetSchemaTable method of the System.Data.SqlClient.SQLDataReader class. It returns a data table with one row for each column returned from the query. Each row contains more than 20 attributes including the column name, size, numeric precision, numeric scaling, data type, AllowDBNull property and so on.

Fortunately, the ExecuteReader method has a CommandBehavior.SchemaOnly option that instructs the SqlDataReader to return just the results schema, rather than run the query or stored procedure. This allows you to gather the desired information without the performance hit of actually running the command, or the risk of altering data by a stored procedure. From a T-SQL perspective, the SchemaOnly option forces the SQLDataReader to precede the command with a SET FMTONLY ON statement. Listing 1 shows the Visual Basic ADO.NET source code to obtain a data table of the results schema for a database query.

Using the Utility
The code download has the full Visual Studio solution. ResultCodeGen.exe is compiled against the .NET 3.5 Client Profile. It's fully compatible with Windows 7; no external libraries are used.

Enter the connection string at the top of the form. The default connection string is stored in the ConnectionString application setting of the SQLQueryResult_CodeGen project. For a database query, enter the T-SQL statement in the Query Text box. Enter the Class Name, choose Visual C# or Visual Basic output, and click Generate. The SchemaTable, as returned by the SQLDataReader, is displayed in the grid. The generated source code is displayed below the grid. Figure 1 shows the same query of the Chinook sample database generated for C# and Visual Basic.

[Click on image for larger view.]

Figure 1. Code generated from a database query.

Each field is represented by a public property with the query column’s name. It's possible that a column was not assigned a name, such as when the column results from an expression. In those cases, the field name is "FIELDxxx" where xxx is the column ordinal as a three-digit number. SQL Server allows field names to begin with a numeric character, which is not permitted for .NET property names. A leading underscore is added to the property name in that case.

The generated, source code contains the original database-field data type and length, or precision, as a comment. This information can be useful knowledge when later working with the code. Clicking in the generated code area will prompt you to copy the code to the clipboard, making it easy to paste the code into your application.

The process for a stored procedure is similar. When you click on the Stored Procedure radio button, the form, shown in Figure 2, appears in order to prompt for the stored procedure name and the list of parameter names and values. When saved, it populates the Query Text with a delimited string in a custom format that is easy to parse. Clicking Generate only returns the result schema and does not actually execute the stored procedure.

Generating the code requires the simple use of a case statement and formatted strings. It does not require CodeDom or other code generation technologies. This helps keep the utility easy to understand and maintain.

[Click on image for larger view.]

Figure 2. Code generated from a database stored procedure.

Testing the Utility
I wrote a test for the Chinook sample database to verify that stored procedures are not actually executed when the SchemaOnly option is used. Listing 2 shows the test table and stored procedure. A row got added into the test table when I executed the stored procedure manually, but not when it was accessed by this utility with the SchemaOnly parameter. This test confirms that the stored procedure was not executed.

I added a table to the Chinook database that contained a column of each of the SQL Server data types to ensure that each would be generated correctly. Listing 3 shows the T-SQL script to create the DataTypes table. The generated, query results code was successfully compiled, for both Visual Basic and C#. Interestingly enough, the DataTypes table did not need to contain any data for this testing; the GetSchemaTable method works fine on an empty query result.

Still Gold in Those Hills
Many .NET developers have moved beyond data access with ADO.NET datasets to use ORM tools such as Entity Framework, LINQ to SQL, nHibernate, and SubSonic, among others. Sometimes developers embrace the latest technologies and wonder about those who still use the older frameworks. The usefulness of the ADO.NET GetSchemaTable method as shown here is a good reminder that older .NET development techniques still have great value. A top-notch developer needs to be skilled in all areas of.NET development, including the older frameworks.

Next Steps
This utility is presented here as a free tool to reduce coding for developers, like me, who like to surface T-SQL database recordsets in a simple collection of customized POCO objects whenever possible. If you're comfortable using the entities generated by your ORM, then by all means continue to do so.

Possible enhancements to the utility include an option to generate the code to fill the generated POCO class from your favorite database access framework, or to implement the INotifyPropertyChanged interface to allow the POCO object to track changes to its properties. I hope you find this utility useful in your coding efforts.

• Get Code Download