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;
}
private static IDataParameter BuildParameter(IDataParameter dbDataParameter, ParamHelper dtxParam)
{
Debug.Assert(dbDataParameter != null, "DataParamter instance required");
Debug.Assert(dtxParam != null, "DtxParam instance required.");
dbDataParameter.Direction = dtxParam.Direction;
dbDataParameter.ParameterName = dtxParam.Name;
dbDataParameter.DbType = ConvertSystemTypeToDbType(dtxParam.Value.GetType());
dbDataParameter.Value = dtxParam.Value;
return dbDataParameter;
}
