Bridging the gap between .NET and HANA

With the advent of HANA SPS 08 circa Q2 2014, SAP finally released the HANA data provider for Microsoft ADO.NET. Admittedly, this is not trousers stirring news this but I am pleased to know that there is now an effective way to allow the .NET framework to efficiently communicate with SAP HANA. In the past, you could still do that but it was through the use of the ODBC driver – there is nothing wrong with it if all you wanted to do was to connect to SAP HANA and perform some simple SQL statements but we all know SAP HANA if more than just an in memory database.

This article will to tease out some of the key concepts and features introduced by SPS 08 on the client interfaces for ADO.NET.  Data access and manipulation will be the theme here today using C# and SAP’s new .NET API – Sap.Data.Hana. If you are wondering what ADO.NET is, it is Microsoft data access technology for the .NET framework using programming languages such as C# or VB.NET.

I beg the question that you ask why is it important for SAP to release this new API when ODBC is sufficient? Given that the standard ODBC connection to SAP HANA is still a valid method to bridge the connection to the database or any database for that matter, we need to understand the core use of an ODBC driver itself. Microsoft’s primary goal when they released the initial ODBC driver was to provide a vendor neutral way of accessing stored data in any
machine. As you might have already guessed, the whole host of ODBC driver that Microsoft currently supports spans vast and wide from databases such as Oracle, Informix, Teradata and all the way to SAP HANA, just to name a few. The result of building an ODBC driver to be as generic as possible can come at a cost. Cost in terms of lost of performance, unleveraged hidden features, missing core functionalities and best practices surrounding a given database.

Although there are no official information from SAP detailing why a developer should use the new API, one can only infer that SAP has put in a lot of hard work to ensure that your .NET code communicates efficiently with their HANA database. Take for example if your team were to deliver a .NET solution specifically on Microsoft SQL Server, they will certainly use the System.Data.SQLClient.SQLConnection object, on Oracle they would most likely end up using the ODP.NET driver and with SAP HANA, it would be this new API. Although the .NET framework is able to connect to SAP HANA through the ODBC driver, SAP reckons that your application will take full advantage of all the underlying advances build specially for the .NET framework to play nicely with SAP HANA. There will be instances when you find yourself with no vendor delivered driver and in this case the ODBC driver is your only option to exchange information with the database on the .NET framework e.g. when developing application on Hadoop Hive.

Getting Started

To start using the new ADO.NET API delivered in SPS 08, you will need to have the latest SAP HANA Client Developer Edition version 1.00.80 installed on your machine or alternatively check the default installation path under C:\Program Files\sap\hdbclient\ado.net. If you have that installed, you should see two folders call v3.5 and v4.5 and clicking through v4.5, you should have the Sap.Data.Hana.v4.5.dll.

View, Insert, Update and Delete

Building an enterprise .NET application on SAP HANA will undoubtedly require you to perform one of these four actions and in this section of the article, the aim is to demonstrate the basic database call to interact and manipulate the data that resides in SAP HANA.

Under the .NET solution explorer, expend the Reference dropdown and ensure that Sap.Data.Hana.v4.5 has been included to your .NET solution and if not you can add a new reference under the Extensions path. Once you have that, standard C #codes ensue for namespace: using Sap.Data.Hana;

Establishing the database connection

The bare minimum parameter required to establish a connection is to have the connection string include the server address, user name and password. Other parameters such as connection lifetime, database, pooling, etc. are optional but worthwhile exploring if you want to maintain a healthy level of database activity.

Selecting records

Once a database connection has been established it is a straight forward approach to acquire the data that you require from any table or schema. In this example, I have issued a simple SELECT statement with the intention of publishing the data to a .NET DataGridViewer. The class demonstrated here is the HanaDataReader class use to return a read only result set and output into a Windows form.

//Establish an active connection to the SAP HANA database

HanaConnection conn = new HanaConnection(“Server=your.hana.server;UserID=username;Password=password”);

//Open the database connection

conn.Open();

String strSQL = “select * from customers order by ID desc”;

//Execute the SQL statement

HanaCommand cmd = new HanaCommand(strSQL, conn);

//Read and store the result set

HanaDataReader reader = cmd.ExecuteReader();

//Bind the result set to a DataGridViewer/

dgViewTweet.DataSource = reader;

//Close the reader connection

reader.Close();

//Close the database connection

conn.Close();

Updating new records

Inserting records into the database is as easy as querying for it but not without additional effort to format and prepare the data. The example provided here stores the information from 5 text fields using standard SQL inserts statements executed by C#.

Upon loading the application, it connects to SAP HANA, loads 150 rows of customer records from the database and the intention here is to create a new piece of information and write it back to the table.

When the Save New button is clicked, the .NET framework runs through this piece of simple code to perform two sets of activities. Firstly, it will perform a SQL insert statement to store all the values captured from the text box by running the ExecuteNonQuery() method and secondly it reread the information by running the ExecuteReader() method as this will repopulate the DataGridViewer  with the new record.

The only information that the ExecuteNonQuery() method returns is the number of rows affected by the specific action and this method is useful for running SQL statements such as INSERT, UPDATE or DELETE.

HanaConnection conn = new HanaConnection(“Server=your.hana.server;UserID=username;Password=password”);

string sCust, sLifespend, sNewspend, sIncome, sLoyalty;

conn.Open();

HanaCommand insertCmd = new HanaCommand(“INSERT INTO Customers(CUSTOMER, LIFESPEND, NEWSPEND, INCOME, LOYALTY) “ + “VALUES(?, ?, ?, ?, ? )”, conn);

sCust = txtCustomer.Text;

sLifespend = txtLifespend.Text;

sNewspend = txtNewspend.Text;

sIncome = txtIncome.Text;

sLoyalty = txtLoyalty.Text;

HanaParameter parm = new HanaParameter();

//Customer

parm = new HanaParameter();

parm.HanaDbType = HanaDbType.NVarChar;

insertCmd.Parameters.Add(parm);

//Life Spend

parm = new HanaParameter();

parm.HanaDbType = HanaDbType.Double;

insertCmd.Parameters.Add(parm);

//New Spend

parm = new HanaParameter();

parm.HanaDbType = HanaDbType.Double;

insertCmd.Parameters.Add(parm);

//Income

parm = new HanaParameter();

parm.HanaDbType = HanaDbType.Double;

insertCmd.Parameters.Add(parm);

//Loyalty

parm = new HanaParameter();

parm.HanaDbType = HanaDbType.Double;

insertCmd.Parameters.Add(parm);

//Customers

insertCmd.Parameters[1].Value = sCust;

//Life Spend

insertCmd.Parameters[2].Value = sLifespend;

//New Spend

insertCmd.Parameters[3].Value = sNewspend;

//Income

insertCmd.Parameters[4].Value = sIncome;

//Loyalty

insertCmd.Parameters[5].Value = sLoyalty;

//Execute the insert statement

insertCmd.ExecuteNonQuery();

//Requery for the latest record from SAP HANA

String strSQL = “select * from customers order by ID desc”;

//Execute the SQL statement

HanaCommand cmd = new HanaCommand(strSQL, conn);

//Read and store the result set

HanaDataReader reader = cmd.ExecuteReader();

//Bind the result set to a DataGridViewer/

dgViewTweet.DataSource = reader;

//Close the reader connection

reader.Close();

//Close the database connection

conn.Close();

Using Stored Procedure

My guess is Stored Procedure has always been off limits for SAP applications that sits on the NetWeaver stack because of the vast database vendors out there and the effort to cater for the different method of creating and calling a procedure can add up to an unnecessary amount of overhead. What SAP has given us in equivalent is the lovely function module builder that we have all known to work with and love throughout the years. Like any high performing, respectable RDBMS out there in the market, SAP HANA has included the Stored Procedure function and using it with .NET is just as simple as the 
rest.

In this next example, the goal here is to change the record for Customer ID 150. The customer name Samuel Johnson has been misspelled and it needs to be corrected. Upon clicking on the Update Changes button, the .NET framework will call a Stored Procedure in SAP HANA, passing in the Customer ID and update the record using a standard SQL update command that is embedded within a Stored Procedure. A Stored Procedure can contain logic as simple as accepting fields and processing it to as complex as performing specific validation and returning an error if the conditions are not met.

CREATE PROCEDURE prcUpdateCustomers(IN ID INT, IN CUSTOMER NVARCHAR(60)) AS

  BEGIN

         UPDATE customers SET customer = :CUSTOMERWHERE ID = :ID;

  END;

The change to execute Stored Procedure is to set the Hana command type to StoredProcedure and passing in the actual name of the Stored Procedure.

                HanaCommand cmd = new HanaCommand(“”, conn);

                //Pass in the Stored Procedure Name

         cmd.CommandText = “prcUpdateCustomers”;

         cmd.CommandType = CommandType.StoredProcedure;

//Read the changed column data

string col0 = dgViewTweet[0, dgViewTweet.CurrentCell.RowIndex].Value.ToString();

string col1 = dgViewTweet[1, dgViewTweet.CurrentCell.RowIndex].Value.ToString();

string col2 = dgViewTweet[2, dgViewTweet.CurrentCell.RowIndex].Value.ToString();

string col3 = dgViewTweet[3, dgViewTweet.CurrentCell.RowIndex].Value.ToString();

//Establish an active connection to the SAP HANA database

HanaConnection conn = new HanaConnection(“Server=your.hana.server;UserID=username;Password=password”);

//Open the database connection

conn.Open();

//Create an instance of a HanaCommand

HanaCommand cmd = new HanaCommand(“”, conn);

//Pass in the Stored Procedure Name

cmd.CommandText = “schia_pal.prcUpdateCustomers”;

cmd.CommandType = CommandType.StoredProcedure;

//Prepare input parameters

HanaParameter param = new HanaParameter();

              param = cmd.CreateParameter();

               param.HanaDbType = HanaDbType.Integer;

               param.Direction = ParameterDirection.Input;

               param.Value = col0;    

               cmd.Parameters.Add(param);

               param = new HanaParameter();

               param = cmd.CreateParameter();

               param.HanaDbType = HanaDbType.NVarChar;

               param.Direction = ParameterDirection.Input;

               param.Value = col1;

               cmd.Parameters.Add(param);

//Execute he update statement

               cmd.ExecuteNonQuery();

               cmd.Dispose();

               conn.Close();

In brief and not trying to include needless details on the vast .NET sample codes which SAP has documented for the wider community, this article is just an example of some of the basic database communication that you can work with using Sap.Data.Hana. With the new SPS 08 client interface, customers who are heavily reliant on the .NET framework to address business problems can now have full SAP support when working with SAP HANA and the ability to fully utilise all features within the product.