Generalizing SQL insert string

Question

I have to do a lot of SQL inserts without using stored procedures.

For big classes, the insert strings get huge so I was thinking of building a generalized insert function to handle it when passing in an object. What I've written below works but it's not ideal because (1) I have to specify all possible data types and (2) I have to convert all values back to strings to build the insert string, I'd rather be using parameters with values.

I just want a function where I pass it an object class and the function inserts the object's values into the database (given all the column names in the table matches the property names of the object)

Any ideas would be greatly appreciated, thanks.

public static IEnumerable<KeyValuePair<string, T>> PropertiesOfType<T>(object obj)
{
    return from p in obj.GetType().GetProperties()
           where p.PropertyType == typeof(T)
           select new KeyValuePair<string, T>(p.Name, (T)p.GetValue(obj, null));
}

public string InsertString(string _table, object _class)
{
    Dictionary<string, string> returnDict = new Dictionary<string, string>();
    StringBuilder sb = new StringBuilder();
    foreach (var property in PropertiesOfType<DateTime>(_class))
        returnDict.Add(property.Key, property.Value.ToString("yyyy-MM-dd HH:mm:ss"));
    foreach (var property in PropertiesOfType<string>(_class))
        returnDict.Add(property.Key, property.Value);
    foreach (var property in PropertiesOfType<int>(_class))
    {
        if (property.Key == "Key")
            continue;
        returnDict.Add(property.Key, property.Value.ToString());
    }            
    foreach (var property in PropertiesOfType<bool>(_class))
    {
        if (property.Value)
            returnDict.Add(property.Key, "1");
        else
            returnDict.Add(property.Key, "0");
    }
    foreach (var property in PropertiesOfType<decimal>(_class))
        returnDict.Add(property.Key, property.Value.ToString());
    foreach (var property in PropertiesOfType<long>(_class))
        returnDict.Add(property.Key, property.Value.ToString());
    if (returnDict.Count == 1)
    {
        sb.Append(string.Format("INSERT INTO [{0}] ({1}) VALUES ('{2}')", _table, returnDict.ElementAt(0).Key, returnDict.ElementAt(0).Value));
    }
    else
    {
        for (int i = 0; i < returnDict.Count; i++)
        {
            if (i == 0)
                sb.Append(string.Format("INSERT INTO [{0}] ({1}, ", _table, returnDict.ElementAt(i).Key));
            else if (i == returnDict.Count - 1)
                sb.Append(string.Format("{0}) ", returnDict.ElementAt(i).Key));
            else
                sb.Append(string.Format("{0}, ", returnDict.ElementAt(i).Key));
        }
        for (int i = 0; i < returnDict.Count; i++)
        {
            if (i == 0)
                sb.Append(string.Format("VALUES ('{0}', ", returnDict.ElementAt(i).Value));
            else if (i == returnDict.Count - 1)
                sb.Append(string.Format("'{0}')", returnDict.ElementAt(i).Value));
            else
                sb.Append(string.Format("'{0}', ", returnDict.ElementAt(i).Value));
        }
    }        
    return sb.ToString();
}



string query = InsertString(_table, _obj);

Answer 1

I've managed to find a way to do this that I'm pretty happy about that doesn't require any external libraries or frameworks.

Basing on @HardikParmar's suggestion I built a new process on converting a class object into a datatable, this will then store all the relevant datatypes as columns.

Then add a row into the structured datatable using the class object.

Now what you have a datatable with one row of values.

Then I create a PARAMATERIZED insert statement. Then in my command text I add the values to the parameters.

Almost clean, always room for improvement.

//this function creates the datatable from a specified class type, you may exclude properties such as primary keys
public DataTable ClassToDataTable<T>(List<string> _excludeList = null)
{
    Type classType = typeof(T);
    List<PropertyInfo> propertyList = classType.GetProperties().ToList();

    DataTable result = new DataTable(classType.Name);

    foreach (PropertyInfo prop in propertyList)
    {
        if (_excludeList != null)
        {
            bool toContinue = false;
            foreach (string excludeName in _excludeList)
            {
                if (excludeName == prop.Name)
                {
                    toContinue = true;
                    break;
                }
            }
            if (toContinue)
                continue;
        }
        result.Columns.Add(prop.Name, prop.PropertyType);
    }
    return result;
}
//add data to the table
public void AddRow(ref DataTable table, object data)
{
    Type classType = data.GetType();
    string className = classType.Name;

    if (!table.TableName.Equals(className))
    {
        throw new Exception("DataTableConverter.AddRow: " +
                            "TableName not equal to className.");
    }
    DataRow row = table.NewRow();
    List<PropertyInfo> propertyList = classType.GetProperties().ToList();
    foreach (PropertyInfo prop in propertyList)
    {            
        foreach (DataColumn col in table.Columns)
        {
            if (col.ColumnName == prop.Name)
            {
                if (table.Columns[prop.Name] == null)
                {
                    throw new Exception("DataTableConverter.AddRow: " +
                                        "Column name does not exist: " + prop.Name);
                }
                row[prop.Name] = prop.GetValue(data, null);
            }
        }                        
    }
    table.Rows.Add(row);
}
//creates the insert string
public string MakeInsertParamString(string _tableName, DataTable _dt, string _condition=null)
{
    StringBuilder sb = new StringBuilder();
    sb.Append(string.Format("INSERT INTO [{0}] (", _tableName));
    for (int i = 0; i < _dt.Columns.Count; i++)
    {
        sb.Append(string.Format("{0}", _dt.Columns[i].ColumnName));
        if (i < _dt.Columns.Count - 1)
            sb.Append(", ");                
    }
    sb.Append(") VALUES (");
    for (int i = 0; i < _dt.Columns.Count; i++)
    {
        sb.Append(string.Format("@{0}", _dt.Columns[i].ColumnName));
        if (i < _dt.Columns.Count - 1)
            sb.Append(", ");
    }
    sb.Append(")");
    if (!string.IsNullOrEmpty(_condition))
        sb.Append(" WHERE " + _condition);
    return sb.ToString();
}
//inserts into the database
public string InsertUsingDataRow(string _tableName, DataTable _dt, string _condition = null)
{
    try
    {
        using (SQLiteConnection conn = new SQLiteConnection(_dbPath))
        {
            string query = MakeInsertParamString(_tableName, _dt, _condition);
            SQLiteCommand cmd = new SQLiteCommand(query, conn);
            foreach (DataColumn col in _dt.Columns)
            {
                var objectValue = _dt.Rows[0][col.ColumnName];                    
                cmd.Parameters.AddWithValue("@" + col.ColumnName, objectValue);
            }
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();                
        }
        //return MakeInsertParamString(_tableName, _dt, _condition);
        return "Success";
    }
    catch (Exception ex) { return ex.Message; }
}