Articulate-ORM/Articulate/QueryBuilder.cs

using System;
using System.Collections.Generic;
using System.Reflection;
using System.Text;
using System.Linq;
using System.Diagnostics;
using Articulate.Attributes;
using Articulate.Extensions;
using MySql.Data.MySqlClient;

namespace Articulate
{
    public class QueryBuilder
    {
        public static string Select(Model model, params string[][] conditions)
        {
            StringBuilder command = new StringBuilder();

            command.Append($"SELECT * FROM `{model.GetTable()}`");

            if (conditions.Length > 0)
            {
                command.Append(" WHERE ");

                for (int i = 0; i < conditions.Length; i++)
                {
                    string[] condition = conditions[i];

                    if (i > 0)
                        command.Append(" AND ");

                    command.Append(string.Join(" ", condition));
                }
            }

            return command.ToString();
        }

        public static MySqlCommand InsertCommand(Model model)
        {
            using (MySqlCommand cmd = new MySqlCommand("", DBConnection.Instance().Connection))
            {
                StringBuilder command = new StringBuilder();

                command.Append($"INSERT INTO `{model.GetTable()}` (");

                PropertyInfo[] columns = model.GetType().GetProperties(BindingFlags.Public |
                                                  BindingFlags.NonPublic |
                                                  BindingFlags.Instance).Where(p => Attribute.IsDefined(p, typeof(Column))).ToArray();

                List<string> valRef = new List<string>();

                for (int i = 0; i < columns.Length; i++)
                {
                    var column = columns[i];

                    var name = column.Name.ToLower();
                    var type = column.GetCustomAttribute<Column>().Type;
                    var value = column.GetValue(model);

                    cmd.Parameters.Add(new MySqlParameter($"@{name}", type) { Value = value });

                    valRef.Add($"@{name}");

                    command.Append($"`{name}`");

                    if (i + 1 < columns.Length)
                        command.Append(", ");
                }

                command.Append(") VALUES (");

                for (int i = 0; i < valRef.Count; i++)
                {
                    var value = valRef[i];

                    command.Append($"'{value}'");

                    if (i + 1 < valRef.Count)
                        command.Append(", ");
                }

                command.Append(");");
            }

            return null;
        }

        public static string Insert(Model model)
        {
            StringBuilder command = new StringBuilder();

            command.Append($"INSERT INTO `{model.GetTable()}` (");

            PropertyInfo[] columns = model.GetType().GetProperties(BindingFlags.Public |
                                              BindingFlags.NonPublic |
                                              BindingFlags.Instance).Where(p => Attribute.IsDefined(p, typeof(Column))).ToArray();

            List<object> values = new List<object>();

            for (int i = 0; i < columns.Length; i++)
            {
                var column = columns[i];

                var name = column.Name.ToLower();
                var value = column.GetValue(model);

                values.Add(value);

                command.Append($"`{name}`");

                if (i + 1 < columns.Length)
                    command.Append(", ");
            }

            command.Append(") VALUES (");

            for (int i = 0; i < values.Count; i++)
            {
                var value = values[i];

                command.Append($"'{value}'");

                if (i + 1 < values.Count)
                    command.Append(", ");
            }

            command.Append(");");

            return command.ToString();
        }

        public static MySqlCommand CreateTable(Type model)
        {
            var modelInstance = (Model)Activator.CreateInstance(model);

            string tableName = modelInstance.GetTable();

            PropertyInfo[] columns = model.GetProperties(BindingFlags.Public |
                                              BindingFlags.NonPublic |
                                              BindingFlags.Instance).Where(p => Attribute.IsDefined(p, typeof(Column))).ToArray();

            StringBuilder command = new StringBuilder();

            command.Append($"CREATE TABLE `{tableName}` (");

            for (int i = 0; i < columns.Length; i++)
            {
                var column = columns[i];
                Column attr = column.GetCustomAttribute<Column>();

                command.Append($"{column.Name.AddUnderscoresToSentence().ToLower()} ");
                command.Append($"{(MysqlTypeNames)attr.Type}");

                if (attr.Size > 0)
                    command.Append($"({attr.Size})");

                if (attr.Primary)
                    command.Append(" PRIMARY KEY");

                if (i + 1 < columns.Length)
                    command.Append(", ");
            }

            command.Append(");");

            return new MySqlCommand(command.ToString(), DBConnection.Instance().Connection);
        }
    }
}