dashboard-404143-winforms-dashboard-winforms-designer-create-dashboards-in-the-winforms-designer-providing-data-sql-data-source-connect-to-sql-databases-sap-hana.md
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 article describes how to establish a connection to the SAP HANA database and retrieve data.
Install the SAP HANA Client 2.0 on the client machine.
Add the Sap.Data.Hana.Core.v2.1.dll project reference.
Follow the steps below to connect to the SAP HANA database in the Dashboard Designer.
Click New Data Source in the Data Source ribbon tab.
On the first page of the invoked Data Source Wizard dialog, specify whether you want to use an existing data connection or create a new data connection.
On the next page, select SAP HANA and click Next.
On the next page, specify connection parameters.
Server name Specifies the name of the SAP HANA database server to which the connection should be established.Port Specifies the port used to connect to the SAP HANA database server.User nameSpecifies the user name used to authenticate to the SAP HANA database server.PasswordSpecifies the password used to authenticate to the SAP HANA database server.DatabaseSpecifies the name of the tenant database that contains data. If you do not specify this parameter, the system database is used instead.
You can select data from the database in the following ways:
Select Query and click Run Query Builder… to invoke the Query Builder.
The Query Builder allows you to choose the tables/columns and passes the resulting SQL query to the SQL String editor.
The text of the generated query is returned to the Data Source wizard.
Click Finish to create the data source.
Set the SqlWizardSettings.EnableCustomSql property to true to enable the capability to specify custom SQL queries in the SQL String editor or Query Builder.
Select Stored Procedure to choose one of the stored procedures from the database. Click Next to specify the parameter settings.
On the last page, you can optionally add query parameters and preview data.
Click Finish to create the data source.
To create a data source that uses a connection to the SAP HANA database, create a DashboardSqlDataSource object and add it to the Dashboard.DataSources collection.
Follow the steps below to configure the created DashboardSqlDataSource object and specify the connection to the SAP HANA database:
Create a HanaConnectionParameters object and specify the following properties:
ServerNameGets or sets the name of the SAP HANA database server to which the connection should be established.PortNumberSpecifies the port number to connect to SAP HANA databases.UserNameGets or sets the user name used to authenticate to the SAP HANA database server.PasswordGets or sets the password used to authenticate to the SAP HANA database server.DatabaseNameGets or sets the name of the tenant database that contains the required data. If you do not specify DatabaseName, the system database is used instead.
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.
Assign the HanaConnectionParameters object to the SqlDataSource.ConnectionParameters property.
Create one of the following query objects to retrieve data:
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 Queries collection.
The following code snippet shows how to supply the dashboard with data from the SAP HANA database:
using DevExpress.DataAccess.Sql;
using DevExpress.DashboardCommon;
using DevExpress.DataAccess.ConnectionParameters;
public partial class Form1 : Form {
public Form1() {
InitializeComponent();
HanaConnectionParameters hanaParams = new HanaConnectionParameters();
hanaParams.ServerName = "hxehost";
hanaParams.PortNumber = 39015;
hanaParams.UserName = "SYSTEM";
hanaParams.Password = "17Me7P7n4";
DashboardSqlDataSource sqlDataSource = new DashboardSqlDataSource("Data Source 1", hanaParams);
SelectQuery selectQuery = SelectQueryFluentBuilder
.AddTable("Northwind.Order Details")
.SelectColumns("UnitPrice", "OrderID", "Quantity")
.Build("Query 1");
sqlDataSource.Queries.Add(selectQuery);
sqlDataSource.Fill();
dashboardDesigner1.Dashboard.DataSources.Add(sqlDataSource);
}
}
Imports DevExpress.DataAccess.Sql
Imports DevExpress.DashboardCommon
Imports DevExpress.DataAccess.ConnectionParameters
Partial Public Class Form1
Inherits Form
Public Sub New()
InitializeComponent()
Dim hanaParams As New HanaConnectionParameters()
hanaParams.ServerName = "hxehost"
hanaParams.PortNumber = 39015
hanaParams.UserName = "SYSTEM"
hanaParams.Password = "17Me7P7n4"
Dim sqlDataSource As New DashboardSqlDataSource("Data Source 1", hanaParams)
Dim selectQuery As SelectQuery = SelectQueryFluentBuilder.AddTable("Northwind.Order Details").SelectColumns("UnitPrice", "OrderID", "Quantity").Build("Query 1")
sqlDataSource.Queries.Add(selectQuery)
sqlDataSource.Fill()
dashboardDesigner1.Dashboard.DataSources.Add(sqlDataSource)
End Sub
End Class