using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OracleClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
namespace Exporting
{
/// <summary>
/// Interaction logic for MainWindow.xaml
/// </summary>
public partial class MainWindow : Window
{
private static string CONNECTION_STRING = "User Id=tpl_owner;Password=TPL_OWNER2;Data Source=(DESCRIPTION=" + "(ADDRESS=(PROTOCOL=TCP)(HOST=lntpld1.uk.db.com)(PORT=1801))" + "(CONNECT_DATA=(SID=LNTPLD1)));";
DateTime dt1 = DateTime.Now;
DataTable Dt = new DataTable();
//http://devtoolshed.com/content/c-download-file-progress-bar
public MainWindow()
{
InitializeComponent();
worker.DoWork += worker_DoWork;
worker.RunWorkerCompleted += worker_RunWorkerCompleted;
worker.ProgressChanged += worker_ProgressChanged;
}
private void worker_DoWork(object sender, DoWorkEventArgs e)
{
dt1 = DateTime.Now;
getData( worker);
}
private void worker_ProgressChanged(object sender, ProgressChangedEventArgs e)
{
pg.Value = e.ProgressPercentage;
}
private void worker_RunWorkerCompleted(object sender,
RunWorkerCompletedEventArgs e)
{
DateTime dt2 = DateTime.Now;
TimeSpan sp = dt2.Subtract(dt1);
pg.Visibility = Visibility.Collapsed;
MessageBox.Show("Excel created E:\\RastPro\\testPersonExceldata.xlsx : Total time :" + sp.Milliseconds.ToString() + "--" + sp.Seconds.ToString());
//update ui once worker complete his work
}
private readonly BackgroundWorker worker = new BackgroundWorker();
private void Button_Click(object sender, RoutedEventArgs e)
{
try
{
worker.WorkerReportsProgress = true;
pg.Visibility = Visibility.Visible;
pg.Value = 10;
worker.RunWorkerAsync();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
static DataTable GetTable()
{
//
// Here we create a DataTable with four columns.
//
DataTable table = new DataTable();
table.Columns.Add("ID", typeof(int));
table.Columns.Add("Name", typeof(string));
table.Columns.Add("Sex", typeof(string));
table.Columns.Add("CreatedDate", typeof(string));
table.Columns.Add("City", typeof(string));
//
// Here we add five DataRows.
//
table.Rows.Add(25, "Devesh Omar", "M", DateTime.Now,"Noida");
table.Rows.Add(50, "Nikhil Vats", "M", DateTime.Now,"Noida");
table.Rows.Add(10, "Heena Sharma", "F", DateTime.Now,"Delhi");
table.Rows.Add(21, "Nancy Sharma", "F", DateTime.Now, "Delhi");
table.Rows.Add(100, "Avinash", "M", DateTime.Now, "Delhi");
table.Rows.Add(25, "Devesh gupta", "M", DateTime.Now, "Delhi");
table.Rows.Add(50, "Nikhil gupta", "M", DateTime.Now, "Noida");
table.Rows.Add(10, "HS gupta", "F", DateTime.Now, "Delhi");
table.Rows.Add(21, "VS gupta", "F", DateTime.Now, "Delhi");
table.Rows.Add(100, "RJ gupta", "M", DateTime.Now, "Delhi");
return table;
}
void getData(BackgroundWorker worker)
{
try
{
OracleConnection conn = new OracleConnection();
conn.ConnectionString = CONNECTION_STRING;
conn.Open();
int progress = 10;
using (var cmd = new OracleCommand())
{
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT count(*) AS Count FROM UNDERLYING";
OracleDataAdapter oDa = new OracleDataAdapter();
oDa.SelectCommand = cmd;
DataSet ds = new DataSet();
oDa.Fill(ds, "dtMy");
Dt = ds.Tables["dtMy"];
int totalRows = Convert.ToInt32(Dt.Rows[0]["Count"].ToString()) % 500;
int reminder = Convert.ToInt32(Dt.Rows[0]["Count"].ToString()) / 500;
//where ROWNUM <= 5
int prev = 0;
DataTable dttemp = new DataTable();
for (int icounter = 0; icounter <= totalRows - 1; icounter++)
{
progress = progress + progress *5/100;
worker.ReportProgress(progress);
int last = prev + 500;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM table WHERE ROWNUM between " + prev + " and " + last;
oDa = new OracleDataAdapter();
oDa.SelectCommand = cmd;
ds = new DataSet();
oDa.Fill(ds, "dtMy");
Dt = ds.Tables["dtMy"];
prev = prev+501;
foreach (DataRow datRow in Dt.Rows)
{
dttemp.ImportRow(datRow);
}
}
worker.ReportProgress(progress);
Excel.ExcelUtlity obj = new Excel.ExcelUtlity();
obj.WriteDataTableToExcel(Dt, "Person Details", "E:\\RastPro\\testPersonExceldata.xlsx", "Details", worker);
}
}catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
}
No comments:
Post a Comment