Microsoft SQL Server
- 4 minutes to read
The Dashboard Designer allows you to connect to different types of SQL databases in the Data Source Wizard. You can also use data access API to connect to the database and select data in code.
This tutorial describes how to establish a connection to a database deployed on the Microsoft SQL Server and select data.
Important
The appropriate data provider must be installed on the client machine. For information on data providers, refer to the Data Sources article.
Create a Data Source in the Data Source Wizard
To connect to the MS SQL database in the Dashboard Designer, do the following:
Click the New Data Source button in the Data Source ribbon tab.
On the first page of the invoked Data Source Wizard dialog, select Microsoft SQL Server and click Next.
On the next page, specify connection parameters.
Server name
Specify the name of the MS SQL server to which the connection should be established.
Authentication type
Specify the authentication mode of the MS SQL Server. You can choose whether to use Windows authentication or Server authentication.
Encrypt
Specify whether all communication between the server and the client is encrypted.
Trust Server Certificate
Specify whether the channel will be encrypted when certificate chain bypass is performed to validate trust.
User name
Specify the user name used to authenticate to the MS SQL server.
Password
Specify the password used to authenticate to the MS SQL server.
Database
Select the database that contains data.
After you have specified connection parameters, click Next and specify how to select data from the database.
Select the Query option and click the Run Query Builder… button to invoke the Query Builder. The Query Builder allows you to choose tables/columns visually and passes the resulting SQL query to the SQL String editor. Here you can customize this query or click Finish to create the data source.
Important
Note that the SQL String editor does not allow you to specify SQL queries manually by default. To enable this capability, set the SqlWizardSettings.EnableCustomSql property exposed by the DashboardDataSourceWizardSettings class to true.
- Select the Stored Procedure option to select one of the stored procedures from the database.
Click Next.
On the final page, you can optionally add query parameters and preview data.
Click Finish to create the data source.
Create a Data Source in Code
To create a data source that uses a connection to the MS SQL database, create an instance of the DashboardSqlDataSource class and follow the steps below:
Specify connection parameters for the MS SQL database. Create the MsSqlConnectionParameters class object and specify the following properties:
- The SqlServerConnectionParametersBase.ServerName property specifies the name of the MS SQL server to which the connection should be established.
- The SqlServerConnectionParametersBase.DatabaseName property specifies the name of the database that contains data.
- The MsSqlConnectionParameters.AuthorizationType property specifies the authentication mode of the Microsoft SQL Server. If this property is set to MsSqlAuthorizationType.SqlServer, use the SqlServerConnectionParametersBase.UserName and SqlServerConnectionParametersBase.Password properties to specify user credentials.
Assign the resulting MsSqlConnectionParameters object to the SqlDataSource.ConnectionParameters property.
Note
Alternatively, you can add a connection string with parameters to the application configuration file. Then, assign the connection string name to the SqlDataSource.ConnectionName property.
Create one of the following objects to specify the query:
- Create a SelectQuery object to specify a set of tables/columns that form a SELECT statement when you execute a query.
- Create a CustomSqlQuery object to specify an SQL query. Use the CustomSqlQuery.Sql property to specify a custom query string.
- Create a StoredProcQuery object to execute a stored procedure call and supply the dashboard with data.
Add the created query to the SqlDataSource.Queries collection exposed by the DashboardSqlDataSource object.
- Add the created DashboardSqlDataSource object to the Dashboard.DataSources collection.
The following code snippet shows how to supply the dashboard with data from the Northwind database deployed on the MS SQL Server:
using DevExpress.DashboardCommon;
using DevExpress.DataAccess.ConnectionParameters;
using DevExpress.DataAccess.Sql;
// ...
MsSqlConnectionParameters msSqlParams = new MsSqlConnectionParameters();
msSqlParams.AuthorizationType = MsSqlAuthorizationType.Windows;
msSqlParams.ServerName = "localhost";
msSqlParams.DatabaseName = "Northwind";
DashboardSqlDataSource sqlDataSource = new DashboardSqlDataSource("Data Source 1", msSqlParams);
SelectQuery selectQuery = SelectQueryFluentBuilder
.AddTable("SalesPerson")
.SelectColumns("CategoryName", "Extended Price")
.Build("Query 1");
sqlDataSource.Queries.Add(selectQuery);
sqlDataSource.Fill();
dashboard.DataSources.Add(sqlDataSource);