Run SQL scripts from VB.NET

[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!


Regards
Mcad

 

[Guest]

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!

Regards
Mcad

http://www.mattberther.com/?p=619
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=719811&SiteID=1

 

[Cowboy \(Gregory A. Beamer\)]

You can open a text file with SQL statements and run them. You can also set
up an installer that you can automate that runs the build and change
scripts. Another option is setting up the create and change scripts as
stored procedures and running using the database name, etc. The last one
will not work if this is an install you are not controlling.

Another thing you might look into, if this is an Enterprise build, is use a
tool like Cruise Control .NET (continuous integration). With CC .NET, your
project is built every time you check in source code. It gives you the
ability, using nAnt, to set up build tasks that run automatically. And, it
can be configured against vault. This is, of course, for your developer test
area and not to automatically be run against production, but you can use the
same tasks that you have used on your development machine.

If you have Team Foundation Server, you also have the option of using the
Build Server. If not, MSBuild can be used to automate a great variety of
tasks and build an application.

 

[Jay Pondy]

Check out http://www.red-gate.com/

I have used their products a number of times and have no reservations
recommending them.