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); }