Release Date: 4/1/2004
| Update Date: 4/1/2004
Build a distributed application using .NET
PRIYA DHAWAN
Microsoft Developer Network
Amendment in February 2002
Summary: This article covers data operations that use ADO .NET to return to a single line entity. (14 page print pages)
Please download BdadotNet.msi or BDADOTNET_BETA2.MSI.
This page
Introduction To return data Operation Using the OUTPUT parameter to get a single value Using the ADO .NET DataReader object with the ADO .NET SQLCOMMAND object and XmlReader Using the ADO .NET DataSet Object Write an ADO .NET Command Object Skimming
Introduction
ADO .NET provides a variety of ways to perform queries and modifying this data. This article will discuss these techniques for these ADO .NET SQL managed providers.
Back to top
Returns the operation of the data
The data from the "Select" statement in the SQL expression can be returned using the Output parameter on the ADO .NET "command" object, the DataReader object, or "Datase" object.
Back to top
Get a single value using the Output parameter
The ADO .NET Command object provides a method called ExecuteScalar to retrieve a single value from the database. This method performs query and returns the first column of the first row in the result of the result returned by the query. The result set can contain multiple lines, ExecuteScalar ignores these rows.
The following example retrieves the single value from the database using the ExecuteScalar method of the Command object.
DIM SQLCONN AS SQLCONNECTION
DIM SQLCMD As Sqlcommand
Dim Param as Sqlparameter
Dim ORDERSTATUS AS INTEGER
Try
'Create a New Connection Object
SQLCONN = New SqlConnection (Common.getConnectionstring)
'Create a New Command Object
SQLCMD = New SQLCOMMAND ()
'Specify The Stored Procedure and Connection
With sqlcmd
.Commandtype = commandtype.text
.Commandtext = _
"Select ORDERSTATUS from Orders Where ORDERID = @ OrderID"
.Connection = SQLCONN
End with
'Define and Add The Input Parameter to the Parameters Collection
Param = Sqlcmd.Parameters.Add (New _
SQLParameter ("@ OrderID", SqldbType.It))
'Specify The Parameter Direction
Param.direction = parameterdirection.input
'Set the parameter value
Param.Value = 1
'Open the Connection
Sqlconn.open ()
'Execute The Command and Get a Single Value
ORDERSTATUS = CINT (Sqlcmd.executeScalar ())
Catch e as exception
'Catch exception herefinally
'Close THE CONNECTION
Sqlconn.close ()
END TRY
Note See Example 1 in BDADOTNETDATA2.VB Sample Code (the download link is available in this article).
Use Output parameters
A stored procedure with an OUTPUT parameter can not only be effectively used as a way to perform queries, but also as a way to return a single line data entity. The OUTPUT parameter is processed using the ADO .NET "command" parameter collection.
In the following example, the parameter of the stored procedure is defined by the parameter collection of the "command" object. This stored procedure is expected to "@Orderid" and output parameters "@orderstatus", and the latter returns the status of the specified order:
DIM SQLCONN AS SQLCONNECTION
DIM SQLCMD As Sqlcommand
Dim Param as Sqlparameter
Try
'Create a New Connection Object
SQLCONN = New SqlConnection (MyConnString)
'Create a New Command Object
SQLCMD = New SQLCOMMAND ()
'Specify The Stored Procedure and Connection
With sqlcmd
.Commandtype = commandtype.storedprocedure
.Commandtext = "getorderstatus"
CONNECTION = SQLCONN
End with
'Define and Add The Input Parameter to the Parameters Collection
Param = Sqlcmd.Parameters.Add (New _ Sqlparameter ("@ OrderID", _
Sqldbtype.Int))))
With param
'Specify The Parameter Direction
.Direction = parameterdirection.input
'Set the parameter value
.Value = 1
End with
'Define and Add The Output Parameter to the Parameters Collection
Param = Sqlcmd.Parameters.Add (New _
SQLParameter ("@ OrderStatus", _
Sqldbtype.Int))))
'Specify The Parameter Direction
Param.direction = parameterDirection.output
'Open the Connection
Sqlconn.open ()
'Execute The Command
Sqlcmd. ExecutenonQuery ()
'Do Something with Returned Data
ORDERSTATUS = Sqlcmd.Parameters ("@ OrderStatus"). Value.toString
??? ...
Catch e as exception
'Handle the Exception
??? ...
Finally
'Close the connectionsqlconn.close ()
END TRY
Note See Example 2 in BDADOTNETDATA2.VB Sample Codes (this article provides download links).
Back to top
Use ADO .NET DataReader objects
The Output parameter has a disadvantage that it requires the corresponding application code on the database (for example, the stored procedure of the Microsoft SQL Server database or the parameter query of the .mdb database). ADO .NET DataReader can return data from an instant SQL query and stored procedure. The DataReader object returns only a only read only data stream.
In the example below, the query returns a single row that contains OrderID 1 orders that contain the customer of its CustomerID 1.
DIM SQLCONN AS SQLCONNECTION
DIM SQLCMD As Sqlcommand
DIM SQLDATARDR As SqldataReader
??? ...
Try
'Create a New Connection Object
SQLCONN = New SqlConnection (MyConnString)
'Create a New Command Object
SQLCMD = New SQLCOMMAND ()
'Specify the command to be exceute
With sqlcmd
.Commandtype = commandtype.text
.Commandtext = "exec getorderHeader @ OrderID = 1"
.Connection = SQLCONN
End with
'Open the Connection
Sqlconn.open ()
'Execute the Command and Retrieve The Row in The DataReader
Sqldatardr = sqlcmd.executeReader ()
'Position The Pointer At the Row
SqlDatardr.read ()
'Do Something with the row
Outstring = SqlDatardr.Item ("ORDERID"). Tostring () and "," and _
SqlDatardr.Item ("Customerid"). Tostring () and "," and _
SqlDatardr.Item ("ORDERSTATUS"). TOSTRING () and "," and _
SqlDatardr.Item ("ORDERDATE"). Tostring ()
Catch e as exception
'Handle the Exception
??? ...
Finally
'Close the DataReader
Sqldatr.close ()
'Close THE CONNECTION
Sqlconn.close ()
END TRY
Note See Example 3 in BDADOTNETDATA2.VB sample code (the download link is provided).
Back to top
Use ADO .NET SQLCOMMAND objects and XMLReader
Microsoft SQL Server 2000 is designed to support XML capabilities. Now, by using the for XML clause, the result of the "Select" statement can be returned in XML. To retrieve the results of the XML form directly from SQL Server 2000, you can use the ExecuteExmlReader method of the SQLCommand object. ExecutexmlReader returns the System.xml.xmlReader object that contains XML returned from SQL Server 2000. In the example below, the SQLCommand object performs a SELECT statement that uses the for XML clause to return the result of the XML form.
DIM SQLCONN AS SQLCONNECTION
DIM SQLCMD As Sqlcommand
DIM XMLRDR As XmlReader
Try
'Create a New Connection Object
SQLCONN = New SqlConnection (MyConnString)
'Create a New Command Object
SQLCMD = New SQLCOMMAND ()
'Specify the command to be exceute
With sqlcmd
.Commandtype = commandtype.text
'Use for xml clause to get results as XML
.Commandtext = _
"SELECT * from ORDERS Where ORDERID = 1 for XML AUTO"
.Connection = SQLCONN
End with
'Open the Connection
Sqlconn.open ()
'Execute the Command and Retrieve The Row in The DataReader
Xmlrdr = Sqlcmd.executexmlReader ()
'Move to the root element
Xmlrdr.movetoContent ()
'Do Something with the data
Outxml = xmlrdr.readouterxml ()
??? ...
Catch e as exception
'Catch eXception
Finally
Sqlconn.close ()
END TRY
Note See Example 4 in BDADOTNetData2.vb sample code (the download link is provided in this article).
Back to top
Use ADO .NET DataSet objects
"Dataset" object is a major offline data container provided by Microsoft? NET Framework. It is a complex cache, together with the SQL and ADO managed providers, and write data from the data source and write the modified data back to the data source. Although a single line data entity actually only needs a small part of the space in this cache, the "Dataset" object is still a useful container that the single line of data entities should consider.
The DataAPter object acts as a mapping layer between the data source and the "Dataset" object. It retrieves data from the data source, populates the "Dataset" object, and then sends the change back to the data source.
In the example below, the query returns a single row that contains OrderID 1 orders that contain the customer of its CustomerID 1.
Dim Resultds as DataSet
DIM SQLDA As SqldataAdapter
Try
'
'
'Create a New DataAdapter Object
SQLDA = New SqldataAdapter () 'Create a New DataSet
Resultds = new dataset ()
With sqlda
'Add a selectcommand object Object
.SelectCommand = New SQLCOMMAND ()
'Specify The Select Command
With .selectcommand
.Commandtype = commandtype.text
.Commandtext = "exec getorderHeader @ OrderID = 1"
.Connection = new SqlConnection (MyConnString)
End with
'Populate the dataset with the returned data
.Fill (Resultds, "ORDER")
End with '' "" '""
'Do Something with the row
COLVALUE = _
Resultds.Tables ("Order"). Rows (0) .Item ("Shiptoname"). Tostring ()
??? ...
Catch e as exception
'Handle the Exception
??? ...
Finally
'??? ...
END TRY
Note See Example 5 in the BDADOTNETDATA2.vb sample code (the download link is provided in this article).
Back to top
Write
With the ADO .NET object, you can easily modify a single row and send it back to the data source. The simplest write operation method is to execute an emotic SQL or stored procedure using the Command Object. The "Dataset" command provides more features, especially if the data has been retrieved to the "Dataset" object.
Back to top
Use ADO .NET COMMAND object
We will discuss in another related article Data Operations That Do Not Return Rows to discuss the use of the ADO .NET command object to perform an emotic SQL or stored procedure.
Back to top
Use ADO .NET DataAdapter object
The DataAdapter object is used to connect to the data source, retrieve data, and populate the "Dataset" object with data. You can change the data in the Dataset object. DataAdapter and the data source coordinated changes in the Dataset object.
Use a DataSet object
The "Update" method of the DataAdapter object submits changes to the Cache in the Dataset object to the data source. DataAdapter object Submit a new line using INSERTCOMMAND, use UpdateCommand to submit a modified row, use DeleteCommand from the database from the database.
If you specify the InsertCommand, UpdateCommand, or DeleteCommand property of the DataAdapter object, the "Update" method will perform "Insert", "Update", or Delete. Another possible way to simply use the CommandBuilder object to automatically generate the INSERT, UPDATE, and DELETE commands based on SelectCommand. The best way is to specify your own INSERTCOMMAND, DeleteCommand, or UpdateCommand because this makes it possible to perform updates and get the performance of the "Automatic Generation" method.
This article only discusses automatic generation of automatically generated data sources for updating changes. For information on how to specify your own "Insert", "Update", "Remove" commands, see related articles Data Operations On Sets of Rows. Automatically generated INSERT command
When calling the "Update" method for the DataAdapter object, it will automatically generate the "Insert" command based on the "Select" command you specify (if the InsertCommand property is not set).
In the following code example, populate the "Dataset" object with a single row containing a set. Add a new line to the Dataset object, then insert the object into the table in the data source:
DIM SQLDA As SqldataAdapter
Dim Resultds as DataSet
DIM Workrow as DataRow
DIM SQLCMDBLDR AS SQLCOMMAVAILDER
Try
'
'Create a new dataset
Resultds = new dataset ()
'Create a New DataAdapter Object
SQLDA = New SqldataAdapter ()
'Create the commandbuilder object to automaticly generate the
'INSERT, UPDATE, AND DELETE STATEMENTS
SQLCMDBLDR = New SQLCommandbuilder (SQLDA)
With sqlda
'Add a selectcommand object Object
.SelectCommand = New SQLCOMMAND ()
'Specify The Select Command
With .selectcommand
.Commandtype = commandtype.text
'Query ON Non-EXISTING ROW RETURns Empty Row with metadata
.Commandtext = "exec getorderheader @ OrderID = -1"
.Connection = new SqlConnection (MyConnString)
End with
'Populate the dataset with the returned data
.Fill (Resultds, "ORDER")
End with '
'Set the default values for columns
Resultds.Tables ("Order"). _
Column ("ShippingHandling"). DefaultValue = 0
Resultds.Tables ("Order"). Column ("Tax"). DefaultValue = 0
Resultds.Tables ("ORDER"). Column ("subtotal"). DefaultValue = 0
'Create a new row
Workrow = Resultds.tables ("Order"). Newrow
'Fill in Workrow Data
Workrow.Item ("Customerid") = 1
Workrow.Item ("ORDERSTATUS") = 400
Workrow.Item ("ORDERDATE") = now () Workrow.Item ("Shiptoname") = "ResidentBdadotNetData2example4"
Workrow.Item ("ShipToadDressID") = 1
'Add the row to the dataset
Resultds.Tables ("Order"). Rows.Add (Workrow)
'Reconcile Changes with the data source
Sqlda.Update (Resultds, "Order")
Catch e as exception
'Handle the Exception
??? ...
Finally
'Close Connection and Other Cleanup Code Here
END TRY
Note See Example 6 in the BDADOTNETDATA2.vb sample code (the download link is provided on this article).
Automatically generated UPDATE command
When calling the "Update" method for the DataAdapter object, it will automatically generate the Update command based on the "Select" command you specify (if the UpdateCommand property is not set).
In the following code example, populate the "Dataset" object with a single row containing a set. Update the line in the Dataset object. Finally, the DataAdapter object will change the change back data source:
DIM SQLDA As SqldataAdapter ()
Dim Resultds as dataset ()
DIM SQLCMDBLDR AS SQLCOMMAVAILDER
DIM colitemname as string
Try
'
'Create a new dataset
Resultds = new dataset ()
'Create a New DataAdapter Object
SQLDA = New SqldataAdapter ()
'Create The CommandBuilder Object
SQLCMDBLDR = New SQLCommandbuilder (SQLDA)
With sqlda
'Add A New SelectCommand Object
.SelectCommand = New SQLCOMMAND ()
'Specify The Select Command
With .selectcommand
.Commandtype = commandtype.storedprocedure
'Get Last Order (as created by eXample 4)
.Commandtext = "getLastOrderheader"
.Connection = new SqlConnection (MyConnString)
End with
'Populate the dataset with the returned data
.Fill (Resultds, "ORDER")
End with '
'Update the Row
Resultds.Tables ("Order"). Rows (0) .Item ("Shiptoname") = _
"Resident BdadotNetData2example5"
'Reconcile Changes
Sqlda.Update (Resultds, "Order") Catch e as Exception
'Handle the Exception
??? ...
Finally
END TRY
Note See Example 7 in BDADOTNETDATA2.VB sample code (the download link is available in this article).
Automatically generated delete command
When calling the "Update" method for the DataAdapter object, it will automatically generate the "Delete" command based on the "Select" command you specify (if the deleteCommand property is not set).
In the following code example, populate the "Dataset" object with a single row containing a set. Delete rows in the Dataset object. Finally, the DataAdapter object update the database:
DIM SQLDA As SqldataAdapter ()
Dim Resultds as dataset ()
DIM SQLCMDBLDR AS SQLCOMMAVAILDER
DIM Workrow as DataRow
DIM colitemname as string
Try
'Create a New Connection Object
SQLCONN = New SqlConnection (MyConnString)
'Create a new dataset
Resultds = new dataset ()
'Create a New DataAdapter Object
SQLDA = New SqldataAdapter ()
'Add a selectcommand object Object
Sqlda.selectCommand = new sqlcommand ()
SQLCMDBLDR = New SQLCommandbuilder (SQLDA)
With sqlda
'Add a selectcommand object Object
.SelectCommand = New SQLCOMMAND ()
'Specify The Select Command
With .selectcommand
.Commandtype = commandtype.storedprocedure
'Get last order (as created by example 4). Delete Will Fail IF
'this Order Has Details
.Commandtext = "getLastOrderheader"
.Connection = new SqlConnection (MyConnString)
End with
'Populate the dataset with the returned data
.Fill (Resultds, "ORDER")
End with '
'Delete the row from the order type in the dataset
Resultds.Tables ("Order"). Rows (0) .delete ()
'Reconcile Changes with the data source
Sqlda.Update (Resultds, "Order")
Catch e as exception
'Handle the Exception
??? ...
Finally
'Cleanup Code Here
END TRY
Note See Example 8 in the BDADOTNETDATA2.vb sample code (the download link is provided in this article).
Back to top
Small junction ADO .NET provides a variety of useful methods to read and write a single line data entity. In order to select the most appropriate method, not only the operations performed on the data source, but also consider the representation of the data within the application. Input and Output parameters, DataReader and XMLReader objects are lightweight and provide good performance, but "Dataset" objects can combine useful data containers with advanced features.
