Transactions

Question

User-1283927044 posted

I'm not sure where this falls, if it's a .Net issue or a Sql issue.  Here goes.

I am doing a bulk upload, inserting records into a table.  I have my dal pre-built to insert records one at a time, and the stored proc inserts one at a time, with a begin tran, commit tran wrapped around it.  I want to create an interface that allows people to upload records, let's just say to the Person table. 

The way I'd like to handle it is to read the csv file, load it into a Generic List<Person> object and then iterate through that object to insert each person into the table.  That's easy.  My question is, instead of placing the transaction in the proc on the individual insert, is it possible to put the transaction around iteration of the List<Person> so that if one insert fails all inserts are rolled back and I throw an exception?  This is all conceptual, so I don't know if there's code I could provide.

Answer 1

User-1360095595 posted

Here's what I'm thinking, if you're using say a SqlConnection.  The SqlConnection class has a BeginTransaction method, which returns a SqlTransaction object.  This object has Commit & Rollback mehtods.  I'm thinking you can use BeginTranaction, do all your DB operations, and if no error occurs, then you call commit, otherwise you rollback.

I haven't tried any of this; don't know that it'll work for sure; just theory at this point :-)

Answer 2

User-1283927044 posted

I was thinking that.  What I was concerned about was if I was using a stored proc for each (single) insert, once it completed the proc and recalled it, would it recognize it as the same transaction or separate ones so that it only rolled back the insert that failed.   I guess there's only one way to find out.

However, I was thinking about it on the way to work, it's rather inefficient to do an insert this way anyway, I'm better off trying to do a bulk insert somehow. and wrap that in the transaction (either in single proc or in C# as you described).  I'll open up a new thread for best methods at a bulk insert from a csv file.  Thanks.

Answer 3

User-1283927044 posted

Could I wrap something like this in the transaction?  Would that work?  I just stumbled on this from another thread.  My datasource would be a csv file, but that shouldn't matter, I'm thinking I could wrap this and it would work.

string cnstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\test.mdb;";

string sql="Select * from Customers";

OleDbDataAdapter da = new OleDbDataAdapter(sql, cnstr);
DataTable dt = new DataTable();
da.Fill(dt);

SqlConnection cn = new SqlConnection(“YourConnectionString”);
cn.Open();

SqlBulkCopy bulkcopy = new SqlBulkCopy(cn);
bulkcopy.DestinationTableName = "SampleCustomers";
SqlBulkCopyColumnMapping colmap1 = new SqlBulkCopyColumnMapping(0, "CusCode");
SqlBulkCopyColumnMapping colmap2 = new SqlBulkCopyColumnMapping(1, "CusName");
SqlBulkCopyColumnMapping colmap3 = new SqlBulkCopyColumnMapping(2, "CusSex");
SqlBulkCopyColumnMapping colmap4 = new SqlBulkCopyColumnMapping(3, "CusCity");
SqlBulkCopyColumnMapping colmap5 = new SqlBulkCopyColumnMapping(4, "CusState");
SqlBulkCopyColumnMapping colmap6 = new SqlBulkCopyColumnMapping(5, "CusType");
bulkcopy.ColumnMappings.Add(colmap1);
bulkcopy.ColumnMappings.Add(colmap2);
bulkcopy.ColumnMappings.Add(colmap3);
bulkcopy.ColumnMappings.Add(colmap4);
bulkcopy.ColumnMappings.Add(colmap5);
bulkcopy.ColumnMappings.Add(colmap6);

bulkcopy.WriteToServer(dt);
bulkcopy.Close();
cn.Close();