/// <summary>
/// Create and populate a DataView from the given SqlResult. Columns in the result set
/// are copied verbatim to the DataView.
/// </summary>
/// <param name="sr">The SqlResult used to generate and populate the DataView.</param>
/// <returns>The new DataView instance</returns>
public static DataView GetDataView( SqlResult sr )
{
DataTable dt = new DataTable();
ObjectMap map = null;
if( sr.Statement != null && sr.Statement.Type != null )
{
map = ObjectFactory.GetMap( sr.SessionBroker, sr.Statement.Type );
}
foreach( string columnName in sr.ColumnNames )
{
DataColumn column = new DataColumn( columnName );
Type type = GetColumnType( columnName, map, sr );
if( type != null )
{
column.DataType = type;
}
dt.Columns.Add( column );
}
DataRow dr;
foreach( object[] row in sr.Rows )
{
dr = dt.NewRow();
dr.ItemArray = row;
dt.Rows.Add( dr );
}
return new DataView( dt );
}
/// <summary>
/// Execute the current instance using the supplied database connection and return
/// the result of the operation as an instance of the <see cref="SqlResult"/> class.
/// </summary>
/// <param name="conn">The database connection to use for executing this statement.</param>
/// <param name="tr">The transaction instance to run this statement in. If there is no
/// transaction context then this parameter should be null. This will cause the
/// connection to be closed after execution.</param>
/// <returns>The result of the operation</returns>
internal SqlResult Execute(IDbConnection conn, IDbTransaction tr)
{
Check.VerifyNotNull(conn, Error.NoNewConnection);
cmd.Connection = conn;
cmd.Transaction = tr;
SqlResult sr = null;
StringBuilder log = IsLoggingEnabled ? new StringBuilder(500) : null;
try
{
if (log != null)
{
log.AppendFormat("Executing query: {0}{1}", cmd.CommandText, Environment.NewLine);
foreach (IDataParameter param in cmd.Parameters)
{
object paramValue = param.Value == null ? "null" : param.Value;
log.AppendFormat("Parameter: {0} = {1}{2}", param.ParameterName, paramValue, Environment.NewLine);
}
}
switch (statementType)
{
case StatementType.Count:
case StatementType.Identity:
sr = new SqlResult(broker, cmd.ExecuteScalar(), this);
break;
case StatementType.Select:
sr = new SqlResult(broker, cmd.ExecuteReader(), this);
break;
case StatementType.Update:
case StatementType.Delete:
case StatementType.SoftDelete:
// returns the number of rows affected
sr = new SqlResult(broker, cmd.ExecuteNonQuery(), this);
break;
case StatementType.Insert:
if (map != null && map.IdentityMap != null)
{
GentleSqlFactory sf = broker.GetSqlFactory();
// check if we need to execute insert statement separately
if (!sf.HasCapability(Capability.BatchQuery))
{
// execute the insert command
cmd.ExecuteNonQuery();
// get the sql string for retrieving the generated id
string sql = sf.GetIdentitySelect(null, map);
// get an sql statement that will execute using ExecuteScalar
SqlStatement stmt = broker.GetStatement(StatementType.Identity, sql);
// execute the query and fetch the id
sr = stmt.Execute(cmd.Connection, tr);
}
else
{
// returns the 1st column of the 1st row in the result set (i.e. new identity)
sr = new SqlResult(broker, cmd.ExecuteScalar(), this);
}
}
else
{
sr = new SqlResult(broker, cmd.ExecuteNonQuery(), this);
}
break;
default:
// returns the number of rows affected
sr = new SqlResult(broker, cmd.ExecuteReader(), this);
break;
}
if (log != null)
{
PerformExecutionLog(log, sr);
}
}
catch (Exception e)
{
if (!Check.IsCalledFrom("Analyzer.Analyze", e) || !GentleSettings.AnalyzerSilent)
{
Check.LogError(LogCategories.StatementExecution, e);
}
if (e is GentleException)
{
throw;
}
else
{
throw new GentleException(Error.StatementError, cmd.CommandText, e);
}
}
finally
{
// close the database connection (only if not in transaction)
if (conn != null && conn.State == ConnectionState.Open && tr == null)
{
conn.Close();
}
// only if not in transaction; would otherwise break with PostgreSQL (not permitted)
if (tr == null)
{
// clear the connection reference
try
{
cmd.Connection = null;
}
catch
{
// ignore errors here.. like for the SQLite-provider which complains for no good reason
}
// clear the transaction reference
cmd.Transaction = null;
}
}
return(sr);
}
private void PerformExecutionLog( StringBuilder log, SqlResult sr )
{
if( log != null )
{
log.Append( "Execution result: " );
LogCategories category = LogCategories.StatementExecutionRead;
switch( StatementType )
{
case StatementType.Select:
case StatementType.Identity:
log.AppendFormat( "{0} row{1} retrieved.", sr.RowsContained, sr.RowsContained == 1 ? "" : "s" );
break;
case StatementType.Count:
log.AppendFormat( "{0} row{1} counted.", sr.Count, sr.Count == 1 ? "" : "s" );
break;
case StatementType.Insert:
case StatementType.Update:
case StatementType.Delete:
case StatementType.SoftDelete:
log.AppendFormat( "{0} row{1} affected.", sr.RowsAffected, sr.RowsAffected == 1 ? "" : "s" );
category = LogCategories.StatementExecutionWrite;
break;
case StatementType.Unknown:
default:
category = LogCategories.StatementExecutionOther;
break; // no post-execution log for these statements
}
log.Append( Environment.NewLine );
Check.LogInfo( category, log.ToString() );
}
}
private static Type GetColumnType( string columnName, ObjectMap map, SqlResult sr )
{
Type result = null;
if( map != null )
{
FieldMap fm = map.GetFieldMapFromColumn( columnName );
if( fm != null )
{
result = fm.Type;
}
}
if( result == null && sr.RowsContained > 0 )
{
object obj = sr.GetObject( 0, columnName );
if( obj != null )
{
result = obj.GetType();
}
}
return result;
}
/// <summary>
/// Update the property values of the supplied object instance with the values of the first
/// row of the rows contained in the supplied SqlResult.
/// </summary>
public void SetProperties( object obj, SqlResult sr )
{
Check.VerifyNotNull( obj, "Unable to update null object!" );
Check.VerifyNotNull( sr, "Unable to update object (SqlResult was null)!" );
ObjectMap map = ObjectFactory.GetMap( sr.SessionBroker, obj.GetType() );
map.SetProperties( obj, sr.ColumnNames, (object[]) sr.Rows[ 0 ] );
}
/// <summary>
/// Execute the current instance using the supplied database connection and return
/// the result of the operation as an instance of the <see cref="SqlResult"/> class.
/// </summary>
/// <param name="conn">The database connection to use for executing this statement.</param>
/// <param name="tr">The transaction instance to run this statement in. If there is no
/// transaction context then this parameter should be null. This will cause the
/// connection to be closed after execution.</param>
/// <returns>The result of the operation</returns>
internal SqlResult Execute( IDbConnection conn, IDbTransaction tr )
{
Check.VerifyNotNull( conn, Error.NoNewConnection );
cmd.Connection = conn;
cmd.Transaction = tr;
SqlResult sr = null;
StringBuilder log = IsLoggingEnabled ? new StringBuilder( 500 ) : null;
try
{
if( log != null )
{
log.AppendFormat( "Executing query: {0}{1}", cmd.CommandText, Environment.NewLine );
foreach( IDataParameter param in cmd.Parameters )
{
object paramValue = param.Value == null ? "null" : param.Value;
log.AppendFormat( "Parameter: {0} = {1}{2}", param.ParameterName, paramValue, Environment.NewLine );
}
}
switch( statementType )
{
case StatementType.Count:
case StatementType.Identity:
sr = new SqlResult( broker, cmd.ExecuteScalar(), this );
break;
case StatementType.Select:
sr = new SqlResult( broker, cmd.ExecuteReader(), this );
break;
case StatementType.Update:
case StatementType.Delete:
case StatementType.SoftDelete:
// returns the number of rows affected
sr = new SqlResult( broker, cmd.ExecuteNonQuery(), this );
break;
case StatementType.Insert:
if( map != null && map.IdentityMap != null )
{
GentleSqlFactory sf = broker.GetSqlFactory();
// check if we need to execute insert statement separately
if( ! sf.HasCapability( Capability.BatchQuery ) )
{
// execute the insert command
cmd.ExecuteNonQuery();
// get the sql string for retrieving the generated id
string sql = sf.GetIdentitySelect( null, map );
// get an sql statement that will execute using ExecuteScalar
SqlStatement stmt = broker.GetStatement( StatementType.Identity, sql );
// execute the query and fetch the id
sr = stmt.Execute( cmd.Connection, tr );
}
else
{
// returns the 1st column of the 1st row in the result set (i.e. new identity)
sr = new SqlResult( broker, cmd.ExecuteScalar(), this );
}
}
else
{
sr = new SqlResult( broker, cmd.ExecuteNonQuery(), this );
}
break;
default:
// returns the number of rows affected
sr = new SqlResult( broker, cmd.ExecuteReader(), this );
break;
}
if( log != null )
{
PerformExecutionLog( log, sr );
}
}
catch( Exception e )
{
if( ! Check.IsCalledFrom( "Analyzer.Analyze", e ) || ! GentleSettings.AnalyzerSilent )
{
Check.LogError( LogCategories.StatementExecution, e );
}
if( e is GentleException )
{
throw;
}
else
{
throw new GentleException( Error.StatementError, cmd.CommandText, e );
}
}
finally
{
// close the database connection (only if not in transaction)
if( conn != null && conn.State == ConnectionState.Open && tr == null )
{
conn.Close();
}
// only if not in transaction; would otherwise break with PostgreSQL (not permitted)
if( tr == null )
{
// clear the connection reference
try
{
cmd.Connection = null;
}
catch
{
// ignore errors here.. like for the SQLite-provider which complains for no good reason
}
// clear the transaction reference
cmd.Transaction = null;
}
}
return sr;
}
/// <summary>
/// This method is for adding set membership constraints using either Operator.In or
/// Operator.NotIn. If an invalid operator is used this method will throw an exception.
/// </summary>
/// <param name="op">The operator used for this constraint.</param>
/// <param name="field">The field to constrain. The framework assumes property names but
/// also checks column names if no property could be found.</param>
/// <param name="sr">The SqlResult containing the data used to constrain the field.</param>
/// <param name="constraintField">This is the column name to use from all rows in the SqlResult.
/// String-type fields are quoted, all other types are not. This method performs no additional
/// type checking and is likely to fail (when the statement is executed) for esoteric types
/// such as decimals or dates.</param>
public void AddConstraint( Operator op, string field, SqlResult sr, string constraintField )
{
if( sr != null && sr.RowsContained > 0 && sr.ColumnNames.Length > 0 )
{
AddConstraint( op, field, sr.TransposeToFieldList( constraintField, false ), null );
}
}
/// <summary>
/// This method is for adding set membership constraints using either Operator.In or
/// Operator.NotIn. If an invalid operator is used this method will throw an exception.
/// </summary>
/// <param name="op">The operator used for this constraint.</param>
/// <param name="field">The field to constrain. The framework assumes property names but
/// also checks column names if no property could be found.</param>
/// <param name="sr">The SqlResult containing the data used to constrain the field. The
/// SqlResult must either contain only one column or contain a column named as the field
/// parameter.</param>
public void AddConstraint( Operator op, string field, SqlResult sr )
{
if( sr == null )
{
// resolve method overloading ambiguity by explicitly casting
// the null value to object
AddConstraint( op, field, (object) null );
}
else
{
string constraintField = null;
if( sr.ColumnNames.Length == 1 )
{
constraintField = sr.ColumnNames[ 0 ];
}
else if( sr.ColumnNames.Length > 1 )
{
// no constraint field given and more than 1 column in result
// try to find column named field
foreach( string column in sr.ColumnNames )
{
if( column.Equals( field ) )
{
constraintField = field;
break;
}
}
}
else
{
Check.Fail( Error.NullParameter, "sr" );
}
Check.VerifyNotNull( constraintField, Error.NullParameter, "constraintField" );
AddConstraint( op, field, sr, constraintField );
}
}
/// <summary>
/// Construct multiple objects of a given type from the data contained in the given SqlResult
/// object. Refer to the Construct method of the <see cref="ObjectMap"/> class for details.
/// </summary>
/// <param name="type">The type of object to construct</param>
/// <param name="sr">The SqlResult instance holding the data</param>
/// <param name="result">An optional existing container in which to store the created objects. If
/// this parameter is null a new IList instance will be created.</param>
/// <returns>An IList holding the created objects</returns>
/// <exception cref="GentleException"> will be raised if no object could be created</exception>
public static IList GetCollection(Type type, SqlResult sr, IList result)
{
if (result == null)
{
result = MakeGenericList(type);
}
ObjectMap objectMap = GetMap(sr.SessionBroker, type);
// check whether to store query result information
bool isCache = GentleSettings.CacheObjects && GentleSettings.SkipQueryExecution &&
objectMap.CacheStrategy != CacheStrategy.Never;
IList cache = isCache ? new ArrayList() : null;
// remember keys of cached objects to permit SQE
// process result set
if (sr.RowsContained > 0)
{
ObjectMap actualMap = objectMap;
int columnComboHashCode = 0;
// cache constructor info for dynamic type construction
Hashtable typeHash = null;
Hashtable typeMaps = null;
if (objectMap.InheritanceMap != null)
{
typeHash = new Hashtable();
typeMaps = new Hashtable();
}
else // precompute fixed hash
{
columnComboHashCode = objectMap.DetermineConstructor(sr.ColumnNames, (object[])sr.Rows[0]);
}
// process result set
for (int i = 0; i < sr.Rows.Count; i++)
{
object[] row = (object[])sr.Rows[i];
// dynamic object construction handling
if (typeHash != null)
{
string assemblyQualifiedName = sr.GetString(i, objectMap.InheritanceMap.ColumnName);
if (typeHash.ContainsKey(assemblyQualifiedName))
{
columnComboHashCode = (int)typeHash[assemblyQualifiedName];
actualMap = (ObjectMap)typeMaps[assemblyQualifiedName];
}
else
{
Type rowType = LoadType(assemblyQualifiedName);
actualMap = GetMap(sr.SessionBroker, rowType);
columnComboHashCode = actualMap.DetermineConstructor(sr.ColumnNames, (object[])sr.Rows[0]);
typeMaps[assemblyQualifiedName] = actualMap;
typeHash[assemblyQualifiedName] = columnComboHashCode;
}
}
// skip non-derived classes for dynamic types
if (actualMap.Type == objectMap.Type || actualMap.Type.IsSubclassOf(objectMap.Type))
{
object obj = actualMap.Construct(columnComboHashCode, row, sr.SessionBroker);
if (obj is IEntity)
{
(obj as IEntity).IsPersisted = true;
}
result.Add(obj);
// cache result if necessary
if (isCache)
{
cache.Add(actualMap.GetInstanceHashKey(obj));
}
}
}
}
if (isCache)
{
CacheManager.Insert(sr.Statement.CacheKey, cache);
}
return(result);
}
public static IList <T> GetCollection <T>(SqlResult sr, IList <T> result)
{
Check.Verify(result == null || result is IList, Error.InvalidRequest, "The supplied generic list does not implement IList.");
return((IList <T>)GetCollection(typeof(T), sr, (IList)(result ?? MakeGenericList <T>())));
}
public static IList <T> GetCollection <T>(SqlResult sr)
{
return(GetCollection <T>(sr, null));
}
/// <summary>
/// Construct multiple objects of a given type from the data contained in the given SqlResult
/// object. Refer to the Construct method of the <see cref="ObjectMap"/> class for details.
/// </summary>
/// <param name="type">The type of object to construct</param>
/// <param name="sr">The SqlResult instance holding the data</param>
/// <returns>An IList holding the created objects</returns>
/// <exception cref="GentleException"> will be raised if no object could be created</exception>
public static IList GetCollection(Type type, SqlResult sr)
{
return(GetCollection(type, sr, null));
}
public static T GetInstance <T>(SqlResult sr)
{
return(GetInstance <T>(sr, null));
}
/// <summary>
/// Construct an instance of the given type using data from the first row of the
/// supplied SqlResult instance.
/// Refer to the Construct method of the <see cref="ObjectMap"/> class for details.
/// </summary>
/// <param name="type">The type of object to construct</param>
/// <param name="sr">The SqlResult instance holding the data</param>
/// <returns>An instance of the given type</returns>
/// <exception cref="GentleException"> will be raised if no object could be created</exception>
public static object GetInstance(Type type, SqlResult sr)
{
return(GetInstance(type, sr, null));
}
public static T GetInstance <T>(SqlResult sr, Key key)
{
return((T)GetInstance(typeof(T), sr, key));
}