Check if value is in SQL Server database or not and show in message

Question

This is the BL class for insert of data:

public  string CategoryIsert(clsCategoryPL objCategory, out int returnId)
{
    returnId = 0;

    try
    {
        var db = new KSoftEntities();

        var category = new tblCategory
        {
            Name = objCategory.Name,
            ParentCategoryID = objCategory.ParentCategoryID,
            description = objCategory.description,
            image = objCategory.image,
            Status = objCategory.Status
        };

        //db.AddTotblCategories(category);
        db.tblCategories.Add(category);
        db.SaveChanges();
        returnId = category.CategoryID;
    }
    catch (Exception ex) { }

    if (returnId > 0)
         return "User Inserted Successfully";
     else
         return "Error on insertion";
}

aspx code for adding value:

private int AddCategory()
{
    clsCategoryBL objcategory = new clsCategoryBL();
    clsCategoryPL objCategoryPL = new clsCategoryPL();

    int retnid = 0;

    objCategoryPL.description = txtCategoryDescription.Text;
    objCategoryPL.Name = txtCategoryName.Text;
    objCategoryPL.ParentCategoryID = Convert.ToInt32(ddlParentCategory.SelectedValue);
    objCategoryPL.Status = true;

    objcategory.CategoryIsert(objCategoryPL, out retnid);

    if (retnid > 0)
    {
        if (Convert.ToInt32(ddlParentCategory.SelectedValue) == 0)
        {
              objCategoryPL.ParentCategoryID = retnid;
        }

        objCategoryPL.CategoryID = retnid;
        string strMessage = objcategory.CategoryUpdate(objCategoryPL);
    }

    return retnid;
 }

I have created a stored procedure in the database:

CREATE PROCEDURE [dbo].[Sp_Checknm_Cat]
   @ID int,
   @NAME nvarchar(400),
   @Count INT = 0
AS
BEGIN
    DECLARE @output int

    IF(@ID > 0)
    BEGIN
        SET @Count = (select count(*) from tblCategory 
                      where Name = @NAME and CategoryID <> @ID)
    END
    ELSE
    BEGIN
        SET @Count = (select count(*) from tblCategory where Name = @NAME)
    END

    IF(@Count > 0)
    BEGIN
        SET @output = 0 
    END
    ELSE
    BEGIN
        SET @output = 1 
    END

    RETURN @output
END

I want to check if the name is already exist during insert/update, then it will show me a error message in lable

So where is the change needed?

This is for bl :

 ClsDB objdb = new ClsDB();
 public Int32 InsertnmCheck(int id, string nm)
 {
     DataTable dtdonor = new DataTable();
     SqlParameter[] param = new SqlParameter[2];

     param[0] = new SqlParameter("@ID", SqlDbType.Int);
     param[0].Direction = ParameterDirection.Input;
     param[0].Value = id;

     param[1] = new SqlParameter("@NAME", SqlDbType.NVarChar);
     param[1].Direction = ParameterDirection.Input;
     param[1].Value = nm;

     int a = objdb.insert_delete_update("[Sp_Checknm_Cat]", param);
     return a;
 }

here is my backend code:

else if (btnSubmit.CommandName == "Add")
{
   clsCategoryBL obj = new clsCategoryBL();
   Int32 dt = obj.InsertnmCheck(0, txtCategoryName.Text);
   //   DataTable dt = obj.InsertnmCheck(0, txtCategoryName.Text);

   {

   }

   int retid = AddCategory();
   if (retid > 0)
   {
   }

problem is that the query(sp) returns 0 but in bl class it will returns -1 so is there any solution??

"Do not return error codes." Your return string with a magic value of worked or did not work is an error code. https://blogs.msdn.microsoft.com/kcwalina/2005/03/16/design-guidelines-update-exception-throwing/ — granadaCoder, Aug 02 '16 at 13:40
what should i do??if i am not return then how can i check ?? — Mr. Manoj Kumar, Aug 02 '16 at 13:42
if i return true or false it will shows me the error..syntax error — Mr. Manoj Kumar, Aug 02 '16 at 13:45
http://stackoverflow.com/questions/26377065/t-sql-throw-exception — granadaCoder, Aug 02 '16 at 13:46
true or false is still a return code. the method should be "void", and an exception should be thrown or not thrown. — granadaCoder, Aug 02 '16 at 13:47
"Throw exceptions instead of returning an error code or HRESULT." https://msdn.microsoft.com/en-us/library/seyhszts%28v=vs.110%29.aspx?f=255&MSPPError=-2147217396 — granadaCoder, Aug 02 '16 at 13:49
what should i change if if(@Count>0) BEGIN; THROW 51000, 'Duplicates Exist', 1; END; is used in backend?? — Mr. Manoj Kumar, Aug 02 '16 at 13:55
Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, Aug 02 '16 at 14:02
ok...but can you tell me what should i do??is there any changes in stored procedure??How to return?? — Mr. Manoj Kumar, Aug 02 '16 at 14:05

granadaCoder · Accepted Answer · 2016-08-02T15:00:06.713

Throw an exception.

CREATE PROCEDURE [dbo].[usp_Checknm_Cat] /* do not prefix with 'sp' */
@ID int,
@NAME nvarchar(400),
@Count INT=0

AS
BEGIN
DECLARE @output int


    if(@ID>0)
    begin
        set @Count= @Count + (select count(*) from tblCategory where Name=@NAME and CategoryID<>@ID)
    end
    else
    begin
        set @Count= @Count + (select count(*) from tblCategory where Name=@NAME)
    end


    if(@Count>0)
    BEGIN;
      THROW 51000, 'Duplicates Exist', 1;
    END;


END

and c#

public void /* VOID, not a return code or return string */ CategoryIsert(clsCategoryPL objCategory)
{

    try
    {
        var db = new KSoftEntities();

        var category = new tblCategory
        {
            Name = objCategory.Name,
            ParentCategoryID = objCategory.ParentCategoryID,
            description = objCategory.description,
            image = objCategory.image,
            Status = objCategory.Status
        };

        //db.AddTotblCategories(category);
        db.tblCategories.Add(category);
        db.SaveChanges();
        returnId = category.CategoryID;
    }
    catch (SqlException sqlex) {
           /* you can examine the sql exception here, if you want to look for the 51000 */
           throw;
 }


    catch (Exception ex) {
           throw;
 }


}

if(@Count>0) BEGIN; THROW 51000, 'Duplicates Exist', 1; END; what is this??how to return here?? — Mr. Manoj Kumar, Aug 02 '16 at 13:53
The tsql code compiles for me with Sql Server 2012. You did not tag your Sql Server version. This code will throw an exception. Your c# code will catch a SqlException. Please read the article I posted in the comments. Stop thinking in terms of Return-Codes and think in terms of Exceptions. — granadaCoder, Aug 02 '16 at 14:56

Check if value is in SQL Server database or not and show in message

1 Answers1