VB.NET and ADO Advice Needed 
Author Message
 VB.NET and ADO Advice Needed

Being new to VB.NET from the old world of VB3-6 I have a question regarding
the best/proper way of doing database access against an Access database
under VB.NET.

Currently I am doing the following (see below). Is this approach correct
under .NET or by using ADODB am I mixing old and new?  How should this be
done under VB.NET?  Any help/suggestions would be appreciated.
Basically I want to query an Access database, load data, do adds, edits,
deletes, etc.

Dim adoConn As ADODB.Connection = New ADODB.Connection()
Dim strConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & "D:\test.mdb"
Dim adoRS As ADODB.Recordset = New ADODB.Recordset()

adoConn.Open(strConnection, "", "", -1)
adoRS.Open("SELECT * FROM People", adoConn,
ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly,
1)

Do While Not adoRS.EOF
    Dim objPerson As New clsPerson()
    objPerson.FirstName = adoRS("FirstName").Value
    objPerson.LastName = adoRS("LastName").Value
    colPeople.Add(objPerson, adoRS("PeopleID").Value)
    adoRS.MoveNext()
Loop

adoRS.Close()



Wed, 24 Nov 2004 03:35:58 GMT  
 VB.NET and ADO Advice Needed
YOu should really move to ADO.Net and use the dataset or the datareader.
You can fill the dataset or datareader with the oledbClient.

If you are going to do what you have below then you might want to implement
something like this.

Dim cn As New System.Data.SqlClient.SqlConnection("Initial Catalog=;Data
Source=;User ID=CMEWeb;Password=cmeweb")

Dim cmd As New System.Data.SqlClient.SqlCommand()

Dim stReader As System.Data.SqlClient.SqlDataReader

cn.Open()

cmd.Connection = cn

cmd.CommandText = "SELECT * FROM People"

cmd.CommandType = CommandType.Text

stReader = cmd.ExecuteReader()

While stReader.Read

    DIm objPerson As New clsPerson()

    objPerson.FirstName = stReader.Item("FirstName").ToString

    objPerson.LastName = stReader.Item("LastName").ToString

    colPeople.Add(objPerson,stReader.Item("PeopleID").toString

End While


Quote:
> Being new to VB.NET from the old world of VB3-6 I have a question
regarding
> the best/proper way of doing database access against an Access database
> under VB.NET.

> Currently I am doing the following (see below). Is this approach correct
> under .NET or by using ADODB am I mixing old and new?  How should this be
> done under VB.NET?  Any help/suggestions would be appreciated.
> Basically I want to query an Access database, load data, do adds, edits,
> deletes, etc.

> Dim adoConn As ADODB.Connection = New ADODB.Connection()
> Dim strConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
> Source=" & "D:\test.mdb"
> Dim adoRS As ADODB.Recordset = New ADODB.Recordset()

> adoConn.Open(strConnection, "", "", -1)
> adoRS.Open("SELECT * FROM People", adoConn,
> ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly,
> 1)

> Do While Not adoRS.EOF
>     Dim objPerson As New clsPerson()
>     objPerson.FirstName = adoRS("FirstName").Value
>     objPerson.LastName = adoRS("LastName").Value
>     colPeople.Add(objPerson, adoRS("PeopleID").Value)
>     adoRS.MoveNext()
> Loop

> adoRS.Close()



Wed, 24 Nov 2004 06:40:02 GMT  
 VB.NET and ADO Advice Needed
Thanks that is what I was looking for, how to move this sort of code to
ADO.NET
I'll try out your example, thanks.


Quote:
> YOu should really move to ADO.Net and use the dataset or the datareader.
> You can fill the dataset or datareader with the oledbClient.

> If you are going to do what you have below then you might want to
implement
> something like this.

> Dim cn As New System.Data.SqlClient.SqlConnection("Initial Catalog=;Data
> Source=;User ID=CMEWeb;Password=cmeweb")

> Dim cmd As New System.Data.SqlClient.SqlCommand()

> Dim stReader As System.Data.SqlClient.SqlDataReader

> cn.Open()

> cmd.Connection = cn

> cmd.CommandText = "SELECT * FROM People"

> cmd.CommandType = CommandType.Text

> stReader = cmd.ExecuteReader()

> While stReader.Read

>     DIm objPerson As New clsPerson()

>     objPerson.FirstName = stReader.Item("FirstName").ToString

>     objPerson.LastName = stReader.Item("LastName").ToString

>     colPeople.Add(objPerson,stReader.Item("PeopleID").toString

> End While



> > Being new to VB.NET from the old world of VB3-6 I have a question
> regarding
> > the best/proper way of doing database access against an Access database
> > under VB.NET.

> > Currently I am doing the following (see below). Is this approach correct
> > under .NET or by using ADODB am I mixing old and new?  How should this
be
> > done under VB.NET?  Any help/suggestions would be appreciated.
> > Basically I want to query an Access database, load data, do adds, edits,
> > deletes, etc.

> > Dim adoConn As ADODB.Connection = New ADODB.Connection()
> > Dim strConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
> > Source=" & "D:\test.mdb"
> > Dim adoRS As ADODB.Recordset = New ADODB.Recordset()

> > adoConn.Open(strConnection, "", "", -1)
> > adoRS.Open("SELECT * FROM People", adoConn,
> > ADODB.CursorTypeEnum.adOpenForwardOnly,

ADODB.LockTypeEnum.adLockReadOnly,

- Show quoted text -

Quote:
> > 1)

> > Do While Not adoRS.EOF
> >     Dim objPerson As New clsPerson()
> >     objPerson.FirstName = adoRS("FirstName").Value
> >     objPerson.LastName = adoRS("LastName").Value
> >     colPeople.Add(objPerson, adoRS("PeopleID").Value)
> >     adoRS.MoveNext()
> > Loop

> > adoRS.Close()



Wed, 24 Nov 2004 06:47:24 GMT  
 VB.NET and ADO Advice Needed
Which is the better object to use the dataset or datareader?


Quote:
> YOu should really move to ADO.Net and use the dataset or the datareader.
> You can fill the dataset or datareader with the oledbClient.

> If you are going to do what you have below then you might want to
implement
> something like this.

> Dim cn As New System.Data.SqlClient.SqlConnection("Initial Catalog=;Data
> Source=;User ID=CMEWeb;Password=cmeweb")

> Dim cmd As New System.Data.SqlClient.SqlCommand()

> Dim stReader As System.Data.SqlClient.SqlDataReader

> cn.Open()

> cmd.Connection = cn

> cmd.CommandText = "SELECT * FROM People"

> cmd.CommandType = CommandType.Text

> stReader = cmd.ExecuteReader()

> While stReader.Read

>     DIm objPerson As New clsPerson()

>     objPerson.FirstName = stReader.Item("FirstName").ToString

>     objPerson.LastName = stReader.Item("LastName").ToString

>     colPeople.Add(objPerson,stReader.Item("PeopleID").toString

> End While



> > Being new to VB.NET from the old world of VB3-6 I have a question
> regarding
> > the best/proper way of doing database access against an Access database
> > under VB.NET.

> > Currently I am doing the following (see below). Is this approach correct
> > under .NET or by using ADODB am I mixing old and new?  How should this
be
> > done under VB.NET?  Any help/suggestions would be appreciated.
> > Basically I want to query an Access database, load data, do adds, edits,
> > deletes, etc.

> > Dim adoConn As ADODB.Connection = New ADODB.Connection()
> > Dim strConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
> > Source=" & "D:\test.mdb"
> > Dim adoRS As ADODB.Recordset = New ADODB.Recordset()

> > adoConn.Open(strConnection, "", "", -1)
> > adoRS.Open("SELECT * FROM People", adoConn,
> > ADODB.CursorTypeEnum.adOpenForwardOnly,

ADODB.LockTypeEnum.adLockReadOnly,

- Show quoted text -

Quote:
> > 1)

> > Do While Not adoRS.EOF
> >     Dim objPerson As New clsPerson()
> >     objPerson.FirstName = adoRS("FirstName").Value
> >     objPerson.LastName = adoRS("LastName").Value
> >     colPeople.Add(objPerson, adoRS("PeopleID").Value)
> >     adoRS.MoveNext()
> > Loop

> > adoRS.Close()



Wed, 24 Nov 2004 06:48:02 GMT  
 VB.NET and ADO Advice Needed
Performance-wise:
It depends on what kind of work you're going to do on the data.
A dataset is a disconnected set of tables from the database.  You
can get some database performance gains by using the dataset
object if you are doing a lot of manipulation of the data
as you're not accessing the database server anymore after
you fill the dataset.

Feature-wise:
Disconnected datasets allow you some functionality such as
easily making the data editable and updateable to the database.

HTH,

Lanik


Quote:
> Which is the better object to use the dataset or datareader?



> > YOu should really move to ADO.Net and use the dataset or the datareader.
> > You can fill the dataset or datareader with the oledbClient.

> > If you are going to do what you have below then you might want to
> implement
> > something like this.

> > Dim cn As New System.Data.SqlClient.SqlConnection("Initial Catalog=;Data
> > Source=;User ID=CMEWeb;Password=cmeweb")

> > Dim cmd As New System.Data.SqlClient.SqlCommand()

> > Dim stReader As System.Data.SqlClient.SqlDataReader

> > cn.Open()

> > cmd.Connection = cn

> > cmd.CommandText = "SELECT * FROM People"

> > cmd.CommandType = CommandType.Text

> > stReader = cmd.ExecuteReader()

> > While stReader.Read

> >     DIm objPerson As New clsPerson()

> >     objPerson.FirstName = stReader.Item("FirstName").ToString

> >     objPerson.LastName = stReader.Item("LastName").ToString

> >     colPeople.Add(objPerson,stReader.Item("PeopleID").toString

> > End While



> > > Being new to VB.NET from the old world of VB3-6 I have a question
> > regarding
> > > the best/proper way of doing database access against an Access
database
> > > under VB.NET.

> > > Currently I am doing the following (see below). Is this approach
correct
> > > under .NET or by using ADODB am I mixing old and new?  How should this
> be
> > > done under VB.NET?  Any help/suggestions would be appreciated.
> > > Basically I want to query an Access database, load data, do adds,
edits,
> > > deletes, etc.

> > > Dim adoConn As ADODB.Connection = New ADODB.Connection()
> > > Dim strConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
> > > Source=" & "D:\test.mdb"
> > > Dim adoRS As ADODB.Recordset = New ADODB.Recordset()

> > > adoConn.Open(strConnection, "", "", -1)
> > > adoRS.Open("SELECT * FROM People", adoConn,
> > > ADODB.CursorTypeEnum.adOpenForwardOnly,
> ADODB.LockTypeEnum.adLockReadOnly,
> > > 1)

> > > Do While Not adoRS.EOF
> > >     Dim objPerson As New clsPerson()
> > >     objPerson.FirstName = adoRS("FirstName").Value
> > >     objPerson.LastName = adoRS("LastName").Value
> > >     colPeople.Add(objPerson, adoRS("PeopleID").Value)
> > >     adoRS.MoveNext()
> > > Loop

> > > adoRS.Close()



Sat, 27 Nov 2004 01:07:31 GMT  
 VB.NET and ADO Advice Needed
It seems to be the general consensus that datasets are the way to go.  Where
I am getting confused is how they work with the business objects.  Most
people seem to be accessing them directly in the UI layer and not using a
business object, such as clsUSer for example to hold the details of a given
user record.
Does anyone have a link to a good article on using datasets with a business
object layer?


Quote:
> Performance-wise:
> It depends on what kind of work you're going to do on the data.
> A dataset is a disconnected set of tables from the database.  You
> can get some database performance gains by using the dataset
> object if you are doing a lot of manipulation of the data
> as you're not accessing the database server anymore after
> you fill the dataset.

> Feature-wise:
> Disconnected datasets allow you some functionality such as
> easily making the data editable and updateable to the database.

> HTH,

> Lanik



> > Which is the better object to use the dataset or datareader?



> > > YOu should really move to ADO.Net and use the dataset or the
datareader.
> > > You can fill the dataset or datareader with the oledbClient.

> > > If you are going to do what you have below then you might want to
> > implement
> > > something like this.

> > > Dim cn As New System.Data.SqlClient.SqlConnection("Initial
Catalog=;Data
> > > Source=;User ID=CMEWeb;Password=cmeweb")

> > > Dim cmd As New System.Data.SqlClient.SqlCommand()

> > > Dim stReader As System.Data.SqlClient.SqlDataReader

> > > cn.Open()

> > > cmd.Connection = cn

> > > cmd.CommandText = "SELECT * FROM People"

> > > cmd.CommandType = CommandType.Text

> > > stReader = cmd.ExecuteReader()

> > > While stReader.Read

> > >     DIm objPerson As New clsPerson()

> > >     objPerson.FirstName = stReader.Item("FirstName").ToString

> > >     objPerson.LastName = stReader.Item("LastName").ToString

> > >     colPeople.Add(objPerson,stReader.Item("PeopleID").toString

> > > End While



> > > > Being new to VB.NET from the old world of VB3-6 I have a question
> > > regarding
> > > > the best/proper way of doing database access against an Access
> database
> > > > under VB.NET.

> > > > Currently I am doing the following (see below). Is this approach
> correct
> > > > under .NET or by using ADODB am I mixing old and new?  How should
this
> > be
> > > > done under VB.NET?  Any help/suggestions would be appreciated.
> > > > Basically I want to query an Access database, load data, do adds,
> edits,
> > > > deletes, etc.

> > > > Dim adoConn As ADODB.Connection = New ADODB.Connection()
> > > > Dim strConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
> > > > Source=" & "D:\test.mdb"
> > > > Dim adoRS As ADODB.Recordset = New ADODB.Recordset()

> > > > adoConn.Open(strConnection, "", "", -1)
> > > > adoRS.Open("SELECT * FROM People", adoConn,
> > > > ADODB.CursorTypeEnum.adOpenForwardOnly,
> > ADODB.LockTypeEnum.adLockReadOnly,
> > > > 1)

> > > > Do While Not adoRS.EOF
> > > >     Dim objPerson As New clsPerson()
> > > >     objPerson.FirstName = adoRS("FirstName").Value
> > > >     objPerson.LastName = adoRS("LastName").Value
> > > >     colPeople.Add(objPerson, adoRS("PeopleID").Value)
> > > >     adoRS.MoveNext()
> > > > Loop

> > > > adoRS.Close()



Sat, 27 Nov 2004 01:20:02 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. Still confused about ado.net - need your advice!

2. An experienced VB guy going to .NET needs advice

3. C# ADO.NET Dataset advice

4. ADO.NET with ASP.NET using VB.NET

5. Need Advice with ADO and MSDE

6. Advice on ADO connections needed

7. Need advice on Security - VB6 ADO 2.5 - jet 4

8. Advice on ADO connections needed

9. Need advice on Security - VB6 ADO 2.5 - jet 4

10. Advice needed from VB veterans concerning Registry Adjustments in VB 5

11. Any advice for a good VB .net book?

12. Problem running VB.NET !!! Please advice

 

 
Powered by phpBB® Forum Software