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); }