/// <summary>
/// Search for customers according to search criteria
/// </summary>
/// <param name="queries">The criteria to search for.</param>
/// <returns>The collection of customers that match the criteria.</returns>
public virtual Data.Customers[] SearchForCustomers(string[] queries)
{
// Create the expression helper.
DataTypeConversion dataTypeConversion = new DataTypeConversion(_connectionDataType);
SqlStatementConstructor statement = new SqlStatementConstructor(dataTypeConversion);
// Create the query list.
Data.Customers[] customers = null;
List <QueryModel> queryModels = new List <QueryModel>();
// For each query.
foreach (string query in queries)
{
// Add the serach model.
SearchQueryModel[] searchModel = new SearchQueryModel[]
{
new SearchQueryModel()
{
ColumnName = "CustomerID",
Operand = Linq.ExpressionOperandType.Like,
Value = query,
ValueType = typeof(string)
},
new SearchQueryModel()
{
ColumnName = "CompanyName",
Operand = Linq.ExpressionOperandType.Like,
Value = query,
ValueType = typeof(string)
},
new SearchQueryModel()
{
ColumnName = "Address",
Operand = Linq.ExpressionOperandType.Like,
Value = query,
ValueType = typeof(string)
},
new SearchQueryModel()
{
ColumnName = "Suburb",
Operand = Linq.ExpressionOperandType.Like,
Value = query,
ValueType = typeof(string)
},
new SearchQueryModel()
{
ColumnName = "PostCode",
Operand = Linq.ExpressionOperandType.Like,
Value = query,
ValueType = typeof(string)
},
new SearchQueryModel()
{
ColumnName = "EmailAddress",
Operand = Linq.ExpressionOperandType.Like,
Value = query,
ValueType = typeof(string)
},
new SearchQueryModel()
{
ColumnName = "ABN",
Operand = Linq.ExpressionOperandType.Like,
Value = query,
ValueType = typeof(string)
},
};
// Add the query model item.
QueryModel model = new QueryModel()
{
Queries = searchModel, Operand = Linq.ExpressionOperandType.OrElse
};
queryModels.Add(model);
}
// Create the company expression.
Expression <Nequeo.Threading.FunctionHandler <bool, Data.Customers> > predicate =
statement.CreateLambdaExpressionEx <Data.Customers>(queryModels.ToArray(), Linq.ExpressionOperandType.OrElse);
// Execute the query.
customers = Select.SelectIQueryableItems(predicate).ToArray();
// Return the list found.
return(customers);
}
/// <summary>
/// Executes the query.
/// </summary>
/// <param name="dataSet">The data set to return containing the data.</param>
/// <param name="tables">The tables names to add.</param>
/// <param name="queryText">The query text to execute.</param>
/// <param name="commandType">The command type.</param>
/// <param name="connectionString">The connection string to use.</param>
/// <param name="connectionDataType">The connection data type.</param>
/// <param name="values">The collection of sql parameters to include.</param>
/// <returns>The sql command containing any return values.</returns>
public DbCommand ExecuteQuery(ref System.Data.DataSet dataSet, string[] tables, string queryText,
CommandType commandType, string connectionString, ConnectionContext.ConnectionDataType connectionDataType,
params DbParameter[] values)
{
// Initial connection objects.
DbCommand dbCommand = null;
OleDbConnection oleDbConnection = null;
IDataReader dataReader = null;
try
{
// Create a new connection.
using (oleDbConnection = new OleDbConnection(connectionString))
{
// Open the connection.
oleDbConnection.Open();
// Create the command and assign any parameters.
dbCommand = new OleDbCommand(DataTypeConversion.GetSqlConversionDataTypeNoContainer(
connectionDataType, queryText), oleDbConnection);
dbCommand.CommandType = commandType;
if (values != null)
{
foreach (OleDbParameter sqlParameter in values)
{
dbCommand.Parameters.Add(sqlParameter);
}
}
// Load the data into the table.
using (dataReader = dbCommand.ExecuteReader())
{
dataSet = new System.Data.DataSet();
dataSet.EnforceConstraints = false;
dataSet.Load(dataReader, LoadOption.OverwriteChanges, tables);
dataReader.Close();
}
// Close the database connection.
oleDbConnection.Close();
}
// Return the sql command, including
// any parameters that have been
// marked as output direction.
return(dbCommand);
}
catch (Exception ex)
{
// Throw a general exception.
throw new Exception(ex.Message, ex.InnerException);
}
finally
{
if (dataReader != null)
{
dataReader.Close();
}
if (oleDbConnection != null)
{
oleDbConnection.Close();
}
}
}
/// <summary>
/// Executes the command.
/// </summary>
/// <param name="dbCommand">The current sql command.</param>
/// <param name="commandText">The command text to execute.</param>
/// <param name="commandType">The command type.</param>
/// <param name="connectionString">The connection string to use.</param>
/// <param name="connectionDataType">The connection data type.</param>
/// <param name="values">The collection of sql parameters to include.</param>
/// <returns>-1 if command execution failed.</returns>
public Int32 ExecuteCommand(ref DbCommand dbCommand, string commandText,
CommandType commandType, string connectionString, ConnectionContext.ConnectionDataType connectionDataType,
params DbParameter[] values)
{
// Initial connection objects.
dbCommand = null;
Int32 returnValue = -1;
OleDbConnection oleDbConnection = null;
OleDbTransaction oleDbTransaction = null;
try
{
// Create a new connection.
using (oleDbConnection = new OleDbConnection(connectionString))
{
// Open the connection.
oleDbConnection.Open();
// Start a new transaction.
oleDbTransaction = oleDbConnection.BeginTransaction();
// Create the command and assign any parameters.
dbCommand = new OleDbCommand(DataTypeConversion.GetSqlConversionDataTypeNoContainer(
connectionDataType, commandText), oleDbConnection);
dbCommand.CommandType = commandType;
dbCommand.Transaction = oleDbTransaction;
if (values != null)
{
foreach (OleDbParameter sqlParameter in values)
{
dbCommand.Parameters.Add(sqlParameter);
}
}
// Execute the command.
returnValue = dbCommand.ExecuteNonQuery();
// Commit the transaction.
oleDbTransaction.Commit();
// Close the database connection.
oleDbConnection.Close();
}
// Return true.
return(returnValue);
}
catch (Exception ex)
{
try
{
// Attempt to roll back the transaction.
if (oleDbTransaction != null)
{
oleDbTransaction.Rollback();
}
}
catch { }
// Throw a general exception.
throw new Exception(ex.Message, ex.InnerException);
}
finally
{
if (oleDbConnection != null)
{
oleDbConnection.Close();
}
}
}
/// <summary>
/// Executes the query.
/// </summary>
/// <param name="dataTable">The data table to return containing the data.</param>
/// <param name="queryText">The query text to execute.</param>
/// <param name="commandType">The command type.</param>
/// <param name="connectionString">The connection string to use.</param>
/// <param name="getSchemaTable">Get the table schema from the database and then load the data. Used when
/// returning data from the database for a particilar table.</param>
/// <param name="connectionDataType">The connection data type.</param>
/// <param name="values">The collection of sql parameters to include.</param>
/// <returns>The sql command containing any return values.</returns>
public DbCommand ExecuteQuery(ref DataTable dataTable, string queryText, CommandType commandType,
string connectionString, bool getSchemaTable, ConnectionContext.ConnectionDataType connectionDataType,
params DbParameter[] values)
{
// Initial connection objects.
DbCommand dbCommand = null;
OleDbConnection oleDbConnection = null;
IDataReader dataReader = null;
try
{
// Create a new connection.
using (oleDbConnection = new OleDbConnection(connectionString))
{
// Open the connection.
oleDbConnection.Open();
// Create the command and assign any parameters.
dbCommand = new OleDbCommand(DataTypeConversion.GetSqlConversionDataTypeNoContainer(
connectionDataType, queryText), oleDbConnection);
dbCommand.CommandType = commandType;
if (values != null)
{
foreach (OleDbParameter sqlParameter in values)
{
dbCommand.Parameters.Add(sqlParameter);
}
}
// Load the data into the table.
using (dataReader = dbCommand.ExecuteReader())
{
// Get the schema from the data because the
// table has not predefined schema
if (getSchemaTable)
{
// Load the table after the schema is
// returned.
dataTable = dataReader.GetSchemaTable();
dataTable = new DataTable();
System.Data.DataSet localDataSet = new System.Data.DataSet();
localDataSet.EnforceConstraints = false;
localDataSet.Tables.Add(dataTable);
dataTable.Load(dataReader);
}
else
{
// Load the data into a table schema.
// Load the data into a table schema.
System.Data.DataSet localDataSet = new System.Data.DataSet();
localDataSet.EnforceConstraints = false;
localDataSet.Tables.Add(dataTable);
dataTable.Load(dataReader);
}
dataReader.Close();
}
// Close the database connection.
oleDbConnection.Close();
}
// Return the sql command, including
// any parameters that have been
// marked as output direction.
return(dbCommand);
}
catch (Exception ex)
{
// Throw a general exception.
throw new Exception(ex.Message, ex.InnerException);
}
finally
{
if (dataReader != null)
{
dataReader.Close();
}
if (oleDbConnection != null)
{
oleDbConnection.Close();
}
}
}
/// <summary>
/// Gets the list of oledb parameters.
/// </summary>
/// <param name="functionParameters">The function parameters created.</param>
/// <param name="functionValues">The function values created.</param>
/// <param name="connectionDataType">The connection data type.</param>
/// <param name="methodInfo">The current method information.</param>
/// <param name="parameters">The parameter collection.</param>
/// <returns>The list of parameters.</returns>
public DbParameter[] GetParameters(ref string functionParameters, ref string functionValues,
ConnectionContext.ConnectionDataType connectionDataType, MethodInfo methodInfo, params Object[] parameters)
{
int i = -1;
long length = -1;
string dbType = null;
bool isNullable = true;
string parameterName = null;
System.Data.ParameterDirection parameterDirection = ParameterDirection.Input;
// Create a new instance of the oracle parameter collection.
string functionParameterNames = string.Empty;
string functionValueNames = string.Empty;
List <System.Data.OleDb.OleDbParameter> oledbParameters = new List <OleDbParameter>();
DataTypeConversion dataTypeConversion = new DataTypeConversion(connectionDataType);
// For each parameter within the method.
foreach (ParameterInfo parameter in methodInfo.GetParameters())
{
// For each attribute for the parameter.
foreach (object attribute in parameter.GetCustomAttributes(true))
{
// If the attribute is the
// function parameter column attribute.
if (attribute is Nequeo.Data.Custom.FunctionParameterAttribute)
{
// Increment the parameter count.
i++;
// Cast the current attribute.
Nequeo.Data.Custom.FunctionParameterAttribute att =
(Nequeo.Data.Custom.FunctionParameterAttribute)attribute;
dbType = att.DbType;
length = att.Length;
parameterName = att.Name;
isNullable = att.IsNullable;
parameterDirection = att.ParameterDirection;
// Add each parameter to the collection.
oledbParameters.Add(new System.Data.OleDb.OleDbParameter(
parameterName, Nequeo.Data.OleDb.ClientDataType.GetOleDbDbType(dbType), Convert.ToInt32(length),
parameterDirection, isNullable, ((Byte)(0)), ((Byte)(0)), "", System.Data.DataRowVersion.Current, parameters[i]));
// If the parameter is an input type
// then add the parameter to the list.
if (parameterDirection == ParameterDirection.Input || parameterDirection == ParameterDirection.InputOutput)
{
functionParameterNames += parameterName + ", ";
functionValueNames += dataTypeConversion.GetSqlStringValue(parameters[i].GetType(), parameters[i]) + ", ";
}
}
}
}
// Get the parameters for the function
functionParameters = functionParameterNames.TrimEnd(' ', ',');
functionValues = functionValueNames.TrimEnd(' ', ',');
// Return the oledb parameters.
return(oledbParameters.ToArray());
}
/// <summary>
/// Executes the query.
/// </summary>
/// <typeparam name="TypeDataTable">The current data table type.</typeparam>
/// <param name="dataTable">The data table to return containing the data.</param>
/// <param name="queryText">The query text to execute.</param>
/// <param name="commandType">The command type.</param>
/// <param name="connectionString">The connection string to use.</param>
/// <param name="values">The collection of sql parameters to include.</param>
/// <returns>The sql command containing any return values.</returns>
public DbCommand ExecuteQuery <TypeDataTable>(ref TypeDataTable dataTable, string queryText,
CommandType commandType, string connectionString, params DbParameter[] values)
where TypeDataTable : System.Data.DataTable, new()
{
dataTable = new TypeDataTable();
// Initial connection objects.
DbCommand dbCommand = null;
OracleClient.OracleConnection orlConnection = null;
IDataReader dataReader = null;
try
{
// Create a new connection.
using (orlConnection = new OracleClient.OracleConnection(connectionString))
{
// Open the connection.
orlConnection.Open();
// Create the command and assign any parameters.
dbCommand = new OracleClient.OracleCommand(DataTypeConversion.GetSqlConversionDataTypeNoContainer(
ConnectionContext.ConnectionDataType.OracleDataType, queryText), orlConnection);
dbCommand.CommandType = commandType;
if (values != null)
{
foreach (OracleClient.OracleParameter sqlParameter in values)
{
dbCommand.Parameters.Add(sqlParameter);
}
}
// Load the data into the table.
using (dataReader = dbCommand.ExecuteReader())
{
System.Data.DataSet localDataSet = new System.Data.DataSet();
localDataSet.EnforceConstraints = false;
localDataSet.Tables.Add(dataTable);
dataTable.Load(dataReader);
dataReader.Close();
}
// Close the database connection.
orlConnection.Close();
}
// Return the sql command, including
// any parameters that have been
// marked as output direction.
return(dbCommand);
}
catch (Exception ex)
{
// Throw a general exception.
throw new Exception(ex.Message, ex.InnerException);
}
finally
{
if (dataReader != null)
{
dataReader.Close();
}
if (orlConnection != null)
{
orlConnection.Close();
}
}
}
