CODEWITHSUNDEEP
c#excelwinformsdevexpressprogress-bar
aminvincent
aminvincent

Reputation: 603

Show a Progress Bar Control devexpress during import data from Excel

I have form import data Excel like this:

enter image description here

First scenario when I select file Excel then press process, my form will show process in progress bar control devexpress.

My code:

    string constr = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source =" + txtPath.Text + "; Extended Properties =\"Excel 8.0; HDR = Yes;\";";
OleDbConnection con = new OleDbConnection(constr);

OleDbCommand ocmd = new OleDbCommand("SELECT * FROM[Sheet1$]", con);
con.Open();
OleDbDataReader odr = ocmd.ExecuteReader();
string kode = "";
string nama = "";
string satuan = "";
string isAktif = "";
string kodeRek = "";

while (odr.Read())
{
    //get value from excel file column
    kode = odr[0].ToString();
    nama = odr[1].ToString();
    satuan = odr[2].ToString();
    isAktif = odr[3].ToString();
    kodeRek = odr[4].ToString();

    //connection
    if (koneksi_manual.con.State == ConnectionState.Open)
    {
        koneksi_manual.con.Close();
    }
    koneksi_manual.con.Open();

    //insert data excel to oracle database
    OracleCommand cd = new OracleCommand();
    cd.CommandText = @"INSERT INTO RAWMATERIAL(ID_BAHAN, KODE_BAHAN, NAMA_BAHAN, UNIT, ISAKTIF, JENIS_BAHAN, 
                        HARGA_BAHAN, CREATEDBY, CREATEDDATE, MODIFIEDBY, MODIFIEDDATE)
                        (SELECT SQ_BAHAN.NEXTVAL, '" + kode + "', '" + nama + "', '" + satuan + "', '" + isAktif +
                        "', '" + null + "', '" + null + "', '" + Program.UserLogin + "', '" + DateTime.Now +
                        "', '" + Program.UserLogin + "', '" + DateTime.Now +
                        "' FROM DUAL WHERE NOT EXISTS(SELECT * FROM RAWMATERIAL WHERE NAMA_BAHAN='" + nama + "'))";
    cd.Connection = koneksi_manual.con;
    cd.ExecuteReader();
}
odr.Close();

It is running well when I insert data from Excel to Oracle database.

Can anyone suggest how to show progress bar control process during insert data from Excel to Oracle database?

Upvotes: 1

Views: 6865

Answers (2)

pedromauro
pedromauro

Reputation: 11

The answer given by jhmt works great. Very nice and clean explanation. Just adding some changes I needed to make it work for me, since I'm using devexpress ProgressBarControl. In case someone need it to...

In step 3:

progressbarcontrol.Maximum = count;

Changed to:

progressbarcontrol.Properties.Maximum = count;

And added this line after:

backgroundWorker1.RunWorkerAsync();

In step 6:

progressbarcontrol.Value = e.ProgressPercentage;

Changed to:

progressbarcontrol.EditValue = e.ProgressPercentage;

Hope it helps!

Upvotes: 1

jhmt
jhmt

Reputation: 1421

BackgroundWorker makes this easier.

1) Drag and drop BackgroundWorker control to your form in Design view.

2) Register DoWork and ProgressChanged event handlers.

backgroundWorker1.DoWork += BackgroundWorker1_DoWork;
backgroundWorker1.ProgressChanged += BackgroundWorker1_ProgressChanged;

3) Set the count of records you are going to insert.

OleDbCommand ocCount = new OleDbCommand("SELECT COUNT(*) FROM [Sheet1$]", con);

con.Open();
int count = (int)ocCount.ExecuteScalar();

progressbarcontrol.Maximum = count;

4) Move the Insert loop in BackgroundWorker1_DoWork event handler

private void BackgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
{
    // insert loop
}

5) Set WorkerReportsProgress true, and call ReportProgress method within the loop.

backgroundWorker1.WorkerReportsProgress = true;
int progress = 0;
while (odr.Read())
{
    backgroundWorker1.ReportProgress(++progress);
}

6) Increment the value in BackgroundWorker1_ProgressChanged event handler.

private void BackgroundWorker1_ProgressChanged(object sender, ProgressChangedEventArgs e)
{
    progressbarcontrol.Value = e.ProgressPercentage;
}

Also, you can reset the progress bar in RunWorkerCompleted event handler.

Upvotes: 4

Related Questions