dataset query connection string??

[themaj]

I have a dataset (GrindItems) with 4 datatables successfully populated from dbf files. If I understand the concept of a dataset, I no longer need an open connection to the source db and I should be able to query my dataset.

My first question is whether I need a Connection string OR can I just do this through a dataadapter OR whether I need both?

If I need a connection string ....what kind...example?

I need to query these tables with a UNION query and output query to a 5th datatable.

Is that a problem with datasets?

Here's the query and it works in Access with no problems

Dim mySQL As String

        mySQL = "SELECT GNDSALE.UNIT, venue.StoreName, GNDSALE.DOB, GNDSALE.PERIOD, PRD.NAME, GNDSALE.TYPE, GNDSALE.TYPEID, TDR.NAME, GNDSALE.AMOUNT" & _
            "FROM (venue INNER JOIN (TDR INNER JOIN GNDSALE ON TDR.ID=GNDSALE.TYPEID) ON venue.ID=GNDSALE.UNIT) INNER JOIN PRD ON GNDSALE.PERIOD=PRD.ID" & _
            "WHERE(((GNDSALE.TYPE) = 4))" & _
            "ORDER BY GNDSALE.TYPEID"

Thanks for your assistance

[G_Waddell]

Hi
Once you have a dataset object you do not need to connect to anything. But you have to fill it first e.g.

Dim DS As New DataSet
Dim cmd As New SqlCommand
Dim DA As SqlDataAdapter
'Assume you have set up a Sql connection MyConnection and a SQL Query in a string, SQLString
with cmd
    .Connection = myConnection
    .CommandType = CommandType.Text
    .CommandText = SqlString
end With
 if MyConnection.State <> ConnectionState.Open then MyConnection.open
 DA = New SqlDataAdapter
 DA.SelectCommand = cmd
 DA.Fill(DS)

I take it you are coming from using technologies like VB where everything was done with "live" connections and record sets through ADO?

Dataset was designed to get around all this once you have it populated, you only need to connect again to update data. You could think of it as a database (It's not but it's a good analogy)

Your Dataset should contain one or more datatables so to use an example from ADO, say I have a Recordset (Datatable,) I wish to parse through. So in VB I'd populate a Recordset and do something like this:

While not RS.EOF
   'do something
   RS.movenext
Wend

In VB.net I can now do this:

Dim DT as Datatable
dim DR as DataRow
DT = DS.Tables(0)
If DT.Rows.Count > 0 then
    for each DR in DT.rows
        'do something.. Instead of RS("") we can use DR("")         
    Next            
End if

If I want to create a new datatable in my dataset, I just do this:

Dim DT = New Datatable

With DT.Columns
.Add("Column1") 'I could specify type etc. here as well
.Add("Column2")
End with
'I could also give my new datatable a name and refer to it as DT.Tables("<myName>") 
'Instead of DT.Tables(i)
DS.Tables.Add(DT) 

So You could run a union query on the Database server and populate as a Datatable or if you have the other tables anyway you could create a new datatable in your dataset and populate it in code.

I should also point out if you need simply a result set of record to run through in order and not post anything back to the database with you should maybe use a datareader and have no need for a dataset (you can think of a datareader as a disconnected Recordset) In fact, If I need to add new datatables to a dataset I sometimes just use a datareader..

[themaj]

G_Waddell,

I'm following you here and I have already filled my 4 datatables from my DBF tables. Now the issue really is HOW to query these tables with a JOIN or UNION.

At the end of the day I want to solve for this simple example:

I have Dataset with 2 Datatables; Car & Color.
Car has "ModelID", "ColorID"
Color has "ColorID", "ColorText"

I want to query these tables and write the results to a new table "Results" with ModelID, ColorID, ColorText WHERE 'ColorID' = 4

While I mention this let me go back and ask whether it is possible to do a single SELECT query involving more than 1 dbf tables because that would make life so much easier. Is that possible in VB.NET.

If so how would I construct the connection string?

[Pgmer]

Cant you prepare the Select statement with Joins and UNION at database side? And just get the result set what u need...

[G_Waddell]

As Pgmer Says you can carry out joins in your SQL query which would probably be the quickest and least memory intensive option.
OR

if in this instance you can not, you can go through one table and use the Select method on the other table i.e. DataTable.Select("My search criteria") this will give a collection of datarows in the table that match the criteria
for example:

Dim DT1, DT2 as datatable
Dim CustID as Integer
dim CustOrders() As DataRow
DT1 = ds.Tables("Customer")
DT2 = ds.Tables("Orders")

For Each DR as Datarow in DT1.Rows
    CustID = DR("CustomerID")
    CustOrders = DT2.SELECT("CustomerID = " &CustID )
    For Each Order AS datarow in CustOrders  
     'Do whatever
    Next
Next

OR

Use a dataRelation object to Join the tables in the dataset then get the child rows of the parent table:

Dim parentColumn As DataColumn = _
        DS.Tables("Customers").Columns("CustID")
    Dim childColumn As DataColumn = DS.Tables( _
        "Orders").Columns("CustID")

    ' Create DataRelation.
    Dim relCustOrder As DataRelation
    relCustOrder = New DataRelation( _
        "CustomersOrders", parentColumn, childColumn)

    ' Add the relation to the DataSet.
    DS.Relations.Add(relCustOrder)
Dim Orders() as DataRow
dim OrderNo as Integer
For Each Customer As Datarow in DS.Tables("Customers").Rows
    Orders = Customer.GetChildRows(relCustOrder)    
    For i as integer = 0 to ubound(Orders)  
        OrderNo = Orders(i).Item("OrderNo")
    Next
Next

[themaj]

Pgmer,
Never really worked with multiple DBF files in a single query before, always used MDBs. I'm not sure how how you create that type of connection string but I'm certainly willing to listen and learn that one.

I did import the DBFs into a MDB and successfully created that the JOIN & UNION query but I can't keep flipping between DBF & MDB all the time, especially since most the work I'm doing right now is with DBFs. Fire away, please!

[themaj]

G_Waddell,

Yeah, these I can understand and work with, thanks very much. I had been told before that I would need to use LINQ to accomplish what I wanted. But I'm always willing to try new ways and these makes sense. Appreciate your response.

[G_Waddell]

I think under most circumstances the Datarelation is the way to go, especially if you are creating jions between multiple tables.

[themaj]

G_Waddell, read through this DataRelation but I'm still a little confused. Since you suggested this as the way to go I want to really get it right.

First, having implemented this (and this particular part doesn't error), how do I use this relationship to get results of a two table query?

At the end of the day I want to solve for this simple example:

I have Dataset with 2 Datatables; Car & Color.
Car has "ModelID", "ColorID"
Color has "ColorID", "ColorText"

I want to query these tables and write the results to a new table "Results" with ModelID, ColorID, ColorText.

I'm trying to keep this sample as simple as possible so anyone else seeing this doesn't get confused with my actual datatables.

I'm still also interested in a Select statement with Joins and UNION at database side for 2 DBF tables. I've always been screwed up on the correct ConnectionString when using 2 DBF tables.

[G_Waddell]

Hi,

I've only ever connected to Relational Databases never flat files so I can't really help you there. However, once you have figured out how to put your data to a dataset, you would be hopefully able to something like this:

dim DS as Dataset
Dim DTCars, DTColors as Datatables

DS = new Dataset
'fill dataset
DTCars = DS.Tables(0)
DTColors = DS.Tables(1)
'need to hook into the actual columns as datacolumns
Dim parentColumn As DataColumn = DTColors.Columns("ColorID") 
Dim childColumn As DataColumn = DTCars.Columns("ColorID")
dim relColorCar As DataRelation = new DataRelation("ColorCars", parentColumn, childColumn)
DS.Relations.add(relColorCar)
'Get colours and which models are offered in this colour:
dim ColorRecord as DataRow
dim CarRecord() as DataRow
dim Output as String
For Each ColorRecord in DTColors.Rows
    Output = ColorRecord("ColorText") 
    CarRecord = ColorRecord.GetChildRows("CarColor")
    If CarRecord.Count > 0 then
     Output +=" is offered on these models:" &vbcrlf
     For each DR as datarow in CarRecord
        Output += DR("ModelID") &", "
     next    
    else
        Output += " is not available on any models"
    end if
Next

You may run into difficulty trying to do the joins as multiple cars will have multiple colour records... I'm not 100% sure it wont freek out over that.

[themaj]

G_Waddell,

Trying to figure this out but having an error when it hits Line 11 (above);

"Cannot create a DataRelation if Parent or Child Columns are not in a DataSet"

Now I'm pretty damn sure they are both in a the DataSet because I've populated 2 DataGridViews with the tables following the 'myAdapter(Fill,DataTable)'

What is more puzzling to me is I keep getting '0' when I try to get a DataSets.Tables.Count after my Fill statement and adding the data to the Grids. How can that be?

:(

[G_Waddell]

Interesting, it would suggest something is emptying the dataset somewhere..

Dataset.Tables.count would only give zero if there were no datatables in it.

I think you better post your code.

One thing you could try is to go into debug mode and put a condition breakpoint in on Dataset.tables.count ie. when this value changes break. If should then break as the data set is populated and where ever it empties...

[themaj]

G_Waddell,

Disregard my last problem; I was filling the Grids with apparently 2 unrelated Datatables. I had not done a myDataSet.Tables.Add(), so I guess I was loading the Grids with two different DataSets. Oooops, wish there was a Dummies Guide to DataSets :)

I up to the Output part in your code above (Line 17) trying to understand what's happening here. Thanks again

[M.Waqas Aslam]

i think you can join datatables using Linq queries ,i dont know much about it but you can google it ,

regards

[G_Waddell]

If you cannot do it via datarelations (I suspect perhaps due to the M:M nature of the data in the tables,) try my Datatable.select example I gave earlier...

[themaj]

I'm going to call this thread solved after finally figuring out the ConnectionString. All the other methods are fine but the original question is complete.

[M.Waqas Aslam]

can you please post your code which solved your prob to help others ?

[themaj]

Sure, here's the code with explanation.

The original problem was to extract data from multiple DBF tables with a JOIN in the SELECT statement but I did not know the exact format and or properties of the ConnectionString to query multiple tables at once. In exploring all the suggestions above I did end up with a DataSet and muliple DataTables but I just didn't have the experience and knowledge to successfully query the DataSet.
So I took Pgmer & G_Waddell's suggestion and decided to return to the basics; the Connection String. Ended up being a lot more efficient with a lot less complicated code involved. I'm sure I'll evolve over time and learn the other methods but this does exactly what I needed.

The bottom line here (the part I didn't understand) was the correct 'Provider=Microsoft.Jet.OLEDB.4.0' and pointing to the entire directory; the previous tests with Provider=VFPOLEDB.1 just wouldn't do it.

I do want to express my appreciation to everyone who commented through this thread; I was getting very frustrated :(

Public Sub sGetData()
        Dim myConn As String 'Connection String
        Dim mySQL As String ' Query String
        Dim databin As String = "20120531" ' Test Directory

        myConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Application.StartupPath + "\" + databin + "\ ;Extended Properties=dBASE IV;User ID=Admin;Password="

        mySQL = "SELECT GNDITEM.UNIT, GNDITEM.DOB, GNDITEM.PERIOD, GNDITEM.ITEM, ITM.LONGNAME, GNDITEM.CATEGORY, CAT.NAME, GNDITEM.QUANTITY, GNDITEM.PRICE, GNDITEM.DISCPRIC " + _
                    "FROM ((CAT INNER JOIN GNDITEM ON CAT.ID = GNDITEM.CATEGORY) INNER JOIN ITM ON GNDITEM.ITEM = ITM.ID) INNER JOIN PRD ON GNDITEM.PERIOD = PRD.ID " + _
                    "ORDER BY GNDITEM.PERIOD;"
        'MsgBox(strquery)

        Dim dsquery As DataSet
        dsquery = getDataSet(myConn, mySQL)
        Me.DataGridView1.DataSource = dsquery.Tables(0)
        Me.DataGridView1.Refresh()
        TextBox1.Text = Me.DataGridView1.RowCount - 1
    End Sub

    Private Function getDataSet(ByVal dbconnect As String, ByVal querystring As String)
    Dim dsContainer As New DataSet
    Dim conn As New System.Data.OleDb.OleDbConnection
    conn.ConnectionString = dbconnect

    Dim command As New System.Data.OleDb.OleDbCommand
    command.CommandText = querystring
    command.Connection = conn

    Dim adp As New System.Data.OleDb.OleDbDataAdapter
    adp.SelectCommand = command


    Try
        conn.Open()
        adp.Fill(dsContainer)

        conn.Close()
    Catch ex As Exception
        MessageBox.Show(ex.ToString)
        conn.Close()
    End Try
    Return dsContainer


End Function