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