The following tutorial demonstrates how to create a .NET application that retrieves information from an SAP HANA database. ADO.NET is a component of the .NET Framework that links a database connection to a set of predefined classes and is primarily used to access and manipulate data inside a relational database. This tutorial implements a sample application to show the different techniques and options available to a HANA user when writing a solution in C# and .NET.
The accompanying code for this project is available for download here.
To create the sample application in this tutorial, you must have an available HANA database installed. You must also have the appropriate credentials to access the data you need in that system. If you do not have a database on premise, you can sign up for a free developer account using HANA Cloud Platform.
The mock data used for this demo was taken from the SAP HANA Interactive Education (SHINE) schema, which you can find and import via the following documentation. This schema is useful for testing HANA application logic without the need to worry about migrating existing data.
The tools used for this sample application were:
Microsoft Visual Studio 2012
HANA SPS09 Rev 91
.NET Framework 4.5
In order to create an application using SAP HANA with ADO.NET, you must add the HANA Driver to your project. This driver will be installed by the HANA Client if the machine has Visual Studio installed beforehand.
To add it to your project, right click on your project’s References folder in the Solution Explorer and select "Add Reference…" Search for the "Sap.Data.Hana for .NET 4.5" reference in the .NET tab and click OK. If you are using a different version of .NET, please select that one from the list instead.
When using any of the ADO.NET classes you need to also include the "Sap.Data.Hana" library in the appropriate namespaces.
Once you have your environment configured, you can use ADO.NET. For those familiar with ADO.NET, you can start working right away by simply replacing the prefix of the classes you use with "Hana".
For example, the following equivalent classes are available:
For those unfamiliar with ADO.NET, I put together a simple demo application that uses some of the features available. Although there are many different ways to accomplish similar results, I chose to write as much C# code as possible, for the sake of learning something new.
This sample application takes advantage of the product data and employee data within the SHINE database. There are two different tabs of information, displayed in different ways. The first tab will display the different products available in a grid view, with additional details populated when a row is clicked. The second tab will have a simple TreeView of the employee data sorted according to gender.
To begin creating the application, create a new Windows Forms Application in Visual Studio by going to File > New > Project and selecting ‘Windows Forms Application’. At this point you can follow the steps above to add the ADO.NET driver into your project.
Double click on the form to create a loading event listener. To hide your credentials from users of the application, create a connection string in your project’s App.config file.
Use port 3##15, where the ## refers to your HANA instance number. For example, a 00 instance would refer to port 30015.
Next, add the following code to connect to your HANA database in this event listener.
Declare the HanaConnection outside of your Form_Load handler so that you can access it later. We are going to leave the connection open so that we can access the product details without having to reconnect every time. For applications with multiple users, the connection should be closed whenever possible to limit the number of connections being made to the database at a given time.
In this example, I’ve also saved constants with the schema name and important SHINE table names along with the HanaConnection. This is a good practice for larger applications where the schema may change, and can also help shorten the SQL query code significantly.
In the Designer view, drag and drop a TabControl into your form. You can also change the text on your tab to reflect the data that will be inside, in this case ‘Employees’ and ‘Products’. Next, add a DataGridView from the Data tab of your toolbox into your Product tab. Optionally change the name of this DataGridView to ‘productGridView’.
In the same Form_Load event handler, create a new HanaAdapter with the query you would like to use to populate the DataGridView.
Create a new DataTable and use your adapter to fill the table.
Finally, set the DataSource of your DataGridView to be the DataTable linked to your query.
Once you have done this, the results should look similar to the following.