/// <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)); }