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

本文关键字：更新 数据库 记录 我的 应用程序 为什么 | 更新日期: 2023-09-27 18:07:08

在更新数据库记录时挂起以下应用程序代码。它只在输出屏幕上显示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)
    {
    }
}
}

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

正常。您没有给消息循环运行的机会。使用BackgroundWorker来处理DB查询，并调用ProgressChanged来更新GUI。

在您的代码中，您在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();
相关文章：