blocks|key|407066|text|我是这样做的..。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|407067|在当前的迁移类中-|407068|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+...
++++";
}|code-block|syntax|javascript|407069|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

I've done this like so...

In the current migration class -

<pre><code>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 ...
 ";
}
</code></pre>

blocks|key|407130|text|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+%2B+@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+'%25'+%2B+@UserName+%2B+'%25')
+++++++++and+(@FullName+is+null+or+FullName+like+'%25'+%2B+@FullName+%2B+'%25')
+++++++++and+(@PhoneNumber+is+null+or+PhoneNumber+like+'%25'+%2B+@PhoneNumber+%2B+'%25')
+++++++++and+(@LocationDescription+is+null+or++@LocationDescription+like+'%25'+%2B+(select+Cities.Name+from+Cities+where+Locations.CityId+=+Cities.Id)+%2B+'%25'+or++@LocationDescription+like+'%25'+%2B+Desciption+%2B+'%25')
+++++++++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
";
}
}|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|407131|结果，当您在SQL+server中查看/修改SP时，这就是它显示"ALTER+PROCEDURE“的原因|unstyled|407132|​|407133|📷|atomic|offset|length|407134|407135|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/be890cbbc1f6d74954e6be49d230d9a0.png|imageAlt^0|0|0|0|0|1|0|0|0^^$0|@$1|2|3|4|5|6|7|X|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|Y|8|@]|9|@]|A|$]]|$1|G|3|H|5|F|7|Z|8|@]|9|@]|A|$]]|$1|I|3|J|5|K|7|10|8|@]|9|@$L|11|M|12|1|13]]|A|$]]|$1|N|3|H|5|F|7|14|8|@]|9|@]|A|$]]|$1|O|3|-4|5|F|7|15|8|@]|9|@]|A|$]]]|P|$Q|$5|R|S|T|A|$U|V|W|-4]]]]

<pre><code>namespace QuickProject.Migrations
{
 using System;
 using System.Data.Entity.Migrations;


public partial class CreateStoredProcedure_GellAllAgents : DbMigration
{
 public override void Up()
 {
 CreateStoredProcedure("dbo.GellAllAgents", c =&gt; 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 &gt; @FirstRec and RowNum &lt;= @LastRec
";
}
}
</code></pre>

Result, When you view/modify the SP in SQL server, that's why it shows "ALTER PROCEDURE"

<a href="https://i.stack.imgur.com/y60II.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/y60II.png" alt="enter image description here"></a>

I have just created a database and done my first migration (just a simple table add). Now I want to add some stored procedures which I have just added by writing the sql and executing it in Management Studio. But I would like to include these stored procedures if possible in a migration so that they are saved and I can run an Up or Down method against them. Is this possible and if so what syntax needs to be used? Or will I just have to add/edit/remove them using Management Studio?

Code First Migrations and Stored Procedures

数据库

我刚刚创建了一个数据库并完成了第一次迁移(只是添加了一个简单的表)。现在，我想添加一些存储过程，这些存储过程是通过编写sql并在Management Studio中执行它来添加的。但是，如果可能的话，我希望在迁移中包含这些存储过程，以便保存它们，并且可以对它们运行Up或Down方法。这是可能的吗?如果可能，需要使用什么...

问代码优先迁移和存储过程
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问代码优先迁移和存储过程EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问代码优先迁移和存储过程
EN