Dynamic SQL Query in C#

Question

I'm trying to create a query with a variable number of parameters, giving to the program the name of the table and an Array for the names of parameters and another array for their values. It seems to do the work but at the instruction ExecuteScalar() it generates an error:

incorrect syntax near nomeParamA...

Why? The code :

 public int inserisciDato(clsParametriQuery param)
    {
        int indice = -1;
        try
        {
            cmd = new SqlCommand();
            cmd.Connection = cn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "INSERT INTO @tabella (";
            cmd.Parameters.AddWithValue("@tabella", param.nomeTabella);
            for (int i = 0; i < param.nomeParametri.Length; i++)
            {
                cmd.CommandText += "@nomeParam" + ((char)(i + 65)).ToString();
                if (i != param.nomeParametri.Length - 1)
                    cmd.CommandText += ",";
                //cmd.Parameters.AddWithValue("@nomeParam" + ((char)(i + 65)).ToString(), param.nomeParametri[i].ToString());
            }
            cmd.CommandText += ") values (";
            for (int i = 0; i < param.valoreParametri.Length; i++)
            {
                cmd.CommandText += "'@valParam" + ((char)(i + 65)).ToString() + "'";
                if (i != param.valoreParametri.Length - 1)
                    cmd.CommandText += ",";
                //cmd.Parameters.AddWithValue("@valParam" + ((char)(i + 65)).ToString(), param.valoreParametri[i].ToString());
            }

            //addWithValue
            for (int i = 0; i < param.nomeParametri.Length; i++)
            {
                cmd.Parameters.AddWithValue("@nomeParam" + ((char)(i + 65)).ToString(), param.nomeParametri[i].ToString());
                cmd.Parameters.AddWithValue("@valParam" + ((char)(i + 65)).ToString(), param.valoreParametri[i].ToString());
            }
            cmd.CommandText += ") select scope_identity()";
            indice = Convert.ToInt32(cmd.ExecuteScalar());
        }
        catch (Exception ex)
        {
            Console.WriteLine("Error: " + ex.Message);
        }
        return indice;

Edit: Thanks to the comment: -Table name and columns name must be written in the query and not as parameters -'nameOfParamater' is not correct. You have to write just nameOfParameter Now it works pretty good

public int inserisciDato(clsParametriQuery param)
    {
        //Controlla : https://stackoverflow.com/questions/62192753/dinamic-query-c-sharp
        int indice = -1;
        try
        {
            cmd = new SqlCommand();
            cmd.Connection = cn;//connessione gia istanziata
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "INSERT INTO " + param.NomeTabella + " (";
            //cmd.Parameters.AddWithValue("@tabella", param.nomeTabella);
            for (int i = 0; i < param.NomeParametri.Length; i++)
            {
                cmd.CommandText += param.NomeParametri[i];
                if (i != param.NomeParametri.Length - 1)//changed
                    cmd.CommandText += ",";
            }
            cmd.CommandText += ") values (";
            for (int i = 0; i < param.ValoreParametri.Length; i++)
            {
                if (param.NomeTabella.ToUpper() == "CONNESSIONE")
                    cmd.CommandText += "@valParam" + ((char)(i + 65)).ToString();//changed
                else
                    cmd.CommandText += "@valParam" + ((char)(i + 65)).ToString();
                if (i != param.ValoreParametri.Length - 1)
                    cmd.CommandText += ",";
            }

            for (int i = 0; i < param.NomeParametri.Length; i++)
            {
                string p1 = "@valParam" + ((char)(i + 65)).ToString();
                string p2 = param.ValoreParametri[i].ToString();//use variable
                cmd.Parameters.AddWithValue(p1, p2);
            }
            cmd.CommandText += ") select scope_identity()";         
            indice = Convert.ToInt32(cmd.ExecuteScalar());
        }
        catch (Exception ex)
        {
            Console.WriteLine("Error: " + ex.Message);
        }
        return indice;
    }

You can not have table name as a parameter in the query. Parameters are only for the passing values to queries, not to pass database items in the queries such as table names and column names. — Chetan, Jun 04 '20 at 10:51
So the table name must be written on the query? Not as parameters? — BlackHole, Jun 04 '20 at 10:52
But, i dont undurstand in this case i have modify the column name and assign the real name and the real value. It's the same of using a query with colName1, colName2 and val1, val2. Just done dinamically, — BlackHole, Jun 04 '20 at 10:54
ExecuteScalar() one returns the first column from the Select statement. You want ExecuteNonQuery() — jdweng, Jun 04 '20 at 10:55
What do you mean by the index? The SQL Server is multi-threaded and data is not read/and stored in parallel threads. So index does not mean anything. The return value from ExecuteScalar is only valid when you do a SELECT and you are doing an INSERT. — jdweng, Jun 04 '20 at 11:02
@jdweng i am adding a record to a table and i need to know his 'id' — BlackHole, Jun 04 '20 at 11:09
Parameters/Variables cannot be used to replace the name of an object. `SELECT * FROM @TableName` would try to return data from the table variable `@TableName` not the table which has the name of the value *stored* in the scalar variable `@TableName`. If you do need dynamic statements like this, it *normally* implies a design flaw. When you do though, you have to ***safely*** inject the value into the statement. — Thom A, Jun 04 '20 at 11:21
https://stackoverflow.com/questions/18373461/execute-insert-command-and-return-inserted-id-in-sql — Chetan, Jun 04 '20 at 11:28
https://www.aspsnippets.com/Articles/Return-Identity-Auto-Increment-Column-value-after-record-insert-in-SQL-Server-Database-using-ADONet-with-C-and-VBNet.aspx — Chetan, Jun 04 '20 at 11:29
@ChetanRanpariya i followed your comments and now it works without the previous problem. Thank you . How can i set this question to "Solved" thanks to your comment? — BlackHole, Jun 04 '20 at 13:15

Dynamic SQL Query in C#

0 Answers0