c# Building Dynamic SQL in c#

Question

Trying to build a Dynamic SQL string in my c# code. Not sure where I'm going wrong with it.

private void Save_Item_Drop_Data()
{
    string uri = string.Empty;
    string strUpdate = string.Empty;
    WBGUpdateValue Update = new WBGUpdateValue();
    Update.Email = Game_Data._User_Account.Email;

    try
    {
        foreach (Base_Item item in Game_Data._Item_Drop)
        {
            //Recipe
            if (item.Name.Contains("Recipe"))
            {
                if (Update_Recipe(item.Name))
                {
                    uri = Game_Data._EndPoint + "api/WhiteboxGaming/Post_UnlockRecipe";
                    strUpdate = strUpdate + "UPDATE Skill_Gemology SET " + item.Name + "= 1 WHERE Email = ''" + Update.Email + "'' ";
                    _txtGameMessage.text = item.Name + " Unlocked";
                }
            }
            //Rune
            else if (item.Name.Contains("Rune") || item.Name == "Polished_Ancient_Stone" || item.Name == "Ancient_Stone")
            {
                uri = Game_Data._EndPoint + "api/WhiteboxGaming/Post_SaveItemdrop";
                strUpdate = strUpdate + "UPDATE Inventory_Runes SET " + item.Name + "=" + item.Name + " + " + item.Count.ToString() + " WHERE Email = ''" + Update.Email + "'' ";
                _txtGameMessage.text = "Found Rune";
            }

            Debug.Log(Update);
        }
    }
    catch (Exception ex)
    {
        Debug.Log(ex);
    }        

    if (Game_Data._Item_Drop.Count > 0)
    {
        Update.Value_1 = strUpdate;
        StartCoroutine(booleanwebrequest(uri, Update));            
    }
    Game_Data._Item_Drop = new List<Base_Item>();
}

The string builds this:

UPDATE Inventory_Runes SET Polished_Ancient_Stone=Polished_Ancient_Stone + 1 WHERE Email = ''[email protected]''

When I run it via SQL I get an error:

Msg 911, Level 16, State 4, Line 74
Database 'UPDATE Inventory_Runes SET Polished_Ancient_Stone=Polished_Ancient_Stone + 1 WHERE Email = 'help' does not exist. Make sure that the name is entered correctly.

I understand what its telling me but I'm not sure how to fix the Email part in my c# code.

Update: now I'm really confused. I have the dynamic SQL correct but it still fails.

Update:

//Rune
            else if (item.Name.Contains("Rune") || item.Name == "Polished_Ancient_Stone" || item.Name == "Ancient_Stone")
            {
                uri = Game_Data._EndPoint + "api/WhiteboxGaming/Post_SaveItemdrop";
                strUpdate = strUpdate + "UPDATE Inventory_Runes SET [" + item.Name + "] = [" + item.Name + "] + " + item.Count.ToString() + " WHERE Email = ''' + @Email + '''";
                _txtGameMessage.text = "Found Rune";
            }

Outputs:

'UPDATE Inventory_Runes SET [Polished_Ancient_Stone] = [Polished_Ancient_Stone] + 4 WHERE Email = ''' + @Email + '''UPDATE Inventory_Runes SET [Rune_sa] = [Rune_sa] + 1 WHERE Email = ''' + @Email + '''UPDATE Inventory_Runes SET [Polished_Ancient_Stone] = [Polished_Ancient_Stone] + 1 WHERE Email = ''' + @Email + '''UPDATE Inventory_Runes SET [Ancient_Stone] = [Ancient_Stone] + 2 WHERE Email = ''' + @Email + '''UPDATE Inventory_Runes SET [Ancient_Stone] = [Ancient_Stone] + 1 WHERE Email = ''' + @Email + ''''

When I tear it apart it looks like this:

UPDATE Inventory_Runes SET [Polished_Ancient_Stone] = [Polished_Ancient_Stone] + 4 WHERE Email = '[email protected]'
UPDATE Inventory_Runes SET [Rune_sa] = [Rune_sa] + 1 WHERE Email = '[email protected]'
UPDATE Inventory_Runes SET [Polished_Ancient_Stone] = [Polished_Ancient_Stone] + 1 WHERE Email = '[email protected]'
UPDATE Inventory_Runes SET [Ancient_Stone] = [Ancient_Stone] + 2 WHERE Email = '[email protected]'
UPDATE Inventory_Runes SET [Ancient_Stone] = [Ancient_Stone] + 1 WHERE Email = '[email protected]'

Can you put a breakpoint after your variable initialisation and check the value of Update.Email? — 4ndy
– 4ndy, Commented Jan 26, 2020 at 22:14
There are several mistakes in that code. You can solve nearly all of them by using parameters and not trying to assemble SQL by string concatenation. — Dour High Arch
– Dour High Arch, Commented Jan 26, 2020 at 22:15
Maybe change ''" + Update.Email + "'' to '" + Update.Email + "'? — devNull
– devNull, Commented Jan 26, 2020 at 22:16
xkcd.com/327 - Use parameters instead of constructing the query in this way — Fabulous
– Fabulous, Commented Jan 26, 2020 at 22:41

ishoo agarwal · Accepted Answer · 2020-01-27 05:44:11Z

0

There could be multiple reasons why this query is failing You are using '''+@Email+''' SQL server changes '' into ' which means when you pass ''' it converts to '' If you want to pass a string variable in you query it should be like

WHERE Email = '" + Update.Email + "'

This way it be passed as string Second option is you could send all the parameter in procedure and according to data can execute you query, It will be much easier to maintain and understand in future

answered Jan 27, 2020 at 5:44

ishoo agarwal

5611 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mholmes Over a year ago

Thanks, I'm going to work on re writing the Dynamic SQL statement tonight based on feedback. I'm at work right now so I cant work on a solution but thank you for the feedback.

mholmes · Accepted Answer · 2020-01-28 04:15:09Z

I found a solution to my issue. Based on suggested feedback I came up with this:

 try
    {
        foreach (Base_Item item in Game_Data._Item_Drop)
        {
            //Recipe
            if (item.Name.Contains("Recipe"))
            {
                if (Update_Recipe(item.Name))
                {
                    uri = Game_Data._EndPoint + "value";
                    Update.Value_1 = "1";//Update.Value_1 + "UPDATE Skill_Gemology SET " + item.Name + "= 1 WHERE Email = ''" + Update.Email + "'' ";
                    Update.Value_2 = "Skill_Gemology";
                    Update.Value_3 = item.Name;
                }
            }
            //Rune
            else if (item.Name.Contains("Rune") || item.Name == "Polished_Ancient_Stone" || item.Name == "Ancient_Stone")
            {
                uri = Game_Data._EndPoint + "value";
                Update.Value_1 = item.Count.ToString();//Update.Value_1 + "UPDATE [dbo].[Inventory_Runes] SET [" + item.Name + "] = [" + item.Name + "] + " + item.Count.ToString() + " WHERE Email = ''" + Update.Email + "''";
                Update.Value_2 = item.Name;
            }

            StartCoroutine(booleanwebrequest(uri, Update));
            Debug.Log(Update);
        }
    }
    catch (Exception ex)
    {
        Debug.Log(ex);
    }

API Backend:

 public bool Save_Item_Drop(string Email, string value, string column)
    {
        try
        {
            SqlConnection SQLConn = new SqlConnection(cn);
            SqlCommand cmd = new SqlCommand("spHere", SQLConn);
            bool Success;

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Email", Email);
            cmd.Parameters.AddWithValue("@Value", value);
            cmd.Parameters.AddWithValue("@Column", column);

            SQLConn.Open();
            Success = Convert.ToBoolean(cmd.ExecuteScalar());
            SQLConn.Close();

            return Success;
        }
        catch (Exception ex)
        {
            string _Product = "White Box Gaming API";
            dynamic _Method = Convert.ToString(System.Reflection.MethodBase.GetCurrentMethod().Name);
            dynamic _Class = Convert.ToString(this.GetType().Name);
            string _Exception = Convert.ToString(ex.ToString());

            Log_Product_Exception(_Product, _Class, _Method, _Exception);
            return false;
        }
    }

SQL:

    ALTER PROCEDURE [dbo].[spHere]
    @Email AS NVARCHAR(500),
    @Value AS NVARCHAR(1000),
    @Column As NVARCHAR(1000)
AS
BEGIN
    DECLARE @Response AS BIT = 0    
    DECLARE @Statement AS NVARCHAR(MAX)

SET @Statement = 'UPDATE Inventory_Runes SET ' + @Column + ' = ' + @Column + ' + ' + @Value + ' WHERE Email = ''' + @Email + ''''

-------Pre Check-----------------------------------------------------------------------------------------------------
    --Runes
    IF Not EXISTS(SELECT Email FROM [dbo].[Inventory_Runes] WHERE Email = @Email) BEGIN
        INSERT INTO [dbo].[Inventory_Runes] (Email) VALUES (@Email)
    END

BEGIN TRY
    EXECUTE sp_executesql @Statement
    SET @Response = 1
END TRY
BEGIN CATCH
    SET @Response = 0
END CATCH

    SELECT @Response
END

Tip: The best practice when assembling object names into dynamic SQL statements is to use QuoteName() to avoid problems with odd names, e.g. New Table with a space or reserved words like From.

Collectives™ on Stack Overflow

c# Building Dynamic SQL in c#

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related