/**
* Obtener todos los campos de una tabla
*
* Sino se pasa la tabla, se obtendra los campos de la tabla del modelo instanciado
*
* @param string tabla de la cual se obtendran sus campos
* @return List<String> lista de campos
*/
public List <String> getFieldsDatabase(String table = "")
{
table = String.IsNullOrEmpty(table) ? this.table : table;
List <String> fieldsDatabase = new List <String>();
string sql = $"SELECT * FROM {table}";
System.Data.OleDb.OleDbDataReader oOleDbDataReader = this.oDataBase.executeSql(sql);
try
{
DataTable dtSchema = new DataTable("Schema");
dtSchema = oOleDbDataReader.GetSchemaTable();
foreach (DataRow schemarow in dtSchema.Rows)
{
fieldsDatabase.Add(schemarow.ItemArray[0].ToString());
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
return(fieldsDatabase);
}
public void RefreshSchema()
{
// creates metadata
if (SelectCommand == null)
{
throw new InvalidOperationException("SelectCommand should be valid");
}
if (SelectCommand.Connection == null)
{
throw new InvalidOperationException("SelectCommand's Connection should be valid");
}
CommandBehavior behavior = CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo;
if (SelectCommand.Connection.State != ConnectionState.Open)
{
SelectCommand.Connection.Open();
behavior |= CommandBehavior.CloseConnection;
}
OleDbDataReader reader = SelectCommand.ExecuteReader(behavior);
_schema = reader.GetSchemaTable();
reader.Close();
// force creation of commands
_insertCommand = null;
_updateCommand = null;
_deleteCommand = null;
_tableName = String.Empty;
}
public void LoadRow(OleDbDataReader reader)
{
var schema = reader.GetSchemaTable();
foreach (var pi in this.GetType().GetProperties())
{
if (schema.Select("ColumnName='" + pi.Name + "'").Length > 0)
{
var value = reader.GetValue(reader.GetOrdinal(pi.Name));
if (value != null)
pi.SetValue(this, value, null);
}
}
}
public System.Data.DataTable GetTable(System.Data.OleDb.OleDbDataReader _reader)
{
System.Data.DataTable _table = _reader.GetSchemaTable();
System.Data.DataTable _dt = new System.Data.DataTable();
System.Data.DataColumn _dc;
System.Data.DataRow _row;
System.Collections.ArrayList _al = new System.Collections.ArrayList();
for (int i = 0; i < _table.Rows.Count; i++)
{
_dc = new System.Data.DataColumn();
if (!_dt.Columns.Contains(_table.Rows[i]["ColumnName"].ToString()))
{
_dc.ColumnName = _table.Rows[i]["ColumnName"].ToString();
_dc.Unique = Convert.ToBoolean(_table.Rows[i]["IsUnique"]);
_dc.AllowDBNull = Convert.ToBoolean(_table.Rows[i]["AllowDBNull"]);
_dc.ReadOnly = Convert.ToBoolean(_table.Rows[i]["IsReadOnly"]);
_al.Add(_dc.ColumnName);
_dt.Columns.Add(_dc);
}
}
while (_reader.Read())
{
_row = _dt.NewRow();
for (int i = 0; i < _al.Count; i++)
{
_row[((System.String)_al[i])] = _reader[(System.String)_al[i]];
}
_dt.Rows.Add(_row);
}
return(_dt);
}
private DataSet ConvertDataReaderToDataSet(OleDbDataReader reader)
{
DataSet dataSet = new DataSet();
DataTable schemaTable = reader.GetSchemaTable();
DataTable dataTable = new DataTable();
for (int cntr = 0; cntr < schemaTable.Rows.Count; ++cntr)
{
DataRow dataRow = schemaTable.Rows[cntr];
string columnName = dataRow["ColumnName"].ToString();
DataColumn column = new DataColumn(columnName, dataRow["ColumnName"].GetType());
dataTable.Columns.Add(column);
}
while (reader.Read())
{
DataRow dataRow = dataTable.NewRow();
for (int cntr = 0; cntr < reader.FieldCount; ++cntr)
{
dataRow[cntr] = reader.GetValue(cntr);
}
dataTable.Rows.Add(dataRow);
}
dataSet.Tables.Add(dataTable);
return dataSet;
}
public DataSet DataReader2DataSet ( OleDbDataReader reader )
{
DataSet dataSet = new DataSet ();
do
{
// Create new data table
DataTable schemaTable = reader.GetSchemaTable ();
DataTable dataTable = new DataTable ();
if ( schemaTable != null )
{
// A query returning records was executed
for ( int i = 0 ; i < schemaTable.Rows.Count ; i++ )
{
DataRow dataRow = schemaTable.Rows [ i ];
// Create a column name that is unique in the data table
string columnName = ( string ) dataRow [ "ColumnName" ];
// Add the column definition to the data table
DataColumn column = new DataColumn ( columnName , ( Type ) dataRow [ "DataType" ] );
dataTable.Columns.Add ( column );
}
dataSet.Tables.Add ( dataTable );
// Fill the data table we just created
while ( reader.Read () )
{
DataRow dataRow = dataTable.NewRow ();
for ( int i = 0 ; i < reader.FieldCount ; i++ )
dataRow [ i ] = reader.GetValue ( i );
dataTable.Rows.Add ( dataRow );
}
}
else
{
// No records were returned
DataColumn column = new DataColumn ( "RowsAffected" );
dataTable.Columns.Add ( column );
dataSet.Tables.Add ( dataTable );
DataRow dataRow = dataTable.NewRow ();
dataRow [ 0 ] = reader.RecordsAffected;
dataTable.Rows.Add ( dataRow );
}
}
while ( reader.NextResult () );
return dataSet;
}
// Methods
protected bool CollectResultsInList(ArrayList results, OleDbDataReader resultSet)
{
int count = results.Count;
try
{
Hashtable hashtable = new Hashtable();
foreach (DataRow row in resultSet.GetSchemaTable().Rows)
{
hashtable.Add(row["ColumnName"].ToString(), null);
}
Hashtable hashtable2 = null;
while (resultSet.Read())
{
hashtable2 = (Hashtable)hashtable.Clone();
foreach (string str in hashtable.Keys)
{
hashtable2[str] = resultSet[str];
if (hashtable2[str] == null)
{
hashtable2[str] = "";
}
}
results.Add(hashtable2);
}
}
catch (Exception exception)
{
this.error.message = "An error occurred while collecting the results of your query.";
this.error.info = exception.ToString();
}
return (results.Count > count);
}
