DataRepository.[tableprovider].Find(string whereClause)-problem.

Hi all,

I have a newbie question that I can't seem to figure out myself:

I have a database table, "Table1", that holds a couple of Guid-identifer fields, EventID and CompanyID. These Guid-identifiers have their own "Get"-methods in the auto-generated DataRepository.Table1Provider object (GetByEventID() and GetByCompanyID()).

Now, Table1 holds more fields than just these two, so I would like to select all rows from Table1 where EventDate = SomeDate. Since "EventDate" doesn't have a Get-method like the two GUID-identifiers, I figured I could use: DataRepository.Table1Provider.Find("EventDate = '" + DateTime.Now + "'") to return rows that match this query.

And here's where the problem occurs. By looking in the SQL Profiler, I see that the query sent to the database looks something like this (simplified!):

Select EventID, CompanyID, EventDate from Table1 where EventID is null and CompanyID is null and EventDate = 'Nov 12 2007'.

This query will, obviously, always return empty since it explicitly checks for "EventID is null and CompanyID is null" which they'll never ever be (EventID is the Primary Key and CompanyID doesn't allow NULLs).

So my question is: Do I need to specify each and every field in my Find-method? Like DataRepository.Table1Provider.Find("EventID not is null, CompanyID not is null, EventDate = '" + DateTime.Now + "'") or am I doing something completely wrong here?

Thanks in advance,

/DonRex. 

Ah, just realized what was wrong (apart from myself).

Turns out that the generated SQL that's fired in the database is fine - the problem lies with the date-value.

For instance, this won't work: Select * from mytable where myDate = 'Nov 12 2007' because this checks to see if the provided date is _exactly_ the same as the one in the database (which it only is for one millisecond). This is, more or less, how the DataRepository-object fires the select-statement in the database.

This, however, will work: Select * from mytable where myDate <= 'Nov 12 2007'.

And so will this: Select * from mytable where myDate BETWEEN 'Nov 12 2007' AND 'Nov 13 2007'

So the question really is: How do I select 'between' or 'less than or equal to' from the DataRepository.tableprovider.find()-method?

And if this is not possible, what can I do to work around it?

Thanks for your help,

/DonRex.

Hi DonRex,

One way to do the exact date match select would be to add an index on myDate. When you generate netTiers again, you'll get a method on your provider like: DataRespository.MyTable.GetByMyDate(DateTime myDate). This should return a TList of MyTable objects.

This doesn't solve your real problem though, in that you need to return a collection of MyTable objects based on a date range.

For this, you could write a custom stored procedure. As long as the procedure name is formatted properly, netTiers will add it as a method of your MyTable repository object. So for instance, if you write a procedure like this:

CREATE PROCEDURE dbo._MyTable_GetAllByMyDateRange

@StartDate as DateTime
, @EndDate as DateTime

AS

SELECT * FROM dbo.MyTable WHERE myDate BETWEEN @StartDate AND @EndDate

The next time you generate netTiers, you'll get the following method: MyTable.GetAllByMyDateRange(DateTime startDate, DateTime endDate). The return of this should be a TList of MyTable objects.

To run the <= scenario, pick a starting date that is less than any known date in the database.

Does this help?

-Agg

Do a search on this forum the ParameterBuilder classes.  It will do exactly what you are looking for.  Off the top of my head, it would look something like the following:

Table1ParameterBuilder pb = new Table1ParameterBuilder();
pb.AppendGreaterThanOrEqual(Table1Column.EventID, "Nov 12 2007");
pb.AppendLessThanOrEqual(Table1Column.EventID, "Nov 12 2007");
TList<Table1> list = DataRepository.Table1Provider.Find(pb.GetParameters());

Hi guys,

Thank you very much for your help. I had the ParameterBuilder-solution figured out yesterday evening, so that's what I went with.

Agg, I'll have a look at your solution. That sounded like the way to go in the long run, but I've just started out with Codesmith and I just want to learn the basics for now! :)

Thanks again for your help,

/Tom.