public DataSet ExecuteQuery(EDtxReaderType type, int timeout, string cmdText, Helpers.ParamHelper[] paramList, ref TimeSpan span)
{
DataSet retv = new DataSet();
try
{
//Technically you only need to register once, but as a showcase of what one would do, here is a quick example.
int retcode = Helpers.HelperFunctions.RegisterUser(keyFileTB.Text);
returnCodeTB.Text = retcode.ToString();
if(retcode == 0)
{
IDbConnection conn = QueryHelper.GetConnection(type, HelperFunctions.GetUniqueKey(10), HelperFunctions.GetUniqueKey(20), string.Empty, string.Empty);
if (conn != null)
{
retv = QueryHelper.ExecuteSelect(type, timeout, conn, cmdText, paramList, ref span);
}
else
{
MessageBox.Show("Unable to build connection object");
}
}
}
catch(Exception e)
{
MessageBox.Show("Execute Query Failed with an unknown error: " + e.Message + Environment.NewLine + e.StackTrace);
Console.WriteLine(e);
}
return retv;
}
private static DataSet ExecuteQuery(EDtxReaderType type, int timeout, string cmdText)
{
DataSet retv = new DataSet();
try
{
//Technically you only need to register once, but as a showcase of what one would do, here is a quick example.
int retcode = DtxSQLBrowser.Helpers.HelperFunctions.RegisterUser(cKeyFile);
DtxDataCrawler.retCode = retcode.ToString();
if (retcode == 0)
{
IDbConnection conn = QueryHelper.GetConnection(type, HelperFunctions.GetUniqueKey(10), HelperFunctions.GetUniqueKey(20), string.Empty, string.Empty);
if (conn != null)
{
retv = QueryHelper.ExecuteSelect(type, timeout, conn, cmdText, null, null);
}
else
{
Console.WriteLine("Unable to build connection object");
}
}
}
catch (Exception e)
{
Console.WriteLine("Execute Query Failed with an unknown error: " + e.Message + Environment.NewLine + e.StackTrace);
}
return(retv);
}
public DataSet ExecuteQuery(EDtxReaderType type, int timeout, string cmdText, ParamHelper[] paramList, ref TimeSpan span)
{
DataSet retv = new DataSet();
if (string.IsNullOrWhiteSpace(usernameTB.Text))
{
MessageBox.Show("Username is required");
return(retv);
}
if (string.IsNullOrWhiteSpace(passwordTB.Text))
{
MessageBox.Show("Password is required");
return(retv);
}
if (EncryptionRequired)
{
if (string.IsNullOrWhiteSpace(encryptTB.Text))
{
MessageBox.Show("EncryptionKey is required");
return(retv);
}
if (string.IsNullOrWhiteSpace(decryptTB.Text))
{
MessageBox.Show("DecryptionKey is required");
return(retv);
}
}
IDbConnection conn = QueryHelper.GetConnection(type, usernameTB.Text, passwordTB.Text, encryptTB.Text, decryptTB.Text);
if (conn != null)
{
retv = QueryHelper.ExecuteSelect(type, timeout, conn, cmdText, paramList, ref span);
}
return(retv);
}
public DataSet ExecuteQuery(EDtxReaderType type, int timeout, string cmdText, Helpers.ParamHelper[] paramList, ref TimeSpan span)
{
DataSet retv = new DataSet();
try
{
//Technically you only need to register once, but as a showcase of what one would do, here is a quick example.
int retcode = Helpers.HelperFunctions.RegisterUser(keyFileTB.Text);
returnCodeTB.Text = retcode.ToString();
if (retcode == 0)
{
IDbConnection conn = QueryHelper.GetConnection(type, HelperFunctions.GetUniqueKey(10), HelperFunctions.GetUniqueKey(20), string.Empty, string.Empty);
if (conn != null)
{
retv = QueryHelper.ExecuteSelect(type, timeout, conn, cmdText, paramList, ref span);
}
else
{
MessageBox.Show("Unable to build connection object");
}
}
}
catch (Exception e)
{
MessageBox.Show("Execute Query Failed with an unknown error: " + e.Message + Environment.NewLine + e.StackTrace);
Console.WriteLine(e);
}
return(retv);
}
private static DataSet ExecuteQuery(EDtxReaderType type, int timeout, string cmdText)
{
DataSet retv = new DataSet();
try
{
//Technically you only need to register once, but as a showcase of what one would do, here is a quick example.
int retcode = DtxSQLBrowser.Helpers.HelperFunctions.RegisterUser(cKeyFile);
DtxDataCrawler.retCode = retcode.ToString();
if (retcode == 0)
{
IDbConnection conn = QueryHelper.GetConnection(type, HelperFunctions.GetUniqueKey(10), HelperFunctions.GetUniqueKey(20), string.Empty, string.Empty);
if (conn != null)
{
retv = QueryHelper.ExecuteSelect(type, timeout, conn, cmdText, null, null);
}
else
{
Console.WriteLine("Unable to build connection object");
}
}
}
catch (Exception e)
{
Console.WriteLine("Execute Query Failed with an unknown error: " + e.Message + Environment.NewLine + e.StackTrace);
}
return retv;
}
public DataSet ExecuteQuery(EDtxReaderType type, int timeout, string cmdText, ParamHelper[] paramList, ref TimeSpan span)
{
DataSet retv = new DataSet();
if(string.IsNullOrWhiteSpace(usernameTB.Text))
{
MessageBox.Show("Username is required");
return retv;
}
if (string.IsNullOrWhiteSpace(passwordTB.Text))
{
MessageBox.Show("Password is required");
return retv;
}
if (EncryptionRequired)
{
if (string.IsNullOrWhiteSpace(encryptTB.Text))
{
MessageBox.Show("EncryptionKey is required");
return retv;
}
if (string.IsNullOrWhiteSpace(decryptTB.Text))
{
MessageBox.Show("DecryptionKey is required");
return retv;
}
}
IDbConnection conn = QueryHelper.GetConnection(type, usernameTB.Text, passwordTB.Text, encryptTB.Text, decryptTB.Text);
if (conn != null)
{
retv = QueryHelper.ExecuteSelect(type, timeout, conn, cmdText, paramList, ref span);
}
return retv;
}
public DataSet ExecuteQuery(EDtxReaderType type, int timeout, string cmdText, ParamHelper[] paramList, ref TimeSpan span)
{
DataSet retv = new DataSet();
IDbConnection conn = QueryHelper.GetConnection(type, usernameTB.Text, passwordTB.Text, serverNameTB.Text, (EDatabase)dbCombo.SelectedIndex);
if (conn != null)
{
retv = QueryHelper.ExecuteSelect(type, timeout, conn, cmdText, paramList, ref span);
}
return retv;
}
public DataSet ExecuteQuery(EDtxReaderType type, int timeout, string cmdText, ParamHelper[] paramList, ref TimeSpan span)
{
DataSet retv = new DataSet();
IDbConnection conn = QueryHelper.GetConnection(type, usernameTB.Text, passwordTB.Text, serverNameTB.Text, (EDatabase)dbCombo.SelectedIndex);
if (conn != null)
{
retv = QueryHelper.ExecuteSelect(type, timeout, conn, cmdText, paramList, ref span);
}
return(retv);
}
internal static IDbConnection GetConnection(EDtxReaderType connType, string un, string pw, string enc, string dec)
{
IDbConnection conn = null;
string connStr = GetConnectionString(connType, un, pw, enc, dec);
if (string.IsNullOrWhiteSpace(connStr) == false)
{
conn = GetConnectionObject(connType);
conn.ConnectionString = connStr;
}
return(conn);
}
internal static string GetConnectionString(EDtxReaderType connType, string un, string pw, string server, EDatabase database)
{
string retv = string.Format(ConnStrBaseFormatter, server, un, pw, database.ToString());
switch (connType)
{
case EDtxReaderType.Adapter:
case EDtxReaderType.Reader:
case EDtxReaderType.ReaderSafe:
retv += OdbcDSN;
break;
default:
break;
}
return(retv);
}
internal static string GetConnectionString(EDtxReaderType connType, string un = QueryHelper.USERNAME, string pw = QueryHelper.PASSWORD, string enc = "", string dec = "")
{
string retv = string.Empty;
try
{
Debug.Assert(string.IsNullOrWhiteSpace(un), "UserName string must be supplied");
Debug.Assert(string.IsNullOrWhiteSpace(pw), "Password string must be supplied");
StringBuilder connectionString = new StringBuilder(512);
string tempStr = pw;
if (HelperFunctions.EncryptionRequired)
{
Debug.Assert(string.IsNullOrWhiteSpace(enc), "Encryption string must be supplied");
Debug.Assert(string.IsNullOrWhiteSpace(dec), "Decryption string must be supplied");
tempStr += DateTime.Now.ToString("yyyyMMdd");
tempStr = Crypto.EncryptStringAES(tempStr, enc);
}
lock (connectionString)
{
switch (connType)
{
case EDtxReaderType.Adapter:
case EDtxReaderType.Reader:
case EDtxReaderType.ReaderSafe:
HelperFunctions.DENTRIXAPI_GetConnectionString(un, tempStr, connectionString, 512);
break;
case EDtxReaderType.FaircomAdapter:
case EDtxReaderType.FaircomReader:
case EDtxReaderType.FaircomReaderSafe:
HelperFunctions.DENTRIXAPI_GetFCConnectionString(un, tempStr, connectionString, 512);
break;
}
}
retv = connectionString.ToString().Trim();
if (HelperFunctions.EncryptionRequired && retv.Length > 0)
{
retv = Crypto.DecryptStringAES(retv, dec);
}
}
catch (Exception e)
{
HelperFunctions.LogException(e);
}
return(retv);
}
private static IDbDataAdapter GetAdapter(EDtxReaderType connType, IDbCommand cmd)
{
IDbDataAdapter adapter = null;
switch (connType)
{
case EDtxReaderType.FaircomAdapter:
adapter = new CtreeSqlDataAdapter(cmd as CtreeSqlCommand);
break;
default:
case EDtxReaderType.Adapter:
adapter = new OdbcDataAdapter(cmd as OdbcCommand);
break;
}
return(adapter);
}
internal static IDbConnection GetConnectionObject(EDtxReaderType connType)
{
IDbConnection conn;
switch (connType)
{
case EDtxReaderType.FaircomAdapter:
case EDtxReaderType.FaircomReader:
case EDtxReaderType.FaircomReaderSafe:
conn = new CtreeSqlConnection();
break;
default:
case EDtxReaderType.Adapter:
case EDtxReaderType.Reader:
case EDtxReaderType.ReaderSafe:
conn = new OdbcConnection();
break;
}
return(conn);
}
internal static string GetConnectionString(EDtxReaderType connType, string un, string pw, string enc, string dec)
{
string retv = string.Empty;
try
{
Debug.Assert(string.IsNullOrWhiteSpace(un), "UserName string must be supplied");
Debug.Assert(string.IsNullOrWhiteSpace(pw), "Password string must be supplied");
StringBuilder connectionString = new StringBuilder(512);
string tempStr = pw;
if (HelperFunctions.EncryptionRequired)
{
Debug.Assert(string.IsNullOrWhiteSpace(enc), "Encryption string must be supplied");
Debug.Assert(string.IsNullOrWhiteSpace(dec), "Decryption string must be supplied");
tempStr += DateTime.Now.ToString("yyyyMMdd");
tempStr = Crypto.EncryptStringAES(tempStr, enc);
}
lock (connectionString)
{
switch (connType)
{
case EDtxReaderType.Adapter:
case EDtxReaderType.Reader:
case EDtxReaderType.ReaderSafe:
HelperFunctions.DENTRIXAPI_GetConnectionString(un, tempStr, connectionString, 512);
break;
case EDtxReaderType.FaircomAdapter:
case EDtxReaderType.FaircomReader:
case EDtxReaderType.FaircomReaderSafe:
HelperFunctions.DENTRIXAPI_GetFCConnectionString(un, tempStr, connectionString, 512);
break;
}
}
retv = connectionString.ToString().Trim();
if (HelperFunctions.EncryptionRequired && retv.Length > 0)
{
retv = Crypto.DecryptStringAES(retv, dec);
}
}
catch (Exception e)
{
HelperFunctions.LogException(e);
}
return retv;
}
internal static string GetConnectionString(EDtxReaderType connType, string un, string pw, string server, EDatabase database)
{
string retv = string.Format(ConnStrBaseFormatter, server, un, pw, database.ToString());
switch(connType)
{
case EDtxReaderType.Adapter:
case EDtxReaderType.Reader:
case EDtxReaderType.ReaderSafe:
retv += OdbcDSN;
break;
default:
break;
}
return retv;
}
internal static IDbConnection GetConnectionObject(EDtxReaderType connType)
{
IDbConnection conn;
switch (connType)
{
case EDtxReaderType.FaircomAdapter:
case EDtxReaderType.FaircomReader:
case EDtxReaderType.FaircomReaderSafe:
conn = new CtreeSqlConnection();
break;
default:
case EDtxReaderType.Adapter:
case EDtxReaderType.Reader:
case EDtxReaderType.ReaderSafe:
conn = new OdbcConnection();
break;
}
return conn;
}
internal static IDbConnection GetConnection(EDtxReaderType connType, string un, string pw, string enc, string dec)
{
IDbConnection conn = null;
string connStr = GetConnectionString(connType, un, pw, enc, dec);
if (string.IsNullOrWhiteSpace(connStr) == false)
{
conn = GetConnectionObject(connType);
conn.ConnectionString = connStr;
}
return conn;
}
internal static IDbDataAdapter GetAdapter(EDtxReaderType connType, IDbCommand cmd)
{
IDbDataAdapter adapter = null;
switch(connType)
{
case EDtxReaderType.FaircomAdapter:
adapter = new CtreeSqlDataAdapter(cmd as CtreeSqlCommand);
break;
default:
case EDtxReaderType.Adapter:
adapter = new OdbcDataAdapter(cmd as OdbcCommand);
break;
}
return adapter;
}
internal static DataSet ExecuteSelect(EDtxReaderType connType, int timeout, IDbConnection conn, string cmdText, ParamHelper[] paramList, ref TimeSpan executionTime)
{
DateTime started = DateTime.Now;
IDataReader reader = null;
IDbCommand cmd = null;
bool bConnOpenedHere = EnsureConnectionOpen(conn);
IDbTransaction transaction = conn.BeginTransaction();
DataSet retv = new DataSet();
try
{
cmd = conn.CreateCommand();
cmd.CommandTimeout = timeout;
cmd.Transaction = transaction;
cmd.CommandText = cmdText;
DbType[] reqLenTypes = new DbType[] { DbType.AnsiString, DbType.AnsiStringFixedLength, DbType.String, DbType.StringFixedLength, DbType.Binary, DbType.Object, DbType.Xml };
for (int i = 0; i < paramList.Length; i++)
{
IDbDataParameter idb = cmd.CreateParameter();
cmd.Parameters.Add(HelperFunctions.BuildParameter(idb, paramList[i]));
if(reqLenTypes.Contains(idb.DbType))
{
if (idb is OdbcParameter)
{
(idb as OdbcParameter).Size = paramList[i].Size;
}
else if (idb is CtreeSqlParameter)
{
(idb as CtreeSqlParameter).Size = paramList[i].Size;
}
}
}
switch(connType)
{
case EDtxReaderType.Adapter:
case EDtxReaderType.FaircomAdapter:
{
IDbDataAdapter adap = GetAdapter(connType, cmd);
retv.BeginInit();
//Since the FillSchema and Fill functions return the same instance of 'DataTable'
//There is probably a better way of doing this, but for sake of explination
//Read the db schema
bool bSchemaFound = false;
DataTable[] dta = adap.FillSchema(retv, SchemaType.Source);
DataTable clone = null;
if(dta.Length > 0)
{
bSchemaFound = true;
dta[0].TableName = "SchemaTable"; //Ensure the table is named 'SchemaTable'
retv.Tables.Remove(dta[0]); //Drop the table from the dataset
clone = dta[0].Clone(); //Clone the results
dta[0].TableName = "Table"; //Rename the datatable instance back to table
}
adap.Fill(retv); //Fill 'Table' with the actual results
if(bSchemaFound && clone != null)
retv.Tables.Add(clone); //Now add the 'schematable' back to the results
retv.EndInit();
break;
}
default:
{
DataTable dt;
reader = cmd.ExecuteReader();
if (reader.FieldCount > 0)
{
retv.Tables.Add(dt = new DataTable("Table"));
retv.Tables.Add(reader.GetSchemaTable());
switch (connType)
{
case EDtxReaderType.FaircomReader:
case EDtxReaderType.Reader:
{
dt.Load(reader, LoadOption.OverwriteChanges);
}
break;
case EDtxReaderType.FaircomReaderSafe:
case EDtxReaderType.ReaderSafe:
default:
{
bool columnsBuilt = false;
while (reader.Read())
{
if (columnsBuilt == false)
{
BuildColumnData(dt, reader);
columnsBuilt = true;
}
AddDataRow(dt, reader);
}
}
break;
}
}
break;
}
}
transaction.Commit();
executionTime = DateTime.Now - started;
//Now update parameter inputs with their respective values
for (int i = 0; i < paramList.Length; i++)
{
IDbDataParameter p = (IDbDataParameter)cmd.Parameters[i];
if(p.Direction != ParameterDirection.Input)
{
paramList[i].OutValue = p.Value;
}
}
}
catch(Exception e)
{
transaction.Rollback();
executionTime = DateTime.Now - started;
HelperFunctions.LogException(e, false);
throw e;
}
finally
{
if(reader != null)
{
if(!reader.IsClosed)
reader.Close();
reader.Dispose();
}
if(cmd != null)
{
cmd.Dispose();
}
if(bConnOpenedHere)
{
conn.Close();
}
}
return retv;
}
internal static DataSet ExecuteSelect(EDtxReaderType connType, int timeout, IDbConnection conn, string cmdText, ParamHelper[] paramList, ref TimeSpan executionTime)
{
DateTime started = DateTime.Now;
IDataReader reader = null;
IDbCommand cmd = null;
bool bConnOpenedHere = EnsureConnectionOpen(conn);
IDbTransaction transaction = conn.BeginTransaction();
DataSet retv = new DataSet();
try
{
cmd = conn.CreateCommand();
cmd.CommandTimeout = timeout;
cmd.Transaction = transaction;
cmd.CommandText = cmdText;
DbType[] reqLenTypes = new DbType[] { DbType.AnsiString, DbType.AnsiStringFixedLength, DbType.String, DbType.StringFixedLength, DbType.Binary, DbType.Object, DbType.Xml };
for (int i = 0; i < paramList.Length; i++)
{
IDbDataParameter idb = cmd.CreateParameter();
cmd.Parameters.Add(BuildParameter(idb, paramList[i]));
if (reqLenTypes.Contains(idb.DbType))
{
if (idb is OdbcParameter)
{
(idb as OdbcParameter).Size = paramList[i].Size;
}
else if (idb is CtreeSqlParameter)
{
(idb as CtreeSqlParameter).Size = paramList[i].Size;
}
}
}
switch (connType)
{
case EDtxReaderType.Adapter:
case EDtxReaderType.FaircomAdapter:
{
IDbDataAdapter adap = GetAdapter(connType, cmd);
retv.BeginInit();
//Since the FillSchema and Fill functions return the same instance of 'DataTable'
//There is probably a better way of doing this, but for sake of explination
//Read the db schema
bool bSchemaFound = false;
DataTable[] dta = adap.FillSchema(retv, SchemaType.Source);
DataTable clone = null;
if (dta.Length > 0)
{
bSchemaFound = true;
dta[0].TableName = "SchemaTable"; //Ensure the table is named 'SchemaTable'
retv.Tables.Remove(dta[0]); //Drop the table from the dataset
clone = dta[0].Clone(); //Clone the results
dta[0].TableName = "Table"; //Rename the datatable instance back to table
}
adap.Fill(retv); //Fill 'Table' with the actual results
if (bSchemaFound && clone != null)
{
retv.Tables.Add(clone); //Now add the 'schematable' back to the results
}
retv.EndInit();
break;
}
default:
{
DataTable dt;
reader = cmd.ExecuteReader();
if (reader.FieldCount > 0)
{
retv.Tables.Add(dt = new DataTable("Table"));
retv.Tables.Add(reader.GetSchemaTable());
switch (connType)
{
case EDtxReaderType.FaircomReader:
case EDtxReaderType.Reader:
{
dt.Load(reader, LoadOption.OverwriteChanges);
}
break;
case EDtxReaderType.FaircomReaderSafe:
case EDtxReaderType.ReaderSafe:
default:
{
bool columnsBuilt = false;
while (reader.Read())
{
if (columnsBuilt == false)
{
BuildColumnData(dt, reader);
columnsBuilt = true;
}
AddDataRow(dt, reader);
}
}
break;
}
}
break;
}
}
transaction.Commit();
executionTime = DateTime.Now - started;
//Now update parameter inputs with their respective values
for (int i = 0; i < paramList.Length; i++)
{
IDbDataParameter p = (IDbDataParameter)cmd.Parameters[i];
if (p.Direction != ParameterDirection.Input)
{
paramList[i].OutValue = p.Value;
}
}
}
catch (Exception e)
{
transaction.Rollback();
executionTime = DateTime.Now - started;
HelperFunctions.LogException(e, false);
throw e;
}
finally
{
if (reader != null)
{
if (!reader.IsClosed)
{
reader.Close();
}
reader.Dispose();
}
if (cmd != null)
{
cmd.Dispose();
}
if (bConnOpenedHere)
{
conn.Close();
}
}
return(retv);
}
