A way to save massive amount of lines from text file to database in C#

Ivonne, It looks like your code is designed to read all lines from the file and process each line individually. The issue with only 1000 lines being saved could be due to several reasons. Here are some things you can check and modify to ensure all lines are processed:

1. Check for Errors in the Data
Ensure that all the lines in your .txt file follow the expected format, especially since your code processes lines based on a specific structure (splitting by | and expecting at least 9 parts). Lines that do not meet this condition will be skipped.

2. Database Transaction and Commit Size
If the database transaction is too large, it might be causing timeouts or memory issues. You can implement batch processing or use transactions to commit data in chunks.

3. Increase the Timeout
Check if your database connection or command timeout is set too low, which might be stopping the process prematurely.

4. Optimize the Code
Your current code is reading and processing the file line by line inside a loop where each line triggers database operations. This can be optimized.

Optimized Code:
You could modify your code to use a batch insert approach, where you collect a batch of data and insert it all at once, reducing the number of round-trips to the database. Here’s an example of how you could modify your loop:

protected void Button1_Click(object sender, EventArgs e)
{
    try
    {
        if (!FileUpload1.HasFile)
        {
            Response.Write("No file Selected"); return;
        }
        
        string path = ConfigurationManager.AppSettings["ImageBasePath"];
        var files = Directory.GetFiles(path, "index.txt", SearchOption.AllDirectories);
        string filePath = Path.Combine(files);
        var lines = File.ReadAllLines(Path.Combine(filePath));

        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DBCU"].ToString()))
        {
            connection.Open();

            foreach (var line in lines)
            {
                var parts = line.Split('|');
                if (parts.Length >= 9)
                {
                    // Extract required information
                    string acctNumber = parts[0];
                    string chkNumber = parts[1];
                    string chkAmount = parts[2];
                    string imgDate = parts[5];
                    string imagePath = parts[8];
                    string basePath = ConfigurationManager.AppSettings["ImageBasePath"];
                    string fullImagePath = Path.Combine(basePath, imagePath);
                    byte[] imageData = File.ReadAllBytes(fullImagePath);

                    SqlCommand cmd = new SqlCommand("INSERT INTO chkImages (acctNumber, chkNumber, chkAmount, imgDate, imageData) VALUES (@acctNumber, @chkNumber, @chkAmount, @imgDate, @imageData)", connection);
                    cmd.Parameters.AddWithValue("@acctNumber", acctNumber);
                    cmd.Parameters.AddWithValue("@chkNumber", chkNumber);
                    cmd.Parameters.AddWithValue("@chkAmount", chkAmount);
                    cmd.Parameters.AddWithValue("@imgDate", imgDate);
                    cmd.Parameters.AddWithValue("@imageData", imageData);
                    cmd.ExecuteNonQuery(); // Execute each command
                }
            }

            connection.Close();
            Response.Write("All images have been added.");
        }
    }
    catch (Exception ex)
    {
        Response.Write("An error occurred: " + ex.Message);
    }
}

By optimizing the code and ensuring that the database operations are efficiently handled, you should be able to process a larger number of lines without issues. Ensure that the server handling the requests has enough resources (RAM, CPU) to handle large operations if the file sizes are very large. Also, consider the network and disk I/O if the database server is separate from the application server.

I think the issue might be due to a limitation in the database. Try the below code once:

protected void Button1_Click(object sender, EventArgs e)
{
    try
    {
        if (!FileUpload1.HasFile) // Validation
        {
            Response.Write("No file Selected");
            return;
        }
        else
        {
            // Read and Extract Information from the Index.txt File:
            string path = ConfigurationManager.AppSettings["ImageBasePath"];
            var files = Directory.GetFiles(path, "index.txt", SearchOption.AllDirectories);

            // Read and populate textboxes from Index.txt
            string filePath = Path.Combine(files);

            var lines = File.ReadAllLines(filePath);
            List parsedLines = new List();

            // Parse lines and prepare data
            foreach (var line in lines)
            {
                var parts = line.Split('|');
                if (parts.Length >= 9)
                {
                    parsedLines.Add(parts);
                }
            }

            // Batch insert to database
            int batchSize = 1000; // Set your desired batch size
            using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DBCU"].ToString()))
            {
                connection.Open();
                for (int i = 0; i < parsedLines.Count; i += batchSize)
                {
                    using (SqlTransaction transaction = connection.BeginTransaction())
                    {
                        for (int j = i; j < i + batchSize && j < parsedLines.Count; j++)
                        {
                            var parts = parsedLines[j];
                            string acctNumber = parts[0];
                            string chkNumber = parts[1];
                            string chkAmount = parts[2];
                            string imgDate = parts[5];
                            string imagePath = parts[8];

                            // Construct the full path to the image file
                            string fullImagePath = Path.Combine(path, imagePath);

                            // Convert the image to byte array
                            byte[] imageData = File.ReadAllBytes(fullImagePath);

                            SqlCommand cmd = new SqlCommand("INSERT INTO chkImages (acctNumber, chkNumber, chkAmount, imgDate, imageData) VALUES (@acctNumber, @chkNumber, @chkAmount, @imgDate, @imageData)", connection, transaction);

                            cmd.Parameters.AddWithValue("@acctNumber", acctNumber);
                            cmd.Parameters.AddWithValue("@chkNumber", chkNumber);
                            cmd.Parameters.AddWithValue("@chkAmount", chkAmount);
                            cmd.Parameters.AddWithValue("@imgDate", imgDate);
                            cmd.Parameters.AddWithValue("@imageData", imageData);

                            cmd.ExecuteNonQuery();
                        }

                        transaction.Commit();
                    }
                }
                connection.Close();
            }

            Response.Write("Images have been Added");
        }
    }
    catch (Exception ex)
    {
        // Handle any exceptions that occurred
        Console.WriteLine("An error occurred while reading the file: " + ex.Message);
    }
}

Hi

1. Batch Processing: Instead of inserting one line at a time, you can use batch processing to insert multiple lines in a single database transaction. This reduces the number of database calls and improves performance.

2. Async/Await: Utilize asynchronous programming to prevent blocking operations, especially when dealing with large files and database operations.

3. Error Handling: Enhance error handling to manage failures gracefully.

Here is an improved version of your code that incorporates these suggestions:

protected async void Button1_Click(object sender, EventArgs e)
{
    try
    {
        if (!FileUpload1.HasFile) // Validation
        {
            Response.Write("No file Selected");
            return;
        }
        else
        {
            // Read and Extract Information from the Index.txt File:
            string path = ConfigurationManager.AppSettings["ImageBasePath"];
            var files = Directory.GetFiles(path, "index.txt", SearchOption.AllDirectories);

            // Read and populate textboxes from Index.txt
            string filePath = Path.Combine(files);

            var lines = await File.ReadAllLinesAsync(filePath);
            List records = new List();

            foreach (var line in lines)
            {
                var parts = line.Split('|');
                if (parts.Length >= 9)
                {
                    records.Add(parts);
                }
            }

            // Batch insertion
            await InsertRecordsAsync(records);
            Response.Write("Images have been Added");
        }
    }
    catch (Exception ex)
    {
        // Handle any exceptions that occurred
        Console.WriteLine("An error occurred while reading the file: " + ex.Message);
    }
}

private async Task InsertRecordsAsync(List records)
{
    try
    {
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DBCU"].ToString()))
        {
            await connection.OpenAsync();
            foreach (var parts in records)
            {
                // Extract required information
                string acctNumber = parts[0];
                string chkNumber = parts[1];
                string chkAmount = parts[2];
                string imgDate = parts[5];
                string imagePath = parts[8];

                // Retrieve the base path from the configuration
                string basePath = ConfigurationManager.AppSettings["ImageBasePath"];

                // Construct the full path to the image file
                string fullImagePath = Path.Combine(basePath, imagePath);

                // Convert the image to byte array
                byte[] imageData = await File.ReadAllBytesAsync(fullImagePath);

                // Insert the data into the database
                using (SqlCommand cmd = new SqlCommand("INSERT INTO chkImages (acctNumber, chkNumber, chkAmount, imgDate, imageData) VALUES (@acctNumber, @chkNumber, @chkAmount, @imgDate, @imageData)", connection))
                {
                    cmd.Parameters.AddWithValue("@acctNumber", acctNumber);
                    cmd.Parameters.AddWithValue("@chkNumber", chkNumber);
                    cmd.Parameters.AddWithValue("@chkAmount", chkAmount);
                    cmd.Parameters.AddWithValue("@imgDate", imgDate);
                    cmd.Parameters.AddWithValue("@imageData", imageData);

                    await cmd.ExecuteNonQueryAsync();
                }
            }
        }
    }
    catch (Exception ex)
    {
        // Handle any exceptions that occurred during the database operation
        Console.WriteLine("An error occurred while inserting records: " + ex.Message);
    }
}

Key Improvements:

1. Batch Processing: The InsertRecordsAsync method processes a list of records, which can be further optimized for batch insertions if your database supports it.

2. Asynchronous Operations: Used asynchronous methods (ReadAllLinesAsync, ReadAllBytesAsync, OpenAsync, ExecuteNonQueryAsync) to prevent blocking operations.

3. Separation of Concerns: Extracted the database insertion logic into a separate method InsertRecordsAsync for better code organization and readability.

4. Enhanced Error Handling: Added exception handling for database operations separately to catch and log any issues specifically related to database operations.