Once I got the initial data transfer up and running, I needed to place the whole thing into a transaction, I was doing multiple table transfers (with some data manipulation). I could not have half a database transferred, even if I did throw an error.
I simply threw in an SqlTransaction, and made everything run inside that.
However, I hit an exception when using the SqlCommandBuilder to create the insert command:
ExecuteReader requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized.
I did not realize why the CommandBuilder needed a transaction, nor could I add the transaction to the builder. After all, it was only creating a SQL statement wasn't it??
I don't know if I was slow on the uptake... (I am writing this blog to hopefully help others out there who might be just as slow as I) but it took me forever to realise that it needed to run the SELECT SQL statement to get the details for creating the INSERT.
As I had created the SqlCommand by placing the SELECT statement into the constructor, I had not thought about assigning the transaction to the SelectCommand.
- DataTable configResults #008000;">= #008000;">new DataTable#008000;">(#666666;">'Config'#008000;">)#008000;">;
- SqlConnection sourceConnection #008000;">= #008000;">new SqlConnection#008000;">(_sourceConnection#008000;">)#008000;">;
- sourceConnection#008000;">.#0000FF;">Open#008000;">(#008000;">)#008000;">;
- SqlDataAdapter a #008000;">= #008000;">new SqlDataAdapter#008000;">(#666666;">'SELECT * FROM Config', sourceConnection#008000;">)#008000;">;
- a#008000;">.#0000FF;">AcceptChangesDuringFill #008000;">= #0600FF; font-weight: bold;">false#008000;">;
- a#008000;">.#0000FF;">Fill#008000;">(configResults#008000;">)#008000;">;
- a#008000;">.#0000FF;">Dispose#008000;">(#008000;">)#008000;">;
- SqlConnection destConnection #008000;">= #008000;">new SqlConnection#008000;">(_destinationConnection#008000;">)#008000;">;
- destConnection#008000;">.#0000FF;">Open#008000;">(#008000;">)#008000;">;
- SqlTransaction transaction #008000;">= destConnection#008000;">.#0000FF;">BeginTransaction#008000;">(#008000;">)#008000;">;
- #0600FF; font-weight: bold;">try
- #008000;">{
- SqlDataAdapter profileAdapter #008000;">= #008000;">new SqlDataAdapter#008000;">(#666666;">'SELECT * FROM Config', destConnection#008000;">)#008000;">;
- profileAdapter#008000;">.#0000FF;">SelectCommand#008000;">.#0000FF;">Transaction #008000;">= transaction#008000;">;
- SqlCommandBuilder builder #008000;">= #008000;">new SqlCommandBuilder#008000;">(profileAdapter#008000;">)#008000;">;
- profileAdapter#008000;">.#0000FF;">InsertCommand #008000;">= builder#008000;">.#0000FF;">GetInsertCommand#008000;">(#008000;">)#008000;">;
- profileAdapter#008000;">.#0000FF;">InsertCommand#008000;">.#0000FF;">Transaction #008000;">= transaction#008000;">;
- profileAdapter#008000;">.#0000FF;">Update#008000;">(configResults#008000;">)#008000;">;
- profileAdapter#008000;">.#0000FF;">Dispose#008000;">(#008000;">)#008000;">;
- builder#008000;">.#0000FF;">Dispose#008000;">(#008000;">)#008000;">;
- transaction#008000;">.#0000FF;">Commit#008000;">(#008000;">)#008000;">;
- #008000;">}
- #0600FF; font-weight: bold;">catch
- #008000;">{
- transaction#008000;">.#0000FF;">Rollback#008000;">(#008000;">)#008000;">;
- #008000;">}
- #0600FF; font-weight: bold;">finally
- #008000;">{
- transaction#008000;">.#0000FF;">Dispose#008000;">(#008000;">)#008000;">;
- #008000;">}
So you can see from the code snippet above, in line 18, I had to assign the transaction to the SELECT command after creating the DataAdapter.
Trackback URL for this post:
Alana Whelan
from Alana Whelan on Mon, 02/01/2012 - 18:44Ella Gamache
from Ella Gamache on Thu, 29/12/2011 - 13:02Elizabeth Hubbert
from Elizabeth Hubbert on Fri, 23/12/2011 - 16:58Chase Phelps
from Chase Phelps on Mon, 19/12/2011 - 12:45Lydia Garrard
from Lydia Garrard on Fri, 02/12/2011 - 06:45- dgrinberg's blog
- Login or register to post comments
