CODEWITHSUNDEEP
c#sqlsql-server
Dejsa Cocan
Dejsa Cocan

Reputation: 1569

Update one SQL table while Deleting from another

I have two SQL tables (SalesCategory and ProductLine), each with two columns. A product category cannot be associated with a Sales Category and a Product Line at the same time. Users can, however, change, for example, Product Category ABC to be associated with Sales Category 123 instead of Product Line 456. When something like this happens, I want to remove the record of Product Category ABC from the ProductLine SQL table and UPDATE the SalesCategory with the ID of Product Category ABC. But I am not sure how to do this without making another separate DELETE function and calling them inside the save function for the SQL table in question. I feel like I'm putting in too many functions related to these 2 SQL tables....

As an important side note, Product Categories cannot be associated with more than one Product Line or more than one Sales Category.

Is there a better way to setup the code so I don't have a bunch of functions floating around associated with two SQL database tables? Or is this the best way to go about things?

Here is my code as it is now:

    //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)
        {
            //
        }
    }

Upvotes: 0

Views: 160

Answers (5)

Brad
Brad

Reputation: 12255

You could put a trigger on both of those tables so that when you insert a new record or update an existing record the DB looks to your other table for that category and deletes that record.

Not all of your parameters were used in your example code and it's kind of long so I don't think this code, as written, will do exactly what you need but the concept I think should work.

CREATE TRIGGER dbo.TRG_SalesCategory_RECORD
   ON  SalesCategory 
   AFTER INSERT, UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    Delete from ProductLine
    Where CategoryID = (select CategoryID from INSERTED)

END
GO

CREATE TRIGGER dbo.TRG_ProductLine_RECORD
   ON  ProductLine 
   AFTER INSERT, UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    Delete from SalesCategory
    Where CategoryID = (select CategoryID from INSERTED)

END
GO

Upvotes: 0

Anon
Anon

Reputation: 10908

Use an OUTPUT clause (MSDN)

DELETE ProductLine
WHERE uidCategory = @CategoryID
OUTPUT column1,column2,etc INTO SalesCategory

or

DELETE SalesCategory
WHERE uidCategory = @CategoryID
OUTPUT column1,column2,etc INTO ProductLine

Upvotes: 0

Matthew Haugen
Matthew Haugen

Reputation: 13286

You might look into the SQL MERGE statement. I'm not sure I completely understand what you're hoping to do enough to give a code example of how it might help you, but that sounds like it might do something like your goal.

It'll let you, for instance, check one table (whether an actual table or just a table-valued-variable) against another given a key or set of keys, then take action when records match or when they don't match.

I suppose I can give a quick example, though:

This query could handle much of the logic in your saveSalesCategory method:

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)

As you'll notice, it checks to see whether any records exist, then inserts or updates accordingly. This gets rid of your need to run salesCatExists() and use multiple TSQL queries.

I recognize that this doesn't answer your question (I think?) but I hope it at least guides you a bit in the right direction, since I'm still not overly sure what exactly you are looking for.

Upvotes: 1

Relevant
Relevant

Reputation: 169

Your code sample looks like it isn't trying to do everything that you descriptions states. Maybe you just hadn't gotten to the delete part. I think a couple of stored procedures might be helpful. I condensed a couple of your methods into one stored proc, and you can do the same thing for the Product Line. I just wasn't sure where to put the DELETE statement. Is this the right direction? If so, we can figure out where to put that delete ;)

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

Upvotes: 0

Sean Lange
Sean Lange

Reputation: 33581

You can't have an update to one table and a delete to another table like this in a single command. It seems that the data structure is what you are fighting the most here. If your ProductCategory table had a column for ReferenceType this would be pretty simple. You would be able to update the ReferenceType and the foreign key value in a single pass. With they way you have put this together you also are going to have challenges with referential integrity because the value in ProductCategory would be a foreign to one or the other table depending on which type it is.

Upvotes: 0

Related Questions