To access Synergy data from non-Synergy programs in a .NET environment, use the .NET Framework Data Provider for ODBC, which is included with .NET Framework. Note that we recommend using ISAM files with the .NET Framework Data Provider for ODBC. ASCII data files are not supported, and inserts, updates, and for relative files joins are limited because the only key is the record number. With ISAM files, the xfODBC driver is able to create additional keys for optimization.
|
For inserting, updating, and deleting records, use xfServerPlus methods rather than the .NET Framework Data Provider for ODBC. The data provider uses ADO.NET which is not efficient for these operations, and in any case, ODBC access can lead to record locking issues. |
To use the .NET Framework Data Provider for ODBC, do the following:
| 1. | Prepare your Synergy database for ODBC access—i.e., generate a system catalog, create a connect file, set up a DSN, and so forth. See The steps to ODBC access. |
| 2. | Supply connection information in your application; see Connection strings. You can also create a data connection in Visual Studio; see Configuring a data connection below. |
For more information on using the .NET Framework Data Provider for ODBC, see Microsoft’s documentation for ADO.NET and Visual Studio and Third-party software requirements. For examples, see xfODBC_DataReader_v3.zip and xfODBC_v2.zip, available from Synergy CodeExchange in the Resource Center on the Synergex web site.
Configuring a data connection
Once you have prepared Synergy data for ODBC access, you can set up a Visual Studio data connection to your Synergy database. A data connection enables you to access your Synergy database from Visual Studio.
The procedure for a Synergy database is the same as for any other ODBC data source (so see Microsoft’s Visual Studio documentation for details), but the following are the basic steps:
| 1. | In Visual Studio, open Server Explorer (View > Server Explorer) and select Tools > Connect to Database (or right-click the Data Connections node and select Add Connection from the context menu). Data connections are independent of solutions and projects, so there’s no need to open a solution or project. |
| 2. | In the Choose Data Source window, set the data source to “Microsoft ODBC Data Source,” which sets the data provider to .NET Framework Data Provider for ODBC. Then click Continue. |
| 3. | In the “Use user or system data source name” field in the Add Connection window, select the DSN for the Synergy database and enter a username and password if the DSN doesn’t specify these. (If the DSN does specify a username and password, user and password settings in this window will override the DSN settings.) |
| 4. | Click the Test Connection button at the bottom of the Add Connection window. If you get an error (e.g., “Authorization failure”), check the information on the Add Connection window. If it appears to be correct, see Troubleshooting data access. |
| 5. | If you are able to connect with the Test Connection button, click OK to close the “Test connection succeeded” message, and then click OK in the Add Connection window. A node for the data connection is added to the Data Connections branch of Server Explorer. |
Once you have set up a Visual Studio data connection to a Synergy database, you can use the Visual Studio Query Designer to directly query the database (see ODBC access examples), or you can use .NET Framework data provider classes (in the System.Data.Odbc namespace) to access data. For example, you can create a DataSet object (you can use the Visual Studio DataSet Designer for this), and you can use a DataReader. See Microsoft’s Visual Studio documentation for information on these topics, and see Appendix B: SQL Support for information on xfODBC’s support for SQL. Ultimately, all ADO.NET access to Synergy data takes the form of SQL statements querying the database through xfODBC, so SQL support and limitations for xfODBC apply to all the methods listed above.
Connection strings
Although you can establish a connection to a Synergy database by adding a data source, this is not recommended. For security reasons it is better to store connection string information in a data file.
The connection string must conform to the connection string syntax for the .NET Framework Data Provider for ODBC. If you use Driver=, it must be set to “xfODBC” (i.e., Driver={xfODBC}). See Microsoft’s documentation for this data provider for information on the syntax. For information on adding connection strings to application configuration files, see Microsoft’s ADO.NET documentation.
You must specify either a connect file or a DSN that specifies a connect file for your Synergy database. However, if you create a connection string that doesn’t specify a DSN (by using DBQ= to specify a connect file), you cannot change the settings listed in step 5 of Adding a user or system DSN (settings such as the maximum number of columns that can be returned for a query). With a DSN-less connection, these settings cannot be changed from their defaults.
Time columns and ADO.NET
ADO.NET retrieves SQL_TIME columns (SQL_TIME is the default for time columns) as System.TimeSpan, which is a .NET data type that represents a time interval rather than a specific time. Generally, this means that applications accessing the data need to be written to calculate the time from TimeSpan values. However, xfODBC includes the VORTEX_ODBC_TIME environment variable, which can make these calculations unnecessary by instructing the xfODBC driver to describe SQL_TIME columns as SQL_TIMESTAMP.
