asked on

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

ASKER CERTIFIED SOLUTION

Kiran Sonawane

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

AndyAinscow

>>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).

JoseHidalgo

ASKER

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.

JoseHidalgo

ASKER

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

AndyAinscow

You must keep the connection open between calls yourself.

SOLUTION

Anurag Thakur

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JoseHidalgo

ASKER

Thanks

Best way to save List&lt;objects&gt; to Database

Best way to save List<objects> to Database