Hi Gang,

I've had an issue in getting transactions to work with my Linq queries... Given the following code:

1                //
2                // TRANSACTION 
3                //
4                db = new DataClassesDataContext();
5                
6                try
7                {
8                    //db.Connection.Open();
9                    //db.Transaction = db.Connection.BeginTransaction();
10                   //db.CommandTimeout = 10000;
11   
12                   //db.Connection.Open();
13   
14                   //
15                   // Begin the transaction
16                   //
17                   //trans = db.Connection.BeginTransaction();
18   
19                   // Assign transaction to context class
20                   // All the database operation perform by this object will now use transaction 
21                   //db.Transaction = trans;
22   
23   
24                   //
25                   // read data file, parse, create datatable
26                   //
27                   DataTable dt = ParseDataFile(Server.MapPath("~/TempUploads/" + tempFileName));
28   
29                   // create a dataImportLog entry, and get ID for dataImportLog entries
30                   tbl_DataImportLog entImportLog = new tbl_DataImportLog();
31                   var newImport = new tbl_DataImportLog
32                   {
33                       CampaignID = Convert.ToInt32(Session["CampaignID"].ToString()),
34                       DataImportName = TxtImportName.Text,
35                       DateImported = DateTime.Now
36                   };
37                   db.tbl_DataImportLogs.InsertOnSubmit(newImport);
38                   db.SubmitChanges();
39   
40                   long newImportID = newImport.DataImportLogID;   // new import log ID
41   
42   
43                   tbl_DataImport entDataImport = new tbl_DataImport();
44   
45                   foreach (DataRow dr in dt.Rows)
46                   {
47                       var newLink = new tbl_DataImport
48                       {
49                           CampaignID = Convert.ToInt32(dr[dt.Columns["CampaignID"]]),
50                           DataImportLogID = newImportID,
51                           FileUrl = Convert.ToString(dr[dt.Columns["FileUrl"]]).Trim(),
52                           TrackUrl = Convert.ToString(dr[dt.Columns["TrackUrl"]]).Trim(),
53                           Title = Convert.ToString(dr[dt.Columns["Title"]]).Trim(),
54                           Description = Convert.ToString(dr[dt.Columns["Description"]]).Trim(),
55                           Keywords = Convert.ToString(dr[dt.Columns["Keywords"]]).Trim(),
56                           Body = Convert.ToString(dr[dt.Columns["Body"]]).Trim(),
57                           PageType = Convert.ToString(dr[dt.Columns["PageType"]]).Trim(),
58                           DateAdded = DateTime.Now
59                       };
60                       // add the data
61                       db.tbl_DataImports.InsertOnSubmit(newLink);
62                       db.SubmitChanges();
63                   }
64   
65   
66                   StringBuilder sbMessage = new StringBuilder();
67                   sbMessage.Append(dt.Rows.Count + " new link rows have been added for this campaign.<br />");
68                   //dt.Rows.Count 
69                   if (ChkShowData.Checked)
70                   {
71                       sbMessage.Append("The client ID has been added in a new column, which preceeds the data below.<br /> ");
72   
73                       // bind the resulting DataTable to a DataGrid Web Control
74                       GridView1.DataSource = dt;
75                       GridView1.DataBind();
76                   }
77                   litMessage.Text = sbMessage.ToString();
78                   PnlResult.Visible = true;
79   
80   
81                   // now that the new data set has been added, check if any master data exists to be modified
82                   // if not, add a new creative, and version, for the master data
83   
84   
85                   GenerateMasterData(newImportID);
86   
87   
88                   //
89                   // COMMIT THE TRANSACTION
90                   //
91                   //db.Transaction.Commit();

In line 38 I do a SubmitChanges() so I can get the ID of the newly-inserted row, then use that in the following query to insert into another table.  Now, I've taken a look at a few Linq transaction related articles, and attempted to implement them (as you can see from the code that is commented-out) - http://weblogs.asp.net/scottgu/archive/2007/08/23/linq-to-sql-part-7-updating-our-database-using-stored-procedures.aspx and  http://blogs.msdn.com/wriju/archive/2007/08/06/linq-to-sql-using-transaction.aspx have some good examples.

Placing a breakpoint at the catch (btw: catch and finally blocks omitted from the code), the two tables have been updated, but for some reason, there is a database timeout error, and the transaction rolls back.  When I remove the transaction-related code, it works fine.

I understand that the SubmitChanges() should do all the updates to a db context, however, in this case, I need to grab the newly-inserted ID, and use it in the next query, how can I get this going?

Thanks.
 

Hi tsanet,

When LINQ-SQL call SubmitChanges method, as far as I know, you don't need to implement transcation because the transaction has been implemented within the SubmitChanges method.

But it's different for your issue - you need to use the newly-inserted ID. So I suggest you could configure the connect string and set the timeout to suit your requirements.

For example: "Persist Security Info=False;Integrated ecurity=SSPI;database=northwind;server=mySQLServer;Connect Timeout=30".

Hope this helps you!

Thanks.

 I figured out the problem.

 In the GenerateMasterData() method (called in line 84), I was resetting the data context - db = new DataClassesDataContext() ... oops.

To ensure I'm using the same data context in the method, I'm passing it to the method and using that one.

 So I'm posting the code for anyone who may need to see how transactions in Linq transactions (one method) are accomplished:
 

1                //
2                // TRANSACTION 
3                //
4                db = new DataClassesDataContext();
5                db.Connection.Open();
6                db.Transaction = db.Connection.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted);
7                try
8                {
9                  .
10                 .
11                 .
12                   // create a dataImportLog entry, and get ID for dataImportLog entries
13                   tbl_DataImportLog entImportLog = new tbl_DataImportLog();
14                   var newImport = new tbl_DataImportLog
15                   {
16                       CampaignID = Convert.ToInt32(Session["CampaignID"].ToString()),
17                       DataImportName = TxtImportName.Text,
18                       DateImported = DateTime.Now
19                   };
20                   db.tbl_DataImportLogs.InsertOnSubmit(newImport);
21                   db.SubmitChanges();
22   
23                   // get the ID of the last-inserted record, for use in the next data insert
24                   long newImportID = newImport.DataImportLogID;   // new import log ID
25   
26   
27                   tbl_DataImport entDataImport = new tbl_DataImport();
28   
29                   foreach (DataRow dr in dt.Rows)
30                   {
31                       var newLink = new tbl_DataImport
32                       {
33                        .
34                        .
35                        .
36                       };
37   
38                       // add the data
39                       db.tbl_DataImports.InsertOnSubmit(newLink);
40                       db.SubmitChanges();         
41   
42                   // send the datacontext as we're in a transaction
43                   if (GenerateMasterData(db, newImportID))
44                   {
45                    .           
46                    .
47                    .
48                   }
49                   //
50                   // COMMIT THE TRANSACTION
51                   //
52                   db.Transaction.Commit();
53               }
54               catch (Exception ex)
55               {
56                   db.Transaction.Rollback();
57   
58                   lblMessage.CssClass = "errortext";
59                   lblMessage.Text = "Error importing data.<br>" + ex.Message.ToString();
60               }
61               finally
62               {
63                   db.Transaction = null;
64               }
65   .
66   .
67   .
68   
69   
70       protected bool GenerateMasterData(DataClassesDataContext db, long newImportID)
71       {
72        .
73        .
74        .
75       }

Take a look at my article on CodeProject or on my Blog. I have created a Linq DataContext class that makes setting Transaction levels a snap.

http://www.codeproject.com/KB/linq/DataContextTransactions.aspx

http://ablogonbothyourhouses.blogspot.com/