How to use SqlBulkCopy with transaction on ReliableSqlConnection in sql azure

azure sqlbulkcopy sql-server transactions

Question

Our application on azure cloud service, We need to perform some operations on sql azure using the same transaction, (insert into and SqlBulkCopy) using ReliableSqlConnection to allow Transaction.Rollback If any steps fail

but run this code: using (SqlBulkCopy ContactsDistBulkCopy = new SqlBulkCopy(oConnection, SqlBulkCopyOptions.KeepIdentity, oTransaction))

Ask for system.data.connection and not ReliableSqlConnection ?

1
2
10/30/2014 1:22:37 PM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Popular Answer

How much data are you passing in Bulk? You might be able to use a Table-Valued-Parameter approach instead of Bulk Copy, and that is simple to include in a transaction. The table you assemble on the client is sent to SQL as a parameter very quickly, and inserts are performed with BULK optimizations on the server. This is appropriate for ~O(10000) rows. This would fit into the ReliableSQLConnection framework, as long as you use the rsc.ExecuteCommand style of invoking the command.

You can find more details at http://msdn.microsoft.com/en-us/library/bb510489.aspx

1
10/31/2014 2:44:59 AM

Related Questions

When dealing with SQL Azure, SQLBulkCopy connection issues occur.

Knowledge Base article ...977291... gives this error message as a symptom of a Windows 2003 TCP/IP issue.
azure-sql-database sqlbulkcopy
asked by RichB

When SQLBulkCopy is used with Transaction, an event is fired each time a batch is copied.

I have finally decided to use an internal transaction. ATM it seems to be working fine if an error is encountered the current batch is rolled back. Regarding Ben's comment above, i tried this but its not possible, its not as though it loops through a section of code where you could raise an event. In the end i used the notifyafter property, which r...
c# sqlbulkcopy sql-server
asked by Hans Rudel

Getting an exception writing a boolean to sql using a datatable and SqlBulkCopy

Encountered the same issue: setting bool column to true/false was ignored by bulk copy, all values were set to null. The previous answer helped to resolve it, but I had to add mappings for all columns to make it work:...foreach (DataColumn column in table.Columns) { sqlBulk.ColumnMappings.Add(column.ColumnName, column.ColumnName); } sqlBulk.Wri...
c# datatable sqlbulkcopy sql-server
asked by Michael Vashkevich

Insert Dictionary elements into SQL table as rows using IDatareader and SqlBulkCopy

As you already have figured out, you need to implement custom ...IDataReader.... As your source is Dictionary - only few relatively simple method/properties such as int FieldCount, bool Read(), object Get(int i) should be implemented....You may find useful examples (...a simple one...) of custom IDataReader implementations by googling ...sqlbulkcop...
c# datareader dictionary sql sqlbulkcopy
asked by kasitan

SqlBulkCopy Alternative for Temporary Table on SQL Azure

SqlBulkCopy to temp table is now supported in SQL DB V12. You don't need these hacks now. If you are still on V11, TVP is the way to go.
azure-sql-database bulkinsert c# sql sqlbulkcopy
asked by Satya_MSFT

Timeout Expired using SqlBulkCopy when there are 300Million rows in SQL Server Database

You have 2 properties on your sqlBulkCopy object that can help you...BulkCopyTimeout: This is the value of the timeout, 30 seconds by default. Set this value to 0 to disable the timeout entirely....BatchSize: Instead of inserting 300MM straight into the table, inserts the records at a batched size, maybe 1MM/time to prevent the timeout issue (and e...
sql sqlbulkcopy timeout
asked by Jason W

How do I capture the data passed in SqlBulkCopy using the Sql Profiler?

Capturing event info for bulk insert operations ( ...BCP.EXE..., ...SqlBulkCopy..., and I assume ...BULK INSERT..., and ...OPENROWSET(BULK...... ) is possible, but you won't be able to see the individual rows and columns....Bulk Insert operations show up as a single (well, one per batch, and the default is to do all rows in a single batch) DML stat...
bulkinsert sqlbulkcopy sql-server sql-server-2012 sql-server-profiler
asked by Solomon Rutzky

SQL Trigger Update to Uppercase doesn't work using SQLBulkCopy Insertion

When you are creating your ...SqlBulkCopy... object add ...SqlBulkCopyOptions.FireTriggers... like so:...SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.FireTriggers); ...FireTriggers... When specified, cause the server to fire the insert triggers for the rows being inserted into the database. - ...SqlBulkCopyOptions...
sqlbulkcopy sql-server-2008-r2 triggers
asked by SqlZim

Can I use sqlbulkcopy with Azure SQL Paas?

Short answer is, yes, you can use SQLBulkCopy to insert data from your .net app to SQL Azure....However, if you have too much record you might face SQL Azure throttling issue....Now, In case of CPU throttling SQL Database will not throw an error but will slowdown the operation and you will probably just get a timeout error. At this time there is no...
azure-sql-database sqlbulkcopy sql-server
asked by Aram

SqlBulkCopy *VERY* slow on Azure Sql and C#

Well. I removed all the indexes, and it made some difference, but the batch was still timing out at 10 minutes. I removed the outer ambient transaction scope (rather than using ...TransactionScope.Suppress...) and all of a sudden the times are looking 'normal' again. It's taking about 50 seconds to insert, and it's getting closer to maxing the DTU ...
azure azure-sql-database c# sqlbulkcopy sql-server
asked by Carl

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow