Bulk insert in SYBASE using .NET

Question

How can I do bulk data insert in Array in SYBASE table using in .NET. I don't want to use BCP utilities.

Answer 1

It's a bit untidy You have to use sp_dboption to turn it on then you can use Select Into to get the data in the you turn the option back off again. It's also recomended that your drop all triggers indexes etc before and put them back after for any 'erm lengthy operation...

How are you connected up, you might have a bit of fun if you are on ODBC, as it tends to blow up on proprietry stuff, unless you put pass thru on.

Found this, fater remembering similar troubles way back when with delphi and sybase

Sybase Manual

Answer 2

There is AseBulkCopy class in name space Sybase.Data.AseClient in Sybase.AdoNet2.AseClient.dll

DataTable dt = SourceDataSet.Tables[0];

using (AseBulkCopy bulkCopy = new AseBulkCopy((AseConnection)conn))
                    {
                        bulkCopy.BatchSize = 10000;
                        bulkCopy.NotifyAfter = 5000;
                        bulkCopy.AseRowsCopied += new AseRowsCopiedEventHandler(bc_AseRowsCopied);
                        bulkCopy.DestinationTableName = DestTableName;

                        bulkCopy.ColumnMappings.Add(new AseBulkCopyColumnMapping("id", "id");

                        bulkCopy.WriteToServer(dt);
                    }


        static void bc_AseRowsCopied(object sender, AseRowsCopiedEventArgs e)
        {
            Console.WriteLine(e.RowCopied + "Copied ....");
        }

Answer 3

You can see this example to see how to execute the insert statement. Then, you simply need to:

1. select each row of the excel at a time
2. build the insert command
3. execute it

or (the best way)

1. build an insert into command with several rows (not all! maybe 50 each time)
2. execute the command

One side note, this will take a lot more time that to do the simple bull copy!

Answer 4

After so much investigation, I found DataAdapter is able to bulk insert. It has property batchsize( I forgot the name). We can specify the number of rows, we want to insert in one trip. DataAdapter insert command should be specified.