CSharp: donet Stored procedure mapping with Entity Framework 6

sql:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].DepartmentMaster') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE DepartmentMaster
GO
create table DepartmentMaster
(
    DepartmentId INT IDENTITY(1,1) PRIMARY KEY,
    Code nvarchar(50),
    [Name] nvarchar(50)
)
go
 
insert into DepartmentMaster(Code,[Name]) values('0001','IT')
go
 
 
select * from DepartmentMaster
 
IF EXISTS (select * from sysobjects where id = object_id(N'[dbo].EmployeeMasters') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE EmployeeMasters
GO
create table EmployeeMasters
(
    EmployeeId INT IDENTITY(1,1) PRIMARY KEY,
    Code nvarchar(50),
    [Name] nvarchar(50),
    [DepartmentId] int
         FOREIGN KEY REFERENCES DepartmentMaster(DepartmentId)
)
go
 
select * from EmployeeMasters
go
 
 
CREATE PROCEDURE [dbo].[InsertEmployee] 
    @Code [nvarchar](max), 
    @Name [nvarchar](max), 
    @DepartmentId [int] 
AS 
BEGIN 
    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 
END 
   
GO 
 
 
CREATE PROCEDURE [dbo].[EmployeeMaster_Insert] 
    @Code [nvarchar](max), 
    @Name [nvarchar](max), 
    @DepartmentId [int] 
AS 
BEGIN 
    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 
END 
   
GO 
   
CREATE PROCEDURE [dbo].[UpdateEmployee] 
    @EmployeeId [int], 
    @Code [nvarchar](max), 
    @Name [nvarchar](max), 
    @DepartmentId [int] 
AS 
BEGIN 
    UPDATE [dbo].[EmployeeMasters] 
    SET [Code] = @Code, [Name] = @Name, [DepartmentId] = @DepartmentId 
    WHERE ([EmployeeId] = @EmployeeId) 
END 
   
GO 
   
CREATE PROCEDURE [dbo].[DeleteEmployee] 
    @EmployeeId [int] 
AS 
BEGIN 
    DELETE [dbo].[EmployeeMasters] 
    WHERE ([EmployeeId] = @EmployeeId) 
END 

　　

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
  /// <summary>
    /// geovindu,Geovin Du
    /// </summary>
    public class EmployeeMaster
    {
 
        /// <summary>
        ///
        /// </summary>
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int EmployeeId { get; set; }
 
        /// <summary>
        ///
        /// </summary>
        [StringLength(50)]
        public string Code { get; set; }
        /// <summary>
        ///
        /// </summary>
        [StringLength(50)]
        public string Name { get; set; }
 
        /// <summary>
        ///
        /// </summary>
         
        public int DepartmentId { get; set; }
 
        /// <summary>
        ///
        /// </summary>
        public virtual DepartmentMaster Department { get; set; }
 
    }
 
 
using EntityFramework6.Entity;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity.ModelConfiguration;
using System.Data.Entity;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
 
namespace EntityFramework6.Map
{
 
 
 
 
 
    /// <summary>
    /// https://learn.microsoft.com/zh-cn/ef/ef6/modeling/code-first/fluent/cud-stored-procedures
    /// https://learn.microsoft.com/en-us/ef/ef6/modeling/code-first/fluent/cud-stored-procedures
    /// ef6 存储过程映射 Entity Framework 6
    /// Stored procedure mapping
    /// Code First Insert, Update, and Delete Stored Procedures
    /// </summary>
    public class EmployeeMasterMap: EntityTypeConfiguration<EmployeeMaster>
    {
 
 
        /// <summary>
        ///
        /// </summary>
        public EmployeeMasterMap() {
 
            //table 
            ToTable("EmployeeMaster");
 
            //key
            HasKey(t => t.EmployeeId);
 
            //fields
            Property(x => x.EmployeeId)
                    .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
 
            Property(x => x.Name).HasColumnType("VARCHAR").HasMaxLength(50);
            Property(x => x.Code).HasColumnType("VARCHAR").HasMaxLength(50);
 
 
            Property(x => x.DepartmentId)
              .HasColumnName("DepartmentId")
             .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
 
 
 
            HasRequired(c => c.Department).WithMany(l => l.Employees);
 
 
            //Stored procedure mapping 存储过程  i => i.HasName("InsertEmployee","dbo")
            //            .Parameter(b => b.Code, "Code")
            //           .Parameter(b => b.Name, "Name")
            //           .Parameter(b => b.DepartmentId, "DepartmentId")
 
 
            /*1 规则默认存储过程 表名，类名，参数名一致
             CREATE PROCEDURE [dbo].[EmployeeMaster_Insert] 
    @Code [nvarchar](max), 
    @Name [nvarchar](max), 
    @DepartmentId [int] 
AS 
BEGIN 
    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 
END 
   
GO
             //规则默认存储过程 表名，类名，参数名一致
            //dbo.EmployeeMaster_Insert
            //MapToStoredProcedures();          
             */
 
 
            //2 自定义存储过程
            MapToStoredProcedures(s=>s.Insert(u=>u.HasName("InsertEmployee","dbo") //添加
                        .Parameter(b => b.Code, "Code")
                       .Parameter(b => b.Name, "Name")
                       .Parameter(b => b.DepartmentId, "DepartmentId")
            )
            .Delete(d => d.HasName("DeleteEmployee","dbo") //删除
                       .Parameter(b => b.EmployeeId, "EmployeeId"))
            .Update(u => u.HasName("UpdateEmployee","dbo")  //更新
                       .Parameter(b => b.EmployeeId, "EmployeeId")
                       .Parameter(b => b.Code, "Code")
                       .Parameter(b => b.Name, "Name")
                       .Parameter(b => b.DepartmentId, "DepartmentId"))
            );
            /*2 自定义存储过程
             CREATE PROCEDURE [dbo].[InsertEmployee] 
    @Code [nvarchar](max), 
    @Name [nvarchar](max), 
    @DepartmentId [int] 
AS 
BEGIN 
    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 
END 
   
GO 
              
             */
 
        }
 
 
 
    }
}

　　

调用：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
using (var ctx = new EfDbContext())
        {
            ctx.Database.Log = Console.WriteLine;
 
 
            InitialEntities initialEntities = new InitialEntities();
            initialEntities.Up();
 
            //未找到存储过程
 
            SqlParameter [] parameters =
            {
                new SqlParameter("@Code",SqlDbType.NVarChar, 50),
                new SqlParameter("@Name",SqlDbType.NVarChar,50),
                new SqlParameter("@DepartmentId",SqlDbType.Int)
            };
 
            EmployeeMaster employee = new EmployeeMaster();
            employee.Code = "A0011";
            employee.Name = "Geovin Du 涂聚文";
            employee.DepartmentId = 1;
 
            parameters[0].Value= employee.Code;
            parameters[1].Value= employee.Name;
            parameters[2].Value = employee.DepartmentId;
 
            //不映射存储过程方式
            //int k=ctx.Database.ExecuteSqlCommand("dbo.InsertEmployee @Code,@Name,@DepartmentId", parameters);
            //  if(k>0)
            //  {
            //      Console.WriteLine("ok");
 
            //  }
            //  else
            //  {
            //      Console.WriteLine("no");
            //  }
 
            //dbo.EmployeeMaster_Insert
            int k = 0;
            //存储过程 添加
            ctx.Employees.Add(employee);
            k = ctx.SaveChanges();
            if (k > 0)
            {
                Console.WriteLine("成功！");
            }
            else
            {
                Console.WriteLine("不成功");
            }
            //修改
            SqlParameter[] eparameters =
            {              
                new SqlParameter("@EmployeeId",SqlDbType.Int),
                new SqlParameter("@Code",SqlDbType.NVarChar, 50),
                new SqlParameter("@Name",SqlDbType.NVarChar,50),
                new SqlParameter("@DepartmentId",SqlDbType.Int)
            };
 
 
 
            EmployeeMaster editemployee = new EmployeeMaster();
            editemployee.EmployeeId = 2;
            editemployee.Code = "A0022";
            editemployee.Name = "涂聚文";
            editemployee.DepartmentId = 1;
            eparameters[0].Value = editemployee.EmployeeId;
            eparameters[1].Value = editemployee.Code;
            eparameters[2].Value = editemployee.Name;
            eparameters[3].Value = editemployee.DepartmentId;
 
            ctx.Entry(editemployee).State = System.Data.Entity.EntityState.Modified;
             k = ctx.SaveChanges();
            if (k > 0)
                Console.WriteLine("ok,eidt成功");
            else
                Console.WriteLine("no,eidt不成功");
 
            EmployeeMaster delemployee = new EmployeeMaster();
            delemployee.EmployeeId = 3;
 
            //删除
            SqlParameter[] delparameters =
           {
                new SqlParameter("@EmployeeId",SqlDbType.Int),
            };
            delparameters[0].Value=delemployee.EmployeeId.ToString();
 
            ctx.Entry(delemployee).State = System.Data.Entity.EntityState.Deleted;
            k = ctx.SaveChanges();
            if (k > 0)
                Console.WriteLine("ok del");
            else
                Console.WriteLine("no del");

　　

posted @   ®Geovin Du Dream Park™  阅读(18)  评论(0)  编辑  收藏  举报
相关博文：
·  CSharp: donet 7 create Stored procedure mapping with Entity Framework core 7
·  CSharp: donet create database view mapping with EF 6
·  Entity Framework教程-存储过程、视图、函数（Stored Procedures, Views, and Functions）
·  SQLServer 存储过程
·  Oracle入门6（plsql语言）
阅读排行：
· 25岁的心里话
· 闲置电脑爆改个人服务器（超详细） #公网映射 #Vmware虚拟网络编辑器
· 基于 Docker 搭建 FRP 内网穿透开源项目（很简单哒）
· 零经验选手，Compose 一天开发一款小游戏！
· 一起来玩mcp_server_sqlite，让AI帮你做增删改查！！
历史上的今天：
2015-02-06 csharp:datagridview enter Half Width and Full Width characters
< 2025年3月 >
日 一 二 三 四 五 六
23 24 25 26 27 28 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31 1 2 3 4 5
点击右上角即可分享
微信分享提示