如何从sp获取数据表
问题描述:
这是我的SP和iam尝试使用LINQ从sp获取数据表,但它只返回0.
我如何获得数据表
this is my SP and iam try to get the datatable from sp using LINQ but it is only returning 0.
how can i get datatable
ALTER PROCEDURE [dbo].[SP_on_TblClients]
(
@Action varchar(50),
@ClientID bigint=0,
@CFName varchar(50)=null,
@CMName varchar(50)=null,
@CLName varchar(50)=null,
@CMobile varchar(15)=null,
@CMobile2 varchar(15)=null,
@CEMail1 varchar(50)=null,
@CEMail2 varchar(50)=null,
@SoftwareID bigint=0,
@CRemarks varchar(MAX)=null,
@CRemarks1 varchar(MAX)=null,
@CRequirement varchar(MAX)=null,
@SelectTp varchar(50)=null,
@successstatus int out
)
As
begin transaction
if @Action ='select'
begin
IF @SelectTp='all'
begin
Select ROW_NUMBER() over(ORDER BY CFName) As SrNo, [ClientID],
[CFName],
[CMName],
[CLName],
[CMobile],
[CMobile2],
[CEMail1],
[CEMail2],
[SoftwareID],
[CRemarks],
[CRemarks1],
[CRequirement] from [TblClients]
end
else
begin
Select ROW_NUMBER() over(ORDER BY CFName) As SrNo, [ClientID],
[CFName],
[CMName],
[CLName],
[CMobile],
[CMobile2],
[CEMail1],
[CEMail2],
[SoftwareID],
[CRemarks],
[CRemarks1],
[CRequirement] from [TblClients] Where ClientID=@ClientID
end
end
else if @Action ='update'
begin
Update [TblClients] set
CFName = @CFName,
CMName = @CMName,
CLName = @CLName,
CMobile = @CMobile,
CMobile2 = @CMobile2,
CEMail1 = @CEMail1,
CEMail2 = @CEMail2,
SoftwareID = @SoftwareID,
CRemarks1 = @CRemarks1,
CRemarks = @CRemarks,
CRequirement = @CRequirement
Where ClientID = @ClientID
end
else if @Action ='delete'
begin
Delete FROM [TblClients]
Where ClientID = @ClientID
end
else if @Action ='insert'
begin
Insert Into [TblClients] (
CFName,
CMName,
CLName,
CMobile,
CMobile2,
CEMail1,
CEMail2,
SoftwareID,
CRemarks,
CRemarks1,
CRequirement) Values (
@CFName,
@CMName,
@CLName,
@CMobile,
@CMobile2,
@CEMail1,
@CEMail2,
@SoftwareID,
@CRemarks,
@CRemarks1,
@CRequirement)
select SCOPE_IDENTITY()
end
SELECT @successstatus =@@ERROR;
if(@successstatus = 0)
begin
commit
end
else
begin
rollback
end
GO
答
您的SP显示您有不同的情况(选择,插入,更新,删除)仅在一个SP中。
如果您有选择案例的输入(@Action ='select'),那么只有您将获得输出表格。
在其他情况下,你不会在输出中得到表格。
如果@Action ='选择'
你在逻辑上为
工作正常。
Your SP shows that you have different cases(Select, Insert, Update, Delete) in one SP only.
If you have inputs for select case(@Action ='select'), then only you will get table in output.
In other cases, you will not get table in output.
You logic in
if @Action ='select'
works fine.