Database Connection Process

[steve65]

Hi all I am new to .NET and some what familiar with data connections using VB6. As I understand it this is the process in .NET to connect and use a database.

You need a OleDbConnection object to connect to the database.

An OleDbDataAdapter object that tells what data you want out of the database that you made a connection with using the OleDbConnection object.

Lastly you need DataSet which "holds" the data you are working on.

Is this all correct first off?

If so does the DataSet object actually have the data from the database in memory or is it just a reference to the data?

[dynamic_sysop]

there are various ways to do it , i presume you are using an access .mdb not sql when i give you these examples
way 1 , using oledbDataReader :

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyDb.mdb"
        Dim objConnection As New OleDbConnection(connString)
 
        Dim commString As String = "Select * From Table1"
        Dim objCommand As New OleDbCommand(commString, objConnection) '/// get the table you want.
 
        objConnection.Open() '/// opens the connection.
        Dim objReader As OleDbDataReader = objCommand.ExecuteReader()
        While objReader.Read
            Console.Write(objReader.GetInt32(0) & " " & objReader.GetString(1)) '/// the info in your table.
        End While
        objReader.Close()
        objConnection.Close()
    End Sub

or the better way ( using a DataGrid on a form )

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyDb.mdb"
        Dim objConnection As New OleDbConnection(connString)
        Try
            objConnection.Open() '/// opens the connection.
            Dim commString As String = "Select * From Table1"
            Dim objCommand As New OleDbCommand(commString, objConnection) '/// get the Table you want.
            Dim objAdapter As New OleDbDataAdapter(objCommand)
            Dim objDataSet As New DataSet()
            objAdapter.Fill(objDataSet, "Table1") '/// the new DataSet now holds your Table.
 
            DataGrid1.DataSource = objDataSet.Tables("Table1") '/// fill the datagrid with your Table.
 
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            objConnection.Close()
        End Try
    End Sub

[Pirate]

When you fill your dataset obj from the source using the oleadapter , you are making an offline copy of the source which is in memory ofcourse , this regarding dataset obj . Yes , The connection obj is a must in database app . The above code should gives you live example of what I'm talking about .

[dynamic_sysop]

hey Pirate , one thing i've wanted to say for ages to you lol , Hope the Twins that me and the wife are having in 4 weeks time dont do like your image by your name

[Pirate]

... ....

[steve65]

Thanks for the reply guys.

If the dataset is filled with a copy of the data, what happens when your query returns a really large selection?

[Edneeis]

Originally posted by steve65
Thanks for the reply guys.

If the dataset is filled with a copy of the data, what happens when your query returns a really large selection?

..The dataset fills up with a lot of data.

[steve65]

Originally posted by Edneeis
..The dataset fills up with a lot of data.

Funny, very Funny I just didn't know if there was some type of limitation. I have a memo field in my database and I would be pulling that if I did a SELECT * with the entire table. Anyway at this point I guess I will pull one row at a time.