inserting multiple record using foreach loop c#

Question

I am creating a web app in which i need to insert multiple records in my database, this is how it is looking

List<string> td = tdate.Split(',').ToList();
//List<string> ps = particular.Split(',').ToList();
int i = 0;
foreach (string t in td)
foreach (string p in ps)
     {
       SqlCommand cmd = new SqlCommand("insert into finalinstructoreexpense(sonvinid,particulars,amount,totalamt,date,utno,paymentid,paymode,issuedate,sondate,trainer,type,bank_id) values(@sonvinid,@particulars,@amount,@totalamt,@date,@utno,@paymentid,@paymode,@issuedate,@sondate,@trainer,@type,@bank_id)", con);
       con.Open();
       cmd.Parameters.Add("@sonvinid", SqlDbType.Int).Value =Convert.ToInt32(id);
       cmd.Parameters.Add("@particulars", SqlDbType.NVarChar).Value = p;
       cmd.Parameters.Add("@amount",SqlDbType.Float).Value=adjamt.Split(',')[i];
       cmd.Parameters.Add("@totalamt", SqlDbType.NVarChar).Value = total;
       cmd.Parameters.Add("@date", SqlDbType.DateTime).Value = date.Split(',')[i];
       cmd.Parameters.Add("@utno", SqlDbType.NVarChar).Value = utrno;
       cmd.Parameters.Add("@paymentid",SqlDbType.NVarChar).Value=paymentid;
       cmd.Parameters.Add("@paymode", SqlDbType.NVarChar).Value = modeofpayment;
       cmd.Parameters.Add("@issuedate", SqlDbType.DateTime).Value = transferdate;
       cmd.Parameters.Add("@sondate", SqlDbType.DateTime).Value = t;
       cmd.Parameters.Add("@trainer", SqlDbType.NVarChar).Value = trainer;
       cmd.Parameters.Add("@type", SqlDbType.NVarChar).Value = typeofadj;
       cmd.Parameters.Add("@bank_id", SqlDbType.Int).Value = Convert.ToInt32(bnkid);
       cmd.ExecuteNonQuery();
       message = "Adjusted Amount Inserted Successfully";
       con.Close();
     }

this problem is i have 4 arrays in the form of string i.e tdate,particular,adjamt,date and the parameters look like

tdate(01-01-2013,03-01-2013)
particular(105,100)
adjamt(500,650)
date(2015,2016)

and this is how i want to enter the records in the database

(01-01-2013,105,500,2015)
(03-01-2013,100,650,2016)

but this is how it is actually inserting in my databas

(01-01-2013,105,500,2015)
(03-01-2013,105,500,2015)

it means only tdate is working properly but the values of other parameters are not changing and inserting the first value only, what i need to do, if i want to enter the records propely

http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad/3653574 — e4c5, Dec 15 '16 at 11:26

score 2 · Accepted Answer · answered Dec 15 '16 at 11:29

You are initialising i to zero:

int i = 0;

but not incrementing it in your loop. So these lines:

   cmd.Parameters.Add("@amount",SqlDbType.Float).Value=adjamt.Split(',')[i];
   cmd.Parameters.Add("@date", SqlDbType.DateTime).Value = date.Split(',')[i];

are always returning the first element of the array.

Add:

i = i++;

to the end of your loop.

You really should split adjamt and date into lists before looping and use those lists inside the loop.

You are also looping over both td and ps. This will add twice as many rows into your table as you want. Remove the inner loop and replace:

   cmd.Parameters.Add("@particulars", SqlDbType.NVarChar).Value = p;

by

   cmd.Parameters.Add("@particulars", SqlDbType.NVarChar).Value = ps[i];

where ps is created outside your loop:

var ps = particular.Split(',').ToList();

inserting multiple record using foreach loop c#

1 Answers1