|
web
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Read and write databaseForward 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/WRITEPrivate 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) 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. --------------------------- Show quoteHide quote "fniles" <fni***@pfmail.com> wrote in message news:eA15kMwEHHA.3768@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) > > 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. Show quoteHide quote On Tue, 28 Nov 2006 09:36:34 -0600, "fniles" <fni***@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 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 Show quoteHide quote "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) > > > 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. Show quoteHide quote "fniles" <fni***@pfmail.com> wrote in message news:eA15kMwEHHA.3768@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) > > 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. Show quoteHide quote "Scott M." <s-mar@nospam.nospam> wrote in message news:eu2JzmzEHHA.3436@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" <fni***@pfmail.com> wrote in message > news:eA15kMwEHHA.3768@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) >> >> > > 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 Show quoteHide quote "fniles" <fni***@pfmail.com> schreef in bericht news:OO712$zEHHA.3524@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-mar@nospam.nospam> wrote in message > news:eu2JzmzEHHA.3436@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" <fni***@pfmail.com> wrote in message >> news:eA15kMwEHHA.3768@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) >>> >>> >> >> > > 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 Show quoteHide quote "Cor Ligthert [MVP]" <notmyfirstn***@planet.nl> wrote in message news:Okma8u2EHHA.4208@TK2MSFTNGP03.phx.gbl... > 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 > > "fniles" <fni***@pfmail.com> schreef in bericht > news:OO712$zEHHA.3524@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-mar@nospam.nospam> wrote in message >> news:eu2JzmzEHHA.3436@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" <fni***@pfmail.com> wrote in message >>> news:eA15kMwEHHA.3768@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) >>>> >>>> >>> >>> >> >> > > 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 Show quoteHide quote "Scott M." <s-mar@nospam.nospam> schreef in bericht news:eu2JzmzEHHA.3436@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" <fni***@pfmail.com> wrote in message > news:eA15kMwEHHA.3768@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) >> >> > > 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() Show quoteHide quote "Scott M." <s-mar@nospam.nospam> wrote in message news:eu2JzmzEHHA.3436@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" <fni***@pfmail.com> wrote in message > news:eA15kMwEHHA.3768@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) >> >> > > 
Other interesting topics
Help - Timing Logic
Datagridview...cell contents Newbie Question here Newbie help: How to detect when a worker thread ihas completed normaly newbie syntax question: adding event handler for button not working Unable to write to file in my web site Serial ports. [OT] VB.Net and MS Access Why does Join method call sit there forever? Error on deleting registry value |
|||||||||||||||||||||||
