为什么我的应用程序在更新数据库记录时挂起？

Question

以下应用程序代码在更新数据库记录时挂起。输出画面只显示0，程序永远挂起。但是，表中有超过50,000条记录。(程序将ISCII代码转换为UNICODE，并将其写回数据库。)

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Collections;
using System.Data.SqlClient;
using System.Data.Sql;

namespace demoupdateform
{
public partial class Form1 : Form
{
    System.IO.StreamWriter file = new System.IO.StreamWriter("c:\\test1.txt");

    String[,] harltabcol = new String[,]
        {
            {"Col_name","Table_name"}

        };

    public Form1()
    {
        InitializeComponent();
    }

    private void button1_Click(object sender, EventArgs e)
    {
        SqlConnection cn = new SqlConnection("Password=admin;Persist Security Info=True;User ID=sa;Initial Catalog=dbname;Data Source=DEEPAK-87E8B4");
        SqlConnection cn1 = new SqlConnection("Password=admin;Persist Security Info=True;User ID=sa;Initial Catalog=dbname;Data Source=DEEPAK-87E8B4");



        string temp;
        string temp1;
        try
        {

            for (int i = 0; i < harltabcol.GetLength(0); i++)
            {

                    cn.Open();


                SqlCommand cmdSel = new SqlCommand( "select [" + harltabcol[i, 0] + "] from [" + harltabcol[i, 1] + "]",cn);
                //cn.Open();
                SqlDataReader rdr = cmdSel.ExecuteReader();
                progressBar1.Value = 0;
                int j = 0;
                while (rdr.Read())
                {

                    temp = (rdr[harltabcol[i, 0]].ToString()).ToString();

                    temp1 = (Iscii2Unicode(temp)).ToString();
                    SqlCommand cmdUpd = new SqlCommand("UPDATE [" + harltabcol[i,1] + "] SET [" + harltabcol[i,0] + "] =N'"+temp1+"' WHERE " + harltabcol[i,0] + "='" + temp + "'",cn1);
                    temp = rdr[harltabcol[i, 0]].ToString();
                    cn1.Open();
                    cmdUpd.CommandTimeout = 0;
                    Console.WriteLine(j++);
                    file.WriteLine(temp+" --> "+temp1);
                    progressBar1.Value = progressBar1.Value + 1;

                    if (progressBar1.Value == 99)
                        progressBar1.Value = 0;

                    cmdUpd.ExecuteNonQuery();
                    temp = null;
                    temp1 = null;
                    cn1.Close();
                }
                progressBar1.Value = 100;
                cn.Close(); 
            }


        }
        catch (Exception ex)
        {

            MessageBox.Show("Error Occured " + ex.ToString());
        }


    }
    public string Iscii2Unicode(string IsciiStr)
    {
        Encoding EncFrom = Encoding.GetEncoding(1252);
        Encoding EncTo = Encoding.GetEncoding(57002);
        Byte[] b = EncFrom.GetBytes(IsciiStr);

        return EncTo.GetString(b);
    }

    private void Form1_Load(object sender, EventArgs e)
    {

    }
}
}

Answer 1

很正常。您没有给消息循环一个运行的机会。使用BackgroundWorker处理数据库查询，并调用ProgressChanged更新图形用户界面。

Answer 2

在你的代码中，你访问了while循环中的数据库，这会导致你的程序挂起。因此，您可以创建一个大型update查询并调用Command.Execute一次。这可能不是最好的解决方案，但它将节省每次迭代打开和关闭数据库连接的时间。

StringBuilder updateQuery = new StringBuilder("");
while (rdr.Read())
{
   temp = (rdr[harltabcol[i, 0]].ToString()).ToString();
   temp1 = (Iscii2Unicode(temp)).ToString();
   updateQuery.Append("UPDATE [" + harltabcol[i,1] + "] SET [" + harltabcol[i,0] + "] =N'"+temp1+"' WHERE " + harltabcol[i,0] + "='" + temp + "';");
} 
SqlCommand cmdUpd = new SqlCommand(updateQuery.ToString(),cn1);
cn1.Open();
cmdUpd.CommandTimeout = 0;
cmdUpd.ExecuteNonQuery();
temp = null;
temp1 = null;
cn1.Close();
cn.Close();