update database through SQL script

microsoft.public.dotnet.languages.vb

9 Nov 2006 3:22 AM

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

9 Nov 2006 4:41 AM

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

Show quoteHide quote

"Leanne" <leann***@connectretail.com.au> schreef in bericht
news:1163042560.296016.144580@f16g2000cwb.googlegroups.com...
>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
>

9 Nov 2006 5:45 AM

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] wrote:
Show quoteHide quote

> 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" <leann***@connectretail.com.au> schreef in bericht
> news:1163042560.296016.144580@f16g2000cwb.googlegroups.com...
> >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
> >

9 Nov 2006 6:04 AM

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

Show quoteHide quote

"Leanne" <leann***@connectretail.com.au> schreef in bericht
news:1163051102.106368.43250@h48g2000cwc.googlegroups.com...
> 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] wrote:
>> 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" <leann***@connectretail.com.au> schreef in bericht
>> news:1163042560.296016.144580@f16g2000cwb.googlegroups.com...
>> >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
>> >
>

9 Nov 2006 6:30 AM

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] wrote:
Show quoteHide quote

> 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" <leann***@connectretail.com.au> schreef in bericht
> news:1163051102.106368.43250@h48g2000cwc.googlegroups.com...
> > 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] wrote:
> >> 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" <leann***@connectretail.com.au> schreef in bericht
> >> news:1163042560.296016.144580@f16g2000cwb.googlegroups.com...
> >> >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
> >> >
> >

10 Nov 2006 5:53 AM

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

Show quoteHide quote

"Leanne" <leann***@connectretail.com.au> schreef in bericht
news:1163053854.124188.261540@i42g2000cwa.googlegroups.com...
> 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] wrote:
>> 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" <leann***@connectretail.com.au> schreef in bericht
>> news:1163051102.106368.43250@h48g2000cwc.googlegroups.com...
>> > 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] wrote:
>> >> 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" <leann***@connectretail.com.au> schreef in bericht
>> >> news:1163042560.296016.144580@f16g2000cwb.googlegroups.com...
>> >> >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
>> >> >
>> >
>