public void sendSelectedResultSetToSqlContext(SqlInt32 resultsetNo, SqlString command)
{
validateResultSetNumber(resultsetNo);
SqlDataReader dataReader = testDatabaseFacade.executeCommand(command);
int ResultsetCount = 0;
if (dataReader.FieldCount > 0)
{
do
{
ResultsetCount++;
if (ResultsetCount == resultsetNo)
{
sendResultsetRecords(dataReader);
break;
}
} while (dataReader.NextResult());
}
dataReader.Close();
if (ResultsetCount < resultsetNo)
{
throw new InvalidResultSetException("Execution returned only " + ResultsetCount.ToString() + " ResultSets. ResultSet [" + resultsetNo.ToString() + "] does not exist.");
}
}
public void SendResultSetToTables(SqlString tablesString, SqlString command, SqlBoolean?allowExtraColumns = null)
{
GetFaultTolerance(allowExtraColumns);
var tablenames = ParseTableString(tablesString);
var tgtColumns = testDatabaseFacade.getDataTableColumns(tablenames);
List <DataTable> datatables = new List <DataTable>();
var ResultsetCount = 0;
using (SqlDataReader dataReader = testDatabaseFacade.executeCommand(command))
{
if (dataReader.FieldCount > 0)
{
do
{
DataTable dt = null;
if (ResultsetCount < tablenames.Length)
{
dt = new DataTable();
dt.Load(dataReader); //this will call nextResult on the reader for us
dt.TableName = tablenames[ResultsetCount]; //this has to happen after the load if no table was specified, it will result in the dt being named after the source table
}
else
{
dataReader.NextResult();
}
datatables.Add(dt);
ResultsetCount++;
} while (dataReader.HasRows && ResultsetCount < tablenames.Length);
}
}
ResultsetCount = 0;
foreach (var table in datatables)
{
SendEachRecordOfData(table, tgtColumns[ResultsetCount]);
ResultsetCount++;
}
}
private String createSchemaStringFromCommand(SqlString command)
{
SqlDataReader reader = null;
try
{
reader = testDatabaseFacade.executeCommand(command);
reader.Read();
DataTable schema = attemptToGetSchemaTable(command, reader);
throwExceptionIfSchemaIsEmpty(command, schema);
return(buildSchemaString(schema));
}
finally
{
closeReader(reader);
}
}
public static SqlChars TableToString(SqlString TableName, SqlString OrderOption, SqlString ColumnList)
{
if (TableName.IsNull) {
throw new Exception("Object name cannot be NULL");
}
if (OrderOption.IsNull)
{
OrderOption = "";
}
if (ColumnList.IsNull)
{
ColumnList = "";
}
TestDatabaseFacade testDbFacade = new TestDatabaseFacade();
String selectStmt = getSqlStatement(ref TableName, ref OrderOption);
SqlDataReader reader = testDbFacade.executeCommand(selectStmt);
List<String[]> results = getTableStringArray(reader, ColumnList);
int numRows = 0;
int[] ColumnLength = new int[results[0].Length];
foreach (String[] rowData in results) {
for (int i = 0; i < rowData.Length; i++) {
ColumnLength[i] = Math.Max(ColumnLength[i], rowData[i].Length);
}
numRows++;
}
for (int i = 0; i < ColumnLength.Length; i++) {
ColumnLength[i] = Math.Min(ColumnLength[i], MAX_COLUMN_WIDTH);
}
int size = 0;
foreach (int i in ColumnLength) {
size += 1 + i;
}
size++;
size *= (numRows + 1);
bool isHeader = true;
StringBuilder output = new StringBuilder(size);
foreach (String[] rowData in results) {
if (!isHeader) {
output.AppendLine();
}
for (int i = 0; i < rowData.Length; i++) {
output.Append("|").Append(PadColumn(TrimToMaxLength(rowData[i]), ColumnLength[i]));
}
output.Append("|");
if (isHeader) {
isHeader = false;
output.AppendLine();
for (int i = 0; i < rowData.Length; i++) {
output.Append("+");
output.Insert(output.Length, "-", ColumnLength[i]);
}
output.Append("+");
}
}
return new SqlChars(output.ToString());
}
internal void ExecuteCommand(System.Data.SqlTypes.SqlString command)
{
SqlDataReader reader = testDatabaseFacade.executeCommand(command);
reader.Close();
}
public static SqlChars TableToString(SqlString TableName, SqlString OrderOption, SqlString ColumnList)
{
if (TableName.IsNull)
{
throw new Exception("Object name cannot be NULL");
}
if (OrderOption.IsNull)
{
OrderOption = "";
}
if (ColumnList.IsNull)
{
ColumnList = "";
}
TestDatabaseFacade testDbFacade = new TestDatabaseFacade();
String selectStmt = getSqlStatement(ref TableName, ref OrderOption);
SqlDataReader reader = testDbFacade.executeCommand(selectStmt);
List <String[]> results = getTableStringArray(reader, ColumnList);
int numRows = 0;
int[] ColumnLength = new int[results[0].Length];
foreach (String[] rowData in results)
{
for (int i = 0; i < rowData.Length; i++)
{
ColumnLength[i] = Math.Max(ColumnLength[i], rowData[i].Length);
}
numRows++;
}
for (int i = 0; i < ColumnLength.Length; i++)
{
ColumnLength[i] = Math.Min(ColumnLength[i], MAX_COLUMN_WIDTH);
}
int size = 0;
foreach (int i in ColumnLength)
{
size += 1 + i;
}
size++;
size *= (numRows + 1);
bool isHeader = true;
StringBuilder output = new StringBuilder(size);
foreach (String[] rowData in results)
{
if (!isHeader)
{
output.AppendLine();
}
for (int i = 0; i < rowData.Length; i++)
{
output.Append("|").Append(PadColumn(TrimToMaxLength(rowData[i]), ColumnLength[i]));
}
output.Append("|");
if (isHeader)
{
isHeader = false;
output.AppendLine();
for (int i = 0; i < rowData.Length; i++)
{
output.Append("+");
output.Insert(output.Length, "-", ColumnLength[i]);
}
output.Append("+");
}
}
return(new SqlChars(output.ToString()));
}
/// <summary>
/// Replacement for "INSERT INTO [table] EXEC" pattern this works like tSQLt.ResultSetFilter
/// with the addition of the extra TargetTable param
///
/// This method will:
/// - Execute the desired command and
/// - Extract the selected result set
/// - Insert new rows into the TargetTable.
///
/// Notes:
/// - Unlike ResultSetFilter this cannot stream the new rows into the target table, they're kept in memory until after command is finished.
/// - Only columns in common to the result set and target table will be populated;
/// - extra result columns will be discarded
/// - extra target table columns will be populated with NULL (will fail if these are 'NOT NULL')
///
/// </summary>
/// <param name="targetTableName">Target Table (this can be a #TemporaryTable, but not a @variable)</param>
/// <param name="resultsetNo">If command returns multiple result sets select which one to capture</param>
/// <param name="command">SQL String that will be executed</param>
/// <exception cref="InvalidResultSetException"></exception>
/// <example>
/// <![CDATA[
/// CREATE TABLE #Actual (val INT);
/// EXEC tSQLt.ResultSetToTable #Actual, 1, 'SELECT 1 AS val; SELECT 2 AS val; SELECT 3 AS val;';
/// -- #Actual now contains a single row with 1 in it
/// ]]></example>
internal void sendSelectedResultSetToTable(string targetTableName, SqlInt32 resultsetNo, SqlString command)
{
validateResultSetNumber(resultsetNo);
// Establish a updatable DataTable linked to the target table
using (var adapter = testDatabaseFacade.getSQLAdapterForCommand("SELECT TOP 0 * FROM " + targetTableName))
// ReSharper disable once UnusedVariable
// SqlCommandBuilder is needed to allow targetDataSet to be updated.
using (var builder = new SqlCommandBuilder(adapter))
using (var targetDataSet = new DataSet())
{
adapter.Fill(targetDataSet);
var targetTable = targetDataSet.Tables[0];
// Run the desired command using a SqlDataRreader so that the results can be streamed.
using (var dataReader = testDatabaseFacade.executeCommand(command))
{
// Find the relevent result by steping througth Result Sets
int resultsetCount = 0;
do
{
// ignore blank Result Sets.
if (dataReader.HasRows || dataReader.FieldCount > 0)
{
resultsetCount++;
if (resultsetCount == resultsetNo)
{
break;
}
}
} while (dataReader.NextResult());
if (resultsetCount < resultsetNo)
{
throw new InvalidResultSetException(
string.Format("Execution returned only {0} ResultSets. ResultSet [{1}] does not exist.",
resultsetCount, resultsetNo));
}
// 4. Work out which columns to keep
// and Extract columns from result's schema
var schemaTable = dataReader.GetSchemaTable();
if (schemaTable == null)
{
return; // ResultSet has no data.
}
var availableColumns = new Dictionary <String, Int32>();
foreach (DataRow c in schemaTable.Rows)
{
availableColumns.Add((string)c["ColumnName"], (int)c["ColumnOrdinal"]);
}
// 3b. Use target table schema to determine which columns are common.
var commonColumns = new Dictionary <DataColumn, Int32>();
foreach (DataColumn k in targetTable.Columns)
{
int index;
if (availableColumns.TryGetValue(k.ToString(), out index))
{
commonColumns.Add(k, index);
}
}
// Step 4. Itterate through Results creating new rows in Target Table
object[] recordData = new object[dataReader.FieldCount];
while (dataReader.Read())
{
dataReader.GetValues(recordData);
var newrow = targetTable.NewRow();
foreach (KeyValuePair <DataColumn, Int32> kvp in commonColumns)
{
newrow[kvp.Key] = recordData[kvp.Value];
}
targetTable.Rows.Add(newrow);
}
}
adapter.Update(targetDataSet);
}
}