上一篇
下一篇
07
09
AE编程连接ArcSDE
作者:独木舟 日期:2008-07-09
Description:
The following examples will illustrate how you can use ADO and the ESRI OLE DB provider, in a Visual Basic environment, to establish a connection to one of the four supported ESRI workspace factories.
ArcSDE database.
either
Provider=ESRI.GeoDB.OLEDB.1;Location=server name;Data Source=database name; User Id=user name;Password=password; Extended Properties=WorkspaceType= esriDataSourcesGDB.SDEWorkspaceFactory.1;Geometry=WKB|OBJECT;Instance=ArcSDE service;Version=ArcSDE version
or
Provider=ESRI.GeoDB.OLEDB.1;Extended Properties=WorkspaceType= esriDataSourcesGDB.SDEWorkspaceFactory.1;ConnectionFile=path to and name of binary ArcSDE connection file
VB6 Example
Dim sConString As String
DIM SDEAdocon as ADODB.connection
Set SDEAdoCon = New ADODB.Connection
'Set a limit on the time to connect
SDEAdoCon.ConnectionTimeout = 30
either
sConString = "Provider=ESRI.GeoDB.OleDB.1;Location=fabio;Data Source=world;" & _
"User Id=avtest;Password=avtest;Extended Properties=workspacetype=esriDataSourcesGDB." & _
"SdeWorkspaceFactory.1;Geometry=WKB;Instance=5152;Version=SDE.DEFAULT"
or
sConString = "Provider=ESRI.GeoDB.OleDB.1;Extended" & _
"Properties=workspacetype=esriDataSourcesGDB.SdeWorkspaceFactory.1;" & _
"ConnectionFile=c:\Winnt\Profiles\jill2846\Application Data\ESRI\ArcCatalog\connection to springs.sde"
SDEAdoCon.Open sConString
The version parameter is optional in the connection string; a list of available ArcSDE versions will be returned if it is not included. Please note that for connections to an ArcSDE service running on an oracle platform, this parameter is case sensitive. If the version parameter is entered incorrectly, the request to open the connection will fail.
A successful connection to the ArcSDE service will return all the available databases (for SQL Server platforms) and database objects. Access to these objects is controlled by DBMS level user permissions.
The data source parameter is only (optionally) required for connection to ArcSDE on a SQL Server platform with multiple databases. If the data source parameter is included, database objects can simply be referenced by the schema object name. For example, "select * from us_states". If the data source parameter is omitted, the fully qualified name of the schema object must be supplied - database name.schema name.object name. For example, "select * from world.world.us_states".
For ArcSDE connection to an oracle database, again all available schema objects will be returned. Assuming the connected user has the appropriate permissions and schema object synonyms, it is not necessary to fully qualify the database object name. Without the correct schema object synonyms, the object must be identified using schema name.object name.
The user name and password required for an ArcSDE connection are the RDBMS user name and password.
Personal Geodatabase (Access .mdb file).
The Access workspace factory requires the path to and name of a Microsoft Access® file (.mdb).
Provider=ESRI.GeoDB.OLEDB.1;Data Source=access file(.mdb);
Extended Properties=WorkspaceType= esriDataSourcesGDB.AccessWorkspaceFactory.1;Geometry=WKB|OBJECT
VB6 Example
Dim sConString As String
Dim JETAdocon as ADODB.connection
Set JETAdoCon = New ADODB.Connection
sConString = "Provider=ESRI.GeoDB.OLEDB.1;" & _
"Data Source=d:\testdata\access\us_states.mdb;" & _
"Extended Properties=workspacetype=esriDataSourcesGDB.AccessWorkspaceFactory.1;Geometry=WKB"
JetAdoCon.Open sConString
The Shapefile workspace factory requires the path to a directory which contains shapefile data; not a specific shapefile.
Provider=ESRI.GeoDB.OLEDB.1;Data Source=path_to_shapefile directory;
Extended Properties=WorkspaceType=esriDataSourcesFile.ShapefileWorkspaceFactory.1;Geometry=WKB|OBJECT
VB6 Example
Dim sConString As String
Dim ShpAdocon as adodb.connection
Set ShpAdoCon = New ADODB.Connection
sConString = "Provider=ESRI.GeoDB.Oledb.1;Data Source=d:\oledb_testdata\shapefiles\;" & _
"Extended Properties=workspacetype=esriDataSourcesFile.ShapefileWorkspaceFactory.1;Geometry=WKB"
ShpAdoCon.Open sConString
The Coverage workspace factory also requires a path to a coverage directory; not a specific coverage.
Provider=ESRI.GeoDB.Oledb.1;Data Source=path to A/I coverage directory;
Extended Properties=workspacetype=esriDataSourcesFile.ArcInfoWorkspaceFactory.1;Geometry=WKB|OBJECT
VB6 Example
Dim sConString As String
Dim CovAdocon ad adodb.connection
Set CovAdoCon = New ADODB.Connection
sConString = "Provider=ESRI.GeoDB.Oledb.1;Data Source=d:\workspace\;Extended " & _
"Properties=workspacetype=esriDataSourcesFile.ArcInfoWorkspaceFactory.1;Geometry=WKB"
CovAdoCon.Open sConString
ADO programming considerations for the ESRI OLE DB provider:
The following recordset object.open method parameters are supported :
Source - a valid command object variable name, an SQL statement, a table name, a stored procedure call, or the file name of a persisted (saved) recordset.
ActiveConnection - a valid connection object or a string that contains connection string parameters.
Cursortype - adOpenForwardOnly.
LockType - adLockReadonly, adLockPessimistic, adLockOptimistic, adLockBatchOptimistic.
Options - all options are supported
Unregistered tables in a geodatabase cannot be edited with the ESRI OLE DB provider. You must either register the table with the geodatabase, or use a native OLE DB provider for the RDBMS data source you are connected.
DMBS Insert/Update/Delete SQL operations applied directly against an ADO connection to a personal or ArcSDE geodatabase feature class or registered table are NOT supported and are not recommended unless you are very familiar with the data structure at source (such as associated indices to be maintained). To edit registered feature classes or tables, all updates must be applied against the ADO recordset. However, such operations are supported against un-registered tables. The ADO command syntax for this operation is: connection obj.Execute(SQL statement)
Neither of the file based data sources (shapefiles and coverages) support Insert/Update/Delete commands applied against the ADO connection. All updates to these data sources should be undertaken via the ADO recordset.
For best results when applying a number of updates to a recordset, always use an edit transaction. This significantly improves the performance for updating. The edit transaction is opened on the connection object - connection object.begintrans.
To successfully disconnect a recordset from a connection, set the cursorlocation to asUseClient and set the lock type to adLockBatchOptimistic. Propogating any changes back to the data source, when the recordset is re-assigned to an active connection, requires support for the recordset updatebatch method. The ESRI OLE DB provider does not support this functionality at present.
If you wish to graphically represent the tabular recordset, use an ADO/OLEDB recordset with a MSChart object in Visual Basic. For best results, set recordset cursorlocation to adUseClient and set the MSChart object's datasource property to be the ADO recordset.
ADO object methods/properties/collections currently supported by ESRI OLE DB provider
| ADO Object | Methods/Properties/Collections | Options |
| Command | Method | Execute |
| --------- | Property | ActiveConnection |
| --------- | --------- | Command Text |
| ---------- | Collection | Parameters |
| ---------- | ---------- | Properties |
| Connection | Method | BeginTrans |
| ---------- | ---------- | Close |
| ---------- | ---------- | CommitTrans |
| ---------- | ---------- | Execute |
| ---------- | ---------- | Open |
| ---------- | ---------- | Rollback |
| ---------- | ---------- | OpenSchema |
| ---------- | Property | ConnectionTimeout |
| ---------- | ---------- | IsolationLevel |
| ---------- | ---------- | Mode |
| ---------- | ---------- | Provider |
| ---------- | ---------- | State |
| ---------- | ---------- | Version |
| ---------- | Collection | Errors |
| ---------- | ---------- | Properties |
| Error | Property | Description |
| ---------- | property | ActualSize |
| ---------- | ----------- | Attributes |
| ---------- | ----------- | DefinedSize |
| ---------- | ----------- | Name |
| ---------- | ----------- | NumericScale |
| ---------- | ----------- | Precision |
| ---------- | ----------- | Type |
| ---------- | ----------- | Value |
| Recordset | Method | AddNew |
| ---------- | ----------- | Close |
| ---------- | ----------- | Delete |
| ---------- | ----------- | GetRows |
| ---------- | ----------- | Move |
| ---------- | ----------- | MoveFirst |
| ---------- | ----------- | MoveNext |
| ---------- | ----------- | Open |
| ---------- | ----------- | Requery |
| ---------- | ----------- | Supports |
| ---------- | ----------- | Update |
| ---------- | Property | ActiveConnection |
| ---------- | ---------- | CacheSize |
| ---------- | ----------- | CursorType |
| ---------- | ----------- | Filter |
| ---------- | ----------- | LockType |
| ---------- | ----------- | Source |
| ---------- | Collection | Fields |
| ---------- | ---------- | Properties |
| Products: Engine, ArcView
Platforms: Windows Requires: A personal Geodatabase (Access) .mdb file Minimum ArcGIS Release: 9.0 |
How to use:
- Creating a new folder - OLEDBTest for a new Visual Basic project.
- Start Visual Basic 6.0 and create a new standard EXE project.
- Change the project name, form name and form caption settings to whatever is appropriate for your data/application.
- Choose Project | Components from the main menu to display the Components dialog.
- Navigate the controls list box until you find the Microsoft Hierarchical FlexGrid Control 6.0 item. Add this control to the project by clicking the check box to the left of the item label.
- Click OK to close the Components dialog and commit your changes to the project.
- Double-click MSHFlexGrid icon from tools bar to add this grid control to the form and adjust the boundary of this control if necessary.
- Double-click CommandButton icon from tools bar to add a command button to the form, adjust the location of the command button and change the caption property to "Execute".
- Choose Project | References from the main menu to display the References dialog.
- Navigate the Available References list box until you find the Microsoft ActiveX Data Objects 2.5 Library. Add this reference to the project by clicking the check box to the left of the item label.
- Click OK to close the References dialog and commit your changes to the project. Setting this reference bring in all the type information for ADO library so that the Visual Basic 6.0 code can use the library in the project.
- Double-click "Execute" command button on the form to open the source code window.
- Add the following lines of code to the Command1_Click() event:
- The data source used in this example is called "usa.mdb" but you may substitute your own .mdb file.
Dim pAdoConn As ADODB.Connection Dim pAdoRS As ADODB.Recordset Dim sSQLstr As String '++ Create ADO Connection and recordset objects Set pAdoConn = New ADODB.Connection Set pAdoRS = New ADODB.Recordset '++ Utilize the ESRI OLE DB provider pAdoConn.ConnectionString = "Provider=ESRI.GeoDB.OLEDB.1;" & _ "Data Source=D:\Data\Access\USA\usa.mdb;" & _ "Extended Properties=workspacetype=esriDataSourcesGDB.AccessWorkspaceFactory.1;Geometry=WKB" pAdoConn.Open '++ Construct a select statement sSQLstr = "Select * from states" '++ Open the recordset pAdoRS.Open sSQLstr, pAdoConn, adOpenForwardOnly, adLockReadOnly '++ Populate the flexgrid control Set MSHFlexGrid1.Recordset = pAdoRS - Save the project by choosing File | Save Project.
- Build the project by choosing File | Make OLEDBTest.exe.
- Run OLEDBTest.exe.
文章来自: 
引用通告: 
Tags: 评论: 0 | 引用: 0 | 查看次数: -
发表评论
