Connecting to a Text File

[ 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
Nội dung trích xuất từ tài liệu:
Connecting to a Text File[ Team LiB ]Recipe 1.19 Connecting to a Text FileProblemYou want to use ADO.NET to access data stored in a text file.SolutionUse 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 toload the contents of the text file Categories.txt, shown in Example 1-13, into a DataTableand displays the contents in a data grid on the form.Example 1-13. File: Categories.txtCategoryID,CategoryName,Description1,Beverages,Soft drinks, coffees, teas, beers, and ales2,Condiments,Sweet and savory sauces, relishes, spreads, and seasonings3,Confections,Desserts, candies, and sweet breads4,Dairy Products,Cheeses5,Grains/Cereals,Breads, crackers, pasta, and cereal6,Meat/Poultry,Prepared meats7,Produce,Dried fruit and bean curd8,Seafood,Seaweed and fishThe C# code is shown in Example 1-14.Example 1-14. File: ConnectTextFileForm.cs// Namespaces, variables, and constantsusing 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;DiscussionThe Jet OLE DB provider can read records from and insert records into a text file datasource. The Jet database engine can access other database file formats through IndexedSequential Access Method (ISAM) drivers specified in the Extended Properties attributeof the connection. Text files are supported with the text source database type as shown inthe 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, specifywhether or not tables include headers as field names in the first row of a range using anHDR 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 bycreating a schema.ini file in the same directory as the text file. As an example, a possibleschema.ini file for the Categories.txt file used in this solution is:[Categories.txt]Format=CSVDelimitedColNameHeader=TrueMaxScanRows=0Character=OEMCol1=CategoryID Long Width 4Col2=CategoryName Text Width 15Col3=Description Text Width 100The schema.ini file provides the following schema information about the data in the textfile: • Filename • File format • Field names, widths, and data types • Character set • Special data type conversionsThe first entry in the schema.ini file is the text filename enclosed in square brackets. Forexample:[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=CSVDelimitedDelimited This is the default value. Fields are delimited with a custom character. You can use any singleCustom character except the double quotation mark () as a delimiter:Delimited Format=Delimited(customCharacter) Fields are fixed length:Fixed Format=FixedLengthLength If the ColumnNameHeader option is True, the first line containing the column names must be comma-delimited. Fields are delimited with tabs:TabDelimited Format=TabDelimitedYou 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 automaticallydetermine 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 isrequired for fixed-length formats and optional for character-delimited formats. Thesyntax 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 ]