Running .sql file using C#

Hi,

I'm trying to run .sql file with C# code, but i have systematically an error
message with the "GO" instruction. When i test the script in SQL Server
Management Studio, it work fine !!!


First part of the error message
----------------------------------------
{System.Data.SqlClient.SqlException: Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.
at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception,
Boolean breakConnection)
etc...

SQL file to be played (SET NOEXEC ON is needed !!!)
--------------------------------------------------------------------------------
SET NOEXEC ON
GO
DROP PROCEDURE spr_gr_test
GO
CREATE PROCEDURE spr_gr_test
as
begin
PRINT 'titi'
end
GO


C# code
----------------------------------------
SqlCommand __cmd = myConnection.CreateCommand();
StreamReader __streamReader = null;
string __myQuery = "";
int __rc = 0;

try
{
__streamReader = new
StreamReader(@"C:\Data\Dvlp\SQL.2005\CheckScript\S QLQuery1.sql");

__myQuery = __streamReader.ReadToEnd();
__cmd.CommandType = CommandType.Text;
__cmd.CommandText = __myQuery;
__rc = __cmd.ExecuteNonQuery();
Debug.WriteLine(__rc.ToString());
}
catch (Exception exp)
{
Debug.WriteLine(exp.ToString());
}
finally
{
if (__streamReader != null)
__streamReader.Close();
}




Where is the solution ?
Where is the mistake .....

Thank's by advance

Gislain

re: Running .sql file using C#

The SQL server does not know what "GO" is. Query analyzer uses the "GO" to
separate the statements but does not send them.

I normally do something like


string[] cmds = command.Replace("GO", "~").split(new Char[] {'~'} );
foreach(string cmd in cmds)
/// exec cmd...

Regards,
John


"Gislain" <nospam@nospam.comwrote in message
news:ecgf2VypGHA.2292@TK2MSFTNGP05.phx.gbl...

re: Running .sql file using C#

Hi,

Do you know if the proc exist before running the script?
If not you better do a IF exist ... before the DROP
other than that it seems ok the script


--
--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

"Gislain" <nospam@nospam.comwrote in message
news:ecgf2VypGHA.2292@TK2MSFTNGP05.phx.gbl...

re: Running .sql file using C#

This is really dangerous, as you end up replacing all of the instances
of "go" in the string, which isn't what you want. You might have table
names with the letters "go" in them and the queries will fail as a result.

My recommendation to the OP is to make sure that your GO statements are
on lines by themselves. Then, read the file line for line. If you detect a
"go" all by itself, then you can execute the last batch of commands that you
have.

--
- Nicholas Paldino [.NET/C# MVP]
- mvp@spam.guard.caspershouse.com

"John J. Hughes II" <no@invalid.comwrote in message
news:OMEXDv0pGHA.1548@TK2MSFTNGP04.phx.gbl...

re: Running .sql file using C#

Thanks for the insight, sound like a good idea :)

Regards,
John

"Nicholas Paldino [.NET/C# MVP]" <mvp@spam.guard.caspershouse.comwrote in
message news:OzQdK51pGHA.4996@TK2MSFTNGP05.phx.gbl...