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

Declaration

[XRDesigner("DevExpress.DataAccess.UI.Design.XRSqlDataSourceDesigner,DevExpress.DataAccess.v19.2.UI, Version=19.2.99.0, Culture=neutral, PublicKeyToken=c38a27d2243c2672", typeof(IDesigner))]
[ToolboxBitmap(typeof(ResFinder), "Bitmaps256.SqlDataSource.bmp")]
[ToolboxSvgImage("DevExpress.DataAccess.Images.SqlDataSource.svg,DevExpress.DataAccess.v19.2, Version=19.2.99.0, Culture=neutral, PublicKeyToken=c38a27d2243c2672")]
public class SqlDataSource :
    DataComponentBase,
    IListSource,
    IDataConnectionParametersService,
    IListAdapterAsync2,
    IListAdapterAsync,
    IListAdapter,
    IListAdapter2,
    ISupportFillAsync
<XRDesigner("DevExpress.DataAccess.UI.Design.XRSqlDataSourceDesigner,DevExpress.DataAccess.v19.2.UI, Version=19.2.99.0, Culture=neutral, PublicKeyToken=c38a27d2243c2672", GetType(IDesigner))>
<ToolboxBitmap(GetType(ResFinder), "Bitmaps256.SqlDataSource.bmp")>
<ToolboxSvgImage("DevExpress.DataAccess.Images.SqlDataSource.svg,DevExpress.DataAccess.v19.2, Version=19.2.99.0, Culture=neutral, PublicKeyToken=c38a27d2243c2672")>
Public Class SqlDataSource
    Inherits DataComponentBase
    Implements IListSource,
               IDataConnectionParametersService,
               IListAdapterAsync2,
               IListAdapterAsync,
               IListAdapter,
               IListAdapter2,
               ISupportFillAsync

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:

  1. Locate the SqlDataSource component on the toolbox (the 'Data and Analytics' tab) and drop it onto the form.

    data-access-winforms-sql-data-source-component-grid-control

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

    sql-data-source-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.

    sql-data-source-manage-relations-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.v19.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.

Implements

Inheritance

Object
MarshalByRefObject
Component
DevExpress.DataAccess.DataComponentBase
SqlDataSource

Extension Methods

DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.ManageQueries()
DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.ManageQueries(UserLookAndFeel)
DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.ManageQueries(UserLookAndFeel, IWin32Window)
DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.ManageQueries(UserLookAndFeel, IWin32Window, DevExpress.DataAccess.Sql.IDBSchemaProvider)
DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.ManageQueries(UserLookAndFeel, IWin32Window, DevExpress.DataAccess.Sql.IDBSchemaProvider, IParameterService)
DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.ManageQueries(UserLookAndFeel, IWin32Window, DevExpress.DataAccess.Sql.IDBSchemaProvider, IParameterService, Action<IWizardCustomization<SqlDataSourceModel>>)
DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.ManageQueries(UserLookAndFeel, IWin32Window, DevExpress.DataAccess.Sql.IDBSchemaProvider, IParameterService, Action<IWizardCustomization<SqlDataSourceModel>>, ICustomQueryValidator)
DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.ManageQueries(EditQueryContext)
DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.ManageQueries<TModel>(EditQueryContext, Action<IWizardCustomization<TModel>>)
DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.ManageRelations()
DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.ManageRelations(UserLookAndFeel)
DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.ManageRelations(UserLookAndFeel, IWin32Window)
DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.ManageRelations(UserLookAndFeel, IWin32Window, DevExpress.DataAccess.Sql.IDBSchemaProvider)
DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.ManageRelations(ManageRelationsContext)
DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.ConfigureConnectionParameters()
DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.ConfigureConnectionParameters(UserLookAndFeel)
DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.ConfigureConnectionParameters(UserLookAndFeel, IWin32Window)
DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.ConfigureConnectionParameters(UserLookAndFeel, IWin32Window, IConnectionStorageService)
DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.ConfigureConnectionParameters(IWizardRunnerContext, IConnectionStorageService)
DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.ConfigureConnectionParameters<TModel>(IWizardRunnerContext, IConnectionStorageService, Action<IWizardCustomization<TModel>>)
DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.ConfigureConnectionParameters(ConfigureConnectionContext)
DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.ConfigureConnectionParameters<TModel>(ConfigureConnectionContext, Action<IWizardCustomization<TModel>>)
DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.ConfigureConnection()
DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.ConfigureConnection(UserLookAndFeel)
DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.ConfigureConnection(UserLookAndFeel, IWin32Window)
DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.ConfigureConnection(UserLookAndFeel, IWin32Window, IConnectionStorageService)
DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.ConfigureConnection(IWizardRunnerContext, IConnectionStorageService)
DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.ConfigureConnection<TModel>(IWizardRunnerContext, IConnectionStorageService, Action<IWizardCustomization<TModel>>)
DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.ConfigureConnection(ConfigureConnectionContext)
DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.ConfigureConnection<TModel>(ConfigureConnectionContext, Action<IWizardCustomization<TModel>>)
DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.AddQueryWithQueryBuilder()
DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.AddQueryWithQueryBuilder(QueryBuilderEditQueryContext)
DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.AddQueryWithQueryBuilder(QueryBuilderEditQueryContext, QueryBuilderRunnerDelegate)
DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.AddQuery()
DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.AddQuery(IRepositoryItemsProvider)
DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.AddQuery(UserLookAndFeel)
DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.AddQuery(UserLookAndFeel, IRepositoryItemsProvider)
DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.AddQuery(UserLookAndFeel, IWin32Window)
DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.AddQuery(UserLookAndFeel, IWin32Window, IRepositoryItemsProvider)
DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.AddQuery(UserLookAndFeel, IWin32Window, DevExpress.DataAccess.Sql.IDBSchemaProvider)
DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.AddQuery(UserLookAndFeel, IWin32Window, DevExpress.DataAccess.Sql.IDBSchemaProvider, IRepositoryItemsProvider)
DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.AddQuery(UserLookAndFeel, IWin32Window, DevExpress.DataAccess.Sql.IDBSchemaProvider, IParameterService)
DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.AddQuery<TModel>(UserLookAndFeel, IWin32Window, DevExpress.DataAccess.Sql.IDBSchemaProvider, IParameterService, Action<IWizardCustomization<TModel>>)
DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.AddQuery<TModel>(UserLookAndFeel, IWin32Window, DevExpress.DataAccess.Sql.IDBSchemaProvider, IParameterService, Action<IWizardCustomization<TModel>>, IServiceProvider)
DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.AddQuery(UserLookAndFeel, IWin32Window, DevExpress.DataAccess.Sql.IDBSchemaProvider, IParameterService, IRepositoryItemsProvider)
DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.AddQuery<TModel>(UserLookAndFeel, IWin32Window, DevExpress.DataAccess.Sql.IDBSchemaProvider, IParameterService, IRepositoryItemsProvider, Action<IWizardCustomization<TModel>>)
DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.AddQuery<TModel>(UserLookAndFeel, IWin32Window, DevExpress.DataAccess.Sql.IDBSchemaProvider, IParameterService, IRepositoryItemsProvider, Action<IWizardCustomization<TModel>>, IServiceProvider)
DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.AddQuery<TModel>(UserLookAndFeel, IWin32Window, DevExpress.DataAccess.Sql.IDBSchemaProvider, IParameterService, IRepositoryItemsProvider, Action<IWizardCustomization<TModel>>, IServiceProvider, ICustomQueryValidator)
DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.AddQuery(EditQueryContext)
DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.AddQuery<TModel>(EditQueryContext, Action<IWizardCustomization<TModel>>)
DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.RebuildResultSchema(UserLookAndFeel, IWin32Window, IParameterService, Boolean)
DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.RebuildResultSchema()
DevExpress.DataAccess.UI.Sql.SqlDataSourceUIHelper.RebuildResultSchema(RebuildResultSchemaContext)
See Also