C＃中多个连接字符串的多线程 [英] Multi thread for more than one connection string in C#

问题描述

我想为连接字符串实现多线程，它将从文件中检索更多的连接字符串，它正在工作但是花了很多时间所以我想改进我的代码：



我尝试了什么：

I want to implement multi threading for connection string which will retrieve more connection string from file, its working but its taking so much time so i want to improve my code:

What I have tried:

using System.Configuration;
        using System.Data;
        using System.Data.SqlClient;
        using MySql.Data.MySqlClient;    
        namespace ConsoleApplication1
        {
            public  class Program
            {
                static void Main(string[] args)
                {
                    Program p = new Program();
                    p.WithTPLAndParallelOptions();
                }               
                private  object threadLock = new object();
                public  void ConnectDBAndExecuteQueryWithLock(string connectionString)
                {
                    lock (threadLock)
                    {
                        try
                        {
                            string mysqlQuery = "SELECT PS_CD,COUNT(*) AS OFFLINE_FIR_COUNT FROM t_fir_registration WHERE state_cd=18 AND lang_cd=99 GROUP BY PS_CD";
        
                            //string connectionString = @"Server=.\SQLEXPRESS;Database=AUTODATA;Password=abc@123;User ID=sa";
                            using (MySqlConnection connection = new MySqlConnection(connectionString))
                            {
                                connection.Open();
                                using (MySqlCommand command = new MySqlCommand(mysqlQuery, connection))
                                {
                                    command.CommandTimeout = 80;
                                    command.ExecuteNonQuery();
                                    Console.WriteLine("Executed Thread.. " + Thread.CurrentThread.Name);
                                }
                            }
                        }
                        catch (Exception e)
                        {
                            Console.WriteLine(e.Message);
                        }
                    }
                }     
               public  void WithTPLAndParallelOptions()
                {
                    string cctns_offline_DBConnectionString = ConfigurationManager.ConnectionStrings["cctns_offline_DBConnectionString"].ConnectionString;
                    string[] filePaths = Directory.GetFiles(ConfigurationManager.AppSettings["filePath"].ToString(), "*.properties",
                                                  SearchOption.TopDirectoryOnly);
                    int ThreadCount = filePaths.Length;
                    ParallelOptions options = new ParallelOptions();
                    options.MaxDegreeOfParallelism = 5;
                    //Create Parallel.For to execute the task
                    Parallel.For(0, ThreadCount, options, i =>
                    {
        
                        foreach (string fileName in filePaths)
                        {
                            // i = i + 1;
                            // Read a text file using StreamReader
                            using (System.IO.StreamReader sr = new System.IO.StreamReader(fileName))
                            {
                                String line;
                                while ((line = sr.ReadLine()) != null)
                                {
        
                                    if (line.StartsWith("db.url"))
                                    {
                                        string[] PS_CD = fileName.Split(new[] { "\\", ".", "_" }, StringSplitOptions.None);
        
                                        string[] ip_address = line.Split(new[] { ";", "//", "/", ":" }, StringSplitOptions.None);
                                        if (ip_address[3].ToString() != string.Empty && ip_address[3] != "SQL2K8CLUSTER")
                                        {
                                            string connstringrpl = cctns_offline_DBConnectionString.Replace("PS_IP", ip_address[3].ToString());
                                            ConnectDBAndExecuteQueryWithLock(connstringrpl);
                                        }
                                    }
                                }
                            }
                        }
                    });
                }
              }
        }

推荐答案

我不是 MySql 专家，但我认为你的问题是锁，试试没有锁定。

你也可以尝试使用MySQL的内置连接池能力，请参见此处的示例：

c＃ - 用于写入MySQL数据库的多线程应用程序 - 代码复审堆栈交换 [ ^ ]

I'm not an MySql specialist, but your problem I think is the lock, try without locking.
You could also try to use MySQL's built in connection pooling abilities, see example here:
c# - Multithreaded app for writing to a MySQL database - Code Review Stack Exchange[^]

这篇关于C＃中多个连接字符串的多线程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！