Dealing with huge amount of data when inserting into sql database

Question

in my code the user can upload an excel document wish contains it's phone contact list.Me as a developer should read that excel file turn it into a dataTable and insert it into the database . The Problem is that some clients have a huge amount of contacts like saying 5000 and more contacts and when i am trying to insert this amount of data into the database it's crashing and giving me a timeout exception. What would be the best way to avoid this kind of exception and is their any code that can reduce the time of the insert statement so the user don't wait too long ?

the code

public SqlConnection connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
public void Insert(string InsertQuery)
{
    SqlDataAdapter adp = new SqlDataAdapter();
    adp.InsertCommand = new SqlCommand(InsertQuery, connection);
    if (connection.State == System.Data.ConnectionState.Closed)
    {
        connection.Open();
    }
    adp.InsertCommand.ExecuteNonQuery();
    connection.Close();
}

protected void submit_Click(object sender, EventArgs e) 
{
    string UploadFolder = "Savedfiles/";
    if (Upload.HasFile) {
        string fileName = Upload.PostedFile.FileName;
        string path=Server.MapPath(UploadFolder+fileName);
        Upload.SaveAs(path);
        Msg.Text = "successfully uploaded";
        DataTable ValuesDt = new DataTable();
        ValuesDt = ConvertExcelFileToDataTable(path);
        Session["valuesdt"] = ValuesDt;
        Excel_grd.DataSource = ValuesDt;
        Excel_grd.DataBind();


    }
}

protected void SendToServer_Click(object sender, EventArgs e)
{
    DataTable Values = Session["valuesdt"] as DataTable ;
    if(Values.Rows.Count>0)
    {
        DataTable dv = Values.DefaultView.ToTable(true, "Mobile1", "Mobile2", "Tel", "Category");
        double Mobile1,Mobile2,Tel;string Category="";
        for (int i = 0; i < Values.Rows.Count; i++)
       {
            Mobile1 =Values.Rows[i]["Mobile1"].ToString()==""?0: double.Parse(Values.Rows[i]["Mobile1"].ToString());
            Mobile2 = Values.Rows[i]["Mobile2"].ToString() == "" ? 0 : double.Parse(Values.Rows[i]["Mobile2"].ToString());
            Tel = Values.Rows[i]["Tel"].ToString() == "" ? 0 : double.Parse(Values.Rows[i]["Tel"].ToString());

           Category = Values.Rows[i]["Category"].ToString();
           Insert("INSERT INTO client(Mobile1,Mobile2,Tel,Category) VALUES(" + Mobile1 + "," + Mobile2 + "," + Tel + ",'" + Category + "')");
           Msg.Text = "Submitied successfully to the server ";
       }



    }

}

Answer 1

I know this is a super old post, but you should not need to use the bulk operations explained in the existing answers for 5000 inserts. Your performance is suffering so much because you close and reopen the connection for each row insert. Here is some code I have used in the past that keeps one connection open and executes as many commands as needed to push all the data to the DB:

public static class DataWorker
{
    public static Func<IEnumerable<T>, Task> GetStoredProcedureWorker<T>(Func<SqlConnection> connectionSource, string storedProcedureName, Func<T, IEnumerable<(string paramName, object paramValue)>> parameterizer)
    {

        if (connectionSource is null) throw new ArgumentNullException(nameof(connectionSource));

        SqlConnection openConnection()
        {
            var conn = connectionSource() ?? throw new ArgumentNullException(nameof(connectionSource), $"Connection from {nameof(connectionSource)} cannot be null");
            var connState = conn.State;

            if (connState != ConnectionState.Open)
            {
                conn.Open();
            }

            return conn;
        }

        async Task DoStoredProcedureWork(IEnumerable<T> workData)
        {
            using (var connection = openConnection())
            using (var command = connection.CreateCommand())
            {
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = storedProcedureName;

                command.Prepare();

                foreach (var thing in workData)
                {
                    command.Parameters.Clear();

                    foreach (var (paramName, paramValue) in parameterizer(thing))
                    {
                        command.Parameters.AddWithValue(paramName, paramValue ?? DBNull.Value);
                    }

                    await command.ExecuteNonQueryAsync().ConfigureAwait(false);
                }
            }
        }

        return DoStoredProcedureWork;
    }
}

This was actually from a project where I was gathering emails for a restriction list, so kind of relevant example of what a parameterizer argument might look like and how to use the above code:

        IEnumerable<(string,object)> RestrictionToParameter(EmailRestriction emailRestriction)
        {
            yield return ("@emailAddress", emailRestriction.Email);
            yield return ("@reason", emailRestriction.Reason);
            yield return ("@restrictionType", emailRestriction.RestrictionType);
            yield return ("@dateTime", emailRestriction.Date);
        }

        var worker = DataWorker.GetStoredProcedureWorker<EmailRestriction>(ConnectionFactory, @"[emaildata].[AddRestrictedEmail]", RestrictionToParameter);


        await worker(emailRestrictions).ConfigureAwait(false);

Answer 2

You can use SqlBulkCopy which is more work, or you can use the batch update feature of the SqlAdpater. Instead of creating your own insert statement, then building a sqladapter, and then manually executing it, create a dataset, fill it, create one sqldataadpater, set the number of inserts in a batch, then execute the adapter once.

I could repeat the code, but this article shows exactly how to do it: http://msdn.microsoft.com/en-us/library/kbbwt18a%28v=vs.80%29.aspx

protected void SendToServer_Click(object sender, EventArgs e)
{
    DataTable Values = Session["valuesdt"] as DataTable ;
    if(Values.Rows.Count>0)
    {
        DataTable dv = Values.DefaultView.ToTable(true, "Mobile1", "Mobile2", "Tel", "Category");
        //Fix up default values
        for (int i = 0; i < Values.Rows.Count; i++)
       {
            Values.Rows[i]["Mobile1"] =Values.Rows[i]["Mobile1"].ToString()==""?0: double.Parse(Values.Rows[i]["Mobile1"].ToString());
            Values.Rows[i]["Mobile2"] = Values.Rows[i]["Mobile2"].ToString() == "" ? 0 : double.Parse(Values.Rows[i]["Mobile2"].ToString());
            Values.Rows[i]["Tel"] = Values.Rows[i]["Tel"].ToString() == "" ? 0 : double.Parse(Values.Rows[i]["Tel"].ToString());

           Values.Rows[i]["Category"] = Values.Rows[i]["Category"].ToString();
       }
       BatchUpdate(dv,1000);


    }

}
public static void BatchUpdate(DataTable dataTable,Int32 batchSize)
{
    // Assumes GetConnectionString() returns a valid connection string.
    string connectionString = GetConnectionString();

    // Connect to the database.
    using (SqlConnection connection = new SqlConnection(connectionString))
    {

        // Create a SqlDataAdapter.
        SqlDataAdapter adapter = new SqlDataAdapter();

        // Set the INSERT command and parameter.
        adapter.InsertCommand = new SqlCommand(
            "INSERT INTO client(Mobile1,Mobile2,Tel,Category) VALUES(@Mobile1,@Mobile2,@Tel,@Category);", connection);
        adapter.InsertCommand.Parameters.Add("@Mobile1", 
          SqlDbType.Float);
        adapter.InsertCommand.Parameters.Add("@Mobile2", 
          SqlDbType.Float);
        adapter.InsertCommand.Parameters.Add("@Tel", 
          SqlDbType.Float);
        adapter.InsertCommand.Parameters.Add("@Category", 
          SqlDbType.NVarchar, 50);
        adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;

        // Set the batch size.
        adapter.UpdateBatchSize = batchSize;

        // Execute the update.
        adapter.Update(dataTable);
    }
}

Answer 3

You can try SqlBulkCopy to insert Datatable to Database Table

Something like this,

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnection, SqlBulkCopyOptions.KeepIdentity))
{
    bulkCopy.DestinationTableName = DestTableName;
    string[] DtColumnName = YourDataTableColumns;
    foreach (string dbcol in DbColumnName)//To map Column of Datatable to that of DataBase tabele
    {
        foreach (string dtcol in DtColumnName)
        {
            if (dbcol.ToLower() == dtcol.ToLower())
            {
                SqlBulkCopyColumnMapping mapID = new SqlBulkCopyColumnMapping(dtcol, dbcol);
                bulkCopy.ColumnMappings.Add(mapID);
                break;
            }
        }
    }
    bulkCopy.WriteToServer(YourDataTableName.CreateDataReader());
    bulkCopy.Close();
}

For more Read http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx

Answer 4

You are inserting 1 row at a time, which is very expensive for this amount of data

In those cases you should use bulk insert, so the round trip to DB will be only once, if you need to roll back - all is the same transaction