Data Source Wizard in SpreadsheetControl
- 5 minutes to read
The Data Source Wizard allows users to create and configure a new data source. If multiple data sources are available, users can select a source that is required for a particular task. After the wizard is complete, data source configuration is stored in the XLS/XLSX spreadsheet file. Users do not need to reconfigure the data source if they reopen the same file.
To invoke the wizard, click Add Data Source in the External Data Sources group of the Mail Merge tab.
The Data Source Wizard appears. Follow the steps below.
Specify a Data Source Name
On the first page of the invoked Data Source wizard, specify a name for the new data source and click Next.
Select a Data Connection
On this page, select a data source type and click Next.
Set the SpreadsheetDataSourceWizardOptions.UseMergedConnectionTypePage property to true to use the wizard design illustrated above.
The XPO Data Source is hidden from the list of data sources available in the Data Source Wizard. To customize the list of displayed data connection types, use the SpreadsheetDataSourceWizardOptions.DataSourceTypes property.
Available Data Connection Types
Wizard allows you to connect to the following types of data connections:
- SQl Databases
- Entity Framework Data Source
- Object Data Source
- Excel Data Source
- MongoDB Data Source
- JSON Data Source
- XPO Data Source
- Custom Connection String
Supported SQL Data Source Providers
The following table lists all supported data source providers you can select on the Select a Data Connection Type page:
Relational Database System | Supported Versions | .NET Framework Data Provider | .NET Data Provider |
|---|---|---|---|
Microsoft SQL Server | 2005 or higher 2005 Express or higher Azure SQL Database |
System.Data.dll (Included in .NET Framework) | |
Microsoft Access | Access 2000 or higher Access 2007 or higher | System.Data.dll (Included in .NET Framework) |
|
Microsoft SQL Server CE | 3.5, 4.0 | System.Data.SqlServerCe.dll (Included in .NET Framework) |
|
Oracle Database | 9i or higher |
System.Data.OracleClient.dll Oracle.DataAccess.dll | |
Amazon Redshift | n/a | ||
Google BigQuery | Only legacy SQL functions and operations are supported | ||
Teradata | 13.0 or higher | ||
SAP HANA | 2.0 or higher |
Sap.Data.Hana.Core.v2.1.dll | |
SAP Sybase Advantage | Advantage Database Server 9.1 or higher | Advantage.Data.Provider.dll |
|
SAP Sybase ASE | Sybase Adaptive Server 12.0 or higher |
| |
SAP SQL Anywhere | 11 or higher |
| |
IBM DB2 | 9.5 or higher | ||
Firebird | 1.5 or higher, Dialect 3 | FirebirdSql.Data.FirebirdClient.dll FirebirdSql.Data.Firebird.dll | |
MySQL | 4.1 or higher | ||
Pervasive PSQL | 9.x or higher |
| |
PostgreSQL | 7.x or higher | ||
VistaDB | 4, 5 |
| |
SQLite | 3.x | ||
XML file | n/a | Built-in support | Built-in support |
Custom Connection String
The Select a Data Connection Type wizard page allows you to specify a custom connection string as a data connection.
Specify the connection string in the following dialog:
Use the XpoProvider parameter to identify a data source provider. See connection string examples in the table below.
Relational Database System | Connection String Sample |
|---|---|
Microsoft SQL Server[1] | XpoProvider=MSSqlServer;Data Source=(local);User ID=username;Password=password;Initial Catalog=database;Persist Security Info=true |
Microsoft Access | XpoProvider=MSAccess;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;User Id=admin;Password=; XpoProvider=MSAccess;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\mydatabase.accdb;User Id=admin;Password=; |
Microsoft SQL Server CE | XpoProvider=MSSqlServerCE;Data Source=MyDatabase.sdf;Password=MyPassword |
Oracle Database | XpoProvider=Oracle;Data Source=TORCL;User ID=MyUserName;Password=MyPassword XpoProvider=ODP;Data Source=TORCL;User ID=MyUserName;Password=MyPassword XpoProvider=ODPManaged;Data Source=TORCL;User ID=MyUserName;Password=MyPassword |
Amazon Redshift | XpoProvider=Amazon Redshift;Server=127.0.0.1;User ID=MyUserName;Password=MyPassword;Database=MyDatabase;Encoding=UNICODE |
Google BigQuery | XpoProvider=BigQuery;ProjectID=myProject;DataSetId=myDataSet;OAuthClientId=myClientId;OAuthClientSecret=mySecret;OAuthRefreshToken=myRefreshToken XpoProvider=BigQuery;ProjectId=project;DatasetId=dataset;ServiceAccountEmail=example@gmail.com;PrivateKeyFileName=key.p12 |
Teradata | XpoProvider=Teradata;Data Source=myServerAddress;User ID=myUsername;Password=myPassword; |
SAP Sybase Advantage | XpoProvider=Advantage;Data Source=\myserver\myvolume\mypat\mydd.add;ServerType=local;User ID=ASSSYS;TrimTrailingSpaces=true |
SAP Sybase ASE | XpoProvider=Ase;Port=5000;Data Source=MyAseServer;User ID=MyUserName;Password=MyPassword;Initial Catalog=MyDatabase;Persist Security Info=true |
SAP SQL Anywhere | XpoProvider=Asa;eng=server;uid=user;pwd=password;dbn=database;persist security info=true; |
IBM DB2 | XpoProvider=DB2;Server=server:port;Database=database;UID=user;PWD=password; |
Firebird | XpoProvider=Firebird;DataSource=localhost;User=SYSDBA;Password=masterkey;Database=MyDatabase.fdb;ServerType=0;Charset=NONE |
MySQL | XpoProvider=MySql;Server=MyServerAddress;User ID=MyUserName;Password=MyPassword;Database=MyDatabase;Persist Security Info= true;Charset=utf8 |
Pervasive PSQL | XpoProvider=Pervasive;Server=MyServerAddress;UID=MyUserName;PWD=MyPassword;ServerDSN=MyDatabase |
PostgreSQL | XpoProvider=Postgres;Server=127.0.0.1;User ID=MyUserName;Password=MyPassword;Database=MyDatabase;Encoding=UNICODE |
VistaDB | XpoProvider=VistaDB;Data Source=C:\mydatabase.vdb4 XpoProvider=VistaDB5;Data Source=C:\mydatabase.vdb5 |
SQLite | XpoProvider=SQLite;Data Source=filename |
XML file | XpoProvider=InMemoryDataStore;data source=D:\Contacts.xml;read only=True |
Select Predefined Data Connections
Your application may already contain data connections that match the selected connection type. In such cases, the wizard shows a page that allows you to select whether you want to use an existing connection or create a new one.
Configure Connection Properties
On this page, specify connection string parameters and click Next.
Depending on the selected data provider, this page may display additional connection options (such as authentication type and database name).
To proceed to the next wizard page, click Next.
On the next page you can choose whether to include user name and password information within the saved connection string. Choose any option and click Next.
Specify a Query
This page allows you to select a query or a stored procedure.
Click Run Query Builder… to invoke the Query Builder window.
Important
Manual SQL editing is not allowed by default. Custom SQL queries may lead to inadvertent or unauthorized modifications to your data/database structure. Before you decide to allow manual SQL editing, we recommend that you follow best practices and implement appropriate end-user permissions at the database level.
If you do need to enable custom SQL, set the SpreadsheetControl.Options.DataSourceWizard.EnableCustomSql option to true.
Note that only SELECT statements are allowed in the text. To override this restriction, handle the SpreadsheetControl.ValidateCustomSqlQuery event.
In the invoked Query Builder window, double-click the Categories item in the list of available tables on the left to add it to the tables included in the query.
Enable the check box for the Categories table, to include all of its fields in the view.
Click OK to close the Query Builder and return to the Data Source Wizard.
When the query is ready, click Finish to save it.
After the Wizard Is Complete
The Field List Panel displays the structure of the created data source.
To modify the query used in the data source, click Manage Relations -> Manage Queries in the External Data Sources group.
Tip
The Wizard saves the data source configuration to the spreadsheet file. If a user closes and reopens the file, they can start a mail merge operation without having to run the Wizard again.