Solved

Best way to save List<objects> to Database

Posted on 2011-09-23
7
279 Views
Last Modified: 2012-08-13
Hi

I have the following code, it takes information from one database and then create pdf with some data and then same other data in another database.

What I will like to know is how can I save the list of objects in dbExternal?
Do I have to a foreach? Is there a more efficient way?

Thanks

public void ExecuteUpdate()
        {
            SaveDataTodb(GetDataFromdbExternal());
        }

        private List<dbExternalQuery> GetDataFromdbExternal()
        {
            try
            {
                cmd = new SqlCommand("SelectNotificationUpdateInformation");
                cmd.CommandType = CommandType.StoredProcedure;

                results = GetListFromCommand(cmd, ConfigurationManager.ConnectionStrings["dbExternal"].ConnectionString);
            }
            catch (Exception ex)
            {
                throw;
            }
            finally
            {
                cmd = null;
            }

            return results;
        }

        private void SaveDataTodb(List<dbExternalQuery> results)
        {
            try
            {
[b]////Here is when I want to understand the best way to save a list<objects>[/b]
               SqlCommand cmd = new SqlCommand("dbExternalQuery_Insert");
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(DBHelper.ToSqlParameter("@Name", dbExternalQuery.Name));
            cmd.Parameters.Add(DBHelper.ToSqlParameter("@ImgPath", dbExternalQuery.ImgPath));
            }
            catch (Exception ex)
            {
                throw;
            }
            finally
            {
                cmd = null;
            }
        }

Open in new window

0
Comment
Question by:JoseHidalgo
7 Comments
 
LVL 17

Accepted Solution

by:
sonawanekiran earned 400 total points
Comment Utility
If your DB is SQL 2005 (or is it 2008) and above, you could serialize the list of objects to XML, then pass the whole XML blob to the SP in a parameter then use MSSQL's XML query capabilities to parse the XML and insert the data into the table.

Sort of a INSERT INTO Person SELECT * FROM XML

That would allow a single SP call and it could be wrapped in a transaction within the SP (rather than crossing process boundaries).
0
 
LVL 44

Expert Comment

by:AndyAinscow
Comment Utility
>>Do I have to a foreach? Is there a more efficient way?


What size of list are we talking about?  10 objects or 10,000,000 objects?

I'd keep it simple and just go with the ForEach (or a for loop) and see if the performance is suitable.  (Somewhere the collection will have to be iterated through, either at your side or the DB side).
0
 

Author Comment

by:JoseHidalgo
Comment Utility
Its about 100.000 records and its only test data. I think that easy we can get 1.000.000. but no more than that.
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

Author Comment

by:JoseHidalgo
Comment Utility
The problem with the foreach its that the ConnetionHelper Class is openning and closing the connection each time

public static string ExecuteValueReturningCommand(SqlCommand cmd, string connection_string) {
            SqlConnection conn = GetConnection(connection_string);
            cmd.Connection = conn;
            string returnValue = null;
            try {
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read()) {
                    returnValue = DBHelper.ToString(reader[0], null);
                }
            }
            catch { }
            finally {
                if (conn.State != ConnectionState.Closed) conn.Close();
            }

            return returnValue;
        }

Open in new window

0
 
LVL 44

Expert Comment

by:AndyAinscow
Comment Utility
You must keep the connection open between calls yourself.
0
 
LVL 26

Assisted Solution

by:Anurag Thakur
Anurag Thakur earned 100 total points
Comment Utility
My suggestion will be to use a XML (serialize the list to an XML, pass it to the DB and the use it in the procedure as mentioned by sonawanekiran)

This way you can do a bulk insert as well (if you don't have master detail relationship and inserting the no. of records you mentioned will be quick

if you use the for each approach it will be slower in first place as the looping will be done in the business object and every time the data will be passed to the DB one by one - so you code will be doing extra processing and more network traffic
0
 

Author Closing Comment

by:JoseHidalgo
Comment Utility
Thanks
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

744 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now