internal Boolean ExecuteSqlCommand(SqlCommand sqlCommand, ref Int32 recordsAffected, ref String returnMessage, ref Int32 statusCode) { // Get the connection to the database sqlCommand.Connection = sqlConnection; // Execute the non-query... try { // SqlTransaction sqlTransaction = null; String command = sqlCommand.CommandText; //if (rollback) //{ // sqlTransaction = sqlCommand.Connection.BeginTransaction(); // sqlCommand.Transaction = sqlTransaction; //} if (rollback && CommandType.Text == sqlCommand.CommandType) sqlCommand.CommandText = "BEGIN TRANSACTION UnitTest\r\n" + command + "\r\nROLLBACK TRANSACTION UnitTest"; //TODO: Need to use ADO.NET transaction for stored procedures Int32 rowsAffected = sqlCommand.ExecuteNonQuery(); // if (null != sqlTransaction) // { // if (rollback) // sqlTransaction.Rollback(); // sqlTransaction.Dispose(); // } CheckUncommittedTransactions(sqlCommand); returnMessage = String.Format("{0} row(s) affected.", rowsAffected); } catch (Exception ex) { statusCode = 400; returnMessage = ex.ToString(); Logging.Log(returnMessage, 1); return false; } return true; }We have several overloads for this method each dealing with different types of ExecuteCommand. Some also return formatted output directly, e.g.
internal Boolean ExecuteSqlCommand(SqlCommand sqlCommand, ReturnType returnType, ref Int32 recordCount, ref String returnMessage, ref Int32 statusCode) { // For STR, XML, and XM2 return types // Execute the query and populate the formatted return string returnMessage = ""; recordCount = 0; sqlCommand.Connection = sqlConnection; SqlDataReader sqlDataReader = null; try { // Execute the command sqlDataReader = sqlCommand.ExecuteReader(); // Generate the return string (STR/XML/XM2), if applicable returnMessage = GetFormattedReturn(sqlDataReader, returnType, out recordCount); if (0 == recordCount && ReturnType.STR == returnType && String.IsNullOrWhiteSpace(returnMessage)) { returnMessage = "DONE"; statusCode = 1; } // NN 02/05/2013 Added support for multiple result sets if (0 == recordCount && ReturnType.STR == returnType) { // Do nothing } else { StringBuilder result = new StringBuilder(returnMessage); while (sqlDataReader.NextResult()) { result.AppendFormat("\r\n{0}", GetFormattedReturn(sqlDataReader, returnType, out recordCount)); } returnMessage = result.ToString(); } // Check for uncommitted transactions and commit them, if necessary String command = sqlCommand.CommandText; CheckUncommittedTransactions(sqlCommand); } catch (Exception ex) { returnMessage = ex.ToString(); Logging.Log(returnMessage, 1); statusCode = 400; return false; } finally { if (null != sqlDataReader && !sqlDataReader.IsClosed) { sqlDataReader.Close(); // sqlDataReader.Dispose(); } } return true; }Basically, the dll we're building is serving as a middle-tier for our main application written in C++ and it performs different database related activity. The C++ application sends invoke XML string and receives back XML (in few cases just string or specially formatted string).