Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Inserting thousands of records into a MS SQL Database using List

Posted on 2013-01-29
7
300 Views
Last Modified: 2013-01-30
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.
0
Comment
Question by:vituxa
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 42

Expert Comment

by:sedgwick
ID: 38832972
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38833510
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
0
 
LVL 13

Accepted Solution

by:
jonnidip earned 500 total points
ID: 38834341
You may use the SqlBulkCopy class:
DataTable dt = new DataTable();
dt.Columns.Add("url", typeof(String));
dt.Columns.Add("title", typeof(String));

// This is just to add a sample element for your Anchor object
List<Anchor> recListLinksAll = new List<Anchor>();
recListLinksAll.Add(new Anchor() { url = "test_url", title = "test_title" });
// end - sample element

foreach (Anchor a in recListLinksAll)
    dt.Rows.Add(new Object[2] { a.url, a.title });
            
SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(new SqlConnection("myConnectionString"), SqlBulkCopyOptions.TableLock, null);
sqlBulkCopy.DestinationTableName = "TableName";
sqlBulkCopy.WriteToServer(dt);

Open in new window


Regards.
0
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
LVL 1

Author Closing Comment

by:vituxa
ID: 38835188
Thank you! When I am looking for a solution -  I am not looking for links, I AM looking for sample code. Thank you again!
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 38835198
the link has a full example code...
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38835258
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.
0
 
LVL 1

Author Comment

by:vituxa
ID: 38835323
OK. My apologies. You are right. I will be more specific next time.
0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

791 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question