Best way to save List<objects> to Database

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

JoseHidalgoAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Kiran SonawaneConnect With a Mentor Project LeadCommented:
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
 
AndyAinscowFreelance programmer / ConsultantCommented:
>>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
 
JoseHidalgoAuthor Commented:
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
JoseHidalgoAuthor Commented:
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
 
AndyAinscowFreelance programmer / ConsultantCommented:
You must keep the connection open between calls yourself.
0
 
Anurag ThakurConnect With a Mentor Technical ManagerCommented:
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
 
JoseHidalgoAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.