Abdalla Elawad

Abdalla Elawad

  • NA
  • 1k
  • 215k

How to import data from excel file sheet to sql server

May 24 2016 6:53 AM
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data;
using System.Data.Common;
using System.IO;
using System.Drawing;
namespace ImportExcel
{
public partial class WebForm1 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
{
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();
DbDataReader da = Cmd.ExecuteReader();
string con_str = @"Data Source=.;Initial Catalog=ABDALLA;Integrated Security=True";
SqlBulkCopy bulkInsert = new SqlBulkCopy(con_str);
bulkInsert.DestinationTableName = "tbl_studentdetails";
bulkInsert.WriteToServer(da);
oledbCon.Close();
Array.ForEach(Directory.GetFiles((Server.MapPath("~/temp/"))), File.Delete);
Label1.Visible = true;
Label1.ForeColor = Color.Green;
Label1.Text = "Inserted Data Successfull";
Label2.ForeColor = Color.Red;
Label2.Text = "Change Team on 25/5/2016";
}
else
{
Label1.ForeColor = Color.Red;
Label1.Text="Please select file ";
}
}
}
}

Answers (3)