public string insert(Web.Core.DB.Transaction _transaction, ITableSpec table, Dictionary <string, string> data)
{
using (var logger = this.CreateCommandExecutionLogger()) {
Transaction transaction = (Transaction)_transaction;
lock (transaction) {
using (DbCommand command = transaction.sqlconnection.CreateCommand()) {
List <string> updates = new List <string>();
List <string> updatesPlaceholders = new List <string>();
ParamsHolder paramsholder = new ParamsHolder();
foreach (KeyValuePair <string, string> kvp in data)
{
updates.Add(this.traits.escapeIdentifier(kvp.Key));
updatesPlaceholders.Add(this.traits.markParam(paramsholder.Add(kvp.Value)));
}
command.Transaction = transaction.sqltransaction;
command.CommandType = System.Data.CommandType.Text;
command.CommandText = logger.commandText = "INSERT INTO " + table.compile(this.traits) + " (" + String.Join(", ", updates.ToArray()) + ") VALUES (" + String.Join(", ", updatesPlaceholders.ToArray()) + ")";
foreach (KeyValuePair <string, string> kvp in paramsholder.data)
{
command.AddParameter(kvp.Key, kvp.Value);
}
command.ExecuteNonQuery();
if (data.ContainsKey(table.idName))
{
return(data[table.idName]);
}
return(this.traits.LastInsertId(command, table).ToString());
}
}
}
}
private List <Dictionary <string, string> > _LoadByIds(DbCommand command, ITableSpec table, List <string> ids, bool forUpdate)
{
using (var logger = this.CreateCommandExecutionLogger()) {
command.CommandType = System.Data.CommandType.Text;
ParamsHolder paramsHolder = new ParamsHolder();
List <string> placeholder = new List <string>();
foreach (string id in ids)
{
placeholder.Add(this.traits.markParam(paramsHolder.Add(id)));
}
command.CommandText = logger.commandText = "SELECT * FROM " + table.compile(this.traits) + " WHERE " + table.getIdSpec().compile(this.traits) + " IN (" + string.Join(", ", placeholder.ToArray()) + ")" + (forUpdate ? " FOR UPDATE" : "");
//command.Prepare();
foreach (KeyValuePair <string, string> kvp in paramsHolder.data)
{
command.AddParameter(kvp.Key, kvp.Value);
}
Dictionary <string, Dictionary <string, string> > rawResult = new Dictionary <string, Dictionary <string, string> >();
using (DbDataReader reader = command.ExecuteReader()) {
while (reader.Read())
{
Dictionary <string, string> row = new Dictionary <string, string>();
for (int i = 0; i < reader.FieldCount; i++)
{
//throw new CriticalException("Name: " + reader.GetName(i));
object value = reader.GetValue(i);
string sValue;
if (value is DateTime)
{
sValue = ((DateTime)value).Ticks.ToString();
}
else if (value is TimeSpan)
{
sValue = ((TimeSpan)value).Ticks.ToString();
}
else
{
sValue = value.ToString();
}
row.Add(reader.GetName(i), sValue);
}
rawResult.Add(row[table.idName], row);
}
}
List <Dictionary <string, string> > result = new List <Dictionary <string, string> >();
foreach (string id in ids)
{
if (rawResult.ContainsKey(id))
{
result.Add(rawResult[id]);
}
}
return(result);
}
}
public void update(Web.Core.DB.Transaction _transaction, ITableSpec table, string id, Dictionary <string, string> data)
{
using (var logger = this.CreateCommandExecutionLogger()) {
Transaction transaction = (Transaction)_transaction;
lock (transaction) {
using (DbCommand command = transaction.sqlconnection.CreateCommand()) {
List <string> updates = new List <string>();
ParamsHolder paramsholder = new ParamsHolder();
foreach (KeyValuePair <string, string> kvp in data)
{
updates.Add(this.traits.escapeIdentifier(kvp.Key) + " = " + this.traits.markParam(paramsholder.Add(kvp.Value)));
}
command.Transaction = transaction.sqltransaction;
command.CommandType = System.Data.CommandType.Text;
command.CommandText = logger.commandText = "UPDATE " + table.compile(traits) + " set " + String.Join(", ", updates.ToArray()) + " where " + table.getIdSpec().compile(this.traits) + " = " + this.traits.markParam("id");
command.AddParameter("id", id);
foreach (KeyValuePair <string, string> kvp in paramsholder.data)
{
command.AddParameter(kvp.Key, kvp.Value);
}
// throw new CriticalException(command.CommandText + "; parameters: " + string.Join(", ", (from DbParameter parameter in command.Parameters select parameter.ParameterName + "='" + parameter.Value.ToString() + "'").ToArray()));
command.ExecuteNonQuery();
}
}
}
}