create a dynamic query using cmd.parameter c#

Question

I know there are many questions related to create a dynamic query but no one can help me. I am trying to create a dynamic cmd.Parameters.AddWithValue with the help of foreach loop but I can't find all the values that I pass in foeach. I know I am writing var values = " " that's way the values give me null value

I am providing my code may that help you to solve an error

  public void insert_Para(System.Web.UI.HtmlControls.HtmlGenericControl ControlName, String TableName)
    {
        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ToString()))
        {
            var values = "";
            SqlCommand cmd = new SqlCommand("insert into "+ TableName +" values(" + values + ")", con);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            foreach (var item in ControlName.Controls)
            {
                if (item is TextBox)
                {
                    cmd.Parameters.AddWithValue("@" + ((TextBox)item).ID, ((TextBox)item).Text);
                    values += "@" + ((TextBox)item).ID + ",";
                }
            }

            if (con.State == ConnectionState.Closed)
                con.Open();
            cmd.ExecuteNonQuery();
        }
    }

The problem is that the string to create the insert statement was already set. If you change `values` you won't change the string from the SqlCommand — hardkoded, Sep 04 '18 at 11:09
@MarcGravell the query itself is just an `INSERT INTO SomeTable VALUES()` without any parameters *or* values. The conditional block adds a parameter without actualy changing the query. — Panagiotis Kanavos, Sep 04 '18 at 11:09
This code is neither dynamic nor does it use any parameters. `values` is empty when the query is constructed which means the SqlCommand object tries to execute an invalid query against some table. — Panagiotis Kanavos, Sep 04 '18 at 11:10
@PanagiotisKanavos it was clearly *intended* to be parameterized, contrary to the comment that I was replying to; the fact that it doesn't work shouldn't surprise us - if the code was working as expected, we rarely see a question here about that :) — Marc Gravell, Sep 04 '18 at 11:13
as a side note: you're going to be *very* vulnerable to the order of the controls if this is how you create the SQL - right now it is assumed that the control order exactly matches the column order — Marc Gravell, Sep 04 '18 at 11:14
you right @panagiotis-kanavos query itself is just an INSERT INTO `SomeTable VALUES()` but want values in `values` parameter. for that what can I do? — Minhaj Patel, Sep 04 '18 at 11:14

score 8 · Accepted Answer · answered Sep 04 '18 at 11:11

The problem here is mostly one of timing. You are currently concatenating values into the string right at the start, when it is an empty string. Once you've done that: that's it - that's the SQL you've created:

insert into Foo values()

which isn't going to help. I would either defer that concatenation until after the loop:

foreach (var item in ControlName.Controls)
{
    if (item is TextBox)
    {
        cmd.Parameters.AddWithValue("@" + ((TextBox)item).ID, ((TextBox)item).Text);
        values += "@" + ((TextBox)item).ID + ",";
    }
}
cmd.CommandText = "insert into "+ TableName +" values(" + values + ")";

or I'd use a StringBuilder throughout:

var sql = new StringBuilder("insert into [").Append(TableName)
              .Append("] values(");
foreach (var item in ControlName.Controls)
{
    if (item is TextBox)
    {
        cmd.Parameters.AddWithValue("@" + ((TextBox)item).ID, ((TextBox)item).Text);
        sql.Append("@").Append(((TextBox)item).ID).Append(",");
    }
}
cmd.CommandText = sql.Append(")").ToString();

Note also: this is CommandType.CommandText, not CommandType.StoredProcedure.

nisha · Answer 2 · 2018-09-07T06:58:36.747

Please Try thi code.

  public void Insert_Data_generic(DataSet dsins)
  {
        SqlConnection con = new SqlConnection(connectionstring);
        string Result = "insert into TableName (";
        SqlCommand cmd = new SqlCommand();    
        con.Open();
        string columns = string.Join(",", dsins.Tables[0].Columns.Cast<DataColumn>().Select(c => c.ColumnName));
        string values = string.Join(",", dsins.Tables[0].Columns.Cast<DataColumn>().Select(c => string.Format("@{0}", c.ColumnName)));

        Result += columns + ") values(" + values + ")";
        foreach (DataRow row in dsins.Tables[0].Rows)
        {
            cmd = new SqlCommand(Result, con);
            cmd.Parameters.Clear();
            foreach (DataColumn col in dsins.Tables[0].Columns)
            {
                cmd.Parameters.AddWithValue("@" + col.ColumnName, row[col]);

            }
            cmd.ExecuteNonQuery();
        }
     }

You assume @Minhaj Patel has a DataSet. This also requires additional calls to the database to get the meta-data (column names). You are not supplying the values from the TextBox in the OP. You are not disposing of SqlConnection or SqlCommand. — Polyfun, Sep 04 '18 at 11:48

create a dynamic query using cmd.parameter c#

2 Answers2