public static void ResultSetCapture
(SqlString Command,
SqlString rsTable1, SqlString rsColumnList1,
SqlString rsTable2, SqlString rsColumnList2
)
{
// Storage for the parameters:
CommandCall cmd = new CommandCall();
// the command to execute
cmd.Command = Command.ToString();
// the tables to hold the result sets produced
cmd.rsTable = new List<ResultTables>();
// rsTableN where N = result set sequence number
// rsColumnList is the list of columns to capture * = All
if ( rsTable1.ToString() != "")
{
cmd.rsTable.Add(new ResultTables { resultSetSeq = 1, tableName = rsTable1.ToString(), columnList = rsColumnList1.ToString().ToLower() });
}
if ( rsTable2.ToString() != "" )
{
cmd.rsTable.Add(new ResultTables { resultSetSeq = 2, tableName = rsTable2.ToString(), columnList = rsColumnList2.ToString().ToLower() });
}
//*******************************************************************
// Execute & Save results to a dataSet
DataSet CommnandResults = CommandCallUtilities.ExecCommand(cmd);
DataSet dsTargetTablesOut = CommandCallUtilities.getTargetTableMetaData(cmd);
// Build a dataset with the output tables
DataSet dsResultSetSchema = CommandCallUtilities.getResultSetMetaData(cmd);
// Use the columnList choose the columns and meta data from the result set
// and then add any columns that are missing to the target table
// Map the columns of the result set to the columns of the output table;
bool result = CommandCallUtilities.mapResultsToOutputTables(CommnandResults, dsTargetTablesOut, dsResultSetSchema, cmd);
return;
}
// map matching column names between the results table and the target table
internal static bool mapResultsToOutputTables(DataSet CommnandResults, DataSet dsTargetTableOut, DataSet dsResultSetSchema, CommandCall CallRequest)
{
// loop through result set tables; find matching columns and write out the results
DataTable dtResultsTable = new DataTable();
DataTable dtOuptutTable = new DataTable();
List<string> matchedColumns = new List<string>();
List<ColumnRef> crSelectedCoulmns = GetTargetColumns("");
string sqlInsert = "";
var sqlValues = "";
string sqlCommandText = "";
int dtIdx = 0;
// Check how many we have of each; not enough output tables means nothing written to it
int resultTableCount = CommnandResults.Tables.Count;
int targetTableCount = dsResultSetSchema.Tables.Count;
// no results, we're done
if (resultTableCount == 0) return true;
// no target tables, we're done
if (targetTableCount == 0) return true;
// resultToTargetMap let's us map a specific result set to a target table
int resultToTargetMap;
string tblName;
string csvColList;
// as long as we have another result set and target table, keep looping
while(dtIdx <= targetTableCount -1 )
{
// get the output table name and find it in the target table list; get the resultSetSeq number
tblName = dsResultSetSchema.Tables[dtIdx].TableName;
resultToTargetMap = CallRequest.rsTable.Find(m => m.tableName == tblName).resultSetSeq -1;
csvColList = CallRequest.rsTable.Find(m => m.tableName == tblName).columnList;
// call function to get a list of the matching columns if a mapping exists
if(resultToTargetMap > -1)
{
matchedColumns = FindMatchingColumns(CommnandResults.Tables[resultToTargetMap], dsTargetTableOut.Tables[dtIdx], dsResultSetSchema.Tables[dtIdx], csvColList);
}
else
{
matchedColumns.Clear();
}
// use the matching column list to build and execute an insert; loop through all rows
if (matchedColumns.Count > 0)
{
// We have matching columns:
// Build the insert statement with the column names from the matched columns
sqlInsert = "INSERT " + dsResultSetSchema.Tables[dtIdx].TableName + " (";
// Add the columns to be inserted
foreach(string m in matchedColumns)
{
sqlInsert += m + ", ";
}
// Remove the trailing comma and space(do we need to remove the space?)
sqlInsert = sqlInsert.TrimEnd(',', ' ');
// add the closing parenthesis
sqlInsert += ") ";
// get the connection string
string connectionString = getConnectionString();
LogMessage(""); // create a line break
// for each row in the result set, build the VALUES clause and execute the INSERT
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand tsqlWrite = new SqlCommand("", conn);
int rowCount;
bool mRow;
System.Type dc;
string colValue;
byte[] ba;
foreach (DataRow r in CommnandResults.Tables[resultToTargetMap].Rows)
{
// Add the VALUES clause
sqlValues = " VALUES ( ";
// get the data from the columns
foreach (string m in matchedColumns)
{
//quoteName = true; //quote every thing
mRow = DBNull.Value.Equals(r[m]);
dc = r[m].GetType();
switch (dc.Name.ToLower())
{
case "byte[]":
ba = (byte[])r[m];
colValue = BitConverter.ToString(ba);
colValue = "0x" + colValue.Replace("-","") + ",";
break;
case "boolean":
if(r[m].ToString() == "True")
{
colValue = "1,";
}
else
{
colValue = "0,";
}
break;
default:
colValue = "N'" + r[m].ToString().Replace("'","''") + "', ";
break;
}
if (DBNull.Value.Equals(r[m]))
{
sqlValues += "NULL, ";
}
else
{
sqlValues += colValue;
}
}
//strip out the remnants
sqlValues = sqlValues.TrimEnd(',', ' ');
// add the closing parenthesis
sqlValues += ") ";
// Form the full INSERT statement
sqlCommandText = sqlInsert + sqlValues;
LogMessage("Command: " + sqlInsert + sqlValues);
// Prepare command
tsqlWrite.CommandText = sqlCommandText;
tsqlWrite.CommandType = CommandType.Text;
// Execute the insert
rowCount = tsqlWrite.ExecuteNonQuery();
}
}
}
dtIdx++;
}
return true;
}
// Returns dataset with meta data of the target tables
internal static DataSet getTargetTableMetaData(CommandCall cmd)
{
DataSet dsTargets = new DataSet();
DataTable dtTarget = new DataTable();
string queryStr;
string connectionString = getConnectionString();
using (SqlConnection conn = new SqlConnection(connectionString))
{
foreach (ResultTables tgt in cmd.rsTable)
{
// ** Should be a Try Catch block here ************
conn.Open();
queryStr = "SELECT * FROM " + tgt.tableName + " WHERE 1 = 0";
SqlDataAdapter daTgtTable = new SqlDataAdapter(queryStr, conn);
daTgtTable.SelectCommand.CommandType = CommandType.Text;
daTgtTable.Fill(dsTargets, tgt.tableName);
conn.Close();
}
}
return dsTargets;
}
// Returns a DataSet with meta data of the result sets
internal static DataSet getResultSetMetaData(CommandCall CallRequest)
{
DataSet dsResultSetSchema = new DataSet();
DataTable schemaTable = new DataTable();
DataTable resultSetTable = new DataTable();
string connectionString = getConnectionString();
// Get Schema Table for the result sets
SqlDataReader SqlDr;
SqlCommand SqlCmd;
//int rowCount;
int tblCount = 0;
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCmd = conn.CreateCommand();
SqlCmd.CommandText = CallRequest.Command;
SqlDr = SqlCmd.ExecuteReader();
SqlDr.Read();
tblCount = 0;
// for each result table in SqlDataReader:
while (SqlDr.HasRows)
{
schemaTable = SqlDr.GetSchemaTable();
schemaTable.TableName = CallRequest.rsTable[tblCount].tableName;
dsResultSetSchema.Tables.Add(schemaTable);
// capture the result set table here?
//resultSetTable.Load(SqlDr);
SqlDr.NextResult();
tblCount += 1;
}
conn.Close();
}
return dsResultSetSchema;
}
// Execute the SQL command, Return a DataSet with the result set(s)
internal static DataSet ExecCommand(CommandCall cmd)
{
DataSet dsResults = new DataSet();
string connectionString = getConnectionString();
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlDataAdapter daExecCommand = new SqlDataAdapter(cmd.Command, conn);
daExecCommand.SelectCommand.CommandType = CommandType.Text;
int rc = daExecCommand.Fill(dsResults);
LogMessage("ExecCommand result = " + rc.ToString());
conn.Close();
}
return dsResults;
}
