How to run multiple SQL Scripts from VB.net (Application_Start method in Global.asax)

[gamesforums]

Hi Everyone!

I work in a company that has developed several VB.Net Web
applications. Currently we use SourceGear's Vault for source control
and versioning for the application files. One area that have been left
behind a little bit is the version control on database level. Our
typical installation of an application is like this:

1. Get latest application file build from FinalBuilder.
2. Install the build output.
3. Run SQL scripts:
3.1 Manually run BuildScripts from \\application folder\BuildScripts\
that creates database, storeproc, tables etc.
3.2 Manually run ChangeScripts from \\application folder\ChangesScripts
\, this is multiple scripts and any changes on the database level is
added in a new script with a number in the script indicating versionnr
(ChangeScr_001.sql - ChangeScr_087.sql) Currently 087.sql is the
latest.

What we are trying to do right now is to get rid of the manual steps
in 3.1 and 3.2 by building these into the application startup. As
mentioned in the topic i need to run the BuildScripts (if database is
not installed) and then the ChangeScripts the first time the
application is started. I'm told to do this from the Application_Start
method in Global.asax.

What's the easiest way for me to execute all the BuildScripts and
ChangeScripts ? Is it possible to read all scripts into a streamreader
and then send the stream to command.ExcecuteNonQuery.
One other thing is that I want this to be in a transaction, so that if
it fails it will be rolled back.

Any tips or codesnippets are greatly appreciated!

Technology:
VS2005 - VB.NET
SQL2000 and SQL 2005.

Regards
Mcad

 

[William Vaughn]

One of the examples I include with my book is a class that replicates some
of the functionality of SQLCMD--the part that executes scripts. It's not
that hard to write, but no, you can't just write a stream to SQL Server and
expect it to execute the batch.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)