如何在 C# 中创建存储过程，然后*保存*到 SQL Server? [英] How to create stored procedure in C#, then *save* it to SQL Server?

问题描述

我一直在用头撞墙一段时间.我知道你可以用 C# 代码创建一个存储过程，这点没问题.但我想不通的是，我可以保存那个存储过程，到 SQL Server - 但通过 C# 代码完成吗?

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?

场景是:我想通过代码创建一个测试数据库，然后我想在新创建的测试数据库(服务器端)中添加一个存储过程——同样，都是通过同一个C#代码项目完成的.

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");  
}  
}  

没有实体框架

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

创建存储通过带有实体框架的 C# 程序

在没有实体框架的情况下通过 C# 创建存储过程

这篇关于如何在 C# 中创建存储过程，然后*保存*到 SQL Server?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！