Read and write database

[fniles]

When using VB6 and ADO, if I only do a Read, I will open a recordset with
Forward Only cursor and Read Only lock, thus it will be faster than a non
Read recordset.
In VB.NET, when only need to do a Read, is it correct that I want to use
OLEDBDataReader (SQLDataReader for SQL Server), when when doing a Read/Write
I use OLEDBDataAdapter (SQLDataAdapter for SQL Server) like in the following
codes ?
Is using OLEDBDataReader/SQLDataReader faster than using
OLEDBDATAAdapter/SQLDataAdapter ?
Thank you


'READ ONLY
Dim dr As OleDb.OleDbDataReader
Dim cmd As New OleDb.OleDbCommand
With cmd
.Connection = g_ConnectionDemoOLE
.CommandText = sql
dr = .ExecuteReader()
:

'READ/WRITE
Private m_da As New OleDb.OleDbDataAdapter
Private m_cmd As New OleDb.OleDbCommand
Dim m_ds As New DataSet

With m_cmd
.Connection = adoConOLE
.CommandText = sSQL
End With
m_da.SelectCommand = m_cmd
m_da.Fill(m_ds)

 

[RobinS]

If you're only reading data, the DataReader is the fastest way.
I think you have to be sure to close it when you're done.

Robin S.

 

[Rad [Visual C# MVP]]

I'd say data reader directly is faster, reason being the overhead
required to setup and populate the plumbing of the data adapter.
Though I don't think the benefit is very significant.

 

[Guest]

fniles,

A data adapter is never required in order to wrtie to the database.

You can use a command object to execute Update, Insert and Delete statements.

Kerry Moorman

 

[Scott M.]

DataAdapters use DataReaders behind the scenes to do their Select queries,
but the extra effor to set one up makes just using a DataReader via a
Command object's .ExecuteReader method much easier.

 

[fniles]

Thank you all.
Could you please give me an example on how to use the DataReader via a
Command object's ?

Thank you very much.

 

[Cor Ligthert [MVP]]

Fniles

In Addition to Scott, it makes of course only sense to use the datareader if
you are not using datasets or datatables.

It can be used with the so called OO dataclasses to make your own
implementation of datahandling.

Cor

 

[Cor Ligthert [MVP]]

Be aware that this is terrible slow.

http://www.vb-tips.com/dbpages.aspx?ID=49f2cff5-56ad-44fc-a4c6-fc0d5c470f53

I hope this gives an idea,

Cor

 

[Scott M.]

Dim conStr As String = your connection string here
Dim selectSQL As String = your select SQL statement here
Dim con As New OleDBConnection(conStr)
Dim cmd As New OleDbCommand(selectSQL, con)

Try
con.Open()
Dim dr As OleDbDataReader =
cmdExecuteReader(CommandBehavior.CloseConnection)
Do While dr.Read()
'extract your data items here, ie. x =
dr.Item("userName").ToString()
Loop
dr.Close()
Catch

Finally
'Don't really need to do this because closing the reader closes the
connection
'but a good idea that doesn't hurt
con.Close()
End Try

 

[fniles]

Are the following the correct codes to do use a DataReader via a Command
object's .ExecuteReader method ?

Dim OleDbCommand2 As New Data.SqlClient.SqlCommand
Dim OleDbConnection1 As New Data.SqlClient.SqlConnection
Dim dreader As Data.SqlClient.SqlDataReader
With OleDbConnection1
.ConnectionString = "Data Source=" & sDataSource & ";Initial
Catalog=pubs;User ID=" & sID & ";Password=" & sPassword
.Open()
End With
OleDbCommand2.CommandText = "update authors set au_fname = 'ffa',
au_lname='smith' where au_id = '172-32-1176'"
OleDbCommand2.Connection = OleDbConnection1
dreader = OleDbCommand2.ExecuteReader()

OR USING Stored Procedure like the following ?

Dim OleDbCommand2 As New Data.SqlClient.SqlCommand
Dim OleDbConnection1 As New Data.SqlClient.SqlConnection
Dim dreader As Data.SqlClient.SqlDataReader

With OleDbConnection1
.ConnectionString = "Data Source=" & sDataSource & ";Initial
Catalog=pubs;User ID=" & sID & ";Password=" & sPassword
.Open()
End With
OleDbCommand2.CommandText = "UpdateAuthors"
OleDbCommand2.CommandType = CommandType.StoredProcedure
OleDbCommand2.Connection = OleDbConnection1
OleDbCommand2.Parameters.Add("@lname", SqlDbType.VarChar, 40)
OleDbCommand2.Parameters.Add("@fname", SqlDbType.VarChar, 20)
OleDbCommand2.Parameters.Add("@id", SqlDbType.VarChar, 11)
OleDbCommand2.Parameters("@lname").Value = "smith"
OleDbCommand2.Parameters("@fname").Value = "ffa"
OleDbCommand2.Parameters("@id").Value = "172-32-1176"
dreader = OleDbCommand2.ExecuteReader()
dreader.Close()
OleDbConnection1.Close()