5
\$\begingroup\$

My code goes from a class to DataTable, and back again. It populates the class's public properties, or creates DataColumns whose names and types match that of the the class's public properties. It also has methods that allow the developer to go from a query to a populated class, or from a class to an SQL script or SQL query to C# class code files.

I still plan on adding more feature here and there, but overall I'm pretty satisfied with its functionality. I am submitting my code for review here, and I'm trying to polish it now for release. I welcome any and all criticism and comments, questions, etc.

One area in particular that I have a question about is organization. Right now, I have the functions broke up into static classes that group them by their functionality. This Is the best I have thought of so far, and the naming is pretty intuitive. The current classes/groups are Map, Query, Script, Code, Convert and Helper.

The name of this product (so far) is EntityJustWorks. I have kept all these classes under the namespace EntityJustWorks.SQL, because these are all (more or less) SQL specific and I think that I may want to add another library that deals with a different repository. Does this seem like a sound naming convention? Would this do good to hide behind an 'live record'? Again I welcome all and any criticisms/comments.

If you would like to go straight to the code download, you can access my GitHub. I also keep a copy of my code, as well as comments and explanations of certain important sections on my C# programming blog.

namespace EntityJustWorks.SQL
{
    public static class Convert
    {
        /// <summary> 
        /// Generates a C# class code file from a Database given an SQL connection string and table name.
        /// </summary>
        public static string SQLToCSharp(string ConnectionString, string TableName)
        {
            DataTable table = Query.QueryToDataTable(ConnectionString, "SELECT TOP 1 * FROM [{0}]", TableName);
            return Code.DatatableToCSharp(table);
        }

        /// <summary>
        /// Creates an SQL table from a class object.
        /// </summary>
        public static bool ClassToSQL<T>(string ConnectionString, params T[] ClassCollection) where T : class
        {
            string createTableScript = Script.CreateTable<T>(ClassCollection);
            return (Query.ExecuteNonQuery(ConnectionString, createTableScript) == -1);
        }
    }

    /// <summary>
    /// DataTable/Class Mapping Class
    /// </summary>
    public static class Map
    {
        /// <summary>
        /// Fills properties of a class from a row of a DataTable where the name of the property matches the column name from that DataTable.
        /// It does this for each row in the DataTable, returning a List of classes.
        /// </summary>
        /// <typeparam name="T">The class type that is to be returned.</typeparam>
        /// <param name="Table">DataTable to fill from.</param>
        /// <returns>A list of ClassType with its properties set to the data from the matching columns from the DataTable.</returns>
        public static IList<T> DatatableToClass<T>(DataTable Table) where T : class, new()
        {
            if (!Helper.IsValidDatatable(Table))
                return new List<T>();

            Type classType = typeof(T);
            IList<PropertyInfo> propertyList = classType.GetProperties();

            // Parameter class has no public properties.
            if (propertyList.Count == 0)
                return new List<T>();

            List<string> columnNames = Table.Columns.Cast<DataColumn>().Select(column => column.ColumnName).ToList();

            List<T> result = new List<T>();
            try
            {
                foreach (DataRow row in Table.Rows)
                {
                    T classObject = new T();
                    foreach (PropertyInfo property in propertyList)
                    {
                        if (property != null && property.CanWrite)   // Make sure property isn't read only
                        {
                            if (columnNames.Contains(property.Name))  // If property is a column name
                            {
                                if (row[property.Name] != System.DBNull.Value)   // Don't copy over DBNull
                                {
                                    object propertyValue = System.Convert.ChangeType(
                                            row[property.Name],
                                            property.PropertyType
                                        );
                                    property.SetValue(classObject, propertyValue, null);
                                }
                            }
                        }
                    }
                    result.Add(classObject);
                }
                return result;
            }
            catch
            {
                return new List<T>();
            }
        }

        /// <summary>
        /// Creates a DataTable from a class type's public properties and adds a new DataRow to the table for each class passed as a parameter.
        /// The DataColumns of the table will match the name and type of the public properties.
        /// </summary>
        /// <param name="ClassCollection">A class or array of class to fill the DataTable with.</param>
        /// <returns>A DataTable who's DataColumns match the name and type of each class T's public properties.</returns>
        public static DataTable ClassToDatatable<T>(params T[] ClassCollection) where T : class
        {
            DataTable result = ClassToDatatable<T>();

            if (Helper.IsValidDatatable(result, IgnoreRows: true))
                return new DataTable();
            if (Helper.IsCollectionEmpty(ClassCollection))
                return result;   // Returns and empty DataTable with columns defined (table schema)

            foreach (T classObject in ClassCollection)
            {
                ClassToDataRow(ref result, classObject);
            }

            return result;
        }

        /// <summary>
        /// Creates a DataTable from a class type's public properties. The DataColumns of the table will match the name and type of the public properties.
        /// </summary>
        /// <typeparam name="T">The type of the class to create a DataTable from.</typeparam>
        /// <returns>A DataTable who's DataColumns match the name and type of each class T's public properties.</returns>
        public static DataTable ClassToDatatable<T>() where T : class
        {
            Type classType = typeof(T);
            DataTable result = new DataTable(classType.UnderlyingSystemType.Name);

            foreach (PropertyInfo property in classType.GetProperties())
            {
                DataColumn column = new DataColumn();
                column.ColumnName = property.Name;
                column.DataType = property.PropertyType;

                if (Helper.IsNullableType(column.DataType) && column.DataType.IsGenericType)
                {   // If Nullable<>, this is how we get the underlying Type...
                    column.DataType = column.DataType.GenericTypeArguments.FirstOrDefault();
                }
                else
                {   // True by default, so set it false
                    column.AllowDBNull = false;
                }

                // Add column
                result.Columns.Add(column);
            }
            return result;
        }

        /// <summary>
        /// Adds a DataRow to a DataTable from the public properties of a class.
        /// </summary>
        /// <param name="Table">A reference to the DataTable to insert the DataRow into.</param>
        /// <param name="ClassObject">The class containing the data to fill the DataRow from.</param>
        private static void ClassToDataRow<T>(ref DataTable Table, T ClassObject) where T : class
        {
            DataRow row = Table.NewRow();
            foreach (PropertyInfo property in typeof(T).GetProperties())
            {
                if (Table.Columns.Contains(property.Name))
                {
                    if (Table.Columns[property.Name] != null)
                    {
                        row[property.Name] = property.GetValue(ClassObject, null);
                    }
                }
            }
            Table.Rows.Add(row);
        }
    }

    /// <summary>
    /// SQL Query Helper Class
    /// </summary>
    public static class Query
    {
        /// <summary>
        /// Runs a SQL query and returns the results as a List of the specified class
        /// </summary>
        /// <typeparam name="T">The type the result will be returned as.</typeparam>
        /// <param name="ConnectionString">The SQL connection string.</param>
        /// <param name="FormatString_Query">A SQL command that will be passed to string.Format().</param>
        /// <param name="FormatString_Parameters">The parameters for string.Format().</param>
        /// <returns>A List of classes that represent the records returned.</returns>
        public static IList<T> QueryToClass<T>(string ConnectionString, string FormatString_Query, params object[] FormatString_Parameters) where T : class, new()
        {
            IList<T> result = new List<T>();
            DataTable tableQueryResult = QueryToDataTable(ConnectionString, string.Format(FormatString_Query, FormatString_Parameters));
            if (Helper.IsValidDatatable(tableQueryResult))
            {
                result = Map.DatatableToClass<T>(tableQueryResult);
            }
            return result;
        }

        /// <summary>
        /// Executes an SQL query and returns the results as a DataTable.
        /// </summary>
        /// <param name="ConnectionString">The SQL connection string.</param>
        /// <param name="FormatString_Query">A SQL command that will be passed to string.Format().</param>
        /// <param name="FormatString_Parameters">The parameters for string.Format().</param>
        /// <returns>The results of the query as a DataTable.</returns>
        public static DataTable QueryToDataTable(string ConnectionString, string FormatString_Query, params object[] FormatString_Parameters)
        {
            try
            {
                DataTable result = new DataTable();

                using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))
                {
                    sqlConnection.Open();

                    using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
                    {
                        sqlCommand.CommandText = string.Format(FormatString_Query, FormatString_Parameters);
                        sqlCommand.CommandType = CommandType.Text;

                        SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlCommand);
                        sqlAdapter.Fill(result);
                    }
                }
                return result;
            }
            catch
            {
                return new DataTable();
            }
        }

        /// <summary>
        /// Executes a query, and returns the first column of the first row in the result set returned by the query.
        /// </summary>
        /// <typeparam name="T">The type the result will be returned as.</typeparam>
        /// <param name="ConnectionString">>The SQL connection string.</param>
        /// <param name="FormatString_Query">The SQL query as string.Format string.</param>
        /// <param name="FormatString_Parameters">The string.Format parameters.</param>
        /// <returns>The  first column of the first row in the result, converted and casted to type T.</returns>
        public static T QueryToScalarType<T>(string ConnectionString, string FormatString_Query, params object[] FormatString_Parameters)
        {
            try
            {
                object result = new object();
                using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))
                {
                    sqlConnection.Open();

                    using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
                    {
                        sqlCommand.CommandText = string.Format(FormatString_Query, FormatString_Parameters);
                        sqlCommand.CommandType = CommandType.Text;

                        result = System.Convert.ChangeType(sqlCommand.ExecuteScalar(), typeof(T));
                    }
                }
                return (T)result;
            }
            catch
            {
                return (T)new object();
            }
        }

        /// <summary>
        /// Executes a non-query SQL command, such as INSERT or DELETE
        /// </summary>
        /// <param name="ConnectionString">The connection string.</param>
        /// <param name="FormatString_Command">The SQL command, as a format string.</param>
        /// <param name="FormatString_Parameters">The parameters for the format string.</param>
        /// <returns>The number of rows affected, or -1 on errors.</returns>
        public static int ExecuteNonQuery(string ConnectionString, string FormatString_Command, params object[] FormatString_Parameters)
        {
            try
            {
                int rowsAffected = 0;

                using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))
                {
                    sqlConnection.Open();
                    using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
                    {
                        string commandText = string.Format(FormatString_Command, FormatString_Parameters);

                        sqlCommand.CommandText = commandText;
                        sqlCommand.CommandType = CommandType.Text;
                        rowsAffected = sqlCommand.ExecuteNonQuery();
                    }
                }

                return rowsAffected;
            }
            catch
            {
                return 0;
            }
        }
    }

    /// <summary>
    /// SQL Script Generation Class
    /// </summary>
    public static class Script
    {
        /// <summary>
        /// Creates a SQL script that inserts the values of the specified classes' public properties into a table.
        /// </summary>
        public static string InsertInto<T>(params T[] ClassObjects) where T : class
        {
            DataTable table = Map.ClassToDatatable<T>(ClassObjects);
            return InsertInto(table);   // We don't need to check IsValidDatatable() because InsertInto does
        }

        /// <summary>
        /// Creates a SQL script that inserts the cell values of a DataTable's DataRows into a table.
        /// </summary>
        public static string InsertInto(DataTable Table)
        {
            if (!Helper.IsValidDatatable(Table))
                return string.Empty;

            StringBuilder result = new StringBuilder();
            foreach (DataRow row in Table.Rows)
            {
                if (row == null || row.Table.Columns.Count < 1 || row.ItemArray.Length < 1)
                    return string.Empty;

                string columns = Helper.RowToColumnString(row);
                string values = Helper.RowToValueString(row);

                if (string.IsNullOrWhiteSpace(columns) || string.IsNullOrWhiteSpace(values))
                    return string.Empty;

                result.AppendFormat("INSERT INTO [{0}] {1} VALUES {2}", Table.TableName, columns, values);
            }

            return result.ToString();
        }

        /// <summary>
        /// Creates a SQL script that creates a table where the column names match the specified class's public properties.
        /// </summary>
        public static string CreateTable<T>(params T[] ClassObjects) where T : class
        {
            DataTable table = Map.ClassToDatatable<T>(ClassObjects);
            return Script.CreateTable(table);
        }

        /// <summary>
        /// Creates a SQL script that creates a table where the columns matches that of the specified DataTable.
        /// </summary>
        public static string CreateTable(DataTable Table)
        {
            if (Helper.IsValidDatatable(Table, IgnoreRows: true))
                return string.Empty;

            StringBuilder result = new StringBuilder();
            result.AppendFormat("CREATE TABLE [{0}] ({1}", Table.TableName, Environment.NewLine);

            bool FirstTime = true;
            foreach (DataColumn column in Table.Columns.OfType<DataColumn>())
            {
                if (FirstTime) FirstTime = false;
                else
                    result.Append(",");

                result.AppendFormat("[{0}] {1} {2}NULL{3}",
                    column.ColumnName,
                    GetDataTypeString(column.DataType),
                    column.AllowDBNull ? "" : "NOT ",
                    Environment.NewLine
                );
            }
            result.AppendFormat(") ON [PRIMARY]{0}GO", Environment.NewLine);

            return result.ToString();
        }

        /// <summary>
        /// Returns the SQL data type equivalent, as a string for use in SQL script generation methods.
        /// </summary>
        private static string GetDataTypeString(Type DataType)
        {
            switch (DataType.Name)
            {
                case "Boolean": return "[bit]";
                case "Char": return "[char]";
                case "SByte": return "[tinyint]";
                case "Int16": return "[smallint]";
                case "Int32": return "[int]";
                case "Int64": return "[bigint]";
                case "Byte": return "[tinyint] UNSIGNED";
                case "UInt16": return "[smallint] UNSIGNED";
                case "UInt32": return "[int] UNSIGNED";
                case "UInt64": return "[bigint] UNSIGNED";
                case "Single": return "[float]";
                case "Double": return "[double]";
                case "Decimal": return "[decimal]";
                case "DateTime": return "[datetime]";
                case "Guid": return "[uniqueidentifier]";
                case "Object": return "[variant]";
                case "String": return "[nvarchar](250)";
                default: return "[nvarchar](MAX)";
            }
        }
    }

    /// <summary>
    /// Helper Functions. Conversion, Validation
    /// </summary>
    public static class Helper
    {
        /// <summary>
        /// Indicates whether a specified DataTable is null, has zero columns, or (optionally) zero rows.
        /// </summary>
        /// <param name="Table">DataTable to check.</param>
        /// <param name="IgnoreRows">When set to true, the function will return true even if the table's row count is equal to zero.</param>
        /// <returns>False if the specified DataTable null, has zero columns, or zero rows, otherwise true.</returns>
        public static bool IsValidDatatable(DataTable Table, bool IgnoreRows = false)
        {
            if (Table == null) return false;
            if (Table.Columns.Count == 0) return false;
            if (!IgnoreRows && Table.Rows.Count == 0) return false;
            return true;
        }

        /// <summary>
        /// Indicates whether a specified Enumerable collection is null or an empty collection.
        /// </summary>
        /// <typeparam name="T">The specified type contained in the collection.</typeparam>
        /// <param name="Input">An Enumerator to the collection to check.</param>
        /// <returns>True if the specified Enumerable collection is null or empty, otherwise false.</returns>
        public static bool IsCollectionEmpty<T>(IEnumerable<T> Input)
        {
            return (Input == null || Input.Count() < 1) ? true : false;
        }

        /// <summary>
        ///  Indicates whether a specified Type can be assigned null.
        /// </summary>
        /// <param name="Input">The Type to check for nullable property.</param>
        /// <returns>True if the specified Type can be assigned null, otherwise false.</returns>
        public static bool IsNullableType(Type Input)
        {
            if (!Input.IsValueType) return true; // Reference Type
            if (Nullable.GetUnderlyingType(Input) != null) return true; // Nullable<T>
            return false;   // Value Type
        }

        /// <summary>
        /// Returns all the column names of the specified DataRow in a string delimited like and SQL INSERT INTO statement.
        /// Example: ([FullName], [Gender], [BirthDate])
        /// </summary>
        /// <returns>A string formatted like the columns specified in an SQL 'INSERT INTO' statement.</returns>
        public static string RowToColumnString(DataRow Row)
        {
            IEnumerable<string> Collection = Row.ItemArray.Select(item => item as String);
            return ListToDelimitedString(Collection, "([", "], [", "])");
        }

        /// <summary>
        /// Returns all the values the specified DataRow in as a string delimited like and SQL INSERT INTO statement.
        /// Example: ('John Doe', 'M', '10/3/1981'')
        /// </summary>
        /// <returns>A string formatted like the values specified in an SQL 'INSERT INTO' statement.</returns>
        public static string RowToValueString(DataRow Row)
        {
            IEnumerable<string> Collection =  GetDatatableColumns(Row.Table).Select(c => c.ColumnName);
            return ListToDelimitedString(Collection, "('", "', '", "')");
        }

        /// <summary>
        /// Enumerates a collection as delimited collection of strings.
        /// </summary>
        /// <typeparam name="T">The Type of the collection.</typeparam>
        /// <param name="Collection">An Enumerator to a collection to populate the string.</param>
        /// <param name="Prefix">The string to prefix the result.</param>
        /// <param name="Delimiter">The string that will appear between each item in the specified collection.</param>
        /// <param name="Postfix">The string to postfix the result.</param>         
        public static string ListToDelimitedString<T>(IEnumerable<T> Collection, string Prefix, string Delimiter, string Postfix)
        {
            if (IsCollectionEmpty<T>(Collection)) return string.Empty;

            StringBuilder result = new StringBuilder();
            foreach (T item in Collection)
            {
                if (result.Length != 0)
                    result.Append(Delimiter);   // Add comma

                result.Append(EscapeSingleQuotes(item as String));
            }
            if (result.Length < 1) return string.Empty;

            result.Insert(0, Prefix);
            result.Append(Postfix);

            return result.ToString();
        }

        /// <summary>
        /// Returns an enumerator, which supports a simple iteration over a collection of all the DataColumns in a specified DataTable.
        /// </summary>
        public static IEnumerable<DataColumn> GetDatatableColumns(DataTable Input)
        {
            if (Input == null || Input.Columns.Count < 1) return new List<DataColumn>();
            return Input.Columns.OfType<DataColumn>().ToList();
        }

        /// <summary>
        /// Returns an enumerator, which supports a simple iteration over a collection of all the DataRows in a specified DataTable.
        /// </summary>
        public static IEnumerable<DataRow> GetDatatableRows(DataTable Input)
        {
            if (!IsValidDatatable(Input)) return new List<DataRow>();
            return Input.Rows.OfType<DataRow>().ToList();
        }

        /// <summary>
        /// Returns a new string in which all occurrences of the single quote character in the current instance are replaced with a back-tick character.
        /// </summary>
        public static string EscapeSingleQuotes(string Input)
        {
            return Input.Replace('\'', '`'); // Replace with back-tick
        }
    }

    /// <summary>
    /// C# Code Generation Class
    /// </summary>
    public static class Code
    {
        /// <summary> 
        /// Generates a C# class code file from a DataTable.
        /// </summary>
        public static string DatatableToCSharp(DataTable Table)
        {
            string className = Table.TableName;

            if (string.IsNullOrWhiteSpace(className))
            {
                return "// Class cannot be created: DataTable.TableName must have a value to use as the name of the class";
            }

            // Create the class
            CodeTypeDeclaration classDeclaration = CreateClass(className);

            // Add public properties
            foreach (DataColumn column in Table.Columns)
            {
                classDeclaration.Members.Add(CreateProperty(column.ColumnName, column.DataType));
            }

            // Add Class to Namespace
            string namespaceName = new StackFrame(2).GetMethod().DeclaringType.Namespace;// "EntityJustWorks.AutoGeneratedClassObject";
            CodeNamespace codeNamespace = new CodeNamespace(namespaceName);
            codeNamespace.Types.Add(classDeclaration);

            // Generate code
            string filename = string.Format("{0}.{1}.cs",namespaceName,className);
            CreateCodeFile(filename, codeNamespace);

            // Return filename
            return filename;
        }

        #region Private Members
        private static CodeTypeDeclaration CreateClass(string name)
        {
            CodeTypeDeclaration result = new CodeTypeDeclaration(name);
            result.Attributes = MemberAttributes.Public;
            result.Members.Add(CreateConstructor(name)); // Add class constructor
            return result;
        }

        private static CodeConstructor CreateConstructor(string className)
        {
            CodeConstructor result = new CodeConstructor();
            result.Attributes = MemberAttributes.Public;
            result.Name = className;
            return result;
        }

        private static CodeMemberField CreateProperty(string name, Type type)
        {
            // This is a little hack. Since you cant create auto properties in CodeDOM,
            //  we make the getter and setter part of the member name.
            // This leaves behind a trailing semicolon that we comment out.
            //  Later, we remove the commented out semicolons.
            string memberName = name + "\t{ get; set; }//";

            CodeMemberField result = new CodeMemberField(type,memberName);
            result.Attributes = MemberAttributes.Public | MemberAttributes.Final;
            return result;
        }

        private static void CreateCodeFile(string filename, CodeNamespace codeNamespace)
        {
            // CodeGeneratorOptions so the output is clean and easy to read
            CodeGeneratorOptions codeOptions = new CodeGeneratorOptions();
            codeOptions.BlankLinesBetweenMembers = false;
            codeOptions.VerbatimOrder = true;
            codeOptions.BracingStyle = "C";
            codeOptions.IndentString = "\t";

            // Create the code file
            using (TextWriter textWriter = new StreamWriter(filename))
            {
                CSharpCodeProvider codeProvider = new CSharpCodeProvider();
                codeProvider.GenerateCodeFromNamespace(codeNamespace, textWriter, codeOptions);
            }

            // Correct our little auto-property 'hack'
            File.WriteAllText(filename, File.ReadAllText(filename).Replace("//;", ""));
        }
        #endregion
    }
}
\$\endgroup\$
  • 1
    \$\begingroup\$ I don't know how /why exactly you did it... but your posted code is encoding html-entities (<>) as their HTML-escape sequences. That's completely superfluous. For more information see the markdown help \$\endgroup\$ – Vogel612 Jan 14 '15 at 16:08
  • \$\begingroup\$ @Vogel612 : This was done out of habit due to my blog provider not playing nice with GT and LT symbols. I didn't encode html entities, just the arrow brackets around generics in the code. Posted the code that way by default, and it looked fine so I posted it because I was getting pressed for time. Question: The post looked fine for me, but where would escaping arrow brackets like that cause problems? It is truly superfluous or is it detrimental as well? \$\endgroup\$ – Adam White Jan 20 '15 at 7:51
  • 1
    \$\begingroup\$ It's detrimental, since formatting the code as codeblock automatically encodes such things for you. This means it would then get displayed as &gt; &lt; respectively, which makes copy pasting the code into your IDE for review harder \$\endgroup\$ – Vogel612 Jan 20 '15 at 10:38
  • \$\begingroup\$ You're not using Automapper anywhere... but you've added the tag... how come ...? \$\endgroup\$ – t3chb0t Nov 29 '18 at 7:42
4
\$\begingroup\$

Intro
This is really a nice to have question. The class seems to be good structured and well commented. But, it is a lot of code to review, so let us start.

General

  • based on the naming guidelines input parameter should be named using camelCase casing.

  • you should use braces {} for single if statements also. This will make your code less errorprone. If you don't want to use them, you should be consistent with your style. In your code you are using them sometimes but most of the time you aren't using them.

  • comments should describe why something is done. What is done should be described by the code itself by using meaningful names for methods, properties etc.
    So comments like // Create the class are just noise which should be removed.

Convert

  • It would be better to name the methods using the conventions of the NET System.Convert class, like ToXXX() or/and FromXXX().
  • SQLToCSharp() returns a string and by the name of the method one could assume that he will get the string representation of a C# class, but this method will instead return on success the filename of the generated and written class. To solve this issue you should consider to add a class CSharpCode which is returned. This class should have a static Empty property to reflect the case that the returned object isn't a good one.

    public class CSharpCode
    {
        public string Name { get; private set; }
        public string NameSpace { get; private set; }
        public string Content { get; private set; }
    
        public CSharpCode(string name, string nameSpace, string content)
        {
            Name = name;
            NameSpace = nameSpace;
            Content = content;
        }
    
        private static CSharpCode instance = new CSharpCode();
        public static CSharpCode Empty { get { return instance; } }
    
        private CSharpCode() { }
        public override bool Equals(object obj)
        {
            if (obj == null) return false;
    
            if (this.GetType() != obj.GetType()) return false;
    
            // safe because of the GetType check
            CSharpCode other = (CSharpCode)obj;
    
            if (!Object.Equals(Name, other.Name)) return false;
            if (!Object.Equals(NameSpace, other.NameSpace)) return false;
            if (!Object.Equals(Content, other.Content)) return false;
    
            return true;
        }
        public override int GetHashCode()
        {
            unchecked // Overflow is fine, just wrap
            {
                int hash = 17;
    
                if (Name != null) { hash = hash * 23 + Name.GetHashCode(); }
                if (NameSpace != null) { hash = hash * 23 + NameSpace.GetHashCode(); }
                if (Content != null) { hash = hash * 23 + Content.GetHashCode(); }
    
                return hash;
            }
        }
    
        public override string ToString()
        {
    
            IList<string> values = new List<string>();
            if (!String.IsNullOrWhiteSpace(NameSpace))
            {
                values.Add(NameSpace);
            }
            if (!String.IsNullOrWhiteSpace(Name))
            {
                values.Add(Name);
            }
            if (values.Count != 0)
            {
                return String.Join(".", values);
            }
    
            return base.ToString();
        }
    } 
    

    Now we can refactor the methods, but I prefer to pass a DataTable over a connectionstring and tablename. So, we will just do both.

    public static CSharpCode ToCSharpCode(string connectionString, string tableName)
    {
        DataTable table = Query.QueryToDataTable(connectionString, "SELECT TOP 1 * FROM [{0}]", tableName);
        return ToCSharpCode(table);
    } 
    
    public static CSharpCode ToCSharpCode(DataTable dataTable)
    {
        return Code.DatatableToCSharp(dataTable);
    } 
    

    The ClassToSQL() method does not belong to Convert class, because it doesn't convert the classCollection but saves them in a database. It would be better to change it to return a DataTable. This DataTable could then be saved using another method which should live inside the Query class.

    public static DataTable FromType<T>(params T[] classCollection) where T : class
    {
        return Map.ClassToDatatable<T>(classCollection);
    }
    

Code

  • CreateCodeFile()
    The creation of the CodeGeneratorOptions should be extracted to a separate method. This improves the readability of the CreateCodeFile() method.

    private static CodeGeneratorOptions GetDefaultOptions()
    {
        CodeGeneratorOptions codeOptions = new CodeGeneratorOptions();
        codeOptions.BlankLinesBetweenMembers = false;
        codeOptions.VerbatimOrder = true;
        codeOptions.BracingStyle = "C";
        codeOptions.IndentString = "\t";
        return codeOptions;
    }  
    
  • if we add a string FromCodeNameSpace() method, we can simplify the CreateCodeFile() method and if we want to, we can just remove it.

    By using a MemoryStream instead of a FileStream we will speed up the creation of the code.

    private static string FromCodeNamespace(CodeNamespace codeNamespace)
    {
        // CodeGeneratorOptions so the output is clean and easy to read
        CodeGeneratorOptions codeOptions = GetDefaultOptions();
    
        string code = String.Empty;
    
        using (MemoryStream memoryStream = new MemoryStream())
        using (TextWriter textWriter = new StreamWriter(memoryStream, new UTF8Encoding(false, true)))
        using (CSharpCodeProvider codeProvider = new CSharpCodeProvider())
        {
            codeProvider.GenerateCodeFromNamespace(codeNamespace, textWriter, codeOptions);
            code = Encoding.UTF8.GetString(memoryStream.ToArray());
        }
    
        // Correct our little auto-property 'hack'
        return code.Replace("//;", "");
    } 
    

    now the CreateCodeFile() method is as simple as

    private static void CreateCodeFile(string filename, CodeNamespace codeNamespace)
    {
        string code = FromCodeNamespace(codeNamespace);
        File.WriteAllText(filename, code);
    }  
    

    Next we will extract the creation of the CodeNamespace to a separate method.

    private static CodeNamespace ToCodeNameSpace(DataTable table)
    {
    
        CodeTypeDeclaration classDeclaration = CreateClass(table.TableName);
    
        foreach (DataColumn column in table.Columns)
        {
            classDeclaration.Members.Add(CreateProperty(column.ColumnName, column.DataType));
        }
    
        string namespaceName = new StackFrame(2).GetMethod().DeclaringType.Namespace;
    
        CodeNamespace codeNamespace = new CodeNamespace(namespaceName);
        codeNamespace.Types.Add(classDeclaration);
    
        return codeNamespace;
    }  
    

    which simplifies the DatatableToCSharp() method to

    public static string DatatableToCSharp(DataTable table)
    {
        string className = table.TableName;
    
        if (string.IsNullOrWhiteSpace(className))
        {
            return "// Class cannot be created: DataTable.TableName must have a value to use as the name of the class";
        }
    
        CodeNamespace codeNamespace = ToCodeNameSpace(table);
    
        // Generate code
        string filename = string.Format("{0}.{1}.cs", codeNamespace.Name, className);
        CreateCodeFile(filename, codeNamespace);
    
        // Return filename
        return filename;
    }
    

    but this wasn't the goal. We wanted to get a method which returns a CSharpCode object. So let us introduce a CSharpCode FromDataTable(DataTable table) method

    public static CSharpCode FromDataTable(DataTable table)
    {
        if (string.IsNullOrWhiteSpace(table.TableName))
        {
            return CSharpCode.Empty;
        }
    
        string className = table.TableName;
    
        CodeNamespace codeNamespace = ToCodeNameSpace(table);
    
        string code = FromCodeNamespace(codeNamespace);
    
        return new CSharpCode(className, codeNamespace.Name, code);
    }  
    

    now the new CSharpCode Convert.ToCSharpCode() method will be refactored to

    public static CSharpCode ToCSharpCode(DataTable dataTable)
    {
        return Code.FromDataTable(dataTable);
    }  
    

    which can be saved using

    private const string noTableName = "Class cannot be created: DataTable.TableName must have a value to use as the name of the class";
    public static string ExportAsCodeFile(DataTable table)
    {
        CSharpCode csharpCode = Convert.ToCSharpCode(table);
        if (csharpCode == CSharpCode.Empty) { throw new ArgumentOutOfRangeException(noTableName); }
    
        String fileName = csharpCode.ToString() + ".cs";
        System.IO.File.WriteAllText(fileName, csharpCode.Content);
        return fileName;
    }
    

Map

  • ClassToDataRow()
    This method does not need any ref parameter. Please read Jon Skeet's answer on StackOverflow

    By inverting the conditions and using continue like already explained in RubberDuck's answer we can remove horizontal spacing. We will do this by extracting the checks to a separate method

     private static bool IsColumnByNameInvalid(DataColumnCollection columns, string propertyName)
    {
        return !columns.Contains(propertyName) || columns[propertyName] == null;
    }  
    

    We should also check the state of the row before we add it. There is no sense in adding a row where no columns are filled.

    private static void ClassToDataRow<T>(DataTable table, T classObject) where T : class
    {
        bool rowChanged = false;
        DataRow row = table.NewRow();
        foreach (PropertyInfo property in typeof(T).GetProperties())
        {
            if (IsColumnByNameInvalid(table.Columns, property.Name)) { continue; }
    
            rowChanged = true;
            row[property.Name] = property.GetValue(classObject, null);
    
        }
    
        if (!rowChanged) { return; }
        table.Rows.Add(row);
    } 
    
\$\endgroup\$
  • 1
    \$\begingroup\$ Thank you for your response. I have not hear before that comments should describe the why instead of the what, but it makes sense. Thank you for your tips on naming conventions, I like your suggestion of ToFoo() and FromBar(). Your ideas for the CSharpCode class and the FromType method are interesting, and I will consider them, but I get your point on the naming of SQLToCSharp(). Thank you for helping me clean up the Code section. Regarding Map, I don't know why I thought it was necessary for a ref here, how silly of me. \$\endgroup\$ – Adam White Jan 31 '15 at 10:51
3
\$\begingroup\$

There's a lot of code here. I'm not even going to pretend I looked at it all, so don't consider this a complete critique. I did see a few things though.

In Map.DataTableToClass there's a serious case of arrow code.

try
{
    foreach (DataRow row in Table.Rows)
    {
        T classObject = new T();
        foreach (PropertyInfo property in propertyList)
        {
            if (property != null && property.CanWrite)   // Make sure property isn't read only
            {
                if (columnNames.Contains(property.Name))  // If property is a column name
                {
                    if (row[property.Name] != System.DBNull.Value)   // Don't copy over DBNull
                    {
                        object propertyValue = System.Convert.ChangeType(
                                row[property.Name],
                                property.PropertyType
                            );
                        property.SetValue(classObject, propertyValue, null);
                    }
                }
            }
        }
        result.Add(classObject);
    }
    return result;
}

I would look for ways to reduce the nesting here. Probably by flipping some of your if statements to be guard clauses instead. The continue statement will help us out here.

List<T> result = new List<T>();
try
{
    foreach (DataRow row in Table.Rows)
    {
        T classObject = new T();
        foreach (PropertyInfo property in propertyList)
        {
            if (property == null || !property.CanWrite)   // Make sure property isn't read only
            {
                continue;
            }

            if (!columnNames.Contains(property.Name))  // If property is a column name
            {
                continue;
            }

            if (row[property.Name] == System.DBNull.Value)   // Don't copy over DBNull
            {
                continue;
            }

            object propertyValue = System.Convert.ChangeType(
                    row[property.Name],
                    property.PropertyType
                );
            property.SetValue(classObject, propertyValue, null);
        }
        result.Add(classObject);
    }
    return result;
}

Which is a start, but we're calling continue to send us to the next iteration in three different places. That's not very DRY is it? Also, some people don't care for continue. That's okay, we can solve those things by extracting a method to validate that it's okay to add the object to the result. This greatly reduces the amount of nesting going on here.

List<T> result = new List<T>();
try
{
    foreach (DataRow row in Table.Rows)
    {
        T classObject = new T();
        foreach (PropertyInfo property in propertyList)
        {   
            if (IsValidObjectData(property, columnNames, row)
            {
                object propertyValue = System.Convert.ChangeType(
                        row[property.Name],
                        property.PropertyType
                    );
                property.SetValue(classObject, propertyValue, null);
            }
        }
        result.Add(classObject);
    }
    return result;
}
\$\endgroup\$
  • \$\begingroup\$ Thank you for your response. I did not think about arrow code, but you are right. I like your idea of wrapping the validation logic in a function. This takes the validation logic out of the function, making it easier to comprehend. This particular function is very important, as it does the actual mapping from the Datatable to the class. \$\endgroup\$ – Adam White Jan 31 '15 at 10:36
  • \$\begingroup\$ I was thinking... is there anyway to replace the nested foreach loops with Linq? I had tried to optimize this function in that way once, but I failed. \$\endgroup\$ – Adam White Jan 31 '15 at 10:57
  • \$\begingroup\$ I'm sure there is, but I'm not very good at Linq to be honest. \$\endgroup\$ – RubberDuck Jan 31 '15 at 12:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.