inserting arraylist values into database

Question

i have used a checkbox list in my project .am storing all the checked items values in arraylist using code below

 ArrayList  services= new ArrayList();
for (int i = 0; i < chkservices.Items.Count; i++)
        {
            if (chkservices.Items[i].Selected == true)
            {
                services.Add(chkservices.Items[i].Text+',');
            }
        }

now the problem is when i insert data in to database instead of data in the arraylist it gets inserted as 'System.Collections.ArrayList' how can i insert all values into database in a single insert statement?

EDIT

inserting into database

con.Open();
        SqlCommand cmd = new SqlCommand("insert into XXX(First_Name,Last_Name,ServicesProvided) values ('" + txtfrstname.Text + "','" + txtlastname.Text + "','" + services + "')", con);
        cmd.ExecuteNonQuery();
        con.Close();

or could anyone provide me a alternative for arraylist..i need to save checked items from checkboxlist and save it in database

it should be saved in database as

  First_Name             Last_name                 ServicesProvided
user1firstname        user1lastname               selectedvalue1,
                                              selectedvalue2,selectedvalue3

I'd imagine that you're calling `ArrayList.ToString()` somewhere, which would give you `System.Collections.ArrayList` — geekchic, Nov 21 '11 at 09:53
Re your edit; ***do not do that***. That is ripe for SQL injection, which is very very bad. You really should parameterise your work with the DB. But also; that is a single insert - it will only create one row; if the number of values does not match the columns, it will fail. — Marc Gravell, Nov 21 '11 at 10:01
actually my insert statement inserts multiple values for the ease of posting i posted only as if am inserting a single value..i suppose that was a bad move — Karthik, Nov 21 '11 at 10:03
with the edit, I think Alexander shows how to do this; my answer was based on a different understanding of the problem, and has been removed — Marc Gravell, Nov 21 '11 at 10:56

Alexander Galkin · Accepted Answer · 2011-11-21T10:13:09.030

2

Why not to concatenate your data using the following code:

 var mydata = String.Join(',', chkservices.Items
   .Where( a => a.Selected).Select( b => b.Text));

So you can add your data as a string.

EDIT: It is a very bad habit of concatenating strings to make a query! Apart from many side effects like in your case here it is a great security breach. Try the parameterized query instead:

     SqlCommand cmd = new SqlCommand(
         @"insert into XXX(First_Name,Last_Name,ServicesProvided) values 
         (@First_Name,@Last_Name,@ServicesProvided")", con); 
      cmd.Parameters.AddWithValue("@ServicesProvided", mydata);
      cmd.Parameters.AddWithValue("@First_Name", frstname.Text);
      cmd.Parameters.AddWithValue("@Last_Name", txtlastname.Text);
    cmd.ExecuteNonQuery();

mydata is the variable from my first example.

edited Nov 21 '11 at 10:13

answered Nov 21 '11 at 09:53

Alexander Galkin

12,086
12
63
115

@Alexander check my edited insertion query it has multiple fields..will parameterized query work – Karthik Nov 21 '11 at 10:08
@MarcGravell: As long as we do not insert the concatenated string directly into SQL but use parameterized queries there is no danger to concatenate. Especially taking into account that we're doing it on the server-side with the items from a drop-down list. – Alexander Galkin Nov 21 '11 at 10:14
@AlexanderGalkin my comment (and answer) was based on an incorrect understanding of the question, which the OP has now clarified with an edit. With the edit, your code as above is ideal. – Marc Gravell Nov 21 '11 at 10:55

score 0 · Answer 2 · edited May 23 '17 at 12:11

0

You need to get the values of the array list and send them one by one

or create a stored procedure where you send all the values to using Alexanders Galkins example (or use the a Aggregate method). Then use the split function to split up the string and insert all the record

edited May 23 '17 at 12:11

Community

1
1

answered Nov 21 '11 at 09:58

Ivo

3,406
4
33
56

score 0 · Answer 3 · answered Nov 21 '11 at 09:58

Using INSERT INTO statement you can insert only one row at a time unless you're using sub query to select data from other table.

As you don't have the data in the database, your only option is iterate over the array and insert each value as new row.

Don't use ArrayList, you have generic list for what you need:

List<string> services = new List<string>();
for (int i = 0; i < chkservices.Items.Count; i++)
{
    if (chkservices.Items[i].Selected == true)
    {
        services.Add(chkservices.Items[i].Text);
    }
}

//...connection stuff....
strSQL = "INSERT INTO MyTable (MyField) VALUES (@val)"
using (SqlCommand command = new SqlCommand(strSQL, connection))
{
    command.Parameters.AddWithValue("@val", "");
    foreach (string service in services)
    {
        command.Parameters["@val"].Value = service;
        command.ExecuteNonQuery();
    }
}

i need to insert multiple values at same time will parameterized queries works in that case — Karthik, Nov 21 '11 at 10:12

score 0 · Answer 4 · answered Nov 21 '11 at 10:05

How many checkbox do you have? If you just have a little checkbox, so I suggest you transform each state of them into bit mask which represent a number, then store it to database.

long bitMask = 0; // All uncheck
for (int i = 0; i < chkServices.Items.Count; ++i) {
    if (chkServices.Items[i].Checked) {
        bitMask |= (1 << i);
    }
}

// Store bitMask to Database

In later, you can get state via bitMask again when needed.

inserting arraylist values into database

4 Answers4