public static SQLQuery GetInsertQuery(this ChildObject childObject, Action <SQLQuery> preQuery)
{
string sql = "insert into Child (parent_pk, Name, SomeProp) values (@parentpk,@name,@someprop);";
var query = new SQLQuery(sql, SQLQueryType.NonQuery);
query.Parameters.Add("name", childObject.Name);
query.Parameters.Add("someprop", childObject.SomeProp);
query.PreQueryProcess = preQuery;
return(query);
}
public void NonQueryTestNoParameters()
{
var query = new SQLQuery(sqlString, SQLQueryType.NonQuery);
queryHelper.RunQuery(query);
VerifyLogging(sqlString);
Assert.AreEqual(sqlString, MockDatabaseFactory.DbCommand.Object.CommandText);
MockDatabaseFactory.DbConnection.VerifySet(dbc => dbc.ConnectionString = connectionString, Times.Exactly(1));
MockDatabaseFactory.Parameters.Verify(p => p.Add(It.IsAny <DbParameter>()), Times.Exactly(0));
MockDatabaseFactory.DbCommand.Verify(dbc => dbc.ExecuteNonQueryAsync(It.IsAny <System.Threading.CancellationToken>()), Times.Exactly(1));
Assert.AreEqual(345, query.RowCount);
}
private DbCommand CreateCommand(SQLQuery query, DbConnection connection, DbTransaction transaction = null)
{
var command = DbFactory.CreateCommand();
command.Connection = connection;
AddParameters(command, query);
command.CommandText = query.ModifiedSQL;
if (transaction != null)
{
command.Transaction = transaction;
}
return(command);
}
public static List <ParentObject> LoadParents()
{
var parentDict = new Dictionary <int, ParentObject>();
var sql = @"select Parent.pk as ParentPk,
Parent.Name as ParentName,
Parent.SomeProp as ParentSomeProp,
Child.pk as ChildPk,
Child.Name as ChildName,
Child.SomeProp as ChildSomeProp
from Parent inner join Child on (Parent.pk = Child.parent_pk)";
var query = new SQLQuery(sql, SQLQueryType.DataReader);
query.ProcessRow = dr =>
{
//add parent
int parentpk = (int)dr["ParentPk"];
if (!parentDict.ContainsKey(parentpk))
{
var newParentObject = new ParentObject()
{
ChildObjects = new List <ChildObject>(),
Name = (string)dr["ParentName"],
SomeProp = (int)dr["ParentSomeProp"]
};
parentDict.Add(parentpk, newParentObject);
}
var parentObject = parentDict[parentpk];
//add child
var newChildObject = new ChildObject()
{
Name = (string)dr["ChildName"],
SomeProp = (int)dr["ChildSomeProp"]
};
parentObject.ChildObjects.Add(newChildObject);
return(true);
};
var qh = new QueryHelper("sandbox");
qh.RunQuery(query);
return(parentDict.Values.ToList());
}
public void NonQueryTestWithParametersBuiltDynamically()
{
var query = new SQLQuery(sqlString, SQLQueryType.NonQuery);
query.PreQueryProcess = new Action <SQLQuery>(q =>
{
q.Parameters.Add("param1", "value1");
q.Parameters.Add("param2", "value2");
q.Parameters.Add("param3", 333);
});
queryHelper.RunQuery(query);
VerifyLogging(sqlString);
Assert.AreEqual(sqlString, MockDatabaseFactory.DbCommand.Object.CommandText);
MockDatabaseFactory.DbConnection.VerifySet(dbc => dbc.ConnectionString = connectionString, Times.Exactly(1));
MockDatabaseFactory.Parameters.Verify(p => p.Add(It.IsAny <DbParameter>()), Times.Exactly(3));
MockDatabaseFactory.DbCommand.Verify(dbc => dbc.ExecuteNonQueryAsync(It.IsAny <System.Threading.CancellationToken>()), Times.Exactly(1));
Assert.AreEqual(345, query.RowCount);
}
public void NonQueryTransactionNoParametersRollbackWhenExceptionAsync()
{
try
{
int returnValue = 100;
MockDatabaseFactory.DbCommand.Setup(dbc => dbc.ExecuteNonQueryAsync(It.IsAny <System.Threading.CancellationToken>()))
.Returns(() =>
{
returnValue++;
if (returnValue == 105)
{
throw new ApplicationException("blah blah");
}
return(Task.FromResult <int>(returnValue));
});
var queries = new Dictionary <int, SQLQuery>();
for (int counter = 0; counter < 10; counter++)
{
var query = new SQLQuery("insert into sometable values (" + counter + ");", SQLQueryType.NonQuery)
{
GroupNumber = counter
};
queries.Add(counter, query);
}
var task = queryHelper.RunQueryAsync(queries.Values, true);
task.Wait();
}
catch (System.AggregateException ex)
{
throw ex.InnerExceptions[0];
}
finally
{
MockDatabaseFactory.DbCommand.VerifySet(dbc => dbc.Transaction = MockDatabaseFactory.DbTransaction.Object);
MockDatabaseFactory.DbCommand.Verify(dbc => dbc.ExecuteNonQueryAsync(It.IsAny <System.Threading.CancellationToken>()), Times.Exactly(5));
MockDatabaseFactory.DbConnection.VerifySet(dbc => dbc.ConnectionString = connectionString, Times.Exactly(1));
MockDatabaseFactory.Parameters.Verify(p => p.Add(It.IsAny <DbParameter>()), Times.Exactly(0));
MockDatabaseFactory.DbTransaction.Verify(dbt => dbt.Commit(), Times.Exactly(0));
MockDatabaseFactory.DbTransaction.Verify(dbt => dbt.Rollback(), Times.Exactly(1));
}
}
public void ReadSingleRowNoParameters()
{
var dataContainer = new TestDataContainer();
var dataReader = MockDatabaseFactory.CreateDbDataReader(dataContainer);
var query = new SQLQuery(sqlString, SQLQueryType.DataReader)
{
ProcessRow = dataContainer.ProcessRow
};
queryHelper.RunQuery(query);
dataContainer.AssertData();
VerifyLogging(sqlString);
MockDatabaseFactory.DbConnection.VerifySet(dbc => dbc.ConnectionString = connectionString, Times.Exactly(1));
MockDatabaseFactory.Parameters.Verify(p => p.Add(It.IsAny <DbParameter>()), Times.Exactly(0));
MockDatabaseFactory.DbCommand.Protected()
.Verify <Task <DbDataReader> >("ExecuteDbDataReaderAsync", Times.Exactly(1), It.IsAny <CommandBehavior>(), It.IsAny <System.Threading.CancellationToken>());
Assert.AreEqual(1, query.RowCount);
dataReader.Verify(dr => dr.Read(), Times.Exactly(2));
}
public void NonQueryTransactionWithParametersAsync()
{
int returnValue = 100;
MockDatabaseFactory.DbCommand.Setup(dbc => dbc.ExecuteNonQueryAsync(It.IsAny <System.Threading.CancellationToken>()))
.Returns(() =>
{
returnValue++;
return(Task.FromResult <int>(returnValue));
});
var queries = new Dictionary <int, SQLQuery>();
for (int counter = 0; counter < 10; counter++)
{
var query = new SQLQuery("insert into sometable values (" + counter + ");", SQLQueryType.NonQuery)
{
GroupNumber = counter
};
query.Parameters.Add("param1", "value1");
query.Parameters.Add("param2", "value2");
query.Parameters.Add("param3", 333);
queries.Add(counter, query);
}
var task = queryHelper.RunQueryAsync(queries.Values, true);
task.Wait();
for (int counter = 0; counter < 10; counter++)
{
Assert.AreEqual(101 + counter, queries[counter].RowCount);
}
MockDatabaseFactory.DbCommand.VerifySet(dbc => dbc.Transaction = MockDatabaseFactory.DbTransaction.Object);
MockDatabaseFactory.DbCommand.Verify(dbc => dbc.ExecuteNonQueryAsync(It.IsAny <System.Threading.CancellationToken>()), Times.Exactly(10));
MockDatabaseFactory.DbConnection.VerifySet(dbc => dbc.ConnectionString = connectionString, Times.Exactly(1));
MockDatabaseFactory.Parameters.Verify(p => p.Add(It.IsAny <DbParameter>()), Times.Exactly(30));
MockDatabaseFactory.DbTransaction.Verify(dbt => dbt.Commit(), Times.Exactly(1));
MockDatabaseFactory.DbTransaction.Verify(dbt => dbt.Rollback(), Times.Exactly(0));
}
private void DumpSqlAndParamsToLog(SQLQuery query)
{
if (LogMessage == null || !DebugLoggingEnabled)
{
return;
}
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.AppendFormat("About to execute \"{0}\" ", query.ModifiedSQL);
if (query.Parameters.Count > 0)
{
sb.Append(" with parameters:\r\n");
foreach (string key in query.Parameters.Keys)
{
sb.AppendFormat("{0}={1} with type {2}.\r\n", key, query.Parameters[key], query.Parameters[key].GetType());
}
}
else
{
sb.Append(" with no parameters.");
}
LogDebug(sb.ToString());
}
private void AddParameters(DbCommand command, SQLQuery query)
{
foreach (string paramName in query.InParameters.Keys)
{
if (query.InParameters[paramName] != null && query.InParameters[paramName].Any() && query.ModifiedSQL.Contains("@" + paramName))
{
var parameterDictionary = new Dictionary <string, object>();
foreach (var value in query.InParameters[paramName])
{
parameterDictionary.Add(string.Format("{0}{1}", paramName, parameterDictionary.Count), value);
}
//TODO: this needs to be a regex otherwise it could be prone to replacing paramName that starts with the same name eg. @myvar & @myvar2.
query.ModifiedSQL = query.ModifiedSQL.Replace("@" + paramName, string.Join(",", parameterDictionary.Select(pd => "@" + pd.Key)));
foreach (var parameter in parameterDictionary.Keys)
{
query.Parameters.Add(parameter, parameterDictionary[parameter]);
}
}
}
foreach (string paramName in query.Parameters.Keys)
{
command.Parameters.Add(CreateParameter(paramName, query.Parameters[paramName]));
}
}
public async Task RunQueryAsync(SQLQuery query)
{
await RunQueryAsync(new[] { query }, TransactionOpen);
}
public void RunQuery(SQLQuery query)
{
RunQuery(new[] { query }, TransactionOpen);
}