C# Class Azavea.Open.DAO.SQL.SqlDaLayer

A SQL-specific implementation of an IDaLayer. Provided functionality to run data access function that are specific to data sources that take sql commands.

Inheritance: AbstractDaLayer

Show file Open project: azavea/net-dao Class Usage Examples

Protected Properties

Property	Type	Description
_connDesc	AbstractSqlConnectionDescriptor
_fullyQualifyColumnNames	System.Boolean
_sqlQueryCache	ClearingCache

Public Methods

Method	Description
AppendParameter ( SqlDaQuery queryToAddTo, object value, Type columnType ) : void	Since it is implementation-dependent whether to use the sql parameters collection or not, this method should be implemented in each implementation.
CreateQuery ( ClassMapping mapping, DaoCriteria crit ) : IDaQuery	Builds the query based on a serializable criteria.
CreateQuery ( string queryStr, IEnumerable queryParams, ClassMapping mapping ) : IDaQuery	Builds the query based on a string, such as a sql string. NOTE: Not all FastDaoLayers are required to support this, if it is not supported a NotSupportedException will be thrown. TODO: This will be removed when FastDAO.QueryFor and IterateOverObjects that take strings are removed.
Delete ( ITransaction transaction, ClassMapping mapping, DaoCriteria crit ) : int
DisposeOfQuery ( IDaQuery query ) : void	Should be called when you're done with the query. Allows us to cache the objects for reuse.
ExecuteQuery ( ITransaction transaction, ClassMapping mapping, IDaQuery query, DataReaderDelegate invokeMe, Hashtable parameters ) : void
ExpressionsToQuery ( SqlDaQuery queryToAddTo, DaoCriteria crit, ClassMapping mapping ) : void	Takes a DaoCriteria, converts it to a " WHERE ..." chunk of SQL. The SQL will begin with a space if non-empty.
GetCount ( ITransaction transaction, ClassMapping mapping, DaoCriteria crit, ICollection groupExpressions ) : List
GetCount ( ITransaction transaction, ClassMapping mapping, DaoCriteria crit ) : int
GetLastAutoGeneratedId ( ITransaction transaction, ClassMapping mapping, string idCol ) : object
GetNextSequenceValue ( ITransaction transaction, string sequenceName ) : int
GroupBysToEndOfQuery ( SqlDaQuery query, ICollection groupExpressions, ClassMapping mapping ) : void	Adds the group by fields to the end of the query, including the keyword "GROUP BY" if necessary.
GroupBysToStartOfQuery ( SqlDaQuery query, ICollection groupExpressions, ClassMapping mapping ) : void	Adds the group by fields to the "column" list ("column" since they may not all be columns) in the beginning of the select (I.E. "SELECT COUNT(*), Field1, Field2, etc).
Insert ( ITransaction transaction, ClassMapping mapping, object>.IDictionary propValues ) : int
OrdersToQuery ( SqlDaQuery queryToAddTo, DaoCriteria crit, ClassMapping mapping ) : void	Takes a DaoCriteria, converts it to an " ORDER BY ..." chunk of SQL. The SQL will begin with a space if non-empty.
ReadScalarValue ( Hashtable parameters, IDataReader reader ) : void
SetFullyQualifyColumnNames ( bool fullyQualifyColumnNames ) : void	Sometimes it is not desirable to fully qualify column names, even for a database, e.g. when doing a query that is a join of foreign tables. This allows this value to be set in those instances.
SqlDaLayer ( AbstractSqlConnectionDescriptor connDesc, bool supportsNumRecords ) : System	Instantiates the data access layer with the connection descriptor for the DB.
Truncate ( ClassMapping classMap ) : void
Update ( ITransaction transaction, ClassMapping mapping, DaoCriteria crit, object>.IDictionary propValues ) : int

Protected Methods

Method	Description
BoolTypeToString ( BooleanOperator boolType ) : string	Returns a nicely spaced AND or OR depending on the boolean type.
ExpressionListToQuery ( SqlDaQuery queryToAddTo, BooleanOperator boolType, IEnumerable expressions, ClassMapping mapping, string colPrefix ) : void	Converts the list of expressions from this criteria into SQL, and appends to the given string builder.
ExpressionToQuery ( SqlDaQuery queryToAddTo, IExpression expr, ClassMapping mapping, string colPrefix, string booleanOperator ) : bool	Converts a single Expression to SQL (mapping the columns as appropriate) and appends to the given string builder. Remember to wrap the SQL in parends if necessary.
GetGroupByValue ( ClassMapping mapping, IDataReader reader, int number, AbstractGroupExpression expression ) : object	Reads a single "group by" field value from the data reader, coerces it to the correct type if necessary/possible, and returns it.
GetValueStrings ( string table, object>.IDictionary propValues ) : string>.IDictionary
OrderListToSql ( StringBuilder orderClauseToAddTo, DaoCriteria crit, ClassMapping mapping ) : void	Converts the list of SortOrders from this criteria into SQL, and appends to the given string builder.
PreProcessPropertyValues ( string table, object>.IDictionary propValues ) : void	Override this method if you need to do any work to convert values from the object's properties into normal SQL parameters. Default implementation does nothing. This is called prior to inserting or updating these values in the table.
ReadGroupByCount ( Hashtable parameters, IDataReader reader ) : void	Reads the results from the data reader produced by the group by query, creates the GroupCountResults, and returns them in the parameters collection.

Method Details

AppendParameter() public method

Since it is implementation-dependent whether to use the sql parameters collection or not, this method should be implemented in each implementation.

public AppendParameter ( SqlDaQuery queryToAddTo, object value, Type columnType ) : void
queryToAddTo	SqlDaQuery	Query to add the parameter to.
value	object	Actual value that we need to append to our SQL.
columnType	System.Type	Type of data actually stored in the DB column. For example, /// Enums may be stored as strings. May be null if no type cast /// is necessary.
return	void

BoolTypeToString() protected static method

Returns a nicely spaced AND or OR depending on the boolean type.

protected static BoolTypeToString ( BooleanOperator boolType ) : string
boolType	BooleanOperator
return	string

CreateQuery() public method

Builds the query based on a serializable criteria.

public CreateQuery ( ClassMapping mapping, DaoCriteria crit ) : IDaQuery
mapping	ClassMapping	The mapping of the table for which to build the query string.
crit	Azavea.Open.DAO.Criteria.DaoCriteria	The criteria to use for "where" comparisons.
return	IDaQuery

CreateQuery() public method

Builds the query based on a string, such as a sql string. NOTE: Not all FastDaoLayers are required to support this, if it is not supported a NotSupportedException will be thrown. TODO: This will be removed when FastDAO.QueryFor and IterateOverObjects that take strings are removed.

public CreateQuery ( string queryStr, IEnumerable queryParams, ClassMapping mapping ) : IDaQuery
queryStr	string	The sql statement to execute that is expected to return a large /// number of rows.
queryParams	IEnumerable	The parameters for the sql statement. If there are none, this /// can be null.
mapping	ClassMapping	The mapping of the table for which to build the query string.
return	IDaQuery

Delete() public method

public Delete ( ITransaction transaction, ClassMapping mapping, DaoCriteria crit ) : int
transaction	ITransaction
mapping	ClassMapping
crit	Azavea.Open.DAO.Criteria.DaoCriteria
return	int

DisposeOfQuery() public method

Should be called when you're done with the query. Allows us to cache the objects for reuse.

public DisposeOfQuery ( IDaQuery query ) : void
query	IDaQuery	Query you're done using.
return	void

ExecuteQuery() public method

public ExecuteQuery ( ITransaction transaction, ClassMapping mapping, IDaQuery query, DataReaderDelegate invokeMe, Hashtable parameters ) : void
transaction	ITransaction
mapping	ClassMapping
query	IDaQuery
invokeMe	DataReaderDelegate
parameters	System.Collections.Hashtable
return	void

ExpressionListToQuery() protected method

Converts the list of expressions from this criteria into SQL, and appends to the given string builder.

protected ExpressionListToQuery ( SqlDaQuery queryToAddTo, BooleanOperator boolType, IEnumerable expressions, ClassMapping mapping, string colPrefix ) : void
queryToAddTo	SqlDaQuery	Query we're adding the expression to.
boolType	BooleanOperator	Whether to AND or OR the expressions together.
expressions	IEnumerable	The expressions to add to the query.
mapping	ClassMapping	Class mapping for the class we're dealing with.
colPrefix	string	What to prefix column names with, I.E. "Table." for "Table.Column". /// May be null if no prefix is desired. May be something other than /// the table name if the tables are being aliased.
return	void

ExpressionToQuery() protected method

Converts a single Expression to SQL (mapping the columns as appropriate) and appends to the given string builder. Remember to wrap the SQL in parends if necessary.

protected ExpressionToQuery ( SqlDaQuery queryToAddTo, IExpression expr, ClassMapping mapping, string colPrefix, string booleanOperator ) : bool
queryToAddTo	SqlDaQuery	Query we're adding the expression to.
expr	IExpression	The expression. NOTE: It should NOT be null. This method does not check.
mapping	ClassMapping	Class mapping for the class we're dealing with.
colPrefix	string	What to prefix column names with, I.E. "Table." for "Table.Column". /// May be null if no prefix is desired. May be something other than /// the table name if the tables are being aliased.
booleanOperator	string	The boolean operator (AND or OR) to insert before /// this expression. Blank ("") if we don't need one.
return	bool

ExpressionsToQuery() public method

Takes a DaoCriteria, converts it to a " WHERE ..." chunk of SQL. The SQL will begin with a space if non-empty.

public ExpressionsToQuery ( SqlDaQuery queryToAddTo, DaoCriteria crit, ClassMapping mapping ) : void
queryToAddTo	SqlDaQuery	Query we're adding the expression to.
crit	Azavea.Open.DAO.Criteria.DaoCriteria	Serializable critera to get the expressions from.
mapping	ClassMapping	Class mapping for the class we're dealing with.
return	void

GetCount() public method

public GetCount ( ITransaction transaction, ClassMapping mapping, DaoCriteria crit, ICollection groupExpressions ) : List
transaction	ITransaction
mapping	ClassMapping
crit	Azavea.Open.DAO.Criteria.DaoCriteria
groupExpressions	ICollection
return	List

GetCount() public method

public GetCount ( ITransaction transaction, ClassMapping mapping, DaoCriteria crit ) : int
transaction	ITransaction
mapping	ClassMapping
crit	Azavea.Open.DAO.Criteria.DaoCriteria
return	int

GetGroupByValue() protected method

Reads a single "group by" field value from the data reader, coerces it to the correct type if necessary/possible, and returns it.

protected GetGroupByValue ( ClassMapping mapping, IDataReader reader, int number, AbstractGroupExpression expression ) : object
mapping	ClassMapping	Mapping of class fields / names / etc.
reader	IDataReader	Data reader to get the value from.
number	int	Which group by field is this (0th, 1st, etc).
expression	Azavea.Open.DAO.Criteria.Grouping.AbstractGroupExpression	The group by expression we're reading the value for.
return	object

GetLastAutoGeneratedId() public method

public GetLastAutoGeneratedId ( ITransaction transaction, ClassMapping mapping, string idCol ) : object
transaction	ITransaction
mapping	ClassMapping
idCol	string
return	object

GetNextSequenceValue() public method

public GetNextSequenceValue ( ITransaction transaction, string sequenceName ) : int
transaction	ITransaction
sequenceName	string
return	int

GetValueStrings() protected method

protected GetValueStrings ( string table, object>.IDictionary propValues ) : string>.IDictionary
table	string
propValues	object>.IDictionary
return	string>.IDictionary

GroupBysToEndOfQuery() public method

Adds the group by fields to the end of the query, including the keyword "GROUP BY" if necessary.

public GroupBysToEndOfQuery ( SqlDaQuery query, ICollection groupExpressions, ClassMapping mapping ) : void
query	SqlDaQuery	Query to append to.
groupExpressions	ICollection	Group by expressions.
mapping	ClassMapping	Class mapping for the class we're dealing with.
return	void

GroupBysToStartOfQuery() public method

Adds the group by fields to the "column" list ("column" since they may not all be columns) in the beginning of the select (I.E. "SELECT COUNT(*), Field1, Field2, etc).

public GroupBysToStartOfQuery ( SqlDaQuery query, ICollection groupExpressions, ClassMapping mapping ) : void
query	SqlDaQuery	Query to append to.
groupExpressions	ICollection	Group by expressions.
mapping	ClassMapping	Class mapping for the class we're dealing with.
return	void

Insert() public method

public Insert ( ITransaction transaction, ClassMapping mapping, object>.IDictionary propValues ) : int
transaction	ITransaction
mapping	ClassMapping
propValues	object>.IDictionary
return	int

OrderListToSql() protected method

Converts the list of SortOrders from this criteria into SQL, and appends to the given string builder.

protected OrderListToSql ( StringBuilder orderClauseToAddTo, DaoCriteria crit, ClassMapping mapping ) : void
orderClauseToAddTo	StringBuilder
crit	Azavea.Open.DAO.Criteria.DaoCriteria
mapping	ClassMapping
return	void

OrdersToQuery() public method

Takes a DaoCriteria, converts it to an " ORDER BY ..." chunk of SQL. The SQL will begin with a space if non-empty.

public OrdersToQuery ( SqlDaQuery queryToAddTo, DaoCriteria crit, ClassMapping mapping ) : void
queryToAddTo	SqlDaQuery
crit	Azavea.Open.DAO.Criteria.DaoCriteria
mapping	ClassMapping
return	void

PreProcessPropertyValues() protected method

Override this method if you need to do any work to convert values from the object's properties into normal SQL parameters. Default implementation does nothing. This is called prior to inserting or updating these values in the table.

protected PreProcessPropertyValues ( string table, object>.IDictionary propValues ) : void
table	string	The table these values will be inserted or updated into.
propValues	object>.IDictionary	A dictionary of "column"/value pairs for the object to insert or update.
return	void

ReadGroupByCount() protected method

Reads the results from the data reader produced by the group by query, creates the GroupCountResults, and returns them in the parameters collection.

protected ReadGroupByCount ( Hashtable parameters, IDataReader reader ) : void
parameters	System.Collections.Hashtable	Input and output parameters for the method.
reader	IDataReader	Data reader to read from.
return	void

ReadScalarValue() public method

public ReadScalarValue ( Hashtable parameters, IDataReader reader ) : void
parameters	System.Collections.Hashtable
reader	IDataReader
return	void

SetFullyQualifyColumnNames() public method

Sometimes it is not desirable to fully qualify column names, even for a database, e.g. when doing a query that is a join of foreign tables. This allows this value to be set in those instances.

public SetFullyQualifyColumnNames ( bool fullyQualifyColumnNames ) : void
fullyQualifyColumnNames	bool	Whether or not to fully qualify column names.
return	void

SqlDaLayer() public method

Instantiates the data access layer with the connection descriptor for the DB.

public SqlDaLayer ( AbstractSqlConnectionDescriptor connDesc, bool supportsNumRecords ) : System
connDesc	AbstractSqlConnectionDescriptor	The connection descriptor that is being used by this FastDaoLayer.
supportsNumRecords	bool	If true, methods that return numbers of records affected will be /// returning accurate numbers. If false, they will probably return /// FastDAO.UNKNOWN_NUM_ROWS.
return	System

Truncate() public method

public Truncate ( ClassMapping classMap ) : void
classMap	ClassMapping
return	void

Update() public method

public Update ( ITransaction transaction, ClassMapping mapping, DaoCriteria crit, object>.IDictionary propValues ) : int
transaction	ITransaction
mapping	ClassMapping
crit	Azavea.Open.DAO.Criteria.DaoCriteria
propValues	object>.IDictionary
return	int

Property Details

_connDesc protected property

From this class on down, we can always treat it as a SqlConnectionDescriptor.

protected AbstractSqlConnectionDescriptor,Azavea.Open.DAO.SQL _connDesc
return	AbstractSqlConnectionDescriptor

_fullyQualifyColumnNames protected property

Tells us whether or not to fully qualify column names ("Table.Column") in the SQL. This is generally harmless for actual databases, but may not always be desirable.

protected Boolean,System _fullyQualifyColumnNames
return	System.Boolean

_sqlQueryCache protected static property

Since there are different types of query for different dao layers, each keeps their own cache.

protected static ClearingCache _sqlQueryCache
return	ClearingCache