The ADO.Net Command Object

In a connected mode environment, after a connection is established with the data, the data is manipulated and returned using the command object. The command object is passed a SQL query SELECT statement, which is run by one of the Execute methods. The three Execute methods are ExecuteNonQuery (for updates or deletes or appends), Execute Reader (for returning datasets to the client), and Execute Scalar (returns a single value).


The command objects themselves are either of SqlCommand, OleDbCommand, or ODBCCommand types. And the principal properties are the command text (the sql statement and the connection object previously created.) Other properties are the Command Type, Transaction, Command Timeout, Parameters, and UpdatedRowSource.
Dim cmd as New SqlCommand
cmd.connection=conn
cmd.CommandText=”SELECT * FROM tblBooks;”
Dim dr as SqlDataReader = cmd.ExecuteReader()
The preceding code will execute a SQL command and put the results into a DataReader after having made and opened a connection. Had the SQL statement been an INSERT statement, the appropriate method would have been ExecuteNonQuery(), rather than ExecuteReader(). There is an additional Execute XMLReader() method which is new in SQL2000, and is used for processing SELECT queries from an XML dataset. ExecuteScalar() is another method used for returning a single value from a dataset.
Parameters
In the OleDb model, you can define parameters in the CommandText using question marks.
SELECT * from tblBooklist where YearPublished = ?;
Then you need to create and define the parameter prior to passing them into the command object.
Dim Parm as New OleDBParameter(“YearPublished”, OleDbType.Integer)
Parm.Value=1994
cmd.Parameters.Add(parm)
You can even have ADO.Net populate the list of parameters for you using the DeriveParameters method.
Dim cmd as New SqlCommand(“up_getshotgun”, conn)
cmd.CommandType=CommandType.StoredProcedure
SqlCommandBuilder.DeriveParameters(cmd)
Debug.writeline(cmd.parameters.count & “parameters”)
For i = 0 to cmd.parameters.count-1
Debug.writeline(cmd.parameters(i).ParameterName)
Next
Stored Procedures
Stored Procedures are handled using the parameters in the previous section and setting them with the Parameters.Add method. The only difference from the previous code is the calling of the stored procedure itself. This is accomplished using the CommandType property, rather than the CommandText property.
Dim cmd as New SqlCommand(“up_getshotgun”, conn)
cmd.CommandType=CommandType.StoredProcedure
cmd.Parameters.Add(“@Brand”,”Remington”)
Dim dr as SqlDataReader=cmd.ExecuteReader()
Resources
• PPT Presentation on .ADO Net
This PPT presentation provides you with the overview of .ADO Net.
• Information on ADO Net resources
This resource provides comprehensive information on .ADO Net resources.

About the Author: Chris Kemp is a well knoen author who writes best quality articles on IT, Software, Programming, etc. For further details please visit the site www.paladn.com