Paladin Consultants, LLC

"Good Information is just good business"

JA slide show
Home Sitemap
Print E-mail
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 ExecuteScalar (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 CommandType, Transaction, CommandTimeout, 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.
 
Bookmark and Share