public IDictionary <TKey, TValue> ExecuteDictionary <TKey, TValue>(string commandText, CommandType commandType, CommandParameterCollection parameters)
{
IDictionary <TKey, TValue> results = new Dictionary <TKey, TValue>();
DbDataReader reader = null;
//HACK: Issue with Npgsql DbReader implementation that if no results are returned
//it'll still return true when invoking reader.Read() which you would have thought
//it'd return false...
try
{
reader = ExecuteReader(commandText, commandType, parameters);
while (reader.Read())
{
results.Add(reader.GetValue <TKey>(0), reader.GetValue <TValue>(1));
}
}
catch (Exception ex)
{
HandleException(ex);
}
finally
{
DisposeUtility.Dispose(reader);
}
return(results);
}
/// <summary>
/// Executes a query to the database using the provided parameters and returns the result as collection of Entity objects.
/// </summary>
public IList <TEntity> ExecuteList <TEntity>(string commandText, CommandType commandType, CommandParameterCollection parameters) where TEntity : Entity <TEntity>, new()
{
IList <TEntity> entities;
DbDataReader reader = null;
//HACK: Issue with Npgsql DbReader implementation that if no results are returned
//it'll still return true when invoking reader.Read() which you would have thought
//it'll return false...
try
{
reader = ExecuteReader(commandText, commandType, parameters);
entities = EntityFactory <TEntity> .CreateListFromReader(reader);
}
catch (Exception ex)
{
entities = new List <TEntity>();
HandleException(ex);
}
finally
{
DisposeUtility.Dispose(reader);
}
return(entities);
}
/// <summary>
/// Executes a query to the database using the provided parameters and returns the result the number of rows affected, if any.
/// </summary>
public int ExecuteNonQuery(string commandText, CommandType commandType, CommandParameterCollection parameters)
{
int affectedRows = -1;
DbCommand command = null;
//IList<DbParameter> outputParameters = new List<DbParameter>();
try
{
CheckConnection();
command = SetupDbCommand(this, this.DatabaseContext, this._connection, this._transaction, commandText, commandType, parameters);
affectedRows = command.ExecuteNonQuery();
//CloseConnection();
ProcessOutputParameters(command, parameters);
}
catch (Exception ex)
{
HandleException(ex);
throw;
}
finally
{
DisposeUtility.Dispose(command);
}
return(affectedRows);
}
/// <summary>
/// Executes a query to the database using the provided parameters and returns the result as an object.
/// </summary>
public object ExecuteScalar(string commandText, CommandType commandType, CommandParameterCollection parameters)
{
object scalarValue = null;
DbCommand command = null;
try
{
CheckConnection();
command = SetupDbCommand(this, this.DatabaseContext, this._connection, this._transaction, commandText, commandType, parameters);
scalarValue = command.ExecuteScalar();
//CloseConnection();
ProcessOutputParameters(command, parameters);
}
catch (Exception ex)
{
HandleException(ex);
throw;
}
finally
{
DisposeUtility.Dispose(command);
}
return(scalarValue);
}
private void Dispose(bool disposing)
{
//If, on dispose, the transaction hasn't been handled, than
//roll it back before disposing it as not all implementations
//of DbTransaction handle these when being disposed.
if (this._transaction != null && this.IsAtomic)
{
RollbackTransaction();
}
this.CloseConnection();
DisposeUtility.Dispose(this._connection, this._transaction);
this._connection = null;
this._transaction = null;
}
/// <summary>
/// Executes a query to the database using the provided parameters and returns the result as a DataSet.
/// </summary>
public DataSet ExecuteDataSet(string commandText, CommandType commandType, CommandParameterCollection parameters)
{
DataSet set = null;
DbCommand command = null;
DbDataAdapter adapter = null;
try
{
CheckConnection();
command = SetupDbCommand(this, this.DatabaseContext, this._connection, this._transaction, commandText, commandType, parameters);
adapter = this.DatabaseContext.Provider.CreateDataAdapter();
adapter.SelectCommand = command;
set = new DataSet();
adapter.Fill(set);
//CloseConnection();
ProcessOutputParameters(command, parameters);
}
catch (Exception ex)
{
if (set != null)
{
set.Dispose();
}
HandleException(ex);
throw;
}
finally
{
DisposeUtility.Dispose(adapter, command);
}
return(set);
}
/// <summary>
/// Executes a query to the database using the provided parameters and returns the result as a DbDataReader.
/// </summary>
public DbDataReader ExecuteReader(string commandText, CommandType commandType, CommandParameterCollection parameters)
{
DbDataReader reader = null;
DbCommand command = null;
try
{
CheckConnection();
command = SetupDbCommand(this, this.DatabaseContext, this._connection, this._transaction, commandText, commandType, parameters);
//if(this._transaction != null)
//{
// reader = command.ExecuteReader();
//}
//else
//{
// reader = command.ExecuteReader(CommandBehavior.CloseConnection);
//}
reader = command.ExecuteReader();
ProcessOutputParameters(command, parameters);
}
catch (Exception ex)
{
HandleException(ex);
throw;
}
finally
{
DisposeUtility.Dispose(command);
}
return(reader);
}
private void btnExecute_Click(object sender, EventArgs e)
{
if (m_dsProvider != null)
{
dgvResults.Rows.Clear();
try
{
// Comment the following line for versions less or equal v. 2.2
IExpressionEvaluationContext exprEvalContext = new DefaultExpressionEvaluationContext();
IFeatureExpressionFilter filterExpr = null;
if (txtFilter.Text.Trim() != string.Empty)
{
filterExpr = new FeatureExpressionFilter();
filterExpr.Compile(txtFilter.Text, m_dsProvider.Information.Attributes);
}
Envelope env = m_dsProvider.GetExtent();
List <string> properties = m_dsProvider.Information.GetAttributeNames();
properties.Remove("_geom_");
DataSourceQuery query = new DataSourceQuery(env, new Tuple <double, double>(1.0, 1.0), null, null, properties);
IFeatureDataReader dataReader = m_dsProvider.GetFeatureReader(query, FeatureFactory.Default);
int i = 0;
if (dataReader != null)
{
int rowsLimit = (int)nudRowsLimit.Value;
while (dataReader.Read())
{
Feature feature = dataReader.GetFeature();
if (rowsLimit > 0 && i >= rowsLimit)
{
break;
}
bool bFeaturePass = true;
if (filterExpr != null)
{
// Apply a given expression to filter features from the data source.
// Change the following line for versions less or equal v. 2.2 by replacing the expression with filterExpr.Pass(feature)
bFeaturePass = filterExpr.Pass(feature, exprEvalContext);
}
if (bFeaturePass)
{
DataGridViewRow row = (DataGridViewRow)dgvResults.Rows[0].Clone();
object[] values = new object[feature.Attributes.Count];
int j = 0;
// Enumerate feature properties and fill the grid view.
foreach (string attr in feature.Attributes.EnumerateNames())
{
object value = feature.GetPropertyValue(attr);
if (attr == "_geom_")
{
value = value.ToString().Replace("MapSurfer.Geometries.", "");
}
values[j] = value;
row.Cells[j].Value = value;
j++;
}
row.SetValues(values);
dgvResults.Rows.Add(row);
i++;
}
}
DisposeUtility.Dispose(ref dataReader);
}
lblRows.Text = i.ToString() + " rows";
UpdateMessage(string.Format("{0} row(s) fetched.", i));
}
catch (Exception ex)
{
UpdateMessage("Exception: " + ex.Message);
}
}
}