Stored Procedure execution from Entity Framework should return 0 or -1 if insert or update fails

indian143

Hi all, I have a stored procedure below:

ALTER PROCEDURE [dbo].[Usp_Add_ProgramType] (@Type varchar(15), @Description varchar(15), @IsValid bit
, @CreatedBy varchar(500), @ModifiedBy varchar(500))
AS
BEGIN
if not exists(select top 1 1 from [dbo].[ProgramTypeLKP] where [Type]=@Type)
begin
INSERT INTO [dbo].[ProgramTypeLKP] ([Type], [Description], IsValid, CreatedDate, CreatedBy, ModifiedDate, ModifiedBy)
values(@Type, @Description, @IsValid, getdate(), @CreatedBy, GETDATE(), @ModifiedBy)
end
--else
--begin
----Want to return some thing here that makes EF funnction to return 0 or -1 or some value so that I can show user a message that its a duplicate record.
--end
END

Even if the Condition fails the EF method is returning value as 1, but I want to get a value on which I will show the end user a message that he is trying to attempt to insert a duplicate record. Like in the below script, I am checking for the result or errors, I have to get error in the response, or at least in the result some where so that I can show the user a message, I am using Kendo Grid, Create and Update with Popup (just a foot-note):

function onRequestEnd(e)
{
//Check request type
if (e.type == "create")
{
if ((e.response != null) && ((e.response.Errors != null) || (e.response.data["0"].Result <= 1)))
{
//Set some label value in red only when to say attempt to insert duplicate record
}
}
else if (e.type == "update")
{
if ((e.response != null) && ((e.response.Errors != null) || (e.response.data["0"].Result <= 1)))
{
//Set some label value in red only when to say attempt to insert duplicate record
}
}
}

Can anybody please help me in this regards, thanks in advance. Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."

Mycroft Holmes

ProgramType - I would have assumed that the client app already has a list of prexisting records to check against. Assuming ProgramType has an identity field to generate the ID I would return the Select @@Scope_Identity after the insert or select -1 in the else segment

Never underestimate the power of human stupidity RAH

indian143

I tried it as below but still in the duplicate situation also it was returning me value 1, I didn't really get it why?

ALTER PROCEDURE [dbo].[Usp_Add_LookupRecord] (@LookupTableId int,
@Type varchar(100), @Description varchar(500), @ForeignKeyId int, @CreatedBy varchar(500), @ModifiedBy varchar(500), @IsValid bit
)
AS
BEGIN
declare @LookupTableName varchar(100)=(select top 1 LookupTableName FROM [dbo].[ListOfLookupTables] WHERE PkListOfLookupTablesId=@LookupTableId)
declare @InsertedId int=0, @FKProgramTypeLKPId int = (select top 1 PKProgramTypeLKPId from ProgramTypeLKP where Type='Mental Health')

IF (@LookupTableName='ProgramTypeLKP')
BEGIN
	IF NOT EXISTS(SELECT Top 1 1 FROM dbo.ProgramTypeLKP WHERE \[Type\]=@Type AND ISNULL(IsValid, 1)=1)
	BEGIN
		INSERT INTO dbo.ProgramTypeLKP (\[Type\], \[Description\], CreatedDate, CreatedBy, ModifiedDate, ModifiedBy, IsValid)
		VALUES(LEFT(@Type, 15), LEFT(@Description, 500), GETDATE(), LEFT(@CreatedBy, 500), GETDATE(), LEFT(@ModifiedBy, 500), @IsValid)
		SET @InsertedId=SCOPE\_IDENTITY()
	END
	ELSE
	BEGIN
		RETURN 0
	END
END
ELSE IF (@LookupTableName='AddressTypeLKP')
BEGIN
	IF NOT EXISTS(SELECT Top 1 1 FROM dbo.AddressTypeLKP WHERE \[Description\]=@Type AND ISNULL(IsValid, 1)=1)
	BEGIN
		INSERT INTO dbo.AddressTypeLKP (\[Description\], FKProgramTypeLKPId, CreatedDate, CreatedBy, ModifiedDate, ModifiedBy, IsValid)
		VALUES(LEFT(@Description, 20), @FKProgramTypeLKPId, GETDATE(), LEFT(@CreatedBy, 30), GETDATE(), LEFT(@ModifiedBy, 30), @IsValid)
		SET @InsertedId=SCOPE\_IDENTITY()
	END
	ELSE
	BEGIN
		RETURN 0
	END
END

RETURN @InsertedId

END

Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."

Mycroft Holmes

That is one nasty design! I would have a seperate procedure for each table!

indian143 wrote:

IF NOT EXISTS(SELECT Top 1 1 FROM dbo.ProgramTypeLKP WHERE [Type]=@Type AND ISNULL(IsValid, 1)=1)

Test the result of that query - I'll bet it never finds a record. The following does not seem to make sense outside of an IF statement.

indian143 wrote:

declare @LookupTableName varchar(100)=(select top 1 LookupTableName FROM [dbo].[ListOfLookupTables] WHERE PkListOfLookupTablesId=@LookupTableId) declare @InsertedId int=0, @FKProgramTypeLKPId int = (select top 1 PKProgramTypeLKPId from ProgramTypeLKP where Type='Mental Health')

Never underestimate the power of human stupidity RAH

Richard Deeming

How are you calling the stored procedure? The ExecuteSqlCommand method returns the number of rows affected, not the return value of the stored procedure. There are two ways to get the return value:

var returnValue = new SqlParameter()
{
ParameterName = "@ReturnValue",
SqlDbType = SqlDbType.Int,
Direction = System.Data.ParameterDirection.Output
};

context.Database.ExecuteSqlCommand("exec dbo.Usp_Add_LookupRecord", returnValue, ...other parameters here...);

int result = (int)returnValue.Value;

Or:

int result = context.Database.SqlQuery<int>("exec dbo.Usp_Add_LookupRecord", ... parameters here ...).First();

sql server - EF ExecuteStoredCommand with ReturnValue parameter - Stack Overflow[^]

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

indian143

Yes I can understand that, but there are 25 tables with same Columns Code and Description, one table is selected with Dropdown to add, update or delete, so I have written just one stored procedure instead of writing multiple ones. That's why I used LookupTableId, Because I have listed all those tables into one LookupTable and the LookupTableId comes from that table. I think it better suited for this scenario, but yes in general its not a good practice. About Program Type, its always the same Program Type that's why I just took the id and used it. Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."

indian143

I am not sure but its calling the SP in the following way:

    public virtual int Usp\_Add\_LookupRecord(Nullable lookupTableId, string type, string description, Nullable foreignKeyId, string createdBy, string modifiedBy, Nullable isValid)
    {
        var lookupTableIdParameter = lookupTableId.HasValue ?
            new ObjectParameter("LookupTableId", lookupTableId) :
            new ObjectParameter("LookupTableId", typeof(int));

        var typeParameter = type != null ?
            new ObjectParameter("Type", type) :
            new ObjectParameter("Type", typeof(string));

        var descriptionParameter = description != null ?
            new ObjectParameter("Description", description) :
            new ObjectParameter("Description", typeof(string));

        var foreignKeyIdParameter = foreignKeyId.HasValue ?
            new ObjectParameter("ForeignKeyId", foreignKeyId) :
            new ObjectParameter("ForeignKeyId", typeof(int));

        var createdByParameter = createdBy != null ?
            new ObjectParameter("CreatedBy", createdBy) :
            new ObjectParameter("CreatedBy", typeof(string));

        var modifiedByParameter = modifiedBy != null ?
            new ObjectParameter("ModifiedBy", modifiedBy) :
            new ObjectParameter("ModifiedBy", typeof(string));

        var isValidParameter = isValid.HasValue ?
            new ObjectParameter("IsValid", isValid) :
            new ObjectParameter("IsValid", typeof(bool));

        return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("Usp\_Add\_LookupRecord", lookupTableIdParameter, typeParameter, descriptionParameter, foreignKeyIdParameter, createdByParameter, modifiedByParameter, isValidParameter);
    }

And when I changed the stored procedure to return -3 is record exists, it is returning me -1, here is my SP:

ALTER PROCEDURE [dbo].[Usp_Add_LookupRecord] (@LookupTableId int,
@Type varchar(100), @Description varchar(500), @ForeignKeyId int, @CreatedBy varchar(500), @ModifiedBy varchar(500), @IsValid bit
)
AS
BEGIN
declare @LookupTableName varchar(100)=(select top 1 LookupTableName FROM [dbo].[ListOfLookupTables] WHERE PkListOfLookupTablesId=@LookupTableId)
declare @InsertedId int=0, @FKProgramTypeLKPId int = (select top 1 PKProgramTypeLKPId from ProgramTypeLKP where Type='Mental Health')

IF (@Lookup

Richard Deeming

Try something like this:

public virtual int Usp_Add_LookupRecord(int? lookupTableId, string type, string description, int? foreignKeyId, string createdBy, string modifiedBy, bool? isValid)
{
var returnValue = new SqlParameter
{
ParameterName = "@ReturnValue",
SqlDbType = SqlDbType.Int,
Direction = System.Data.ParameterDirection.Output
};

var lookupTableIdParameter = new SqlParameter("LookupTableId", SqlDbType.Int) 
{
    Value = (object)lookupTableId ?? DBNull.Value
};

var typeParameter = new SqlParameter("Type", SqlDbType.VarChar, 100)
{
    Value = (object)type ?? DBNull.Value
};

var descriptionParameter = new SqlParameter("Description", SqlDbType.VarChar, 500)
{
    Value = (object)description ?? DBNull.Value
};

var foreignKeyIdParameter = new SqlParameter("ForeignKeyId", SqlDbType.Int)
{
    Value = (object)foreignKeyId ?? DBNull.Value
};

var createdByParameter = new SqlParameter("CreatedBy", SqlDbType.VarChar, 500)
{
    Value = (object)createdBy ?? DBNull.Value
};

var modifiedByParameter = new SqlParameter("ModifiedBy", SqlDbType.VarChar, 500)
{
    Value = (object)modifiedBy ?? DBNull.Value
};

var isValidParameter = new SqlParameter("IsValid", SqlDbType.Bit)
{
    Value = (object)isValid ?? DBNull.Value
};

Database.ExecuteSqlCommand("exec dbo.Usp\_Add\_LookupRecord", 
    returnValue, 
    lookupTableIdParameter, 
    typeParameter, 
    descriptionParameter, 
    foreignKeyIdParameter, 
    createdByParameter, 
    modifiedByParameter, 
    isValidParameter);

return (int)returnValue.Value;

}

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

indian143

Yes I did it thanks a lot Rich Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."