Start by tidying up your stored procedure:
CREATE OR ALTER PROCEDURE dbo.Save_Customer
(
@CustomerID int,
@CustomerName nvarchar (150)
)
As
BEGIN
SET NOCOUNT ON;
If Exists(SELECT 1 FROM dbo.CustomersTbl WHERE CustomerID = @CustomerID)
BEGIN
Return -3;
END;
If Exists(SELECT 1 FROM dbo.CustomersTbl WHERE CustomerName = @CustomerName)
BEGIN
Return -4;
END;
INSERT INTO dbo.CustomersTbl (CustomerID, CustomerName)
VALUES (@CustomerID, @CustomerName);
Return -2;
END
Your BL code will need to check the return value from the stored procedure:
public CustomerSaveResult Save_Customers(int Customer_Id, string Customer_Name)
{
var pReturnValue = new SqlParameter("@ReturnValue", SqlDbType.Int) { Direction = ParameterDirection.ReturnValue };
var pCustomerId = new SqlParameter("@CustomerID", SqlDbType.Int) { Value = Customer_Id };
var pCustomerName = new SqlParameter("@CustomerName", SqlDbType.NVarChar, 150) { Value = Customer_Name };
SqlParameter[] param = { pReturnValue, pCustomerId, pCustomerName };
var DAL = new Data_Layer.DataAccessLayer();
DAL.Open();
try
{
DAL.ExecuteCommand("Save_Customer", param);
}
finally
{
DAL.Close();
}
switch ((int)pReturnValue.Value)
{
case -2: return CustomerSaveResult.Ok;
case -3: return CustomerSaveResult.DuplicateId;
case -4: return CustomerSaveResult.DuplicateName;
default: return CustomerSaveResult.UnknownError;
}
}
Then your UI code will need to inspect the returned
CustomerSaveResult
value and display the appropriate message.