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