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 - thibbard@engraph.com
/// </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:
C#,
code,
.NET,
Database