'Dynamic SQL Query in C#

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;
    }


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source