//***************************************************************************
// Class Constructors
//
public DataRead()
{
_connStr = "";
_cmndStr = "";
_prvType = AdoProviderType.Auto;
_qryRslt = null;
}
//***************************************************************************
// Class Constructors
//
public DataRead()
{
_connStr = "";
_cmndStr = "";
_prvType = AdoProviderType.Auto;
_qryRslt = null;
}
//***************************************************************************
// Private Methods
//
private DataSet ExecuteCommand(string ConnectionString, string Command, AdoProviderType Provider)
{
this.ReadStartedEvent();
// Declare DB object.
rsDb dbRead = null;
try
{
// Initialize using the proper DB provider class.
dbRead = rsDb.GetDbObject(Provider, ConnectionString, Command);
dbRead.DataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
return(dbRead.GetData());
}
catch (Exception ex)
{
throw new Exception("Unable to read data from database.", ex);
}
finally
{
if (dbRead != null)
{
dbRead.Dispose();
}
}
}
//***************************************************************************
// Private Properties
//
//internal rsDb BaseConnection
//{ get { return this._db; } }
#endregion
#region Class Constructors
//***************************************************************************
// Class Constructors
//
public DataQuery(AdoProviderType provider, string connString, string qryString)
{
//this._db = rsDb.GetDbObject(provider, connString, qryString);
this._dbType = provider;
this._cnStr = connString;
this._qrStr = qryString;
}
public static DbConnection GetOpenConnection(AdoProviderType providerType, string connStr, int retryCount, int retryDelay)
{
DbConnection conn = null;
switch (providerType)
{
case AdoProviderType.SqlProvider:
conn = new SqlConnection(connStr);
break;
case AdoProviderType.OleProvider:
conn = new OleDbConnection(connStr);
break;
case AdoProviderType.OdbcProvider:
conn = new OdbcConnection(connStr);
break;
default:
throw new Exception("This provider type is not supported.");
}
if (conn != null)
{
conn.Open();
int attemptCount = 0;
while (conn.State!=ConnectionState.Open && ++attemptCount < retryCount)
{
System.Threading.Thread.Sleep(retryDelay);
conn.Open();
}
if (conn.State != ConnectionState.Open)
throw new Exception("Unable to open connection using specified connection string.");
}
return conn;
}
public frmStringBuilder(AdoProviderType LockProviderId)
: this()
{
lstProvider.Enabled = false;
lstProvider.SelectedIndex = (int)LockProviderId;
Application.DoEvents();
txtIP.Focus();
}
public frmStringBuilder(AdoProviderType LockProviderId)
: this()
{
lstProvider.Enabled = false;
lstProvider.SelectedIndex = (int)LockProviderId;
Application.DoEvents();
txtIP.Focus();
}
public DbConnectionProperties(AdoProviderType providerid)
{
if (providerid == AdoProviderType.Auto)
{
throw new ArgumentException("Enumeration value \"Auto\" is not valid for DbConnectionProperties object.", "providerid");
}
this._vals = new StringCollection();
this._dbType = providerid;
}
//public void BeginTransaction(IsolationLevel isoLvl)
//{
// this._db.DbConnection.BeginTransaction(isoLvl);
//}
//public void RollbackTransaction()
//{
// this._db.DbCommand.Transaction.Rollback();
//}
//public void CommitTransaction()
//{
// this._db.DbCommand.Transaction.Commit();
//}
#endregion
#region Private Methods
//***************************************************************************
// Private Methods
//
//private void DoQueryExec(rsDb dataObj)
private void DoQueryExec(AdoProviderType dbType, string cnStr, string qrStr, int timeOut)
{
this._abort = false;
this._isExec = true;
this.ExecuteStartedEvent();
this._abortEx = null;
// Create an rsDb object to handle our database interface.
using (rsDb dataObj = rsDb.GetDbObject(dbType, cnStr, qrStr))
{
// We set the connection's timeout here. By default, this value is
// set to 'Zero' (meaning no timeout).
dataObj.DbCommand.CommandTimeout = timeOut;
// We use the 'keepReading' value to determine if the statement
// returns multiple result sets.
bool keepReading = true;
try
{
// Performing the call to 'Prepare' ensures there are no syntax errors
// before actually executing a batch of statements. This also helps
// to speed execution slightly.
dataObj.DbCommand.Prepare();
dataObj.InitReader();
// Loop through each result set.
while (keepReading && !this._abort)
{
int idx = 0;
DataTable dtNew = dataObj.DataReader.GetSchemaTable().Clone();
this.ResultStartEvent(dtNew);
while (dataObj.DataReader.Read())
{
DataRow drNew = dtNew.NewRow();
dataObj.DataReader.GetValues(drNew.ItemArray);
this.RecordProcessedEvent(drNew.ItemArray, idx);
}
this.ResultCompleteEvent();
keepReading = dataObj.DataReader.NextResult();
}
}
catch (Exception ex)
{
this._abortEx = ex;
this.ExecuteAbortEvent();
return;
}
finally
{
dataObj.CloseReader();
this._isExec = false;
}
}
}
//***************************************************************************
// Private Methods
//
//private DataSet DoQueryExec(rsDb dataObj)
private DataSet DoQueryExec(string connStr, string qryString, AdoProviderType provId)
{
rsDb dataObj = null;
this.RaiseExecuteStarted();
this._abortEx = null;
this._dtResult = null;
this._recAffected = 0;
DataSet retVal = null;
try
{
if (this._db == null || this._db.ConnectionString != connStr || this._db.QueryString != qryString)
{
if (this._db != null)
{
this._db.Dispose();
}
dataObj = rsDb.GetDbObject(provId, connStr, qryString);
dataObj.DbCommand.CommandTimeout = this._timeOut;
this._db = dataObj;
}
retVal = new DataSet();
this._recAffected = dataObj.GetData(ref retVal);
return(retVal);
}
catch (Exception ex)
{
this._abortEx = ex;
this.RaiseExecuteAbortEvent();
return(null);
}
//finally
//{
// if (dataObj != null)
// dataObj.Dispose();
//}
}
//***************************************************************************
// Private Methods
//
private void InitValues(AdoProviderType val)
{
switch (val)
{
case AdoProviderType.SqlProvider:
this._vals.Add("", "DataSource");
this._vals.Add("", "Initial Catalog");
break;
case AdoProviderType.OleProvider:
this._vals.Add("", "Provider");
this._vals.Add("", "DataSource");
break;
case AdoProviderType.OdbcProvider:
this._vals.Add("", "Driver");
break;
case AdoProviderType.DB2Provider:
break;
}
}
public static DbConnection GetOpenConnection(AdoProviderType providerType, string connStr, int retryCount, int retryDelay)
{
DbConnection conn = null;
switch (providerType)
{
case AdoProviderType.SqlProvider:
conn = new SqlConnection(connStr);
break;
case AdoProviderType.OleProvider:
conn = new OleDbConnection(connStr);
break;
case AdoProviderType.OdbcProvider:
conn = new OdbcConnection(connStr);
break;
default:
throw new Exception("This provider type is not supported.");
}
if (conn != null)
{
conn.Open();
int attemptCount = 0;
while (conn.State != ConnectionState.Open && ++attemptCount < retryCount)
{
System.Threading.Thread.Sleep(retryDelay);
conn.Open();
}
if (conn.State != ConnectionState.Open)
{
throw new Exception("Unable to open connection using specified connection string.");
}
}
return(conn);
}
//***************************************************************************
// Private Methods
//
private void InitValues(AdoProviderType val)
{
switch (val)
{
case AdoProviderType.SqlProvider:
this._vals.Add("", "DataSource");
this._vals.Add("", "Initial Catalog");
break;
case AdoProviderType.OleProvider:
this._vals.Add("", "Provider");
this._vals.Add("", "DataSource");
break;
case AdoProviderType.OdbcProvider:
this._vals.Add("", "Driver");
break;
case AdoProviderType.DB2Provider:
break;
}
}
//***************************************************************************
// Static Methods
//
public static DbConnectionProperties FromString(AdoProviderType provid, string value)
{
if (provid == AdoProviderType.Auto)
throw new ArgumentException("Enumeration value \"Auto\" is not valid for this function.", "provid");
DbConnectionProperties retVal = new DbConnectionProperties();
retVal._dbType = provid;
DbConnectionStringBuilder connBldr = null;
switch (provid)
{
case AdoProviderType.SqlProvider:
connBldr = new SqlConnectionStringBuilder(value);
break;
case AdoProviderType.OleProvider:
connBldr = new OleDbConnectionStringBuilder(value);
break;
case AdoProviderType.OdbcProvider:
connBldr = new OdbcConnectionStringBuilder(value);
break;
case AdoProviderType.DB2Provider:
connBldr = new Db2ConnectionStringBuilder(value);
break;
}
string[] keys = new string[connBldr.Keys.Count];
connBldr.Keys.CopyTo(keys, 0);
string[] vals = new string[connBldr.Values.Count];
connBldr.Values.CopyTo(vals, 0);
for (int i = 0; i < keys.Length; i++)
retVal._vals.Add(vals[i], keys[i]);
return retVal;
#region Depreciated Code
//AdoProviderType provid;
//string ds = "", un = "", pw = "", ic = "", exAttr = "";
//// Parse the provider type.
//if (value.ToUpper().Contains("PROVIDER="))
// provid = AdoProviderType.OleProvider;
//else if (value.ToUpper().Contains("DRIVER="))
// provid = AdoProviderType.OdbcProvider;
//else
// provid = AdoProviderType.SqlProvider;
//// Parse the datasource.
//int dsIdx = value.ToUpper().IndexOf("DATA SOURCE=");
//if (dsIdx > -1)
//{
// int dsLen = value.IndexOf(';', dsIdx) - dsIdx;
// string dsStr = value.Substring(dsIdx, dsLen);
// ds = dsStr.Substring(dsStr.IndexOf('=') + 1);
//}
//// Parse the username.
//int unIdx = value.ToUpper().IndexOf("USER ID=");
//if (unIdx > -1)
//{
// int unLen = value.IndexOf(';', unIdx) - unIdx;
// string unStr = value.Substring(unIdx, unLen);
// un = unStr.Substring(unStr.IndexOf('=') + 1);
//}
//// Parse the password.
//int pwIdx = value.ToUpper().IndexOf("PASSWORD="******"INITIAL CATALOG=");
//if (icIdx > -1)
//{
// int icLen = value.IndexOf(';', icIdx) - icIdx;
// string icStr = value.Substring(icIdx, icLen);
// ic = icStr.Substring(icStr.IndexOf('=') + 1);
//}
//// Create a new object based on the captured values.
//DbConnectionProperties dbProp = new DbConnectionProperties(provid, ds, un, pw, ic);
//// Check for any extended properties
//int epIdx = value.ToUpper().IndexOf("EXTENDED PROPERTIES=");
//if (epIdx > -1)
//{
// int epLen = value.IndexOf(';', epIdx) - epIdx;
// string epStr = value.Substring(epIdx, epLen);
// dbProp.ExtendedProperties = epStr;
//}
//// Return the new struct.
//return dbProp;
#endregion
}
public static void UploadObject(AdoProviderType id, string connStr, string tableName, object typeObj)
{
using (rsDb db = rsDb.GetDbObject(id, connStr, "SELECT * FROM " + tableName))
{
// Retrieve the existing data and the Type of the passed
// object to be uploaded.
DataSet dt = db.GetData();
Type objType = typeObj.GetType();
// Get the properties defined by the passed object.
System.Reflection.PropertyInfo[] pi = objType.GetProperties(System.Reflection.BindingFlags.DeclaredOnly | System.Reflection.BindingFlags.Instance);
// Try and grab the DataTable
}
}
public static DbConnection GetOpenConnection(AdoProviderType providerType, string connStr)
{ return rsData.GetOpenConnection(providerType, connStr, 5, 1200); }
public static DbCommand GetInsertCommand(DataRow schemaRow, AdoProviderType providerID)
{
return rsData.GetInsertCommand(schemaRow, schemaRow.Table.TableName, providerID);
}
//***************************************************************************
// Static Methods
//
public static DbConnectionProperties FromString(AdoProviderType provid, string value)
{
if (provid == AdoProviderType.Auto)
{
throw new ArgumentException("Enumeration value \"Auto\" is not valid for this function.", "provid");
}
DbConnectionProperties retVal = new DbConnectionProperties();
retVal._dbType = provid;
DbConnectionStringBuilder connBldr = null;
switch (provid)
{
case AdoProviderType.SqlProvider:
connBldr = new SqlConnectionStringBuilder(value);
break;
case AdoProviderType.OleProvider:
connBldr = new OleDbConnectionStringBuilder(value);
break;
case AdoProviderType.OdbcProvider:
connBldr = new OdbcConnectionStringBuilder(value);
break;
case AdoProviderType.DB2Provider:
connBldr = new Db2ConnectionStringBuilder(value);
break;
}
string[] keys = new string[connBldr.Keys.Count];
connBldr.Keys.CopyTo(keys, 0);
string[] vals = new string[connBldr.Values.Count];
connBldr.Values.CopyTo(vals, 0);
for (int i = 0; i < keys.Length; i++)
{
retVal._vals.Add(vals[i], keys[i]);
}
return(retVal);
#region Depreciated Code
//AdoProviderType provid;
//string ds = "", un = "", pw = "", ic = "", exAttr = "";
//// Parse the provider type.
//if (value.ToUpper().Contains("PROVIDER="))
// provid = AdoProviderType.OleProvider;
//else if (value.ToUpper().Contains("DRIVER="))
// provid = AdoProviderType.OdbcProvider;
//else
// provid = AdoProviderType.SqlProvider;
//// Parse the datasource.
//int dsIdx = value.ToUpper().IndexOf("DATA SOURCE=");
//if (dsIdx > -1)
//{
// int dsLen = value.IndexOf(';', dsIdx) - dsIdx;
// string dsStr = value.Substring(dsIdx, dsLen);
// ds = dsStr.Substring(dsStr.IndexOf('=') + 1);
//}
//// Parse the username.
//int unIdx = value.ToUpper().IndexOf("USER ID=");
//if (unIdx > -1)
//{
// int unLen = value.IndexOf(';', unIdx) - unIdx;
// string unStr = value.Substring(unIdx, unLen);
// un = unStr.Substring(unStr.IndexOf('=') + 1);
//}
//// Parse the password.
//int pwIdx = value.ToUpper().IndexOf("PASSWORD="******"INITIAL CATALOG=");
//if (icIdx > -1)
//{
// int icLen = value.IndexOf(';', icIdx) - icIdx;
// string icStr = value.Substring(icIdx, icLen);
// ic = icStr.Substring(icStr.IndexOf('=') + 1);
//}
//// Create a new object based on the captured values.
//DbConnectionProperties dbProp = new DbConnectionProperties(provid, ds, un, pw, ic);
//// Check for any extended properties
//int epIdx = value.ToUpper().IndexOf("EXTENDED PROPERTIES=");
//if (epIdx > -1)
//{
// int epLen = value.IndexOf(';', epIdx) - epIdx;
// string epStr = value.Substring(epIdx, epLen);
// dbProp.ExtendedProperties = epStr;
//}
//// Return the new struct.
//return dbProp;
#endregion
}
//***************************************************************************
// Class Constructors
//
public DbConnectionProperties(AdoProviderType providerid, string connString)
: this(providerid)
{
this = DbConnectionProperties.FromString(providerid, connString);
}
/// <summary>
/// Checks the given connection string for validity by attempting to open a connection with it.
/// </summary>
/// <param name="ConnectionString">The connection string to test.</param>
/// <param name="ProviderArg">An integer (as enumerated in AosCommon.ProviderType) indicating the ADO.NET adapter to use.</param>
/// <returns>A true or false value indicating whether or not the test was successful.</returns>
public static bool CheckConnectionString(string ConnectionString, AdoProviderType ProviderArg)
{
#region Status Output
#if DEBUG
Console.WriteLine("Checking connection string validity...");
#endif
#endregion
try
{
string ServerVersion = "", ConnState = "";
switch (ProviderArg)
{
case AdoProviderType.Auto:
return(rsData.CheckConnectionString(ConnectionString, rsData.ParseProviderType(ConnectionString)));
case AdoProviderType.SqlProvider:
using (SqlConnection conTest = new SqlConnection(ConnectionString))
{
conTest.Open();
ServerVersion = conTest.ServerVersion;
ConnState = conTest.State.ToString();
conTest.Close();
}
break;
case AdoProviderType.OleProvider:
#region Status Output
#if DEBUG
Console.WriteLine("OleDb provider found. Creating connection...");
#endif
#endregion
using (OleDbConnection conOle = new OleDbConnection(ConnectionString))
{
conOle.Open();
ServerVersion = conOle.ServerVersion;
ConnState = conOle.State.ToString();
conOle.Close();
}
break;
case AdoProviderType.DB2Provider:
//using (DB2Connection conDb2 = new DB2Connection(ConnectionString))
//{
// conDb2.Open();
// ServerVersion = conDb2.ServerVersion;
// ConnState = conDb2.State.ToString();
// conDb2.Close();
//}
//break;
throw new ArgumentException("DB2 Connection Types are not supported by this function.");
case AdoProviderType.OdbcProvider:
using (OdbcConnection conOdbc = new OdbcConnection(ConnectionString))
{
conOdbc.Open();
ServerVersion = conOdbc.ServerVersion;
ConnState = conOdbc.State.ToString();
conOdbc.Close();
}
break;
default:
// We got something unhandled, so inform the user & break.
// This *should* only ever occur if there is a an error in the calling program.
throw new Exception("Invalid provider type.");
}
#region Status Output
#if DEBUG
Console.WriteLine("Connection string valid. Returning true...");
#endif
#endregion
return(true);
}
catch (System.Data.SqlClient.SqlException)
{ return(false); }
catch (System.Data.OleDb.OleDbException)
{ return(false); }
catch (System.Data.Odbc.OdbcException)
{ return(false); }
//catch (IBM.Data.DB2.DB2Exception)
//{ return false; }
catch (InvalidOperationException)
{ throw; }
catch (Exception)
{ throw; }
}
public static DbCommand GetInsertCommand(DataRow schemaRow, AdoProviderType providerID)
{
return(rsData.GetInsertCommand(schemaRow, schemaRow.Table.TableName, providerID));
}
protected int UpdateDataRow(DataRow schemaRow, string tableName, string keyField, AdoProviderType providerID)
{
using (System.Data.Common.DbCommand cmd = rsData.GetUpdateCommand(schemaRow, tableName, keyField, providerID))
return cmd.ExecuteNonQuery();
}
public static int ExecuteNonQuery(AdoProviderType providerId, string connStr, string queryString, CommandType type, int timeOut, params DbParameter[] p)
{
try
{
using (rsDb db = rsDb.GetDbObject(providerId, connStr, queryString))
{
db.DbCommand.CommandType = type;
db.DbCommand.CommandTimeout = timeOut;
if (p != null)
for (int i = 0; i < p.Length; i++)
db.DbCommand.Parameters.Add((DbParameter)p[i]);
return db.DbCommand.ExecuteNonQuery();
}
}
catch
{ throw; }
}
public static rsDb GetDbObject(AdoProviderType id, string connStr, string qryString)
{
switch (id)
{
case AdoProviderType.Auto:
return rsDb.GetDbObject(rsData.ParseProviderType(connStr), connStr, qryString);
case AdoProviderType.SqlProvider:
return new rsDbSql(connStr, qryString);
case AdoProviderType.OleProvider:
return new rsDbOle(connStr, qryString);
case AdoProviderType.OdbcProvider:
return new rsDbOdbc(connStr, qryString);
case AdoProviderType.DB2Provider:
//return new rsDbDb2(connStr, qryString);
string dllFn = "RainstormStudios.Data.DB2.dll";
if (!System.IO.File.Exists(System.IO.Path.Combine(Environment.CurrentDirectory, dllFn)))
throw new System.IO.FileNotFoundException("RainstormStudios DB2 Library was not found in the application path.", dllFn);
System.Reflection.Assembly asm = System.Reflection.Assembly.LoadWithPartialName(dllFn);
if (asm == null)
throw new Exception("Unable to load RainstormStudios.Data.DB2.dll library file.");
object db2 = asm.CreateInstance("RainstormStudios.Data.DB2.rsDbDb2");
if (db2 == null)
throw new Exception("An error occured while trying to create rsDbDb2 instance.");
else
return (rsDb)db2;
default:
// We should *never* hit this line of code.
throw new ArgumentException("Unrecognized AdoProviderType value.");
}
}
//***************************************************************************
// Class Constructors
//
public DbConnectionProperties(AdoProviderType providerid, string connString)
:this(providerid)
{
this = DbConnectionProperties.FromString(providerid, connString);
}
protected int InsertDataRow(DataRow schemaRow, string tableName, AdoProviderType providerID)
{
using (System.Data.Common.DbCommand cmd = rsData.GetInsertCommand(schemaRow, tableName, providerID))
return cmd.ExecuteNonQuery();
}
public DbConnectionProperties(AdoProviderType providerid)
{
if (providerid == AdoProviderType.Auto)
throw new ArgumentException("Enumeration value \"Auto\" is not valid for DbConnectionProperties object.", "providerid");
this._vals = new StringCollection();
this._dbType = providerid;
}
public static bool IsSQL(string connStr, AdoProviderType dbType)
{
int iStrt = connStr.ToLower().IndexOf("provider");
if (iStrt >= 0) iStrt = connStr.IndexOf('=', iStrt);
int iEnd = (iStrt > -1) ? connStr.IndexOf(';', iStrt) : -1;
return dbType == AdoProviderType.SqlProvider
|| (dbType == AdoProviderType.OleProvider && iStrt > -1
&& ((iEnd > -1 && connStr.Substring(iStrt, iEnd - iStrt).ToUpper().Contains("SQL"))
|| (connStr.Substring(iStrt).ToUpper().Contains("SQL"))));
}
public static DbCommand GetInsertCommand(DataRow schemaRow, string tableName, AdoProviderType providerID)
{
if (schemaRow.Table == null)
{
throw new ArgumentException("DataRow must belong to an existing DataTable with populated columns.", "schemaRow");
}
DbCommand cmdObj = null;
switch (providerID)
{
case AdoProviderType.SqlProvider:
cmdObj = new SqlCommand();
break;
case AdoProviderType.OleProvider:
cmdObj = new OleDbCommand();
break;
case AdoProviderType.OdbcProvider:
cmdObj = new OdbcCommand();
break;
case AdoProviderType.DB2Provider:
throw new ArgumentException("Dynamic insert command generation is not supported for DB2 providers.", "providerID");
default:
throw new ArgumentException("The supplied provider ID is invalid for this function.", "providerID");
}
// We're going to build the SQL Insert statement in two
// pieces, so that the ForEach loop can do it for us.
string qry_Insert = "INSERT INTO " + tableName + " (";
string qry_Values = ") VALUES (";
// Then, we parse through all available fields in the
// DataRow by creating a DataColumn object referenced
// to the "Columns" property of the DataRow's
// parent table in the DataSet.
foreach (DataColumn rowField in schemaRow.Table.Columns)
{
qry_Insert += rowField.ColumnName + ", ";
qry_Values += "@" + rowField.ColumnName + ", ";
switch (providerID)
{
case AdoProviderType.SqlProvider:
{
SqlParameter p = ((SqlCommand)cmdObj).Parameters.Add("@" + rowField.ColumnName, rsData.GetSqlDataType(rowField.DataType), rowField.MaxLength);
p.SourceColumn = rowField.ColumnName; p.SourceVersion = DataRowVersion.Original;
}
break;
case AdoProviderType.OleProvider:
{
OleDbParameter p = ((OleDbCommand)cmdObj).Parameters.Add("@" + rowField.ColumnName, rsData.GetOleDataType(rowField.DataType), rowField.MaxLength);
p.SourceColumn = rowField.ColumnName; p.SourceVersion = DataRowVersion.Original;
}
break;
case AdoProviderType.OdbcProvider:
{
OdbcParameter p = ((OdbcCommand)cmdObj).Parameters.Add("@" + rowField.ColumnName, rsData.GetOdbcDataType(rowField.DataType), rowField.MaxLength);
p.SourceColumn = rowField.ColumnName; p.SourceVersion = DataRowVersion.Original;
}
break;
}
}
// Once we've captured all our fields, we return the QueryString
cmdObj.CommandText = qry_Insert.Substring(0, qry_Insert.Length - 2) + qry_Values.Substring(0, qry_Values.Length - 2) + ")";
return(cmdObj);
}
public static DbConnection GetOpenConnection(AdoProviderType providerType, string connStr)
{
return(rsData.GetOpenConnection(providerType, connStr, 5, 1200));
}
public static DbCommand GetUpdateCommand(DataRow schemaRow, string keyField, AdoProviderType providerID)
{
return(GetUpdateCommand(schemaRow, schemaRow.Table.TableName, keyField, providerID));
}
public static DbCommand GetUpdateCommand(DataRow schemaRow, string keyField, AdoProviderType providerID)
{
return GetUpdateCommand(schemaRow, schemaRow.Table.TableName, keyField, providerID);
}
public static DataSet Execute(AdoProviderType dbProvider, string connStr, string sql, CommandType type, int timeOut, params DbParameter[] p)
{
try
{
using (rsDb db = rsDb.GetDbObject(dbProvider, connStr, sql))
{
db.DbCommand.CommandType = type;
db.DbCommand.CommandTimeout = timeOut;
if (p != null)
for (int i = 0; i < p.Length; i++)
db.DbCommand.Parameters.Add((DbParameter)p[i]);
return rsDb.Execute(db.DbCommand);
}
}
catch
{ throw; }
}
public static DataSet Execute(AdoProviderType dbProvider, string connStr, string sql, CommandType type, params DbParameter[] p)
{
try
{ return rsDb.Execute(dbProvider, connStr, sql, type, 0, p); }
catch
{ throw; }
}
/// <summary>
/// Checks the given connection string for validity by attempting to open a connection with it.
/// </summary>
/// <param name="ConnectionString">The connection string to test.</param>
/// <param name="ProviderArg">An integer (as enumerated in AosCommon.ProviderType) indicating the ADO.NET adapter to use.</param>
/// <returns>A true or false value indicating whether or not the test was successful.</returns>
public static bool CheckConnectionString(string ConnectionString, AdoProviderType ProviderArg)
{
#region Status Output
#if DEBUG
Console.WriteLine("Checking connection string validity...");
#endif
#endregion
try
{
string ServerVersion = "", ConnState = "";
switch (ProviderArg)
{
case AdoProviderType.Auto:
return rsData.CheckConnectionString(ConnectionString, rsData.ParseProviderType(ConnectionString));
case AdoProviderType.SqlProvider:
using (SqlConnection conTest = new SqlConnection(ConnectionString))
{
conTest.Open();
ServerVersion = conTest.ServerVersion;
ConnState = conTest.State.ToString();
conTest.Close();
}
break;
case AdoProviderType.OleProvider:
#region Status Output
#if DEBUG
Console.WriteLine("OleDb provider found. Creating connection...");
#endif
#endregion
using (OleDbConnection conOle = new OleDbConnection(ConnectionString))
{
conOle.Open();
ServerVersion = conOle.ServerVersion;
ConnState = conOle.State.ToString();
conOle.Close();
}
break;
case AdoProviderType.DB2Provider:
//using (DB2Connection conDb2 = new DB2Connection(ConnectionString))
//{
// conDb2.Open();
// ServerVersion = conDb2.ServerVersion;
// ConnState = conDb2.State.ToString();
// conDb2.Close();
//}
//break;
throw new ArgumentException("DB2 Connection Types are not supported by this function.");
case AdoProviderType.OdbcProvider:
using (OdbcConnection conOdbc = new OdbcConnection(ConnectionString))
{
conOdbc.Open();
ServerVersion = conOdbc.ServerVersion;
ConnState = conOdbc.State.ToString();
conOdbc.Close();
}
break;
default:
// We got something unhandled, so inform the user & break.
// This *should* only ever occur if there is a an error in the calling program.
throw new Exception("Invalid provider type.");
}
#region Status Output
#if DEBUG
Console.WriteLine("Connection string valid. Returning true...");
#endif
#endregion
return true;
}
catch (System.Data.SqlClient.SqlException)
{ return false; }
catch (System.Data.OleDb.OleDbException)
{ return false; }
catch (System.Data.Odbc.OdbcException)
{ return false; }
//catch (IBM.Data.DB2.DB2Exception)
//{ return false; }
catch (InvalidOperationException)
{ throw; }
catch (Exception)
{ throw; }
}
//***************************************************************************
// Private Methods
//
private DataSet ExecuteCommand(string ConnectionString, string Command, AdoProviderType Provider)
{
this.ReadStartedEvent();
// Declare DB object.
rsDb dbRead = null;
try
{
// Initialize using the proper DB provider class.
dbRead = rsDb.GetDbObject(Provider, ConnectionString, Command);
dbRead.DataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
return dbRead.GetData();
}
catch(Exception ex)
{
throw new Exception("Unable to read data from database.", ex);
}
finally
{
if (dbRead != null)
dbRead.Dispose();
}
}
public static DbCommand GetInsertCommand(DataRow schemaRow, string tableName, AdoProviderType providerID)
{
if (schemaRow.Table == null)
throw new ArgumentException("DataRow must belong to an existing DataTable with populated columns.", "schemaRow");
DbCommand cmdObj = null;
switch (providerID)
{
case AdoProviderType.SqlProvider:
cmdObj = new SqlCommand();
break;
case AdoProviderType.OleProvider:
cmdObj = new OleDbCommand();
break;
case AdoProviderType.OdbcProvider:
cmdObj = new OdbcCommand();
break;
case AdoProviderType.DB2Provider:
throw new ArgumentException("Dynamic insert command generation is not supported for DB2 providers.", "providerID");
default:
throw new ArgumentException("The supplied provider ID is invalid for this function.", "providerID");
}
// We're going to build the SQL Insert statement in two
// pieces, so that the ForEach loop can do it for us.
StringBuilder sbQry_Insert = new StringBuilder("INSERT INTO " + tableName + " (");
StringBuilder sbQry_Values = new StringBuilder(") VALUES (");
//string qry_Insert = "INSERT INTO " + tableName + " (";
//string qry_Values = ") VALUES (";
// Then, we parse through all available fields in the
// DataRow by creating a DataColumn object referenced
// to the "Columns" property of the DataRow's
// parent table in the DataSet.
foreach (DataColumn rowField in schemaRow.Table.Columns)
{
//qry_Insert += rowField.ColumnName + ", ";
//qry_Values += "@" + rowField.ColumnName + ", ";
sbQry_Insert.Append(rowField.ColumnName + ", ");
sbQry_Values.Append("@" + rowField.ColumnName + ", ");
switch (providerID)
{
case AdoProviderType.SqlProvider:
{
SqlParameter p = ((SqlCommand)cmdObj).Parameters.Add("@" + rowField.ColumnName, rsData.GetSqlDataType(rowField.DataType), rowField.MaxLength);
p.SourceColumn = rowField.ColumnName; p.SourceVersion = DataRowVersion.Original;
}
break;
case AdoProviderType.OleProvider:
{
OleDbParameter p = ((OleDbCommand)cmdObj).Parameters.Add("@" + rowField.ColumnName, rsData.GetOleDataType(rowField.DataType), rowField.MaxLength);
p.SourceColumn = rowField.ColumnName; p.SourceVersion = DataRowVersion.Original;
}
break;
case AdoProviderType.OdbcProvider:
{
OdbcParameter p = ((OdbcCommand)cmdObj).Parameters.Add("@" + rowField.ColumnName, rsData.GetOdbcDataType(rowField.DataType), rowField.MaxLength);
p.SourceColumn = rowField.ColumnName; p.SourceVersion = DataRowVersion.Original;
}
break;
}
}
// Once we've captured all our fields, we return the QueryString
//cmdObj.CommandText = qry_Insert.Substring(0, qry_Insert.Length - 2) + qry_Values.Substring(0, qry_Values.Length - 2) + ")";
cmdObj.CommandText = sbQry_Insert.ToString().TrimEnd(',', ' ') + sbQry_Values.ToString().TrimEnd(',', ' ') + ")";
return cmdObj;
}
public static int ExecuteNonQuery(AdoProviderType providerId, string connStr, string queryString, CommandType type, params DbParameter[] p)
{ return rsDb.ExecuteNonQuery(providerId, connStr, queryString, type, 0, p); }
public static DbCommand GetUpdateCommand(DataRow schemaRow, string tableName, string keyField, AdoProviderType providerID)
{
if (schemaRow.Table == null)
{
throw new ArgumentException("DataRow must belong to an existing DataTable with populated columns.", "schemaRow");
}
DbCommand cmdObj = null;
switch (providerID)
{
case AdoProviderType.SqlProvider:
cmdObj = new SqlCommand();
break;
case AdoProviderType.OleProvider:
cmdObj = new OleDbCommand();
break;
case AdoProviderType.OdbcProvider:
cmdObj = new OdbcCommand();
break;
case AdoProviderType.DB2Provider:
throw new ArgumentException("Dynamic insert command generation is not supported for DB2 providers.", "providerID");
default:
throw new ArgumentException("The supplied provider ID is invalid for this function.", "providerID");
}
string updCmd = "UPDATE " + tableName + " SET ";
using (DataTable dtSchema = new DataTable(tableName))
{
foreach (DataColumn dc in schemaRow.Table.Columns)
{
switch (providerID)
{
case AdoProviderType.SqlProvider:
{
SqlParameter p = ((SqlCommand)cmdObj).Parameters.Add("@" + dc.ColumnName, rsData.GetSqlDataType(dc.DataType), dc.MaxLength);
p.SourceColumn = dc.ColumnName; p.SourceVersion = DataRowVersion.Original;
}
break;
case AdoProviderType.OleProvider:
{
OleDbParameter p = ((OleDbCommand)cmdObj).Parameters.Add("@" + dc.ColumnName, rsData.GetOleDataType(dc.DataType), dc.MaxLength);
p.SourceColumn = dc.ColumnName; p.SourceVersion = DataRowVersion.Original;
}
break;
case AdoProviderType.OdbcProvider:
{
OdbcParameter p = ((OdbcCommand)cmdObj).Parameters.Add("@" + dc.ColumnName, rsData.GetOdbcDataType(dc.DataType), dc.MaxLength);
p.SourceColumn = dc.ColumnName; p.SourceVersion = DataRowVersion.Original;
}
break;
}
updCmd += dc.ColumnName + " = @" + dc.ColumnName;
}
}
cmdObj.CommandText = updCmd + " WHERE " + keyField + " = @" + keyField;
return(cmdObj);
}
public static void DropTable(AdoProviderType id, string connStr, string tableName)
{
try
{
string qryStr = String.Format("DROP TABLE {0}", tableName);
using (rsDb db = rsDb.GetDbObject(id, connStr, qryStr))
db.ExecuteNonQuery();
}
catch
{ throw; }
}
public static DbCommand GetUpdateCommand(DataRow schemaRow, string tableName, string keyField, AdoProviderType providerID)
{
if (schemaRow.Table == null)
throw new ArgumentException("DataRow must belong to an existing DataTable with populated columns.", "schemaRow");
DbCommand cmdObj = null;
switch (providerID)
{
case AdoProviderType.SqlProvider:
cmdObj = new SqlCommand();
break;
case AdoProviderType.OleProvider:
cmdObj = new OleDbCommand();
break;
case AdoProviderType.OdbcProvider:
cmdObj = new OdbcCommand();
break;
case AdoProviderType.DB2Provider:
throw new ArgumentException("Dynamic insert command generation is not supported for DB2 providers.", "providerID");
default:
throw new ArgumentException("The supplied provider ID is invalid for this function.", "providerID");
}
//string updCmd = "UPDATE " + tableName + " SET ";
StringBuilder sbUpdCmd = new StringBuilder("UPDATE " + tableName + " SET ");
using (DataTable dtSchema = new DataTable(tableName))
{
foreach (DataColumn dc in schemaRow.Table.Columns)
{
switch (providerID)
{
case AdoProviderType.SqlProvider:
{
SqlParameter p = ((SqlCommand)cmdObj).Parameters.Add("@" + dc.ColumnName, rsData.GetSqlDataType(dc.DataType), dc.MaxLength);
p.SourceColumn = dc.ColumnName; p.SourceVersion = DataRowVersion.Original;
}
break;
case AdoProviderType.OleProvider:
{
OleDbParameter p = ((OleDbCommand)cmdObj).Parameters.Add("@" + dc.ColumnName, rsData.GetOleDataType(dc.DataType), dc.MaxLength);
p.SourceColumn = dc.ColumnName; p.SourceVersion = DataRowVersion.Original;
}
break;
case AdoProviderType.OdbcProvider:
{
OdbcParameter p = ((OdbcCommand)cmdObj).Parameters.Add("@" + dc.ColumnName, rsData.GetOdbcDataType(dc.DataType), dc.MaxLength);
p.SourceColumn = dc.ColumnName; p.SourceVersion = DataRowVersion.Original;
}
break;
}
//updCmd += dc.ColumnName + " = @" + dc.ColumnName;
sbUpdCmd.Append(dc.ColumnName + " = @" + dc.ColumnName);
}
}
//cmdObj.CommandText = updCmd + " WHERE " + keyField + " = @" + keyField;
cmdObj.CommandText = sbUpdCmd.ToString() + " WHERE " + keyField + " = @" + keyField;
return cmdObj;
}
//***************************************************************************
// Class Constructors
//
public Connection(string nm, string cnStr, AdoProviderType provId)
{
this.ConnectionString = cnStr;
this.Name = nm;
this.DatabaseProvider = provId;
}
