How to insert multiple list in SQL Server 2008 using c#?

Question

My table contains 10 columns. I need to insert a list using c#.
I have stored the details of multiple members, for each count its has to insert the consecutive details in the same row.

  if (members.Count >= 1)

    {
         foreach (Members myList in members)
             {                             
            Command.Parameters.Add("first", SqlDbType.VarChar).Value = myList.first;
            Command.Parameters.Add("last", SqlDbType.VarChar).Value = myList.last;
            Command.Parameters.Add("age", SqlDbType.VarChar).Value = myList.age;
             }
    }

Example : for count=1 the table looks like "fName1","lName1",21

for count=2 the table looks like "fName1","lName1",21,"fname2","lName2",21

please help on this.

from your example count=2 do you mean it should insert into col1...col6 and for count=3 it will be col1...col9? If that is true it's a bad design. Each member should be on separate rows — codingbiz
– codingbiz, Commented Sep 17, 2012 at 8:16
@user1671639 give us a pastebin of the code you are using, if you want a good answer. — classicjonesynz
– classicjonesynz, Commented Sep 18, 2012 at 3:15
@user1671639 :: Can you post a sample list? I mean to say how the list looks before reaching the foreach loop portion. and based on that list give us how should the tabel will look like. — Krishanu Dey
– Krishanu Dey, Commented Sep 18, 2012 at 3:47

Typist · Accepted Answer · 2012-09-18 04:20:22Z

The coding style looks ambiguous. Your foreach loop runs for - 'Members' in members. It makes hard to understand what are trying to do. Let me suggest you to refactor your code and let the class name be 'Member'. You can put members in db with ADO.Net (there are other ways too) as follows -

       using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = connection.CreateCommand())
            {
                //select just schema of the table.
                command.CommandText = "select * from members where 1=2;";
                using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                {
                    using (SqlCommandBuilder builder = new SqlCommandBuilder(adapter))
                    {
                        using (DataTable dt = new DataTable())
                        {
                            foreach (Member item in memebers)
                            {
                                DataRow row = dt.NewRow();

                                row.SetField<string>("", item.FirstName);
                                row.SetField<string>("", item.LastName);
                                row.SetField<int>("", item.Age);
                                //
                                // number of SetField should be equal to number of selected columns.
                                //
                                dt.Rows.Add(row);
                            }
                            adapter.Update(dt);
                        }                         
                    }   
                }
            }
        }

Collectives™ on Stack Overflow

How to insert multiple list in SQL Server 2008 using c#?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related