Insert arraylist into database C# webservice

Question

I am creating a web app in which I need to insert into database some values. Here is my webservice for the same:

[WebMethod]
[ScriptMethod(UseHttpGet = true)]
public void saverecd(string id, string particular,string amt,string tdate, string total, string date, string utrno, string modeofpayment, string transferdate,string trainer, string typeofadj)
{
    sqlq = "";
    sqlq = "insert into finalinstructoreexpense(sonvinid,particulars,amount,totalamt,date,utno,paymentid,paymode,issuedate,sondate,trainer,type,bank_id) values('" + id + "','" + particular + "','" + amt + "','" + total + "',convert(datetime,'" + date + "',105),'" + utrno + "','" + paymentid + "','" + modeofpayment + "',convert(datetime,'" + transferdate + "',105),convert(datetime,'" + tdate + "',105),'" + trainer + "','" + typeofadj + "',null)";
    con.Open();
    SqlCommand comm1 = new SqlCommand(sqlq, con);
    comm1.ExecuteNonQuery();
    message = "Adjusted Amount Inserted Successfully";
    con.Close();
    Context.Response.Write(message);
}

Here I am getting particular as 13111300002,13111300001, I want to store this values separately in my database:

1531    20667   13111300002,13111300001 200 200 2013-12-15 00:00:00.000 test    1312150001  Online  2013-01-01 00:00:00.000 2013-11-13 00:00:00.000 Ibrahim shaikh  split   NULL

This is how my data is storing in database:

1531    20667   13111300002 200 200 2013-12-15 00:00:00.000 test    1312150001  Online  2013-01-01 00:00:00.000 2013-11-13 00:00:00.000 Ibrahim shaikh  split   NULL
1531    20667   13111300001 200 200 2013-12-15 00:00:00.000 test    1312150001  Online  2013-01-01 00:00:00.000 2013-11-13 00:00:00.000 Ibrahim shaikh  split   NULL

I want to store my data like this.

What I need to do here?

Sorry for bad explanation

First and foremost is you need to change the query to Parameterized Query. — Mohit S, Dec 15 '16 at 06:35

score 0 · Answer 1 · answered Dec 15 '16 at 06:47

0

You could split your particular variable with comma separator and make multiple database calls to insert the data in the way you want.

string[] particulars = particular.split(',');

for(int i=0; i < particulars.Length; i++)
{
// here you could make a database call to insert row into the database
}

answered Dec 15 '16 at 06:47

Nikunj Kakadiya

2,689
2
20
35

thank you sir, but what if my amount is also coming in array, what i need to do then – Dec 15 '16 at 06:55
initially it was not mentioned that amount would also be in array but the solution you gave would work if the amount is also in array and have the same number of elements as particulars. – Nikunj Kakadiya Dec 15 '16 at 07:46

score 0 · Accepted Answer · edited May 23 '17 at 11:45

First of all you need to change the Query to Parameterized Query, since it is prone to SQL Injection. and then you can split the string coming with , and run a loop to get the values in the DB.

public void saverecd(string id, string particular,string amt,string tdate, string total, string date, string utrno, string modeofpayment, string transferdate,string trainer, string typeofadj)
{
    List<string> sp = particular.split(',');
    int i = 0;
    foreach(string s in sp)
    {
        using (SqlConnection connection = new SqlConnection(/* connection info */))
        {
            sqlq = "insert into finalinstructoreexpense(sonvinid,particulars,amount,totalamt,date,utno,paymentid,paymode,issuedate,sondate,trainer,type,bank_id) values(@id,@s,@amt,@total,@dt,@utrno,@paymentid,@modeofpayment,@transferdate,@tdate,@trainer,@typeofadj,null)";
            connection.Open();
            using (SqlCommand comm1 = new SqlCommand(sql, connection))
            {
                comm1.Parameters.Add("@id",SqlDbType.Int).value=id;
                comm1.Parameters.Add("@s",SqlDbType.Varchar, 50).value =s;
                comm1.Parameters.Add("@amt",SqlDbType.Varchar, 50).value =amt.Split(',')[i];;
                comm1.Parameters.Add("@trainer",SqlDbType.Varchar, 50).value =trainer;
                comm1.Parameters.Add("@dt", SqlDbType.Date).Value = date;
                //You can add all your Parameters here
                // I have added 3 types of as Int, Varchar and Date to give the idea
                comm1.ExecuteNonQuery();
                message = "Adjusted Amount Inserted Successfully";
                Context.Response.Write(message);
                i++;
            }
        }
    }
}

thank you sir, but what if my amount is also coming in array, what i need to do then — , Dec 15 '16 at 06:53
can u give the example of amount and Particulars ?? how does it look ??? — Mohit S, Dec 15 '16 at 06:54
i know i need to split that as well but if i split that i think it will effect the insert query — , Dec 15 '16 at 07:00
@MumbaiWadala Another way of saying thanks on SO is that you can upvote the answer and if this answer has solved your question please consider [accepting it](http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work) by clicking the check-mark. This indicates to the wider community that you've found a solution and gives some reputation to both the answerer and yourself. There is no obligation to do this. — Mohit S, Dec 15 '16 at 07:09

Insert arraylist into database C# webservice

2 Answers2