CODEWITHSUNDEEP
c#sqlsql-serverstored-procedures
ajonno
ajonno

Reputation: 2240

How to create stored procedure in C#, then *save* it to SQL Server?

I've been banging my head against a wall for a little while on this one. I know you can create a stored procedure in C# code, that bit's OK. But what I can't figure out is can I then save that stored procedure, onto the SQL Server - but done via C# code?

Scenario is: I want to create a test database via code, then I want to add a stored procedure to the newly created test database (server side) - again, all done via the same C# code project.

Upvotes: 6

Views: 26512

Answers (2)

Anik Saha
Anik Saha

Reputation: 4494

Yes it is possibe. Here I give you a sample example.

public partial class InitialEntities : DbMigration  
{  
public override void Up()  
{  
    CreateStoredProcedure(  
        "dbo.InsertEmployee",  
         p => new  
        {  
            Code = p.String(),  
            Name = p.String(),  
            DepartmentId = p.Int(),  
        },  
        body:  
            @"INSERT [dbo].[EmployeeMasters]([Code], [Name], [DepartmentId])  
        VALUES (@Code, @Name, @DepartmentId)  

        DECLARE @EmployeeId int  
        SELECT @EmployeeId = [EmployeeId]  
        FROM [dbo].[EmployeeMasters]  
        WHERE @@ROWCOUNT > 0 AND [EmployeeId] = scope_identity()  

        SELECT t0.[EmployeeId]  
        FROM [dbo].[EmployeeMasters] AS t0  
        WHERE @@ROWCOUNT > 0 AND t0.[EmployeeId] = @EmployeeId"  
    );  

    CreateStoredProcedure(  
        "dbo.UpdateEmployee",  
        p => new  
        {  
            EmployeeId = p.Int(),  
            Code = p.String(),  
            Name = p.String(),  
            DepartmentId = p.Int(),  
        },  
        body:  
            @"UPDATE [dbo].[EmployeeMasters]  
        SET [Code] = @Code, [Name] = @Name, [DepartmentId] = @DepartmentId  
        WHERE ([EmployeeId] = @EmployeeId)"  
    );  

    CreateStoredProcedure(  
        "dbo.DeleteEmployee",  
        p => new  
        {  
            EmployeeId = p.Int(),  
        },  
        body:  
            @"DELETE [dbo].[EmployeeMasters]  
        WHERE ([EmployeeId] = @EmployeeId)"  
    );  

}  

public override void Down()  
{  
    DropStoredProcedure("dbo.DeleteEmployee");  
    DropStoredProcedure("dbo.UpdateEmployee");  
    DropStoredProcedure("dbo.InsertEmployee");  
}  
}

Without entity framework 

 StringBuilder sbSP = new StringBuilder();

 sbSP.AppendLine("CREATE PROCEDURE [spInsertADAuthorization] @AD_Account varchar(255),@AD_SID varchar(255),@AD_EmailAddress varchar(255),@DateImported datetime,@Active bit AS BEGIN SET NOCOUNT ON; INSERT INTO AD_Authorization (AD_Account, AD_SID, AD_EmailAddress, DateImported, Active) VALUES (@AD_Account,@AD_SID,@AD_EmailAddress,@DateImported,@Active) END");
using (SqlConnection connection = new SqlConnection(ConnectionString))
{

 using (SqlCommand cmd = new SqlCommand(sbSP.ToString(), connection))
                        {
                            connection.Open();
                            cmd.CommandType = CommandType.Text;
                            cmd.ExecuteNonQuery();
                            connection.Close();
                        }
                    }

Creating a stored procedure via C# with entityframework

Creating a stored procedure via C# without entityframework

Upvotes: 8

Gustavo Rubio
Gustavo Rubio

Reputation: 10807

Yes. You use the usual CREATE PROCEDURE text within your string then execute it through a SqlCommand given that the user of course has permissions to store procedures. Read this other answer as it has some details regarding GO and USE which is often used in the creation of sprocs (and will give you trouble within C#)

Upvotes: 0

Related Questions