Demonstrating Attributes and Reflection in .NET - Generating SQL using .NET Reflection
(Page 5 of 8 )
This is where you will start to see the beauty of Attributes and Reflection in action. We are going to write code that inspects objects and generates all of the required SQL statements for managing persistence. Additionally, we will write code to generate SQL used to define a table within SQL Server.
Let's start with a simple method that uses .NET Reflection to determine the table name that a class maps to. This method is used quite a bit by other methods within the SqlGenerator class.
public string GetQualifiedTableName(object ob)
{
Type t = ob.GetType();
// iterate through each attribute in the list
foreach(Attribute att in t.GetCustomAttributes(false))
{
// grab the custom attribute
DBTable dta = att as DBTable;
// make sure it is valid
if(null != dta)
{
// if it is, return the owner.table_name. Note that
// we stop processing here because there can only be one
// table entry/type.
return dta.TableOwner +
"." +
dta.TableName;
}
}
// if we got here, there was not a table attribute specified
// for the type of object passed in. We need to throw an exception
throw new Exception("The DBTable attribute must be specified " +
"for the SQL generator to act on the object.");
}The GetQualifiedTableName method above iterates through the list of custom attributes applied to the object passed in, and attempts to cast the attribute to our custom DbTable attribute. If the attribute is a DbTable attribute, the method returns the fully qualified name.
Now let's take a look at the GenerateSQLInsertStatement method exposed by the SqlGenerator class. As the signature implies, this method generates the appropriate insert SQL statement to add a row to a table for an object.
public string GenerateSQLInsertStatement(object ob)
{
// initialize SQL statement
string strSQLStatement = "INSERT INTO ";
// grab the type
Type t = ob.GetType();
// grab the table name that we are inserting into
string strTableName = GetQualifiedTableName(ob);
// the statement me want to generate here is
// INSER INTO TABLE_NAME(COL1,COL2) VALUES('1',3958)
strSQLStatement += strTableName;
strSQLStatement += " (";
strSQLStatement += GetColumnList(ob) + ") VALUES(";
strSQLStatement += GetValueList(ob) + ")";
// return the SQL statement.
return strSQLStatement;
}This method initializes the INSERT statement, extracts the fully qualified table name (using our GetQualifiedTableName method), and appends the column and value lists to the SQL statement. Note that this method invokes two methods called GetColumnList and GetValueList. We will cover these methods next.
private string GetValueList(Object ob)
{
Type t = ob.GetType();
// will contain the list of values
string strValueList = "";
// flag to indicate whether or not this is the
bool bFirstColumn = true;
// first item in the value list.
// iterate through each property
foreach(PropertyInfo p in t.GetProperties())
{
// grab the custom attributes
foreach(Attribute att in p.GetCustomAttributes(false))
{
// grab the custom attribute
DBColumn dca = att as DBColumn;
// make sure it is valid
if(null != dca)
{
// if this is not the first column we
// have hit, then we need to preceed
// the column with a comma.
if(!bFirstColumn)
{
strValueList += ", ";
}
else
{
bFirstColumn = false;
}
// this sample code assumes properties
// are not collecections of objects.
// this dummy array of objects is
// required to satifsy the GetValue
// method.
object []obRetVal = new Object[0];
// append the value to the list
strValueList += "'" +
p.GetValue(ob, obRetVal).ToString() +
"'";
}
}
}
// return the list of columns.
return strValueList;
}GetValueList starts by iterating through all the properties within the object that was passed in. For each property, it iterates through each custom attribute.
When the method encounters a DbColumn attribute, the value of the underlying property is appended to a comma-separated list. The resulting comma-separated list is returned to the caller.
I won't bore you with the full implementation of GetColumnList. It is identical to GetValueList, except it formulates a comma-separated column list by appending the DBColumn.ColumnName property instead of interrogating the value of the property of the class as follows:
// if this is not the first column we have hit,
// then we need to preceed the column with a comma.
if(!bFirstColumn)
{
strColumnList += ", ";
}
else
{
bFirstColumn = false;
}
// apend the column to the list
strColumnList += dca.ColumnName;The code in the download link at the bottom of this page contains methods for generating Delete, Update, Insert, and SelectSQL statements.
If you wish to explore these methods, open the ObjectManager project and examine the GenerateSQLDeleteStatement, GenerateSQLUpdateStatement and GenerateSQLSelectStatement methods.
Managing Objects in the DatabaseNow that we have a foundation for creating SQL statements for objects, we may now design an object manager that provides an interface for storing and retrieving items within a SQL Server Database.
We will examine two methods from our SqlObjectManager class that use the custom SqlGenerator class to insert and retrieve objects from the database.
Before we jump into some detailed code, take a look at a partial view of the SqlObjectManager's class definition below:
(Note that the SqlObjectManager is geared to operate with the SqlClient data provider).
///
/// Manages DB objects that support the custom DB database
/// attributes
///
public class SqlObjectManager
{
///
/// the Connection to the database
///
private SqlConnection _conn;
///
/// Generates SQL statements for objects
///
private SqlGenerator _sg = new SqlGenerator();
. . .
. . .
}The SqlObjectManager class definition contains an SqlConnection member variable. This variable is accessed via the class's public Connection property.
The user of this class must specify the location of the underlying database by setting this connection property. Note that the class maintains an instance of SqlGenerator. This instance is used to formulate the actual SQL statements.
First we will add an object to the underlying database:
public void AddToRepository(object ob)
{
SqlCommand _comm = new SqlCommand();
// set the command text to the Insert SQL statement
_comm.CommandText = _sg.GenerateSQLInsertStatement(ob);
// set the connection object
_comm.Connection = _conn;
// lets insert the row
_comm.ExecuteNonQuery();
}The above code block first creates a SqlCommand object, and then sets the CommandText property to the SQL statement generated by the SqlGenerator.GenerateSQLInsertStatement method we created earlier. The last line of code executes the SQL statement.
The RetrieveObject method shown below populates an object's properties with data from a row within a table:
public void RetrieveObject(object ob)
{
// we can't construct a where clause unless the primary
// key is specified
if(this.DoesObjectHaveAPrimaryKeyAttribute(ob))
{
// given this has a primary key, the primary key must
// have all of its values set.
if(this.AreAllPrimaryKeyAttributesSet(ob))
{
SqlCommand _comm = new SqlCommand();
DataSet ds = new DataSet();
// put together the command
_comm.CommandText = _sg.GenerateSQLSelectStatement(ob);
_comm.Connection = _conn;
// create the adapter
SqlDataAdapter da = new SqlDataAdapter(_comm);
// fill the DataSet
int iNumRows = da.Fill(ds);
if(iNumRows > 0)
{
// set the class members with
// the data retrieved
this.SetClassMembers(ob,
ds.Tables[0].Rows[iNumRows - 1]);
}
else
{
throw new Exception("There was no row in " +
"the repository that matched the " +
"specified criteria.");
}
}
else
{
throw new Exception("All primary key properties " +
"must be set before retrieving a unique row.");
}
}
else
{
throw new Exception("Object must have a primary key " +
"to populate it with a unique row.");
}
}This method must first verify that the object has at least one DBPrimaryKeyField attribute by invoking the DoesObjectHaveAPrimaryKeyAttribute method.
Next, the RetrieveObject method must ensure that all of the properties marked with the DBPrimaryKeyField attribute actually contain a value. The AreAllPrimaryKeyAttributesSet method performs this logic.
Next, RetrieveObject sets up the SqlCommand object by invoking the SqlGenerator.GenerateSQLSelectStatement, followed by issuing the SqlDataAdapter.Fill method to populate a DataSet. Finally the properties of the class are set with the values retrieved from the database using the custom SetClassMembers method.
The last method we will cover within the SqlObjectManager class creates the underlying table that a class represents. This method is very useful for quick table creation:
public void CreateTableForObject(object ob)
{
SqlCommand _comm = new SqlCommand();
// put together the command
_comm.CommandText = _sg.GenerateCreateTableSQL(ob);
_comm.Connection = _conn;
// execute the create table sql
_comm.ExecuteNonQuery();
}This above method creates an SqlCommand object, sets the CommandText and Connection properties, and finally executes the SQL that creates the table.
Next: Putting it all together >>
More ASP.NET Articles
More By Wrox Team
