Execute File SQL in AdoNET VB

  • Thread starter Thread starter John Fred
  • Start date Start date
J

John Fred

Hello,

I have a file .SQL Containing a Script to Create Tables and Stored
Procedure...

Example :
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[SP_ADVANCED_SEARCH]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[SP_ADVANCED_SEARCH]
GO
........

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE TABLE [dbo].[TBLTEST]

Etc Etc....

It is possible Execute Then Scirpt With AdoNet ...
 
Yes and no. We use this as an exercise in my programming classes. Basically,
an SQL file is a series of SQL batches that must be run serially--one at a
time and submitted as such. This means you need to execute each batch
individually (leaving off the "GO"). To do so using ADO, parse the file and
pick off each batch (denoted by the "GO"), set the CommandText to the batch
and execute the batch. If you're expecting rows back from the batch, you'll
need to deal with the returning rowsets otherwise you'll want to catch the
RowsAffected value.

Another alternative would be to shell out to OSQL or somesuch.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
I have done this once, the hard way, you have to run each command as an
ExecuteNonQuery() , after setting the SqlCommand object's CommandText to the
script. Please note that you may end up with problems, "GO"will not be
recognized. Some statements need to be the first line (Create Procedure
etc.)
Best way to do this is, load the script in a string, split it with "GO", and
run commands in a loop for each string in the resulting string array.

The other way is to use osql. I have had bad experiences.
 
Back
Top