how to get the return value from server.ConnectionContext.ExecuteNonQuery(fileData);

See more:

C#

SQL-Server

C#4.0

Hello all,

I m trying to execute the .sql file sequentially for creating the table, sp and functions for the database from c# application.

I have 3 .sql file
1. 01_Table.sql (Contains more then 10 tables)
2. 02_Sp.sql (Contains more then 25 Sp)
3. 03_Function.sql (Contains 2 functions)

I have created a method which execute the .sql one-by-one and creates the tables and sp. and its working fine.
But my issue is when i try to execute the code it return a negative value,from which i m not able to decide weather the query is executed successfully or not.
as it returns the -ve value, but on the background side it successfully execute the query without any error.
(For Table it returns -459)
(For Sp it returns -847)
(For Table it returns -2)
In this case i have to implement if any of the query is fail to execute then it should return false and stop to execute another .sql files
Beside this i also want to execute the insert query for the table for entering the default values in the tables
My method is...

public static bool ExecuteScriptsFile(string databasedb, FileInfo scriptFile)
{
   int result;
   try
   {
        _con = new SqlConnection();
        _con.ConnectionString = databasedb;

        string fileData = scriptFile.OpenText().ReadToEnd();

        using (_con)
        {
            Server server = new Server(new ServerConnection(_con));
            result = server.ConnectionContext.ExecuteNonQuery(fileData);
            // Here in the result i m getting the negative value even the query is executed
            // successfully,due to this i m not able to know whether it is executed
            // successfully or not.

        }
        scriptFile.OpenText().Close();
        return (result > 0) ? true : false;
   }
   catch (Exception ex)
   {
       return false;
   }
}

If any one had the solution for the above scenario then please suggest me.
Thank you

UPDATE:

Hello Shahin,
Thanks for replying
The below query i m executing for the creating the table returns -459
(01_Table.sql)
Note: As i mansion that table script contains more then 10 table, but its a company data so cant share that,
but i created the dummy table script same as the original one.
and it returns -13
The script is given below

----------------------------------------------------------------
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_User_address]') AND parent_object_id = OBJECT_ID(N'[dbo].[User]'))
ALTER TABLE [dbo].[User] DROP CONSTRAINT [FK_User_address]
GO
/****** Object:  Table [dbo].[User]    Script Date: 03/06/2012 09:39:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[User]') AND type in (N'U'))
DROP TABLE [dbo].[User]
GO
/****** Object:  Table [dbo].[address]    Script Date: 03/06/2012 09:39:13 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[address]') AND type in (N'U'))
DROP TABLE [dbo].[address]
GO
/****** Object:  Table [dbo].[User]    Script Date: 03/06/2012 09:39:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[User](
	[autUserId] [int] IDENTITY(1,1) NOT NULL,
	[vchName] [varchar](50) NULL,
	[intAddressId] [int] NULL,
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
	[autUserId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[address]    Script Date: 03/06/2012 09:39:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[address](
	[autAddressId] [int] IDENTITY(1,1) NOT NULL,
	[vchAddress1] [nvarchar](256) NULL,
	[vchAddress2] [nvarchar](256) NULL,
	[vchCity] [nvarchar](50) NULL,
 CONSTRAINT [PK_address] PRIMARY KEY CLUSTERED 
(
	[autAddressId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[User]  WITH CHECK ADD  CONSTRAINT [FK_User_address] FOREIGN KEY([intAddressId])
REFERENCES [dbo].[address] ([autAddressId])
GO
ALTER TABLE [dbo].[User] CHECK CONSTRAINT [FK_User_address]
GO
--------------------------------------------------------

waiting for replay...

[Nithin - Moved question updates from Solution to question]