ASP.NET - MySQL Bulk Insert Asynchronous Response For Each Row

Question

I'm creating a web application and i need to upload a LIST OF CLIENTS into the database, the list will come from a CSV,TSV or EXCEL file.

These files may contain 50-200 records

I need a way to give a response back to the client for every row insertion to the database. Such as successful or failed.

I don't want to execute all INSERTS and then return the summary of results.

I need just the idea on how to do this. Its ok if you can't provide the code. But better of course if you can.

Please feel free to flag or edit or retag..

Thank you. Best Regards!!!

does the client need to interact with the inserts? like cancel in the middle of the inserts or something? — Adrian Iftode
– Adrian Iftode, Commented Feb 18, 2012 at 16:04
hi thanks for the response... uhm it would be nice if i can add that feature.. but not necessary.. thanks again — Philip Badilla
– Philip Badilla, Commented Feb 18, 2012 at 16:05

Community · Accepted Answer · 2020-06-20 09:12:55Z

I'll try an answer and is based on using Session, javascript timers and AJAX calls.

The most common way of a communication between client and server is like the client consequently asks the server for something. This is implemented in JavaScript with a timer and when the time elapses, make the AJAX call and create a new timer which will do the same thing.

On the server you have the enable the AJAX Methods and these will be entry point for the AJAX calls. That method will respond with the status of your inserts.

Since you deal with inserts in two places, one in the page which does the inserts and the other in that AJAX method (which is static and you won't have access to the Page instance), you need to move the logic in a separate class and to track the inserts in the Session.

So the code will look like this:

public class CsvInserts
{
   private IList<string> InsertsProgress {
        get {
             if (HttpContext.Current.Session["CsvInserts.Inserts"] == null )
                HttpContext.Current.Session["CsvInserts.Inserts"]  = new List<string>();
             return (IList<string>)HttpContext.Current.Session["CsvInserts.Inserts"];
        }
   }
   public IList<string> GetInsertsProgress() {
         return InsertsProgress;
   }
   public void InsertFile(string[] lines) {
         foreach ( var line in lines)
              {
                    var row = DataAccess.CsvInserts.Insert(line); // code to insert the line
                    InsertsProgress.Add(row.GetRelevantInfoForUser()); // successfully inserted or not and which line was inserted
              }
   }
}

On the insert Page you will do something like this

protected void btInsert_OnClick(object sender, EventArgs e)
{
  var lines = .. get lines from the posted file;
  var insertManager = new CsvInserts();
  insertManager.InsertFile(lines);
}

The WebMethod will be like this

[WebMethod]
  public static IList<string> GetInsertsProgress()
  {
          var insertManager = new CsvInserts();
          return insertManager.GetInsertsProgress();
  }

On the client side with that timer you will call this method again and again, until the insert finishes. With jQuery or something else show the received strings.

This C# code is from memory, is more like a guideline. Sorry for not giving JavaScipt code. Also you have to clear the strings list either when the insert is finished or when you create a new bulk insert.

thanks for very detailed response. ill be trying something out based on these logic. thanks. alot. ill be back after i made it work =)

Lloyd · Accepted Answer · 2012-02-18 17:39:21Z

Heres something similar, the code compilies and works but needs a little "work", we have for doing single line updates where, I prefer to use the ThreadPool for ASP.Net async calls as we have had issues with Tasks and Unhandled exceptions etc, always coders fault tho.

// In the FileUpload Event Handler, or other Event Handler raised to start the process
ThreadPool.QueueUserWorkItem(new WaitCallback(DataLoader.InsertClientRecords), new Tuple<string, Guid>("PathToFile.csv", uniqueIdentifier));


// In the Ajax callback (or WebMethod as previously proposed) to update the UI, possible update a GridView with results etc.
List<Tuple<Guid, int, bool, string>> updates = DataLoader.GetProgressReports(uniqueIdentifier);


// Static class
public static class DataLoader
{
    private static readonly object locker = new object();

    // Tuple Guid for unique Identifier, int RowNumber, bool Result, string Message (if any)
    private static List<Tuple<Guid, int, bool, string>> results = new List<Tuple<Guid, int, bool, string>>();


    public static void InsertClientRecords(object stateInfo)
    {
        // string FilePath, Guid for unique Identifier
        Tuple<string, Guid> recordInfo = stateInfo as Tuple<string, Guid>;  

        if (recordInfo != null)
        {
            string filePath = recordInfo.item1;
            Guid id = recordInfo.item1;

            lock (locker)
            {
                // Update results List
            }            
        }
    }


    // Tuple Guid for unique Identifier, int RowNumber, bool Result, string Message (if any)
    public static List<Tuple<Guid, int, bool, string>> GetProgressReports(Guid identifier)
    {
        List<Tuple<Guid, int, bool, string>> updatedRecords = null; 
        bool lockTaken = false;

        try
        {
            // 1000 Millisecond Timeout so the Ajax callback does not hang.
            Monitor.TryEnter(locker, 1000, ref lockTaken);

            if (lockTaken)
            {
                updatedRecords = results.Where(r => (r.Item1 == identifier)).ToList();

                if (updatedRecords != null)
                    DataLoader.results.RemoveAll(r => (r.Item1 == identifier));
            }
        }
        finally
        {
            if (lockTaken)
                Monitor.Exit(locker);
        }  

        return updatedRecords;
    }

Collectives™ on Stack Overflow

ASP.NET - MySQL Bulk Insert Asynchronous Response For Each Row

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related