freelanceprogrammers.org Forum Index » ASP
ADO.Net Transactions
Joined: 10 Jan 2006
Posts: 33
ADO.Net Transactions
I am trying to set up a method to do an insert of several records. I am passing
in an arraylist of SP_Parameters which is a class I have (it is below). I am
confused on how this will work in regards to the using statement. The foreach
has all the data for each insert which is being sent to a stored proc. I want
to insert them all or non hence the transaction. I don`t know where to put the
commit and rollback when using the "using" statement for the IDisposable
cleanup.
public void setTransactionData(ref string ProcedureName, ArrayList Params) {
using (SqlTransaction myTransaction = new SqlTransaction()) {
using (SqlConnection conn = new
SqlConnection(ConfigurationSettings.AppSettings["ConnString"])) {
myTransaction = conn.BeginTransaction();
foreach (SP_Parameters myParams in Params) {
using (SqlCommand cmd = new SqlCommand(ProcedureName, conn)) {
cmd.Transaction = myTransaction;
cmd.CommandType = CommandType.StoredProcedure;
try {
for (int count = 0; count <= myParams.count - 1; count++)
cmd.Parameters.Add((String) myParams.ParameterName[count],
myParams.ParameterValue[count]);
cmd.ExecuteNonQuery();
}
catch (Exception ex) {
log.Error(ex.Message);
}
myTransaction.Commit();
}
}
}
}
}
*************************************************************************
This communication, including attachments, is
for the exclusive use of addressee and may contain proprietary,
confidential and/or privileged information. If you are not the intended
recipient, any use, copying, disclosure, dissemination or distribution is
strictly prohibited. If you are not the intended recipient, please notify
the sender immediately by return e-mail, delete this communication and
destroy all copies.
*************************************************************************
Joined: 11 Jan 2006
Posts: 46
ADO.Net Transactions
Haven`t spent a lot of time with using, but if I remember correctly
the variable goes out of scope when you leave the block. Since the
Transaction you want to use gets created on with the BeginTransaction
call to the connection I`d do this...
public void setTransactionData(ref string ProcedureName, ArrayList Params) {
using (SqlConnection conn = new
SqlConnection(ConfigurationSettings.AppSettings["ConnString"])) {
SqlTransaction myTransaction = conn.BeginTransaction();
foreach (SP_Parameters myParams in Params) {
using (SqlCommand cmd = new
SqlCommand(ProcedureName, conn)) {
cmd.Transaction = myTransaction;
cmd.CommandType = CommandType.StoredProcedure;
try {
for (int count = 0; count <=
myParams.count - 1; count++)
cmd.Parameters.Add((String)myParams.ParameterName[count],myParams.ParameterValue
[count]);
cmd.ExecuteNonQuery();
}
catch (Exception ex) {
log.Error(ex.Message);
myTransaction.Rollback();
}
} //end command using
} //end for
myTransaction.Commit();
} //end connection using
}
You need to commit when everything is done and rollback on any error.
On 1/20/06, Falls, Travis D (HTSC, CASD) <travis.falls@...> wrote:
> I am trying to set up a method to do an insert of several records. I am
passing in an arraylist of SP_Parameters which is a class I have (it is below).
I am confused on how this will work in regards to the using statement. The
foreach has all the data for each insert which is being sent to a stored proc.
I want to insert them all or non hence the transaction. I don`t know where to
put the commit and rollback when using the "using" statement for the IDisposable
cleanup.
>
>
> public void setTransactionData(ref string ProcedureName, ArrayList Params) {
> using (SqlTransaction myTransaction = new SqlTransaction()) {
> using (SqlConnection conn = new
SqlConnection(ConfigurationSettings.AppSettings["ConnString"])) {
> myTransaction = conn.BeginTransaction();
> foreach (SP_Parameters myParams in Params) {
> using (SqlCommand cmd = new
SqlCommand(ProcedureName, conn)) {
> cmd.Transaction =
myTransaction;
> cmd.CommandType =
CommandType.StoredProcedure;
> try {
> for (int count = 0;
count <= myParams.count - 1; count++)
>
cmd.Parameters.Add((String) myParams.ParameterName[count],
myParams.ParameterValue[count]);
> cmd.ExecuteNonQuery();
> }
> catch (Exception ex) {
> log.Error(ex.Message);
> }
> myTransaction.Commit();
> }
> }
> }
> }
> }
>
>
> *************************************************************************
> This communication, including attachments, is
> for the exclusive use of addressee and may contain proprietary,
> confidential and/or privileged information. If you are not the intended
> recipient, any use, copying, disclosure, dissemination or distribution is
> strictly prohibited. If you are not the intended recipient, please notify
> the sender immediately by return e-mail, delete this communication and
> destroy all copies.
> *************************************************************************
>
>
>
>
> Yahoo! Groups Links
>
>
>
>
>
>
>
--
Dean Fiala
Very Practical Software, Inc
http://www.vpsw.com
Joined: 10 Jan 2006
Posts: 33
ADO.Net Transactions
Dean I was on the exact same page I was with this but I guess I didn`t
understand the rollback... basically a brain fart. I was thinking that my catch
had to be around the connection using statement to do the rollback... sorry for
the moment of stupidity. Thanks again.
Travis D. Falls | Consultant RAFT.Net IT | 860.547.4070 |
travis.falls@...
-----Original Message-----
From: AspNetAnyQuestionIsOk@yahoogroups.com
[mailto:AspNetAnyQuestionIsOk@yahoogroups.com]On Behalf Of Dean Fiala
Sent: Friday, January 20, 2006 3:27 PM
To: AspNetAnyQuestionIsOk@yahoogroups.com
Subject: Re: [AspNetAnyQuestionIsOk] ADO.Net Transactions
Haven`t spent a lot of time with using, but if I remember correctly
the variable goes out of scope when you leave the block. Since the
Transaction you want to use gets created on with the BeginTransaction
call to the connection I`d do this...
public void setTransactionData(ref string ProcedureName, ArrayList Params) {
using (SqlConnection conn = new
SqlConnection(ConfigurationSettings.AppSettings["ConnString"])) {
SqlTransaction myTransaction = conn.BeginTransaction();
foreach (SP_Parameters myParams in Params) {
using (SqlCommand cmd = new
SqlCommand(ProcedureName, conn)) {
cmd.Transaction = myTransaction;
cmd.CommandType = CommandType.StoredProcedure;
try {
for (int count = 0; count <=
myParams.count - 1; count++)
cmd.Parameters.Add((String)myParams.ParameterName[count],myParams.ParameterValue
[count]);
cmd.ExecuteNonQuery();
}
catch (Exception ex) {
log.Error(ex.Message);
myTransaction.Rollback();
}
} //end command using
} //end for
myTransaction.Commit();
} //end connection using
}
You need to commit when everything is done and rollback on any error.
On 1/20/06, Falls, Travis D (HTSC, CASD) <travis.falls@...> wrote:
> I am trying to set up a method to do an insert of several records. I am
passing in an arraylist of SP_Parameters which is a class I have (it is below).
I am confused on how this will work in regards to the using statement. The
foreach has all the data for each insert which is being sent to a stored proc.
I want to insert them all or non hence the transaction. I don`t know where to
put the commit and rollback when using the "using" statement for the IDisposable
cleanup.
>
>
> public void setTransactionData(ref string ProcedureName, ArrayList Params) {
> using (SqlTransaction myTransaction = new SqlTransaction()) {
> using (SqlConnection conn = new
SqlConnection(ConfigurationSettings.AppSettings["ConnString"])) {
> myTransaction = conn.BeginTransaction();
> foreach (SP_Parameters myParams in Params) {
> using (SqlCommand cmd = new
SqlCommand(ProcedureName, conn)) {
> cmd.Transaction =
myTransaction;
> cmd.CommandType =
CommandType.StoredProcedure;
> try {
> for (int count = 0;
count <= myParams.count - 1; count++)
>
cmd.Parameters.Add((String) myParams.ParameterName[count],
myParams.ParameterValue[count]);
> cmd.ExecuteNonQuery();
> }
> catch (Exception ex) {
> log.Error(ex.Message);
> }
> myTransaction.Commit();
> }
> }
> }
> }
> }
>
>
> *************************************************************************
> This communication, including attachments, is
> for the exclusive use of addressee and may contain proprietary,
> confidential and/or privileged information. If you are not the intended
> recipient, any use, copying, disclosure, dissemination or distribution is
> strictly prohibited. If you are not the intended recipient, please notify
> the sender immediately by return e-mail, delete this communication and
> destroy all copies.
> *************************************************************************
>
>
>
>
> Yahoo! Groups Links
>
>
>
>
>
>
>
--
Dean Fiala
Very Practical Software, Inc
http://www.vpsw.com
Yahoo! Groups Links
All times are GMT
Page 1 of 1
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
Freelace Website Designer - Customer web design and software building.
China Wholesale - Electronics Products
Character Studio - Tutorials and Help
China Wholesale - Electronics Products
Character Studio - Tutorials and Help