469,924 Members | 1,431 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,924 developers. It's quick & easy.

Read and write database

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)
Nov 28 '06 #1
9 1547
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.
---------------------------
"fniles" <fn****@pfmail.comwrote in message
news:eA**************@TK2MSFTNGP06.phx.gbl...
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)


Nov 28 '06 #2
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.

On Tue, 28 Nov 2006 09:36:34 -0600, "fniles" <fn****@pfmail.com>
wrote:
>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)
--

Bits.Bytes.
http://bytes.thinkersroom.com
Nov 28 '06 #3
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
"fniles" wrote:
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)
Nov 28 '06 #4
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" <fn****@pfmail.comwrote in message
news:eA**************@TK2MSFTNGP06.phx.gbl...
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)


Nov 28 '06 #5
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.

"Scott M." <s-***@nospam.nospamwrote in message
news:eu**************@TK2MSFTNGP03.phx.gbl...
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" <fn****@pfmail.comwrote in message
news:eA**************@TK2MSFTNGP06.phx.gbl...
>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)



Nov 28 '06 #6
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

"Scott M." <s-***@nospam.nospamschreef in bericht
news:eu**************@TK2MSFTNGP03.phx.gbl...
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" <fn****@pfmail.comwrote in message
news:eA**************@TK2MSFTNGP06.phx.gbl...
>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)



Nov 29 '06 #7
Be aware that this is terrible slow.

http://www.vb-tips.com/dbpages.aspx?...6-fc0d5c470f53

I hope this gives an idea,

Cor

"fniles" <fn****@pfmail.comschreef in bericht
news:OO**************@TK2MSFTNGP06.phx.gbl...
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.

"Scott M." <s-***@nospam.nospamwrote in message
news:eu**************@TK2MSFTNGP03.phx.gbl...
>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" <fn****@pfmail.comwrote in message
news:eA**************@TK2MSFTNGP06.phx.gbl...
>>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)




Nov 29 '06 #8
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
"Cor Ligthert [MVP]" <no************@planet.nlwrote in message
news:Ok**************@TK2MSFTNGP03.phx.gbl...
Be aware that this is terrible slow.

http://www.vb-tips.com/dbpages.aspx?...6-fc0d5c470f53

I hope this gives an idea,

Cor

"fniles" <fn****@pfmail.comschreef in bericht
news:OO**************@TK2MSFTNGP06.phx.gbl...
>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.

"Scott M." <s-***@nospam.nospamwrote in message
news:eu**************@TK2MSFTNGP03.phx.gbl...
>>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" <fn****@pfmail.comwrote in message
news:eA**************@TK2MSFTNGP06.phx.gbl...
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)




Nov 29 '06 #9
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()

"Scott M." <s-***@nospam.nospamwrote in message
news:eu**************@TK2MSFTNGP03.phx.gbl...
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" <fn****@pfmail.comwrote in message
news:eA**************@TK2MSFTNGP06.phx.gbl...
>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)



Nov 29 '06 #10

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by JDJones | last post: by
10 posts views Thread by David ROBERT | last post: by
7 posts views Thread by Graham Taylor | last post: by
18 posts views Thread by jas | last post: by
3 posts views Thread by =?Utf-8?B?ZGF2aWQ=?= | last post: by
4 posts views Thread by farhaaad | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.