将SQL Server中的存储过程/触发器消息捕获到C＃窗体中 [英] Catching stored procedure/trigger message from SQL server into C# windows forms

问题描述

我创建了一个简单的登录应用程序，如果我输入正确的数据，它将给我我想要的数据，否则如果我输入一个没有记录的数据我的存储过程将执行RAISERROR我的问题是我不知道如何正确捕捉它。



这是我的代码DAL文件夹

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

在我的BAL

in my BAL

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

和我的winform

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

我尝试了什么：



i尝试将一个全局变量放入捕获但当我试图把它放入在winform的标签中，它已经消失了。

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.

推荐答案

你似乎对RAISERROR的作用感到困惑：它会导致你的C＃代码出现异常，它不会返回任何数据给你的读者。

和你捕获的异常和immedia tely rethrow意味着异常立即传递，没有dtaa放入你的标签，因为执行该操作的代码永远不会执行。

更糟糕的是，在一种情况下，生成异常的代码是在 try ... catch 块之外，所以即使你真的对异常做了任何事情（在任何情况下都没有，只是重新抛出它）它就不会无论如何都不会被这个方法所吸引！

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!

这篇关于将SQL Server中的存储过程/触发器消息捕获到C＃窗体中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！