Nigel Whitworth's Development Site

You are currently not logged in.

What is the difference between ExecuteReader, ExecuteScalar, ExecuteXMLReader and ExecuteNonQuery, DA.Fill(objDT)?

Setting up the connection is the same for all those commands in that you create a Connection object and a command object, setting the parameters collection as appropriate. For our example, we would use the following VB.NET statements to access the database and select the properties of the command object.

Dim oradb As String = "<ConnectionString>"

Dim conn As New sqlConnection(oradb) ' VB.NET
conn.Open

Dim cmd As New sqlCommand
     cmd.Connection = conn
     cmd.CommandType = CommandType.Text

Then you would use one of the following sections of code accordingly.

CommandPurpose
ExecuteReaderReturns a connected recordset. It is forward only and uneditable at the same time.

     cmd.CommandText = "select deptno, dname, loc from dept";
     Dim dr As sqlDataReader = cmd.ExecuteReader
     While dr.Read
          ' Process data as you would normally.
     End While
ExecuteScalarThis returns one value only, no recordsets.

     cmd.CommandText = "Select Name, DOB, from Emp where ID=1";
     Dim strName As string = cmd.ExecuteScalar.ToString
ExecuteXMLReaderReturns the recordset as a XML document.

     cmd.CommandText = "select deptno, dname, loc from dept";
     cmd.CommandText = "Select Name, DOB, Town from Emp FOR XML AUTO, ELEMENTS";
     Dim strName As string = cmd.ExecuteScalar.ToString

     dim reader as System.XML.XMLReader = cmd.ExecuteXMLReader

     Do While objXML.Read
          ' Do Some processing
     Loop

     objXML.Close
     con.Close
ExecuteNonQueryDoesn't return any recordsets, ideal for insert, update and delete queries where no recordsets are returned.

     cmd.CommandText = "Delete from Class where PupilHasLeft=true";
     cmd.ExecuteNonQuery

However, although records are returned, the number of affected records is.
da.Fill(DT)This populates a datatable or dataset from a select query.

     Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter
     Dim dataSet As System.Data.DataTable = New System.Data.DataTable

     dataAdapter.SelectCommand = dbCommand
     dataAdapter.Fill(dataTable)

     For Each objDR in objDT.Rows
          ' Do Some Processing
     Next


If you intend to vote or make a comment, please enter the security code.

Make a comment

*Comments are the views of individuals, they may or may not be correct.
All comments are reviewed and accepted or rejected.
If you give an email address, you will be sent an email when someone makes a comment on this page.*

Name :
Email :
Comments :


Only name and Code is compulsory.
 
 
Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)