Link to home
Start Free TrialLog in
Avatar of vituxa
vituxa

asked on

Inserting thousands of records into a MS SQL Database using List

I have this class:
==============================================
   public class Anchor
        {
            public string url;
            public string title;          
        }
==============================================
I am gathering a bunch of data into

==============================================
public static List<Anchor> recListLinksAll = new List<Anchor>();
==============================================


Now by the time I am done recording everything into my recListLinksAll class I need to insert all of these records into a MS SQL database and here is what I am trying to do:

==============================================
conn.Open();
foreach (Anchor anchor in recListAnchorsAll)
            {              
                stSQL+= " INSERT INTO allAnchors ";
                stSQL += " VALUES(  ";
                stSQL += "'" + anchor.url + "',";
                stSQL += "'" + anchor.title.Replace("'", "''") + "'";
                stSQL += ") ";

                SqlCommand cmdInsert = new SqlCommand(stSQL, conn);

                try
                {
                    cmdInsert.ExecuteNonQuery();
                }
                catch (System.Net.WebException e)
                {
                    throw (e);
                }
             
            }
conn.Close();
==============================================

And what happens is it inserts about 4000 records and times out. Can anyone provide me with a more efficient way? Perhaps an XMLSerializer or something like that where I wouldn't have to insert each one at a time, but rather in bulk.
Avatar of Meir Rivkin
Meir Rivkin
Flag of Israel image

Providing you are using SQL Server 2008 you can use Table Valued Parameters with .NET.  Check out the following link and ask if you have any more questions:
http://msdn.microsoft.com/en-us/library/bb510489(v=sql.100).aspx
ASKER CERTIFIED SOLUTION
Avatar of jonnidip
jonnidip
Flag of Italy image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of vituxa
vituxa

ASKER

Thank you! When I am looking for a solution -  I am not looking for links, I AM looking for sample code. Thank you again!
the link has a full example code...
When I am looking for a solution -  I am not looking for links, I AM looking for sample code.
They pray tell why did you not say so?  All you did was ask "Can anyone provide me with a more efficient way?"  We did and you chose to use another approach and that is your choice.
Avatar of vituxa

ASKER

OK. My apologies. You are right. I will be more specific next time.