EF调用存储过程实例

创建实体：

 public class User
{
public string UserID { get; set; }
public string UserName { get; set; }
public string Password { get; set; }
[Description("测试")]
public string UserGroupId { get; set; }
public UserGroup UserGroup { get; set; }
public List<Role> Roles { get; set; }
}
 
public class UserGroup
{
public string GroupID { get; set; }
public string GroupName { get; set; }
public List<User> Users { get; set; }
}
 
public class Role
{
public string RoleID { get; set; }
public string RoleName { get; set; }
public List<User> Users { get; set; }
}

 

2.2、准备SQL数据

--新增数据

alter PROC createData
@rowNum INT
AS
DECLARE @index INT =1,@id VARCHAR(50)=NEWID()
INSERT dbo.UserGroups
( GroupID, GroupName )
VALUES ( @id, -- GroupID - nvarchar(128)
N'开发组' -- GroupName - nvarchar(max)
)
WHILE(@index<=@rowNum)
BEGIN
INSERT dbo.Users
( UserID ,
UserName ,
Password ,
UserGroupId
)
VALUES ( NEWID(),
N'张三' , -- UserName - nvarchar(max)
N'123456' , -- Password - nvarchar(max)
@id -- UserGroupId - nvarchar(128)
)
SET @index=@index+1
END

--删除数据

alter PROC DeleteData
AS
BEGIN
TRUNCATE TABLE dbo.Users
END

--修改数据

ALTER PROC ModifyData
@uid VARchar(50),
@userName varchar(50)
AS
BEGIN
UPDATE dbo.Users SET UserName=@userName WHERE UserID=@uid
END

--查找数据

ALTER PROC GetLlist
@rowNumber int
AS
BEGIN
SELECT * FROM ( SELECT ROW_NUMBER() OVER(ORDER BY UserID) AS ID,* FROM dbo.Users ) AS A WHERE A.ID<=@rowNumber
END

 

2.3C#代码调用实例

2.3.1 查询列表

var db = new TestDbContext();
SqlParameter[] parameter = new SqlParameter[1];
parameter[0] = new SqlParameter("@rowNumber", 10);
var users = db.Users.SqlQuery("exec GetLlist @rowNumber", parameter).ToList();

2.3.2  返回有参数的存储过程

 var db = new TestDbContext();
int count = db.Database.SqlQuery(typeof(int),"exec ProSelectCount").Cast<int>().FirstOrDefault();

 

2.3.3  增删查改

 

var db = new TestDbContext();
SqlParameter[] parameter = new SqlParameter[1];
parameter[0] = new SqlParameter("@rowNum", 5);
db.Database.ExecuteSqlCommand("exec CreateData @rowNum", parameter);