更新一个SQL表，同时从另一个SQL表中删除

Question

我有两个SQL表(SalesCategory和ProductLine)，每个表都有两个列。产品类别不能同时与销售类别和产品线相关联。但是，用户可以将产品类别ABC更改为与Sales类别123相关联，而不是Product 456。当发生这种情况时，我希望从ProductLine SQL表中移除ProductLine的记录，并使用ProductLine的ID更新SalesCategory。但是，我不知道如何做到这一点，除非为SQL表创建另一个单独的DELETE函数并在保存函数中调用它们。我觉得我投入了太多与这两个SQL表相关的函数.

作为一个重要的附带说明，产品类别不能与多个产品线或多个销售类别相关联。

是否有更好的方法来设置代码，这样我就没有一堆与两个SQL数据库表相关联的函数了？或者这是最好的方式去做事情？

下面是我现在的代码：

    //Get current Product Line and Sales Cateogry data for the current Category.
    //These two functions are called in the Page_Load    
    protected string getProductLine()
    {
        string retVal = "";
        try
        {
            using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["AbleCommerce"].ToString()))
            {
                cn.Open();
                SqlCommand cmd = new SqlCommand("SELECT ProductLine FROM ProductLine WHERE uidCategory = @CategoryID", cn);
                cmd.Parameters.Add(new SqlParameter("@CategoryID", _CategoryId));
                cmd.CommandType = CommandType.Text;
                using (IDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        if (reader["ProductLine"].ToString() != "")
                        {
                            productLineTxt.Text = reader["ProductLine"].ToString();
                            retVal = productLineTxt.Text;
                        }
                        else
                        {
                            retVal = "";
                        }
                    }
                }
                cn.Close();
            }
        }
        catch (Exception ex)
        {
            //
        }
        return retVal;
    }

    protected string getSalesCategory()
    {
            string retVal = "";
            try
            {
                using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["AbleCommerce"].ToString()))
                {
                    cn.Open();
                    SqlCommand cmd = new SqlCommand("SELECT SalesCat FROM SalesCategory WHERE uidCat = @CategoryID", cn);
                    cmd.Parameters.Add(new SqlParameter("@CategoryID", _CategoryId));
                    cmd.CommandType = CommandType.Text;
                    using (IDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            if (reader["SalesCat"].ToString() != "")
                            {
                                salesCatTxt.Text = reader["SalesCat"].ToString();
                                retVal = salesCatTxt.Text;
                            }
                            else
                            {
                                retVal = "";
                            }
                        }
                    }
                    cn.Close();
                }
            }
            catch (Exception x)
            {
                //
            }
        return retVal;
    }

    //These two functions are called in the saveSalesCategory() and saveProductLine() functions respectively. They determine if those save functions should perform an UPDATE or INSERT. This is meant to prevent a Product Category from having association with more than one Product Line or Sales Category
    protected bool salesCatExists()
    {
        bool retVal = true;

        try
        {
            using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["AbleCommerce"].ToString()))
            {
                cn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = cn;
                cmd.CommandText = "SELECT COUNT(*) AS 'Exists' FROM SalesCategory WHERE uidCat = @CategoryID";
                cmd.Parameters.Add(new SqlParameter("@CategoryID", _CategoryId));
                cmd.Parameters.Add(new SqlParameter("@SalesCategory", salesCatTxt.Text));
                cmd.CommandType = CommandType.Text;
                using (IDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        if (Convert.ToInt32(reader["Exists"]) == 0)
                        {

                            retVal = false;
                        }
                        else
                        {
                            retVal = true;
                        }
                    }
                }
                cn.Close();
            }
        }
        catch (Exception x)
        {
            //
        }

        return retVal;
    }

    protected bool productLineExists()
    {
        bool retVal = true;

        try
        {
            using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["AbleCommerce"].ToString()))
            {
                cn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = cn;
                cmd.CommandText = "SELECT COUNT(*) AS 'Exists' FROM ProductLine WHERE uidCategory = @CategoryID";
                cmd.Parameters.Add(new SqlParameter("@CategoryID", _CategoryId));
                cmd.Parameters.Add(new SqlParameter("@ProductLine", productLineTxt.Text));
                cmd.CommandType = CommandType.Text;
                using (IDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        if (Convert.ToInt32(reader["Exists"]) == 0)
                        {

                            retVal = false;
                        }
                        else
                        {
                            retVal = true;
                        }
                    }
                }
                cn.Close();
            }
        }
        catch (Exception x)
        {
           //
        }

        return retVal;
    }

    //Save new or update old Product Line and Sales Category data for the current Category
    protected void saveProductLine()
    {
        try
        {
            string update1 = "UPDATE ProductLine SET ProductLine = @ProductLine WHERE uidCategory = @CategoryID";
            string update2 = "UPDATE ProductLine SET ProductLine = '' AND uidCategory = '' WHERE uidCategory = @CategoryID";
            string insert = "INSERT INTO ProductLine (uidCategory, ProductLine) VALUES(@CategoryID, @ProductLine)";
            using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["AbleCommerce"].ToString()))
            {
                cn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = cn;
                cmd.Parameters.Add(new SqlParameter("@CategoryID", _CategoryId));
                cmd.Parameters.Add(new SqlParameter("@ProductLine", productLineTxt.Text));
                cmd.CommandType = CommandType.Text;

                if (getProductLine() == "")
                {
                    cmd.CommandText = insert;
                }
                else
                {
                    productLineTxt.Text = getProductLine();
                    cmd.CommandText = update;
                }
                cmd.ExecuteNonQuery();
                cn.Close();
            }
        }
        catch (Exception ex)
        {
            //
        }
    }

    protected void saveSalesCategory()
    {
        string update = "UPDATE SalesCategory SET SalesCat = @SalesCategory WHERE uidCat = @CategoryID";
        string insert = "INSERT INTO SalesCategory (uidCat, SalesCat) VALUES(@CategoryID, @SalesCategory)";
        try
        {
            using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["AbleCommerce"].ToString()))
            {
                cn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Parameters.Add(new SqlParameter("@CategoryID", _CategoryId));
                cmd.Parameters.Add(new SqlParameter("@SalesCategory", salesCatTxt.Text));
                cmd.Connection = cn;
                cmd.CommandType = CommandType.Text;

                if (salesCatExists() == false)
                {
                    cmd.CommandText = insert;
                }
                else
                {

                    cmd.CommandText = update;
                }

                cmd.ExecuteNonQuery();
                cn.Close();
            }
        }
        catch (Exception x)
        {
            //
        }
    }

Answer 1

您可以查看SQL 合并语句。我不确定我是否完全理解您希望做什么，以便给出一个代码示例，说明它如何帮助您，但听起来它可能会实现一些与您的目标类似的事情。

例如，它允许您根据给定的键或键集检查一个表(无论是实际表还是表值变量)，然后在记录匹配或不匹配时采取行动。

我想我可以举个简单的例子：

这个查询可以处理saveSalesCategory方法中的大部分逻辑：

MERGE SalesCategory AS T
USING (VALUES ((@CategoryID, @SalesCategory)) AS S (uidCat, SalesCat)
ON (T.uidCat = S.uidCat)
WHEN MATCHED THEN
    UPDATE SET SalesCat = S.SalesCat
WHEN NOT MATCHED THEN
    INSERT (uidCat, SalesCat) VALUES (S.uidCat, S.SalesCat)

正如您注意到的，它检查是否存在任何记录，然后相应地插入或更新。这样就不需要运行salesCatExists()并使用多个TSQL查询。

我知道这不能回答你的问题(我想？)但我希望它至少能引导你朝着正确的方向前进，因为我仍然不太确定你到底在寻找什么。

Answer 2

不能在一个命令中对一个表进行更新，而不能对另一个表进行删除。数据结构似乎是您在这里最努力的地方。如果您的ProductCategory表有一个用于ReferenceType的列，这将非常简单。您可以在一次传递中更新ReferenceType和外键值。使用它们的方式，您已经将其组合在一起，您还将面临引用完整性方面的挑战，因为ProductCategory中的值对于一个表或另一个表来说是一个外来值，这取决于它的类型。

Answer 3

您的代码示例看起来似乎没有尝试完成您描述的所有内容。也许你还没说到删除的那部分。我想几个存储过程可能会有帮助。我将您的几个方法浓缩到一个存储的proc中，您可以为Product做同样的事情。我只是不知道该把删除语句放在哪里。这个方向对吗？如果是这样的话，我们可以找出删除的地方;)

CREATE PROCEDURE SaveSalesCategory
    @CategoryID INT ,
    @SalesCategory INT
AS 
    BEGIN
        DECLARE @SalesCatCount INT = ( SELECT   COUNT(*) AS 'Exists'
                                       FROM     SalesCategory
                                       WHERE    uidCat = @CategoryID
                                     )

        IF @SalesCatCount = 0 
            BEGIN
                INSERT  INTO SalesCategory
                        ( uidCat, SalesCat )
                VALUES  ( @CategoryID, @SalesCategory )
            END
        ELSE 
            BEGIN
                UPDATE  SalesCategory
                SET     SalesCat = @SalesCategory
                WHERE   uidCat = @CategoryID
            END
    END
GO