Data Tables, querying using SQL and returning results to a new data table.

What you're describing is an in-memory SQL engine...  

Take a look at the following article that describes how you can use the ordinary SQL Select command against DataTables in a "disconnected" DataSet without going back to the underlying database.

http://home.hot.rr.com/graye/Articles/SQL_Engine.htm

The article also has downloadable VB.Net source code for the SQL_Engine demonstration project.

Thanks guys, both look promising I cannot get to this project for a day or so I will get to them as soon as I can and give you feedback.

Regards,
Neil

HTH,

I have tried the code you have suggested but it does not produce the result I require.  It populates the datagridview with the records that contain only the first entry in the parent table.  In the example this would be the first StateID.
It also does not show the value of the parent table the should be substituted into the child table.  So in my example it is only showing the StateID not the string from the States table.

So I get a result like

Field1, Feild2, StateID, Field3
Value, Value, 1         , Value
Value, Value, 1         , Value
Value, Value, 1         , Value

When I used the good ol' SQL query on the source database I get something like below which is what I want.  
Field1, Feild2, StateID, Field3
Value, Value, Ohio    , Value
Value, Value, Essex  , Value
Value, Value, Ohio    , Value
Value, Value, Tasmania  , Value
Value, Value, Ohio    , Value
Value, Value, Essex  , Value
Value, Value, Essex  , Value
Value, Value, Tasmania  , Value
Value, Value, Tasmania  , Value

Incidentally I am not working with Users and States but this is a simple example.

I have read again about datarelations and it still seems to me they are all about using code to loop through child tables for a given value of a parent table, as opposed to getting a datasource that shows all the results.

I am missing something?

Neil

graye,

I was initially excited about your solution but I noticed you said that ...
"...there is no support for any DDL statements (ALTER, CREATE, or DROP) nor any other DML statements (INSERT, UPDATE, or DELETE) ..."

The second part of my "cunning plan" described above, was that I would detect changes in the related data, then work out which "source" table records needed updating and do so in memory.  Then I would use their dataadapters to update the database.  I am correct in assuming that your SQL_Engine does not support the updating of datatables in memory?

Neil

Hi

When you create your dataset, do not use your query above, but simply retrieve the records for Users and States and merge them,


for example

ds.Merge(<the users table>)
ds.Merge(<the states table>)

then build the datarelation.

I am doing this myself using winForms .NET2003 and works quite well

My SQL_Engine will take the 2 dataTables and produce a 3rd DataTable.   Just like in a "real" database, the result set has no ties back to its source.   It's a one-way proposition.

I noticed that your example was just a simple read-only "look up" for column.   Is that indicative of the real data?   If so, you may be able to accomplish the same concepts with just a DataRelation and some clever tricks with the DataGrid class.   That way the user will be able to edit the data and have it saved back in the normal way.

HTH,

Sorry I was off line for a while.  I tried using the merge above then build the relation.  The net result was the same as before.

I created a new dataset as below, used the merge command on the tables in the existing dataset I had, without error.  (I am now showing the real project names)

        Dim dsMainNew As New System.Data.DataSet
        dsMainNew.Merge(dsMain.Tables("Images"))
        dsMainNew.Merge(dsMain.Tables("Drives"))


Then I set the relation as below
        Dim MyRelNew As DataRelation
        MyRelNew = New DataRelation("MainRelNew", dsMainNew.Tables("Drives").Columns("DriveNum"), dsMainNew.Tables("Images").Columns("DriveNum"))
        dsMainNew.Relations.Add(MyRelNew)

Then I set the datagridview source as below.
        Me.dgvMain.DataSource = dsMainNew
        Me.dgvMain.DataMember = "Drives.MainRelNew"

The datagridview shows all the records from the Images table that has the DriveNum from the first record in the Drives table.  There is no substitution of the DriveNum value with the DriveLet (the other field in the Drives Table).

I read up on the merge command and it seems to me to be more about updating changes from one object to another instance of the same object that may have different records in it.

I am sorry if I am missing the point but I still can't get this to work.

Neil

graye,

Thanks for the confirmation.  I guess I am a bit reluctant to step outside the standard functionality offered by VS.NET.  This is not a reflection on your work, but more on my inexperience.

I am interested in following up your comments about " ... just a simple read-only "look up" for column.   Is that indicative of the real data?"
The answer is that there is a table called Images that will have 3 or more "look ups".  However the users must be able to add values to each of these tables.  For example on table is States and if a record is enterred for which a state does not exist, the user needs to be able to create the record in the States Table.

You, like HTH, mention a DataRelation but as you can see in my comments to them, I can only seem to get records shown from the "child" table for the first record in the "parent" table.

Whatever the outcome of how to display the related data.  I am still assuming once my code detects changes/additions are required in any of the four or more tables in the dataset, that I will be able to locate the record needing changing, and make the change in memory, then eventuall call the update method of the dataadapter for each table.

Do you see a problem with this?

Neil

First of all, the Merge() command is for reconciling changes/additions/deletions for rows in a table.   So, it is not designed to do an SQL JOIN type operation.

OK, if you want to stick with just the "store bought" solution, then you could try using DataColumn expressions to perform the "lookup" for the related rows.   Here is an example from http://home.hot.rr.com/graye/Articles/DataRelations.htm

      ' create a relationship between two of the tables
      ' arguments are Name, ParentCol, ChildCol, create_foreign_key
      ds.Relations.Add(New DataRelation("CategoryProducts", ds.Tables("Categories").Columns("CategoryID"), ds.Tables("Products").Columns("CategoryID"), False))

      ' add a column from a related table.
      dc = New DataColumn("CategoryName")
      dc.DataType = GetType(String)
      dc.Expression = "Parent(CategoryProducts).CategoryName"
      ds.Tables("Products").Columns.Add(dc)

      ' Since a Parent relationship can only return one row, this is a safe expression. However,
      ' if you had a child relationship, you'd have to include an aggregate in order to guarantee
      ' that the function would return just one row"Max(Child(CategoryProducts).UnitPrice)"
      DataGrid1.DataSource = ds.Tables("Products")  
       



 

graye,

Apologies for the delay, this is a job I cannot focus on a lot for now.   Plus I am still very much learning .NET and database connections.  Now the grovelling is out of the way THANKS HEAPS!  Your solution is great for me.  

I have started to read your web site as well.  You present the ideas well.  Congratulations on that.

I didn't realise you could add columns to a DataSet.DataTable without mucking up the ability to use the command builder for the original table to get your updates back to the database.

Plus, as a novice, I had no idea there was a "methodology" involving the DataColumn.Expression that could so neatly emulate a join like you have shown.  It is even interactive on the DataGridView ie if I change the ID field from the child table the related value is updated immediately, fantastic.

Before I accept your answer, I hope you won’t mind me being a bit cheeky and asking your advice on the next directly related problem I face now.

Thanks to your help I can now present a DataGridView to users that has the joined values and only shows the relevant fields to the user.  So the ID field in the child table is hidden and only corresponding values are shown from the parent tables.

I want to be able to offer the user either editing via another form which handles a single records at a time with some smarts to help them, or editing directly on the DataGridView.

The single form will be relatively easy (I believe) for me to control the issue of displaying values from the parent tables.  One such table is Countries which has CountryID field and a CountryVal field.  So I am picturing I will show a combo box of the current values with the correct one highlighted for the record.  If they change the value in the combo box (CountryVal) or add a new one, I will change the CountryID value in the child table and update the Countries table if necessary.

However, I would very much appreciate your thoughts on how to combine a combo box selection with the DataGridView.  As my project stands now, I can make the DataGridView editable for all the fields that are in my child table as gathered from the database.  However, not surprisingly the “joined fields” like CountryVal are not editable in the DataGridView.

Do you have any suggestions as to how, upon entry into the CountryVal column, they could be presented with a combo box of options.  It would be OK to me if adding a new Parent record was not allowed when editing on the DataGridView.

Any ideas?

I will understand if you wish me to issue this as separate question.  Let me know.

Neil