}//end Delete
#endregion
#region Find Functions
#region Parsed Find Methods
/// <summary>
/// Returns rows meeting the whereClause condition from the DataSource.
/// </summary>
/// <param name="transactionManager"><see cref="TransactionManager"/> object</param>
/// <param name="whereClause">Specifies the condition for the rows returned by a query (Name='John Doe', Name='John Doe' AND Id='1', Name='John Doe' OR Id='1').</param>
/// <param name="start">Row number at which to start reading.</param>
/// <param name="pageLength">Number of rows to return.</param>
/// <param name="count">out. The number of rows that match this query.</param>
/// <remarks>Operators must be capitalized (OR, AND).</remarks>
/// <returns>Returns a typed collection of ClinicDoctor.Entities.RosterType objects.</returns>
public override TList<RosterType> Find(TransactionManager transactionManager, string whereClause, int start, int pageLength, out int count)
{
count = -1;
if (whereClause.IndexOf(";") > -1)
return new TList<RosterType>();
SqlDatabase database = new SqlDatabase(this._connectionString);
DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.RosterType_Find", _useStoredProcedure);
bool searchUsingOR = false;
if (whereClause.IndexOf(" OR ") > 0) // did they want to do "a=b OR c=d OR..."?
searchUsingOR = true;
database.AddInParameter(commandWrapper, "@SearchUsingOR", DbType.Boolean, searchUsingOR);
database.AddInParameter(commandWrapper, "@Id", DbType.Int64, DBNull.Value);
database.AddInParameter(commandWrapper, "@Title", DbType.String, DBNull.Value);
database.AddInParameter(commandWrapper, "@IsBooked", DbType.Boolean, DBNull.Value);
database.AddInParameter(commandWrapper, "@ColorCode", DbType.String, DBNull.Value);
database.AddInParameter(commandWrapper, "@Note", DbType.String, DBNull.Value);
database.AddInParameter(commandWrapper, "@IsDisabled", DbType.Boolean, DBNull.Value);
database.AddInParameter(commandWrapper, "@CreateUser", DbType.String, DBNull.Value);
database.AddInParameter(commandWrapper, "@CreateDate", DbType.DateTime, DBNull.Value);
database.AddInParameter(commandWrapper, "@UpdateUser", DbType.String, DBNull.Value);
database.AddInParameter(commandWrapper, "@UpdateDate", DbType.DateTime, DBNull.Value);
// replace all instances of 'AND' and 'OR' because we already set searchUsingOR
whereClause = whereClause.Replace(" AND ", "|").Replace(" OR ", "|") ;
string[] clauses = whereClause.ToLower().Split('|');
// Here's what's going on below: Find a field, then to get the value we
// drop the field name from the front, trim spaces, drop the '=' sign,
// trim more spaces, and drop any outer single quotes.
// Now handles the case when two fields start off the same way - like "Friendly='Yes' AND Friend='john'"
char[] equalSign = {'='};
char[] singleQuote = {'\''};
foreach (string clause in clauses)
{
if (clause.Trim().StartsWith("id ") || clause.Trim().StartsWith("id="))
{
database.SetParameterValue(commandWrapper, "@Id",
clause.Trim().Remove(0,2).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("title ") || clause.Trim().StartsWith("title="))
{
database.SetParameterValue(commandWrapper, "@Title",
clause.Trim().Remove(0,5).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("isbooked ") || clause.Trim().StartsWith("isbooked="))
{
database.SetParameterValue(commandWrapper, "@IsBooked",
clause.Trim().Remove(0,8).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("colorcode ") || clause.Trim().StartsWith("colorcode="))
{
database.SetParameterValue(commandWrapper, "@ColorCode",
clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("note ") || clause.Trim().StartsWith("note="))
{
database.SetParameterValue(commandWrapper, "@Note",
clause.Trim().Remove(0,4).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("isdisabled ") || clause.Trim().StartsWith("isdisabled="))
{
database.SetParameterValue(commandWrapper, "@IsDisabled",
clause.Trim().Remove(0,10).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("createuser ") || clause.Trim().StartsWith("createuser="******"@CreateUser",
clause.Trim().Remove(0,10).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("createdate ") || clause.Trim().StartsWith("createdate="))
{
database.SetParameterValue(commandWrapper, "@CreateDate",
clause.Trim().Remove(0,10).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("updateuser ") || clause.Trim().StartsWith("updateuser="******"@UpdateUser",
clause.Trim().Remove(0,10).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("updatedate ") || clause.Trim().StartsWith("updatedate="))
{
database.SetParameterValue(commandWrapper, "@UpdateDate",
clause.Trim().Remove(0,10).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
throw new ArgumentException("Unable to use this part of the where clause in this version of Find: " + clause);
}
IDataReader reader = null;
//Create Collection
TList<RosterType> rows = new TList<RosterType>();
try
{
//Provider Data Requesting Command Event
OnDataRequesting(new CommandEventArgs(commandWrapper, "Find", rows));
if (transactionManager != null)
{
reader = Utility.ExecuteReader(transactionManager, commandWrapper);
}
else
{
reader = Utility.ExecuteReader(database, commandWrapper);
}
Fill(reader, rows, start, pageLength);
if(reader.NextResult())
{
if(reader.Read())
{
count = reader.GetInt32(0);
}
}
//Provider Data Requested Command Event
OnDataRequested(new CommandEventArgs(commandWrapper, "Find", rows));
}
finally
{
if (reader != null)
reader.Close();
commandWrapper = null;
}
return rows;
}
}//end Delete
#endregion
#region Find Functions
#region Parsed Find Methods
/// <summary>
/// Returns rows meeting the whereClause condition from the DataSource.
/// </summary>
/// <param name="transactionManager"><see cref="TransactionManager"/> object</param>
/// <param name="whereClause">Specifies the condition for the rows returned by a query (Name='John Doe', Name='John Doe' AND Id='1', Name='John Doe' OR Id='1').</param>
/// <param name="start">Row number at which to start reading.</param>
/// <param name="pageLength">Number of rows to return.</param>
/// <param name="count">out. The number of rows that match this query.</param>
/// <remarks>Operators must be capitalized (OR, AND).</remarks>
/// <returns>Returns a typed collection of Nettiers.AdventureWorks.Entities.TransactionHistory objects.</returns>
public override TList<TransactionHistory> Find(TransactionManager transactionManager, string whereClause, int start, int pageLength, out int count)
{
count = -1;
if (whereClause.IndexOf(";") > -1)
return new TList<TransactionHistory>();
SqlDatabase database = new SqlDatabase(this._connectionString);
DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Production.usp_adwTiers_TransactionHistory_Find", _useStoredProcedure);
bool searchUsingOR = false;
if (whereClause.IndexOf(" OR ") > 0) // did they want to do "a=b OR c=d OR..."?
searchUsingOR = true;
database.AddInParameter(commandWrapper, "@SearchUsingOR", DbType.Boolean, searchUsingOR);
database.AddInParameter(commandWrapper, "@TransactionId", DbType.Int32, DBNull.Value);
database.AddInParameter(commandWrapper, "@ProductId", DbType.Int32, DBNull.Value);
database.AddInParameter(commandWrapper, "@ReferenceOrderId", DbType.Int32, DBNull.Value);
database.AddInParameter(commandWrapper, "@ReferenceOrderLineId", DbType.Int32, DBNull.Value);
database.AddInParameter(commandWrapper, "@TransactionDate", DbType.DateTime, DBNull.Value);
database.AddInParameter(commandWrapper, "@TransactionType", DbType.StringFixedLength, DBNull.Value);
database.AddInParameter(commandWrapper, "@Quantity", DbType.Int32, DBNull.Value);
database.AddInParameter(commandWrapper, "@ActualCost", DbType.Currency, DBNull.Value);
database.AddInParameter(commandWrapper, "@ModifiedDate", DbType.DateTime, DBNull.Value);
// replace all instances of 'AND' and 'OR' because we already set searchUsingOR
whereClause = whereClause.Replace(" AND ", "|").Replace(" OR ", "|") ;
string[] clauses = whereClause.ToLower().Split('|');
// Here's what's going on below: Find a field, then to get the value we
// drop the field name from the front, trim spaces, drop the '=' sign,
// trim more spaces, and drop any outer single quotes.
// Now handles the case when two fields start off the same way - like "Friendly='Yes' AND Friend='john'"
char[] equalSign = {'='};
char[] singleQuote = {'\''};
foreach (string clause in clauses)
{
if (clause.Trim().StartsWith("transactionid ") || clause.Trim().StartsWith("transactionid="))
{
database.SetParameterValue(commandWrapper, "@TransactionId",
clause.Trim().Remove(0,13).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("productid ") || clause.Trim().StartsWith("productid="))
{
database.SetParameterValue(commandWrapper, "@ProductId",
clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("referenceorderid ") || clause.Trim().StartsWith("referenceorderid="))
{
database.SetParameterValue(commandWrapper, "@ReferenceOrderId",
clause.Trim().Remove(0,16).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("referenceorderlineid ") || clause.Trim().StartsWith("referenceorderlineid="))
{
database.SetParameterValue(commandWrapper, "@ReferenceOrderLineId",
clause.Trim().Remove(0,20).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("transactiondate ") || clause.Trim().StartsWith("transactiondate="))
{
database.SetParameterValue(commandWrapper, "@TransactionDate",
clause.Trim().Remove(0,15).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("transactiontype ") || clause.Trim().StartsWith("transactiontype="))
{
database.SetParameterValue(commandWrapper, "@TransactionType",
clause.Trim().Remove(0,15).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("quantity ") || clause.Trim().StartsWith("quantity="))
{
database.SetParameterValue(commandWrapper, "@Quantity",
clause.Trim().Remove(0,8).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("actualcost ") || clause.Trim().StartsWith("actualcost="))
{
database.SetParameterValue(commandWrapper, "@ActualCost",
clause.Trim().Remove(0,10).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("modifieddate ") || clause.Trim().StartsWith("modifieddate="))
{
database.SetParameterValue(commandWrapper, "@ModifiedDate",
clause.Trim().Remove(0,12).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
throw new ArgumentException("Unable to use this part of the where clause in this version of Find: " + clause);
}
IDataReader reader = null;
//Create Collection
TList<TransactionHistory> rows = new TList<TransactionHistory>();
try
{
//Provider Data Requesting Command Event
OnDataRequesting(new CommandEventArgs(commandWrapper, "Find", rows));
if (transactionManager != null)
{
reader = Utility.ExecuteReader(transactionManager, commandWrapper);
}
else
{
reader = Utility.ExecuteReader(database, commandWrapper);
}
Fill(reader, rows, start, pageLength);
if(reader.NextResult())
{
if(reader.Read())
{
count = reader.GetInt32(0);
}
}
//Provider Data Requested Command Event
OnDataRequested(new CommandEventArgs(commandWrapper, "Find", rows));
}
finally
{
if (reader != null)
reader.Close();
commandWrapper = null;
}
return rows;
}
}//end Delete
#endregion
#region Find Functions
#region Parsed Find Methods
/// <summary>
/// Returns rows meeting the whereClause condition from the DataSource.
/// </summary>
/// <param name="transactionManager"><see cref="TransactionManager"/> object</param>
/// <param name="whereClause">Specifies the condition for the rows returned by a query (Name='John Doe', Name='John Doe' AND Id='1', Name='John Doe' OR Id='1').</param>
/// <param name="start">Row number at which to start reading.</param>
/// <param name="pageLength">Number of rows to return.</param>
/// <param name="count">out. The number of rows that match this query.</param>
/// <remarks>Operators must be capitalized (OR, AND).</remarks>
/// <returns>Returns a typed collection of Nettiers.AdventureWorks.Entities.StudentMasterIndex objects.</returns>
public override TList<StudentMasterIndex> Find(TransactionManager transactionManager, string whereClause, int start, int pageLength, out int count)
{
count = -1;
if (whereClause.IndexOf(";") > -1)
return new TList<StudentMasterIndex>();
SqlDatabase database = new SqlDatabase(this._connectionString);
DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Test.usp_adwTiers_STUDENT_MASTER_INDEX_Find", _useStoredProcedure);
bool searchUsingOR = false;
if (whereClause.IndexOf(" OR ") > 0) // did they want to do "a=b OR c=d OR..."?
searchUsingOR = true;
database.AddInParameter(commandWrapper, "@SearchUsingOR", DbType.Boolean, searchUsingOR);
database.AddInParameter(commandWrapper, "@StudentId", DbType.Int32, DBNull.Value);
database.AddInParameter(commandWrapper, "@EpassId", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@StudentUpn", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@SsabsaId", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@Surname", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@FirstName", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@OtherNames", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@KnownName", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@LegalName", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@Dob", DbType.DateTime, DBNull.Value);
database.AddInParameter(commandWrapper, "@Gender", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@IndigeneousStatus", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@Lbote", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@EslPhase", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@TribalGroup", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@SlpCreatedFlag", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@AddressLine1", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@AddressLine2", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@AddressLine3", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@AddressLine4", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@Suburb", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@Postcode", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@Phone1", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@Phone2", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@SourceSystem", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@PhoneticMatchId", DbType.Int32, DBNull.Value);
// replace all instances of 'AND' and 'OR' because we already set searchUsingOR
whereClause = whereClause.Replace(" AND ", "|").Replace(" OR ", "|") ;
string[] clauses = whereClause.ToLower().Split('|');
// Here's what's going on below: Find a field, then to get the value we
// drop the field name from the front, trim spaces, drop the '=' sign,
// trim more spaces, and drop any outer single quotes.
// Now handles the case when two fields start off the same way - like "Friendly='Yes' AND Friend='john'"
char[] equalSign = {'='};
char[] singleQuote = {'\''};
foreach (string clause in clauses)
{
if (clause.Trim().StartsWith("student_id ") || clause.Trim().StartsWith("student_id="))
{
database.SetParameterValue(commandWrapper, "@StudentId",
clause.Trim().Remove(0,10).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("epass_id ") || clause.Trim().StartsWith("epass_id="))
{
database.SetParameterValue(commandWrapper, "@EpassId",
clause.Trim().Remove(0,8).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("student_upn ") || clause.Trim().StartsWith("student_upn="))
{
database.SetParameterValue(commandWrapper, "@StudentUpn",
clause.Trim().Remove(0,11).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("ssabsa_id ") || clause.Trim().StartsWith("ssabsa_id="))
{
database.SetParameterValue(commandWrapper, "@SsabsaId",
clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("surname ") || clause.Trim().StartsWith("surname="))
{
database.SetParameterValue(commandWrapper, "@Surname",
clause.Trim().Remove(0,7).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("first_name ") || clause.Trim().StartsWith("first_name="))
{
database.SetParameterValue(commandWrapper, "@FirstName",
clause.Trim().Remove(0,10).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("other_names ") || clause.Trim().StartsWith("other_names="))
{
database.SetParameterValue(commandWrapper, "@OtherNames",
clause.Trim().Remove(0,11).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("known_name ") || clause.Trim().StartsWith("known_name="))
{
database.SetParameterValue(commandWrapper, "@KnownName",
clause.Trim().Remove(0,10).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("legal_name ") || clause.Trim().StartsWith("legal_name="))
{
database.SetParameterValue(commandWrapper, "@LegalName",
clause.Trim().Remove(0,10).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("dob ") || clause.Trim().StartsWith("dob="))
{
database.SetParameterValue(commandWrapper, "@Dob",
clause.Trim().Remove(0,3).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("gender ") || clause.Trim().StartsWith("gender="))
{
database.SetParameterValue(commandWrapper, "@Gender",
clause.Trim().Remove(0,6).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("indigeneous_status ") || clause.Trim().StartsWith("indigeneous_status="))
{
database.SetParameterValue(commandWrapper, "@IndigeneousStatus",
clause.Trim().Remove(0,18).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("lbote ") || clause.Trim().StartsWith("lbote="))
{
database.SetParameterValue(commandWrapper, "@Lbote",
clause.Trim().Remove(0,5).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("esl_phase ") || clause.Trim().StartsWith("esl_phase="))
{
database.SetParameterValue(commandWrapper, "@EslPhase",
clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("tribal_group ") || clause.Trim().StartsWith("tribal_group="))
{
database.SetParameterValue(commandWrapper, "@TribalGroup",
clause.Trim().Remove(0,12).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("slp_created_flag ") || clause.Trim().StartsWith("slp_created_flag="))
{
database.SetParameterValue(commandWrapper, "@SlpCreatedFlag",
clause.Trim().Remove(0,16).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("address_line_1 ") || clause.Trim().StartsWith("address_line_1="))
{
database.SetParameterValue(commandWrapper, "@AddressLine1",
clause.Trim().Remove(0,14).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("address_line_2 ") || clause.Trim().StartsWith("address_line_2="))
{
database.SetParameterValue(commandWrapper, "@AddressLine2",
clause.Trim().Remove(0,14).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("address_line_3 ") || clause.Trim().StartsWith("address_line_3="))
{
database.SetParameterValue(commandWrapper, "@AddressLine3",
clause.Trim().Remove(0,14).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("address_line_4 ") || clause.Trim().StartsWith("address_line_4="))
{
database.SetParameterValue(commandWrapper, "@AddressLine4",
clause.Trim().Remove(0,14).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("suburb ") || clause.Trim().StartsWith("suburb="))
{
database.SetParameterValue(commandWrapper, "@Suburb",
clause.Trim().Remove(0,6).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("postcode ") || clause.Trim().StartsWith("postcode="))
{
database.SetParameterValue(commandWrapper, "@Postcode",
clause.Trim().Remove(0,8).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("phone1 ") || clause.Trim().StartsWith("phone1="))
{
database.SetParameterValue(commandWrapper, "@Phone1",
clause.Trim().Remove(0,6).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("phone2 ") || clause.Trim().StartsWith("phone2="))
{
database.SetParameterValue(commandWrapper, "@Phone2",
clause.Trim().Remove(0,6).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("source_system ") || clause.Trim().StartsWith("source_system="))
{
database.SetParameterValue(commandWrapper, "@SourceSystem",
clause.Trim().Remove(0,13).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("phonetic_match_id ") || clause.Trim().StartsWith("phonetic_match_id="))
{
database.SetParameterValue(commandWrapper, "@PhoneticMatchId",
clause.Trim().Remove(0,17).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
throw new ArgumentException("Unable to use this part of the where clause in this version of Find: " + clause);
}
IDataReader reader = null;
//Create Collection
TList<StudentMasterIndex> rows = new TList<StudentMasterIndex>();
try
{
//Provider Data Requesting Command Event
OnDataRequesting(new CommandEventArgs(commandWrapper, "Find", rows));
if (transactionManager != null)
{
reader = Utility.ExecuteReader(transactionManager, commandWrapper);
}
else
{
reader = Utility.ExecuteReader(database, commandWrapper);
}
Fill(reader, rows, start, pageLength);
if(reader.NextResult())
{
if(reader.Read())
{
count = reader.GetInt32(0);
}
}
//Provider Data Requested Command Event
OnDataRequested(new CommandEventArgs(commandWrapper, "Find", rows));
}
finally
{
if (reader != null)
reader.Close();
commandWrapper = null;
}
return rows;
}
}//end Delete
#endregion
#region Find Functions
#region Parsed Find Methods
/// <summary>
/// Returns rows meeting the whereClause condition from the DataSource.
/// </summary>
/// <param name="transactionManager"><see cref="TransactionManager"/> object</param>
/// <param name="whereClause">Specifies the condition for the rows returned by a query (Name='John Doe', Name='John Doe' AND Id='1', Name='John Doe' OR Id='1').</param>
/// <param name="start">Row number at which to start reading.</param>
/// <param name="pageLength">Number of rows to return.</param>
/// <param name="count">out. The number of rows that match this query.</param>
/// <remarks>Operators must be capitalized (OR, AND).</remarks>
/// <returns>Returns a typed collection of PetShop.Business.Orders objects.</returns>
public override TList<Orders> Find(TransactionManager transactionManager, string whereClause, int start, int pageLength, out int count)
{
count = -1;
if (whereClause.IndexOf(";") > -1)
return new TList<Orders>();
SqlDatabase database = new SqlDatabase(this._connectionString);
DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Orders_Find", _useStoredProcedure);
bool searchUsingOR = false;
if (whereClause.IndexOf(" OR ") > 0) // did they want to do "a=b OR c=d OR..."?
searchUsingOR = true;
database.AddInParameter(commandWrapper, "@SearchUsingOR", DbType.Boolean, searchUsingOR);
database.AddInParameter(commandWrapper, "@OrderId", DbType.Int32, DBNull.Value);
database.AddInParameter(commandWrapper, "@UserId", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@OrderDate", DbType.DateTime, DBNull.Value);
database.AddInParameter(commandWrapper, "@ShipAddr1", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@ShipAddr2", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@ShipCity", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@ShipState", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@ShipZip", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@ShipCountry", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@BillAddr1", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@BillAddr2", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@BillCity", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@BillState", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@BillZip", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@BillCountry", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@Courier", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@TotalPrice", DbType.Decimal, DBNull.Value);
database.AddInParameter(commandWrapper, "@BillToFirstName", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@BillToLastName", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@ShipToFirstName", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@ShipToLastName", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@AuthorizationNumber", DbType.Int32, DBNull.Value);
database.AddInParameter(commandWrapper, "@Locale", DbType.AnsiString, DBNull.Value);
// replace all instances of 'AND' and 'OR' because we already set searchUsingOR
whereClause = whereClause.Replace(" AND ", "|").Replace(" OR ", "|") ;
string[] clauses = whereClause.ToLower().Split('|');
// Here's what's going on below: Find a field, then to get the value we
// drop the field name from the front, trim spaces, drop the '=' sign,
// trim more spaces, and drop any outer single quotes.
// Now handles the case when two fields start off the same way - like "Friendly='Yes' AND Friend='john'"
char[] equalSign = {'='};
char[] singleQuote = {'\''};
foreach (string clause in clauses)
{
if (clause.Trim().StartsWith("orderid ") || clause.Trim().StartsWith("orderid="))
{
database.SetParameterValue(commandWrapper, "@OrderId",
clause.Trim().Remove(0,7).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("userid ") || clause.Trim().StartsWith("userid="))
{
database.SetParameterValue(commandWrapper, "@UserId",
clause.Trim().Remove(0,6).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("orderdate ") || clause.Trim().StartsWith("orderdate="))
{
database.SetParameterValue(commandWrapper, "@OrderDate",
clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("shipaddr1 ") || clause.Trim().StartsWith("shipaddr1="))
{
database.SetParameterValue(commandWrapper, "@ShipAddr1",
clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("shipaddr2 ") || clause.Trim().StartsWith("shipaddr2="))
{
database.SetParameterValue(commandWrapper, "@ShipAddr2",
clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("shipcity ") || clause.Trim().StartsWith("shipcity="))
{
database.SetParameterValue(commandWrapper, "@ShipCity",
clause.Trim().Remove(0,8).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("shipstate ") || clause.Trim().StartsWith("shipstate="))
{
database.SetParameterValue(commandWrapper, "@ShipState",
clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("shipzip ") || clause.Trim().StartsWith("shipzip="))
{
database.SetParameterValue(commandWrapper, "@ShipZip",
clause.Trim().Remove(0,7).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("shipcountry ") || clause.Trim().StartsWith("shipcountry="))
{
database.SetParameterValue(commandWrapper, "@ShipCountry",
clause.Trim().Remove(0,11).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("billaddr1 ") || clause.Trim().StartsWith("billaddr1="))
{
database.SetParameterValue(commandWrapper, "@BillAddr1",
clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("billaddr2 ") || clause.Trim().StartsWith("billaddr2="))
{
database.SetParameterValue(commandWrapper, "@BillAddr2",
clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("billcity ") || clause.Trim().StartsWith("billcity="))
{
database.SetParameterValue(commandWrapper, "@BillCity",
clause.Trim().Remove(0,8).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("billstate ") || clause.Trim().StartsWith("billstate="))
{
database.SetParameterValue(commandWrapper, "@BillState",
clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("billzip ") || clause.Trim().StartsWith("billzip="))
{
database.SetParameterValue(commandWrapper, "@BillZip",
clause.Trim().Remove(0,7).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("billcountry ") || clause.Trim().StartsWith("billcountry="))
{
database.SetParameterValue(commandWrapper, "@BillCountry",
clause.Trim().Remove(0,11).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("courier ") || clause.Trim().StartsWith("courier="))
{
database.SetParameterValue(commandWrapper, "@Courier",
clause.Trim().Remove(0,7).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("totalprice ") || clause.Trim().StartsWith("totalprice="))
{
database.SetParameterValue(commandWrapper, "@TotalPrice",
clause.Trim().Remove(0,10).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("billtofirstname ") || clause.Trim().StartsWith("billtofirstname="))
{
database.SetParameterValue(commandWrapper, "@BillToFirstName",
clause.Trim().Remove(0,15).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("billtolastname ") || clause.Trim().StartsWith("billtolastname="))
{
database.SetParameterValue(commandWrapper, "@BillToLastName",
clause.Trim().Remove(0,14).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("shiptofirstname ") || clause.Trim().StartsWith("shiptofirstname="))
{
database.SetParameterValue(commandWrapper, "@ShipToFirstName",
clause.Trim().Remove(0,15).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("shiptolastname ") || clause.Trim().StartsWith("shiptolastname="))
{
database.SetParameterValue(commandWrapper, "@ShipToLastName",
clause.Trim().Remove(0,14).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("authorizationnumber ") || clause.Trim().StartsWith("authorizationnumber="))
{
database.SetParameterValue(commandWrapper, "@AuthorizationNumber",
clause.Trim().Remove(0,19).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("locale ") || clause.Trim().StartsWith("locale="))
{
database.SetParameterValue(commandWrapper, "@Locale",
clause.Trim().Remove(0,6).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
throw new ArgumentException("Unable to use this part of the where clause in this version of Find: " + clause);
}
IDataReader reader = null;
//Create Collection
TList<Orders> rows = new TList<Orders>();
try
{
//Provider Data Requesting Command Event
OnDataRequesting(new CommandEventArgs(commandWrapper, "Find", rows));
if (transactionManager != null)
{
reader = Utility.ExecuteReader(transactionManager, commandWrapper);
}
else
{
reader = Utility.ExecuteReader(database, commandWrapper);
}
Fill(reader, rows, start, pageLength);
if(reader.NextResult())
{
if(reader.Read())
{
count = reader.GetInt32(0);
}
}
//Provider Data Requested Command Event
OnDataRequested(new CommandEventArgs(commandWrapper, "Find", rows));
}
finally
{
if (reader != null)
reader.Close();
commandWrapper = null;
}
return rows;
}
}//end Delete
#endregion
#region Find Functions
/// <summary>
/// Returns rows meeting the whereclause condition from the DataSource.
/// </summary>
/// <param name="transactionManager"><see cref="TransactionManager"/> object</param>
/// <param name="whereClause">Specifies the condition for the rows returned by a query (Name='John Doe', Name='John Doe' AND Id='1', Name='John Doe' OR Id='1').</param>
/// <param name="start">Row number at which to start reading.</param>
/// <param name="pageLength">Number of rows to return.</param>
/// <param name="count">out. The number of rows that match this query.</param>
/// <remarks>Operators must be capitalized (OR, AND)</remarks>
/// <returns>Returns a typed collection of SmartMassEmail.Entities.EmailMessageDetail objects.</returns>
public override SmartMassEmail.Entities.TList<EmailMessageDetail> Find(TransactionManager transactionManager, string whereClause, int start, int pageLength, out int count)
{
count = -1;
if (whereClause.IndexOf(";") > -1)
return new SmartMassEmail.Entities.TList<EmailMessageDetail>();
SqlDatabase database = new SqlDatabase(this._connectionString);
DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.EmailMessageDetail_Find", _useStoredProcedure);
bool searchUsingOR = false;
if (whereClause.IndexOf("OR") > 0) // did they want to do "a=b OR c=d OR..."?
searchUsingOR = true;
database.AddInParameter(commandWrapper, "@SearchUsingOR", DbType.Boolean, searchUsingOR);
database.AddInParameter(commandWrapper, "@ID", DbType.Guid, DBNull.Value);
database.AddInParameter(commandWrapper, "@ChangeStamp", DbType.DateTime, DBNull.Value);
database.AddInParameter(commandWrapper, "@IsBinary", DbType.Int32, DBNull.Value);
database.AddInParameter(commandWrapper, "@Name", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@BinaryData", DbType.Binary, DBNull.Value);
database.AddInParameter(commandWrapper, "@StringData", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@EmailMessageID", DbType.Guid, DBNull.Value);
// replace all instances of 'AND' and 'OR' because we already set searchUsingOR
whereClause = whereClause.Replace("AND", "|").Replace("OR", "|") ;
string[] clauses = whereClause.ToLower().Split('|');
// Here's what's going on below: Find a field, then to get the value we
// drop the field name from the front, trim spaces, drop the '=' sign,
// trim more spaces, and drop any outer single quotes.
// Now handles the case when two fields start off the same way - like "Friendly='Yes' AND Friend='john'"
char[] equalSign = {'='};
char[] singleQuote = {'\''};
foreach (string clause in clauses)
{
if (clause.Trim().StartsWith("id ") || clause.Trim().StartsWith("id="))
{
database.SetParameterValue(commandWrapper, "@ID", new Guid(
clause.Replace("id","").Trim().TrimStart(equalSign).Trim().Trim(singleQuote)));
continue;
}
if (clause.Trim().StartsWith("changestamp ") || clause.Trim().StartsWith("changestamp="))
{
database.SetParameterValue(commandWrapper, "@ChangeStamp",
clause.Replace("changestamp","").Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("isbinary ") || clause.Trim().StartsWith("isbinary="))
{
database.SetParameterValue(commandWrapper, "@IsBinary",
clause.Replace("isbinary","").Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("name ") || clause.Trim().StartsWith("name="))
{
database.SetParameterValue(commandWrapper, "@Name",
clause.Replace("name","").Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("binarydata ") || clause.Trim().StartsWith("binarydata="))
{
database.SetParameterValue(commandWrapper, "@BinaryData",
clause.Replace("binarydata","").Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("stringdata ") || clause.Trim().StartsWith("stringdata="))
{
database.SetParameterValue(commandWrapper, "@StringData",
clause.Replace("stringdata","").Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("emailmessageid ") || clause.Trim().StartsWith("emailmessageid="))
{
database.SetParameterValue(commandWrapper, "@EmailMessageID", new Guid(
clause.Replace("emailmessageid","").Trim().TrimStart(equalSign).Trim().Trim(singleQuote)));
continue;
}
throw new ArgumentException("Unable to use this part of the where clause in this version of Find: " + clause);
}
IDataReader reader = null;
//Create Collection
SmartMassEmail.Entities.TList<EmailMessageDetail> rows = new SmartMassEmail.Entities.TList<EmailMessageDetail>();
try
{
if (transactionManager != null)
{
reader = Utility.ExecuteReader(transactionManager, commandWrapper);
}
else
{
reader = Utility.ExecuteReader(database, commandWrapper);
}
Fill(reader, rows, start, pageLength);
if(reader.NextResult())
{
if(reader.Read())
{
count = reader.GetInt32(0);
}
}
}
finally
{
if (reader != null)
reader.Close();
}
return rows;
}
/// <summary>
/// Returns rows meeting the whereClause condition from the DataSource.
/// </summary>
/// <param name="transactionManager"><see cref="TransactionManager"/> object</param>
/// <param name="whereClause">Specifies the condition for the rows returned by a query (Name='John Doe', Name='John Doe' AND Id='1', Name='John Doe' OR Id='1').</param>
/// <param name="start">Row number at which to start reading.</param>
/// <param name="pageLength">Number of rows to return.</param>
/// <param name="count">out. The number of rows that match this query.</param>
/// <remarks>Operators must be capitalized (OR, AND).</remarks>
/// <returns>Returns a typed collection of Northwind.Entities.Employees objects.</returns>
public override TList<Employees> Find(TransactionManager transactionManager, string whereClause, int start, int pageLength, out int count)
{
count = -1;
if (whereClause.IndexOf(";") > -1)
return new TList<Employees>();
SqlDatabase database = new SqlDatabase(this._connectionString);
DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.sp_nt_Employees_Find", _useStoredProcedure);
bool searchUsingOR = false;
if (whereClause.IndexOf(" OR ") > 0) // did they want to do "a=b OR c=d OR..."?
searchUsingOR = true;
database.AddInParameter(commandWrapper, "@SearchUsingOR", DbType.Boolean, searchUsingOR);
database.AddInParameter(commandWrapper, "@EmployeeId", DbType.Int32, DBNull.Value);
database.AddInParameter(commandWrapper, "@LastName", DbType.String, DBNull.Value);
database.AddInParameter(commandWrapper, "@FirstName", DbType.String, DBNull.Value);
database.AddInParameter(commandWrapper, "@Title", DbType.String, DBNull.Value);
database.AddInParameter(commandWrapper, "@TitleOfCourtesy", DbType.String, DBNull.Value);
database.AddInParameter(commandWrapper, "@BirthDate", DbType.DateTime, DBNull.Value);
database.AddInParameter(commandWrapper, "@HireDate", DbType.DateTime, DBNull.Value);
database.AddInParameter(commandWrapper, "@Address", DbType.String, DBNull.Value);
database.AddInParameter(commandWrapper, "@City", DbType.String, DBNull.Value);
database.AddInParameter(commandWrapper, "@Region", DbType.String, DBNull.Value);
database.AddInParameter(commandWrapper, "@PostalCode", DbType.String, DBNull.Value);
database.AddInParameter(commandWrapper, "@Country", DbType.String, DBNull.Value);
database.AddInParameter(commandWrapper, "@HomePhone", DbType.String, DBNull.Value);
database.AddInParameter(commandWrapper, "@Extension", DbType.String, DBNull.Value);
database.AddInParameter(commandWrapper, "@Photo", DbType.Binary, DBNull.Value);
database.AddInParameter(commandWrapper, "@Notes", DbType.String, DBNull.Value);
database.AddInParameter(commandWrapper, "@ReportsTo", DbType.Int32, DBNull.Value);
database.AddInParameter(commandWrapper, "@PhotoPath", DbType.String, DBNull.Value);
// replace all instances of 'AND' and 'OR' because we already set searchUsingOR
whereClause = whereClause.Replace(" AND ", "|").Replace(" OR ", "|") ;
string[] clauses = whereClause.ToLower().Split('|');
// Here's what's going on below: Find a field, then to get the value we
// drop the field name from the front, trim spaces, drop the '=' sign,
// trim more spaces, and drop any outer single quotes.
// Now handles the case when two fields start off the same way - like "Friendly='Yes' AND Friend='john'"
char[] equalSign = {'='};
char[] singleQuote = {'\''};
foreach (string clause in clauses)
{
if (clause.Trim().StartsWith("employeeid ") || clause.Trim().StartsWith("employeeid="))
{
database.SetParameterValue(commandWrapper, "@EmployeeId",
clause.Trim().Remove(0,10).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("lastname ") || clause.Trim().StartsWith("lastname="))
{
database.SetParameterValue(commandWrapper, "@LastName",
clause.Trim().Remove(0,8).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("firstname ") || clause.Trim().StartsWith("firstname="))
{
database.SetParameterValue(commandWrapper, "@FirstName",
clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("title ") || clause.Trim().StartsWith("title="))
{
database.SetParameterValue(commandWrapper, "@Title",
clause.Trim().Remove(0,5).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("titleofcourtesy ") || clause.Trim().StartsWith("titleofcourtesy="))
{
database.SetParameterValue(commandWrapper, "@TitleOfCourtesy",
clause.Trim().Remove(0,15).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("birthdate ") || clause.Trim().StartsWith("birthdate="))
{
database.SetParameterValue(commandWrapper, "@BirthDate",
clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("hiredate ") || clause.Trim().StartsWith("hiredate="))
{
database.SetParameterValue(commandWrapper, "@HireDate",
clause.Trim().Remove(0,8).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("address ") || clause.Trim().StartsWith("address="))
{
database.SetParameterValue(commandWrapper, "@Address",
clause.Trim().Remove(0,7).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("city ") || clause.Trim().StartsWith("city="))
{
database.SetParameterValue(commandWrapper, "@City",
clause.Trim().Remove(0,4).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("region ") || clause.Trim().StartsWith("region="))
{
database.SetParameterValue(commandWrapper, "@Region",
clause.Trim().Remove(0,6).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("postalcode ") || clause.Trim().StartsWith("postalcode="))
{
database.SetParameterValue(commandWrapper, "@PostalCode",
clause.Trim().Remove(0,10).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("country ") || clause.Trim().StartsWith("country="))
{
database.SetParameterValue(commandWrapper, "@Country",
clause.Trim().Remove(0,7).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("homephone ") || clause.Trim().StartsWith("homephone="))
{
database.SetParameterValue(commandWrapper, "@HomePhone",
clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("extension ") || clause.Trim().StartsWith("extension="))
{
database.SetParameterValue(commandWrapper, "@Extension",
clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("photo ") || clause.Trim().StartsWith("photo="))
{
database.SetParameterValue(commandWrapper, "@Photo",
clause.Trim().Remove(0,5).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("notes ") || clause.Trim().StartsWith("notes="))
{
database.SetParameterValue(commandWrapper, "@Notes",
clause.Trim().Remove(0,5).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("reportsto ") || clause.Trim().StartsWith("reportsto="))
{
database.SetParameterValue(commandWrapper, "@ReportsTo",
clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("photopath ") || clause.Trim().StartsWith("photopath="))
{
database.SetParameterValue(commandWrapper, "@PhotoPath",
clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
throw new ArgumentException("Unable to use this part of the where clause in this version of Find: " + clause);
}
IDataReader reader = null;
//Create Collection
TList<Employees> rows = new TList<Employees>();
try
{
//Provider Data Requesting Command Event
OnDataRequesting(new CommandEventArgs(commandWrapper, "Find", rows));
if (transactionManager != null)
{
reader = Utility.ExecuteReader(transactionManager, commandWrapper);
}
else
{
reader = Utility.ExecuteReader(database, commandWrapper);
}
Fill(reader, rows, start, pageLength);
if(reader.NextResult())
{
if(reader.Read())
{
count = reader.GetInt32(0);
}
}
//Provider Data Requested Command Event
OnDataRequested(new CommandEventArgs(commandWrapper, "Find", rows));
}
finally
{
if (reader != null)
reader.Close();
commandWrapper = null;
}
return rows;
}
/// <summary>
/// Returns rows meeting the whereClause condition from the DataSource.
/// </summary>
/// <param name="transactionManager"><see cref="TransactionManager"/> object</param>
/// <param name="whereClause">Specifies the condition for the rows returned by a query (Name='John Doe', Name='John Doe' AND Id='1', Name='John Doe' OR Id='1').</param>
/// <param name="start">Row number at which to start reading.</param>
/// <param name="pageLength">Number of rows to return.</param>
/// <param name="count">out. The number of rows that match this query.</param>
/// <remarks>Operators must be capitalized (OR, AND).</remarks>
/// <returns>Returns a typed collection of Library.BLL.Book objects.</returns>
public override TList<Book> Find(TransactionManager transactionManager, string whereClause, int start, int pageLength, out int count)
{
count = -1;
if (whereClause.IndexOf(";") > -1)
return new TList<Book>();
SqlDatabase database = new SqlDatabase(this._connectionString);
DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Book_Find", _useStoredProcedure);
bool searchUsingOR = false;
if (whereClause.IndexOf(" OR ") > 0) // did they want to do "a=b OR c=d OR..."?
searchUsingOR = true;
database.AddInParameter(commandWrapper, "@SearchUsingOR", DbType.Boolean, searchUsingOR);
database.AddInParameter(commandWrapper, "@ID", DbType.Int32, DBNull.Value);
database.AddInParameter(commandWrapper, "@Name", DbType.String, DBNull.Value);
database.AddInParameter(commandWrapper, "@Publisher", DbType.String, DBNull.Value);
database.AddInParameter(commandWrapper, "@Publish_Date", DbType.Date, DBNull.Value);
database.AddInParameter(commandWrapper, "@IsAvailablePdf", DbType.Boolean, DBNull.Value);
database.AddInParameter(commandWrapper, "@IsAvailablePaper", DbType.Boolean, DBNull.Value);
database.AddInParameter(commandWrapper, "@IsBorrowed", DbType.Boolean, DBNull.Value);
database.AddInParameter(commandWrapper, "@User_ID", DbType.Int32, DBNull.Value);
database.AddInParameter(commandWrapper, "@BorrowDate", DbType.Date, DBNull.Value);
database.AddInParameter(commandWrapper, "@Borrow_Times", DbType.Int32, DBNull.Value);
database.AddInParameter(commandWrapper, "@IsLost", DbType.Boolean, DBNull.Value);
database.AddInParameter(commandWrapper, "@Type_ID", DbType.Int32, DBNull.Value);
database.AddInParameter(commandWrapper, "@Publisher_ID", DbType.Int32, DBNull.Value);
database.AddInParameter(commandWrapper, "@Papers_no", DbType.Int32, DBNull.Value);
database.AddInParameter(commandWrapper, "@Introducer_ID", DbType.Int32, DBNull.Value);
database.AddInParameter(commandWrapper, "@EmployeeI_D", DbType.Int32, DBNull.Value);
database.AddInParameter(commandWrapper, "@Size", DbType.Double, DBNull.Value);
database.AddInParameter(commandWrapper, "@Price", DbType.Int32, DBNull.Value);
database.AddInParameter(commandWrapper, "@Pdf_Link", DbType.String, DBNull.Value);
database.AddInParameter(commandWrapper, "@Image_ID", DbType.Int32, DBNull.Value);
database.AddInParameter(commandWrapper, "@ISBN", DbType.String, DBNull.Value);
database.AddInParameter(commandWrapper, "@CreatedOn", DbType.DateTime, DBNull.Value);
database.AddInParameter(commandWrapper, "@CreatedBy", DbType.Int32, DBNull.Value);
database.AddInParameter(commandWrapper, "@UpdatedOn", DbType.DateTime, DBNull.Value);
database.AddInParameter(commandWrapper, "@UpdatedBy", DbType.Int32, DBNull.Value);
database.AddInParameter(commandWrapper, "@DeletedOn", DbType.DateTime, DBNull.Value);
database.AddInParameter(commandWrapper, "@DeletedBy", DbType.Int32, DBNull.Value);
// replace all instances of 'AND' and 'OR' because we already set searchUsingOR
whereClause = whereClause.Replace(" AND ", "|").Replace(" OR ", "|") ;
string[] clauses = whereClause.ToLower().Split('|');
// Here's what's going on below: Find a field, then to get the value we
// drop the field name from the front, trim spaces, drop the '=' sign,
// trim more spaces, and drop any outer single quotes.
// Now handles the case when two fields start off the same way - like "Friendly='Yes' AND Friend='john'"
char[] equalSign = {'='};
char[] singleQuote = {'\''};
foreach (string clause in clauses)
{
if (clause.Trim().StartsWith("id ") || clause.Trim().StartsWith("id="))
{
database.SetParameterValue(commandWrapper, "@ID",
clause.Trim().Remove(0,2).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("name ") || clause.Trim().StartsWith("name="))
{
database.SetParameterValue(commandWrapper, "@Name",
clause.Trim().Remove(0,4).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("publisher ") || clause.Trim().StartsWith("publisher="))
{
database.SetParameterValue(commandWrapper, "@Publisher",
clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("publish_date ") || clause.Trim().StartsWith("publish_date="))
{
database.SetParameterValue(commandWrapper, "@Publish_Date",
clause.Trim().Remove(0,12).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("isavailablepdf ") || clause.Trim().StartsWith("isavailablepdf="))
{
database.SetParameterValue(commandWrapper, "@IsAvailablePdf",
clause.Trim().Remove(0,14).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("isavailablepaper ") || clause.Trim().StartsWith("isavailablepaper="))
{
database.SetParameterValue(commandWrapper, "@IsAvailablePaper",
clause.Trim().Remove(0,16).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("isborrowed ") || clause.Trim().StartsWith("isborrowed="))
{
database.SetParameterValue(commandWrapper, "@IsBorrowed",
clause.Trim().Remove(0,10).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("user_id ") || clause.Trim().StartsWith("user_id="))
{
database.SetParameterValue(commandWrapper, "@User_ID",
clause.Trim().Remove(0,7).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("borrowdate ") || clause.Trim().StartsWith("borrowdate="))
{
database.SetParameterValue(commandWrapper, "@BorrowDate",
clause.Trim().Remove(0,10).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("borrow_times ") || clause.Trim().StartsWith("borrow_times="))
{
database.SetParameterValue(commandWrapper, "@Borrow_Times",
clause.Trim().Remove(0,12).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("islost ") || clause.Trim().StartsWith("islost="))
{
database.SetParameterValue(commandWrapper, "@IsLost",
clause.Trim().Remove(0,6).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("type_id ") || clause.Trim().StartsWith("type_id="))
{
database.SetParameterValue(commandWrapper, "@Type_ID",
clause.Trim().Remove(0,7).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("publisher_id ") || clause.Trim().StartsWith("publisher_id="))
{
database.SetParameterValue(commandWrapper, "@Publisher_ID",
clause.Trim().Remove(0,12).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("papers_no ") || clause.Trim().StartsWith("papers_no="))
{
database.SetParameterValue(commandWrapper, "@Papers_no",
clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("introducer_id ") || clause.Trim().StartsWith("introducer_id="))
{
database.SetParameterValue(commandWrapper, "@Introducer_ID",
clause.Trim().Remove(0,13).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("employeei_d ") || clause.Trim().StartsWith("employeei_d="))
{
database.SetParameterValue(commandWrapper, "@EmployeeI_D",
clause.Trim().Remove(0,11).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("size ") || clause.Trim().StartsWith("size="))
{
database.SetParameterValue(commandWrapper, "@Size",
clause.Trim().Remove(0,4).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("price ") || clause.Trim().StartsWith("price="))
{
database.SetParameterValue(commandWrapper, "@Price",
clause.Trim().Remove(0,5).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("pdf_link ") || clause.Trim().StartsWith("pdf_link="))
{
database.SetParameterValue(commandWrapper, "@Pdf_Link",
clause.Trim().Remove(0,8).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("image_id ") || clause.Trim().StartsWith("image_id="))
{
database.SetParameterValue(commandWrapper, "@Image_ID",
clause.Trim().Remove(0,8).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("isbn ") || clause.Trim().StartsWith("isbn="))
{
database.SetParameterValue(commandWrapper, "@ISBN",
clause.Trim().Remove(0,4).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("createdon ") || clause.Trim().StartsWith("createdon="))
{
database.SetParameterValue(commandWrapper, "@CreatedOn",
clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("createdby ") || clause.Trim().StartsWith("createdby="))
{
database.SetParameterValue(commandWrapper, "@CreatedBy",
clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("updatedon ") || clause.Trim().StartsWith("updatedon="))
{
database.SetParameterValue(commandWrapper, "@UpdatedOn",
clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("updatedby ") || clause.Trim().StartsWith("updatedby="))
{
database.SetParameterValue(commandWrapper, "@UpdatedBy",
clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("deletedon ") || clause.Trim().StartsWith("deletedon="))
{
database.SetParameterValue(commandWrapper, "@DeletedOn",
clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("deletedby ") || clause.Trim().StartsWith("deletedby="))
{
database.SetParameterValue(commandWrapper, "@DeletedBy",
clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
throw new ArgumentException("Unable to use this part of the where clause in this version of Find: " + clause);
}
IDataReader reader = null;
//Create Collection
TList<Book> rows = new TList<Book>();
try
{
//Provider Data Requesting Command Event
OnDataRequesting(new CommandEventArgs(commandWrapper, "Find", rows));
if (transactionManager != null)
{
reader = Utility.ExecuteReader(transactionManager, commandWrapper);
}
else
{
reader = Utility.ExecuteReader(database, commandWrapper);
}
Fill(reader, rows, start, pageLength);
if(reader.NextResult())
{
if(reader.Read())
{
count = reader.GetInt32(0);
}
}
//Provider Data Requested Command Event
OnDataRequested(new CommandEventArgs(commandWrapper, "Find", rows));
}
finally
{
if (reader != null)
reader.Close();
commandWrapper = null;
}
return rows;
}
/// <summary>
/// Returns rows meeting the whereClause condition from the DataSource.
/// </summary>
/// <param name="transactionManager"><see cref="TransactionManager"/> object</param>
/// <param name="whereClause">Specifies the condition for the rows returned by a query (Name='John Doe', Name='John Doe' AND Id='1', Name='John Doe' OR Id='1').</param>
/// <param name="start">Row number at which to start reading.</param>
/// <param name="pageLength">Number of rows to return.</param>
/// <param name="count">out. The number of rows that match this query.</param>
/// <remarks>Operators must be capitalized (OR, AND).</remarks>
/// <returns>Returns a typed collection of Library.BLL.Employee objects.</returns>
public override TList<Employee> Find(TransactionManager transactionManager, string whereClause, int start, int pageLength, out int count)
{
count = -1;
if (whereClause.IndexOf(";") > -1)
return new TList<Employee>();
SqlDatabase database = new SqlDatabase(this._connectionString);
DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "dbo.Employee_Find", _useStoredProcedure);
bool searchUsingOR = false;
if (whereClause.IndexOf(" OR ") > 0) // did they want to do "a=b OR c=d OR..."?
searchUsingOR = true;
database.AddInParameter(commandWrapper, "@SearchUsingOR", DbType.Boolean, searchUsingOR);
database.AddInParameter(commandWrapper, "@ID", DbType.Int32, DBNull.Value);
database.AddInParameter(commandWrapper, "@UserName", DbType.String, DBNull.Value);
database.AddInParameter(commandWrapper, "@Password", DbType.String, DBNull.Value);
database.AddInParameter(commandWrapper, "@Last_Login", DbType.DateTime, DBNull.Value);
database.AddInParameter(commandWrapper, "@Last_Logout", DbType.DateTime, DBNull.Value);
database.AddInParameter(commandWrapper, "@Is_Online", DbType.Boolean, DBNull.Value);
database.AddInParameter(commandWrapper, "@Is_Active", DbType.Boolean, DBNull.Value);
database.AddInParameter(commandWrapper, "@Is_Admin", DbType.Boolean, DBNull.Value);
database.AddInParameter(commandWrapper, "@First_Name", DbType.String, DBNull.Value);
database.AddInParameter(commandWrapper, "@Last_Name", DbType.String, DBNull.Value);
database.AddInParameter(commandWrapper, "@Email", DbType.String, DBNull.Value);
database.AddInParameter(commandWrapper, "@Country", DbType.String, DBNull.Value);
database.AddInParameter(commandWrapper, "@City", DbType.String, DBNull.Value);
database.AddInParameter(commandWrapper, "@Address", DbType.String, DBNull.Value);
database.AddInParameter(commandWrapper, "@Mobile", DbType.String, DBNull.Value);
database.AddInParameter(commandWrapper, "@Phone", DbType.String, DBNull.Value);
database.AddInParameter(commandWrapper, "@Postion", DbType.String, DBNull.Value);
database.AddInParameter(commandWrapper, "@Is_Deleted", DbType.Boolean, DBNull.Value);
database.AddInParameter(commandWrapper, "@CreatedOn", DbType.DateTime, DBNull.Value);
database.AddInParameter(commandWrapper, "@CreatedBy", DbType.Int32, DBNull.Value);
database.AddInParameter(commandWrapper, "@UpdatedOn", DbType.DateTime, DBNull.Value);
database.AddInParameter(commandWrapper, "@UpdatedBy", DbType.Int32, DBNull.Value);
database.AddInParameter(commandWrapper, "@DeletedOn", DbType.DateTime, DBNull.Value);
database.AddInParameter(commandWrapper, "@DeletedBy", DbType.Int32, DBNull.Value);
// replace all instances of 'AND' and 'OR' because we already set searchUsingOR
whereClause = whereClause.Replace(" AND ", "|").Replace(" OR ", "|") ;
string[] clauses = whereClause.ToLower().Split('|');
// Here's what's going on below: Find a field, then to get the value we
// drop the field name from the front, trim spaces, drop the '=' sign,
// trim more spaces, and drop any outer single quotes.
// Now handles the case when two fields start off the same way - like "Friendly='Yes' AND Friend='john'"
char[] equalSign = {'='};
char[] singleQuote = {'\''};
foreach (string clause in clauses)
{
if (clause.Trim().StartsWith("id ") || clause.Trim().StartsWith("id="))
{
database.SetParameterValue(commandWrapper, "@ID",
clause.Trim().Remove(0,2).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("username ") || clause.Trim().StartsWith("username="******"@UserName",
clause.Trim().Remove(0,8).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("password ") || clause.Trim().StartsWith("password="******"@Password",
clause.Trim().Remove(0,8).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("last_login ") || clause.Trim().StartsWith("last_login="******"@Last_Login",
clause.Trim().Remove(0,10).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("last_logout ") || clause.Trim().StartsWith("last_logout="))
{
database.SetParameterValue(commandWrapper, "@Last_Logout",
clause.Trim().Remove(0,11).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("is_online ") || clause.Trim().StartsWith("is_online="))
{
database.SetParameterValue(commandWrapper, "@Is_Online",
clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("is_active ") || clause.Trim().StartsWith("is_active="))
{
database.SetParameterValue(commandWrapper, "@Is_Active",
clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("is_admin ") || clause.Trim().StartsWith("is_admin="))
{
database.SetParameterValue(commandWrapper, "@Is_Admin",
clause.Trim().Remove(0,8).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("first_name ") || clause.Trim().StartsWith("first_name="))
{
database.SetParameterValue(commandWrapper, "@First_Name",
clause.Trim().Remove(0,10).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("last_name ") || clause.Trim().StartsWith("last_name="))
{
database.SetParameterValue(commandWrapper, "@Last_Name",
clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("email ") || clause.Trim().StartsWith("email="))
{
database.SetParameterValue(commandWrapper, "@Email",
clause.Trim().Remove(0,5).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("country ") || clause.Trim().StartsWith("country="))
{
database.SetParameterValue(commandWrapper, "@Country",
clause.Trim().Remove(0,7).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("city ") || clause.Trim().StartsWith("city="))
{
database.SetParameterValue(commandWrapper, "@City",
clause.Trim().Remove(0,4).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("address ") || clause.Trim().StartsWith("address="))
{
database.SetParameterValue(commandWrapper, "@Address",
clause.Trim().Remove(0,7).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("mobile ") || clause.Trim().StartsWith("mobile="))
{
database.SetParameterValue(commandWrapper, "@Mobile",
clause.Trim().Remove(0,6).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("phone ") || clause.Trim().StartsWith("phone="))
{
database.SetParameterValue(commandWrapper, "@Phone",
clause.Trim().Remove(0,5).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("postion ") || clause.Trim().StartsWith("postion="))
{
database.SetParameterValue(commandWrapper, "@Postion",
clause.Trim().Remove(0,7).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("is_deleted ") || clause.Trim().StartsWith("is_deleted="))
{
database.SetParameterValue(commandWrapper, "@Is_Deleted",
clause.Trim().Remove(0,10).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("createdon ") || clause.Trim().StartsWith("createdon="))
{
database.SetParameterValue(commandWrapper, "@CreatedOn",
clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("createdby ") || clause.Trim().StartsWith("createdby="))
{
database.SetParameterValue(commandWrapper, "@CreatedBy",
clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("updatedon ") || clause.Trim().StartsWith("updatedon="))
{
database.SetParameterValue(commandWrapper, "@UpdatedOn",
clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("updatedby ") || clause.Trim().StartsWith("updatedby="))
{
database.SetParameterValue(commandWrapper, "@UpdatedBy",
clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("deletedon ") || clause.Trim().StartsWith("deletedon="))
{
database.SetParameterValue(commandWrapper, "@DeletedOn",
clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("deletedby ") || clause.Trim().StartsWith("deletedby="))
{
database.SetParameterValue(commandWrapper, "@DeletedBy",
clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
throw new ArgumentException("Unable to use this part of the where clause in this version of Find: " + clause);
}
IDataReader reader = null;
//Create Collection
TList<Employee> rows = new TList<Employee>();
try
{
//Provider Data Requesting Command Event
OnDataRequesting(new CommandEventArgs(commandWrapper, "Find", rows));
if (transactionManager != null)
{
reader = Utility.ExecuteReader(transactionManager, commandWrapper);
}
else
{
reader = Utility.ExecuteReader(database, commandWrapper);
}
Fill(reader, rows, start, pageLength);
if(reader.NextResult())
{
if(reader.Read())
{
count = reader.GetInt32(0);
}
}
//Provider Data Requested Command Event
OnDataRequested(new CommandEventArgs(commandWrapper, "Find", rows));
}
finally
{
if (reader != null)
reader.Close();
commandWrapper = null;
}
return rows;
}
}//end Delete
#endregion
#region Find Functions
#region Parsed Find Methods
/// <summary>
/// Returns rows meeting the whereClause condition from the DataSource.
/// </summary>
/// <param name="transactionManager"><see cref="TransactionManager"/> object</param>
/// <param name="whereClause">Specifies the condition for the rows returned by a query (Name='John Doe', Name='John Doe' AND Id='1', Name='John Doe' OR Id='1').</param>
/// <param name="start">Row number at which to start reading.</param>
/// <param name="pageLength">Number of rows to return.</param>
/// <param name="count">out. The number of rows that match this query.</param>
/// <remarks>Operators must be capitalized (OR, AND).</remarks>
/// <returns>Returns a typed collection of Nettiers.AdventureWorks.Entities.TestProduct objects.</returns>
public override TList<TestProduct> Find(TransactionManager transactionManager, string whereClause, int start, int pageLength, out int count)
{
count = -1;
if (whereClause.IndexOf(";") > -1)
return new TList<TestProduct>();
SqlDatabase database = new SqlDatabase(this._connectionString);
DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Test.usp_adwTiers_tblTestProduct_Find", _useStoredProcedure);
bool searchUsingOR = false;
if (whereClause.IndexOf(" OR ") > 0) // did they want to do "a=b OR c=d OR..."?
searchUsingOR = true;
database.AddInParameter(commandWrapper, "@SearchUsingOR", DbType.Boolean, searchUsingOR);
database.AddInParameter(commandWrapper, "@ProductId", DbType.Int32, DBNull.Value);
database.AddInParameter(commandWrapper, "@ProductTypeId", DbType.Int32, DBNull.Value);
database.AddInParameter(commandWrapper, "@DownloadId", DbType.Int32, DBNull.Value);
database.AddInParameter(commandWrapper, "@ManufacturerId", DbType.Int32, DBNull.Value);
database.AddInParameter(commandWrapper, "@BrandName", DbType.String, DBNull.Value);
database.AddInParameter(commandWrapper, "@ProductName", DbType.String, DBNull.Value);
database.AddInParameter(commandWrapper, "@ProductCode", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@UniqueIdentifier", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@TypeName", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@ModelName", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@DisplayName", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@ProductLink", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@ConnectorCode", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@BaseId", DbType.Int32, DBNull.Value);
database.AddInParameter(commandWrapper, "@OrgProductId", DbType.Int32, DBNull.Value);
database.AddInParameter(commandWrapper, "@ImageFileType", DbType.AnsiStringFixedLength, DBNull.Value);
database.AddInParameter(commandWrapper, "@FullImageFileType", DbType.AnsiStringFixedLength, DBNull.Value);
database.AddInParameter(commandWrapper, "@Status", DbType.AnsiStringFixedLength, DBNull.Value);
database.AddInParameter(commandWrapper, "@AddedBy", DbType.Int32, DBNull.Value);
database.AddInParameter(commandWrapper, "@AddedDate", DbType.DateTime, DBNull.Value);
database.AddInParameter(commandWrapper, "@UpdatedBy", DbType.Int32, DBNull.Value);
database.AddInParameter(commandWrapper, "@UpdatedDate", DbType.DateTime, DBNull.Value);
// replace all instances of 'AND' and 'OR' because we already set searchUsingOR
whereClause = whereClause.Replace(" AND ", "|").Replace(" OR ", "|") ;
string[] clauses = whereClause.ToLower().Split('|');
// Here's what's going on below: Find a field, then to get the value we
// drop the field name from the front, trim spaces, drop the '=' sign,
// trim more spaces, and drop any outer single quotes.
// Now handles the case when two fields start off the same way - like "Friendly='Yes' AND Friend='john'"
char[] equalSign = {'='};
char[] singleQuote = {'\''};
foreach (string clause in clauses)
{
if (clause.Trim().StartsWith("productid ") || clause.Trim().StartsWith("productid="))
{
database.SetParameterValue(commandWrapper, "@ProductId",
clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("producttypeid ") || clause.Trim().StartsWith("producttypeid="))
{
database.SetParameterValue(commandWrapper, "@ProductTypeId",
clause.Trim().Remove(0,13).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("downloadid ") || clause.Trim().StartsWith("downloadid="))
{
database.SetParameterValue(commandWrapper, "@DownloadId",
clause.Trim().Remove(0,10).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("manufacturerid ") || clause.Trim().StartsWith("manufacturerid="))
{
database.SetParameterValue(commandWrapper, "@ManufacturerId",
clause.Trim().Remove(0,14).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("brandname ") || clause.Trim().StartsWith("brandname="))
{
database.SetParameterValue(commandWrapper, "@BrandName",
clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("productname ") || clause.Trim().StartsWith("productname="))
{
database.SetParameterValue(commandWrapper, "@ProductName",
clause.Trim().Remove(0,11).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("productcode ") || clause.Trim().StartsWith("productcode="))
{
database.SetParameterValue(commandWrapper, "@ProductCode",
clause.Trim().Remove(0,11).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("uniqueidentifier ") || clause.Trim().StartsWith("uniqueidentifier="))
{
database.SetParameterValue(commandWrapper, "@UniqueIdentifier",
clause.Trim().Remove(0,16).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("typename ") || clause.Trim().StartsWith("typename="))
{
database.SetParameterValue(commandWrapper, "@TypeName",
clause.Trim().Remove(0,8).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("modelname ") || clause.Trim().StartsWith("modelname="))
{
database.SetParameterValue(commandWrapper, "@ModelName",
clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("displayname ") || clause.Trim().StartsWith("displayname="))
{
database.SetParameterValue(commandWrapper, "@DisplayName",
clause.Trim().Remove(0,11).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("productlink ") || clause.Trim().StartsWith("productlink="))
{
database.SetParameterValue(commandWrapper, "@ProductLink",
clause.Trim().Remove(0,11).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("connectorcode ") || clause.Trim().StartsWith("connectorcode="))
{
database.SetParameterValue(commandWrapper, "@ConnectorCode",
clause.Trim().Remove(0,13).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("baseid ") || clause.Trim().StartsWith("baseid="))
{
database.SetParameterValue(commandWrapper, "@BaseId",
clause.Trim().Remove(0,6).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("orgproductid ") || clause.Trim().StartsWith("orgproductid="))
{
database.SetParameterValue(commandWrapper, "@OrgProductId",
clause.Trim().Remove(0,12).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("imagefiletype ") || clause.Trim().StartsWith("imagefiletype="))
{
database.SetParameterValue(commandWrapper, "@ImageFileType",
clause.Trim().Remove(0,13).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("fullimagefiletype ") || clause.Trim().StartsWith("fullimagefiletype="))
{
database.SetParameterValue(commandWrapper, "@FullImageFileType",
clause.Trim().Remove(0,17).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("status ") || clause.Trim().StartsWith("status="))
{
database.SetParameterValue(commandWrapper, "@Status",
clause.Trim().Remove(0,6).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("addedby ") || clause.Trim().StartsWith("addedby="))
{
database.SetParameterValue(commandWrapper, "@AddedBy",
clause.Trim().Remove(0,7).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("addeddate ") || clause.Trim().StartsWith("addeddate="))
{
database.SetParameterValue(commandWrapper, "@AddedDate",
clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("updatedby ") || clause.Trim().StartsWith("updatedby="))
{
database.SetParameterValue(commandWrapper, "@UpdatedBy",
clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("updateddate ") || clause.Trim().StartsWith("updateddate="))
{
database.SetParameterValue(commandWrapper, "@UpdatedDate",
clause.Trim().Remove(0,11).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
throw new ArgumentException("Unable to use this part of the where clause in this version of Find: " + clause);
}
IDataReader reader = null;
//Create Collection
TList<TestProduct> rows = new TList<TestProduct>();
try
{
//Provider Data Requesting Command Event
OnDataRequesting(new CommandEventArgs(commandWrapper, "Find", rows));
if (transactionManager != null)
{
reader = Utility.ExecuteReader(transactionManager, commandWrapper);
}
else
{
reader = Utility.ExecuteReader(database, commandWrapper);
}
Fill(reader, rows, start, pageLength);
if(reader.NextResult())
{
if(reader.Read())
{
count = reader.GetInt32(0);
}
}
//Provider Data Requested Command Event
OnDataRequested(new CommandEventArgs(commandWrapper, "Find", rows));
}
finally
{
if (reader != null)
reader.Close();
commandWrapper = null;
}
return rows;
}
}//end Delete
#endregion
#region Find Functions
#region Parsed Find Methods
/// <summary>
/// Returns rows meeting the whereClause condition from the DataSource.
/// </summary>
/// <param name="transactionManager"><see cref="TransactionManager"/> object</param>
/// <param name="whereClause">Specifies the condition for the rows returned by a query (Name='John Doe', Name='John Doe' AND Id='1', Name='John Doe' OR Id='1').</param>
/// <param name="start">Row number at which to start reading.</param>
/// <param name="pageLength">Number of rows to return.</param>
/// <param name="count">out. The number of rows that match this query.</param>
/// <remarks>Operators must be capitalized (OR, AND).</remarks>
/// <returns>Returns a typed collection of Nettiers.AdventureWorks.Entities.SalesOrderHeader objects.</returns>
public override TList<SalesOrderHeader> Find(TransactionManager transactionManager, string whereClause, int start, int pageLength, out int count)
{
count = -1;
if (whereClause.IndexOf(";") > -1)
return new TList<SalesOrderHeader>();
SqlDatabase database = new SqlDatabase(this._connectionString);
DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Sales.usp_adwTiers_SalesOrderHeader_Find", _useStoredProcedure);
bool searchUsingOR = false;
if (whereClause.IndexOf(" OR ") > 0) // did they want to do "a=b OR c=d OR..."?
searchUsingOR = true;
database.AddInParameter(commandWrapper, "@SearchUsingOR", DbType.Boolean, searchUsingOR);
database.AddInParameter(commandWrapper, "@SalesOrderId", DbType.Int32, DBNull.Value);
database.AddInParameter(commandWrapper, "@RevisionNumber", DbType.Byte, DBNull.Value);
database.AddInParameter(commandWrapper, "@OrderDate", DbType.DateTime, DBNull.Value);
database.AddInParameter(commandWrapper, "@DueDate", DbType.DateTime, DBNull.Value);
database.AddInParameter(commandWrapper, "@ShipDate", DbType.DateTime, DBNull.Value);
database.AddInParameter(commandWrapper, "@Status", DbType.Byte, DBNull.Value);
database.AddInParameter(commandWrapper, "@OnlineOrderFlag", DbType.Boolean, DBNull.Value);
database.AddInParameter(commandWrapper, "@SalesOrderNumber", DbType.String, DBNull.Value);
database.AddInParameter(commandWrapper, "@PurchaseOrderNumber", DbType.String, DBNull.Value);
database.AddInParameter(commandWrapper, "@AccountNumber", DbType.String, DBNull.Value);
database.AddInParameter(commandWrapper, "@CustomerId", DbType.Int32, DBNull.Value);
database.AddInParameter(commandWrapper, "@ContactId", DbType.Int32, DBNull.Value);
database.AddInParameter(commandWrapper, "@SalesPersonId", DbType.Int32, DBNull.Value);
database.AddInParameter(commandWrapper, "@TerritoryId", DbType.Int32, DBNull.Value);
database.AddInParameter(commandWrapper, "@BillToAddressId", DbType.Int32, DBNull.Value);
database.AddInParameter(commandWrapper, "@ShipToAddressId", DbType.Int32, DBNull.Value);
database.AddInParameter(commandWrapper, "@ShipMethodId", DbType.Int32, DBNull.Value);
database.AddInParameter(commandWrapper, "@CreditCardId", DbType.Int32, DBNull.Value);
database.AddInParameter(commandWrapper, "@CreditCardApprovalCode", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@CurrencyRateId", DbType.Int32, DBNull.Value);
database.AddInParameter(commandWrapper, "@SubTotal", DbType.Currency, DBNull.Value);
database.AddInParameter(commandWrapper, "@TaxAmt", DbType.Currency, DBNull.Value);
database.AddInParameter(commandWrapper, "@Freight", DbType.Currency, DBNull.Value);
database.AddInParameter(commandWrapper, "@TotalDue", DbType.Currency, DBNull.Value);
database.AddInParameter(commandWrapper, "@Comment", DbType.String, DBNull.Value);
database.AddInParameter(commandWrapper, "@Rowguid", DbType.Guid, DBNull.Value);
database.AddInParameter(commandWrapper, "@ModifiedDate", DbType.DateTime, DBNull.Value);
// replace all instances of 'AND' and 'OR' because we already set searchUsingOR
whereClause = whereClause.Replace(" AND ", "|").Replace(" OR ", "|") ;
string[] clauses = whereClause.ToLower().Split('|');
// Here's what's going on below: Find a field, then to get the value we
// drop the field name from the front, trim spaces, drop the '=' sign,
// trim more spaces, and drop any outer single quotes.
// Now handles the case when two fields start off the same way - like "Friendly='Yes' AND Friend='john'"
char[] equalSign = {'='};
char[] singleQuote = {'\''};
foreach (string clause in clauses)
{
if (clause.Trim().StartsWith("salesorderid ") || clause.Trim().StartsWith("salesorderid="))
{
database.SetParameterValue(commandWrapper, "@SalesOrderId",
clause.Trim().Remove(0,12).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("revisionnumber ") || clause.Trim().StartsWith("revisionnumber="))
{
database.SetParameterValue(commandWrapper, "@RevisionNumber",
clause.Trim().Remove(0,14).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("orderdate ") || clause.Trim().StartsWith("orderdate="))
{
database.SetParameterValue(commandWrapper, "@OrderDate",
clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("duedate ") || clause.Trim().StartsWith("duedate="))
{
database.SetParameterValue(commandWrapper, "@DueDate",
clause.Trim().Remove(0,7).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("shipdate ") || clause.Trim().StartsWith("shipdate="))
{
database.SetParameterValue(commandWrapper, "@ShipDate",
clause.Trim().Remove(0,8).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("status ") || clause.Trim().StartsWith("status="))
{
database.SetParameterValue(commandWrapper, "@Status",
clause.Trim().Remove(0,6).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("onlineorderflag ") || clause.Trim().StartsWith("onlineorderflag="))
{
database.SetParameterValue(commandWrapper, "@OnlineOrderFlag",
clause.Trim().Remove(0,15).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("salesordernumber ") || clause.Trim().StartsWith("salesordernumber="))
{
database.SetParameterValue(commandWrapper, "@SalesOrderNumber",
clause.Trim().Remove(0,16).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("purchaseordernumber ") || clause.Trim().StartsWith("purchaseordernumber="))
{
database.SetParameterValue(commandWrapper, "@PurchaseOrderNumber",
clause.Trim().Remove(0,19).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("accountnumber ") || clause.Trim().StartsWith("accountnumber="))
{
database.SetParameterValue(commandWrapper, "@AccountNumber",
clause.Trim().Remove(0,13).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("customerid ") || clause.Trim().StartsWith("customerid="))
{
database.SetParameterValue(commandWrapper, "@CustomerId",
clause.Trim().Remove(0,10).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("contactid ") || clause.Trim().StartsWith("contactid="))
{
database.SetParameterValue(commandWrapper, "@ContactId",
clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("salespersonid ") || clause.Trim().StartsWith("salespersonid="))
{
database.SetParameterValue(commandWrapper, "@SalesPersonId",
clause.Trim().Remove(0,13).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("territoryid ") || clause.Trim().StartsWith("territoryid="))
{
database.SetParameterValue(commandWrapper, "@TerritoryId",
clause.Trim().Remove(0,11).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("billtoaddressid ") || clause.Trim().StartsWith("billtoaddressid="))
{
database.SetParameterValue(commandWrapper, "@BillToAddressId",
clause.Trim().Remove(0,15).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("shiptoaddressid ") || clause.Trim().StartsWith("shiptoaddressid="))
{
database.SetParameterValue(commandWrapper, "@ShipToAddressId",
clause.Trim().Remove(0,15).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("shipmethodid ") || clause.Trim().StartsWith("shipmethodid="))
{
database.SetParameterValue(commandWrapper, "@ShipMethodId",
clause.Trim().Remove(0,12).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("creditcardid ") || clause.Trim().StartsWith("creditcardid="))
{
database.SetParameterValue(commandWrapper, "@CreditCardId",
clause.Trim().Remove(0,12).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("creditcardapprovalcode ") || clause.Trim().StartsWith("creditcardapprovalcode="))
{
database.SetParameterValue(commandWrapper, "@CreditCardApprovalCode",
clause.Trim().Remove(0,22).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("currencyrateid ") || clause.Trim().StartsWith("currencyrateid="))
{
database.SetParameterValue(commandWrapper, "@CurrencyRateId",
clause.Trim().Remove(0,14).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("subtotal ") || clause.Trim().StartsWith("subtotal="))
{
database.SetParameterValue(commandWrapper, "@SubTotal",
clause.Trim().Remove(0,8).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("taxamt ") || clause.Trim().StartsWith("taxamt="))
{
database.SetParameterValue(commandWrapper, "@TaxAmt",
clause.Trim().Remove(0,6).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("freight ") || clause.Trim().StartsWith("freight="))
{
database.SetParameterValue(commandWrapper, "@Freight",
clause.Trim().Remove(0,7).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("totaldue ") || clause.Trim().StartsWith("totaldue="))
{
database.SetParameterValue(commandWrapper, "@TotalDue",
clause.Trim().Remove(0,8).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("comment ") || clause.Trim().StartsWith("comment="))
{
database.SetParameterValue(commandWrapper, "@Comment",
clause.Trim().Remove(0,7).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("rowguid ") || clause.Trim().StartsWith("rowguid="))
{
database.SetParameterValue(commandWrapper, "@Rowguid", new Guid(
clause.Trim().Remove(0,7).Trim().TrimStart(equalSign).Trim().Trim(singleQuote)));
continue;
}
if (clause.Trim().StartsWith("modifieddate ") || clause.Trim().StartsWith("modifieddate="))
{
database.SetParameterValue(commandWrapper, "@ModifiedDate",
clause.Trim().Remove(0,12).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
throw new ArgumentException("Unable to use this part of the where clause in this version of Find: " + clause);
}
IDataReader reader = null;
//Create Collection
TList<SalesOrderHeader> rows = new TList<SalesOrderHeader>();
try
{
//Provider Data Requesting Command Event
OnDataRequesting(new CommandEventArgs(commandWrapper, "Find", rows));
if (transactionManager != null)
{
reader = Utility.ExecuteReader(transactionManager, commandWrapper);
}
else
{
reader = Utility.ExecuteReader(database, commandWrapper);
}
Fill(reader, rows, start, pageLength);
if(reader.NextResult())
{
if(reader.Read())
{
count = reader.GetInt32(0);
}
}
//Provider Data Requested Command Event
OnDataRequested(new CommandEventArgs(commandWrapper, "Find", rows));
}
finally
{
if (reader != null)
reader.Close();
commandWrapper = null;
}
return rows;
}
}//end Delete
#endregion
#region Find Functions
#region Parsed Find Methods
/// <summary>
/// Returns rows meeting the whereClause condition from the DataSource.
/// </summary>
/// <param name="transactionManager"><see cref="TransactionManager"/> object</param>
/// <param name="whereClause">Specifies the condition for the rows returned by a query (Name='John Doe', Name='John Doe' AND Id='1', Name='John Doe' OR Id='1').</param>
/// <param name="start">Row number at which to start reading.</param>
/// <param name="pageLength">Number of rows to return.</param>
/// <param name="count">out. The number of rows that match this query.</param>
/// <remarks>Operators must be capitalized (OR, AND).</remarks>
/// <returns>Returns a typed collection of Nettiers.AdventureWorks.Entities.Contact objects.</returns>
public override TList<Contact> Find(TransactionManager transactionManager, string whereClause, int start, int pageLength, out int count)
{
count = -1;
if (whereClause.IndexOf(";") > -1)
return new TList<Contact>();
SqlDatabase database = new SqlDatabase(this._connectionString);
DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "Person.usp_adwTiers_Contact_Find", _useStoredProcedure);
bool searchUsingOR = false;
if (whereClause.IndexOf(" OR ") > 0) // did they want to do "a=b OR c=d OR..."?
searchUsingOR = true;
database.AddInParameter(commandWrapper, "@SearchUsingOR", DbType.Boolean, searchUsingOR);
database.AddInParameter(commandWrapper, "@ContactId", DbType.Int32, DBNull.Value);
database.AddInParameter(commandWrapper, "@NameStyle", DbType.Boolean, DBNull.Value);
database.AddInParameter(commandWrapper, "@Title", DbType.String, DBNull.Value);
database.AddInParameter(commandWrapper, "@FirstName", DbType.String, DBNull.Value);
database.AddInParameter(commandWrapper, "@MiddleName", DbType.String, DBNull.Value);
database.AddInParameter(commandWrapper, "@LastName", DbType.String, DBNull.Value);
database.AddInParameter(commandWrapper, "@Suffix", DbType.String, DBNull.Value);
database.AddInParameter(commandWrapper, "@EmailAddress", DbType.String, DBNull.Value);
database.AddInParameter(commandWrapper, "@EmailPromotion", DbType.Int32, DBNull.Value);
database.AddInParameter(commandWrapper, "@Phone", DbType.String, DBNull.Value);
database.AddInParameter(commandWrapper, "@PasswordHash", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@PasswordSalt", DbType.AnsiString, DBNull.Value);
database.AddInParameter(commandWrapper, "@AdditionalContactInfo", DbType.Xml, DBNull.Value);
database.AddInParameter(commandWrapper, "@Rowguid", DbType.Guid, DBNull.Value);
database.AddInParameter(commandWrapper, "@ModifiedDate", DbType.DateTime, DBNull.Value);
// replace all instances of 'AND' and 'OR' because we already set searchUsingOR
whereClause = whereClause.Replace(" AND ", "|").Replace(" OR ", "|") ;
string[] clauses = whereClause.ToLower().Split('|');
// Here's what's going on below: Find a field, then to get the value we
// drop the field name from the front, trim spaces, drop the '=' sign,
// trim more spaces, and drop any outer single quotes.
// Now handles the case when two fields start off the same way - like "Friendly='Yes' AND Friend='john'"
char[] equalSign = {'='};
char[] singleQuote = {'\''};
foreach (string clause in clauses)
{
if (clause.Trim().StartsWith("contactid ") || clause.Trim().StartsWith("contactid="))
{
database.SetParameterValue(commandWrapper, "@ContactId",
clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("namestyle ") || clause.Trim().StartsWith("namestyle="))
{
database.SetParameterValue(commandWrapper, "@NameStyle",
clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("title ") || clause.Trim().StartsWith("title="))
{
database.SetParameterValue(commandWrapper, "@Title",
clause.Trim().Remove(0,5).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("firstname ") || clause.Trim().StartsWith("firstname="))
{
database.SetParameterValue(commandWrapper, "@FirstName",
clause.Trim().Remove(0,9).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("middlename ") || clause.Trim().StartsWith("middlename="))
{
database.SetParameterValue(commandWrapper, "@MiddleName",
clause.Trim().Remove(0,10).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("lastname ") || clause.Trim().StartsWith("lastname="))
{
database.SetParameterValue(commandWrapper, "@LastName",
clause.Trim().Remove(0,8).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("suffix ") || clause.Trim().StartsWith("suffix="))
{
database.SetParameterValue(commandWrapper, "@Suffix",
clause.Trim().Remove(0,6).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("emailaddress ") || clause.Trim().StartsWith("emailaddress="))
{
database.SetParameterValue(commandWrapper, "@EmailAddress",
clause.Trim().Remove(0,12).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("emailpromotion ") || clause.Trim().StartsWith("emailpromotion="))
{
database.SetParameterValue(commandWrapper, "@EmailPromotion",
clause.Trim().Remove(0,14).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("phone ") || clause.Trim().StartsWith("phone="))
{
database.SetParameterValue(commandWrapper, "@Phone",
clause.Trim().Remove(0,5).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("passwordhash ") || clause.Trim().StartsWith("passwordhash="))
{
database.SetParameterValue(commandWrapper, "@PasswordHash",
clause.Trim().Remove(0,12).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("passwordsalt ") || clause.Trim().StartsWith("passwordsalt="))
{
database.SetParameterValue(commandWrapper, "@PasswordSalt",
clause.Trim().Remove(0,12).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("additionalcontactinfo ") || clause.Trim().StartsWith("additionalcontactinfo="))
{
database.SetParameterValue(commandWrapper, "@AdditionalContactInfo",
clause.Trim().Remove(0,21).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
if (clause.Trim().StartsWith("rowguid ") || clause.Trim().StartsWith("rowguid="))
{
database.SetParameterValue(commandWrapper, "@Rowguid", new Guid(
clause.Trim().Remove(0,7).Trim().TrimStart(equalSign).Trim().Trim(singleQuote)));
continue;
}
if (clause.Trim().StartsWith("modifieddate ") || clause.Trim().StartsWith("modifieddate="))
{
database.SetParameterValue(commandWrapper, "@ModifiedDate",
clause.Trim().Remove(0,12).Trim().TrimStart(equalSign).Trim().Trim(singleQuote));
continue;
}
throw new ArgumentException("Unable to use this part of the where clause in this version of Find: " + clause);
}
IDataReader reader = null;
//Create Collection
TList<Contact> rows = new TList<Contact>();
try
{
//Provider Data Requesting Command Event
OnDataRequesting(new CommandEventArgs(commandWrapper, "Find", rows));
if (transactionManager != null)
{
reader = Utility.ExecuteReader(transactionManager, commandWrapper);
}
else
{
reader = Utility.ExecuteReader(database, commandWrapper);
}
Fill(reader, rows, start, pageLength);
if(reader.NextResult())
{
if(reader.Read())
{
count = reader.GetInt32(0);
}
}
//Provider Data Requested Command Event
OnDataRequested(new CommandEventArgs(commandWrapper, "Find", rows));
}
finally
{
if (reader != null)
reader.Close();
commandWrapper = null;
}
return rows;
}
