How to retrieve multi-row blocks from a datatable?

[John Dann]

Is there a concise/efficient way to retrieve blocks of rows from a
datatable with VB2005?

I've got a datatable (let's call it AllData), constructed
programmatically, that contains a lot of sequential scientific data,
each row being a point in a time-course potentially every minute and
with data maybe extending over many months. There's a column
(Date_Time) set as a primary key which contains a datetime stamp (as a
datetime type) for each row.

What I'd like to do is to query the datatable for all rows relating to
say a single day by coding something like (I know this isn't legal
VB):

Dim DailyData as datatable = AllData.Select(col("Date_Time") where
datetime > start-time AND datetime < end-time)

I could obviously do something by iterating 'manually' over all the
rows in AllData, but I was hoping to find a simpler and maybe more
efficient approach. AllData is just an in-memory datatable so AIUI I
can't use a standard SQL query on the datatable as I could if it were
a database. And I'm stuck with VB2005 so I don't believe that I could
use LINQ, which might make this easy in eg VB2008. So are there any
other options please?

 

[James Hahn]

I presume that AllData is a System.Data.DataTable object, as indicated in
your example line for the daily data.. You can extract a block of rows from
the table using criteria such as you have listed with an expression like:

Dim Daily() as DataRow
Dim FilterString as String = String.Format("datetime > #{0}# AND datetime <
#{1}#", DateTime.Parse(start-time), DateTime.Parse(end-time))
Daily = AllData.Select(FilterString)

 

[John Dann]

I presume that AllData is a System.Data.DataTable object, as indicated in
your example line for the daily data.. You can extract a block of rows from
the table using criteria such as you have listed with an expression like:

Dim Daily() as DataRow
Dim FilterString as String = String.Format("datetime > #{0}# AND datetime <
#{1}#", DateTime.Parse(start-time), DateTime.Parse(end-time))
Daily = AllData.Select(FilterString)

Many thanks. I'll be able to take a detailed look at this a little
later in the day. Yes in my example AllData was indeed a standard
DataTable object.

Ideally what I'd like to return is another - obviously smaller -
DataTable. Is there a neat (I'm thinking single line of code) way of
reassembling that array of datarows (ie Daily() ) back into a
DataTable? Or do I need to somehow clone the schema from the original
datatable and instantiate a new empty datatable and then iterate
through all the rows in Daily() and add each in turn to the new
datatable?

 

[John Dann]

I presume that AllData is a System.Data.DataTable object, as indicated in
your example line for the daily data.. You can extract a block of rows from
the table using criteria such as you have listed with an expression like:

Dim Daily() as DataRow
Dim FilterString as String = String.Format("datetime > #{0}# AND datetime <
#{1}#", DateTime.Parse(start-time), DateTime.Parse(end-time))
Daily = AllData.Select(FilterString)

Sadly I can't get this to work. In the above FilterString definition
if I use:

Dim FilterString as String = String.Format("Date_Time > #{0}# AND
DateTime ,<#{1}#", DateTime.Parse("1/5/2008"), DateTime.Parse(
"13/5/2008"))

(Date_Time is the column name for my datetime column and I'm working
with some data from May 2008) then I get:

FilterString = "Date_Time >#1/5/2008 00:00:00# AND Date_Time
<#13/5/2008 00:00:00#"

which seems as expected. But at the line

Daily = AllData.Select(FilterString)

a 'FormatException not handled' error 'String was not recognised as a
valid DateTime' is thrown.

It seems like this suggestion is trying to select from a datetime type
column by searching on a string type, which maybe (?) can't work?

Is there an error in my code snippett that I haven't spotted? Or is
there some other way of formatting the filter string that will filter
on datetime types?

 

[Armin Zingler]

Dim FilterString as String = String.Format("Date_Time > #{0}# AND
DateTime ,<#{1}#", DateTime.Parse("1/5/2008"), DateTime.Parse(
"13/5/2008"))

(Date_Time is the column name for my datetime column and I'm working
with some data from May 2008) then I get:

FilterString = "Date_Time >#1/5/2008 00:00:00# AND Date_Time
<#13/5/2008 00:00:00#"

You'd better display the value during debugging because this string is not
the one created by the assignment. In the assignment, you have a "," too
much between "DateTime" and "<".

In addition, you can use date literals instead of parsing a string.

"13/5/2008" dosn't work because there is not 13th month.

You should also be aware of different date/time formats depending on the
current locale, so I recommend using a determined format:

Const DateTimeFormat As String = "\#MM\/dd\/yyyy HH\:mm\:ss\#"

Dim MinDate = #5/1/2008#
Dim MaxDate = #5/13/2008#

Dim FilterString As String = String.Format( _
"test > {0} AND test <{1}", _
mindate.ToString(DateTimeFormat), _
maxdate.ToString(DateTimeFormat) _
)



Armin

 

[John Dann]

You'd better display the value during debugging because this string is not
the one created by the assignment. In the assignment, you have a "," too
much between "DateTime" and "<".

Many thanks -that's very useful.

Sorry I don't have a news client on my VB development PC so I was
copying manually and obviously introduced an error. Here's the actual
string:

Date_Time >#01/05/2008 00:00:00# AND Date_Time <#13/05/2008 00:00:00#

which looks as intended to me.

However, the actual mindate and maxdate values (as you've named them)
are actually pre-existing datetime-type values in my code. If I print
one of these values in a msgbox it looks like:

13/05/2008 00:00:00 (UK locale)

So I've rewritten your example as follows:

Const DateTimeFormat As String = "dd\/MM\/yyyy HH\:mm\:ss"

Dim FilterString As String = String.Format("Date_Time >{0} AND
Date_Time <{1}", _
mindate.ToString(DateTimeFormat), maxdate.ToString(DateTimeFormat))


But the datatable.Select operation is giving a SyntaxErrorException
saying 'Missing operand after '00' operator'. So it looks like I'm
making progress in that the previous FormatException error has
vanished. But there's clearly something about format string that's not
quite right. Unfortunately I can't spot what that is. (NB Code above
is cut and paste across machines so is definitely exactly as is
written in the working code - other than line wrap.)

 

[Armin Zingler]

Const DateTimeFormat As String = "dd\/MM\/yyyy HH\:mm\:ss"

The format string I posted was:

Const DateTimeFormat As String = "\#MM\/dd\/yyyy HH\:mm\:ss\#"



Armin

 

[Rich P]

Hi John,

I have done what you are trying to do. It is doable, but it is not a
one liner. Essentially, you need/want to execute some sql operations on
the fly based on the current rows you are working with. You have 2
options.

The 1st option is to go with the Rows retrieved from the
ds.Table.Select("StartDate >= '" & SomeStartDate.ToString & "' And
StartDate <= '" & someEndDate.ToString & "'")

Then loop through this datarow array into a dataTable you create in code
and populate the datatable. Kinda kludgy but doable.

The 2nd option is to push the data in your local datatable back to sql
server to a #tmp table and then query the #tmp table. This gives you a
little more flexibility. The caveat is that you have to create the #tmp
table in your code first (there is no escaping having to create a table
in code first - either a #tmp table or a local dataTable). Here is an
example:

---------------------------------------
'--da4 is a sqlDataAdapter

da4.SelectCommand.CommandText = "If (object_id('tempdb..#tmp1') is not
null) drop table #tmp1"
da4.SelectCommand.ExecuteNonQuery()

'--create #tmp1 in TempDB
da4.SelectCommand.CommandText = "Create Table #tmp1(FirstDate datetime,
ListNo varchar(2))"
da4.SelectCommand.ExecuteNonQuery()

'--now copy the #tmp1 structure to memory for app usage
da4.SelectCommand.CommandText = "Select * From #tmp1"
da4.Fill(dsFL, "tmpLocal")

da4.InsertCommand.CommandText = "Insert Into #tmp1(FirstDate, ListNo)
Select @FirstDate, @ListNo"

da4.InsertCommand.Parameters.Add("@FirstDate", SqlDbType.DateTime, 8,
"FirstDate")
da4.InsertCommand.Parameters.Add("@ListNo", SqlDbType.VarChar, 2,
"ListNo")

Dim reader As DataTableReader =
dataset1.YourLocalDataTable.CreateDataReader

'--set up the data push here
dataset1.Tables("tmpLocal").Load(reader, LoadOption.Upsert)

'--This is where you push the data
'--from yourLocalTable to #tmp1
da4.Update(dsFL, "tmpLocal")

'--now you can use tSql on #tmp1
da4.SelectCommand.CommandText = "Select * From #tmp1 Where Some
Condition"
da4.Fill(dataset1, "tblResult")

Datagridview1.DataSource = dataset1.Tables("tblResult")

Rich

 

[John Dann]

The format string I posted was:

Const DateTimeFormat As String = "\#MM\/dd\/yyyy HH\:mm\:ss\#"

Yes, I know. The thinking was that in your example mindate was
explicitly bounded by # characters. In my code, mindate is a
pre-assigned datetime type that, so far as I know, has no bounding #
characters, so I took them out. If I use the format string almost
exactly as you posted:

Const DateTimeFormat As String = "\#dd\/MM\/yyyy HH\:mm\:ss\#"

(pasted from my code) then I revert to the original 'string was not
recognised as a valid datetime' error. I've obviously interchanged dd
and MM compared to your example, but that's how datetime values seem
to appear to my system.

Maybe I should be adding bounding # characters to the formatted
values? ie instead of:

mindate.ToString(DateTimeFormat)

it should be

"#" & mindate.ToString(DateTimeFormat) & "#"

Sorry there's obviously something that I don't understand about the
exact usage and syntax of datetime values. But if you can spot any
further errors that I'm committing then I'd be most grateful.

 

[John Dann]

Ok, sorry - I just tried the filter string exactly as suggested with
MM and dd in the original order and it seems to work. (I've got some
more processing to do to be sure, but the expected number of rows are
being pulled out).

I guess datetime values are always initially processed internally in
US format even if the current locale does things differently.

 

[John Dann]

The 1st option is to go with the Rows retrieved from the
ds.Table.Select("StartDate >= '" & SomeStartDate.ToString & "' And
StartDate <= '" & someEndDate.ToString & "'")

Then loop through this datarow array into a dataTable you create in code
and populate the datatable. Kinda kludgy but doable.

This is what I'm currently trying to do, but seeing a problem with. I
was hoping I could do:

Dim Daily() As DataRow
Daily = MonthlyRawDT.Select(FilterString) ' FilterString set elsewhere

Dim DailyRawDT As DataTable = MonthlyRawDT.Clone()
For Each dr As DataRow In Daily
DailyRawDT.Rows.Add(dr)
Next

So:

MonthlyRawDT is my parent datatable from which I'm trying to extract a
subset of rows.

Daily() is the array of datarows populated by the .Select method.

Then I was thinking that I needed to instantiate a new datatable with
the same schema as the original parent datatable and iterate through
the datarows array adding one row at a time to the new datatable. But
I get the error that dr already belongs to another datatable.

Have I miscoded this (been a long day today!) or do I need to iterate
through each row item by item. That would be kludgy!

I guess VB2008 and LINQ really might make this exercise simpler?

 

[Armin Zingler]

Ok, sorry - I just tried the filter string exactly as suggested with
MM and dd in the original order and it seems to work. (I've got some
more processing to do to be sure, but the expected number of rows
are being pulled out).

I guess datetime values are always initially processed internally in
US format even if the current locale does things differently.

Maybe you got something wrong here. A DateTime value does not have any
format, or I'd better say it has only a binary format. It neither has "#"
nor is stored in a culture specific format.

Second, the "#" are there to mark date literals within the source code.
Still they are compiled as DateTime values without any specific format. The
format of the literal in the source code is always the English format.

The filter expression requires the English/US format either, enclosed in
"#". As this is required at two locations in the expression string, I put
the "#" into the format string. So, the following two versions are 100%
identical, however I chose the first one because you don't have to write the
two "#" twice.

Const DateTimeFormat As String = "MM\/dd\/yyyy HH\:mm\:ss"
'...
Dim FilterString As String = String.Format( _
"test > #{0}# AND test <#{1}#", _
mindate.ToString(DateTimeFormat), _
maxdate.ToString(DateTimeFormat) _
)
'or:

Const DateTimeFormat As String = "\#MM\/dd\/yyyy HH\:mm\:ss\#"
'...
Dim FilterString As String = String.Format( _
"test > {0} AND test <{1}", _
mindate.ToString(DateTimeFormat), _
maxdate.ToString(DateTimeFormat) _
)


Armin

 

[Rich P]

do a console.writeline (or debug.print) on your date vars to see what
kind of data value your are passing in to

ds.table.Select("StartDate = '"...

And don't forget that .Select(...) is the Where clause in a tsql
statement and uses the same syntax - you have to delimit your dates with
single quotes .Select("StartDate = '" & someDateVar.ToString & "'")

So the DateVar needs to be a string to fit into the Where Clause of
..Select("StartDate = '" & DateVar.ToString & "'")

The DataRow Array is OK if you only have a few rows to filter. But if
you have lots of rows (more than 50 say)you will be better off pushing
your data back to the server to a #tmp table and then use regular tsql
on the #tmp table. It may seem kludgy at first, but way more flexible
than looping through an array of datarows.


Rich

 

[James Hahn]

You can copy the datarows into a new table that has been created with the
same structure as the original. .ImportRow is probably the easiest way to do
this. See, for instance:
http://ziver.blogspot.com/2007/08/datarow-arrays-may-easily-be.html