我刚刚创建了一个数据库并完成了第一次迁移(只是添加了一个简单的表)。现在,我想添加一些存储过程,这些存储过程是通过编写sql并在Management Studio中执行它来添加的。但是,如果可能的话,我希望在迁移中包含这些存储过程,以便保存它们,并且可以对它们运行Up或Down方法。这是可能的吗?如果可能,需要使用什么语法?或者,我只需使用Management Studio添加/编辑/删除它们?
发布于 2013-05-16 21:44:28
我是这样做的..。
在当前的迁移类中-
public partial class MyMigration : DbMigration
{
public override void Up()
{
... other table creation logic
// This command executes the SQL you have written
// to create the stored procedures
Sql(InstallScript);
// or, to alter stored procedures
Sql(AlterScript);
}
public override void Down()
{
... other table removal logic
// This command executes the SQL you have written
// to drop the stored procedures
Sql(UninstallScript);
// or, to rollback stored procedures
Sql(RollbackScript);
}
private const string InstallScript = @"
CREATE PROCEDURE [dbo].[MyProcedure]
... SP logic here ...
";
private const string UninstallScript = @"
DROP PROCEDURE [dbo].[MyProcedure];
";
// or for alters
private const string AlterScript = @"
ALTER PROCEDURE [dbo].[AnotherProcedure]
... Newer SP logic here ...
";
private const string RollbackScript = @"
ALTER PROCEDURE [dbo].[AnotherProcedure]
... Previous / Old SP logic here ...
";
}
发布于 2018-10-06 21:23:34
namespace QuickProject.Migrations
{
using System;
using System.Data.Entity.Migrations;
public partial class CreateStoredProcedure_GellAllAgents : DbMigration
{
public override void Up()
{
CreateStoredProcedure("dbo.GellAllAgents", c => new
{
DisplayLength = c.Int(10),
DisplayStart = c.Int(0),
UserName = c.String(maxLength: 255, defaultValueSql: "NULL"),
FullName = c.String(maxLength: 255, defaultValueSql: "NULL"),
PhoneNumber = c.String(maxLength: 255, defaultValueSql: "NULL"),
LocationDescription = c.String(maxLength: 255, defaultValueSql: "NULL"),
AgentStatusId = c.Int(defaultValueSql: "NULL"),
AgentTypeId = c.Int(defaultValueSql: "NULL")
}, StoredProcedureBody);
}
public override void Down()
{
DropStoredProcedure("dbo.GellAllAgents");
}
private const string StoredProcedureBody = @"
Declare @FirstRec int, @LastRec int
Set @FirstRec = @DisplayStart;
Set @LastRec = @DisplayStart + @DisplayLength;
With CTE_AspNetUsers as
(
Select ROW_NUMBER() over (order by AspNetUsers.Id) as RowNum,
COUNT(*) over() as TotalCount, AspNetUsers.Id, AspNetUsers.FullName, AspNetUsers.UserName, AspNetUsers.PhoneNumber, Locations.Desciption as LocationDescription, Cities.Name as LocationCity, AgentStatus.Name as AgentStatusName, AgentTypes.Name as AgentTypeName
from AspNetUsers
join Locations on AspNetUsers.LocationId = Locations.id
join Cities on Locations.CityId = Cities.Id
join AgentStatus on AspNetUsers.AgentStatusId = AgentStatus.Id
join AgentTypes on AspNetUsers.AgentTypeId = AgentTypes.Id
where (Discriminator = 'Agent'
and (@UserName is null or UserName like '%' + @UserName + '%')
and (@FullName is null or FullName like '%' + @FullName + '%')
and (@PhoneNumber is null or PhoneNumber like '%' + @PhoneNumber + '%')
and (@LocationDescription is null or @LocationDescription like '%' + (select Cities.Name from Cities where Locations.CityId = Cities.Id) + '%' or @LocationDescription like '%' + Desciption + '%')
and (@AgentStatusId is null or AgentStatusId = @AgentStatusId)
and (@AgentTypeId is null or AgentTypeId = @AgentTypeId)
)
group by AspNetUsers.Id, AspNetUsers.FullName,AspNetUsers.UserName, AspNetUsers.PhoneNumber, Locations.Desciption, Cities.Name, AgentStatus.Name, AgentTypes.Name
)
Select *
from CTE_AspNetUsers
where RowNum > @FirstRec and RowNum <= @LastRec
";
}
}
结果,当您在SQL server中查看/修改SP时,这就是它显示"ALTER PROCEDURE“的原因
https://stackoverflow.com/questions/14139445
复制相似问题