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 = ''help.whiteboxgaming@gmail.com''

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 = 'help.whiteboxgaming@gmail.com'
UPDATE Inventory_Runes SET [Rune_sa] = [Rune_sa] + 1 WHERE Email = 'help.whiteboxgaming@gmail.com'
UPDATE Inventory_Runes SET [Polished_Ancient_Stone] = [Polished_Ancient_Stone] + 1 WHERE Email = 'help.whiteboxgaming@gmail.com'
UPDATE Inventory_Runes SET [Ancient_Stone] = [Ancient_Stone] + 2 WHERE Email = 'help.whiteboxgaming@gmail.com'
UPDATE Inventory_Runes SET [Ancient_Stone] = [Ancient_Stone] + 1 WHERE Email = 'help.whiteboxgaming@gmail.com'

Can you put a breakpoint after your variable initialisation and check the value of `Update.Email`? — 4ndy, Jan 26 '20 at 22:14
https://stackoverflow.com/questions/24491996/dynamic-sql-with-c-sharp-sqlcommand — Prateek Shrivastava, Jan 26 '20 at 22:14
There are several mistakes in that code. You can solve nearly all of them by [using parameters](https://stackoverflow.com/questions/7505808/) and not trying to assemble SQL by string concatenation. — Dour High Arch, Jan 26 '20 at 22:15
Maybe change `''" + Update.Email + "''` to `'" + Update.Email + "'`? — devNull, Jan 26 '20 at 22:16
https://www.xkcd.com/327/ - Use parameters instead of constructing the query in this way — Fabulous, Jan 26 '20 at 22:41
@DevNull I think I tried that earlier it does not match up right. I also thought about using the Email instead of passing email via string update. Also yes I did break point to verify email is being passed in. — mholmes, Jan 26 '20 at 23:07
Error message says that problem is in connection string. Generated dynamic query is used as database name in connection string. Can you show the code which do actual sql query execution, how you are building connection string. — Fabio, Jan 27 '20 at 05:08
Oh goo d catch Fabio, I'll update the post tonight with code. Its in the API. — mholmes, Jan 27 '20 at 15:41

score 0 · Answer 1 · answered Jan 27 '20 at 05:44

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 '20 at 05:44

ishoo agarwal

66
11

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 Jan 27 '20 at 15:30

score 0 · Accepted Answer · answered Jan 28 '20 at 04:15

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()`](https://learn.microsoft.com/en-us/sql/t-sql/functions/quotename-transact-sql) to avoid problems with odd names, e.g. `New Table` with a space or reserved words like `From`. — HABO, Aug 07 '20 at 18:43

c# Building Dynamic SQL in c#

2 Answers2