Access Microsoft Office Data from. Net Applications [reposted from msdn]

Release date: 8/12/2004 | update date: 8/12/2004

Christa carpentiere

Applicability:

Microsoft Access

Microsoft Excel

Microsoft Office

Microsoft Visual Basic. net

Microsoft Visual Studio. NET

Summary: View how to obtain Microsoft Access and Excel Data from the office file and put it in.. NET application, and view how the ole db Provider is used for compiling and processing. XLS or. common code that makes MDB files easier.

Introduction

In Microsoft. most of the tasks required to use Microsoft Office Data in the. NET application are the same as those required to use any other data-create a connection, create a datareader or dataadapter, create one or multiple dataset are used to encapsulate the datatable of relevant data, and so on. Therefore, I do not intend to re-compile the ADO. Net document here. The purpose of this article is to provide missing or widely distributed information segments required to write basic data retrieval code by office data source. It seems that those who know the uniqueness of jet are not very familiar with ADO. net, while most ADO. Net professionals are not very familiar with office programmable technology. Therefore, we provide a quick overview and hope to answer the frequently asked questions I have seen.

First, let's discuss the basics of connecting to and retrieving data from Microsoft Excel and Microsoft Access. I think there are the biggest problems in this field-usually once people can solve this problem, the specific problems of office will be solved, and the other problems mainly focus on how to use ADO. net. I will discuss how to useOledbconnectionObjectGetoledbschematableMethod to retrieve the architecture information about the access or Excel Data at runtime. In this way, you can avoid hard encoding information about the data source. The data source is very useful and can obtain data from it.

Back to Top to establish a connection

To connect to an Excel worksheet or an Access database, you must create an oledbconnection object and pass it a connection string with detailed information about the target data source. You must specify the JET 4.0 ole db provider for the provider attribute.

Connect to access

Let's take a look at the typical connection string used for Access database:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source= C:\Databases\mymusic.mdb"

This is very simple-it only specifies the provider and Data Source attributes. As mentioned above, provider is the JET 4.0 provider, whileData SourceThe attribute contains the fully qualified path of the database.

Of course, you even need to ensure the security of the Access database, right? In this way, you also need to specify the path of the workgroup information file (system. MDW by default) and provide the user ID and password:

"Provider=Microsoft.Jet.OLEDB.4.0;Data " & _ "Source=C:\Databases\mymusic.mdb; " & _"Jet OLEDB:System database=" & _"C:\Databases\system.mdw; " & _"User ID=UserX;Password=UserXPassword"

Note: When specifying the. MDW file that controls access database security, you must use the specific attributes of jet ole db. If you want to better control database behavior, you can browse other properties provided by the jet ole db Provider (which is located in Appendix B: Microsoft JET 4.0 OLE DB properties reference, to control the locking behavior and how to handle specific types of failures. Note that only the attributes that can be set in the connection string can be accessed. Before specifying the attributes of the provider, the connection must be in the open state. Otherwise, these attributes cannot be set.

You can use Microsoft Visual Studio. NET to obtain a template that includes all the settings of the jet ole db provider and the complete access connection string. Create a data connection with the ACCESS database in the server resource manager, and then createOledbconnectionObject. TheConnectionstringThe property contains all default jet ole db Provider properties.

Connect to excel

Now, let's take a look at the appearance of a typical connection string in an Excel Worksheet:

"Provider=Microsoft.Jet.OLEDB.4.0;" & _"Data Source=C:\Spreadsheets\calculations.xls;" & _"Extended Properties=Excel 8.0"

When connecting to an Excel file, we can see that we must useExtended PropertiesProperties and provider and data source. For Excel 8.0 and later versions, use "Excel 8.0. For more information about other acceptable values for this property, see the extended properties property Settings section of ADO provider properties and settings.

You will say, "Ah, but how is the security in Excel ?" Well, I'm afraid there is no exciting news. You cannot open a connection to a password-protected workbook, unless you have manually opened this workbook in Excel (for more information, see XL2000: "cocould not decrypt file" error with password protected file ). The description error occurs in the Excel ODBC provider, but the behavior is the same in the JET 4.0 ole db Provider. Other options are to delete the workbook password and use some other security mechanisms (for example, restrict the permissions of the folder where the file is located) to control access.

Unfortunately, you cannot use Visual Studio. NET to obtain the template of the Excel connection string. Make some small attempts to create an Excel data connection. However, you will find that its attributes cannot be edited, andConnectionstringProperty will be left blank-this is a strange feature in the IDE (integrated development environment. For more information, see PRB: cannot configure data connection to excel files in Visual Studio. NET. In this way, you basically need to work on your own for Excel connections, but in this case, coding the connections is as simple as creating connections on the user interface (UI.

Back to Top

Now that we understand how to establish a connection with the office data source, let's see how it retrieves data. For simplicity, I plan to useOledbconnection/oledbcommand/oledbdatareaderData Retrieval solution. After slight adjustment, the same method can be used for buildingOledbdataadapterAnd fill in dataset. For more information about ADO. net, see accessing data with ADO. net in the. NET Framework developer's guide.

Retrieve access data

When writing access data retrieval code, remember that the syntax that must be followed when specifying SQL has certain features. You cannot create an access query in the graphic UI, and you cannot access the SQL view. Instead, you cannot copy and paste the obtained SQL statement into the code. No, this should be very simple. The generated SQL code usually has a part but not all of the required syntax. Anyone who has to write code in the access development environment will know this, But for general. Net client application developers, this is regarded as news. The most annoying thing is the conditional expression, which needs to separate specific types of data in the WHERE clause in some way. The date and time values must be separated by a number sign. Text values must be separated by single quotes. For example:

SELECT City, Neighborhood, SalePrice, MonthsOnMarketFROM RealEstateWHERE ListingDate > #1/1/04#

For more information about these issues, see date and time criteria expressions.

Another point that needs to be remembered seems obvious but confusing, so I will discuss this: Make sure that the column names in the access table do not use reserved words. You can query Reserved Words in SQL reserved words. If you use any reserved words, I want to rename the column if possible. I know that this operation is unlikely if you are already using a database. If necessary, you can create a query and use it as a replacement query for tables with problematic columns. Only use as to rename a column, as shown in the following figure:

SELECT Artists.ArtistName, Genres.Genre, Labels.Label, Tracks.Public AS Track, Releases.ReleaseNameFROM (Labels INNER JOIN ((Artists...

Remember these points. Let's take a look at an example:

Imports systemimports system. dataimports system. data. oledb... public Function getaccessdata (byval UID as string, _ byval PWD as string, byval artist as string) dim conn as new oledbconnection dim musicreader as oledbdatareader dim cmd as new oledbcommand dim connstring as string dim I as integer try 'sets the connection string. Connstring = "jet oledb: System database =" & _ "C: \ databases \ system. MDW; "& _" Data Source = c: \ databases \ mymusic. MDB; "& _" provider = Microsoft. jet. oledb.4.0; "& _" User ID = "& uid &"; Password = "& PWD" Open the connection. Conn. connectionstring = connstring conn. open () 'sets the command attributes. Cmd. Connection = conn cmd. commandtext = "select * from music" & _ "where artistname = '" & artist & "'" 'obtain oledbdatareader and process it. Musicreader = _ cmd. executereader (commandbehavior. closeconnection) Try while (musicreader. Read) 'to process data. End while finally musicreader. Close () end try catch ex as exception 'end try end function' getaccessdata

Retrieving Excel Data

For column names, Excel and access have the same reserved word restrictions. Generally, it is better to remember SQL Reserved Words and avoid reserved words when creating any object that may be used as a data source. There is another point to consider.

The syntax of Excel is also strange. The most influential item in the Code is the syntax used to reference the dataset to be returned.

Note:: For the simplest excel data retrieval, use a workbook similar to table-style maintenance.

The first option is to specify the worksheet and the cell set in the table (optional ). Make sure that the worksheet name is followed by the dollar sign and the cell set (optional ). Use a colon to separate the start and end cells in the set to specify this cell set. Then, use parentheses to enclose the entire data identity string. The SELECT statement using this type of syntax may be as follows:

SELECT SalesMonth, TotalSales, PercentageChange1Year FROM [Sheet1$A1:E24]

Another option is to create a naming range in Excel, which is similar to a table. To create a name range, see create named cell references or ranges. The name of the range to be used is like the table name in the SELECT statement:

SELECT SalesMonth, TotalSales, PercentageChange1Year FROM SalesHighlights

Remember these points. Let's take a look at an example:

Imports systemimports system. dataimports system. data. oledb... public Function getexceldata () dim conn as new oledbconnection dim salesreader as oledbdatareader dim connstring as string dim cmd as new oledbcommand try 'sets the connection string. Connstring = "Data Source =" & _ "C: \ spreadsheets \ calculations.xls;" & _ "provider = Microsoft. jet. oledb.4.0; "& _" extended properties = Excel 8.0; "'Open the connection. Conn. connectionstring = connstring conn. open () 'sets the command attributes. Cmd. connection = conn cmd. commandtext = "select salesmonth," & _ "totalsales, percentagechange1year," & _ "volumediscounts, profit from [sheet1 $]" 'get oledbdatareader 'and perform some processing on it. Salesreader = _ cmd. executereader (commandbehavior. closeconnection) Try while (salesreader. Read) 'to process data. End while finally salesreader. Close () end try catch ex as exception 'end try end function' getexceldata

Back to Top use metadata for common data retrieval

Now we can connect to the Excel or access data source and retrieve the data. Let's take a further step. Let's take a look at the operations that need to be performed to retrieve metadata from one data source. Then you can use the data source to construct the data access code. This process may be useful if you want to create a process that provides some common features and do not want to bind these features to a specific data source.

Use oledbconnection. getoledbschematable

To obtain the required metadata, you must useOledbconnection. getoledbschematableMethod. The constructor of this method usesOledbschemaguidObjects, and a group of objects that represent the basic content of the Selection Conditions for the schema information to be returned.

Note:: For those unfamiliar with the ole db architecture row set, they are basically a standardized architecture built by the database defined by the ANSI SQL-92. Each schema row set has a set of columns that provide the definition metadata for the specified structure (referred to as the "restriction column" in the. NET document "). In this way, if the Request Architecture Information (for example, the architecture information of the column or the Architecture Information of the sorting rule), you will know the type of data that can be obtained. For more information, visit Appendix B: schema rowsets.

ObjectThe array is defined as an array of restricted values in the document ". It is used to determine (that is, limit) the returned dataset, which is similar to the WHERE clause in SQL. For example, connect to a workbook with worksheet Alpha, beta, and PI. The architecture information is required to determine the columns included in the worksheet beta. Your code will be as follows:

schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, _New Object() {Nothing, Nothing, _"Beta", Nothing})

UseOledbschemaguid. ColumnsField to indicate that columns architecture should be used to return column information. The columns architecture contains the table_catalog, table_schema, table_name, and column_name restriction columns. You must provide objects that represent the limit values of each restriction column in the array. By specifying "Beta" as the table_name value, you can limit the returned column information to only information from the "table.

Now you are very familiar with our friends.Getoledbschematable, Let's see how it is implemented. By traversing tables and columns in the data source, you can retrieve all the information required for data retrieval without having to familiarize yourself with the architecture in advance. Let's take a look at an example of using Excel:

Imports systemimports system. dataimports system. data. oledb... public Function getexcelschema (byval xlspath as string) as dataset dim schematable as new datatable dim workadapter as new oledbdataadapter dim workset as new dataset dim conn as new oledbconnection dim I as integer dim X as integer dim chararray as char () = {", "," "} dim chararray2 as char () = {" $ "} dim character string as string dim character strin G2 as string dim cmd as new oledbcommand dim tablename as string workset. datasetname = "exceldata" try 'sets the connection string. Dim connstring as string = _ "Data Source =" & xlspath & _ "; provider = Microsoft. Jet. oledb.4.0;" & _ "extended properties = Excel 8.0" 'Open the connection. Conn. connectionstring = connstring conn. open () 'fill the able with the schema information in the data source table. Schematable = _ conn. getoledbschematable (oledbschemaguid. Tables, _ new object () {nothing, "table"}) 'Fill the array with the table name. I = schematable. rows. count-1 dim tablesarray (I) as string for I = 0 to schematable. rows. count-1 tablesarray (I) = schematable. rows (I ). item ("table_name") Next 'clears the datatable schematable. clear () 'use the table name and column scheme' information to construct the SELECT statement, 'and return data for each table in the data source. For I = 0 to tablesarray. getlength (0)-1 'fill the able with the schema information in the data source column. Schematable = _ Conn. getoledbschematable (oledbschemaguid. columns, _ new object () {nothing, nothing, _ tablesarray (I ). tostring (), nothing}) 'view the column names one by one and append them to 'select statement 'explain string = "select" for x = 0 to schematable. rows. count-1 bytes string = bytes string & _ schematable. rows (X ). item ("column_name") & _ "," Next vertex string2 = struct string. trimend (chararray) 'note that you do not need to append "$" to the 'table name-it is included. Using string2 = Using string2 & "from [" & _ tablesarray (I). tostring () & "]" 'Fill the dataset with the SELECT command and 'oledbdataadapter. Cmd. commandtext = javasstring2 workadapter. selectcommand = cmd workadapter. selectcommand. connection = conn tablename = _ tablesarray (I ). tostring (). trimend (chararray2) workadapter. fill (workset, tablename) schematable. clear () next catch ex as exception 'Finally Conn. close () end try return workset end function 'getexcelschema

As you can see, the involved code is quite simple. If you want to perform the same operation on the Access database, the only difference is that the connection string does not need to format the table name as a worksheet and make it available in the SELECT statement.

In addition, this method can be used in the SQL Server database, and the effect is also good; or you can modify it so that it can perform other operations based on the schema information obtained from the data source. This is useful if you want to try to perform any type of search or document management.

Back to Top conclusion

Thank you very much for completing this brief introduction to using office data with ADO. net. It is not a special task, and I don't think it should be so difficult, just because some basic information of this task is not easy to obtain. Whether you only want to drag information from the Department's access database or collect data from each person's monthly expense workbook, I want to provide you with a start platform.