internal static string GetConnectionString(InputParameters InputParams)
{
SqlConnectionStringBuilder _ConnectionString;
if (InputParams.ConnectionString != String.Empty)
{
Debug("Using passed-in ConnectionString.");
_ConnectionString = new SqlConnectionStringBuilder(InputParams.ConnectionString);
_ConnectionString.Pooling = false; // override just in case it was passed in
_ConnectionString.ContextConnection = false; // override just in case it was passed in
return(_ConnectionString.ConnectionString);
}
_ConnectionString = new SqlConnectionStringBuilder();
_ConnectionString.Pooling = false;
_ConnectionString.ApplicationIntent = InputParams.AppIntent;
_ConnectionString.ApplicationName = InputParams.ApplicationName;
_ConnectionString.AttachDBFilename = InputParams.AttachDBFilename;
_ConnectionString.ConnectTimeout = InputParams.LoginTimeout;
_ConnectionString.DataSource = InputParams.Server;
_ConnectionString.Encrypt = InputParams.EncryptConnection;
_ConnectionString.InitialCatalog = InputParams.DatabaseName;
_ConnectionString.MultiSubnetFailover = InputParams.MultiSubnetFailover;
_ConnectionString.TrustServerCertificate = InputParams.TrustServerCertificate;
_ConnectionString.WorkstationID = InputParams.WorkstationName;
_ConnectionString.PacketSize = InputParams.PacketSize;
if (InputParams.UserID != String.Empty)
{
_ConnectionString.UserID = InputParams.UserID;
_ConnectionString.Password = InputParams.Password;
_ConnectionString.IntegratedSecurity = false;
}
else
{
_ConnectionString.IntegratedSecurity = true;
}
return(_ConnectionString.ConnectionString);
}
internal static string GetConnectionString(InputParameters InputParams)
{
SqlConnectionStringBuilder _ConnectionString;
if (InputParams.ConnectionString != String.Empty)
{
Debug("Using passed-in ConnectionString.");
_ConnectionString = new SqlConnectionStringBuilder(InputParams.ConnectionString);
_ConnectionString.Pooling = false; // override just in case it was passed in
_ConnectionString.ContextConnection = false; // override just in case it was passed in
return _ConnectionString.ConnectionString;
}
_ConnectionString = new SqlConnectionStringBuilder();
_ConnectionString.Pooling = false;
_ConnectionString.ApplicationIntent = InputParams.AppIntent;
_ConnectionString.ApplicationName = InputParams.ApplicationName;
_ConnectionString.AttachDBFilename = InputParams.AttachDBFilename;
_ConnectionString.ConnectTimeout = InputParams.LoginTimeout;
_ConnectionString.DataSource = InputParams.Server;
_ConnectionString.Encrypt = InputParams.EncryptConnection;
_ConnectionString.InitialCatalog = InputParams.DatabaseName;
_ConnectionString.MultiSubnetFailover = InputParams.MultiSubnetFailover;
_ConnectionString.TrustServerCertificate = InputParams.TrustServerCertificate;
_ConnectionString.WorkstationID = InputParams.WorkstationName;
_ConnectionString.PacketSize = InputParams.PacketSize;
if (InputParams.UserID != String.Empty)
{
_ConnectionString.UserID = InputParams.UserID;
_ConnectionString.Password = InputParams.Password;
_ConnectionString.IntegratedSecurity = false;
}
else
{
_ConnectionString.IntegratedSecurity = true;
}
return _ConnectionString.ConnectionString;
}
public QueryBatches(InputParameters InputParams)
{
_BatchTerminator = InputParams.BatchTerminator;
// add a place-holder batch in slot 1 since the first call to
// NextBatch() will remove the first entry.
this._Batches.Add(new QueryBatch(" { placeholder } ", 1));
if (InputParams.InputFiles.Count > 0)
{
Helpers.Debug("Query from files.");
this._InputFiles = InputParams.InputFiles;
}
else
{
Helpers.Debug("Query from input parameter.");
ParseBatches(InputParams.Query);
this._InputFiles = new List<string>();
}
return;
}
public QueryBatches(InputParameters InputParams)
{
_BatchTerminator = InputParams.BatchTerminator;
// add a place-holder batch in slot 1 since the first call to
// NextBatch() will remove the first entry.
this._Batches.Add(new QueryBatch(" { placeholder } ", 1));
if (InputParams.InputFiles.Count > 0)
{
Helpers.Debug("Query from files.");
this._InputFiles = InputParams.InputFiles;
}
else
{
Helpers.Debug("Query from input parameter.");
ParseBatches(InputParams.Query);
this._InputFiles = new List <string>();
}
return;
}
static int Main(string[] args)
{
InputParameters _InputParams;
Console.Title = "Simple SQL Exec (from Sql Quantum Leap -- http://SqlQuantumLeap.com/)";
try
{
_InputParams = new InputParameters(args);
}
catch(ArgumentException _ArgException)
{
Display.Error(_ArgException.Message);// + " (" + _ArgException.ParamName + ")");
return 1;
}
catch(Exception _Exception)
{
Display.Error(_Exception.Message);
return 2;
}
Helpers.SetDebugMode(_InputParams.DebugInfoFile);
Helpers.Debug("--===========================================================--");
Helpers.Debug("Starting...");
if (_InputParams.DisplayUsage)
{
Display.Usage();
return 0;
}
string _ConnectionString;
try
{
Helpers.Debug("Parsing ConnectionString.");
_ConnectionString = Helpers.GetConnectionString(_InputParams);
}
catch(Exception _Exception)
{
Display.Error(_Exception.Message);
return 3;
}
try
{
ProcessQueries(_InputParams, _ConnectionString);
}
catch (SqlException _SqlException)
{
Display.Error(String.Concat(_SqlException.Message, "\n\n",
"Error Number: ", _SqlException.Number, "\n",
"Error Level: ", _SqlException.Class, "\n",
"Error State: ", _SqlException.State, "\n",
"Error Procedure: ", _SqlException.Procedure, "\n",
"Error Line: ", _SqlException.LineNumber, "\n",
"HRESULT: ", _SqlException.ErrorCode, "\n"
));
return 4;
}
catch (Exception _Exception)
{
Display.Error(_Exception.Message);
Helpers.Debug(_Exception.Message);
Helpers.Debug(_Exception.StackTrace);
return 5;
}
try
{
if (_InputParams.RowsAffectedDestination != String.Empty)
{
Helpers.Debug("Handling RowsAffected.");
if (_InputParams.RowsAffectedDestination.LastIndexOf(".") > -1)
{
Helpers.Debug("RowsAffected saved to file: " + _InputParams.RowsAffectedDestination);
File.WriteAllText(_InputParams.RowsAffectedDestination,
Capture._RowsAffected.ToString());
}
else
{
Helpers.Debug("RowsAffected stored in variable: " + _InputParams.RowsAffectedDestination);
Environment.SetEnvironmentVariable(_InputParams.RowsAffectedDestination,
Capture._RowsAffected.ToString(), EnvironmentVariableTarget.User);
}
}
}
catch (Exception _Exception)
{
Display.Error(_Exception.Message);
return 7;
}
Helpers.Debug("Ending...");
Helpers.Debug("--===========================================================--");
return 0;
}
static int Main(string[] args)
{
InputParameters _InputParams;
Console.Title = "Simple SQL Exec (from Sql Quantum Leap -- http://SqlQuantumLeap.com/)";
try
{
_InputParams = new InputParameters(args);
}
catch (ArgumentException _ArgException)
{
Display.Error(_ArgException.Message);// + " (" + _ArgException.ParamName + ")");
return(1);
}
catch (Exception _Exception)
{
Display.Error(_Exception.Message);
return(2);
}
Helpers.SetDebugMode(_InputParams.DebugInfoFile);
Helpers.Debug("--===========================================================--");
Helpers.Debug("Starting...");
if (_InputParams.DisplayUsage)
{
Display.Usage();
return(0);
}
string _ConnectionString;
try
{
Helpers.Debug("Parsing ConnectionString.");
_ConnectionString = Helpers.GetConnectionString(_InputParams);
}
catch (Exception _Exception)
{
Display.Error(_Exception.Message);
return(3);
}
try
{
ProcessQueries(_InputParams, _ConnectionString);
}
catch (Exception _Exception)
{
Display.Error(_Exception.Message);
Helpers.Debug(_Exception.Message);
Helpers.Debug(_Exception.StackTrace);
return(5);
}
try
{
if (_InputParams.RowsAffectedDestination != String.Empty)
{
Helpers.Debug("Handling RowsAffected.");
if (_InputParams.RowsAffectedDestination.LastIndexOf(".") > -1)
{
Helpers.Debug("RowsAffected saved to file: " + _InputParams.RowsAffectedDestination);
File.WriteAllText(_InputParams.RowsAffectedDestination,
Capture._RowsAffected.ToString());
}
else
{
Helpers.Debug("RowsAffected stored in variable: " + _InputParams.RowsAffectedDestination);
Environment.SetEnvironmentVariable(_InputParams.RowsAffectedDestination,
Capture._RowsAffected.ToString(), EnvironmentVariableTarget.User);
}
}
}
catch (Exception _Exception)
{
Display.Error(_Exception.Message);
return(7);
}
Helpers.Debug("Ending...");
Helpers.Debug("--===========================================================--");
return(0);
}
private static void ProcessQueries(InputParameters InputParams, string ConnectionString)
{
using (SqlConnection _Connection = new SqlConnection(ConnectionString))
{
if (InputParams.MessagesFile != String.Empty)
{
Helpers.Debug("Setting up the MessagesFile and handler.");
// clear out existing file
File.Delete(InputParams.MessagesFile);
Capture.MessagesFile = InputParams.MessagesFile;
Capture.OutputEncoding = InputParams.OutputEncoding;
_Connection.InfoMessage += Capture.InfoMessageHandler;
}
_Connection.FireInfoMessageEventOnUserErrors = false;
using (SqlCommand _Command = _Connection.CreateCommand())
{
_Command.CommandType = CommandType.Text;
_Command.CommandTimeout = InputParams.QueryTimeout;
if (InputParams.RowsAffectedDestination != String.Empty)
{
Helpers.Debug("Setting up the RowsAffected handler.");
_Command.StatementCompleted += Capture.StatementCompletedHandler;
}
_Connection.Open();
ResultsOutput _Output = null;
QueryBatches _Queries = null;
try
{
_Queries = new QueryBatches(InputParams);
_Output = Helpers.GetResultsOutput(InputParams);
while (_Queries.NextBatch())
{
_Command.CommandText = _Queries.GetBatch();
Helpers.Debug("Executing the batch...");
using (SqlDataReader _Reader = _Command.ExecuteReader())
{
object[] _ResultRow;
string _OutputRow;
do
{
_Output.Send(_Output.GetHeader(_Reader, InputParams.ColumnSeparator));
if (_Reader.HasRows)
{
_ResultRow = new object[_Reader.FieldCount];
while (_Reader.Read())
{
_Reader.GetValues(_ResultRow);
_OutputRow = String.Join(InputParams.ColumnSeparator, _ResultRow);
_Output.Send(_OutputRow);
}
_ResultRow = null;
}
} while (_Reader.NextResult());
} // using (SqlDataReader...
} // while (_Queries.NextBatch())
}
//catch
//{
// throw;
//}
finally
{
if (_Output.GetType() == typeof(OutputFile))
{
Helpers.Debug("Close output file!");
_Output.Dispose();
}
}
} // using (SqlCommand...
} // using (SqlConnection
return;
}
internal static ResultsOutput GetResultsOutput(InputParameters InputParams)
{
if (InputParams.OutputFile == String.Empty)
{
return new OutputDisplay();
}
else
{
return new OutputFile(InputParams.OutputFile,
InputParams.OutputFileAppend, InputParams.OutputEncoding);
}
}
private static void ProcessQueries(InputParameters InputParams, string ConnectionString)
{
using (SqlConnection _Connection = new SqlConnection(ConnectionString))
{
if (InputParams.MessagesFile != String.Empty)
{
Helpers.Debug("Setting up the MessagesFile and handler.");
// clear out existing file
File.Delete(InputParams.MessagesFile);
Capture.MessagesFile = InputParams.MessagesFile;
Capture.OutputEncoding = InputParams.OutputEncoding;
_Connection.InfoMessage += Capture.InfoMessageHandler;
}
_Connection.FireInfoMessageEventOnUserErrors = false;
using (SqlCommand _Command = _Connection.CreateCommand())
{
_Command.CommandType = CommandType.Text;
_Command.CommandTimeout = InputParams.QueryTimeout;
if (InputParams.RowsAffectedDestination != String.Empty)
{
Helpers.Debug("Setting up the RowsAffected handler.");
_Command.StatementCompleted += Capture.StatementCompletedHandler;
}
_Connection.Open();
ResultsOutput _Output = null;
QueryBatches _Queries = null;
try
{
_Queries = new QueryBatches(InputParams);
_Output = Helpers.GetResultsOutput(InputParams);
while (_Queries.NextBatch())
{
_Command.CommandText = _Queries.GetBatch();
Helpers.Debug("Executing the batch...");
using (SqlDataReader _Reader = _Command.ExecuteReader())
{
object[] _ResultRow;
string _OutputRow;
do
{
_Output.Send(_Output.GetHeader(_Reader, InputParams.ColumnSeparator));
if (_Reader.HasRows)
{
_ResultRow = new object[_Reader.FieldCount];
while (_Reader.Read())
{
_Reader.GetValues(_ResultRow);
_OutputRow = String.Join(InputParams.ColumnSeparator, _ResultRow);
_Output.Send(_OutputRow);
}
_ResultRow = null;
}
} while (_Reader.NextResult());
} // using (SqlDataReader...
} // while (_Queries.NextBatch())
}
//catch
//{
// throw;
//}
finally
{
if (_Output.GetType() == typeof(OutputFile))
{
Helpers.Debug("Close output file!");
_Output.Dispose();
}
}
} // using (SqlCommand...
} // using (SqlConnection
return;
}