We help IT Professionals succeed at work.

How can i start a MultiThread Heavy Excel data to add Sql Table?

programmerist
on

  i want to start Multi Thread while adding 20000 rows Excel Data to sql data.How can i do that?
 
Comment
Watch Question

Gorkem YukselDirector, IT Development

Commented:
Hi,

If you already have a method or function created for adding the rows, you can spawn a new thread to do this.  Below is a sample snippet that can get you started.  This example will start off the worker thread and return control back to your application.  You can take this one step further and fire off an event when the import is finished so you can notify the user that it has completed.

public void Form_Load()
{
// Main logic of code here....
// This is the point where the thread is branch off
Thread t = new Thread(new ThreadStart(StartProcess));
t.Start();
}

public void StartProcess()
{
      //Call the import function
      DoImport();
}

public void DoImport()
{
     // Do your import here..    
}

--Lets have a start processing method in your form.
Also Lets Assume:-

1. you have excel file's path in class variable say

public partial class Form1 : Form
    {

        private string filePath="";

        public Form1()
        {
            InitializeComponent();
        }

private void btnStartImport_Click(object sender, EventArgs e)
{
//Delegate execution of this thread to the StartAsyncProcessing method
ThreadStart ts = new ThreadStart(StartAsyncProcessing);

//Create the thread
Thread t = new Thread(ts);

//Begin
t.Start();

//Some post thread start code goes here
}

public Void StartAsyncProcessing()
{
//Conn string like
//            <add name="xls"connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Sample1.xls;Extended Properties=Excel 8.0"/>
//            <add name="xlsx"connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Sample.xlsx;Extended Properties=Excel 12.0"/>
//These will allow you to directly read from Excel file using normal ADO.Net

    OleDbConnection oledbConn = new OleDbConnection(connString);
        try
        {
            // Open connection
            oledbConn.Open();
 
            // Create OleDbCommand object and select data from worksheet Sheet1
            OleDbCommand cmd = new OleDbCommand("SELECT * FROM [Sheet1$]", oledbConn);
 
            // Create new OleDbDataAdapter
            OleDbDataAdapter oleda = new OleDbDataAdapter();
 
            oleda.SelectCommand = cmd;
 
            // Create a DataSet which will hold the data extracted from the worksheet.
            DataSet ds = new DataSet();
 
            // Fill the DataSet from the data extracted from the worksheet.
            oleda.Fill(ds, "Employees");

           //Insert each entry in Dataset to SQL
}
catch(Exception ex)
{

}
}
    }