oleDB: Datasets and Data Sources

[fixitchris]

DsPitem is the dataset which I manually bound at design time with the BindingSource Control.

 

'data access using OLEDB
       [b]Dim dc1 As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\chrism\Desktop\qs2.net test\db.mdb")[/b]        Dim da1 As New OleDb.OleDbDataAdapter(sSQL, dc1)
       Dim dr1 As DataRow
       da1.Fill(DsPitem, "Pitem")
       ListBox1.Items.Clear()
       For Each dr1 In DsPitem.Tables("Pitem").Rows
           ListBox1.Items.Add(dr1("item"))
       Next

 

 

1. instead of creating the OLEDBCONNECTION object, how can i make reference to the Connection object I created when creating datasets in Data Sources? I named it dbConn but i don't know how to use it in my code.

 

2. do i still need to call the SQL statement to the dataAdapter if i have datasets created in Data Sources?

 

The whole purpose for this project is to familiarize myself with ADO.NET.

A. Populate List Box with [item] from dsPitem dataset

B. Create Form2 on Listbox1_click event

C. Populate Listview on Form2 with records from dsRouting dataset that match the [item] field selected on form1.listbox

 

 

Any help with ADO.NET logic would be helpful.

[Joe Mamma]

1. instead of creating the OLEDBCONNECTION object' date=' how can i make reference to the Connection object I created when creating datasets in Data Sources?[/quote']What is 'Data Sources' ?

Unless you need to keep track of Transactions manually, Connections should not have a lifetime that goes beyond and atomic process. Connection Pooling will optimize things for you. . .

 

2. do i still need to call the SQL statement to the dataAdapter if i have datasets created in Data Sources?

As long as the DataAdapter is not destroyed after initial creation, no, the internal OleDBCommand objects are still available to you.

You can check to see if the SelectCommand object is null, or if not null, the command text is still there.

 

[indent]Dim dc1 As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\chrism\Desktop\qs2.net
testdb.mdb")        
Dim da1 As New OleDb.OleDbDataAdapter(sSQL, dc1)
Dim dr1 As DataRow
da1.Fill(DsPitem, "Pitem")
ListBox1.Items.Clear()
For Each dr1 In DsPitem.Tables("Pitem").Rows
[indent]ListBox1.Items.Add(dr1("item"))
[/indent]
Next 
[/indent]

Man. . . VBSux did so many people a disservice. Ther above is is an example of old vb coders being confounded by databinding.

[indent]Dim conn As New OleDb.OleDbConnection("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=localhost")
Try
[indent]Dim da As New OleDb.OleDbDataAdapter("select * from Categories", conn)
Try
[indent]Dim ds As New Data.DataSet
da.Fill(ds)
ListBox1.DataSource = ds.Tables(0)
ListBox1.DisplayMember = "CategoryName"
ListBox1.ValueMember = "CategoryID"
[/indent]
Finally

[indent]da.Dispose()
[/indent]
End Try
[/indent]Finally

[indent]conn.Dispose()
[/indent]
End Try
[/indent]

 

If in vbsux or vbnot you are iterating through a recordset to load grids/lists/collections, etc. . .

YOU ARE DOING IT WRONG!!!!!!!

[fixitchris]

You're right , it is a big mess migrating from vB6 to .net

 

 

Data Sources is the Data menu in VS2005 beta. Keeps track of datasets. Its the 1st picture I attached.

 

databinding = BAD?

 

If in vbsux or vbnot you are iterating through a recordset to load grids/lists/collections, etc. . .

YOU ARE DOING IT WRONG!!!!!!!

 

That is my intention. I want to load the database into collections. What is the best way of doing this... or is it even necessary since Datasets are available?

[fixitchris]

From the examples I have seen so far, I concluded that the best practice is to have a connection, dataadapter, dataset in each class... or basically wherever data reading/updating is neccessary. I see there is no real trick in .net where the data will appear magically. I guess i'm going to stick to programatically reading/updating data.

[Joe Mamma]

You're right , it is a big mess migrating from vB6 to .net

its not your fault. . .vbsux was just awful! and the advice alot of people give is dead wrong!

 

databinding = BAD?

No, databind=good. . . always has been good! just vbsux hid so much from the user and the help files were attrocious. I learned it in Delphi whci comes woith the source code that is Delphi. I highly recommend getting Delphi for the source code alone!

 

That is my intention. I want to load the database into collections.

hmmm. . .isnt a dataset a collection? a collection of datatables (among other things) and a datatable is, among other things, a collection of rows.

or is it even necessary since Datasets are available?

BINGO!!!!

and. . . heres the kicker. . . in vbsux, it wasn't necessary either, because. . . .

 

Recordsets are collections!!!! Just disconnect the recordset!

 

You look around the xtremevbtalk forum and you see "experts" RECOMMENDING getting a recordset, itterating through the recordset to load an array/collection and then iterating through that to load a grid.

 

HUH???? you got the collection - the recordset. just set the list's datassource to the recordset and "boom goes the dynamite!" the grid is loaded!!!!

[fixitchris]

how can I access a specific row in a datatable? Is there a key that i can rely on or do I have to iterate through each Row to find the record that I am looking for?

 

so each control has the capability of being data bound? just have to play around with the data members?

[Joe Mamma]

how can I access a specific row in a datatable? Is there a key that i can rely on or do I have to iterate through each Row to find the record that I am looking for?

look up:

DataRowCollection.Find

DataView.Find

DataTable.Find

 

 

so each control has the capability of being data bound? just have to play around with the data members?

boom goes the dynamite!!!

 

now, there are exceptions to every rule:

 

If you are working with a custom data object framework as a data access layer, you will iterate through the dataset/datatables and load the data into your custom objects, but your objects should implement IEditableObject while your custom lists should implement IBindingList.