public DataTable ExcuteReader(
Connection aConn,
String asqlStmt,
Object aDataObject)
{
if (asqlStmt.Contains("'S'"))
{
asqlStmt = asqlStmt.Replace
(
"'S'",
"''''S'");
}
using (var aDataTable = new DataTable())
{
SqlCommand command;
if (aConn != null)
{
_aSqlErrorCapture = new SqlErrorCapture
(
aConn,
asqlStmt);
}
if (aConn != null && aConn.Conn != null)
{
using (command = aConn.Conn.CreateCommand())
{
command.CommandText = asqlStmt;
//String.Format("select * from [dbo].[USER_CONTROL_LAYOUT] where [USER_ID] = '{0}'", aUser_Id);
command.CommandType = CommandType.Text;
try
{
SqlDataReader reader = command.ExecuteReader();
aDataTable.Load(reader);
if (aDataObject != null)
{
SqlErrorText = "Status: ok";
}
}
catch (SqlException e)
{
if (!IsConnectionOpen())
{
//connection closed, open it again.
if (aDataObject != null)
{
SqlErrorText =
"Status: Error with this SQL request. Trying to reconnect and redo query, Error message is: "
+
e.Message;
}
try
{
aConn.CreateConnection
(
aConn.AUserName,
aConn.APassword,
aConn.AServerAddress,
aConn.ADatabaseName,
aConn.AType);
if (aConn.Conn != null)
{
command = aConn.Conn.CreateCommand();
}
command.CommandText = asqlStmt;
//String.Format("select * from [dbo].[USER_CONTROL_LAYOUT] where [USER_ID] = '{0}'", aUser_Id);
command.CommandType = CommandType.Text;
SqlDataReader reader = command.ExecuteReader();
aDataTable.Load(reader);
Console.Out.WriteLine("Issue with SQL connection: " + e);
}
catch (Exception ex)
{
MessageDialog.Show
(
"Issue with SQL connection \r\n" + ex.InnerException,
"Error",
MessageDialog.MessageBoxButtons.Ok,
MessageDialog.MessageBoxIcon.Error,
"Please Check You Credentials Are Valid" + "\r\n" + ex.StackTrace);
}
}
//removed here to try and stop crashing.
else
{
// else try closing it and going again.
if (aDataObject != null)
{
SqlErrorText =
"Status: Error with this SQL request. Trying force close connection, open a new connection and redo query, Error message is: "
+
e.Message;
}
// MessageBox.Show("Error with this SQL request. Trying force close connection, open a new connection and redo query, Error message is: " + e.Message);
try
{
aConn.CloseConnection();
aConn.CreateConnection
(
aConn.AUserName,
aConn.APassword,
aConn.AServerAddress,
aConn.ADatabaseName,
aConn.AType);
if (aConn.Conn != null)
{
command = aConn.Conn.CreateCommand();
}
command.CommandText = asqlStmt;
//String.Format("select * from [dbo].[USER_CONTROL_LAYOUT] where [USER_ID] = '{0}'", aUser_Id);
command.CommandType = CommandType.Text;
SqlDataReader reader = command.ExecuteReader();
if (!reader.IsClosed)
{
aDataTable.Load(reader);
}
}
catch (Exception ex)
{
MessageDialog.Show
(
"Issue with SQL connection \r\n" + ex.InnerException,
"Error",
MessageDialog.MessageBoxButtons.Ok,
MessageDialog.MessageBoxIcon.Error,
"Please Check You Credentials Are Valid" + "\r\n" + ex.StackTrace);
}
}
}
}
}
// aSmeCallCon.SQLErrorText = "Error with this SQL request. Trying force close connection, open a new connection and redo query, Error message is: ";
return aDataTable;
}
}
public DataTable ExcuteReader
(
Connection aConn,
String asqlStmt,
Object aDataObject)
{
if (asqlStmt.Contains("'S'"))
{
asqlStmt = asqlStmt.Replace
(
"'S'",
"''''S'");
}
using (var aDataTable = new DataTable())
{
SqlCommand command;
if (aConn != null)
{
_aSqlErrorCapture = new SqlErrorCapture
(
aConn,
asqlStmt);
}
if (aConn != null && aConn.Conn != null)
{
using (command = aConn.Conn.CreateCommand())
{
command.CommandText = asqlStmt;
//String.Format("select * from [dbo].[USER_CONTROL_LAYOUT] where [USER_ID] = '{0}'", aUser_Id);
command.CommandType = CommandType.Text;
try
{
SqlDataReader reader = command.ExecuteReader();
aDataTable.Load(reader);
if (aDataObject != null)
{
SqlErrorText = "Status: ok";
}
}
catch (SqlException e)
{
if (!IsConnectionOpen())
{
//connection closed, open it again.
if (aDataObject != null)
{
SqlErrorText =
"Status: Error with this SQL request. Trying to reconnect and redo query, Error message is: "
+
e.Message;
}
try
{
aConn.CreateConnection
(
aConn.AUserName,
aConn.APassword,
aConn.AServerAddress,
aConn.ADatabaseName,
aConn.AType);
if (aConn.Conn != null)
{
command = aConn.Conn.CreateCommand();
}
command.CommandText = asqlStmt;
//String.Format("select * from [dbo].[USER_CONTROL_LAYOUT] where [USER_ID] = '{0}'", aUser_Id);
command.CommandType = CommandType.Text;
SqlDataReader reader = command.ExecuteReader();
aDataTable.Load(reader);
Console.Out.WriteLine("Issue with SQL connection: " + e);
}
catch (Exception ex)
{
MessageDialog.Show
(
"Issue with SQL connection \r\n" + ex.InnerException,
"Error",
MessageDialog.MessageBoxButtons.Ok,
MessageDialog.MessageBoxIcon.Error,
"Please Check You Credentials Are Valid" + "\r\n" + ex.StackTrace);
}
}
//removed here to try and stop crashing.
else
{
// else try closing it and going again.
if (aDataObject != null)
{
SqlErrorText =
"Status: Error with this SQL request. Trying force close connection, open a new connection and redo query, Error message is: "
+
e.Message;
}
// MessageBox.Show("Error with this SQL request. Trying force close connection, open a new connection and redo query, Error message is: " + e.Message);
try
{
aConn.CloseConnection();
aConn.CreateConnection
(
aConn.AUserName,
aConn.APassword,
aConn.AServerAddress,
aConn.ADatabaseName,
aConn.AType);
if (aConn.Conn != null)
{
command = aConn.Conn.CreateCommand();
}
command.CommandText = asqlStmt;
//String.Format("select * from [dbo].[USER_CONTROL_LAYOUT] where [USER_ID] = '{0}'", aUser_Id);
command.CommandType = CommandType.Text;
SqlDataReader reader = command.ExecuteReader();
if (!reader.IsClosed)
{
aDataTable.Load(reader);
}
}
catch (Exception ex)
{
MessageDialog.Show
(
"Issue with SQL connection \r\n" + ex.InnerException,
"Error",
MessageDialog.MessageBoxButtons.Ok,
MessageDialog.MessageBoxIcon.Error,
"Please Check You Credentials Are Valid" + "\r\n" + ex.StackTrace);
}
}
}
}
}
// aSmeCallCon.SQLErrorText = "Error with this SQL request. Trying force close connection, open a new connection and redo query, Error message is: ";
return(aDataTable);
}
}
public DataTable ExcuteReader(
Connection aConn,
String asqlStmt)
{
if (asqlStmt.Contains("'S'"))
{
asqlStmt = asqlStmt.Replace
(
"'S'",
"''''S'");
}
using (var aDataTable = new DataTable())
{
SqlCommand command;
if (aConn != null && aConn.Conn != null)
{
if (aConn.Conn.State == ConnectionState.Closed)
{
aConn.Conn.Open();
}
}
if (aConn != null)
{
_aSqlErrorCapture = new SqlErrorCapture
(
aConn,
asqlStmt);
}
if (aConn != null && aConn.Conn != null)
{
using (command = aConn.Conn.CreateCommand())
{
command.CommandText = asqlStmt;
command.CommandType = CommandType.Text;
try
{
if (!IsConnectionOpen())
{
aConn.CreateConnection
(
aConn.AUserName,
aConn.APassword,
aConn.AServerAddress,
aConn.ADatabaseName,
aConn.AType);
if (aConn.Conn != null)
{
command = aConn.Conn.CreateCommand();
}
command.CommandText = asqlStmt;
command.CommandType = CommandType.Text;
if (command.Connection.State != ConnectionState.Closed)
{
SqlDataReader reader = command.ExecuteReader();
aDataTable.Load(reader);
}
Console.Out.WriteLine("Issue with SQL connection: " + aConn);
}
else
{
if (aConn.Conn != null)
{
command = aConn.Conn.CreateCommand();
}
command.CommandText = asqlStmt;
command.CommandType = CommandType.Text;
try
{
command.Connection.FireInfoMessageEventOnUserErrors = true;
SqlDataReader reader = command.ExecuteReader();
aDataTable.Load(reader);
}
catch (Exception ex)
{
MessageDialog.Show
(
"Issue with SQL connection \r\n" + ex.InnerException,
"Error",
MessageDialog.MessageBoxButtons.Ok,
MessageDialog.MessageBoxIcon.Error,
"Please Check You Credentials Are Valid" + "\r\n" + ex.StackTrace);
}
}
}
catch (SqlException e)
{
if (!IsConnectionOpen())
{
//connection closed, open it again.
aConn.CreateConnection
(
aConn.AUserName,
aConn.APassword,
aConn.AServerAddress,
aConn.ADatabaseName,
aConn.AType);
if (aConn.Conn != null)
{
command = aConn.Conn.CreateCommand();
}
command.CommandText = asqlStmt;
command.CommandType = CommandType.Text;
SqlDataReader reader = command.ExecuteReader();
aDataTable.Load(reader);
MessageDialog.Show
(
"Issue with SQL connection \r\n" + e.InnerException,
"Error",
MessageDialog.MessageBoxButtons.Ok,
MessageDialog.MessageBoxIcon.Error,
"Please Check You Credentials Are Valid" + "\r\n" + e.StackTrace);
}
else
{
// else try closing it and going again.
aConn.CloseConnection();
aConn.CreateConnection
(
aConn.AUserName,
aConn.APassword,
aConn.AServerAddress,
aConn.ADatabaseName,
aConn.AType);
if (aConn.Conn != null)
{
command = aConn.Conn.CreateCommand();
}
command.CommandText = asqlStmt;
command.CommandType = CommandType.Text;
try
{
SqlDataReader reader = command.ExecuteReader();
aDataTable.Load(reader);
}
catch (Exception ex)
{
MessageDialog.Show
(
"Issue with SQL connection \r\n" + ex.InnerException,
"Error",
MessageDialog.MessageBoxButtons.Ok,
MessageDialog.MessageBoxIcon.Error,
"Please Check You Credentials Are Valid" + "\r\n" + ex.StackTrace);
}
}
}
}
}
return aDataTable;
}
}
public DataTable ExcuteReader
(
Connection aConn,
String asqlStmt)
{
if (asqlStmt.Contains("'S'"))
{
asqlStmt = asqlStmt.Replace
(
"'S'",
"''''S'");
}
using (var aDataTable = new DataTable())
{
SqlCommand command;
if (aConn != null && aConn.Conn != null)
{
if (aConn.Conn.State == ConnectionState.Closed)
{
aConn.Conn.Open();
}
}
if (aConn != null)
{
_aSqlErrorCapture = new SqlErrorCapture
(
aConn,
asqlStmt);
}
if (aConn != null && aConn.Conn != null)
{
using (command = aConn.Conn.CreateCommand())
{
command.CommandText = asqlStmt;
command.CommandType = CommandType.Text;
try
{
if (!IsConnectionOpen())
{
aConn.CreateConnection
(
aConn.AUserName,
aConn.APassword,
aConn.AServerAddress,
aConn.ADatabaseName,
aConn.AType);
if (aConn.Conn != null)
{
command = aConn.Conn.CreateCommand();
}
command.CommandText = asqlStmt;
command.CommandType = CommandType.Text;
if (command.Connection.State != ConnectionState.Closed)
{
SqlDataReader reader = command.ExecuteReader();
aDataTable.Load(reader);
}
Console.Out.WriteLine("Issue with SQL connection: " + aConn);
}
else
{
if (aConn.Conn != null)
{
command = aConn.Conn.CreateCommand();
}
command.CommandText = asqlStmt;
command.CommandType = CommandType.Text;
try
{
command.Connection.FireInfoMessageEventOnUserErrors = true;
SqlDataReader reader = command.ExecuteReader();
aDataTable.Load(reader);
}
catch (Exception ex)
{
MessageDialog.Show
(
"Issue with SQL connection \r\n" + ex.InnerException,
"Error",
MessageDialog.MessageBoxButtons.Ok,
MessageDialog.MessageBoxIcon.Error,
"Please Check You Credentials Are Valid" + "\r\n" + ex.StackTrace);
}
}
}
catch (SqlException e)
{
if (!IsConnectionOpen())
{
//connection closed, open it again.
aConn.CreateConnection
(
aConn.AUserName,
aConn.APassword,
aConn.AServerAddress,
aConn.ADatabaseName,
aConn.AType);
if (aConn.Conn != null)
{
command = aConn.Conn.CreateCommand();
}
command.CommandText = asqlStmt;
command.CommandType = CommandType.Text;
SqlDataReader reader = command.ExecuteReader();
aDataTable.Load(reader);
MessageDialog.Show
(
"Issue with SQL connection \r\n" + e.InnerException,
"Error",
MessageDialog.MessageBoxButtons.Ok,
MessageDialog.MessageBoxIcon.Error,
"Please Check You Credentials Are Valid" + "\r\n" + e.StackTrace);
}
else
{
// else try closing it and going again.
aConn.CloseConnection();
aConn.CreateConnection
(
aConn.AUserName,
aConn.APassword,
aConn.AServerAddress,
aConn.ADatabaseName,
aConn.AType);
if (aConn.Conn != null)
{
command = aConn.Conn.CreateCommand();
}
command.CommandText = asqlStmt;
command.CommandType = CommandType.Text;
try
{
SqlDataReader reader = command.ExecuteReader();
aDataTable.Load(reader);
}
catch (Exception ex)
{
MessageDialog.Show
(
"Issue with SQL connection \r\n" + ex.InnerException,
"Error",
MessageDialog.MessageBoxButtons.Ok,
MessageDialog.MessageBoxIcon.Error,
"Please Check You Credentials Are Valid" + "\r\n" + ex.StackTrace);
}
}
}
}
}
return(aDataTable);
}
}