private void ExecuteSql(ThreadObj Param)
{
string query = Param.Query;
// new recordset reference
ADODB.Recordset Data = null;
// keep a connection reference
ADODB.Connection Connect = null;
try
{
// change cursor to wait cursor
this.Invoke(ChangeCursorDel, new object[] { Cursors.WaitCursor });
// initialize the message string to be empty
string Message = "";
string Warnings = "";
// keep track of the max number of columns
int nMaxNumCols = 0;
// fill in a data table with our results
DataTable SqlResults = new DataTable();
// open the connection
Connect = new Connection();
Connect.ConnectionString = Param.ConnectionString;
Connect.CursorLocation = CursorLocationEnum.adUseClient;
Connect.Open();
// initialize an object to store the number of records affected
object obRecsAffected;
// number of queries returned so far is none
int nQueriesReturned = 0;
// construct a command object
ADODB.Command SqlCmd = new Command();
SqlCmd.ActiveConnection = Connect;
SqlCmd.CommandText = (string)query;
SqlCmd.CommandType = CommandTypeEnum.adCmdText;
// execute the sql statement
Data = Connect.Execute((string)query, out obRecsAffected);
// while there are recordsets
while (Data != null)
{
// for this query, print out all the errors that occurred
foreach (ADODB.Error e in Connect.Errors)
{
Warnings += e.Description + "\r\n";
}
// we have another query, increment the count
nQueriesReturned++;
// if there is something to read (hence the object is not closed)...
if ((ADODB.ObjectStateEnum)Data.State != ADODB.ObjectStateEnum.adStateClosed)
{
// count of rows retrieved
int nRowsRecieved = 0;
// if this recordset has more columns in it than previous columns
for (int i = nMaxNumCols; i < Data.Fields.Count; i++)
{
// add a column and increment the max col count
DataColumn temp = new DataColumn(Data.Fields[i].Name, typeof(string));
SqlResults.Columns.Add(temp);
nMaxNumCols++;
}
// grab the current time in ticks
long InitialTime = DateTime.Now.Ticks;
// while there is stuff in the recordset
while (!Data.EOF)
{
// create a new row
DataRow tempRow = SqlResults.NewRow();
// fill each column with data
for (int i = 0; i < Data.Fields.Count; i++)
{
tempRow[i] = Data.Fields[i].Value.ToString();
}
// add it to the results
SqlResults.Rows.Add(tempRow);
nRowsRecieved++;
// if the user didnt cancel, display the record number being loaded, otherwise the user did cancel so stop doing work
if (!m_bUserCancel)
{
this.Invoke(SetResultsDel, new object[] { String.Format("Loading Record {0} in Query {1}...", nRowsRecieved, nQueriesReturned), false });
}
else
{
this.Invoke(SetResultsDel, new object[] { "Canceling... Please wait...", false });
break;
}
// continue to the next record in the recordset
Data.MoveNext();
}
// the time elapsed is the time now minus the initial
double TimeElapsed = ((float)(DateTime.Now.Ticks - InitialTime)) / 10000000.0;
// add the number of rows returned and how long it took to the messages to display once the results are ready to be displayed
Message += nQueriesReturned + ": " + nRowsRecieved + " row(s) returned. " + String.Format("({0:0.0000} seconds)", TimeElapsed) + "\r\n";
}
else
{
// otherwise, it was a non action query, so records must have been affected
Message += nQueriesReturned + ": " + (int)obRecsAffected + " record(s) affected.\r\n";
}
// if the user canceled, just return the form to its original state and state that the user canceled
if (m_bUserCancel)
{
this.Invoke(SetUpFormDel, new object[] { true });
this.Invoke(ShowResultsDel, new object[] { SqlResults, Message });
this.Invoke(SetResultsDel, new object[] { "Loading Was Canceled by User.", true });
return;
}
// try to go to the next recordset
try
{
Data = Data.NextRecordset(out obRecsAffected);
}
catch (COMException ex)
{
// if for some reason, the provider doesnt like that, just break out on the first recordset
if (ex.ErrorCode == -2146825037)
{
Data = null;
}
else
{
// otherwise, just rethrow our excetion, its a bigger problem
throw ex;
}
}
}
this.Invoke(SetMessageDel, new object[] { Warnings.Trim() });
// after all records have been run through, go ahead and show the results
this.Invoke(ShowResultsDel, new object[] { SqlResults, Message });
}
catch (Exception ex)
{
string ErrorText = "";
if (Connect.Errors.Count > 0)
{
// for this query, print out all the errors that occurred
foreach (ADODB.Error e in Connect.Errors)
{
ErrorText += "Error " + e.Number + ": " + e.Description + "\r\n";
}
Connect.Errors.Clear();
}
else
{
ErrorText = "Error: " + ex.Message;
}
// if any exception does happen, dont display results, but print out the error
this.Invoke(SetResultsDel, new object[] { ErrorText.Trim(), true });
this.Invoke(SetUpFormDel, new object[] { true });
// change cursor to default cursor
this.Invoke(ChangeCursorDel, new object[] { Cursors.Default });
return;
}
finally
{
if (Data != null && Data.State != (int)ConnectionState.Closed)
{
Data.Close();
}
}
}