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.
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();
}
}
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#)
