Solved

Best way to save List<objects> to Database

Posted on 2011-09-23
7
282 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
ID: 36585626
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
ID: 36585716
>>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
ID: 36585789
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:JoseHidalgo
ID: 36585846
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
ID: 36585918
You must keep the connection open between calls yourself.
0
 
LVL 26

Assisted Solution

by:Anurag Thakur
Anurag Thakur earned 100 total points
ID: 36586390
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
ID: 36588330
Thanks
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

803 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