How to get stored procedure error in your C#-application using connected layer

Question

I have a stored procedure for an insert into a database, with exception handling and errors which I want to catch in C#. The whole execution is going well but when I insert a statement that is wrong and I expect an error, I don't know how to print the message from the error into a messagebox in the C# application.

This is my stored procedure:

CREATE PROCEDURE spNewBooking
( 
   @customer VARCHAR(255), 
   @incheckdate DATE,
   @checkoutdate DATE,
   @hotel VARCHAR(40),
   @count_persons INT,
   @emplyeeid INT,
   @roomid int
) 
AS 
BEGIN 
    DECLARE @bookdate DATE = GETDATE() 

    IF NOT EXISTS(SELECT name
                  FROM customer
                  WHERE @customer = name)   
    BEGIN 
        RAISERROR ('This customer does not exists', 16, 1)
        RETURN
    END 

    BEGIN TRANSACTION
        SELECT @customer = customerid
        FROM customer
        WHERE @customerid = name

        SELECT @hotel = hotelid
        FROM hotel
        WHERE @hotel = location

        INSERT INTO booking 
        VALUES (@bookdate, @count_persons, NULL, @customer, @hotel, @emplyeeid)

        INSERT INTO boekingroom 
        VALUES (@roomid, @incheckdate, @checkoutdate, NULL, NULL)

        IF @@ERROR <> 0
        BEGIN
            ROLLBACK
            RAISERROR ('Error! Check the input', 16, 1)
            RETURN
        END

        COMMIT
END

In my application I have a class Database where I have method for the insert. I get the parameters from my mainwindow and the execution happens in the main window.

This is the code for the main window:

private void BtnBook_Click_1(object sender, RoutedEventArgs e)
{
        try
        {
            Database.AddBooking(textBoxcustomer.Text, Convert.ToDateTime(dpIncheck.Text), Convert.ToDateTime(dpCheckout.Text), dpHotellocation.Text, Convert.ToInt32(dpCountpersons.Text), 2, 7);
            MessageBox.Show("Done!!");
        }
        catch (SqlException E)
        {
            MessageBox.Show(E.Message);
        }
}

Code from the database class

public static void Addbooking(string klant, DateTime incheck, DateTime uitcheck, string hotel, int aantal_personen, int medewerker, int kamerid)
{
    using (SqlConnection connection = new SqlConnection(ConnectionString))
    {
        connection.Open();
        string query = "exec spNewBooking '" + customer + "', '" + incheck + "', '" + checkout + "', '" + hotel + "', '" + countperson + "', '" + employeeid + "', '" + kamerid +"'";

        SqlCommand selectNieuweAuditCommand = new SqlCommand(query, connection);

        SqlDataReader myreader;
        myreader = selectNewBookingCommand.ExecuteReader();

        while (myreader.Read())
        { }
    }
}

How can I get the error in the stored procedure in a message box in my C# application?

First of all, I recommend that you wrap your commands and readers in `using` clauses to make sure they are disposed as well — Jaques, May 20 '19 at 12:41
You can find many discussions and tutorials about [parameterizing your queries](https://csharp-station.com/Tutorial/AdoDotNet/Lesson06) - please learn to do so to avoid many different issues (sql injection being a primary one). — SMor, May 20 '19 at 12:53
Aside from the terrible practice of not parameterizing your queries you have a major logical flaw in your procedure. You want to capture any errors but you don't have a try/catch block. The code right now will only throw your error if there is an error inserting into "boekingroom". After you finish learning how to properly parameterize your database call you need to read about try/catch in sql server and implement it. — Sean Lange, May 20 '19 at 13:17

score 0 · Answer 1 · answered May 20 '19 at 13:17

You could return status code instead empty return from you stored procedure.

 IF @@ERROR <> 0
        BEGIN
            ROLLBACK
            RAISERROR ('Error! Check the input', 16, 1)
            RETURN @@ERROR
        END

Also you could add your SP to DataContext and use it as regular method of DataContext with integer return data type

How to get stored procedure error in your C#-application using connected layer

1 Answers1