C# & SQL Server database : record Parallel.ForEach error

Question

I am trying to fill my SQL Server database with 3 different .txt files I accomplished this using regular for but it takes a long time. Then since the order does not matter (in my database) I tried to use Parallel.ForEach but I get this error error

Here is my code

using System;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data;

namespace LogwithDataBase
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=LogsUploaded;Integrated Security=SSPI");

        void filldata()
        {
            SqlDataAdapter da = new SqlDataAdapter("Select *From LogsTable", con);
            DataSet ds = new DataSet();
            con.Open();

            da.Fill(ds, "LogsTable");

            dataGridView1.DataSource = ds.Tables["LogsTable"];
            dataGridView1.Columns[1].DefaultCellStyle.Format = "yyyy-MM-dd HH:mm:ss.fff";
            con.Close();
        }

        private void datab_connect_Click(object sender, EventArgs e)
        {
            con.Open();
            IEnumerable<string> lines1 = File.ReadLines(openFileDialog1.FileName); //HWLog

            Parallel.ForEach(lines1, line1 =>
            {
                string timeStmp = line1.Split('\t')[0];
                string atmIdd = line1.Split('\t')[1];
                string fileTyp = "HWLog";
                string evenTex = line1.Split('\t')[2];
                string query = "INSERT INTO LogsTable (timestampof_log,atmID,fileType,eventText) values (@timeStmp, @atmIdd, @fileTyp, @evenTex)";
                SqlCommand cmd = new SqlCommand(query, con);
                cmd.Parameters.AddWithValue("@timeStmp", DateTime.Parse(timeStmp));
                cmd.Parameters.AddWithValue("@atmIdd", atmIdd);
                cmd.Parameters.AddWithValue("@fileTyp", fileTyp);
                cmd.Parameters.AddWithValue("@evenTex", evenTex);
                cmd.ExecuteNonQuery();
            });

            IEnumerable<string> lines2 = File.ReadLines(openFileDialog2.FileName); //SWLog
            Parallel.ForEach(lines2, line2 =>
            {
                string timeStmp = line2.Split('\t')[0];
                string atmIdd = line2.Split('\t')[1];
                string fileTyp = "SWLog";
                string evenTex = line2.Split('\t')[2];
                string query = "INSERT INTO LogsTable (timestampof_log,atmID,fileType,eventText) values (@timeStmp, @atmIdd, @fileTyp, @evenTex)";
                SqlCommand cmd = new SqlCommand(query, con);
                cmd.Parameters.AddWithValue("@timeStmp", DateTime.Parse(timeStmp));
                cmd.Parameters.AddWithValue("@atmIdd", atmIdd);
                cmd.Parameters.AddWithValue("@fileTyp", fileTyp);
                cmd.Parameters.AddWithValue("@evenTex", evenTex);
                cmd.ExecuteNonQuery();
            });

            IEnumerable<string> lines3 = File.ReadLines(openFileDialog3.FileName); //JournalLog
            Parallel.ForEach(lines3, line3 =>
            {
                string timeStmp = line3.Split('\t')[0];
                string atmIdd = line3.Split('\t')[1];
                string fileTyp = "JournalLog";
                string evenTex = line3.Split('\t')[2];
                string query = "INSERT INTO LogsTable (timestampof_log, atmID, fileType, eventText) VALUES (@timeStmp, @atmIdd, @fileTyp, @evenTex)";

                SqlCommand cmd = new SqlCommand(query, con);

                cmd.Parameters.AddWithValue("@timeStmp", DateTime.Parse(timeStmp));
                cmd.Parameters.AddWithValue("@atmIdd", atmIdd);
                cmd.Parameters.AddWithValue("@fileTyp", fileTyp);
                cmd.Parameters.AddWithValue("@evenTex", evenTex);

                cmd.ExecuteNonQuery();
            });

            string querycb = "SELECT DISTINCT fileType FROM LogsTable"; //COMBOBOXA DATA ÇEKMEK
            SqlCommand cmdcb = new SqlCommand(querycb, con); //COMBOBOXA DATA ÇEKMEK
            SqlDataReader drcb = cmdcb.ExecuteReader(); //COMBOBOXA DATA ÇEKMEK
            List<string> list = new List<string>(); //COMBOBOXA DATA ÇEKMEK
            list.Add(""); //COMBOBOXA DATA ÇEKMEK

            while (drcb.Read())
            {
                list.Add(drcb["fileType"].ToString());
            }

            drcb.Close();

            comboBox1.DataSource = list; //COMBOBOXA DATA ÇEKMEK
            con.Close();

            filldata();
        }
    }
}

I couldn't solve this problem and I don't want to go back to regular for

You should not be calling ExecuteNonQuery in a loop to begine with, especially creating a new command object every time. You ought to be populating a DataTable and then saving it in a single batch using a SqlDataAdapter or even SqlBulkCopy. — John
– John, Commented Aug 11, 2022 at 5:24
I have an opinion, is it because execute query should be completed before paralleling lines ? If its like this how do we make this parallel or how can I do this faster ? — U.Deniz A.
– U.Deniz A., Commented Aug 11, 2022 at 5:25

Hytac · Accepted Answer · 2022-08-11 06:51:30Z

2

In terms of performance i would suggest to use sqlbulkcopy Just fill a DataTable with all the lines and insert all at once, its much faster than make a single query for each line in the file thant could be really really long.

    public static void BulkInsert(IEnumerable<string> lines)
    {
        DataTable dt = new DataTable();
        dt.TableName = "LogsTable";
        
        dt.Columns.Add("timestampof_log");
        dt.Columns.Add("atmID");
        dt.Columns.Add("fileType");
        dt.Columns.Add("eventText");

        foreach (var line in lines)
        {
            string timeStmp = line.Split('\t')[0];
            string atmIdd = line.Split('\t')[1];
            string fileTyp = "JournalLog";
            string evenTex = line.Split('\t')[2];

            dt.Rows.Add(timeStmp, atmIdd, fileTyp, evenTex);

        }

        BulkInsert(dt);
    }

    public static void BulkInsert(DataTable table)
    {
        using (var bulkInsert = new SqlBulkCopy(ConnectionString))
        {
            bulkInsert.DestinationTableName = table.TableName;
            bulkInsert.WriteToServer(table);
        }
    }

edited Aug 11, 2022 at 6:51

answered Aug 11, 2022 at 5:31

Hytac

2061 gold badge2 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Jonathan Willcock Over a year ago

The only caveat which you should point out in your answer, is that SqlBulkCopy does not cause triggers to fire. If the table has triggers that need to be fired, then the solution would be a User Defined Table Type

U.Deniz A. Over a year ago

@JonathanWillcock what you mean by triggers that need to fired? Is it like my filldata() I use it in every button click but I use it at the end

Hytac Over a year ago

Have a look trigger

U.Deniz A. Over a year ago

@Hytac I believe this method copies data from another datatable/dataset but I have an .txt file its not a data set I have a log file

Hytac Over a year ago

@U.DenizA. I have edited my answer, check it now.

|

Yuri Myakotin · Accepted Answer · 2022-08-11 05:25:21Z

1

Simplest way - open sql connection in each parallel thread.

Parallel.Foreach SQL querying sometimes results in Connection more detailed here

answered Aug 11, 2022 at 5:25

Yuri Myakotin

611 silver badge3 bronze badges

6 Comments

Dai Over a year ago

Databasses are IO-bound, not CPU-bound - using additional threads won't be faster - and in-fact will likely slow things down.

Yuri Myakotin Over a year ago

Depends of size and and database implementation. Some times all needed data already cached in memory.

U.Deniz A. Over a year ago

When I do that it gives this error " System.InvalidCastException: 'Unable to cast object of type 'System.Data.ProviderBase.DbConnectionClosedConnecting' to type 'System.Data.SqlClient.SqlInternalConnectionTds'.' " Original code is ' ` Parallel.ForEach(lines1, line1 => { con.Open(); same as before con.Close(); });'`

Yuri Myakotin Over a year ago

you have to create and open connection in each thread, not just open.

U.Deniz A. Over a year ago

@YuriMyakotin now it says timeout period expired maximum pool connection

|

Collectives™ on Stack Overflow

C# & SQL Server database : record Parallel.ForEach error

2 Answers 2

9 Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related