/// <summary> /// used when the CommandType is TableDirect. /// </summary> private DbTestSqlResult ExecuteSqlTableDirectRequest(SqlRequest request, DbTestRunnerResult result, string phaseName) { var sqlResult = new DbTestSqlResult(); try { // Create a new command using the same connection as the data context using (var cmd = this.GetDbConnection().CreateCommand()) { // Set the command parameters foreach (var p in request.Parameters.Select(i => i.Value)) { result.Logs.Add($"{phaseName} Sql Parameter:{p.ParameterName} = {p.Value}"); cmd.Parameters.Add(p); } if (cmd.Connection.State != ConnectionState.Open) { cmd.Connection.Open(); } // build up the sql command object cmd.CommandText = $"Select * From {request.SqlToExecute}"; result.Logs.Add($"{phaseName} Table Direct Sql: {cmd.CommandText}"); cmd.CommandType = CommandType.Text; // Execute the command via a data reader and use it to load data tables; one for each result set returned from the sql. using (var rdr = cmd.ExecuteReader()) { do { var dt = new DataTable(); dt.Load(rdr); sqlResult.Data.Add(dt); result.Logs.Add($"{phaseName} Phase: DataTable[{sqlResult.Data.Count - 1}] read {dt.Rows.Count} rows(s)."); } while (!rdr.IsClosed && rdr.NextResult()); } result.Logs.Add($"{phaseName} Phase: Read {sqlResult.Data.Count} result set(s)."); } sqlResult.IsSuccessful = true; } catch (Exception e) { sqlResult.Exception = e; sqlResult.IsSuccessful = false; } return(sqlResult); }
/// <summary> /// Used when CommandType is StoredProcedure /// </summary> private DbTestSqlResult ExecuteSqlStoredProcedureRequest(SqlRequest request, DbTestRunnerResult result, string phaseName) { var sqlResult = new DbTestSqlResult(); try { // var dataTable = new DataTable(); var dataset = new DataSet(); // Create a new command using the same connection as the data context using (var cmd = GetDbConnection().CreateCommand()) { if (cmd.Connection.State != ConnectionState.Open) { cmd.Connection.Open(); } // build up the sql command object cmd.CommandText = request.SqlToExecute; cmd.CommandType = CommandType.StoredProcedure; // Set the command parameters foreach (var p in request.Parameters.Select(i => i.Value)) { cmd.Parameters.Add(p); } result.Logs.Add($"{phaseName} Stored Procedure: {cmd.CommandText}"); // Execute the command via a data adapter and use it to load a data set using (var dataAdapter = new SqlDataAdapter(cmd)) { //dataAdapter.Fill(dataTable); dataAdapter.Fill(dataset); } } // populate the sqlResult data tables with the tables filled by the adapter sqlResult.Data.AddRange(dataset.Tables.Cast <DataTable>()); //sqlResult.Data.Add(dataTable); sqlResult.IsSuccessful = true; } catch (Exception e) { sqlResult.Exception = e; sqlResult.IsSuccessful = false; } return(sqlResult); }
public static SqlRequest InvokeScalarFunction(this IDbTest test, string functionName, string schema = null, params SqlParameter[] parameters) { var sql = new StringBuilder($"Select {(!string.IsNullOrEmpty(schema) ? $"{schema}." : "")}{functionName}("); var firstParam = true; foreach (var p in parameters) { if (firstParam) { firstParam = false; } else { sql.Append(", "); } sql.Append(p.ParameterName); } sql.Append(") as RetVal"); return(SqlRequest.RunSqlText(sql.ToString(), parameters)); }
public static SqlRequest ReturnAllRows <TEntity>(this IDbTest test, params SqlParameter[] parameters) { return(SqlRequest.ReturnAllRows(test.GetQualifiedTableOrViewName <TEntity>(), parameters)); }
/// <summary> /// Configures the SqlRequest to not commit any data context changes /// </summary> public static SqlRequest DontCommitDataContext(this SqlRequest request) { request.DataContextCommitPoint = DataContextCommitPoint.None; return(request); }
/// <summary> /// Configures the SqlRequest to commit data context changes after running the sql commands /// </summary> public static SqlRequest CommitDataContextAfterRunningSql(this SqlRequest request) { request.DataContextCommitPoint = DataContextCommitPoint.AfterRunningSql; return(request); }
public static SqlRequest RunTableDirect <TEntity>(this IDbTest test, params SqlParameter[] parameters) { return(SqlRequest.RunTableDirect(test.GetQualifiedTableOrViewName <TEntity>(), parameters)); }
public static SqlRequest RunTableDirect(this IDbTest test, string tableName = null, string schema = null, params SqlParameter[] parameters) { return(SqlRequest.RunTableDirect(test.GetQualifiedTableOrViewName(tableName, schema), parameters)); }
public static SqlRequest ExecuteStoredProcedure(this IDbTest test, string sprocName = null, string schema = null, params SqlParameter[] parameters) { return(SqlRequest.RunStoredProcedure(sprocName, schema, parameters)); }
public static SqlRequest RunSqlText(this IDbTest test, string sql = null, params SqlParameter[] parameters) { return(SqlRequest.RunSqlText(sql, parameters)); }
public static SqlRequest CommitDataContextNow(this IDbTest test) { return(SqlRequest.CommitDataContextNow()); }
/// <summary> /// Executes the sql requests /// </summary> protected DbTestSqlResult ExecuteTestPhase(DbTestRunnerResult result, string phaseName, SqlRequest request) { var phaseResult = new DbTestSqlResult(); try { if (request == null) { return(DbTestSqlResult.Success); } // if we're supposed to commit the data context changes before running the sql, do so now. if (request.DataContextCommitPoint == DataContextCommitPoint.BeforeRunningSql) { var contextRowsAffected = this.CommitChanges(); result.Logs.Add($"{phaseName} Phase: Commited Data Context prior to executing sql. Rows Affected: {contextRowsAffected}"); } if (request.IsEmpty) { return(DbTestSqlResult.Success); } // Run the sql, we have different execution strategies based on the command types switch (request.CommandType) { case CommandType.Text: phaseResult = this.ExecuteSqlTextRequest(request, result, phaseName); break; case CommandType.StoredProcedure: phaseResult = this.ExecuteSqlStoredProcedureRequest(request, result, phaseName); break; case CommandType.TableDirect: phaseResult = this.ExecuteSqlTableDirectRequest(request, result, phaseName); break; default: throw new Exception("Unsupported Command Type"); } // if there was a problem running the sql command, stop executing now if (!phaseResult.IsSuccessful) { return(phaseResult); } // if we're supposed to commit the data context changes after running the sql, do so now. if (request.DataContextCommitPoint == DataContextCommitPoint.AfterRunningSql) { var contextRowsAffected = this.CommitChanges(); result.Logs.Add($"{phaseName} Phase: Commited Data Context prior to executing sql. Rows Affected: {contextRowsAffected}"); } // if we got this far, the test executed successfully, set the return value phaseResult.IsSuccessful = true; } catch (Exception e) { // runtime exception executing the test. result.Exception = e; result.Logs.Add($"Error attempting to execute {phaseName} sql. {e.GetType().Name} - {e.Message}"); phaseResult.IsSuccessful = false; throw; } // set the overall result success status based on how this command was executed result.IsSuccessful = phaseResult.IsSuccessful; result.TestPhase = phaseName; return(phaseResult); }