SQL Queries on standalone tables

[Lev Blaivas]

Hi,

I have 2 data tables filled with data in my program, that I need to join into one and display it. The problem is that using the .merge method of the table does not allow me to filter out repeating rows. Using a fully functional SQL command would solve this problem but I can not find a way to execute an SQL command on standalone tables without a database. And taking into account the fact that both tables contain more that 100,000 rows, using loops does not seem to be the best option. Additionally the tables are filled programmatically and not from a database.

Does anyone know of a way to perform SQL queries without accessing a database? Is it possible at all?

 

[jmcilhinney]

No it's not possible in .NET 2.0. It is in .NET 3.5 with LINQ. You'll just have to use a loop.

 

[giadich]

put the two datatables in a dataset and define a relationship between the tables then you can run query against it.

 

[Lev Blaivas]

put the two datatables in a dataset and define a relationship between the tables then you can run query against it.

It still doesn't let me run queries agains it =(

No it's not possible in .NET 2.0. It is in .NET 3.5 with LINQ. You'll just have to use a loop.

A bit slow but it looks like I have o other choice, thanks!

 

[jmcilhinney]

Just note that you do have some limited options. The DataTable.Select method allows you to get an array of DataRows based on what is essentially an SQL WHERE clause. You can also specify a sort order but you cannot specify a subset of columns and you cannot join tables. You can also use the DataTable.Compute method to execute an aggregate function on a single column.

 

[giadich]

you should be able to do this via when you add the tables to the dataset, define the relationship for the tables, and then define the view to pull the info together

 

[cjard]

Hi,

I have 2 data tables filled with data in my program, that I need to join

Be careful to use the correct terms. You say "join" but you actually appear to be talking about a "union" - the two are very different operations

The problem is that using the .merge method of the table does not allow me to filter out repeating rows.

Funny, when I use merge on two tables that have a proper primary key set up, the second table will update any rows in the first where the primary key values match.

Using a fully functional SQL command would solve this problem

I think you mean to say that using a full functional database would be a better way to solve this problem

but I can not find a way to execute an SQL command on standalone tables without a database.

That's because DataTables are not databases. SQL is a query language inherently available in databases. Complaining that datatable dont support it is a bit like taking issue with windows not running native linux binaries

And taking into account the fact that both tables contain more that 100,000 rows, using loops does not seem to be the best option.

Indeed.. Hashtables would be far better. Databases implement hashtables very well and are suited to processing this volume of data. A client software may not be.

Does anyone know of a way to perform SQL queries without accessing a database? Is it possible at all?

Using the right tools for the job would be better.. but your most significant struggle seems to be because your datatables have no primary key, and hence .NET cannot leverage the proper flexibility of the underlying hashtable implementation

 

[cjard]

You'll just have to use a loop.

Are you nuts? 100,000 records per table, and you want to visit every record in B, per record in A, just to checkwhether it's there or not? That's 10,000,000,000 accesses.. or On^2 - yuck

http://www.google.co.uk/search?sour...=GGLG,GGLG:2006-25,GGLG:en&q=hashtable+theory