Geeks With Blogs


Add to Google

Tim Hibbard CEO for EnGraph software

Update: 02/27/2007 added support for raw SQL Strings

When I build data access layers, I like to define a const for each column in the query like this:

const int CLIENTID = 0; const int STATUS = 1; const int FIRSTNAME = 2;

That way, I can pull data like this:

newClient.Name.FirstName = reader.GetString(FIRSTNAME);

And if something changes in the database, I know I only have to change the field that FIRSTNAME references.

However, sometimes, tables can be quite large and it becomes a pain in the wrist.  So I wrote a class that uses the EnterpriseLibrary and builds the code that defines the const for each column.  Then I can copy and paste it into my code:

This class can be consumed like this:

ExtractFieldNames x = new ExtractFieldNames("myConnectionString"); textBoxResults.Text = x.ExtractConstantsFromTable("myTableName);

using System; using System.Collections.Generic; using System.Text; using System.Data; using Microsoft.Practices.EnterpriseLibrary.Data; using Microsoft.Practices.EnterpriseLibrary.Data.Sql; namespace ExtractConstFromDBTable { /// <summary> /// This class pulls field names from a table in a database /// and builds c# code to define const. /// Let me know if you find this useful - /// </summary> /// <history> /// [Tim Hibbard] 02/14/2007 Created /// </history> public class ExtractFieldNames { #region --Variables-- private SqlDatabase db; #endregion #region --Public Methods-- /// <summary> /// Grabs all the colums from a specific table and builds the string /// that will define const for the table name /// </summary> /// <param name="tableName">The database table name</param> /// <returns>A string of code for the table</returns> /// <history> /// [Tim Hibbard] 02/14/2007 Created /// </history> public string ExtractConstantsFromTable(string tableName) { return ExtractConstantsFromSQLString("select top 1 * from " + tableName); } /// <summary> /// Grabs all the columns from a sql text and builds the string that will /// define const for the sql text /// </summary> /// <param name="sqlText">The text to grab</param> /// <returns>A string of const for the string</returns> /// <history> /// [Tim Hibbard] 02/27/2006 Created /// </history> public string ExtractConstantsFromSQLString(string sqlText) { string rv = ""; IDataReader reader = null; try { StringBuilder sb = new StringBuilder(); reader = db.ExecuteReader(CommandType.Text, sqlText); for (int i = 0; i < reader.FieldCount; i++) { //make the field name upper case and replace spaces with underscores string fieldName = reader.GetName(i).ToUpper().Replace(" ", "_"); //build the line of code sb.Append("const int " + fieldName + " = " + i.ToString() + ";" + Environment.NewLine); } //populate the return value from the string builder rv = sb.ToString(); } catch (Exception) { rv = ""; } finally { //clean up if (reader != null) { reader.Close(); reader = null; } } return rv; } #endregion #region --Constructors-- /// <summary> /// Constructor that takes connection string /// </summary> /// <param name="connectionString">Connection to database</param> /// <history> /// [Tim Hibbard] 02/14/2007 Created /// </history> public ExtractFieldNames(string connectionString) { db = new SqlDatabase(connectionString); } #endregion } }


Technorati tags: , , ,
Posted on Wednesday, February 14, 2007 12:50 PM | Back to top

Comments on this post: C# Code To Extract Const Int code from database

# re: C# Code To Extract Const Int code from database
Requesting Gravatar...
It's an interesting idea... You could use enumerations to achieve the same. If the data layer is abstracted I'm not sure why renaming columns would be an issue. You would have to maintain the stored procedures...

This method would be helpful if your application used native .Net data structures (DataTables and DataSets).
Left by Chuck Conway on Feb 16, 2007 8:50 AM

# re: C# Code To Extract Const Int code from database
Requesting Gravatar...
Thanks for the comment, Chuck. I could use enumerations, but as a personal choice, I prefer constants.

I plan on modifying the code to work with stored procedures and sql text queries. I just haven't needed that functionality yet :)
Left by Tim Hibbard on Feb 16, 2007 9:07 AM

Your comment:
 (will show your gravatar)

Copyright © Tim Hibbard | Powered by: