如何在MVC中调用存储过程，请检查我的代码 [英] How to call stored procedure in MVC, please check my code

问题描述

CREATE TABLE [dbo]。[tblDepartment]（

[departmentId] [int] IDENTITY（1,1）NOT NULL，

[name] [nvarchar] （50）NULL，

CONSTRAINT [PK_tblDepartment] PRIMARY KEY CLUSTERED

（

[departmentId] ASC

） WITH（PAD_INDEX = OFF，STATISTICS_NORECOMPUTE = OFF，IGNORE_DUP_KEY = OFF，ALLOW_ROW_LOCKS = ON，ALLOW_PAGE_LOCKS = ON）ON [PRIMARY]

）ON [PRIMARY]



GO

CREATE TABLE [dbo]。[tblUser]（

[userId] [int] IDENTITY（1,1）NOT NULL，

[name] [nvarchar]（50）NULL，

[地址] [nvarchar]（500）空，

[州] [nvarchar]（ 50）NULL，

[salary] [bigint] NULL，

[departmentId] [int] NULL，

CONSTRAINT [PK_tblUser] PRIMARY KEY CLUSTERED

（

[userId] ASC

）WITH（PAD_INDEX = OFF，STATISTICS_NORECOMPUTE = OFF，IGNORE_DUP_KEY = OFF，ALLOW_ROW_LOCKS = ON，ALLOW_PAGE_LOCKS = ON）ON [PRIMARY]

）ON [主要]



GO



ALTER TABLE [dbo]。[tblUser] with CHECK ADD FOREIGN KEY （[departmentId]）

参考[dbo]。[tblDepartment]（[departmentId]）

GO







我的程序

创建proc [dbo]。[tblDepartment_insertUpdate]

@userId int = 0，

@name nvarchar（50），

@state nvarchar（50），

@address nvarchar（500），

@departmentId int，

@result nvarchar（50）输出

AS

开始

如果（ @userId = 0）

开始

插入到tblUser（名称，地址，州，departmentId）

值

（@ name，@ address，@ state，@ departmentId）

if（@@ identity> 0）

set @ result ='记录保存成功'

else

set @result ='记录未保存！'

结束

其他

开始

update tblUser set

name = @ name，

地址= @地址，

州= @州，

departmentId = @ departmentId

其中userId = @ userId

设置@ result ='记录已更新！'

结束

结束



我尝试了什么：



public IHttpActionResult PostEmpDetails（string name，int departmentId，string address，int salary，string state，int userId）

{

if（！ModelState.IsValid）

{

返回BadRequest（ModelState）;

}



//使用自己的存储过程



var spName = new SqlParameter （@ name，name）;

var spState = new SqlParameter（@ state，state）;

var spAddress = new SqlParameter（@ address，地址）;

var spDepartmentId = new SqlParameter（@ departmentId，departmentId）;

var spUser = new SqlParameter（@ userId， userId）;



var spOut = new SqlParameter

{

ParameterName =@ result，

SqlDbType = System.Data.SqlDbType.NVarChar，

大小= 100，

Direction = System.Data.ParameterDirection.Output

};



var user = eContext.Database.SqlQuery< tbluser> （exec tblDepartment_insertUpdate @ userId，@ name，@ state，@ address，@ departmentId，@ result out，

spUser，spName，spState，spAddress，spDepartmentId，spOut

）.ToList< tbluser>（）;

返回Ok（用户）;

}



it保存数据但是给出错误

数据阅读器与指定的'EntityTestModel.tblUser'不兼容。类型为userId的成员在数据读取器中没有相应名称的相应列。

CREATE TABLE [dbo].[tblDepartment](
[departmentId] [int] IDENTITY(1,1) NOT NULL,
[name] [nvarchar](50) NULL,
CONSTRAINT [PK_tblDepartment] PRIMARY KEY CLUSTERED
(
[departmentId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[tblUser](
[userId] [int] IDENTITY(1,1) NOT NULL,
[name] [nvarchar](50) NULL,
[address] [nvarchar](500) NULL,
[state] [nvarchar](50) NULL,
[salary] [bigint] NULL,
[departmentId] [int] NULL,
CONSTRAINT [PK_tblUser] PRIMARY KEY CLUSTERED
(
[userId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tblUser] WITH CHECK ADD FOREIGN KEY([departmentId])
REFERENCES [dbo].[tblDepartment] ([departmentId])
GO



my procedure
create proc [dbo].[tblDepartment_insertUpdate]
@userId int =0,
@name nvarchar(50) ,
@state nvarchar (50),
@address nvarchar(500),
@departmentId int ,
@result nvarchar(50) output
AS
Begin
if (@userId = 0)
begin
insert into tblUser (name ,address,state,departmentId)
values
(@name,@address,@state,@departmentId)
if (@@identity >0)
set @result='Record save successfully'
else
set @result ='Record not saved!'
end
else
begin
update tblUser set
name=@name,
address=@address,
state=@state,
departmentId=@departmentId
where userId=@userId
set @result='Record Updated !'
end
end

What I have tried:

public IHttpActionResult PostEmpDetails(string name ,int departmentId , string address , int salary , string state , int userId )
{
if (!ModelState.IsValid)
{
return BadRequest(ModelState);
}

// by using own Stored procedure

var spName = new SqlParameter("@name", name);
var spState = new SqlParameter("@state", state);
var spAddress = new SqlParameter("@address", address);
var spDepartmentId = new SqlParameter("@departmentId", departmentId);
var spUser = new SqlParameter("@userId", userId);

var spOut = new SqlParameter
{
ParameterName = "@result",
SqlDbType = System.Data.SqlDbType.NVarChar,
Size = 100,
Direction = System.Data.ParameterDirection.Output
};

var user = eContext.Database.SqlQuery<tbluser> ("exec tblDepartment_insertUpdate @userId,@name,@state,@address,@departmentId ,@result out ",
spUser, spName, spState, spAddress, spDepartmentId, spOut
).ToList<tbluser>();
return Ok(user);
}

it save data but give error
The data reader is incompatible with the specified 'EntityTestModel.tblUser'. A member of the type, 'userId', does not have a corresponding column in the data reader with the same name.

推荐答案

SqlQuery< TResult> 用于执行返回实体列表的查询。



您的查询不会返回任何内容。因此，您需要使用 ExecuteSqlCommand 。

SqlQuery<TResult> is for executing a query which returns a list of entities.

Your query does not return anything. Therefore, you need to use ExecuteSqlCommand instead.

eContext.Database.ExecuteSqlCommand(
    "exec tblDepartment_insertUpdate @userId,@name,@state,@address,@departmentId ,@result out",
    spUser, spName, spState, spAddress, spDepartmentId, spOut);

return Ok();

或者，如果你想返回您的操作中的用户详细信息，您将需要在存储过程结束时额外的 SELECT 语句，以返回您刚刚更新的用户的详细信息。

Or, if you want to return the user details from your action, you will need an extra SELECT statement at the end of your stored procedure to return the details of the user you've just updated.

if (@userId = 0)
begin
    insert into tblUser 
    (
        name,
        address,
        state,
        departmentId
    )
    values 
    (
        @name,
        @address,
        @state,
        @departmentId
    );
    
    -- Store the ID of the new user, so we can select the details:
    SET @userId = Scope_Identity();
end
else
begin
    update 
        tblUser 
    set
        name = @name,
        address = @address,
        state = @state,
        departmentId = @departmentId
    where 
        userId = @userId
    ;
end;

SELECT
    userId,
    name,
    address,
    state,
    departmentId
FROM
    tblUser
WHERE
    userId = @userId
;

这篇关于如何在MVC中调用存储过程，请检查我的代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！