Skip to Content
Author's profile photo Former Member

Using the .NET ODBC Connection to Bulk Insert Data

After spending some time trying to load data using a C# ETL loader we created, I noticed that SAP exposes a “Bulk load” option in their driver. I couldn’t find any examples on using it, so I figured I would post an example. Performance is pretty good, with the BatchSize = 1000, I am able to load 1 million records in about 10 seconds.

        private string dbConnectString;
        private HanaCommand dbConnection;
        public void LoadData(DataTable dt, string tablename, string[] columns)
            string[] fullName = tablename.Split('.');
            dbConnectString = ConfigurationManager.AppSettings["DatabaseString"];
            HanaConnection dbConnection = new HanaConnection(dbConnectString);
            HanaCommand da = new HanaCommand("SET SCHEMA " + fullName[0], dbConnection);
            HanaBulkCopy blkcmd = new HanaBulkCopy(dbConnection);
            blkcmd.BulkCopyTimeout = 1000;
            blkcmd.DestinationTableName = fullName[1];
            HanaBulkCopyColumnMappingCollection mappings = blkcmd.ColumnMappings;
            for (int i = 0; i < columns.Length; i++)
                mappings.Add(new HanaBulkCopyColumnMapping(i, columns[i]));
            blkcmd.BatchSize = 1000;

A few notes about implementation and some things I found:

1.The datatable has to have matching datatypes for the columns, but the rows can be all strings, so when you initially build your datatable you have to do something like this for each column:

DataTable dt = new DataTable();                                       
DataColumn dc = new DataColumn("col" + j.ToString(), typeof(double));

And specify if it is a double, string or DateTime etc.

2.The BatchSize has some strange behavior. I didn’t play with it much, but if you don’t define it then performance is really terrible. Not any better than inserting one record at a time. I found 1000 gives great performance, but I bet it could be even better, so if someone is able to do some metrics that would be great.

3. I am using the HanaBulkCopyColumnMapping in my example, but it is completely optional to use. If you don’t provide the mapping, then column 0 in the datatable gets mapped to column 0 in Hana 1:1, 2:2 etc. The mapping is nice of course, because then you don’t have to have the same number of columns or the correct order, I just pass the tables columns names that I am loading in as an array string in the example.

4. You will notice that I switch the Schema before loading. If you don’t do that, then even if you pass in the fully qualified name (ex USERS.USER) the bulk loader will still try to load into its own schema.

5. The connection string is a pretty simple string that consists of something like this:


Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Yaron Inghel
      Yaron Inghel

      Hi Philip,

      Helpful article.

      I encountered the same issues couple of weeks ago.

      About the BatchSize - according to the Ado .net reference (SAP HANA Data Provider for Microsoft ADO.NET Reference - SAP Library) the default batch size is 0 and according to SAP: "Setting this property to zero causes all the rows to be sent in one batch"

      But as you mentioned it acts like it sends each row at a time (and you are correct. I started a trace in the HANA and a insert statement was executed for each row)

      I then tried to do it with batch size in the same size as my data (e.g 10,000 rows - batch size 10,000) - and it acted like in the 0 option..

      1000 or 5000 worked well for my example (10,000 rows).

      So if you have some more insights about it I will be glad to hear (For a stable solution..)

      Thank you,

      Yaron Inghel

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Yes, I had the same experience. unfortunately, I don't have any more to add. Not sure if it is a bug or what. At first I was so disappointed with the performance, but then that simple switch made it work really well.

      Author's profile photo Former Member
      Former Member

      Philip - First of all, great article. It really helped me out. I have a separate question for you in case you have run into it.

      I am trying to check if a table exists in the HANA database, and if it does not then create it.

      The script (dynamic SQL) is correct because it works just fine when I run it in HANA studio, but throws the following error from the .NET application:


      identifier must be declared: 1: line 5 col 4 (at pos 138)


      This is the code that is being executed from .NET:

      declare tblCount int;
      select count(*) into tblCount from tables where table_name like 'mytable' and schema_name = 'myschema';
      if :tblCount = 0 then
      CREATE COLUMN TABLE "mytable" ("Id" INT NOT NULL primary key generated by default as IDENTITY, 
      end if;END;


      Author's profile photo Margoth Del Rosario
      Margoth Del Rosario


      Im actually having the same problem... Please if you are solved this could you share!