how to insert multiple dynamic row value into the database

Question

 public void create(account_detail c, int jobcard_id)
        {
            SqlConnection con = new SqlConnection(@"Data source =(LocalDB)\v11.0;AttachDbFilename=C:\Users\Wattabyte Inc\Documents\CarInfo.mdf;Integrated Security=True;");
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.Text;
            cmd.Connection = con;
            con.Open();
            foreach (var details in c.Data)
            {
                cmd.CommandText = "insert into child_detail values ('" + jobcard_id + "','" + details.completed_by + "','" + details.reporting_time + "','" + details.cost_activity + "','" + details.spared_part_used + "');";
                cmd.ExecuteNonQuery();
            }

        }

I am using this code but it only taking single value but I want to save the multiple values into the database ?

Have you tried running this in debug mode to see the count of `c.Data`? — James Joyce Alano, Jul 08 '15 at 05:55
If `details` in `c.Data` appears once, then it should only run once. — shapiro yaacov, Jul 08 '15 at 06:05

shapiro yaacov · Answer 1 · 2015-07-08T06:11:03.343

0

Try something like:

string additionalText = string.Empty;
bool needComma = false;

foreach (var details in c.Data)
{
    if (needComma)
        additionalText += ", ";
    else
        needComma = true;

    additionalText += "('" + jobcard_id + "','" + details.completed_by + "','" + details.reporting_time + "','" + details.cost_activity + "','" + details.spared_part_used + "')";
}

cmd.CommandText = "insert into child_detail values " + additionalText + ";";
cmd.ExecuteNonQuery();

Basic idea - prepare the string of teh command in the foreach loop, then execute the command once.

edited Jul 08 '15 at 06:11

answered Jul 08 '15 at 06:02

shapiro yaacov

2,308
2
26
39

"insert into child_detail values ("; is this syntax error – raja Jul 08 '15 at 06:08
Typo fixed (working according to http://stackoverflow.com/questions/452859/inserting-multiple-rows-in-a-single-sql-query ) – shapiro yaacov Jul 08 '15 at 06:10
Take the code, swap the foreach loops, add the required variables, and you have it – shapiro yaacov Jul 08 '15 at 06:17
string additionalText = string.Empty; bool needComma = false; foreach (var details in c.Data) { if (needComma) additionalText += ", "; else needComma = true; additionalText += "('" + jobcard_id + "','" + details.completed_by + "','" + details.reporting_time + "','" + details.cost_activity + "','" + details.spared_part_used + "')"; } its still taking one row value i cant able to find the solution please help me to find the solution – raja Jul 08 '15 at 06:51
What is the count on `c.Data` ? (rows or length or something) – shapiro yaacov Jul 08 '15 at 07:36
Then the issue is in the iteration. Look at the class of account_details, and find in MSDN the right attribute / method for iterating through the list. – shapiro yaacov Jul 08 '15 at 07:47
this is my account detail public class account_detail { public int jobcard_id { get; set; } public Nullable date_job_card { get; set; public String jobcard_preparedby { get; set; } public int estimate_prize { get; set; } public string expectedtime_detail { get; set; } public string compalin { get; set; } public int Carid { get; set; } public List Data { get; set; } public account_detail() { Data = new List(); } – raja Jul 08 '15 at 07:51
Please add this as additional data in the question, not a comment. **This should have been part of the question to begin with** – shapiro yaacov Jul 08 '15 at 07:52
sir i tried many time but i am not able to get multiple values please help? – raja Jul 08 '15 at 07:58

how to insert multiple dynamic row value into the database

1 Answers1