SqlDataSource Class
A component that allows you to bind data-aware controls to a SQL database (in read-only mode) with the specified data connection options.
Namespace: DevExpress.DataAccess.Sql
Assembly: DevExpress.DataAccess.v21.1.dll
NuGet Packages: DevExpress.DataAccess, DevExpress.Win.Design
Declaration
[ToolboxBitmap(typeof(ResFinder), "Bitmaps256.SqlDataSource.bmp")]
[ToolboxSvgImage("DevExpress.DataAccess.Images.SqlDataSource.svg,DevExpress.DataAccess.v21.1, Version=21.1.99.0, Culture=neutral, PublicKeyToken=c38a27d2243c2672")]
[XRDesigner("DevExpress.DataAccess.UI.Design.XRSqlDataSourceDesigner,DevExpress.DataAccess.v21.1.UI, Version=21.1.99.0, Culture=neutral, PublicKeyToken=c38a27d2243c2672", typeof(IDesigner))]
public class SqlDataSource :
DataComponentBase,
IListSource,
IDataConnectionParametersService,
IListAdapterAsync2,
IListAdapterAsync,
IListAdapter,
IListAdapter2,
ISupportFillAsyncRemarks
Design Time
Add the Component Automatically
The SqlDataSource component is added to your project automatically when you run the wizard and connect the following DevExpress components to a SQL database:
WinForms: Add the Component Manually
You can add the SQLDataSource component to your WinForms application as follows:
Drag the SqlDataSource component from the DX.21.1: Data & Analytics Toolbox section and drop it on the form.
The SqlDataSource Wizard is invoked. The Wizard pages are composed of the Data Source Wizard pages. The Wizard guides you through the steps required to configure a new data source.
WinForms: Configure the Data Source
To modify the data source, expand the SqlDataSource‘s smart tag.
The following commands are available:
Configure Connection
Invokes the Connection Editor:
Manage Queries
Invokes the Manage Queries dialog:
Manage Relations
The command is available when there are two or more queries in the collection. Invokes the Master-Detail Relation Editor:
Rebuild Result Schema
Calls the SqlDataSource.RebuildResultSchema method, which updates the result schema on the client side after the query is executed. This command does not affect the actual database schema on the server.
When you use a 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. For the code example, review the following help topic: Bind a Report to a Microsoft SQL Server Database at Runtime.
Request Database Schema
Calls the SqlDataConnection.GetDBSchema method, which returns the database schema from the database and updates the schema on the client.
Note
The GridControl and some other controls do not retrieve data automatically when bound to the SqlDataSource component. Call the SqlDataSource.Fill method to populate the controls with data. Review the following help topic for more information: Binding to SQL Data.
Runtime Specifics
You can use the SqlDataSourceUIHelper class methods to invoke dialogs that help users to configure a data connection. Ensure that your application references the DevExpress.DataAccess.v21.1.UI.dll assembly to access this class.
If you bind an XtraReport to a data source that does not support schema-only mode, call the data source’s Fill method before you call the IReportPrintTool.ShowPreview method.
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 |
Connection Strings
When you construct a custom connection string with the CustomStringConnectionParameters instance, use the XpoProvider parameter to identify the provider type. Refer to the following table for connection string examples:
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 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 |
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.