Mr. Manoj Kumar Mr. Manoj Kumar - 4 months ago 12
ASP.NET (C#) Question

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

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??

Answer

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;
 }


}
Comments