Stored procedure call to project and check for exists record C#

Question

0.00/5 (No votes)

See more:

I have this stored procedure:

SQL

Create procedure [dbo].[Save_Customer]

    @CustomerID int output,
    @CustomerName nvarchar (150)

    

as
    if (select count(*) FROM dbo.CustomersTbl WHERE CustomersTbl.CustomerID= @CustomerID)>0
    BEGIN

        return -3
    END 
ELSE    
   if (select count(*) FROM dbo.CustomersTbl WHERE CustomersTbl.CustomerName= @CustomerName)=0
     BEGIN
       insert into CustomersTbl
                (CustomerID,CustomerName)
                values
                (@CustomerID,@CustomerName)
                return -2
     END
            else
            begin
            return -4
            
    END

in the project i have class called DataAccessLayer this call has this method to add,update or delete :

C#

public void ExecuteCommand(string stored_procedure, SqlParameter[] param)
{
    SqlCommand sqlcmd = new SqlCommand();
    sqlcmd.CommandType = CommandType.StoredProcedure;
    sqlcmd.CommandText = stored_procedure;
    sqlcmd.Connection = sqlconnection;
    if (param != null)
    {
        sqlcmd.Parameters.AddRange(param);
    }
    sqlcmd.ExecuteNonQuery();
}

Now i have create another Layer Called Business Layer Folder contain class called CLS_AddUpdateDelete and it has this code to save customer :

C#

public void Save_Customers(int Customer_Id, string Customer_Name)
{
    Data_Layer.DataAccessLayer DAL = new Data_Layer.DataAccessLayer();
    DAL.Open();
    SqlParameter[] param = new SqlParameter[2];
    param[0] = new SqlParameter("@CustomerID", SqlDbType.Int);
    param[0].Value = Customer_Id;
    param[1] = new SqlParameter("@CustomerName", SqlDbType.NVarChar,150);
    param[1].Value = Customer_Name;
    DAL.ExecuteCommand("Save_Customer", param);
    DAL.Close();
}

in the form i have button :

C#

private void button3_Click(object sender, EventArgs e)
{
    if (textBox3.Text.Trim() == "")
    {
        MessageBox.Show("Please Enter Customer Name");
        return;
    }
    Saving.Save_Customers(Convert.ToInt32(textBox4.Text.Trim()),textBox3.Text.Trim());
    MessageBox.Show("Added Successfully", "Add Customer", MessageBoxButtons.OK, MessageBoxIcon.Information);
    ClearControls.ResetAllControls(this);
    dataGridView2.DataSource = GetInfo.GetAllFroDataGridView();
    DataTable tbl = GetLastRecord.GetLastRecord();
    textBox4.Text = (Convert.ToInt32(tbl.Rows[0][0])).ToString();
}

what i want is how to know if this record is exists in database and if it is exists i need to popup message to use that record is exists in database .

Thanks in Advance :)

What I have tried:

public DataTable Checking(int Customer_Id, string Customer_Name)
{
Data_Layer.DataAccessLayer DAL = new Data_Layer.DataAccessLayer();
DataTable tbl = new DataTable();
tbl = DAL.readData("Save_Customer");
DAL.Close();
return tbl;
}

Posted 2-Dec-20 23:32pm

khalid4775

Updated 3-Dec-20 1:47am

Add a Solution

Comments

F-ES Sitecore 3-Dec-20 6:27am

You need to get the "return" value from your SP so you know what happened inside it. You return -3 when the id exists but your code doesn't retrieve that information. You need to add the return parameter to your command so you can process that information.

https://flylib.com/books/en/1.105.1/retrieving_stored_procedure_return_values_using_a_datareader.html

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Answer 1 · 2020-12-03T00:24:00

Solution 1

Start by tidying up your stored procedure:

SQL

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:

C#

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.

Posted 3-Dec-20 0:24am

Richard Deeming

Comments

khalid4775 3-Dec-20 8:57am

Thanks For help but iam getting errors :

1.CS0246 C# The type or namespace name could not be found (are you missing a using directive or an assembly reference?)

2.CS0103 C# The name does not exist in the current context

https://ibb.co/t20h3N3][img]https://i.ibb.co/090XQWQ/d.jpg[^][^]

Richard Deeming 3-Dec-20 9:25am

Because you need to create the CustomerSaveResult enum to represent the result of the method.

public enum CustomerSaveResult
{
    Ok,
    DuplicateId,
    DuplicateName,
    UnknownError,
}

khalid4775 3-Dec-20 9:45am

Thank you Thank you Thank you
i have add this code to button save

Saving.Save_Customers(Convert.ToInt32(textBox4.Text.Trim()), textBox3.Text.Trim());

How to inspect the returned CustomerSaveResult value and display it in message

Than you very much

Richard Deeming 3-Dec-20 10:57am

Seriously?!

CustomerSaveResult saveResult = Saving.Save_Customers(Convert.ToInt32(textBox4.Text.Trim()), textBox3.Text.Trim());
if (saveResult == CustomerSaveResult.DuplicateId)
{
    ...
}
else if (saveResult == CustomerSaveResult.DuplicateName)
{
    ...
}
...

khalid4775 3-Dec-20 11:20am

Yes serious iam new in C# so i try to read and search for solution for my errors before i send question
now i got error
Cannot implicitly convert type to. An explicit conversion exists (are you missing a cast?)
can you help me with this :)

https://ibb.co/TH4r0Vj

Richard Deeming 3-Dec-20 11:21am

No idea. You haven't provided the full error message, nor the relevant part of your code.

khalid4775 3-Dec-20 11:24am

https://ibb.co/TH4r0Vj

Richard Deeming 3-Dec-20 11:25am

Once again: You haven't provided the full error message, nor the relevant part of your code.

khalid4775 3-Dec-20 11:28am

https://ibb.co/2ZPzc0t

Error CS0266 Cannot implicitly convert type 'Test.Business_Layer.CLS_AddUpdateDelete.CustomerSaveResult' to 'Test.PL.FrmAddUpdateDelete.CustomerSaveResult'. An explicit conversion exists (are you missing a cast?)

Richard Deeming 3-Dec-20 11:29am

You've created multiple versions of the CustomerSaveResult enum, nested under different classes.

Create a single version of the enum, in the same namespace as your DAL class, not nested inside another class.

khalid4775 3-Dec-20 11:29am

private void button3_Click(object sender, EventArgs e)
{
if (textBox3.Text.Trim() == "")
{
MessageBox.Show("Enter Customer Name");
return;
}
CustomerSaveResult saveResult = Saving.Save_Customers(Convert.ToInt32(textBox4.Text.Trim()), textBox3.Text.Trim());
if (saveResult == CustomerSaveResult.DuplicateId)
{
MessageBox.Show("Custumer Id Exites");
}
else if (saveResult == CustomerSaveResult.DuplicateName)
{
MessageBox.Show("Custumer Name Exites");
}
{

khalid4775 3-Dec-20 11:41am

ok sir i have added CustomerSaveResult enum to DAL class but iam unable call it to other class or form : i added this to DAL class :

public enum CustomerSaveResult
{
Ok,
DuplicateId,
DuplicateName,
UnknownError,
}

khalid4775 3-Dec-20 12:20pm

Solved Thanks :)

just added
using static Test.Data_Layer.DataAccessLayer;