Running .sql file using C#

G

Gislain

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(SqlException 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\SQLQuery1.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
 
J

John J. Hughes II

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
 
I

Ignacio Machin \( .NET/ C# MVP \)

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
 
N

Nicholas Paldino [.NET/C# MVP]

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]
- (e-mail address removed)

John J. Hughes II said:
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 said:
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(SqlException 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\SQLQuery1.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
Click to expand...
Click to expand...
 
J

John J. Hughes II

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

Regards,
John

Nicholas Paldino said:
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]
- (e-mail address removed)

John J. Hughes II said:
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 said:
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(SqlException 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\SQLQuery1.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
Click to expand...
Click to expand...
Click to expand...
 
Top