'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 |
---|