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,
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);
}
}
private void btnLogin_Click(
object sender,
EventArgs e)
{
Cursor = Cursors.WaitCursor;
switch (chkWinAuth.CheckState)
{
case CheckState.Checked:
AConnection = new Connection();
AConnection.CreateConnection
(
txtUsername.Text,
txtPassword.Text,
cmbServer.Text,
cmbDataBase.Text,
"WIN");
break;
default:
AConnection = new Connection();
AConnection.CreateConnection
(
txtUsername.Text,
txtPassword.Text,
cmbServer.Text,
cmbDataBase.Text,
"SQL");
break;
}
ADatabases = new GetDatabases();
cmbDataBase.DataSource = ADatabases.GetSqlDataBases(AConnection);
AConnectionIsOpen = AConnection.IsConnectionOpen();
Cursor = Cursors.Default;
aformtoConnect.Show();
this.Hide();
}
private void cmbDataBase_BeforeDropDown(
object sender,
CancelEventArgs e)
{
Cursor = Cursors.WaitCursor;
switch (chkWinAuth.CheckState)
{
case CheckState.Checked:
AConnection = new Connection();
AConnection.CreateConnection
(
txtUsername.Text,
txtPassword.Text,
cmbServer.Text,
"",
"WIN");
break;
default:
AConnection = new Connection();
AConnection.CreateConnection
(
txtUsername.Text,
txtPassword.Text,
cmbServer.Text,
"",
"SQL");
break;
}
ADatabases = new GetDatabases();
cmbDataBase.DataSource = ADatabases.GetSqlDataBases(AConnection);
Cursor = Cursors.Default;
}