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();
}
public SqlErrorCapture(
Connection aConn,
string aSqlStatement)
{
_aConnection = aConn;
if (_aConnection == null)
{
return;
}
_aSqlStatement = aSqlStatement;
aConn.Conn.InfoMessage += (Conn_InfoMessage);
}
public List<string> GetSqlDataBases(Connection aConn)
{
_aTable = new DataTable();
_list1 = new List<string>();
try
{
_aTable = aConn.ExcuteReader
(
aConn,
"SELECT [name] as DATABASE_NAME FROM [master].[sys].[databases] order by name");
foreach (DataRow row in _aTable.Rows)
{
_list1.Add(row["DATABASE_NAME"].ToString());
}
return _list1;
}
catch (Exception ex)
{
Console.WriteLine(@"General Exception error with the GetSqlDataBases function, error is: " + ex);
}
return _list1;
}
public static void ExcuteNonQuery(
Connection aConn,
string asqlStmt)
{
if (aConn == null)
{
return;
}
if (aConn.Conn.State == ConnectionState.Closed)
{
aConn.Conn.Open();
}
SqlCommand command = aConn.Conn.CreateCommand();
if (asqlStmt != null)
{
command.CommandText = asqlStmt;
}
if (asqlStmt != null && asqlStmt.Contains("'S'"))
{
asqlStmt = asqlStmt.Replace
(
"'S'",
"''''S'");
}
command.CommandType = CommandType.Text;
IAsyncResult result = command.BeginExecuteNonQuery();
while (!result.IsCompleted)
{
Thread.Sleep(100);
}
if (!result.IsCompleted)
{
return;
}
command.EndExecuteNonQuery(result);
}
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 static object GetValue(
SqlConnection dc,
string procedureName,
DataTable aParameterDataTable,
Connection appSettings)
{
_connection = dc;
AppSettings = appSettings;
_mDbServer = AppSettings.AServerAddress;
_mDbDatabaseName = AppSettings.ADatabaseName;
_mDbUserId = AppSettings.AUserName;
_mDbUserPassword = AppSettings.APassword;
var sqlAdapter = new SqlDataAdapter();
string strSql = procedureName;
var holdVals = new DataTable();
object objValue;
try
{
if ((MTheTransaction == null))
{
OpenConnection();
}
sqlAdapter.SelectCommand = new SqlCommand
(
strSql,
_connection)
{
Transaction = MTheTransaction,
CommandTimeout = CCmdTimeOut,
CommandType = CommandType.StoredProcedure
};
foreach (DataRow dataRow in aParameterDataTable.Rows)
{
//Create the parameters
//sqlAdapter.SelectCommand.Parameters.Clear();
sqlAdapter.SelectCommand.Parameters.Add
(
new SqlParameter
(
dataRow[0].ToString(),
dataRow[1].GetType()));
sqlAdapter.SelectCommand.Parameters[dataRow[0].ToString()].Value = dataRow[1].ToString();
//Set the parameter direction as input (1) or output (0)
sqlAdapter.SelectCommand.Parameters[dataRow[0].ToString()].Direction = dataRow[3].ToString() == "1"
? ParameterDirection
.Input
: ParameterDirection
.Output;
}
sqlAdapter.Fill(holdVals);
objValue = sqlAdapter.SelectCommand.ExecuteScalar();
}
catch (Exception)
{
return false;
}
return objValue ?? (new decimal(0.00));
}
public static string BuildInsertQuery(
List<SqlParameter> sqlParamColl,
string seqTableName,
string tableName,
Connection appsettings,
object value = null)
{
_appsettings = appsettings;
_aTableName = tableName;
{
int i = 1;
string aSqlQuery = "INSERT INTO " + tableName + " ( ";
foreach (SqlParameter sqlParameter in sqlParamColl)
{
aSqlQuery = aSqlQuery + sqlParameter.ParameterName;
if (i < sqlParamColl.Count)
{
aSqlQuery = aSqlQuery + ", ";
i++;
}
else
{
aSqlQuery = aSqlQuery + " ) VALUES( ";
}
}
i = 1;
foreach (SqlParameter sqlParameter in sqlParamColl)
{
//Check if PRIMARY exists in Column name from the nth Character if true then Primary - otherwise might be a FK
const string stringPrimary = "PRIMARY";
string aCheckString = "";
if (sqlParameter.ParameterName.Contains("PRIMARY"))
{
aCheckString =
sqlParameter.ParameterName.Substring
(
sqlParameter.ParameterName.IndexOf
(
("_"),
StringComparison.Ordinal) +
1,
7);
}
else
{
aSqlQuery = aSqlQuery + "@" + sqlParameter.ParameterName;
if (i < sqlParamColl.Count)
{
aSqlQuery = aSqlQuery + ", ";
i++;
}
else
{
aSqlQuery = aSqlQuery + " ) ";
}
}
if (stringPrimary != aCheckString && sqlParameter.ParameterName.Contains("PRIMARY"))
{
sqlParameter.SqlValue = value;
aSqlQuery = aSqlQuery + "@" + sqlParameter.ParameterName;
if (i < sqlParamColl.Count)
{
aSqlQuery = aSqlQuery + ", ";
i++;
}
else
{
aSqlQuery = aSqlQuery + " ) ";
}
}
if (stringPrimary != aCheckString || !sqlParameter.ParameterName.Contains("PRIMARY"))
{
continue;
}
int aPrimaryValue = GetSequenceNumber
(
sqlParameter,
seqTableName,
_aTableName);
sqlParameter.SqlValue = aPrimaryValue;
aSqlQuery = aSqlQuery + "@" + sqlParameter.ParameterName;
if (i < sqlParamColl.Count)
{
aSqlQuery = aSqlQuery + ", ";
i++;
}
else
{
aSqlQuery = aSqlQuery + " ) ";
}
}
return aSqlQuery;
}
}
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;
}
