Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Best way to save List<objects> to Database

Posted on 2011-09-23
7
Medium Priority
?
309 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 1600 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 45

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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

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 45

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 400 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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…

876 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