Connect to SAP HANA Database in C-Sharp or VB.NET Code using ODBC Connection
In this programming tutorial, I want to demonstrate SAP HANA database connection from a C Sharp C# application or VB.NET application code using ODBC connection.
I have created a Windows Forms C# application on Visual Studio 2017 using existing application templates. Then I created the same solution in VB.NET
Microsoft .NET developers can choose any application template regarding their reuirement to connect SAP HANA database.
First of all, to connect to a SAP HANA Database developers should create a SYSTEM DSN entry.
As I documented in tutorial Connect SAP HANA Database from SQL Server using Linked Server, programmers can define an ODBC connection with Data Sources (ODBC) from Administrative Tools.
The related ODBC connection should be created using the HDBODBC driver which is provided by SAP.
I have already described how to download SAP HANA Database Client and install for HDBODBC driver.
After SAP HANA Database Client is installed and a SYSTEM DSN is created via ODBC Data Sources, C-Sharp or VB.NET developers can continue with sample program code below.
Within sample project code, first import namespace "System.Data.Odbc"
Then define a string variable for connection string as follows:
"DSN={System DSN Name}; SERVERNODE={HANA Server:Port}; UID={Database User}; PWD={Database User Password}; DATABASENAME={Database Name}"
ServerNode details, the HANA Database server and port number was already used in the definition of the ODBC connection createad in SYSTEM DSN tab.
If you don't know the database name, you can query M_DATABASES system view to find out the name of your HANA database. Please do not confuse database name with catalog name
string connectionString = "DSN=KodyazHANADb; SERVERNODE=hana.server.kodyaz:30815; UID=myHDBUser; PWD=myDBUserPwd; DATABASENAME=myDBName";
Execution of the following C# code will validate if the ODBC connection to SAP HANA database could be successfully established or not.
string connectionString;
connectionString = "DSN=KodyazHANADb; SERVERNODE=hana.server.kodyaz:30815; UID=myHDBUser; PWD=myDBUserPwd; DATABASENAME=myDBName";
OdbcConnection conn = new OdbcConnection(connectionString);
conn.Open();
...
conn.Close();
It is important to close connections if they are not needed any more.
In order to execute SQLScript or SQL query on SAP HANA database to fetch data from database tables, OdbcCommand object and OdbcDataReader object can be used as in below sample CSharp code.
Please note that I have placed a multiline textbox control on Form layout to display data (a few table columns from SQLScript query result)
To show how parameters can be passed to SQL command, I filtered tables from a given schema that I pass as a parameter.
Please try to use parametric queries or procedures with parameters instead of building dynamic ad-hoc SQL queries which might be a target of a SQL injection attack.
using (
OdbcCommand cmd = new OdbcCommand(
"select top 3 * from \"PUBLIC\".\"M_TABLES\" where schema_name = ?;", conn
)
)
{
cmd.Parameters.AddWithValue("@schema_name", "MYHDBUSER");
using (OdbcDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string rowData = reader.GetString(0);
rowData = rowData + ' ' + reader["TABLE_NAME"];
textBox1.Text = textBox1.Text + System.Environment.NewLine + rowData;
}
}
}
Above C-Sharp code connects to SAP HANA database.
Executes a parametris SQLScript query.
Reads returning data set within in WHILE loop.
Within the loop, first column value is fetched as well as TABLE_NAME column.
So I just wanted to show the two ways a column value can be read (either using column index or column name)
When I execute above code within my sample Visual Studio C# project, I got following outcome as result of the application.
VB.NET Code for SAP HANA Database Connection using ODBC in Visual Studio
I also wanted to attach VB.NET codes that programmers can use for SAP HANA database connection using a Windows Forms application built in a Visual Studio solution. Below are sample VB codes for developers to use in HANA database connection projects
Dim connectionString As String
connectionString = "DSN=KodyazHANADb; SERVERNODE=hana.server.kodyaz:30815; UID=myHDBUser; PWD=myDBUserPwd; DATABASENAME=myDBName"
Dim conn As New OdbcConnection(connectionString)
Try
conn.Open()
TextBox1.Text = "SAP HANA Database Connection is successful!"
Using cmd As New OdbcCommand(
"select top 3 * from ""PUBLIC"".""M_TABLES"" where schema_name = ?;", conn
)
cmd.Parameters.AddWithValue("@schema_name", "MYHDBUSER")
Using reader = cmd.ExecuteReader()
While reader.Read
Dim rowData As String = reader.GetString(0)
rowData = rowData + " " + reader("TABLE_NAME")
TextBox1.Text = TextBox1.Text + System.Environment.NewLine + rowData
End While
End Using
End Using
conn.Close()
Catch ex As Exception
TextBox1.Text = ex.ToString()
End Try
Odbc Exception due to Architecture Between DSN Driver and Application
You might have an exception thrown when you execute your Visual Studio project and OdbcConnection is being opened with an error message indicating that there is an architecture mismatch between the application and the DSN referred. Since I got this message in Turkish, error message is as follows
System.Data.Odbc.OdbcException (0x80131937): ERROR [IM014] [Microsoft][ODBC Driver Manager] Belirtilen DSN'de Sürücü ile Uygulama arasında bir mimari uyuşmazlığı var
This is because we are using a 64-bit ODBC driver (SAP HANA Database Client for HDBODBC driver)
Visual Studio developers can check their project Properties settings in Build tab or Compile tab for Target CPU option and "Prefer 32-bit" checkbox options.
Building the project as an 64-bit application will save developers from above mentioned error message (OdbcException)
For a Visual Basic project check Compile tab in project properties
If the project is created for C# then on project properties same configuration can be managed at Build tab.
Change HANA Database User Password
In some cases when the programmer is trying to connect to SAP HANA database via ODBC connection a message informing that the HANA database user should change the password can be returned preventing ODBC connection.
Although I did not get this message when I connect to the database using SAP HANA Studio as the GUI (Graphical User Interface), it was interesting to get the exception when HDBODBC connection is being established.
The solution is easy. The application developer can connect to database on SAP HANA Studio and execute following ALTER USER command with password arguments.
ALTER USER myHDBUser PASSWORD "myPwd*123";
For example above SQLScript command will set my HANA database user password to given one as above