How can I check existence of a stored procedure and view and function in seed method of Entity Framework?

Question

I have an ASP.NET MVC web application and Entity Framework code first. I use a stored procedure and views and functions for my report section in my project. Then I want to after updating my database these stored procedure and views and functions dos not clean and drop after updating. I use this way and I want check if not exist this object then create it but when I run this code Nuget package manger said 'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.

I don't know what is wrong in my code any tips or trick would be welcome I haven't many time to waiting for this question please send me answer immediately thanks in advance

context.Database.ExecuteSqlCommand(@"IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'stp_Report1-2')
  SET ANSI_NULLS ON
     SET QUOTED_IDENTIFIER ON

CREATE PROCEDURE [dbo].[stp_Report1-2] 
    @startDate char(10),
    @endDate char(10),
    @ContractorTitle nvarchar(100)

    AS
BEGIN
    -- setting the smallest date to filter
    if(@startDate is null) SET @startDate='1300/01/01'
    --setting the end date the current date if it has not been set
    if(@endDate is null)SET @endDate=dbo.GetShamsiDate( GetDate())
    if(@ContractorTitle is null)SET @ContractorTitle=''
    SELECT *
    from Raja.dbo.[Report1-2]
       where [contractor_title]=@ContractorTitle and export_date between @startDate and @endDate
       Order by export_date desc


END

");

and when I use this code instead of up code, I get this error in Nuget

Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch.

Code:

 context.Database.ExecuteSqlCommand(@"IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'stp_Report1-2')
  SET ANSI_NULLS ON
GO
     SET QUOTED_IDENTIFIER ON
     GO
CREATE PROCEDURE [dbo].[stp_Report1-2] 
    @startDate char(10),
    @endDate char(10),
    @ContractorTitle nvarchar(100)

    AS
BEGIN
    -- setting the smallest date to filter
    if(@startDate is null) SET @startDate='1300/01/01'
    --setting the end date the current date if it has not been set
    if(@endDate is null)SET @endDate=dbo.GetShamsiDate( GetDate())
    if(@ContractorTitle is null)SET @ContractorTitle=''
    SELECT *
    from Raja.dbo.[Report1-2]
       where [contractor_title]=@ContractorTitle and export_date between @startDate and @endDate
       Order by export_date desc


END

");

and if I use this code these error

Incorrect syntax near the keyword 'PROCEDURE'.
Must declare the scalar variable "@startDate".
Must declare the scalar variable "@startDate".
Must declare the scalar variable "@endDate".
Must declare the scalar variable "@endDate".
Must declare the scalar variable "@ContractorTitle".
Must declare the scalar variable "@ContractorTitle".
Must declare the scalar variable "@ContractorTitle".

Code:

context.Database.ExecuteSqlCommand(@"IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'stp_Report1-2')

CREATE PROCEDURE [dbo].[stp_Report1-2] 
    @startDate char(10),
    @endDate char(10),
    @ContractorTitle nvarchar(100)

    AS
BEGIN
    -- setting the smallest date to filter
    if(@startDate is null) SET @startDate='1300/01/01'
    --setting the end date the current date if it has not been set
    if(@endDate is null)SET @endDate=dbo.GetShamsiDate( GetDate())
    if(@ContractorTitle is null)SET @ContractorTitle=''
    SELECT *
    from Raja.dbo.[Report1-2]
       where [contractor_title]=@ContractorTitle and export_date between @startDate and @endDate
       Order by export_date desc
END
");

Answer 1

You can check that doing the following:

protected override void Seed(DbContext context)
{
   if (context.Database.SqlQuery<int>("SELECT COUNT(*) FROM sys.objects WHERE type = 'P' AND name = @uspName",
                                       new SqlParameter("@uspName", "your stored procedure name")).Single() == 0)
    {
        -- // On the example below I am using a resource file named StoredProcedures, which has the t-sql
        context.Database.ExecuteSqlCommand(DatabaseScripts.StoredProcedures.your stored procedure name);
    }
}

Answer 2

First execute this statemment:

SELECT COUNT(*) FROM sys.objects WHERE type = 'P' AND name = 'stp_Report1-2'

It will Return 0 or 1.

Then in Your C# code check the Return value, if it is 0, run Your create stored procedure code.

Answer 3

You can avoid having to return the result of the IF EXISTS call to C# by always doing a DROP and CREATE and you can avoid the

should be the first statement in a batch file

error by putting the sql inside an EXEC command:

Sql(EXEC('IF  EXISTS (SELECT * 
                      FROM sys.objects 
                      WHERE object_id = OBJECT_ID(N'dbo.Foos')
                      AND type in (N'P', N'PC'))
             DROP PROCEDURE dbo.Foos')

Sql(EXEC('Create PROCEDURE dbo.Foos As etc'))

The problem with the GO statement is that

it is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor

References:

Issue with Sql GO

MSDN