I am trying to import data from Excel into SQL Server 2008 R2. Using the OLEDB provider. I am using Visual Studio 2010. ACE.OLEDB provider is not registered with my Visual Studio. I have 100,000 records in my Excel but it is copying only 65,535. The following is my code:
namespace WebApplication1
{
public partial class Upload_page : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnUpload_Click(object sender, EventArgs e)
{
Response.Write("Working");
string path = "path";
OleDbConnection connection = null;
if (FileUpload1.HasFile)
{
path = string.Concat(Server.MapPath("~/Uploaded Folder/" + FileUpload1.FileName));
FileUpload1.SaveAs(path);
}
path = "C:\\Users\\temp\\Documents\\visual studio 2010\\Projects\\WebApplication1\\WebApplication1\\Uploaded Folder\\TempReport.xls";
try
{
string excelConnectionString = string.Format("Provider=Microsoft.jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0", path);
connection = new OleDbConnection();
connection.ConnectionString = excelConnectionString;
OleDbCommand command = new OleDbCommand("select * from [ISO$]", connection);
connection.Open();
// Create DbDataReader to Data Worksheet
DbDataReader dr = command.ExecuteReader();
string sqlConnectionString = @"Initial Catalog=CensusTrack;Data Source=CWVQ-2K12E1D;Integrated Security=SSPI;";
// Bulk Copy to SQL Server
SqlBulkCopy bulkInsert = new SqlBulkCopy(sqlConnectionString);
bulkInsert.BulkCopyTimeout = 0;
bulkInsert.BatchSize =0;
bulkInsert.DestinationTableName = "[dbo].[2015_ISO]";
bulkInsert.WriteToServer(dr);
connection.Close();
Response.Write("Completed");
//Label1.Text = "Ho Gaya";
}
catch (Exception obj)
{
connection.Close();
}
}
}
}
Can any one help me so that I can copy all the data from the Excel to the database table in one go?
