Solved

Explanation of SQL Rollover in Visual Basic .NET 2005

Posted on 2011-03-02
19
417 Views
Last Modified: 2012-05-11
We were told that VB2005 has line statement that we can use for controlling or validating all the SQL scripts that within the application prior sending the command to the SQL server.

Can an EE gives us an explanation of how this works, benefits and recommendation?
0
Comment
Question by:rayluvs
  • 8
  • 5
  • 4
  • +1
19 Comments
 
LVL 3

Expert Comment

by:MTroutwine
ID: 35016740
0
 
LVL 41

Expert Comment

by:graye
ID: 35017820
No, this is not a feature of Visual Studio...

However, there are a few on-line SQL validators out there...  You might be able to programmatically send the SQL to an external web service to be validated.

http://developer.mimer.com/validator/
0
 

Author Comment

by:rayluvs
ID: 35017829
I don't think thats it.   We were told that it's an object created in VB and used to do a rollback of SQL statement if any of the queries was not successful.

I just noticed i misplace the word, it not "rollover" I meant ROLLBACK.
0
 
LVL 41

Accepted Solution

by:
graye earned 300 total points
ID: 35018648
Well, there is a way to perform "transactions" in a VB.Net application.  It is designed to make sure that multiple SQL statements either a) all work, or b) all fail.    To make this work, there is a "Rollback" function that you can use if an error occurs to return the database to a state just before you started the update.

There is an example towards the bottom of this article.   Let's see if that what you're talking about
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqltransaction.aspx
0
 

Author Comment

by:rayluvs
ID: 35019282
Yes, that seems to be it.  Though it more on the technical side, which is ok, bit can you give us an explanation on the concept?

To our understanding is that the rollback is activated if there is a problem any script that is about to be sent to SQL server.  So the DB is protected if any crash or LAN problem occurs.  But what happens when the application needs to be online in a series of application, how does that works?  Does it lock one user while the other user updates the data?

So if you can give us a brief explanation, it would be great.
0
 
LVL 41

Assisted Solution

by:graye
graye earned 300 total points
ID: 35019849
... well it's not "about to be sent"

Here is an example.... suppose you want to transfer money from one bank account to another.  This would envolve two separate SQL actions (one for the "withdrawl", and one for the "deposit").  As you can imagine, you'd want to make sure that both actions were sucessful.  Otherwise, it'd possible that the withdrawl of funds might suceed, but the deposit would fail... and you'd be out the money.

So, the created a way to "wrap" multiple SQL actions in a single transaction.  That way if something goes wrong half way through the process, you can "rewind" things back to the way they were before you started.

so... it's not a validation "before things are sent"... it's a validation *after* things are sent.  If all of the individual SQL actions were sucessful, then they are all "committed" to the database.  If not, then they are all "rolled back".

That example is pretty cool... it shows two SQL actions, and if nothing goes wrong (no exceptions are thrown), then it performs a Commit to make the actions permanent.   If something does go wrong, then it performs a RollBack to undo the actions up to that point.
0
 

Author Comment

by:rayluvs
ID: 35021584
I like the way you put it, 'a "wrap" multiple SQL actions in a single transaction. '.

I think I understand it:

   When an application that registers a sales invoice and the transaction has to update 4 tables,
   all tables will be updated only after it checks that all SQL actions that consists of this transaction
   were successful.

Would this be a correct assumption?
0
 
LVL 41

Expert Comment

by:graye
ID: 35022495
Yep, that's it.
0
 

Author Comment

by:rayluvs
ID: 35022708
Two last questions:


1. When the application checks that all SQL actions are OK, during this "cehcking", are these
    statements passed to the LOG database of SQL or is done in the PC's memory?

2. If the question #1 is true, during this process "checking", is the SQL server locked to all users
    except the current user running the apps?
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 22

Assisted Solution

by:8080_Diver
8080_Diver earned 200 total points
ID: 35063064
1. When the application checks that all SQL actions are OK, during this "cehcking", are these
    statements passed to the LOG database of SQL or is done in the PC's memory?

I believe that the steps are actually logged to the transaction log file and that is where the information is pulled from in order to back out the transaction.

2. If the question #1 is true, during this process "checking", is the SQL server locked to all users
    except the current user running the apps?

SQL Server, as a whole, is not locked; however, the rows and pieces of the indexes (and, possibly pages of rows and index rows) are locked until the transaction is either committed or rolled back.  If you think about it, this is as it must be because, if the rows were not locked, that would allow someone else to modify the row and then how would you be able to roll the transaction back?

It is because of the locking issue that it is highly advisable to minimize the time involved with any given transaction and also the number of rows involved.
0
 
LVL 41

Assisted Solution

by:graye
graye earned 300 total points
ID: 35063463
We're getting into symantics now...   Yes, it appears "as if" the statements are fully executed, when in reality the are only "partially" executed.  The database "commit" function completes the partially executed statements.

However, the inner workings of the database engine actually determine how that actually works.  Database vendors are free to implement this as a "in-memory" operation and just hold the writing of the database "page".... or they could implement this feature as an "undo" (where the action is reversed out).  I would suspect a "delayed writing of the page" would be the most common implementation.

For example, for SQL Server, if an insert fails during a transaction and there is an autonumber field... then your table will end up with a "missing" autonumber value for the row that "was partially committed".

As 8080_Diver has pointed out, SQL transaction imply the use of locks... they wouldn't work without them.  The "granularity" of the lock is once again a factor that's left up to the database vendor.  The most common type of lock for an insert would be a table (or region) lock.  Whereas update and deletes would typically just do a record lock.
0
 

Author Comment

by:rayluvs
ID: 35069423
Great info, Thanx!

However, graye comments on "Database vendors are free to implement this as a 'in-memory'", makes me ask:

The SQL-VB statements for Commit/Rollback referred on ID: 35018648, are they performed in memory or on the SQL server?
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35070525
Ramante,

Personally, I know of no major RDBMS vendor that impliments the transaction as an "in-memory" activity.  If you are processing a lot of data within the transaction (e.g. multiple tables and many rows per table) then you would chew up a lot of memory.  Now, suppose that multiple transactions are in process at the same time, that could chew up a whole lot of memory in a hurry.

0
 

Author Comment

by:rayluvs
ID: 35070571
Understood.  So its safe to say the SQL-VB statements for Commit/Rollback referred on ID: 35018648, are executed in the SQL server?
0
 
LVL 22

Assisted Solution

by:8080_Diver
8080_Diver earned 200 total points
ID: 35070615
Ramante,

The initiation of the transaction, the details of the transaction, and either the COMMIT or ROLLBACK, by definition, have to be executed within the SQL Server database engine because, otherwise, there would be nothing to prevent multiple transactions updating or even deleting a given row.
0
 

Author Comment

by:rayluvs
ID: 35072360
Thanx
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35072429
Your welcome.
0
 

Author Closing Comment

by:rayluvs
ID: 35077032
Thanx
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now