2.0: database application with transactions

[RAM]

Hello,
(Sorry for my English...)
I am learning .NET 2.0 (C#, ASP.NET, ADO.NET etc.).
I need to write a database application (SQL Server) consisting of a number
of database transactions (like accounting system). Each of these
transactions has enty in menu, parameters screen (.aspx page), some logic
(probably implemented in code-behind), and results screen (could be
same.aspx page). I need a good design, some ideas of experienced
programmers.
My idea is the following: I will use web.sitemap to implement menu, and
X.aspx/X.aspx.cs to implement transactions. In Default.aspx I have a menu
using sitemap data source. In X.aspx.cs I have written

protected void Page_Load(...)
{
if (!IsPostBack)
{
...
transaction = connection.BeginTransaction("...");
}
}
protected void SaveButton_Click(...)
{
...
transaction.Commit();
}

One of my problems is that I don't know where to put Rollback. It is not
enough (I think) to create CancelButton because user could press Back button
in his/her browser :-(. I can put Rollback code into Default.aspx.cs
Page_Load (to be called when new menu option is executed), but I don't like
this idea.
Could you help me please to correctly design my application? (I must use
ASP.NET!)
Thank you.
/RAM/

 

[David Browne]

Hello,
(Sorry for my English...)
I am learning .NET 2.0 (C#, ASP.NET, ADO.NET etc.).
I need to write a database application (SQL Server) consisting of a number
of database transactions (like accounting system). Each of these
transactions has enty in menu, parameters screen (.aspx page), some logic
(probably implemented in code-behind), and results screen (could be
same.aspx page). I need a good design, some ideas of experienced
programmers.
My idea is the following: I will use web.sitemap to implement menu, and
X.aspx/X.aspx.cs to implement transactions. In Default.aspx I have a menu
using sitemap data source. In X.aspx.cs I have written

protected void Page_Load(...)
{
if (!IsPostBack)
{
...
transaction = connection.BeginTransaction("...");
}
}
protected void SaveButton_Click(...)
{
...
transaction.Commit();
}

One of my problems is that I don't know where to put Rollback. It is not
enough (I think) to create CancelButton because user could press Back
button in his/her browser :-(. I can put Rollback code into
Default.aspx.cs Page_Load (to be called when new menu option is executed),
but I don't like this idea.
Could you help me please to correctly design my application? (I must use
ASP.NET!)

You should not hold database transactions open across page postbacks. If
you begin a transaction in Page_Load, you commit it or roll back before you
leave the method.

David

 

[RAM]

Thanks for answer....

You should not hold database transactions open across page postbacks. If
you begin a transaction in Page_Load, you commit it or roll back before
you leave the method.

Why? Could you explain me please. My transactions are complicated, they
require complex user's interaction, maybe sometimes
on a number of ASP.NET pages. How to rollback the transaction when the user
cancels work pressing Back returning to Default.aspx? Please help.
/RAM/

 

[Frans Bouma [C# MVP]]

Thanks for answer....

Uzytkownik "David Browne" <davidbaxterbrowne no potted
(e-mail address removed)> napisal w wiadomosci


Why? Could you explain me please. My transactions are complicated,
they require complex user's interaction, maybe sometimes
on a number of ASP.NET pages. How to rollback the transaction when
the user cancels work pressing Back returning to Default.aspx? Please
help. /RAM/

One golden rule: NEVER EVER have user interaction during a db
transaction. NEVER.

This is because a user can walk away to the coffee machine or go home
and the transaction will stall, blocking other threads.

What you should do is collect data in-memory, and when the user is
done specifying things and ready to save, you THEN start a transaction
and persist all data. This also solves it when the user cancels halfway
through a bunch of screens, you never saved anything.

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

 

[RAM]

Thanks.

What you should do is collect data in-memory, and when the user is
done specifying things and ready to save, you THEN start a transaction
and persist all data. This also solves it when the user cancels halfway
through a bunch of screens, you never saved anything.

OK.
Suppose I have a sheet of cells with some editable text data (to be
implemented using data list) and I would like to have a whole-list-changes
transaction (NOT row-level). Do you mean that I should save changes
considering modifications of rows instead of using
OnEdit/Update/Cancel/DeleteCommand attributes of DataList? (In case of
row-level changes I could write proper UPDATE/DELETE...)
Please help. Thank you very much!
/RAM/

 

[Frans Bouma [C# MVP]]

Thanks.



OK.
Suppose I have a sheet of cells with some editable text data (to be
implemented using data list) and I would like to have a
whole-list-changes transaction (NOT row-level). Do you mean that I
should save changes considering modifications of rows instead of
using OnEdit/Update/Cancel/DeleteCommand attributes of DataList? (In
case of row-level changes I could write proper UPDATE/DELETE...)
Please help. Thank you very much!
/RAM/

You should track the changes in some datastructure. I'm not sure what
datastructure you're using to store the values in when you read them
from the db, perhaps a datatable. When the user makes changes, you
track the changes in a datastructure, which you then use later on to
persist to the DB.

That's the way you should go. There's no other way, as you should
never go the route of having one big transaction for the whole screen
DURING the edit process, which can take for example a whole morning.

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

 

[RAM]

(Sorry for my English...)

You should track the changes in some datastructure. I'm not sure what
datastructure you're using to store the values in when you read them
from the db, perhaps a datatable. Yes, I use DataTable.
When the user makes changes, you
track the changes in a datastructure, which you then use later on to
persist to the DB.

I tried to use DataTable assigned to DataSource attribute. I don't
understand why I failed to obtain DataSource value in next event after
DataBind - I receive null. Could you explain me this phenomena?

Thus, I decided to use my own DataTable object and maintain its rows in
DataList commands' procedures. Additionally I 'log' SQL instructions
(UPDATE/DELETE/INSERT) in string field. I don't like this solution because
it is quite complicated (Microsoft's support for transactional applications
in .NET is not enough for me if I cannot make modifications of a list
control to be a database transaction
I would prefer to use solution with DataSource attribute if it is posiible.
Please help. Maybe it makes my code simpler if rows of DataSource are
maintained automatically (are they?).

That's the way you should go. There's no other way, as you should
never go the route of having one big transaction for the whole screen
DURING the edit process, which can take for example a whole morning.

Wise. Thanks.