使用进度条从excel导入到sql [英] Import from excel to sql using progressbar

问题描述

亲爱的所有人。

任何人都可以帮助使用进度条从excel导入到sql的代码，在那里用正在导入的项目更新标签，如果项目存在，它应该显示在标签中。

我之前的帖子显示了我用于复制的代码示例，但我似乎对如何使用其中的进度条感到迷茫

Dear All.
Can anyone help with a code to import from excel to sql using progress bar, where it update the label with the items being imported, If item exist, it should display in label.
My previous post shows sample of code i used for the copy but i seem to be lost as to how to use the progressbar in it

private void openFileFile()
{
    string name;
    string model;
    string serial;
    string serial2;
    string windowsuser = txtuserid.Text;
    string createdate = txtcreated.Text;
    string cost = txtcost.Text;
    string quantity = txtquantity.Text;
    string active = "1";
    string filepath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "product.xls");
    bool flag = true;
    Econ = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0");
    try
    {
        OleDbCommand ocmd = new OleDbCommand("select * from [Sheet1$]", Econ);
        Econ.Open();
        OleDbDataReader odr = ocmd.ExecuteReader();
        if (odr.HasRows)
        {
            while (odr.Read())
            {
                name = odr.GetValue(0).ToString();
                model = odr.GetValue(1).ToString();
                serial = odr.GetValue(2).ToString();
                serial2 = odr.GetValue(3).ToString();
                importexcel(name, model, serial, serial2, windowsuser, createdate, cost, quantity, active);
            }
            flag = false;
        }
        else
        {
            lblstat.Text= "Object reference not set.Empty worksheet.";
            timer.Stop();
            //MessageBox.Show("Object reference not set.\nError: Empty worksheet.", "Inventory");
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString());
    }
}

public void importexcel(string name, string model, string serial, string serial2, string windowsuser, string createdate, string cost, string quantity, string active)
{
    con = new SqlConnection(connection);
    string seek = "select * from productx where serial='" + serial + "'";
    SqlCommand cmd2 = con.CreateCommand();
    cmd2.CommandText = seek;
    con.Open();
    SqlDataReader dr;
    dr = cmd2.ExecuteReader();
    if (dr.HasRows)
    {
        while (dr.Read())
        {
            lblstat.Text = "Error importing duplicate serial number: " + serial;
            //MessageBox.Show("Error importing duplicate serial number:" + "\n" + serial, "Inventory");
        }
    }
    else
    {
        try
        {
            con.Close();
            string insertexcel = "insert into productx(name, model, serial, serial2,windowsuser,Ondate,cost,quantity,status)values(@name, @model, @serial, @serial2,@wuser,@date,@cost,@quantity,@stat)";
            SqlCommand cmd = con.CreateCommand();
            cmd.CommandText = insertexcel;
            cmd.Parameters.AddWithValue("@name", SqlDbType.NVarChar).Value = name;
            cmd.Parameters.AddWithValue("@model", SqlDbType.NVarChar).Value = model;
            cmd.Parameters.AddWithValue("@serial", SqlDbType.NVarChar).Value = serial;
            cmd.Parameters.AddWithValue("@serial2", SqlDbType.NVarChar).Value = serial2;
            cmd.Parameters.AddWithValue("@wuser", SqlDbType.NVarChar).Value = windowsuser;
            cmd.Parameters.AddWithValue("@date", SqlDbType.NVarChar).Value = createdate;
            cmd.Parameters.AddWithValue("@cost", SqlDbType.NVarChar).Value = cost;
            cmd.Parameters.AddWithValue("@quantity", SqlDbType.NVarChar).Value = quantity;
            cmd.Parameters.AddWithValue("@stat", SqlDbType.NVarChar).Value = active;
            cmd.CommandType = Com

推荐答案

，Econ）;
Econ.Open（ ）;
OleDbDataReader odr = ocmd.ExecuteReader（）;
if （odr.HasRows）
{
while （odr.Read（））
{
name = odr.GetValue（ 0 ）.ToString（）;
model = odr.GetValue（ 1 ）。ToString（）;
serial = odr.GetValue（ 2 ）。ToString（）;
serial2 = odr.GetValue（ 3 ）。ToString（）;
importexcel（name，model，serial，serial2，windowsuser，createdate，cost，quantity，active）;
}
flag = false ;
}
else
{
lblstat.Text = 对象引用未设置.Empty工作表。;
timer.Stop（）;
// MessageBox.Show（对象引用未设置。\ nnError：空工作表。，库存）;
}
}
catch （例外情况）
{
MessageBox.Show（ex.ToString（））;
}
}

public void importexcel（ 字符串名称，字符串模型，字符串 serial ， string serial2， string windowsuser， string createdate， string cost， string quantity， string active）
{
con = new SqlConnection（连接）;
string seek = select * from productx其中serial =' + serial + ';
SqlCommand cmd2 = con.CreateCommand（）;
cmd2.CommandText = seek;
con.Open（）;
SqlDataReader dr;
dr = cmd2.ExecuteReader（）;
if （dr.HasRows）
{
while （dr .Read（））
{
lblstat.Text = 输入重复序列号时出错： + serial;
// MessageBox.Show（输入重复序列号时出错：+\ n+ serial ，库存）;
}
}
其他
{
尝试
{
con.Close（）;
string insertexcel = insert into productx（ name，model，serial，serial2，windowsuser，Ondate，cost，quantity，status）values（@ name，@ model，@ serial，@ serial2，@ wuser，@ date，@ cost，@ quantity，@ stat）;
SqlCommand cmd = con.CreateCommand（）;
cmd.CommandText = insertexcel;
cmd.Parameters.AddWithValue（ @ name，SqlDbType.NVarChar）.Value =名称;
cmd.Parameters.AddWithValue（ @ model，SqlDbType.NVarChar）.Value =模型;
cmd.Parameters.AddWithValue（ @ serial，SqlDbType.NVarChar）.Value =串行;
cmd.Parameters.AddWithValue（ @ serial2，SqlDbType.NVarChar）.Value = serial2;
cmd.Parameters.AddWithValue（ @ wuser，SqlDbType.NVarChar）.Value = windowsuser;
cmd.Parameters.AddWithValue（ @ date，SqlDbType.NVarChar）.Value = CREATEDATE;
cmd.Parameters.AddWithValue（ @ cost，SqlDbType.NVarChar）.Value =成本;
cmd.Parameters.AddWithValue（ @ quantity，SqlDbType.NVarChar）.Value =数量;
cmd.Parameters.AddWithValue（ @ stat，SqlDbType.NVarChar）.Value =活性;
cmd.CommandType = Com

", Econ); Econ.Open(); OleDbDataReader odr = ocmd.ExecuteReader(); if (odr.HasRows) { while (odr.Read()) { name = odr.GetValue(0).ToString(); model = odr.GetValue(1).ToString(); serial = odr.GetValue(2).ToString(); serial2 = odr.GetValue(3).ToString(); importexcel(name, model, serial, serial2, windowsuser, createdate, cost, quantity, active); } flag = false; } else { lblstat.Text= "Object reference not set.Empty worksheet."; timer.Stop(); //MessageBox.Show("Object reference not set.\nError: Empty worksheet.", "Inventory"); } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } } public void importexcel(string name, string model, string serial, string serial2, string windowsuser, string createdate, string cost, string quantity, string active) { con = new SqlConnection(connection); string seek = "select * from productx where serial='" + serial + "'"; SqlCommand cmd2 = con.CreateCommand(); cmd2.CommandText = seek; con.Open(); SqlDataReader dr; dr = cmd2.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { lblstat.Text = "Error importing duplicate serial number: " + serial; //MessageBox.Show("Error importing duplicate serial number:" + "\n" + serial, "Inventory"); } } else { try { con.Close(); string insertexcel = "insert into productx(name, model, serial, serial2,windowsuser,Ondate,cost,quantity,status)values(@name, @model, @serial, @serial2,@wuser,@date,@cost,@quantity,@stat)"; SqlCommand cmd = con.CreateCommand(); cmd.CommandText = insertexcel; cmd.Parameters.AddWithValue("@name", SqlDbType.NVarChar).Value = name; cmd.Parameters.AddWithValue("@model", SqlDbType.NVarChar).Value = model; cmd.Parameters.AddWithValue("@serial", SqlDbType.NVarChar).Value = serial; cmd.Parameters.AddWithValue("@serial2", SqlDbType.NVarChar).Value = serial2; cmd.Parameters.AddWithValue("@wuser", SqlDbType.NVarChar).Value = windowsuser; cmd.Parameters.AddWithValue("@date", SqlDbType.NVarChar).Value = createdate; cmd.Parameters.AddWithValue("@cost", SqlDbType.NVarChar).Value = cost; cmd.Parameters.AddWithValue("@quantity", SqlDbType.NVarChar).Value = quantity; cmd.Parameters.AddWithValue("@stat", SqlDbType.NVarChar).Value = active; cmd.CommandType = Com

看来你好吗？按照msdn的说法。



试试这个示例代码

Hi,

It seems you don't follow what msdn says.

Try this sample code

using System.Data;
using System.Data.SqlClient;
public class A {
   public static void Main() {
      using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=Northwind;Integrated Security=SSPI;")) {
         connection.Open();
         SqlCommand command= connection.CreateCommand();
         command.CommandText = "SELECT * FROM Categories ORDER BY CategoryID";
         command.CommandTimeout = 15;
         command.CommandType = CommandType.Text;
      }
   }
}

您的代码实例化了从SqlConnection调用Open（）方法之前的CreateCommand（）。

这就是msdn不建议的。



您提供的示例代码确实如此没有显示进度条。

Your code instanciates a CreateCommand() before calling Open() method from SqlConnection.
That's what msdn does not advise.

And the sample code you gave does not show a progress bar.

这篇关于使用进度条从excel导入到sql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！