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.2.dll

Declaration

C#
VB.NET

[XRDesigner("DevExpress.DataAccess.UI.Design.XRSqlDataSourceDesigner,DevExpress.DataAccess.v18.2.UI", typeof(IDesigner))]
[ToolboxBitmap(typeof(ResFinder), "Bitmaps256.SqlDataSource.bmp")]
[ToolboxSvgImage("DevExpress.DataAccess.Images.SqlDataSource.svg,DevExpress.DataAccess.v18.2, Version=18.2.99.0, Culture=neutral, PublicKeyToken=c38a27d2243c2672")]
[ToolboxTabName("DX.18.2: Data & Analytics")]
public class SqlDataSource :
    DataComponentBase,
    IListSource,
    IDataConnectionParametersService,
    IListAdapterAsync,
    IListAdapter

<XRDesigner("DevExpress.DataAccess.UI.Design.XRSqlDataSourceDesigner,DevExpress.DataAccess.v18.2.UI", GetType(IDesigner))>
<ToolboxBitmap(GetType(ResFinder), "Bitmaps256.SqlDataSource.bmp")>
<ToolboxSvgImage("DevExpress.DataAccess.Images.SqlDataSource.svg,DevExpress.DataAccess.v18.2, Version=18.2.99.0, Culture=neutral, PublicKeyToken=c38a27d2243c2672")>
<ToolboxTabName("DX.18.2: Data & Analytics")>
Public Class SqlDataSource
    Inherits DataComponentBase
    Implements IListSource,
               IDataConnectionParametersService,
               IListAdapterAsync,
               IListAdapter

The following members 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.

data-access-winforms-sql-data-source-component-smart-tag-commands

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.2.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.SqlClient.dll System.Data.dll (Included in .NET Framework)	System.Data.SqlClient.dll
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	Oracle.ManagedDataAccess.dll System.Data.OracleClient.dll Oracle.DataAccess.dll	Oracle.ManagedDataAccess.Core.dll
Amazon Redshift	n/a	Npgsql.dll	Npgsql.dll
Google BigQuery	Only legacy SQL functions and operations are supported	DevExpress.DataAccess.BigQuery.dll	DevExpress.DataAccess.BigQuery.dll
Teradata	13.0 or higher	Teradata.Client.Provider.dll	Teradata.Client.Provider.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	Sybase.AdoNet4.AseClient.dll
SAP SQL Anywhere	11 or higher	Sap.Data.SQLAnywhere.dll
IBM DB2	9.5 or higher	IBM.Data.DB2.dll	IBM.Data.DB2.Core.dll
Firebird	1.5 or higher, Dialect 3	FirebirdSql.Data.FirebirdClient.dll FirebirdSql.Data.Firebird.dll	FirebirdSql.Data.FirebirdClient.dll
MySQL	4.1 or higher	MySql.Data.dll	MySql.Data.dll
Pervasive PSQL	9.x or higher	Pervasive.Data.SqlClient.dll
PostgreSQL	7.x or higher	Npgsql.dll	Npgsql.dll
VistaDB	4, 5	VistaDB.5.NET40.dll
SQLite	3.x	System.Data.SQLite.dll Microsoft.Data.SQLite.dll	System.Data.SQLite.Core.dll Microsoft.Data.Sqlite.Core.dll
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.

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.

C#
VB.NET

Reporting_how-to-use-the-web-document-viewer-in-javascript-with-reactjs-t561300/CS/ServerSide/XtraReport2.cs#L9

public class XtraReport2: XtraReport {
    private DevExpress.DataAccess.Sql.SqlDataSource sqlDataSource1;
    private System.ComponentModel.IContainer components;

Reporting_how-to-implement-a-custom-authorization-service-t488888/CS/AuthorizationService/Reports/CategoriesReport.cs#L23

private XRLine xrLine1;
private DevExpress.DataAccess.Sql.SqlDataSource sqlDataSource1;
private PageFooterBand pageFooterBand1;

Reporting_how-to-use-the-web-report-designer-in-javascript-with-reactjs-t561295/CS/ServerSide/Controllers/ReportDesignerController.cs#L38

var dataSources = new Dictionary<string, object>();
SqlDataSource ds = new SqlDataSource("Northwind_Connection");
var query = SelectQueryFluentBuilder.AddTable("Products").SelectAllColumns().Build("Products");

Reporting_how-to-use-the-web-document-viewer-in-javascript-with-reactjs-t561300/VB/ServerSide/XtraReport2.vb#L11


Private sqlDataSource1 As DevExpress.DataAccess.Sql.SqlDataSource
Private components As System.ComponentModel.IContainer

Reporting_how-to-implement-a-custom-authorization-service-t488888/VB/AuthorizationService/Reports/CategoriesReport.vb#L25

Private xrLine1 As XRLine
Private sqlDataSource1 As DevExpress.DataAccess.Sql.SqlDataSource
Private pageFooterBand1 As PageFooterBand

Reporting_how-to-use-the-web-report-designer-in-javascript-with-reactjs-t561295/VB/ServerSide/Controllers/ReportDesignerController.vb#L32

Dim dataSources = New Dictionary(Of String, Object)()
Dim ds As New SqlDataSource("Northwind_Connection")
Dim query = SelectQueryFluentBuilder.AddTable("Products").SelectAllColumns().Build("Products")