Catching stored procedure/trigger message from SQL server into C# windows forms

Question

0.00/5 (No votes)

See more:

i created a simple log in application where if i entered a correct data it will give me the data i want, else if i enter a data that has no record my stored procedure will execute an RAISERROR my problem is i don't know how to catch it properly.

here's my code DAL folder

public List<globalVariables> getRecords(string employeeName)
        {
            globalVariables variables = new globalVariables();
            var credentials = new List<globalVariables>();
            var test = new List<globalVariables>();
            using (SqlConnection oConnection = new SqlConnection(variables.sqlConnection))
            {
                using (SqlCommand oCommand = new SqlCommand("spGetallinfor", oConnection))
                {
                    oCommand.Connection = oConnection;
                    oCommand.CommandType = CommandType.StoredProcedure;
                    oCommand.Parameters.AddWithValue("@employeeFirstname", employeeName);
                    SqlDataReader oReader = null;
                    try
                    {
                        oConnection.Open();
                        oReader = oCommand.ExecuteReader();
                        while (oReader.Read())
                        {
                            variables.employeeFirstname = oReader["employeeFirstname"].ToString();
                            variables.employeePhoto = (byte[])oReader["employeeImage"];
                            credentials.Add(variables);
                        }
                    }
                    catch (SqlException ex)
                    {
                        throw;
                    }
                }
            }
            return credentials;
        }

in my BAL

public List<globalVariables> getEmployeerecord(string employeeName)
        {
            try
            {
                return imageConnection.getRecords(employeeName);
            }
            catch (Exception ex)
            {
                throw;
            }
        }

and in my winform

if (e.KeyCode == Keys.Enter)
            {
                string name = txtName.Text;
                List<globalVariables> result = imageBL.getEmployeerecord(name);
                try
                {
                    foreach (var item in result)
                    {
                        lblName.Text = item.employeeFirstname;
                        byte[] byteResult = new byte[0];
                        byteResult = item.employeePhoto;
                        MemoryStream imageResult = new MemoryStream(byteResult);
                        pictureBox1.Image = Image.FromStream(imageResult);
                    }
                }
                catch (Exception ex)
                {
                    throw;
                }                
            }

My stored procedure

CREATE PROC [dbo].[spGetallinfor]

@employeeFirstname varchar(50)

AS
BEGIN

	IF EXISTS (SELECT * FROM tutorials WHERE employeeFirstname = @employeeFirstname)
		BEGIN
			
			SELECT employeeFirstname, employeeImage
			FROM tutorials
			WHERE employeeFirstname = @employeeFirstname
		END

	ELSE
		RAISERROR('Employee (%s) does not exists', 16, 0, @employeeFirstname)
END

What I have tried:

i tried to put a global variable in the catch but when i tried to put it in a label in winform it's gone.

Posted 21-Feb-19 20:24pm

Reden Rodriguez

Updated 17-Oct-22 6:59am

v3

Add a Solution

2 solutions

Solution 2

IN your SQL code change RAISERROR to: ; THROW 50000, <@YourErrormsg>, 16 ;
Notes on SQL code:
1. if you are not useing semicolons after your statements, THROWS needs one in front of it, else an error.
2. If you want to rollback entire trans (say code is in trigger) before THROW do: SET XACT_ABORT ON. Unlike RAISERROR, THROWS uses XACT_ABORT to detirmine to rollback or not (thus can be used for FYI messages as well if SET XACT_ABORT OFF).
3. Error# needs to be => 50000. Use a specific number if you are using numbers have additional meaning (you could have C# enum on client side in a switch).

In your C# code use try catch with a catch of:
catch (Microsoft.Data.SqlClient.SqlException ex)
At first I had just SqlException ex, but that resolved to System.Data.SqlClient.SqlException, which will NOT have your message. Use full: Microsoft.Data.SqlClient.SqlException.
ex.Messsage will have your custom set message in your sql code.

This took me a while to figure out, especially the Microsoft.Data.SqlClient.SqlException vs Microsoft.Data.SqlClient.SqlException

Posted 17-Oct-22 6:59am

Member 4759547

Comments

Richard Deeming 18-Oct-22 4:56am

What on Earth makes you think the OP was using the Microsoft.Data.SqlClient library, which wasn't even released in preview until three months after they posted the question?!

Particularly since the question was already marked as "solved" back in February 2019?

Stick to answering new questions unless you have something new and interesting to add to the discussion.

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)

OriginalGriff · Accepted Answer · 2019-02-21T20:57:00

You seem confused as to what RAISERROR does: it causes an exception in your C# code,it doesn't return any data to your reader.
And the exception you catch and immediately rethrow means that the exception is passed on up immediately, no dtaa is put into your label because the code to do that is never executed.
Worse, in one case, the code that generates the exception is outside the try...catch block, so even if you actually did anything with the exception (which you don't in any case, just re-throw it) it wouldn't be caught in the method anyway!

List<globalVariables> result = imageBL.getEmployeerecord(name);
try
{
    ...

A non-existent user isn't an error: it's normal processing. It's a spelling mistake by the user maybe - handling it via exceptions is not good practice. And "handling it" by immediately just throwing it again doesn't do anything useful anyway.

Stop and think what you are trying to achieve here: this is very much the wrong approach!