SqlDataSource Class
The data source that allows you to bind data-aware controls to a database (in read-only mode) using specified data connection options.
Namespace: DevExpress.DataAccess.Sql
Assembly: DevExpress.DataAccess.v23.2.dll
NuGet Packages: DevExpress.DataAccess, DevExpress.Win.PivotGrid, DevExpress.Win.TreeMap
Declaration
[ToolboxBitmap(typeof(SqlDataSource), "SqlDataSource.bmp")]
[ToolboxSvgImage("DevExpress.DataAccess.Images.SqlDataSource.svg,DevExpress.DataAccess.v23.2, Version=23.2.99.0, Culture=neutral, PublicKeyToken=c38a27d2243c2672")]
[XRDesigner("DevExpress.DataAccess.UI.Design.XRSqlDataSourceDesigner,DevExpress.DataAccess.v23.2.UI, Version=23.2.99.0, Culture=neutral, PublicKeyToken=c38a27d2243c2672", typeof(IDesigner))]
public class SqlDataSource :
DataComponentBase,
IListSource,
IDataConnectionParametersService,
IListAdapterAsync2,
IListAdapterAsync,
IListAdapter,
IListAdapter2,
ISupportFillAsync,
IDynamicLookupSettingsDataProviderRemarks
Design Time
The SqlDataSource component is added when you run the Data Source Wizard to connect DevExpress WinForms data-aware controls, reports, or dashboards to a database.
Alternatively, you can add the SQLDataSource component to your application as follows:
Locate the SqlDataSource component in the DX.23.2: Data & Analytics Toolbox section and drop it onto the form.
The Data Source Wizard is invoked. Follow the Wizard pages to set up a connection.
To modify the data source, expand 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, which provides access to the queries collection. The following image illustrates the Manage Queries dialog.
Manage Relations
Corresponds to the SqlDataSource.Relations property, which gives access to the data relations collection.
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, which updates the result schema on the client side after the query is executed. Use the SqlDataSource.Result property to access the schema. This command does not affect the actual database schema on the server.
When you use an SqlDataSource to bind an XtraReport to data, call the RebuildResultSchema method to update the data source structure in the End-User Report Designer’s Field List. To view a code example, see the following help topic: Bind a Report to a Microsoft SQL Server Database (Runtime Sample).
Request Database Schema
Corresponds to the SqlDataConnection.GetDBSchema method, which returns the database schema from the server and updates the schema on the client.
Note
Certain controls (for instance, the GridControl) do not retrieve data when bound to the SqlDataSource component. Call the SqlDataSource.Fill method to populate the data sources of these controls. To view an example, see the following help topic: Binding to SQL Data.
If you bind an XtraReport to an SqlDataSource, the Fill method is called when a report document is generated by the XtraReport.CreateDocument or IReportPrintTool.ShowPreview method.
Runtime Specifics
Use the SqlDataSourceUIHelper class methods to configure a data connection in code. Ensure that your application references the DevExpress.DataAccess.v23.2.UI.dll assembly to access this class.
If you bind an XtraReport to a data source that does not support the schema-only mode, call the data source’s Fill method before you call IReportPrintTool.ShowPreview.
Supported Data Providers
The SqlDataSource component supports the following data providers:
Relational Database System | Supported Versions | .NET Framework Data Provider | .NET 6+ 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.OleDb.dll |
|
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 |
| |
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 5.5, 5.6, 5.7, 8.0, 8.3 (for the MySqlConnector provider) | ||
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[1] |
|---|---|
Microsoft SQL Server[2] | 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 HANA | XpoProvider=Hana;server=myServerAddress:40000;uid=user;pwd=password; |
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 |