Christa Carpentiere
Scope of application:
Microsoft® Access
Microsoft® Excel
Microsoft® Office
Microsoft® Visual Basic® .NET
Microsoft® Visual Studio® .NET
Summary: See How to get Microsoft Access and Excel data from the Office file and put it in the .NET application, and see how the OLE DB provider is used to write universal code that makes the processing .xls or .mdb file easier.
introduction
Most of the tasks required to use Microsoft Office data in the Microsoft .NET application are the same as the task when using any other data - create a connection, create a DataReader or DataAdapter based on the need to use data, create one or more DataSet to package DataTable, and so on. So I don't plan to repume the ADO.NET document here. The purpose of this paper is to provide the deletion or widely distributed information segment required to press the OFFICE data source. It seems that those who know the strangeness of Jet are not very familiar with ADO.NET, and most of the ADO.NET professionals don't know much about supporting Office programmable technologies. So provide a quick overview, I hope to answer the common problems I have seen.
Let us first discuss the data that connects to Microsoft Excel and Microsoft Access and retrieves data from the data. I think there is the biggest problem in this area - Once people can solve this problem, the Office-specific issue will be resolved, and the remaining problems are mainly focused on how to use ADO.NET. Below I will discuss how to use the getoledbschematable method of the OLEDBConnection object to retrieve the architectural information about runtime Access or Excel data. In this way, you can avoid hard coding information about the data source; the data source is very useful, and data can be obtained.
establish connection
If you want to connect to an Excel spreadsheet or an Access database, you need to create an OLEDBCONNECTION object and deliver a connection string with target data source details. You need to specify Jet 4.0 OLE DB providers for the Provider property.
Connect to Access
Let's take a look at the typical connection string for the Access database:
"Provider = microsoft.jet.Oledb.4.0; data source = c: /database/mymusic.mdb"
This is very simple - it specifies only the Provider and Data Source properties. As mentioned above, Provider is a Jet 4.0 provider, and the Data Source property contains a fully qualified path for the database.
Of course, you don't even need to ensure the security of the Access database, right? This way, you also need to specify the path of the Workgroup Information file (default system.mdw), and provide user ID and password:
"Provider = microsoft.jet.Oledb.4.0; data" & _
"Source = C: /Database/mymusic.mdb;" & _
"Jet OLEDB: System Database =" & _
"C: /Databases/system.mdw;" & _
"User ID = Userx; Password = Userxpassword" Please note that the JET OLE DB-specific properties are required when specifying the .mdw file that controls Access database security. If you want to better control the behavior of the database, you can browse the other properties provided by the Jet OLE DB provider (this provider located in Appendix B: Microsoft Jet 4.0 Ole DB Properties Reference) to control the lock behavior and how to handle specific types. Failure, and so on. Note that only those properties that can be set in the connection string can be accessed; they need to connect to the open state before specifying the properties of the provider, otherwise these properties will not be set.
You can use Microsoft® Visual Studio® .NET to get templates that include all Jet OLE DB provider settings, complete Access connection strings. Create a data connection to the Access database in Server Explorer, and then create an OLEDBConnection object using the data section of the toolbox. The Connectionstring property of the obtained object will contain all the default Jet OLE DB provider properties.
Connect to Excel
Now let's take a look at the exterior of the typical connection string of the Excel spreadsheet:
"Provider = microsoft.jet.Oledb.4.0;" & _
"Data Source = C: /SPReadsheets/calculations.xls;" & _
"Extended Properties = Excel 8.0"
In the case of connecting to an Excel file, you can see that we must use the Extended Properties attributes in the connection string and the Provider and Data SOURCE. If you are Excel 8.0 and later, use the "Excel 8.0" setting. If you want to get other acceptable values of this property, see the "Extended Properties Property Settings" section of Ado Provider Properties and Settings.
You will say: "Ah, how is the security in Excel?" Well, I am afraid no exciting news. You cannot open the connection with the password-protected spreadsheet unless you have opened this spreadsheet manually (for more information, see XL2000: "Could Not Decrypt File" ERROR with password protected file). The description error appears in the Excel ODBC provider, but this behavior is the same in the Jet 4.0 OLE DB provider. Other options are to control access.
Unfortunately, you cannot use Visual Studio .NET to get the template for the Excel connection string. Create some small attempts to create an Excel data connection; however, you will find that its properties are not editable, and the Connectionstring property will remain blank - this is the strange 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, for Excel connections, it is basically necessary to work, but in this case, the connection is simply simple in the user interface (UI). Search data
Now I have understood how to establish a connection with the Office data source, let us see how it retrieves data. For the simplicity of consideration, I plan to use the OLEDBConnection / OLEDBCommand / OLEDBDataReader data search scheme. The same method can be used to build OLEDBDataAdapter after a slightly adjustment and populate DataSet. If you want to know about ADO.NET, see the Accessing Data With ADO.NET section of ".NET Framework Developer's Guide, it will bring you the fun of reading.
Retrieve Access data
Remember when writing Access data retrieval code, specifying the syntax that must be followed when SQL has certain characteristics. Unable to create Access queries in the graphics UI, you cannot access the SQL view, you cannot copy the obtained SQL statement and paste it into the code. No, this should be very simple. The generated SQL code typically has a part but not all desired syntax. Anyone who must write code in the Access development environment will know this, but for a general .NET client application developer, this is the news. The most troubled conditional expression, it needs to separate specific types of data in the WHERE in some way. Date and time values must be separated using digital marks (#). The text value must be separated using single quotes ('). E.g:
Select City, Neighborhood, Saleprice, MonthsonMarket
From ReaState
WHERE ListingDate> # 1/1/04 #
For more information on these issues, see Date and Time Criteria Expressions.
Another point that needs to be remembered seems to be obvious, but it makes people feel confused, so I will discuss this: Make sure that the column name of the Access table does not use the reserved word. You can query the reserved word at SQL RESERVED WORDS. If you use any reserved words, I want to say that if you might be renamed. I know if you have used the database, this action is unlikely, so you can create a query and use it as a replacement query for a table with a problem column. Just use the AS redistribution column, as:
Select Artists.artistName, Genres.genre, Labels.label,
TRACKS.PUBLIC AS TRACK, Releases.ReleaseName
Labels Inner Join ((Artists ...
Remember these points, let's take a look at an example:
Imports system
Imports system.data
Imports 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
'Set the connection string.
Connstring = "Jet OLEDB: System Database =" & _
"C: /Databases/system.mdw;" & _
"Data Source = C: /Database/mymusic.mdb;" & _
"Provider = microsoft.jet.Oledb.4.0;" & _
"User ID =" & Uid & "; Password =" & PWD
'Open the connection.
Conn.connectionstring = connString
Conn.open ()
'Set the command properties.
cmd.connection = conn
cmd.commandtext = "SELECT * FROM MUSIC" & _
"Where artistname = '" & artist & "'"
'Get OledbDataReader
'And processed it.
MusicReader = _
cmd.executeReader (Commandbehavior.CloseConnection)
Try
While (MusicReader.read)
'Data processing.
End while
Finally
MusicReader.close ()
END TRY
Catch exception
'Error handling
END TRY
End function 'getaccessData
Retrieve Excel data
The same reserved word limit is the same as the column name, Excel and Access. In general, if you remember the SQL reserved word and avoid the reserved word when you create any object that may be used as a data source, it will be better. There is still a point to consider.
Excel's syntax also has a strange place. The maximum impact on the code is the syntax for reference to the data set to return.
Note: For the simplest Excel data retrieval, use the electronic form that maintains a similar table format.
The first option is to specify a worksheet and unit set in the table (optional). Need to ensure that the worksheet name is followed by US dollar symbols and cell collections (optional). This cell set is specified by using the start cell and terminating cells in the colon. Then, use parentheses to enclose the entire data identification stroke. SELECT statement using this type of syntax is as follows:
Select SaleSmonth, Totalsales, PercentageChange1Year
From [Sheet1 $ A1: E24]
Another option is to create a naming range in Excel, which will play a role similar to the table. To create a naming range, see Create Named Cell References or Ranges. The range name to use 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 System
Imports system.data
Imports 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
'Set the connection string.
Connstring = "Data Source =" & _
"C: /sspreadsheets/calculations.xls;" & _
"Provider = microsoft.jet.Oledb.4.0;" & _
"Extended Properties = Excel 8.0;"
'Open the connection.
Conn.connectionstring = connString
Conn.open ()
'Set the command properties.
cmd.connection = conn
cmd.commandtext = "SELECT SALESMONTH," & _
"Totalsales, PercentageChange1year," & _
"VoluMediscounts, Profit from [Sheet1 $]"
'Get OledbDataReader
'And processed it.
SalesReader = _
cmd.executeReader (Commandbehavior.CloseConnection)
Try
While (SalesReader.Read)
'Data processing.
End while
Finally
SalesReader.close ()
END TRY
Catch exception
'Error handling
END TRY
End Function 'getExceldata
Search for universal data use metadata
Now connect to the Excel or Access data source and retrieve data, let us go deep into step. Let us see the operations that need to be performed when detection of metadata from one of the data sources, and then you can use the data source to construct data access code. If you want to create a process to provide some generic features, and do not want to bind these functions on a specific data source, the process may have access.
Use OLEDBConnection.GetoledBschematable
To get the desired metadata, you need to use the OLEDBConnection.Getoledbschematable method. The constructor of this method uses an OLEDBSChemaGuid object that represents the OLE DB architecture row, and a group of objects that represent the basic content of the selection criteria to return.
Note: For those who are not familiar with the OLE DB architecture, they are basically a standardized architecture that is defined by ANSI SQL-92. Each architecture routine has a set of columns (called "restriction columns" in the .NET document for the specified constructor. Thus, if the architecture information (eg, the schema information of the column is schema information or the sorting rule), you will clearly know which type of data can be obtained. If you want to learn more, visit Appendix B: Schema Rowsets.
The Object array is defined in the document as a "array of limit values". It is used to determine the data set returned (ie, restricted), and some are similar to the WHERE clause in SQL. For example, it is connected to a workbook with worksheets Alpha, Beta, and Pi. Architecture information is required to determine the columns included in the worksheet beta. Your code will look like this: schemaable = conn.getoledbschematable (OLEDBSChemaGuid.columns, _
New Object () {Nothing, Nothing, _
"Beta", nothing})
Use the OLEDBSChemaGuid.columns field to indicate that the columns architecture should be used due to returning column information. The Column architecture contains Table_catalog, Table_Schema, Table_Name, and Column_Name restriction columns, and needs to provide an object that represents a limit value of each restriction column in the array. By specifying "beta" as a table_name value, the returned column information can be limited to information from "table".
Now, you are very familiar with our friends getoledbschematable, let's take a look at how it is implemented. By using the tables and columns in the data source, all the information required to retrieve data can be obtained, without having to be familiar with the architecture in advance. Let's take a look at an example of using Excel:
Imports system
Imports system.data
Imports 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 CharRay as char () = {",", "}
Dim CharRay2 as char () = {"$"}
DIM CMDString As String
DIM CMDString2 As String
DIM CMD AS New OLEDBCommand
DIM TABLENAME AS STRING
WorkSet.DataSetName = "ExcelData"
Try
'Set 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 ()
'Using architecture information in the data source table
'Pack DATATABLE.
Schematable = _
CONN.GETOLEDBSCHEMATABLE (OLEDBSChemaGuid.tables, _
New Object () {Nothing, Nothing, Nothing, "Table"})
'Fill an array using the table name.
i = schemable.rows.count - 1
DIM TABLESARRAY (i) AS STRINGFOR I = 0 to schematable.rows.count - 1
TableSArray (i) = schematable.rows (i) .Item ("Table_name")
NEXT
'Clear DataTable
Schematable.clear ()
'Using table names and column architecture
'Information to construct the SELECT statement,
'And return data for each table in the data source.
For i = 0 to TableSArray.getLength (0) - 1
'Using the architecture information in the data source column
'Pack DATATABLE.
Schematable = _
CONN.GETOLEDBSCHEMATABLE (OLEDBSChemaGuid.columns, _
New Object () {Nothing, Nothing, _
TableSArray (i) .tostring (), Nothing})
'View column names one by one and attach it to
'SELECT statement
cmdstring = "SELECT"
For x = 0 to schematable.rows.count - 1
cmdstring = cmdstring & _
Schematable.Rows (x) .Item ("Column_name") &_
","
NEXT
cmdstring2 = cmdstring.trimend (Chararray)
'Please note that there is no need to attach "$" to
The 'Table Name - it has been included.
cmdstring2 = cmdstring2 & "from [" & _
TableSArray (i) .tostring () & "]"
'Use the select command and
'OLEDBDataAdapter populates DataSet.
cmd.commandtext = cmdstring2
Workadapter.selectCommand = cmd
Workadapter.selectCommand.connection = conn
TableName = _
TABLESARRY (i) .tostring (). Trimend (Chararray2)
Workadapter.Fill (Workset, TableName)
Schematable.clear ()
NEXT
Catch exception
'Error handling
Finally
CONN.CLOSE ()
END TRY
Return Workset
End Function 'getExcelschema
As you can see, the code involved is quite simple. If you want to do the same operation to the Access database, the only truly difference is to connect the string without using the table name as a worksheet and allows it to be used in the SELECT statement.
In addition, this method can be used for the SQL ServerTM database, the effect is also very good; or can be modified to perform other operations based on the schema information acquired from the data source. This will be very useful if you want to perform any type of lookup or document management.
in conclusion
Thank you very much for completing this short introduction to the use of Office data and ADO.NET. It is not a special task, and I think this task should not have such a high difficulty, just because some basic information of this task is not easy to get. Whether you want to drag the information from the department's Access database, you want to collect data from each month's annual cost spreadsheet, I hope to provide you with a platform starting. Reference
Microsoft ADO.NET
© 2004 Microsoft Corporation All rights reserved. all rights reserved. Use the specification.
