virtual public Recordset ExecuteSql(string sql)
{
Recordset oRS = new Recordset();
OleDbConnection cn = null;
OleDbDataReader reader = null;
try
{
cn = new OleDbConnection(dbRoot.ConnectionString);
cn.Open();
try
{
cn.ChangeDatabase(this.Name);
}
catch { } // some databases don't have the concept of catalogs. Catch this and throw it out
OleDbCommand command = new OleDbCommand(sql, cn);
command.CommandType = CommandType.Text;
reader = command.ExecuteReader();
DataTable schema;
string dataType, fieldname;
int length;
bool firstTime = true;
while (reader.Read())
{
if (firstTime)
{
schema = reader.GetSchemaTable();
foreach (DataRow row in schema.Rows)
{
fieldname = row["ColumnName"].ToString();
dataType = row["DataType"].ToString();
length = Convert.ToInt32(row["ColumnSize"]);
oRS.Fields.Append(fieldname, GetADOType(dataType), length,
FieldAttributeEnum.adFldIsNullable, System.Reflection.Missing.Value);
}
oRS.Open(System.Reflection.Missing.Value, System.Reflection.Missing.Value,
CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockOptimistic, 1);
firstTime = false;
}
oRS.AddNew(System.Reflection.Missing.Value, System.Reflection.Missing.Value);
for (int i = 0; i < reader.FieldCount; i++)
{
if (reader[i] is System.Guid)
{
oRS.Fields[i].Value = "{" + reader[i].ToString() + "}";
}
else
{
oRS.Fields[i].Value = reader[i];
}
}
}
cn.Close();
//Move to the first record
if (!firstTime)
{
oRS.MoveFirst();
}
else
{
oRS = null;
}
}
catch (Exception ex)
{
if ((reader != null) && (!reader.IsClosed))
{
reader.Close();
reader = null;
}
if ((cn != null) && (cn.State == ConnectionState.Open))
{
cn.Close();
cn = null;
}
throw ex;
}
return(oRS);
}
protected Recordset ExecuteIntoRecordset(string sql, IDbConnection cn)
{
Recordset oRS = new Recordset();
IDataReader reader = null;
try
{
IDbCommand command = cn.CreateCommand();
command.CommandText = sql;
command.CommandType = CommandType.Text;
reader = command.ExecuteReader();
DataTable schema;
string dataType, fieldname;
int length;
bool firstTime = true;
// Skip columns contains the index of any columns that we cannot handle, array types and such ...
Hashtable skipColumns = null;
while (reader.Read())
{
if (firstTime)
{
skipColumns = new Hashtable();
schema = reader.GetSchemaTable();
int colID = 0;
foreach (DataRow row in schema.Rows)
{
fieldname = row["ColumnName"].ToString();
dataType = row["DataType"].ToString();
length = Convert.ToInt32(row["ColumnSize"]);
try
{
oRS.Fields.Append(fieldname, GetADOType(dataType), length,
FieldAttributeEnum.adFldIsNullable, System.Reflection.Missing.Value);
}
catch
{
// We can't handle this column type, ie, Firebird array types
skipColumns[colID] = colID;
}
colID++;
}
oRS.Open(System.Reflection.Missing.Value, System.Reflection.Missing.Value,
CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockOptimistic, 1);
firstTime = false;
}
oRS.AddNew(System.Reflection.Missing.Value, System.Reflection.Missing.Value);
for (int i = 0, j = 0; i < reader.FieldCount; i++)
{
// Skip columns that we cannot handle
if (!skipColumns.ContainsKey(i))
{
if (reader[j] is System.Guid)
{
oRS.Fields[j].Value = "{" + reader[j].ToString() + "}";
}
else
{
try
{
oRS.Fields[j].Value = reader[j];
}
catch
{
// For some reason it wouldn't accept this value?
oRS.Fields[j].Value = DBNull.Value;
}
}
j++;
}
}
}
cn.Close();
//Move to the first record
if (!firstTime)
{
oRS.MoveFirst();
}
else
{
oRS = null;
}
}
catch (Exception ex)
{
if ((reader != null) && (!reader.IsClosed))
{
reader.Close();
reader = null;
}
if ((cn != null) && (cn.State == ConnectionState.Open))
{
cn.Close();
cn = null;
}
throw ex;
}
return(oRS);
}