如何用C#.Net桌面应用连接SAP HANA云数据库及配置连接字符串

Hey Sunil, welcome to the world of SAP HANA development! Let's walk through getting your C# desktop app connected to your HANA Cloud instance and fix up the code you shared.

First, make sure you've installed the SAP HANA Data Provider for .NET via NuGet (look for SAP.Data.Hana.Core or SAP.Data.Hana.Core.v4.0 depending on your .NET framework version).

Connection String Format (Basic Authentication) #

The standard connection string for HANA Cloud uses SSL (required) and looks like this:

Server=<your_hana_host>:443;UID=<your_username>;PWD=<your_password>;Encrypt=true;ValidateCertificate=false

• <your_hana_host>: Find this in your SAP BTP Cockpit (e.g., xxxx.hana.trial-eu10.hanacloud.ondemand.com)
• 443: Default port for HANA Cloud SSL connections
• Encrypt=true: Mandatory for HANA Cloud
• ValidateCertificate=false: Safe for trial environments; set to true in production and configure trusted certificates

Storing in App.config #

It's best practice to keep connection strings in your app's config file (e.g., App.config for desktop apps):

<configuration>
  <connectionStrings>
    <add name="Hana" 
         connectionString="Server=xxxx.hana.trial-eu10.hanacloud.ondemand.com:443;UID=YOUR_USER;PWD=YOUR_PASSWORD;Encrypt=true;ValidateCertificate=false" 
         providerName="SAP.Data.Hana.Core" />
  </connectionStrings>
</configuration>

Looking at your code, there are a few small issues we need to address:

Key Issues in Your Original Code #

• Case sensitivity: C# is case-sensitive, so con.open() should be conn.Open() (note the capital O and correct variable name conn)
• Table name formatting: The EPM demo tables already have fully qualified names (including schema/namespace), so you don't need to append the SCHEMA variable to them
• Incorrect join condition: The p."SUPPLIERID.PARTNERID" field doesn't exist in the EPM model; it should be p.SUPPLIERID joining to bp.PARTNERID

Corrected Code #

Here's the revised version with fixes and best practices (like using statements to auto-manage resources):

using System.Data;
using System.Configuration;
using SAP.Data.Hana;

namespace HanaCSharpDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            // Pull connection string from config
            var connectionString = ConfigurationManager.ConnectionStrings["Hana"].ConnectionString;

            // Use 'using' to auto-dispose the connection
            using (HanaConnection conn = new HanaConnection(connectionString))
            {
                try
                {
                    conn.Open();
                    System.Console.WriteLine("Successfully connected to SAP HANA Cloud!");

                    // Fully qualified table names (enclosed in quotes for special characters like ::)
                    const string PRODUCTS_TABLE = "\"sap.hana.democontent.epm.data::EPM.MD.Products\"";
                    const string PARTNER_TABLE = "\"sap.hana.democontent.epm.data::EPM.MD.BusinessPartner\"";
                    const string TEXT_TABLE = "\"sap.hana.democontent.epm.data::EPM.Util.Texts\"";

                    // Corrected SQL query
                    string sqlQuery = $"SELECT t.TEXT AS \"Name\", p.PRODUCTID as \"Product ID\", p.CATEGORY as \"Category\" " +
                                      $"FROM {PRODUCTS_TABLE} p " +
                                      $"INNER JOIN {TEXT_TABLE} t ON t.TEXTID = p.NAMEID " +
                                      $"INNER JOIN {PARTNER_TABLE} bp ON p.SUPPLIERID = bp.PARTNERID";

                    using (HanaDataAdapter dataAdapter = new HanaDataAdapter(sqlQuery, conn))
                    {
                        DataTable testTable = new DataTable();
                        dataAdapter.Fill(testTable);

                        // Print results to verify
                        System.Console.WriteLine($"\nRetrieved {testTable.Rows.Count} product records:");
                        foreach (DataRow row in testTable.Rows)
                        {
                            System.Console.WriteLine($"ID: {row["Product ID"]} | Name: {row["Name"]} | Category: {row["Category"]}");
                        }
                    }
                }
                catch (HanaException hanaEx)
                {
                    System.Console.WriteLine($"HANA Error: {hanaEx.Message}");
                }
                catch (System.Exception ex)
                {
                    System.Console.WriteLine($"General Error: {ex.Message}");
                }
                finally
                {
                    // Ensure connection is closed
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                }
            }
        }
    }
}

• Verify Permissions: Make sure your HANA user has SELECT access to the EPM tables. Run this in HANA Studio/DBeaver:

  GRANT SELECT ON "sap.hana.democontent.epm.data::EPM.MD.Products" TO <your_username>;
  GRANT SELECT ON "sap.hana.democontent.epm.data::EPM.MD.BusinessPartner" TO <your_username>;
  GRANT SELECT ON "sap.hana.democontent.epm.data::EPM.Util.Texts" TO <your_username>;
  

• Test Queries First: Validate your SQL in a tool like HANA Studio or DBeaver before putting it into code—it's easier to debug that way!
• Use Parameterized Queries: For production code, avoid string concatenation for queries. Use HanaCommand with parameters to prevent SQL injection.

内容的提问来源于stack exchange，提问作者Sunil