Introduction 2

Chia sẻ: Bui Tuan | Ngày: | Loại File: PDF | Số trang:7

Thêm vào BST

Báo xấu

76
lượt xem 4
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Introduction The DataSet is a disconnected, in-memory relational database that provides sophisticated navigational capabilities. It acts as a container for other objects including DataTable, DataColumn, DataRow, and DataRelation.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Introduction 2

[ Team LiB ] Introduction The DataSet is a disconnected, in-memory relational database that provides sophisticated navigational capabilities. It acts as a container for other objects including DataTable, DataColumn, DataRow, and DataRelation. The DataAdapter works with the DataSet to update the data source with changes made offline to the DataSet. You can also data bind a DataSet to a variety of Windows Forms and Web Forms controls, in particular, any control that supports the IList interface. The DataSet maintains both current and original versions of its data. Although data appears to be changed, it is not permanently changed until the AcceptChanges( ) method is called either explicitly or implicitly to commit the changes. Recipe 2.6 shows how to access rows marked for deletion. The DataReader provides forward-only, read-only access to a result set. The DataReader offers the best performance for accessing data by avoiding the overhead associated with the DataSet. The Connection object underlying a DataReader remains open and cannot be used for any other purpose while data is being accessed. This makes the DataReader unsuitable for communicating data remotely between application tiers, or interacting with the data dynamically. If you want to discard a result set in a DataReader before the entire result set has been read, call the Cancel( ) method of the DataReader before calling the Close( ) method. This discards the results on the server so they are not transmitted to the client. Simply calling Close( ) causes the remaining results to be pulled to the client to empty the stream. Since the DataReader reads a result set stream directly from a connection, there is no way to know the number of records in a DataReader. Recipe 2.7 demonstrates techniques that simulate a record count for a DataReader and discusses limitations of the techniques. You can define DataSet and DataReader object schemas programmatically or infer them from a database schema. Retrieving schema information from the database has its limitations. For example, data relations cannot be created in a DataSet from the database schema. The DataSet is data source independent and uses .NET Framework data types to define column schema in tables. These data types are not the same as .NET data provider types; the provider data types are mapped to .NET Framework data types. Recipe 2.8 shows the mappings for SQL Server, Oracle, OLE DB,
and ODBC .NET data providers to .NET Framework data types as well as the DataReader type accessors for each. The DataAdapter can map table and column names in the database to tables and columns with different names in a DataTable. This allows the application to use different column and table names than are used by the database. The DataAdapter automatically maps names when retrieving and updating data. Recipe 2.16 demonstrates table and column mapping. A variety of error information is available when a DataAdapter fails while updating data. Recipe 2.15 shows what error information is available and how to work with it. In addition to recipes for working with the DataSet, DataReader, and DataAdapter classes, this chapter covers the following: Strongly typed DataSets A strongly typed DataSet is a collection of classes that inherit from and extend the DataSet, DataTable, and DataRow classes, providing additional properties, methods, and events, and making them easier to use. Because they are typed, you'll get type mismatch and other errors at compilation rather than at runtime (also, strongly typed DataSets work with Visual Studio .NET's IntelliSense). They are, however, slightly slower than untyped DataSet objects because of extra overhead. Because they are typed, they can make maintaining interfaces in distributed applications more complicated and difficult to administer. Recipe 2.3 discusses the different ways to create and use a strongly typed DataSet. Recipe 2.18 and Recipe 2.19 show how to override the default naming used by and behavior of a strongly typed DataSet. Stored procedures Stored procedure output parameters are generally used to return results from a single row and are slightly faster than a DataReader for this purpose with connection pooling enabled; without connection pooling, the opposite is true. Additionally, the DataReader is capable of returning metadata for the row. Accessing output parameters is straightforward with disconnected classes and a data adapter, but not when using the DataReader. Recipe 2.9 shows how. Stored procedures can also return a return value parameter, which usually returns status or error information. Recipe 2.12 shows how get a stored procedure return value.
Passing input arguments to a stored procedure is straightforward. Recipe 2.14 shows how to pass null values. SQL has a RAISERROR function that lets you generate custom errors from the stored procedure and return them to the caller. Recipe 2.10 shows how to raise and handle stored procedure errors. In addition to stored procedures, Oracle has packages that serve as containers for stored procedures and functions. Recipe 2.20 shows how to use Oracle packages from ADO.NET. Scalar functions are routines that take one or more parameters and return a single value. Recipe 2.13 shows how to execute a scalar function and get the return value. In addition to using parameters with stored procedures, ADO.NET allows you to execute parameterized SQL statements. Recipe 2.21 shows how to create and execute these statements. Queries SQL Server supports batch queries that return multiple result sets from a single request to the server. Recipe 2.4 shows how to process the result sets using both a DataSet and a DataReader. Sometimes it's useful to know whether a query returned any records; therefore, Recipe 2.11 shows you how to find out when using both a DataTable and a DataReader. Web services and messaging Web services allow distributed applications running on disparate platforms to communicate using open standards and protocols. Recipe 2.5 shows how to create a web service that processes a query request, and how to call the web service from a .NET application. Messaging allows applications running on disparate platforms to communicate whether they are connected or disconnected. Recipe 2.22 shows how to use messaging to query a database. [ Team LiB ]
[ Team LiB ] Recipe 1.19 Connecting to a Text File Problem You want to use ADO.NET to access data stored in a text file. Solution Use the OLE DB Jet provider to access data in a text file. The sample code creates an OleDbDataAdapter that uses the Jet OLE DB provider to load the contents of the text file Categories.txt, shown in Example 1-13, into a DataTable and displays the contents in a data grid on the form. Example 1-13. File: Categories.txt "CategoryID","CategoryName","Description" 1,"Beverages","Soft drinks, coffees, teas, beers, and ales" 2,"Condiments","Sweet and savory sauces, relishes, spreads, and seasonings" 3,"Confections","Desserts, candies, and sweet breads" 4,"Dairy Products","Cheeses" 5,"Grains/Cereals","Breads, crackers, pasta, and cereal" 6,"Meat/Poultry","Prepared meats" 7,"Produce","Dried fruit and bean curd" 8,"Seafood","Seaweed and fish" The C# code is shown in Example 1-14. Example 1-14. File: ConnectTextFileForm.cs // Namespaces, variables, and constants using System; using System.Configuration; using System.Windows.Forms; using System.Data; using System.Data.OleDb; // . . . // Create the data adapter to retrieve all rows from text file. OleDbDataAdapter da =
new OleDbDataAdapter("SELECT * FROM [Categories.txt]", ConfigurationSettings.AppSettings["TextFile_0119_ConnectString"]); // Create and fill the table. DataTable dt = new DataTable("Categories"); da.Fill(dt); // Bind the default view of the table to the grid. categoriesDataGrid.DataSource = dt.DefaultView; Discussion The Jet OLE DB provider can read records from and insert records into a text file data source. The Jet database engine can access other database file formats through Indexed Sequential Access Method (ISAM) drivers specified in the Extended Properties attribute of the connection. Text files are supported with the text source database type as shown in the following example: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\MyTextFileDirectory; Extended Properties="text;HDR=yes;FMT=Delimited"; The Extended Properties attribute can, in addition to the ISAM version property, specify whether or not tables include headers as field names in the first row of a range using an HDR attribute. It is not possible to define all characteristics of a text file through the connection string. You can access files that use non-standard text delimiters and fixed-width text files by creating a schema.ini file in the same directory as the text file. As an example, a possible schema.ini file for the Categories.txt file used in this solution is: [Categories.txt] Format=CSVDelimited ColNameHeader=True MaxScanRows=0 Character=OEM Col1=CategoryID Long Width 4 Col2=CategoryName Text Width 15 Col3=Description Text Width 100 The schema.ini file provides the following schema information about the data in the text file: • Filename
• File format • Field names, widths, and data types • Character set • Special data type conversions The first entry in the schema.ini file is the text filename enclosed in square brackets. For example: [Categories.txt] The Format option specifies the text file format. Table 1-8 describes the different options. Table 1-8. Schema.ini format options Format Description Fields are delimited with commas: CSV Format=CSVDelimited Delimited This is the default value. Fields are delimited with a custom character. You can use any single Custom character except the double quotation mark (") as a delimiter: Delimited Format=Delimited(customCharacter) Fields are fixed length: Fixed Format=FixedLength Length If the ColumnNameHeader option is True, the first line containing the column names must be comma-delimited. Fields are delimited with tabs: Tab Delimited Format=TabDelimited You can specify the fields in the text file in two ways: • Include the field names in the first row of the text file and set the ColNameHeader option to True. • Identify each column using the format ColN (where N is the one-based column number) and specify the name, width, and data type for each column. The MaxScanRows option indicates how many rows should be scanned to automatically
determine column type. A value of 0 indicates that all rows should be scanned. The ColN entries specify the name, width, and data type for each column. This entry is required for fixed-length formats and optional for character-delimited formats. The syntax of the ColN entry is: ColN=columnName dataType [Width n] The parameters in the entry are: columnName The name of the column. If the column name contains spaces, it must be enclosed in double quotation marks. dataType The data type of the column. This value can be Bit, Byte, Currency, DateTime, Double, Long, Memo, Short, Single, or Text. DateTime values must be in one of the following formats: dd-mmm-yy, mm-dd- yy, mmm-dd-yy, yyyy-mm-dd, or yyyy-mmm-dd, where mm is the month number and mmm are the characters specifying the month. Width n The literal value Width followed by the integer value specifying the column width. The Character option specifies the character set; you can set it to either ANSI or OEM. [ Team LiB ]