Convert a Class or List of Class to a DataTable, using reflection.

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:

• Populate the public properties of a class from a DataTable

• DataTable to class as a C# code file (using CodeDOM)

• DataTable to class Assembly (using Reflection.Emit)

• C# class to a SQL 'CREATE TABLE' or 'INSERT INTO' script

• 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.

    This class uses generics to accepts a class type, and uses reflection to determine the name and type of the class's public properties. With that, a new DataTable is made and the DataColumnCollection is fleshed out. Then you can add rows to the DataTable by passing instances of the class with it's property fields containing values.

    Finally, we serialize the DataTable to an XML file, save it's Schema, then load it all back in again as a proof of concept.


Usage example:

List<Order> orders = new List<Order>();

// Fill in orders here ...
// orders.Add(new Order());

// Convert class to DataTable
DataTable ordersTable = ClassListToDataTable(orders);

// Set DataGrid's DataSource to DataTable
dataGrid1.DataSource = ordersTable;

Here is the Code:

public static DataTable ClassToDataTable<T>() where T : class
{
    Type classType = typeof(T);

    List<PropertyInfo> propertyList = classType.GetProperties().ToList();
    if (propertyList.Count < 1)
    {
        return new DataTable();
    }

    string className = classType.UnderlyingSystemType.Name;
    DataTable result = new DataTable(className);

    foreach (PropertyInfo property in propertyList)
    {
        DataColumn col = new DataColumn();
        col.ColumnName = property.Name;

        Type dataType = property.PropertyType;

        if (IsNullable(dataType))
        {
            if(dataType.IsGenericType)
            {
                dataType = dataType.GenericTypeArguments.FirstOrDefault();
            }
        }
        else
        {   // True by default
            col.AllowDBNull = false;
        }

        col.DataType = dataType;

        result.Columns.Add(col);
    }

    return result;
}

public static DataTable ClassListToDataTable<T>(List<T> ClassList) where T : class
{
   DataTable result = ClassToDataTable<T>();
   
   if(result.Columns.Count < 1)
   {
      return new DataTable();
   }
   if(ClassList.Count < 1)
   {
      return result;
   }
   
   foreach(T item in ClassList)
   {
      ClassToDataRow(ref result, item);
   }
   
   return result;
}

public static void ClassToDataRow<T>(ref DataTable Table, T Data) where T : class
{
    Type classType = typeof(T);
    string className = classType.UnderlyingSystemType.Name;

    // Checks that the table name matches the name of the class. 
    // There is not required, and it may be desirable to disable this check.
    // Comment this out or add a boolean to the parameters to disable this check.
    if (!Table.TableName.Equals(className))
    {
        return;
    }

    DataRow row = Table.NewRow();
    List<PropertyInfo> propertyList = classType.GetProperties().ToList();

    foreach (PropertyInfo prop in propertyList)
    {
        if (Table.Columns.Contains(prop.Name))
        {
            if (Table.Columns[prop.Name] != null)
            {
                row[prop.Name] = prop.GetValue(Data, null);
            }
        }
    }
    Table.Rows.Add(row);
}

public static bool IsNullable(Type Input)
{
    if (!Input.IsValueType) return true; // Is a ref-type, such as a class
    if (Nullable.GetUnderlyingType(Input) != null) return true; // Nullable
    return false; // Must be a value-type
}

Here is an example of how to serialize a DataTable to XML, and load it back again

string filePath = "order1.xml";
string schemaPath = Path.ChangeExtension(filePath,".xsd");

ordersTable.WriteXml(filePath);
ordersTable.WriteXmlSchema(schemaPath);

// Load
DataTable loadedTable = new DataTable();
loadedTable.ReadXmlSchema(schemaPath);
loadedTable.ReadXml(filePath);

// Set DataGrid's DataSource
dataGrid1.DataSource = dataTable;

The full project and source code for EntityJustWorks can be found on GitHub and CodePlex.

XML Serializable Dictionary, Tuple, and Object

Serializing a data class to an XML file using XmlSerializer is very useful. However, some of the most useful data classes in .NET are not serializable. Dictionary and Tuple most notably. If you looking for a blog post on how to make a Dictionary that accepts duplicate keys by storing the values values with identical keys in a List, please see this blog post.

The below SerializableDictionary class works by inheriting from IXmlSerializable, which requires you implement the following three methods:
* GetSchema() - Remember, you should always return null for this function.
* ReadXml(XmlReader reader)
* WriteXml(XmlWriter writer)
(Read about IXmlSerializable on MSDN)

Here is the code to serialize a dictionary or serialize a tuple:

namespace XMLSerializableDictionary
{
    using System;
    using System.Collections.Generic;
    using System.Runtime.Serialization;
    using System.Xml;
    using System.Xml.Schema;
    using System.Xml.Serialization;

     [Serializable]
    [XmlRoot("Dictionary")]
    public class SerializableDictionary<TKey, TValue>
        : Dictionary<TKey, TValue>, IXmlSerializable
    {
        private const string DefaultTagItem = "Item";
        private const string DefaultTagKey = "Key";
        private const string DefaultTagValue = "Value";
        private static readonly XmlSerializer KeySerializer =
                                        new XmlSerializer(typeof(TKey));

        private static readonly XmlSerializer ValueSerializer =
                                        new XmlSerializer(typeof(TValue));

        public SerializableDictionary() : base()
        {
        }

        protected SerializableDictionary(SerializationInfo info, StreamingContext context)
                : base(info, context)
        {
        }

        protected virtual string ItemTagName
        {
            get { return DefaultTagItem; }
        }

        protected virtual string KeyTagName
        {
            get { return DefaultTagKey; }
        }

        protected virtual string ValueTagName
        {
            get { return DefaultTagValue; }
        }

        public XmlSchema GetSchema()
        {
            return null;
        }

        public void ReadXml(XmlReader reader)
        {
            bool wasEmpty = reader.IsEmptyElement;

            reader.Read();

            if (wasEmpty)
            {
             return;
            }

            try
            {
                while (reader.NodeType != XmlNodeType.EndElement)
                {
                    reader.ReadStartElement(this.ItemTagName);
                    try
                    {
                        TKey tKey;
                        TValue tValue;

                        reader.ReadStartElement(this.KeyTagName);
                        try
                        {
                            tKey = (TKey)KeySerializer.Deserialize(reader);
                        }
                        finally
                        {
                            reader.ReadEndElement();
                        }

                        reader.ReadStartElement(this.ValueTagName);
                        try
                        {
                            tValue = (TValue)ValueSerializer.Deserialize(reader);
                        }
                        finally
                        {
                            reader.ReadEndElement();
                        }

                        this.Add(tKey, tValue);
                    }
                    finally
                    {
                        reader.ReadEndElement();
                    }

                    reader.MoveToContent();
                }
            }
            finally
            {
                reader.ReadEndElement();
            }
        }

        public void WriteXml(XmlWriter writer)
        {
            foreach (KeyValuePair<TKey, TValue> keyValuePair in this)
            {
                writer.WriteStartElement(this.ItemTagName);
                try
                {
                    writer.WriteStartElement(this.KeyTagName);
                    try
                    {
                        KeySerializer.Serialize(writer, keyValuePair.Key);
                    }
                    finally
                    {
                        writer.WriteEndElement();
                    }

                    writer.WriteStartElement(this.ValueTagName);
                    try
                    {
                        ValueSerializer.Serialize(writer, keyValuePair.Value);
                    }
                    finally
                    {
                        writer.WriteEndElement();
                    }
                }
                finally
                {
                    writer.WriteEndElement();
                }
            }
        }
    }
}

The idea behind the serializable tuple is we just make our own Tuple that stores the items by declaring the properties to represent them with their generic T type. If you are not used to working with generics, this can be a little strange. T1, T2 and T3 are just placeholders for the type that is to be determined by the calling function, or the function above that if the calling function uses generics too.

And a serializable tuple:

public class SerializableTuple<T1,T2,T3>
{
 public T1 Item1 { get; set; }
 public T2 Item2 { get; set; }
 public T3 Item3 { get; set; }
 
 public static implicit operator Tuple<T1,T2,T3>(SerializableTuple<T1,T2,T3>  st)
 {
  return Tuple.Create(st.Item1,st.Item2,st.Item3);
 }

 public static implicit operator SerializableTuple<T1,T2,T3>(Tuple<T1,T2,T3> t)
 {
  return new SerializableTuple<T1,T2,T3>() {
   Item1 = t.Item1,
   Item2 = t.Item2,
   Item3 = t.Item3
  };   
 }
 
 public SerializableTuple()
 {
 }
}

And finally, a generic object serializer and deserializer:

public static class XML
{
   public static class Serialize
   {
      public static void Object(string Filename, object obj)
      {
         using (StreamWriter streamWriter = new StreamWriter(Filename))
         {
            XmlSerializer xmlSerializer = new XmlSerializer(obj.GetType());
            xmlSerializer.Serialize(streamWriter, obj);
         }
      }
   }

   public static class DeSerialize
   {
      public static string Generic<T>(T data)
      {
         if (data == null)
            return string.Empty;

         string content = string.Empty;
         using (MemoryStream memoryStream = new MemoryStream())
         {
            XmlSerializer serializer = new XmlSerializer(typeof(T));
            serializer.Serialize(memoryStream, data);

            memoryStream.Seek(0, SeekOrigin.Begin);
            using (StreamReader reader = new StreamReader(memoryStream))
            {
               content = reader.ReadToEnd();
            }
         }
         return content;
      }

      public static object Object(string Filename, Type type)
      {
         object result = null;
         using (TextReader reader = new StringReader(Filename))
         {
            XmlSerializer serializer = new XmlSerializer(type);
            result = serializer.Deserialize(reader);
         }
         return result;
      }
   }
}

And perhaps after you serialize your data to an XML file, you would like to generate a schema XML file from it:

  
void XmlToSchema(string FileName)
{
 XmlReader xmlReader = XmlReader.Create(FileName);
 XmlSchemaSet schemaSet = new XmlSchemaSet();
 XmlSchemaInference schemaInfer = new XmlSchemaInference();
 schemaSet = schemaInfer.InferSchema(xmlReader);

 string outFilename = Path.ChangeExtension(FileName,".xsd");
 using(Stream streamOut = new FileStream(outFilename,FileMode.Create) )
 {
  TextWriter textWriter =  new StreamWriter(streamOut);    
  foreach (XmlSchema s in schemaSet.Schemas())
  {
   s.Write(textWriter );
  }
  textWriter .Close();
 }
}