问题描述
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.
推荐答案
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中调用存储过程，请检查我的代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！