C# add thousands of rows from text file to database very quickly

I am creating an app for Grepolis, an onli开发者_开发问答ne war game where you play alongside thousands of other players. I am connecting to a text file at http://en12.grepolis.com/data/players.txt and downloading it into WebClient.DownloadString. I am then splitting this on "\n" and splitting each of those strings on ",". Each row contains the player id, name, alliance_id, points, rank and towns (number of towns the player has). There are over 60000 at the last count. I got them all uploaded to a local database in about 10 minutes, and now doing an update to each row so that all those players on 0 points are removed in the process, and any who have changed since the last update are updated. The problem is that even just the update has taken over 10 minutes so far.

Is there any really quick way to do what I am trying to do? Here is all the code which I think is relevant:

File: Global.asax.cs

protected void Application_Start(object sender, EventArgs e)
{
    WebClient wc = new WebClient();
    String str = wc.DownloadString("http://en12.grepolis.com/data/players.txt");
    String[] players = str.Split('\n');
    foreach (String player in players)
    {
        String[] playerInfo = player.Split(',');
        ContentManager cm = new ContentManager();
        int id = 0;
        String name = "";
        int alliance_id = 0;
        int points = 0;
        int rank = 0;
        int towns = 0;
        try
        {
            int.Parse(playerInfo[0]);
            id = int.Parse(playerInfo[0]);
        }
        catch
        {
        }
        try
        {
            name = playerInfo[1];
        }
        catch
        {
        }
        try
        {
            int.Parse(playerInfo[2]);
            alliance_id = int.Parse(playerInfo[2]);
        }
        catch
        {
        }
        try
        {
            int.Parse(playerInfo[3]);
            points = int.Parse(playerInfo[3]);
        }
        catch
        {
        }
        try
        {
            int.Parse(playerInfo[4]);
            rank = int.Parse(playerInfo[4]);
        }
        catch
        {
        }
        try
        {
            int.Parse(playerInfo[5]);
            towns = int.Parse(playerInfo[5]);
        }
        catch
        {
        }
        if (points > 0 && towns > 0 && id > 0 && !name.Equals(""))
        {
            try
            {
                cm.UpdatePlayer(id, name, alliance_id, points, rank, towns);
            }
            catch
            {
            }
        }
        else
        {
            try
            {
                cm.DeletePlayer(id);
            }
            catch
            {
            }
        }
    }
}

File: ContentManager.cs

public class ContentManager : IHttpModule
{
    SqlDataSource conn = new SqlDataSource(WebConfigurationManager.ConnectionStrings["DevConn"].ConnectionString, "");
    public void UpdatePlayer(int id, String name, int alliance_id, int points, int rank, int towns) {
        conn.UpdateCommandType = SqlDataSourceCommandType.StoredProcedure;
        conn.UpdateCommand = "UpdatePlayer";
        conn.UpdateParameters.Clear();
        conn.UpdateParameters.Add("id", id.ToString());
        conn.UpdateParameters.Add("name", name);
        conn.UpdateParameters.Add("alliance_id", alliance_id.ToString());
        conn.UpdateParameters.Add("points", points.ToString());
        conn.UpdateParameters.Add("rank", rank.ToString());
        conn.UpdateParameters.Add("towns", towns.ToString());
        conn.Update();
    }

    public void DeletePlayer(int id)
    {
        conn.DeleteCommandType = SqlDataSourceCommandType.StoredProcedure;
        conn.DeleteCommand = "DeletePlayer";
        conn.DeleteParameters.Clear();
        conn.DeleteParameters.Add("id", id.ToString());
        conn.Delete();
    }
}

---

You're processing one player at a time, you need to do a batch upload.

You need to process all the users in your FOR loop and then do a "bulk insert".

At my work here, I can bulk insert at the speed my HD can read.

Word of warning, a bulk insert locks the table, so you may have to set batches for your bulk insert if you plan to have the table readable at all during the import.

eg: 100k rows with 10k batches. 1) lock table 2) begin import until 10k rows 3) unlock table 4) if more rows to commit, goto 1

Between steps 3 and 4, there will be a break where other queries may run against the table.

SQL and .NET manages nearly all of this, I just broke out the logic for you. All you need to do is read up on bulk inserts and batching, which will seem really easy once you get it.

---

Whenever I've had to do this in the past I've outputted the data to a temporary text file and then used Bulk Insert to dump the data into the database.