Release Date: 10/26/2004
| Update Date: 10/26/2004
Rick Dobson
Speaking of database connections, .NET supporters like to highlight the advantages of offline access provided by data adapters and data sets. During this process, DataReader is sometimes ignored. However, as Rick Dobson demonstrates here, DataReader is different - they provide only the data source, read-only connectivity, and they do not support data operations. So why do you want to use so limited? The answer is performance, for entry: DataReader is much faster. Another benefit is that the usage is less memory - DataReader allows you to handle it when you get data, each line. Therefore, DataRead is particularly suitable for dealing with data that cannot be accommodated in memory.
In order to get the maximum benefit from DataReader, you need to understand its features and restrictions. Since DataReader has a fixed limit, you can also find out how to use other .NET entities (such as arrays) to supplement DataReader features and benefit. This article reviews DataRead's functionality through some of the individual sample applications in three areas. First, I will showcase, populate and configure DataReader for efficient code mode for Windows Form Controls. Second, an example highlights how to use type of data to calculate an expression, which will reflect the characteristics of the data types listed in DataReader. I will compare the two techniques of the DataReader to retrieve the layered data to end this article.
From DataReader to the list box
You can easily fill the list box with DataReader pointing to the data source. Examples of this section are often applied to the combination frame control. You should first create DataReader for the Command object and call the ExecuteReader method, which is usually created inline.
The ExecuteReader method can accept the Commandbehavior enumeration from the DataReader from the definition of Command and its associated DataReader. Two examples in this section highlight the interaction between DataReader and its Command object, and provides more interesting applications for forms and control management. See the HCVSDataReaders project to access all the code for each example.
Back to top
Display the original DataReader data
The first DataReader example in the HCVSDataReaders project is in the Click event on the Button1 on Form1, and the DataReaderFortable function processes behind the AdonEtObjects class and the DataReaderfortable function process behind Form1 are also in the event. For convenience, the AdonTObjects class resides in the HCVSDataReaders project. Figure 1 shows the form after clicking the Populate from DataReader button. The Click event procedure of the button fills the list box with the column value selected from the Employees table in the SQL Server Northwind database.
The SqlDataReader class has many special methods for obtaining data from a variety of dedicated .NET and SQL Server data formats. However, for simple applications, you don't have to worry about them. All DataReaders need to do things that accept the default conversion from any non-string data type to the string, then add a calculated string expression to the list box. This is what the following code is going. It is from the Button1_Click process. A While loop reads a line by line, each time you create a STR2 expression that contains four references to DRD1 DataReader. Two in these references are digital instances. The value can even be empty (such as the REPORTSTO column value of the No. 2 employee). However, for each line, the expression is successful. You can specify the column by name or zero-based index. Do while DRD1.READ
DIM str2 as string = _
"Employee" & DRD1 ("EmployeeID") & _
"," & DRD1 ("firstname") & _
"" "Lastname") & _
"Reports to:" & DRD1 ("Reportsto")
ListBox1.Items.Add (str2)
Loop
The most interesting part in the first example may be how to create DRD1 DataReader first. Button1 Click event procedure creates DRD1 as a SqlDataReader class and specifies the return value of the function that is the function called at AtareAderFortable in the future. It passes the name of the Employees table - DataReaderfortable developed a DataReader.
DIM DRD1 AS SQLCLIENT.SQLDATAREADER = _
DataReaderfortable ("EMPLOYEES")
There are three steps to create DataReader procedures for the DataReaderFortable process.
DIM DRD1 As SqlClient.sqldataReader
Dim Adoobjs As New AdonEtObjects
'Specify Connection Object
DIM CNN1 As SqlClient.sqlConnection = _
Adoobjs.makenorthWindConnection
'Specify A Command Object
DIM str1 as string = _
"Select * from" & TableName
DIM CMD1 AS _
Sqlclient.sqlcommand = _
Adoobjs.makeacommand (CNN1, STR1)
'Open CNN1 and CREATE THE DRD1 DATAREADER
'with the executereader method cnn1.open ()
DRD1 = cmd1.executeReader_
(Commandbehavior.CloseConnection)
Return DRD1
First, it creates a connection to the Northwind database with the MakenorthWindConnection method of my AdonEtObjects class. Second, I created a Command object for DataReader. The DataReaderfortable process passes two parameters to my AdonEtObjects Makeummand method to return a new Command object. These parameters are used to extract all columns of SQL statements to all rows that are passed to the TABLENAME parameters of the DataReaderFortable process, and the Connection object returned by the MakenortWindConnection method. In the third step, the process actually creates a DataReader with the ExcecuteReader method to actually create a DataReader. Using the Commandbehavior.CloseConnection enumeration, you can turn the button1_click process to turn it back to its DataReader without having to operate the associated Connection object. This is because the enumeration indication .Net Framework automatically shuts down the Connection object when DataRead is off. The DataReaderFortable process ends by returning to instantiated DataReader.
By the way, the DataReaderFortable process has a shared access mode declaration so that the process in other modules of the entire HCVSDataReaders project can call it.
Back to top
Hand DataReader data
At least the content of ListBox1 can be improved in two aspects. First, there is no EMPLOYEEID value to indicate who is reported by Andrew Fuller. This is not a mistake because he does not report other people in the list box. However, gap may still make people confuse. Second, ListBox1 specifies the manager of the employee according to the manager's EmployeeID. By replacing its EMPLOYEEID with a manager, the readability of the ListBox1 content can be improved.
The Button2_Click process uses the Button1_Click process to process the two questions to populate Listbox1. Figure 2 shows the output after clicking the Populate from Array button. The row of employees named Andrew Fuller indicates that he is not supervisor in the list. The items of all other employees in ListBox1 displays the surname of the supervisor rather than EMPLOYEEID.
One of the main challenges that convert the supervisor's Employeid column value to the last name is that DataReader only knows a row of an employee. In order to convert the Employeeid column value of the supervisor, the application needs to link each EMPLOYEID value to the last name. By storing the value from DataReader to the array of strings, the process can find the last name that matches the EmployeID value. (Of course, this particular problem can also be resolved by creating a more complex SELECT statement in the query, but in order to use the array with DataReader, I will show you how to solve this problem on the client.)
The code snippet of the following Button2_Click process displays how to fill a string value with a string value from DRD1 DataReader, which is defined in the same way in Button1_Click.
Const RowsCount as integer = 99
DIM Myemps (RowsCount, 3) AS String
Do while DRD1.READ
IF INT1 <= ROWSCOUNT THEN
For int2 = 0 to DRD1.FIELDCOUNT () - 1
SELECT CASE DRD1.GETNAME (INT2)
Case "Employeeid"
Myemps (INT1, 0) = DRD1 (INT2)
Case "firstname"
Myemps (INT1, 1) = DRD1 (INT2)
Case "lastname"
Myemps (INT1, 2) = DRD1 (INT2)
Case "Reportsto"
'TOSTRING METHOD FORCES Conversion -
'Even for DBNULL VALUE TO STRING
Myemps (INT1, 3) = DRD1 (INT2) .tostring
End SELECT
NEXT
INT1 = 1
Else
Messagebox.show (_
"RESET ROWSCOUNT to A Larger Number and RE-RUN.", _
"Terminal Error Message", _
MessageboxButtons.ok, MessageBoxicon.exclamation
EXIT SUB
END IF
Loop
The MyEmps array has 4 columns for storing EmployeeID, FirstName, LastName, and Reportsto column values. For the default row of the Northwind database (9 lines), its largest line is more than enough. The WHILE cycle of reading is available in the WHILE cycle.
The FOR loop can be loopically access all column values to select a subset of the DataReader column value for storage in MyEmps. The FieldCount property returns the number of columns of DataReader. SELECT ... END SELECT statement Checks the column name of DataReader with a getName method to identify which MyEmps column to store the column value.
In addition to the reportsto column value, this code will apply the default Visual Basic .NET conversion, convert the SQL Server data format to the .NET string format in the MyEmps array. Since the ReportSto column can contain a null value (DBNULL), this process must be explicitly specified to specify the toString method to force DBNULL to string value - ie, empty string ("").
After collecting all the DRD1 column values in the MyEmps array, Button2_Click will turn off the DataReader and release these resources. The primary code segment shown below will calculate string expressions in line with the row of MyEmps to display in ListBox1. This code once again uses the lastname column value that matches the ReportSto column value in turn, not only the original Reportsto column value of the line. Before entering the loop to decode the ReportSto value value to the lastName column value, the code determines whether the ReportSto value in column 4 is an empty string.
For int1 = 0 to 99
IF myemps (INT1, 0) <> "" "
IF MyEmps (INT1, 3) <> "" "" "
strsupvrempid = myemps (INT1, 3)
For int2 = myemps.getLowerBound (0) to _
Myemps.getupperbound (0)
IF myemps (int2, 0) = strsupvrempid then
strempid = myemps (INT2, 2)
EXIT for
END IF
NEXT
Else
strempid = "no one in list box"
END IF
Str1 = "Employeeid" & myemps (INT1, 0) & _
"," & Myemps (INT1, 1) & "& _
Myemps (INT1, 2) & "Reports TO:" & _
strempid
ListBox1.Items.add (str1)
Else
EXIT for
END IF
NEXT
Back to top
Treatment type data
The Form1 application converts the contents of each DataReader column to a string value, regardless of the basic data type column columns in the data source. Sometimes you need to use the original data type, such as when you need to perform a value or data algorithm for the column value. If you don't understand the basic data type, you need to use a technique to find the original data type before using them.
Back to top
Report column name and data type
The Button1_Click process in Form2 demonstrates a technology that is written in column names and data types of DataReader. Although .NET provides other methods for this task, this technology is built on your understanding of DataReader and how to use them with an array. This process first creates DataReader to the ORDERS table in the Northwind database based on the DataReaderfortable process in Form1. Since the DataReaderfortable process is declared with shared access mode, FORM2 can be called by the following code:
DIM DRD1 AS SQLCLIENT.SQLDATAREADER = _
Form1.DataReaderfortable ("ORDERS")
You also need an array to save the column name and data type of DRD1 DataReader. The number of sets will be saved as the name of the name of the column and its data type of the DRD1 DataReader. The following code excerpts how to apply the CREATEINSTANCE sharing method of the Array class to create an array called OrdersColNameStypes. How many columns in DRD1 DataReader, how many rows of this array, and two columns. The FOR loop can be loop to access the DataReader column to populate the array with column name and data type metadata. The setValue method is specified by array elements. You can learn how to use the getName method to return column names from the above example. This process illustrates how to apply the getDataTypename method to restore the native data type names listed in DataReader.
Dim ORDERSCOLNAMESTYPES as array = _
Array.createInstance (Gettype (String), _
DRD1.FIELDCOUNT, 2)
For Int1 as integer = 0 to DRD1.FIELDCOUNT - 1
ORDERSCOLNAMESTYPES.SETVALUE
(DRD1.GETNAME (INT1), INT1, 0)
ORDERSCOLNAMESTYPES.SETVALUE
(DRD1.GETDATATIPENAME (INT1), INT1, 1)
NEXT
As you can see in Figure 3, the last code snippet of the Button1_Click process on FORM2 is just through the column values per consecutive row in the ORDERSCOLNAMESTYPES array, and print the column name and data type to the "Output" window. The report in the figure indicates that the ORDER table has 14 columns. The first column name of the ORDER table is ORDERID, the data type is SQL Server Int. Other columns contain string data types (NVARCHAR and NCHAR) and DateTime and Money data types. Back to top
Execution algorithm
Tips for performing algorithms for DataReader column values is to save them as a Visual Basic .NET data type that matches its native database data type. However, arrays will enforce all element members as the same type. Using the array stores the value of DataReader, one way to keep the data source data type is to save the DataReader column value into an array of Object data type elements. In essence, this process boxes the DataReader column value to the Object instance that does not force it to another data type. Later, you can restore the basic basic data format by specifying the array elements to variables declared with the appropriate data type. In essence, this designation will cancel the boxed data type.
The code after Form2 includes a process - PopArray, which boxes the DataReader column value to an array with an Object element. If you are interested in this process, please check the popARRAY list in the HCVSDataReaders project.
In this article, a primary purpose of the PopArray process is to demonstrate the Integer and DateTime algorithm with column values of the ORDERS table in the Windows application. The Button2_Click process of Form2 has two main code snippet. The first demonstrates how to calculate the difference between the first row and the last row of ORDERID column values in the ORDERS array, which will mirror the Orders table in the Northwind database. Before starting the first primary code segment, the process calls the popARRAY process to fill the Orders array. If you want to know, the ORDERS table has 830 rows. The designated or last line of the ORDERS array first column is decremented by the first row and the last row of the ORDERS array. The Parameters of the WriteLine method include a simple expression of an Integer variable from other Integer variables.
Dim ORDERS As Array = PopArray ("ORDERS", 830)
DIM INT1 AS INTEGER = _
Orders (ORDERS.GETLOWERBOUND (0), _
ORDERS.GETLOWERBOUND (1))
DIM INT2 AS Integer = _
Orders (ORDERS.GetupperBound (0), _
ORDERS.GETLOWERBOUND (1))
Console.writeline (ControlChars.crlf & _
"AN EXAMPLE with INTEGER ARITHMETIC:")
Console.writeline (_
"There is {2} Order NumBers Between" & _
"THE FIRST ORDER NUMBER ({0}) and the" & _
"Last ORDER NUMBER ({1})", _
INT1, INT2, INT2 - INT1)
The second primary code snippet of Button2_Click executes the datetime algorithm for the shippedDate and RequiredDate column values in the first row of the Orders array. This code will unpack the two columns to the DATE data type, rather than unpacking the Object element to the INTEGER data type variable. You can use the Date and DateTime keywords to specify the DateTime value in Visual Basic .NET. The datediff function calculates the difference between the two DateTime variables. The WriteLine method for the Console class will be displayed in the "Output" window. 'DemonStrate Arithmetic with dates
DIM DATREQUIRED AS DATE = Orders (0, 4)
DIM DATSHIPPED As Date = Orders (0, 5)
Console.writeline (ControlChars.crlf & _
"An Example with Date Arithmetic")
Console.writeline (_
"Required Date ({1}) - ShippedDate ({0})" & _
"= {2} days", _
Datshipped.toString ("m / d / yyyy"), _
DATREQUIRED.TOSTRING ("m / d / yyyyy"), _
DateDiff (DateInterVal.day, Datshipped, _
DATREQUIRED))
Back to top
Generate moisture data
The application requires hierarchical data (such as rows that belong to a certain order). The last two examples show two ways to returns hierarchical data over DataReader. One way demonstrates how to use a dedicated MSDataShape provider. The second method uses more conventional tools on the basis of the tools shown in the previous article. In addition, the second technique can be built on the first one by the formatting syntax of the DateTime and the Currency value, and the formatting of the DateTime and Currency values.
Back to top
Using the MSDataShape Provider
As I pointed before, the MSDataShape provider is a dedicated provider for returning hierarchical data. This provider is to go back to Visual Basic 6, but Microsoft published a knowledge base article that describes how to use the MSDataShape provider in Visual Basic .NET and ADO.NET (http://support.microsoft.com/default.aspx ? scID = Kb; [ln]; 308045). Although the MSDataShape provider is especially effective in returning hierarchical results, it relies on SQL subsets and dedicated keywords and other grammar conventions. In addition, this provider cannot be used with the .NET SQL Server data provider. Instead, you will be forced to change to the OLEDB .NET data provider - even when processing the SQL Server database.
The connection to the database with the MSDataShape provider is slightly different. The following code comes from the Button1_Click process in Form3. Note that the code specifies a Connection object in the OLEDB namespace. Although the last three parameters of the server, integration security, and initial directory are the same as those parameters of the SQLConnection object, but the initial two parameters are very different. The front parameter specifies the MSDataShape provider, which works with the SQLOLEDB data provider specified in the second parameter.
New oledb.oledbconnection (_
"Provider = msdatashape; data provider = sqloledb;" & _ "server = (local); Integrated Security = SSPI;" & _
"Initial Catalog = Northwind")
The next three code blocks illustrate the syntax of the specified Command object, which is based on the ORDERS table of the Northwind database and the ORDER DETAILS generating ingredients.
DIM cmd1 as oledb.oledbcommand = _
New oledb.oledbcommand (_
"Shape {Select ORDERID, OrderDate" & _
"From Orders" & _
"Where orderid =" & textbox1.text & "}" & _
Append ({Select OrderID, ProductID, "& _
"Unitprice, Quantity, Discount" & _
"From [Order Details]} & _
"Relate Orderid to Orderid", CNN1)
CNN1.Open ()
DIM DRD1 AS OLEDB.OLEDBDATAREADER = _
cmd1.executeReader (Commandbehavior.CloseConnection)
DRD1.READ ()
Console.writeLine ("{0}, {1}", _
DRD1 (0), DRD1 (1))
DIM DRD2 AS OLEDB.OLEDBDATAREADER = DRD1 (2)
Do while DRD2.READ
Console.writeline ("{0}, {1}, {2}, {3}, {4}", _
DRD2 (0), DRD2 (1), DRD2 (2), DRD2 (3), DRD2 (4)))
Loop
Please pay attention to the dedicated keyword Shape, Append and Relate. The SQL statement of the Shape clause specifies the line of the primary result set. This statement references TextBox1's text property that should always specify a valid OrderID column value. The SQL statement of the Append clause specifies the result set of detailed members of the hierarchical result set. The ReLate clause indicates which columns are matched to the main data source and the dialogue in the detail data source.
After instantiation of the Command object, the code will be prepared by opening the CNN1 Connection object to generate a few DataReader. DRD1 DataReader returns data from the primary data source, DRD2 DataReader extracts data from the detailed data source. The main data source console.writeLine statement prints the first two column values of the main data source, which is ORDERID and ORDERDATE. CONSOLE.WRITELINE statement of the detail data source prints all rows of the Order Details table, which matches the values displayed in TextBox1.
Figure 4 shows the FORM3 after clicking the Shape button. The "Output" window under the form represents the hierarchical result set. The first line shows the row of the primary data source, including the ORDERID and ORDERDATE column values. The next 3 line shows the details of the order of the ORDERID value of 10248. The second column and the third column are used for ProductID and Unitprice columns. Print the ProductID column value (not the ProductName column value) makes it more difficult to distinguish which products they have referenced each line item. In addition, it is not clear that the unitprice column value is a currency value. Back to top
Return the hierarchical result set with conventional tools
A second example of returning to the hierarchical data depends on conventional tools, such as those that have been displayed in this article. The detailed code of the second example is displayed during the Button2_Click process, and the FORM3 module of the HCVSDataReaders project is named computeraRayIndex. The second method of returning the hierarchical data creates three arrays based on the associated DataReader of the ORDERS, Order Details, and Products tables in the Northwind database. Using this method Using an array can reduce the load on the database server because it allows the application to turn off the DataReader and its associated connectivity to the data source.
The following code snippet comes from the Button2_Click in the Form3 module, which interprets the method used to generate an order array. The PopArray process in Form2 has been briefly described above. It generates arrays based on the DataReader of the Northwind database. You will pass the maximum number of rows and table names to it. By the way, the popARRAY process will turn off its DataReader after filling the array. The ComputeArrayIndex process generates a one-dimensional array from a 2D array (such as Orders).
Dim IntmaxordersRows = 830
ORDERS = Form2.PopArray ("Orders", _
IntMaxordersRows)
IDXORDERS = ComputeArrayIndex (Orders, _
IntMaxordersRows)
One-dimensional Search Arbor can accelerate the lookup of the row in the two-dimensional array, which is fast to scan all rows in the two-dimensional array to find the value that matches a certain condition. This is because Visual Basic .NET provides an IndexOF sharing method for its Array class, which returns an index corresponding to a value in the one-dimensional array. The following code example shows the syntax of this method for the IDXORDERS array to restore the ORDERID and the ORDERDATE column value from the ORDERS array. The code snippet also has the format of the ORDERDATE column value to exclude the irrelevant time period of the DateTime value.
DIM intidx as integer = _
Array.indexof (idxorders, _
Integer.Parse (TextBox1.text))
Console.writeLine ("{0}, {1}", _
Orders (intIDX, 0), _
DateTime.Parse (_
Orders (intIDX, 3)). Tostring ("m / dd / yy"))
Figure 5 shows the final output of the ORDERID value that appears in Figure 4 in the Button2_Click process. Note that this process performs queries for the ProductID value and changed to the display of the productName column. The lookup logic of the productName column value based on the ProductID column value is the code based on the REPORTSTO column value and the extension of the above code segment in the second example of this article. Methods of setting up Unitprice's format to currency values Simply call common FormatCurrency functions. Although you can use a more reliable way to set the currency format, you know that Visual Basic .NET supports common and easy-to-use Formatcurrency functions. Back to top
summary
DataReader is a fast, flexible and powerful tool for data access to remote data sources. This article highlights the three specific types of applications in the .NET application, and there are many other applications. Using DataReader in your custom solution allows these scenarios to run faster, or even enhance your .NET basic development skills. You can usually be added value by using DataReader with array.
Download 407DObson.zip
For more information on Hardcore Visual Studio and Pinnacle Publishing, please visit http://www.pinpub.com/
Note: This is not a website of Microsoft Corporation. Microsoft is not responsible for the content of the website.
This article is reproduced from the July July 2004 from Hardcore Visual Studio. Copyright 2004, Pinnacle Publishing, Inc. (unless otherwise stated). all rights reserved. Hardcore Visual Studio is a product of Pinnacle Publishing, Inc.. No part of this article is not used in any form without Pinnacle Publishing, Inc., except for any form of this article (except for short references in the comment). To contact Pinnacle Publishing, Inc., call 1-800-788-1900.
Go to the original English page
