Saturday, May 14, 2011

Store complete data table in database

Hi All,

Below is a simple method that can store complete datatable to database in a go.

private static bool StoreInDataBase(DataTable dtDetails, string connectionString, string tableName)
    {
        if (dtDetails != null && dtDetails.Rows.Count > 0)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (SqlBulkCopy bulkcopy = new SqlBulkCopy(connection))
                {
                    bulkcopy.DestinationTableName = tableName;
                    try
                    {
                        bulkcopy.WriteToServer(dtDetails);
                    }
                    catch (Exception ex)
                    {
                                        //Log Exception
                        return false;
                    }
                }
                connection.Close();
            }
        }
        return true;
    }

Its better than iterating complete dataTable and writing one record at a time.

About Me

My photo
Delhi, India
Fun, music, travel and nature loving, always smiling, computer addict!!