update database through SQL script

[Leanne]

I am doing customization for Microsoft Pos. I am working on
installation package now. I added some new tables and stored
procuedures and generated SQL Script for that. During the installation
process, after user entered the server name and database name, how to
open the script file (*.sql) and run that to update database? So they
don't have to open Query Analizer to run it. The idea is to get
everything done during the installation process. Anyone got an idea?

Thanks in advance.

Leanne

 

[Cor Ligthert [MVP]]

Leanne,

You can run every script using the SQLCommand.executenonquery, therefore as
well Create and Alter statements.

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx

I hope this helps,

Cor

 

[Leanne]

Cor,
Thank you for your reply. My problem is, the generated SQL script
contains a lot " if exist...then drop...Go..." etc, so when I use

ls=My.Computer.FileSystem.ReadAllText(Dialog1.FileName)
sqlCommand.CommandText = ls
sqlCommand.ExecuteNonQuery()


I got many syntex error. (such as "Go" should be the last). I set the
command type to "text". I don't know what method I should use to read
string from the file and how should I tell sqlCommand that this is SQL
Script and not normal SQL Statement.

Thanks again
Leanne

 

[Cor Ligthert [MVP]]

Leanne,

Did you look at the code in the Query analyzer from the SQL management tool,
if you start with a new one, than that starts with statements to let it
execute to be imported in the program libraries of SQL server.

Cor

 

[Leanne]

Cor,

If I start Query analyzer, then open the sql file, I can run that
without any problem.
Now what I want is to open the sql file from VB, and then execute that
throuth sqlCommand. Can you tell me how to do that? Some code sample
maybe?

Thanks
Leanne

 

[Cor Ligthert [MVP]]

Not tested,

cmd.CommandText = _
"CREATE PROCEDURE dbo.whatever AS & vbCrLf & _
SELECT * FROM MyTable"
cmd.ExecuteNonQuery()

If it is already in your database than CREATE is ALTER

I hope this helps,

Cor