DbTransaction.RollBack() not working when executing multiple stored procedures.

EDN Admin

EDN Admin

Well-known member
Joined
Aug 7, 2010
Messages
12,794
Location
In the Machine
So Ive been stuck on this for a day and decided to start looking else where for answers.  Basically, I came across an error with some existing code and noticed that the DbTransaction object wasnt rolling back.  The code below explains why the error occured and gives an example.  I can simply fix the error by changing the parameter type to an Int64, but that is not the issue I am worried about.  Its the fact that I would expect the DbTransaction object to rollback the execution of the first stored procedure once an error was raised on the second stored procedure.

As a FYI, no transaction gets created in either stored procedure that we are executing.


.Net Code
<div style="border-right:#7f9db9 1px solid;border-top:#7f9db9 1px solid;font-size:11px;overflow:auto;border-left:#7f9db9 1px solid;line-height:100%! important;border-bottom:#7f9db9 1px solid;font-family:Courier New;background-color:white
<table style="border-top-width:0px;border-left-width:0px;margin:2px 0px;width:99%;border-bottom:#eee 0px solid;border-collapse:collapse;background-color:#fff;border-right-width:0px" cellspacing=0 cellpadding=0>
<colgroup>
<col style="border-right:gray 1px solid;padding-right:5px;padding-left:10px;font-size:11px;vertical-align:top;width:5px;color:gray;font-family:Courier New;background-color:#eee;text-align:right
<col style="padding-left:10px;font-size:11px;border-bottom:#f7f7f7 1px solid;font-family:Courier New;white-space:nowrap
<tbody>
<tr>
<td><nobr>1</nobr></td>
<td><font style="font-size:11px </font><font style="color:green // This gets a Enterprise Library Database Object.  Works fine. </font><font style="font-size:11px  </font></td></tr>
<tr>
<td><nobr>2</nobr></td>
<td style="background-color:#f7f7f7 Database databaseObject = DataAccessFactory.GetDatabase();   </td></tr>
<tr>
<td><nobr>3</nobr></td>
<td></font><font style="color:blue using</font><font style="font-size:11px  (DbConnection connectionObject = databaseObject.CreateConnection())  </font></td></tr>
<tr>
<td><nobr>4</nobr></td>
<td style="background-color:#f7f7f7 {  </td></tr>
<tr>
<td><nobr>5</nobr></td>
<td>     connectionObject.Open();  </td></tr>
<tr>
<td><nobr>6</nobr></td>
<td style="background-color:#f7f7f7  </td></tr>
<tr>
<td><nobr>7</nobr></td>
<td>     DbTransaction transactionObject = connectionObject.BeginTransaction();  </td></tr>
<tr>
<td><nobr>8</nobr></td>
<td style="background-color:#f7f7f7      </td></tr>
<tr>
<td><nobr>9</nobr></td>
<td>     </font><font style="color:blue try</font><font style="font-size:11px  </font></td></tr>
<tr>
<td><nobr>10</nobr></td>
<td style="background-color:#f7f7f7      {  </td></tr>
<tr>
<td><nobr>11</nobr></td>
<td>          </font><font style="color:green //This stored proc works and executes fine.</font></td></tr>
<tr>
<td><nobr>12</nobr></td>
<td style="background-color:#f7f7f7           databaseObject.AddInParameter(storedProcDbCommand, </font><font style="color:blue "@PARAM1"</font><font style="font-size:11px , DbType.Int64, dataSetObject.Tables[0].Rows[0][</font><font style="color:blue "PARAM1"</font><font style="font-size:11px ]);   </font></td></tr>
<tr>
<td><nobr>13</nobr></td>
<td>          databaseObject.ExecuteScalar(storedProcDbCommand);
</td></tr>
<tr>
<td><nobr>14</nobr></td>
<td style="background-color:#f7f7f7           </font><font style="color:green //Error occurs because the expected type is Int64 not Int32.  Error Raised, goto Catch Statement. </font><font style="font-size:11px  </font></td></tr>
<tr>
<td><nobr>15</nobr></td>
<td>          databaseObject.AddInParameter(storedProcDbCommand2, "@PARAM1", DbType.Int32, longValueToInsert);
          databaseObject.UpdateDataSet(dataSetObject, </font><font style="color:blue "TABLE1"</font><font style="font-size:11px , storedProcDbCommand2, </font><font style="color:blue null</font><font style="font-size:11px , </font><font style="color:blue null</font><font style="font-size:11px , UpdateBehavior.Transactional, 0);  </font></td></tr>
<tr>
<td><nobr>16</nobr></td>
<td style="background-color:#f7f7f7 </td></tr>
<tr>
<td><nobr>17</nobr></td>
<td>     }  </td></tr>
<tr>
<td><nobr>18</nobr></td>
<td style="background-color:#f7f7f7      </font><font style="color:blue catch</font><font style="font-size:11px  </font></td></tr>
<tr>
<td><nobr>19</nobr></td>
<td>     {  </td></tr>
<tr>
<td><nobr>20</nobr></td>
<td style="background-color:#f7f7f7           transactionObject.RollBack(); <font color="#008000 //This doesnt rollback anything the first stored procedure inserted into the database.</font></td></tr>
<tr>
<td><nobr>21</nobr></td>
<td>          </font><font style="color:blue throw</font><font style="font-size:11px ;  </font></td></tr>
<tr>
<td><nobr>22</nobr></td>
<td style="background-color:#f7f7f7      }  </td></tr>
<tr>
<td><nobr>23</nobr></td>
<td>} </td></tr></tbody></table>
Thanks in advance for any help.

View the full article
 
Back
Top