Skip to Content

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.

/wp-content/uploads/2014/10/sap_data_hana_564270.png

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;

/wp-content/uploads/2014/10/ref1_564271.png /wp-content/uploads/2014/10/ref2_564276.png

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.

/wp-content/uploads/2014/10/conn_564277.png

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();

/wp-content/uploads/2014/10/table1_564281.png

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.

/wp-content/uploads/2014/10/table2_564283.png

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();

/wp-content/uploads/2014/10/table3_564284.png

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;

/wp-content/uploads/2014/10/table4_564285.png

//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();

/wp-content/uploads/2014/10/table5_564293.png

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.

To report this post you need to login first.

10 Comments

You must be Logged on to comment or reply to a post.

  1. Kiran Kumar

    Hello Simon,

    A quick question, is it possible to call HANA Stored Procedure which accepts table input parameter from .NET via the ADO.Net connector?. Looked at the examples but all of them are related to a calling with single input values rather than table input values.

    My use case is that I want to send a bunch of records(ID, user, dates, etc) to a stored procedure which  insert or update these records in a DB table. It does some validations before the insert/update operations all that logic is in the stored procedure.

     

    Any inputs here is greatly appreciated.

     

    Best Regards,

    Kiran

    (0) 
    1. Lars Breddemann

      Table typed input parameters are not supported for client calls. This applies to all clients (JDBC, ODBC, ADO.net… ).

       

      If you need to transfer a whole table content you need to look into other options like filling temporary tables.

       

      – Lars

      (0) 
      1. Vijayesh Narayan

        Hello Lars,

         

        Thanks for your inputs about usage of table-type parameters.

        I am new to HANA.

        I am migrating Stored Procedures from SQL to HANA.

        I have successfully connected HANA database in .Net environment and I’m able to extract records from a Stored Procedure using HanaConnection class in sap.data.hana dll.

         

        But there are some SPs for which I need to supply a table-type output parameter and there are some SPs where I need to supply a table-type input parameter.

         

        For the input parameter as a table-type, I need to send bulk of records from .Net environment onto Hana database. In SQL, that was possible using User-defined Table types and specifying the respective input parameter in the SP as that table-type.

         

        But, how can I do it in HANA? You mentioned about filling temporary tables, but how to fill them using data that I need to send from .Net?

         

         

        Secondly, for the SP where I have specified a table-type output parameter, I defined the parameter values in my .Net end as:-

                        param = new HanaParameter();

                        param = cmd.CreateParameter();

                        param.Direction = ParameterDirection.Output;

                        param.HanaDbType = HanaDbType.TableType;               

                        param.Value = “?”;

         

        along with other input parameters which are Integer values. When I ran the code, the control gave error on the statement “param HanaDbType = HanaDbType.TableType;”, stating that “The feature is not supported”.

         

        So, I’m not able to specify table-type as an input as well as an output variable.

         

        Any help regarding this is greatly appreciated. Thanks!

         

        Kind Regards

        Vijayesh

        (0) 
        1. Lars Breddemann

          Hey Vijayesh,

           

          in order to use the table-typed output you need to fetch the corresponding result set(s).

          As mentioned before SAP HANA currently doesn’t support table typed parameters for client-server communication.

           

          – Lars

          (0) 
  2. Edison Cui

    Hi,
    I would like to  make progrmming a small application on the motorola scanner , the system is window CE6.0 and have to use vs2008 to develop, but the framework is .net3.5 not .net4.5 in vs2008, my question is is it possible to connect to SAP hana database using vs2008 with .net3.5, how to do with it? thank you very much!

    (0) 
  3. Edison Cui

    during debug the application there is an error “External component has thrown an exception” on the row of HanaDataReader reader = cmd.ExecuteReader();, do you know whats problem?

    (0) 
    1. Ricardo Renteria

      Edison, in that OS do you have installed the ADO .NET API for connection to HANA database as Simon mention in Getting Started section? Do you have the reference to Sap.Data.Hana in your code?

      (0) 
  4. Ricardo Renteria

    Hi Simon,

    I see you use the connection string with the parameters needed to establish the connection with the HANA DB server, but I don’t see the schema in which run the queries.

    If I run a query only with those parameters I get the error “the table XXX does not exist in the schema SYSTEM”; the same occurs if I try to add the “Current schema=MYHANADATABASE” parameter to the connection string. Despite I set the schema, the query looks like still address the SYSTEM schema.

    How to solve this?

    (0) 

Leave a Reply