如果返回的 SELECT 值为 NULL 或表中不存在，则发送新异常

本文关键字：新异常 异常 不存在 SELECT 返回 值为 NULL 如果 | 更新日期: 2023-09-27 18:32:44

所以我正在使用存储过程从数据库中选择一个注册表，存储过程唯一要做的就是选择...问题是我使用该存储过程来填充我在 Web 服务中返回的数据集。当我想发送异常而不是数据集时，问题就来了，因为存储过程会检查数据库并返回一个空行，该数据集填充的任何内容都没有，并且不会将我发送到异常......现在，我将所有异常保存在同一个数据库的日志表中......我的问题是，如果 SELECT 值为空，我可以转到异常块吗？

这是我的代码

[WebMethod(Description = "Private", EnableSession = false)]
public DataSet M812(string p_transaction)
{
    string extran, enclosure, eDate;
    try
    {
        SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CTSSQL"].ConnectionString);
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "dbo.sp_M812";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@p_trans", SqlDbType.NChar, 18).Value = p_transaction;
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds, "Entradas");
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
        cmd.Connection.Close();
        return ds;
    }
    catch(SqlException Ex)
    {
        SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CTSSQL"].ConnectionString);
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "dbo.sp_reqdataerrorlog";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@p_inTrans", SqlDbType.NChar, 12).Value = p_transaction;
        cmd.Parameters.Add("@vo_enclosure", SqlDbType.VarChar, 250).Direction = ParameterDirection.Output;
        cmd.Parameters.Add("@vo_trans", SqlDbType.VarChar, 10).Direction = ParameterDirection.Output;
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
        cmd.Connection.Close();
        enclosure = "" + cmd.Parameters["@vo_enclosure"].Value;
        extran = "" + cmd.Parameters["@vo_trans"].Value;
        SqlConnection con2 = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CTSSQL"].ConnectionString);
        SqlCommand cmd2 = new SqlCommand();
        cmd2.Connection = con2;
        cmd2.CommandText = "dbo.sp_errorlog";
        cmd2.CommandType = CommandType.StoredProcedure;
        cmd2.Parameters.Add("@p_inTrans", SqlDbType.NChar, 12).Value = p_transaction;
        cmd2.Parameters.Add("@p_enclosure", SqlDbType.NChar, 6).Value = enclosure;
        cmd2.Parameters.Add("@p_trans", SqlDbType.NChar, 18).Value = extran;
        cmd2.Parameters.Add("@p_method", SqlDbType.NChar, 6).Value = "812";
        cmd2.Parameters.Add("@p_message", SqlDbType.NVarChar, 250).Value = "SQL Error: " + Ex.Message;
        cmd2.Parameters.Add("@vo_message", SqlDbType.VarChar, 250).Direction = ParameterDirection.Output;
        cmd2.Parameters.Add("@vo_errorDate", SqlDbType.DateTime).Direction = ParameterDirection.Output;
        con2.Open();
        cmd2.ExecuteNonQuery();
        con2.Close();
        cmd2.Connection.Close();
        eDate = "" + cmd2.Parameters["@vo_errorDate"].Value;
        SqlConnection con3 = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CTSSQL"].ConnectionString);
        SqlCommand cmd3 = new SqlCommand();
        cmd3.Connection = con3;
        cmd3.CommandText = "dbo.sp_selecterrorlog";
        cmd3.CommandType = CommandType.StoredProcedure;
        cmd3.Parameters.Add("@p_trans", SqlDbType.NChar, 18).Value = p_transaction;
        cmd3.Parameters.Add("@p_date", SqlDbType.DateTime).Value = eDate;
        SqlDataAdapter da = new SqlDataAdapter(cmd3);
        DataSet ds = new DataSet();
        da.Fill(ds, "ErrorLog");
        con3.Open();
        cmd3.ExecuteNonQuery();
        con3.Close();
        cmd3.Connection.Close();
        return ds;
    }
    catch (Exception Ex)
    {
        SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CTSSQL"].ConnectionString);
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandText = "dbo.sp_reqdataerrorlog";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@p_inTrans", SqlDbType.NChar, 12).Value = p_transaction;
        cmd.Parameters.Add("@vo_enclosure", SqlDbType.VarChar, 250).Direction = ParameterDirection.Output;
        cmd.Parameters.Add("@vo_trans", SqlDbType.VarChar, 10).Direction = ParameterDirection.Output;
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
        cmd.Connection.Close();
        enclosure = "" + cmd.Parameters["@vo_enclosure"].Value;
        extran = "" + cmd.Parameters["@vo_trans"].Value;
        SqlConnection con2 = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CTSSQL"].ConnectionString);
        SqlCommand cmd2 = new SqlCommand();
        cmd2.Connection = con2;
        cmd2.CommandText = "dbo.sp_errorlog";
        cmd2.CommandType = CommandType.StoredProcedure;
        cmd2.Parameters.Add("@p_inTrans", SqlDbType.NChar, 12).Value = p_transaction;
        cmd2.Parameters.Add("@p_enclosure", SqlDbType.NChar, 6).Value = enclosure;
        cmd2.Parameters.Add("@p_trans", SqlDbType.NChar, 18).Value = extran;
        cmd2.Parameters.Add("@p_method", SqlDbType.NChar, 6).Value = "812";
        cmd2.Parameters.Add("@p_message", SqlDbType.NVarChar, 250).Value = "WEB Error: " + Ex.Message;
        cmd2.Parameters.Add("@vo_message", SqlDbType.VarChar, 250).Direction = ParameterDirection.Output;
        cmd2.Parameters.Add("@vo_errorDate", SqlDbType.DateTime).Direction = ParameterDirection.Output;
        con2.Open();
        cmd2.ExecuteNonQuery();
        con2.Close();
        cmd2.Connection.Close();
        eDate = "" + cmd2.Parameters["@vo_errorDate"].Value;
        SqlConnection con3 = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CTSSQL"].ConnectionString);
        SqlCommand cmd3 = new SqlCommand();
        cmd3.Connection = con3;
        cmd3.CommandText = "dbo.sp_selecterrorlog";
        cmd3.CommandType = CommandType.StoredProcedure;
        cmd3.Parameters.Add("@p_trans", SqlDbType.NChar, 18).Value = p_transaction;
        cmd3.Parameters.Add("@p_date", SqlDbType.DateTime).Value = eDate;
        SqlDataAdapter da = new SqlDataAdapter(cmd3);
        DataSet ds = new DataSet();
        da.Fill(ds, "ErrorLog");
        con3.Open();
        cmd3.ExecuteNonQuery();
        con3.Close();
        cmd3.Connection.Close();
        return ds;
    }
}

如果返回的 SELECT 值为 NULL 或表中不存在，则发送新异常

以下是检查DataSet在任何表中是否有任何行的方法：

bool hasRows = ds.Tables.Cast<DataTable>().Any(table => table.Rows.Count != 0);

现在，您可以选择如何将错误数据返回给调用方：

  1. 将错误构建逻辑拉出到一个单独的方法中，您的异常catch处理程序块和您的if条件都可以调用该方法，如下所示：

    private void BuildAndReturnErrorDataSet()
{
    SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CTSSQL"].ConnectionString);
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = con;
    cmd.CommandText = "dbo.sp_reqdataerrorlog";
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@p_inTrans", SqlDbType.NChar, 12).Value = p_transaction;
    cmd.Parameters.Add("@vo_enclosure", SqlDbType.VarChar, 250).Direction = ParameterDirection.Output;
    cmd.Parameters.Add("@vo_trans", SqlDbType.VarChar, 10).Direction = ParameterDirection.Output;
    con.Open();
    cmd.ExecuteNonQuery();
    con.Close();
    cmd.Connection.Close();
    enclosure = "" + cmd.Parameters["@vo_enclosure"].Value;
    extran = "" + cmd.Parameters["@vo_trans"].Value;
    SqlConnection con2 = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CTSSQL"].ConnectionString);
    SqlCommand cmd2 = new SqlCommand();
    cmd2.Connection = con2;
    cmd2.CommandText = "dbo.sp_errorlog";
    cmd2.CommandType = CommandType.StoredProcedure;
    cmd2.Parameters.Add("@p_inTrans", SqlDbType.NChar, 12).Value = p_transaction;
    cmd2.Parameters.Add("@p_enclosure", SqlDbType.NChar, 6).Value = enclosure;
    cmd2.Parameters.Add("@p_trans", SqlDbType.NChar, 18).Value = extran;
    cmd2.Parameters.Add("@p_method", SqlDbType.NChar, 6).Value = "812";
    cmd2.Parameters.Add("@p_message", SqlDbType.NVarChar, 250).Value = "WEB Error: " + Ex.Message;
    cmd2.Parameters.Add("@vo_message", SqlDbType.VarChar, 250).Direction = ParameterDirection.Output;
    cmd2.Parameters.Add("@vo_errorDate", SqlDbType.DateTime).Direction = ParameterDirection.Output;
    con2.Open();
    cmd2.ExecuteNonQuery();
    con2.Close();
    cmd2.Connection.Close();
    eDate = "" + cmd2.Parameters["@vo_errorDate"].Value;
    SqlConnection con3 = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["CTSSQL"].ConnectionString);
    SqlCommand cmd3 = new SqlCommand();
    cmd3.Connection = con3;
    cmd3.CommandText = "dbo.sp_selecterrorlog";
    cmd3.CommandType = CommandType.StoredProcedure;
    cmd3.Parameters.Add("@p_trans", SqlDbType.NChar, 18).Value = p_transaction;
    cmd3.Parameters.Add("@p_date", SqlDbType.DateTime).Value = eDate;
    SqlDataAdapter da = new SqlDataAdapter(cmd3);
    DataSet ds = new DataSet();
    da.Fill(ds, "ErrorLog");
    con3.Open();
    cmd3.ExecuteNonQuery();
    con3.Close();
    cmd3.Connection.Close();
    return ds;
}
if(!hasRows)
{
    // Call error data set building logic
    BuildAndReturnErrorDataSet();
}
catch (Exception Ex)
{
    // Call error data set building logic
    BuildAndReturnErrorDataSet();
}

  2. 如果 hasRows 为 false，则引发异常 - 不建议这样做，因为这使用异常处理来导致程序控制流，但它在技术上是可行的。

相关文章：