public void TestParsedQuery1()
{
var parsedQuery = new ParsedQuery("INSERT into BATCH_JOB_INSTANCE(JOB_INSTANCE_ID, JOB_NAME, JOB_KEY, VERSION) values (@id, @jobName, @jobKey, @version)", new PlaceholderGetter(name => ":" + name, true));
Assert.AreEqual("INSERT into BATCH_JOB_INSTANCE(JOB_INSTANCE_ID, JOB_NAME, JOB_KEY, VERSION) values (:id, :jobName, :jobKey, :version)",
parsedQuery.SubstitutedQuery);
Assert.IsTrue(parsedQuery.ParameterNames.SequenceEqual(new List<string> { "id", "jobName", "jobKey", "version" }));
}
public void TestParsedQuery3()
{
var parsedQuery = new ParsedQuery("SELECT SHORT_CONTEXT, SERIALIZED_CONTEXT FROM {0}JOB_EXECUTION_CONTEXT WHERE JOB_EXECUTION_ID = :id",
new PlaceholderGetter(name => "?", false));
Assert.AreEqual("SELECT SHORT_CONTEXT, SERIALIZED_CONTEXT FROM {0}JOB_EXECUTION_CONTEXT WHERE JOB_EXECUTION_ID = ?",
parsedQuery.SubstitutedQuery);
Assert.IsTrue(parsedQuery.ParameterNames.SequenceEqual(new List<string> { "id" }));
}
public void TestParsedQuery2()
{
var parsedQuery = new ParsedQuery("SELECT JOB_EXECUTION_ID, START_TIME, END_TIME, STATUS, EXIT_CODE, EXIT_MESSAGE, CREATE_TIME, LAST_UPDATED, VERSION, JOB_CONFIGURATION_LOCATION from BATCH_JOB_EXECUTION E where JOB_INSTANCE_ID = @id and JOB_EXECUTION_ID in (SELECT max(JOB_EXECUTION_ID) from BATCH_JOB_EXECUTION E2 where E2.JOB_INSTANCE_ID = @id)",
new PlaceholderGetter(name => "?", false));
Assert.AreEqual("SELECT JOB_EXECUTION_ID, START_TIME, END_TIME, STATUS, EXIT_CODE, EXIT_MESSAGE, CREATE_TIME, LAST_UPDATED, VERSION, JOB_CONFIGURATION_LOCATION from BATCH_JOB_EXECUTION E where JOB_INSTANCE_ID = ? and JOB_EXECUTION_ID in (SELECT max(JOB_EXECUTION_ID) from BATCH_JOB_EXECUTION E2 where E2.JOB_INSTANCE_ID = ?)",
parsedQuery.SubstitutedQuery);
Assert.IsTrue(parsedQuery.ParameterNames.SequenceEqual(new List<string> { "id", "id" }));
}
private static void SetParameters(IDbCommand command, ParsedQuery query, IQueryParameterSource parameterSource)
{
Assert.IsTrue(query.ParameterNames.Count == 0 || parameterSource != null, "The query has parameters but no parameter source was provided.");
if (query.Named)
{
foreach (var name in query.ParameterNames.Distinct())
{
command.AddParameter(name, parameterSource[name]);
}
}
else
{
for (var i = 0; i < query.ParameterNames.Count; i++)
{
var name = query.ParameterNames[i];
command.AddParameter(i.ToString(), parameterSource[name]);
}
}
}
/// <summary>
/// Executes an insert or update command multiple times.
/// </summary>
/// <param name="query">the parsed query to execute</param>
/// <param name="parameterSources">a list containing a <see cref="IQueryParameterSource"/> for each execution</param>
/// <returns>an array containing the affected rows for each execution</returns>
public int[] BatchUpdate(ParsedQuery query, IList<IQueryParameterSource> parameterSources)
{
var affectedRows = new int[parameterSources.Count];
using (var command = GetCommand(query.SubstitutedQuery))
{
// Define parameters
var names = query.Named
? query.ParameterNames.Distinct()
: Enumerable.Range(0, query.ParameterNames.Count).Select(i => i.ToString());
foreach (var name in names)
{
command.AddParameter(name);
}
// For each parameter source, set the parameter values and execute the command
for (var i = 0; i < parameterSources.Count; i++)
{
var parameterSource = parameterSources[i];
if (query.Named)
{
foreach (var name in query.ParameterNames.Distinct())
{
command.Parameters[name].Value = parameterSource[name];
}
}
else
{
for (var j = 0; j < query.ParameterNames.Count; j++)
{
var name = query.ParameterNames[j];
command.Parameters[j].Value = parameterSource[name];
}
}
affectedRows[i] = command.ExecuteNonQuery();
}
}
return affectedRows;
}
/// <summary>
/// Executes an insert or update command multiple times.
/// </summary>
/// <param name="query">the query to execute</param>
/// <param name="parameterSources">a list containing a <see cref="IQueryParameterSource"/> for each execution</param>
/// <returns>an array containing the affected rows for each execution</returns>
public int[] BatchUpdate(string query, IList<IQueryParameterSource> parameterSources)
{
var parsedQuery = new ParsedQuery(query, ConnectionProvider.PlaceholderGetter);
return BatchUpdate(parsedQuery, parameterSources);
}
/// <summary>
/// Executes an insert or update query on a database.
/// </summary>
/// <param name="query">the query to execute</param>
/// <param name="parameterSource">a parameter source that holds the parameter values for the query</param>
/// <returns>the number of affected rows</returns>
public int Update(string query, IQueryParameterSource parameterSource = null)
{
var parsedQuery = new ParsedQuery(query, ConnectionProvider.PlaceholderGetter);
return Update(parsedQuery, parameterSource);
}
/// <summary>
/// Executes a select query using a <see cref="DbDataAdapter"/> and returns a <see cref="DataSet"/>.
/// </summary>
/// <param name="query">the query</param>
/// <param name="parameterSource">a parameter source that holds the parameter values for the query</param>
/// <returns>a data set filled using the query</returns>
public DataSet Select(string query, IQueryParameterSource parameterSource = null)
{
var parsedQuery = new ParsedQuery(query, ConnectionProvider.PlaceholderGetter);
return Select(parsedQuery, parameterSource);
}
/// <summary>
/// Executes a select query using a DataAdapter and returns data using the provided data reader extractor.
/// </summary>
/// <typeparam name="T"> the type of the data to return</typeparam>
/// <param name="query">the SQL query</param>
/// <param name="extractor">the data reader mapper used to extract data</param>
/// <param name="parameterSource">a parameter source that holds the parameter values for the query</param>
/// <returns>the extracted data</returns>
public T Select<T>(string query, DataReaderExtractor<T> extractor, IQueryParameterSource parameterSource = null)
{
var parsedQuery = new ParsedQuery(query, ConnectionProvider.PlaceholderGetter);
return Select(parsedQuery, extractor, parameterSource);
}
