Note by author:
Since writing this, I have expanded on this idea quite a bit. I have written a lightweight ORM class library that I call EntityJustWorks.
The full project can be found on GitHub or CodePlex.
EntityJustWorks not only goes from a class to DataTable (below), but also provides:
- SQL 'SELECT' statement to a List<T> of populated classes, each one resembling a row
Security Warning:
This library generates dynamic SQL, and has functions that generate SQL and then immediately executes it. While it its true that all strings funnel through the function Helper.EscapeSingleQuotes, this can be defeated in various ways and only parameterized SQL should be considered SAFE. If you have no need for them, I recommend stripping semicolons ; and dashes --. Also there are some Unicode characters that can be interpreted as a single quote or may be converted to one when changing encodings. Additionally, there are Unicode characters that can crash .NET code, but mainly controls (think TextBox). You almost certainly should impose a white list:
string clean = new string(dirty.Where(c => "abcdefghijklmnopqrstuvwxyz0123456789.,\"_ !@".Contains(c)).ToArray());
PLEASE USE the SQLScript.StoredProcedure and DatabaseQuery.StoredProcedure classes to generate SQL for you, as the scripts it produces is parameterized. All of the functions can be altered to generate parameterized instead of sanitized scripts. Ever since people have started using this, I have been maintaining backwards compatibility. However, I may break this in the future, as I do not wish to teach one who is learning dangerous/bad habits. This project is a few years old, and its already showing its age. What is probably needed here is a total re-write, deprecating this version while keep it available for legacy users after slapping big warnings all over the place. This project was designed to generate the SQL scripts for standing up a database for a project, using only MY input as data. This project was never designed to process a USER'S input.! Even if the data isn't coming from an adversary, client/user/manually entered data is notoriously inconsistent. Please do not use this code on any input that did not come from you, without first implementing parameterization. Again, please see the SQLScript.StoredProcedure class for inspiration on how to do that.
In this post I showed how to create a DataTable where the column names and types matched the properties of a class. In this post, we work the opposite direction and start with a Data-First approach. Given an SQL Database, we can easily convert a query to a DataTable using System.Data's SqlDataAdapter.Fill method. Now, given a DataTable, I show you here how to use Reflection to populate a class's public properties from a DataRow in a DataTable (or a List<> of classes, one from each DataRow in the DataTable) where the ColumnName matches the name of the public property in the class exactly (case-sensitive).
If the DataTable has extra columns that don't match up to a property in the class, they are ignored. If the DataTable is missing columns to match a class property, that property is ignored and left at the default value for that type (since it is a property). If you desire the ColumnName/PropertyInfo.Name matching behavior to be case insensitive, simply modify the line that compares the two strings (PropertyInfo.Name and DataColumn.ColumnName) to include a call to String.ToUpper() or String.ToLower() for each name.
If you paying close attention, or have ever attempted this kind of thing before, you are probably thinking to yourself that the most laborious (and error-prone) process is going to be creating the C# classes plus their many auto-properties that have to match the columns of a table, all manually. Well, take solace in the fact that I already thought of this and created a solution to generate C# class object code files from a DataTable using CodeDOM. It even implements a little hack to generate the properties as auto-properties (something not supported by CodeDOM) for clean, compact code that isn't bloated with private backing fields, and full getter/setter implementation.
Ultimately, the goal is to have a full, end-to-end, class-to-DataTable-to-SQL and back-again class library solution. Something like a poor-man's Entity Framework, or minimum-viable ORM. So stay alert for the next piece that will bring these wagons 'round full-circle: Automatic generation of SQL CREATE, INSERT INTO, and UPDATE scripts from a DataTable, which was generated from a C# class object, which can be generated from a DataTable, which can be generated by a SQL Database, which can be... well you get the idea.
This code has been tested and is a a little more robust than some of the equivalent samples I have been finding on StackOverflow (such as being able to handle properties of type Nullable<>. However there probably exists some conditions or use cases that I have not thought of, so please feel free to leave a comment if you find a way I can improve this class or have a feature request. In the next paragraph, I describe what the code is doing, or if you don't care, you can jump straight to the code below it. Enjoy.
How it works: Fist we get a list of PropertyInfo from the class. This will effectively be a list of properties in that class that we will want to fill. PropertyInfo exposes the Name property and the SetValue method, which takes an object and a value as parameters.
We are going to make three nested loops to do this (one for each DataRow, one for each PropertyInfo and one for each DataColumn) and return a List of classes, each one filled out from a single row in the DataTable. It is possible to fill out one class provided a DataTable and row index in only two nested loops, and this post will provide that example too.
For each row in DataTable.Rows, we will need to loop through each property (to fill them) and then loop through each DataTable's DataColumn and match the PropertyInfo.Name to the DataColumn.ColumnName. We then call the PropertyInfo's SetValue method. This function will take advantage of generics so that we can pass in any class as a parameter.
Here is the code:
public static class Helper
{
public static class Table
{
/// <summary>
/// Fills the public properties of a class from the first row of a DataTable
/// where the name of the property matches the column name from that DataTable.
/// </summary>
/// <param name="Table">A DataTable that contains the data.</param>
/// <returns>A class of type T with its public properties matching column names
/// set to the values from the first row in the DataTable.</returns>
public static T ToClass<T>(DataTable Table) where T : class, new()
{
T result = new T();
if (Validate(Table))
{ // Because reflection is slow, we will only pass the first row of the DataTable
result = FillProperties<T>(Table.Rows[0]);
}
return result;
}
/// <summary>
/// Fills the public properties of a class from each row of a DataTable where the name of
/// the property matches the column name in the DataTable, returning a List of T.
/// </summary>
/// <param name="Table">A DataTable that contains the data.</param>
/// <returns>A List class T with each class's public properties matching column names
/// set to the values of a diffrent row in the DataTable.</returns>
public static List<T> ToClassList<T>(DataTable Table) where T: class, new()
{
List<T> result = new List<T>();
if (Validate(Table))
{
foreach(DataRow row in Table.Rows)
{
result.Add(FillProperties<T>(row));
}
}
return result;
}
/// <summary>
/// Fills the public properties of a class from a DataRow where the name
/// of the property matches a column name from that DataRow.
/// </summary>
/// <param name="Row">A DataRow that contains the data.</param>
/// <returns>A class of type T with its public properties set to the
/// data from the matching columns in the DataRow.</returns>
public static T FillProperties<T>(DataRow Row) where T: class, new()
{
T result = new T();
Type classType = typeof(T);
// Defensive programming, make sure there are properties to set,
// and columns to set from and values to set from.
if( Row.Table.Columns.Count < 1
|| classType.GetProperties().Length < 1
|| Row.ItemArray.Length < 1)
{
return result;
}
foreach (PropertyInfo property in classType.GetProperties())
{
foreach(DataColumn column in Row.Table.Columns)
{
// Skip if Property name and ColumnName do not match
if(property.Name != column.ColumnName)
continue;
// This would throw if we tried to convert it below
if(Row[column] == DBNull.Value)
continue;
object newValue;
// If type is of type System.Nullable, do not attempt to convert the value
if (IsNullable(property.PropertyType))
{
newValue = Row[property.Name];
}
else
{ // Convert row object to type of property
newValue = Convert.ChangeType(Row[column], property.PropertyType);
}
// This is what sets the class properties of the class
property.SetValue(result, newValue, null);
}
}
return result;
}
/// <summary>
/// Checks a DataTable for empty rows, columns or null.
/// </summary>
/// <param name="DataTable">The DataTable to check.</param>
/// <returns>True if DataTable has data, false if empty or null.</returns>
public static bool Validate(DataTable DataTable)
{
if (DataTable == null) return false;
if (DataTable.Rows.Count == 0) return false;
if (DataTable.Columns.Count == 0) return false;
return true;
}
/// <summary>
/// Checks if type is nullable, Nullable<T> or its reference is nullable.
/// </summary>
/// <param name="type">Type to check for nullable.</param>
/// <returns>True if type is nullable, false if it is not.</returns>
public static bool IsNullable(Type type)
{
if (!type.IsValueType) return true; // ref-type
if (Nullable.GetUnderlyingType(type) != null) return true; // Nullable<T>
return false; // value-type
}
}
}