Friday, 9 December 2016

Upload / Import excel with bulk import

Drag a button control in aspx page and find it button_click event and write these code inside
protected void Button1_Click(object sender, EventArgs e)
    {string path = string.Concat((Server.MapPath("~/temp/" FileUpload1.FileName)));
            FileUpload1.PostedFile.SaveAs(path);
            OleDbConnection OleDbcon = new OleDbConnection("Provider=Microsoft.Ace.OLEDB.12.0; Data Source =" path "; Extended Properties=Excel 12.0;");
            OleDbCommand cmd = new OleDbCommand("Select * from [Sheet1$]", OleDbcon);
            OleDbDataAdapter objAdapter1 = new OleDbDataAdapter(cmd);

            OleDbcon.Open();
            OleDbDataReader dr = cmd.ExecuteReader();
            // SqlConnection con =new  SqlConnection(connection.cstr);
            SqlConnection con = new SqlConnection(connection.cstr);
            con.Open();
           // string con_str = @"Data Source=SANDEEP-PC\\SQLEXPRESS;Initial Catalog=Tmndx;Integrated Security=True";
            SqlBulkCopy bulkInsert = new SqlBulkCopy(con);
            bulkInsert.DestinationTableName = "country_code";
            bulkInsert.WriteToServer(dr);
            OleDbcon.Close();

            Array.ForEach(Directory.GetFiles((Server.MapPath("~/temp/"))), File.Delete);
            Label1.ForeColor = Color.Green;
            Label1.Text = "Successfully Inserted";
        }
        else
        {
            Label1.ForeColor = Color.Red;
            Label1.Text = "Please Select the File";
        }
    }

No comments:

Post a Comment