Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 480
  • Last Modified:

Explanation of SQL Rollover in Visual Basic .NET 2005

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
rayluvs
Asked:
rayluvs
  • 8
  • 5
  • 4
  • +1
5 Solutions
 
MTroutwineCommented:
0
 
grayeCommented:
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
 
rayluvsAuthor Commented:
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
grayeCommented:
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
 
rayluvsAuthor Commented:
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
 
grayeCommented:
... 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
 
rayluvsAuthor Commented:
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
 
grayeCommented:
Yep, that's it.
0
 
rayluvsAuthor Commented:
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
 
8080_DiverCommented:
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
 
grayeCommented:
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
 
rayluvsAuthor Commented:
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
 
8080_DiverCommented:
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
 
rayluvsAuthor Commented:
Understood.  So its safe to say the SQL-VB statements for Commit/Rollback referred on ID: 35018648, are executed in the SQL server?
0
 
8080_DiverCommented:
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
 
rayluvsAuthor Commented:
Thanx
0
 
8080_DiverCommented:
Your welcome.
0
 
rayluvsAuthor Commented:
Thanx
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 8
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now