SqlDataSource Class
The data source that allows you to bind Windows Forms data-aware controls to a database (in read-only mode) using specified data connection options.
Namespace: DevExpress.DataAccess.Sql
Assembly: DevExpress.DataAccess.v18.1.dll
Declaration
[XRDesigner("DevExpress.DataAccess.UI.Design.XRSqlDataSourceDesigner,DevExpress.DataAccess.v18.1.UI", typeof(IDesigner))]
[ToolboxBitmap(typeof(ResFinder), "Bitmaps256.SqlDataSource.bmp")]
[ToolboxTabName("DX.18.1: Data & Analytics")]
public class SqlDataSource :
DataComponentBase,
IListSource,
IDataConnectionParametersService,
IListAdapterAsync,
IListAdapter
Related API Members
The following members accept/return SqlDataSource objects:
Remarks
Design-Time
The SQLDataSource component is automatically added when you run a Data Source Wizard to provide database access to DevExpress WinForms data-aware controls, reports and dashboards.
Do the following to add the SQLDataSource component manually:
Locate the SqlDataSource component on the toolbox (the ‘Data and Analytics’ tab) and drop it onto the form.
- After the component is dropped, the Data Source Wizard appears. Go through the Wizard pages to set up the connection.
If you need to modify the data source later, you can use the SqlDataSource‘s smart-tag.
The following commands are available:
Configure Connection…
Corresponds to the SqlDataSource.ConfigureDataConnection method that invokes the Connection Editor.
Manage Queries…
Corresponds to the SqlDataSource.Queries property that is used to access the collection of queries. The following image illustrates the Manage Queries dialog.
Manage Relations…
Corresponds to the SqlDataSource.Relations property that is used to access the collection of data relations.
This command is available when there are two or more queries in the collection.
The following image illustrates the Master-Detail Relation Editor.
Rebuild Result Schema
Corresponds to the SqlDataSource.RebuildResultSchema method that updates the result schema available on the client after query execution (it can be accessed using the SqlDataSource.Result property). This command does not affect the actual database schema on the server.
When using a SqlDataSource to bind an XtraReport to data, call the RebuildResultSchema method to make the data source structure displayed in the Field List of an End-User Report Designer. For a code sample, see Bind a Report to a Microsoft SQL Server Database (Runtime Sample).
Request Database Schema
Corresponds to the SqlDataConnection.GetDBSchema method that returns the schema of the database from the server and updates the result schema on the client.
Note
Certain controls (e.g., the GridControl) do not automatically retrieve data when bound to the SqlDataSource component, and require calling the SqlDataSource.Fill method manually. See the Binding to SQL Data topic for an example.
When using a SqlDataSource to bind an XtraReport to data, the Fill method is called internally when a report document is generated by using the XtraReport.CreateDocument or IReportPrintTool.ShowPreview method.
Runtime Specifics
To configure a data connection in code, you can use the methods of the SqlDataSourceUIHelper class (requires referencing the DevExpress.DataAccess.v18.1.UI.dll assembly to an application).
Supported Data Providers
The SqlDataSource component supports the following data providers:
Relational Database System | Supported Versions | .NET Framework Data Provider | .NET Standard 2.0 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 Sybase Advantage | Advantage Database Server 9.1 or higher |
| |
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 |
When you construct a connection string manually (see CustomStringConnectionParameters), use the XpoProvider parameter to identify the provider type. See connection string examples in the table below.
Relational Database System | Connection String Sample See Note |
---|---|
Microsoft SQL Server * | XpoProvider=MSSqlServer;Data Source=(local);User ID=username;Password=password;Initial Catalog=database;Persist Security Info=true *The MS SQL Server data provider is automatically specified if you set the “Initial Catalog” parameter and do not use the word “Provider” (for instance, the “XpoProvider” parameter) in the connection string. |
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 |
Note
The connection strings demonstrated above are examples. You can specify alternative variants.
Related GitHub Examples
The following code snippets (auto-collected from DevExpress Examples) contain references to the SqlDataSource class.
Note
The algorithm used to collect these code examples remains a work in progress. Accordingly, the links and snippets below may produce inaccurate results. If you encounter an issue with code examples below, please use the feedback form on this page to report the issue.