Блог

Ниже приведен код для приложения Windows Forms, которое я использую для перемещения данных из Excel в базу данных SQL Server. Если я переключаю сервер на свою локальную базу данных, он работает и копирует 28000 записей за 2 секунды.

Для другого сервера, как упоминалось, она продолжает выполняться до истечения времени ожидания, а иногда копирует через долгое время.

 public partial class form1 : Form
{
    public form1()
    {
        InitializeComponent();           
        button2.Enabled = false;
    }

    private void button1_Click(object sender, EventArgs e)
    {
        OpenFileDialog openFileDialog1 = new OpenFileDialog();
        openFileDialog1.Filter= "Excel Files|*.xls;*.xlsx;*.xlsm";

        button2.Enabled = false;
        lblStatus.Text = null;

        if (openFileDialog1.ShowDialog() == DialogResult.OK)
        {
            string strfilename = openFileDialog1.FileName;
            filePath.Text = strfilename;
            button2.Enabled = true;
        }
    }

    private void button2_Click(object sender, EventArgs e)
    {
        button2.Enabled = false;
        this.Cursor = Cursors.WaitCursor;
        lblStatus.Text = "Uploading...";

        progressBar1.Style = ProgressBarStyle.Marquee;
        progressBar1.MarqueeAnimationSpeed = 50;
        backgroundWorker1.RunWorkerAsync(argument: filePath.Text);
    }

    private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
    {
        string filePath = (string)e.Argument;

        if (File.Exists(filePath))
        {
            try
            {
                String Connection = "Data Source=*******; Initial Catalog= ****; User Id=*****; Password= *****; Integrated Security=false;";

                String excelConnString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties="Excel 12.0"", filePath);

                using (SqlConnection strConnection = new SqlConnection(Connection))
                {
                    //Create Connection to Excel work book 
                    using (OleDbConnection excelConnection = new OleDbConnection(excelConnString))
                    {
                        //Create OleDbCommand to fetch data from Excel 
                        using (OleDbCommand cmd = new OleDbCommand("Select * from [Sheet0$]", excelConnection))
                        {
                            excelConnection.Open();
                            using (OleDbDataReader dReader = cmd.ExecuteReader())
                            {
                                using (SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection, SqlBulkCopyOptions.TableLock |
            SqlBulkCopyOptions.FireTriggers |
            SqlBulkCopyOptions.UseInternalTransaction,
            null))
                                {
                                    e.Result = 0;

                                    sqlBulk.DestinationTableName = "tblCMHC";

                                    string sClearSql = "Truncate table "   sqlBulk.DestinationTableName;
                                    SqlConnection sqlConn0 = new SqlConnection(Connection);
                                    SqlCommand sqlCMD0 = new SqlCommand(sClearSql, sqlConn0);
                                    sqlConn0.Open();
                                    sqlCMD0.CommandTimeout = 80000;
                                    sqlCMD0.ExecuteNonQuery();
                                    sqlConn0.Close();

                                    sqlBulk.ColumnMappings.Add("AptSuite", "AptSuite");
                                    sqlBulk.ColumnMappings.Add("City", "City");
                                    sqlBulk.ColumnMappings.Add("ChangedDate", "ChangedDate");
                                    sqlBulk.ColumnMappings.Add("Country", "Country");
                                    sqlBulk.ColumnMappings.Add("Type", "Type");
                                    sqlBulk.ColumnMappings.Add("PostalCode", "PostalCode");
                                    sqlBulk.ColumnMappings.Add("Name", "Name");
                                    sqlBulk.ColumnMappings.Add("SSN", "SSN");
                                    sqlBulk.ColumnMappings.Add("BirthDate", "BirthDate");
                                    sqlBulk.ColumnMappings.Add("Gender", "Gender");
                                    sqlBulk.ColumnMappings.Add("Race", "Race");
                                    sqlBulk.ColumnMappings.Add("PrimaryLanguage", "PrimaryLanguage");
                                    sqlBulk.ColumnMappings.Add("Education", "Education");
                                    sqlBulk.ColumnMappings.Add("Program", "Program");
                                    sqlBulk.ColumnMappings.Add("PrimaryDx", "PrimaryDx");
                                    sqlBulk.ColumnMappings.Add("PrimaryDxDesc", "PrimaryDxDesc");
                                    sqlBulk.ColumnMappings.Add("SecondaryDx", "SecondaryDx");
                                    sqlBulk.ColumnMappings.Add("SecondaryDxDesc", "SecondaryDxDesc");
                                    sqlBulk.ColumnMappings.Add("AdmissionDate", "AdmissionDate");
                                    sqlBulk.ColumnMappings.Add("DischargeDate", "DischargeDate");
                                    sqlBulk.ColumnMappings.Add("StatusEpisodes", "StatusEpisodes");
                                    sqlBulk.ColumnMappings.Add("StatusClients", "StatusClients");
                                    sqlBulk.ColumnMappings.Add("ClientID", "ClientID");
                                    sqlBulk.ColumnMappings.Add("EffectiveDate", "EffectiveDate");

                                    sqlBulk.BulkCopyTimeout = 1800;
                                    sqlBulk.BatchSize = 3000;

                                    strConnection.Open();

                                    while (dReader.Read())
                                    {
                                        sqlBulk.WriteToServer(dReader);
                                    }

                                    strConnection.Close();

                                    string sCount = "Select count(*) from  "   sqlBulk.DestinationTableName;
                                    SqlConnection sqlConn1 = new SqlConnection(Connection);
                                    SqlCommand sqlCMD1 = new SqlCommand(sCount, sqlConn1);
                                    sqlConn1.Open();
                                    sqlCMD1.CommandTimeout = 80000;
                                    int count=(int)sqlCMD1.ExecuteScalar();
                                    sqlConn1.Close();

                                    e.Result = count;
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }
        else
        {
            MessageBox.Show("File path does not exist");
        }
    }

    private void backgroundWorker1_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
    {
        progressBar1.MarqueeAnimationSpeed = 0;
        progressBar1.Style = ProgressBarStyle.Blocks;
        progressBar1.Value = progressBar1.Minimum;

        this.Cursor = Cursors.Defau<
        lblStatus.Text = null;

        int count = (int)e.Resu<

        MessageBox.Show(count   " Rows Uploaded!");
    }

    private void button3_Click(object sender, EventArgs e)
    {
        Close();
    }
}