Showing posts with label Import Excel To Sql in c# Dotnet. Show all posts
Showing posts with label Import Excel To Sql in c# Dotnet. Show all posts

Tuesday 24 January 2012

Import Excel To Sql in c# Dotnet

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data.OleDb;
using System.Data.SqlClient;

public partial class Default2 : System.Web.UI.Page
{
      string strConnection = ConfigurationManager.ConnectionStrings
        ["MarinaNewConnectionString"].ConnectionString;
  
     
        protected void Page_Load(object sender, EventArgs e)
        {
             string excelConnectionString =
            @"Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:\UniqueMarinaData.xls;
Extended Properties=""Excel 4.0;HDR=YES;""";

            //Create Connection to Excel work book
            OleDbConnection excelConnection =
            new OleDbConnection(excelConnectionString);

            //Create OleDbCommand to fetch data from Excel
            OleDbCommand cmd = new OleDbCommand
            ("Select [WebSource],[MarinaName],[Address],[Region],[Country],[Fax],[Phone],[Email],[Website],[Latitude],[Longitude],[NumberOfMoorings],[MGiD] from [Sheet1$]",
            excelConnection);

            excelConnection.Open();
            OleDbDataReader dReader;
            dReader = cmd.ExecuteReader();

            SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
            sqlBulk.DestinationTableName = "Marina";
            //sqlBulk.ColumnMappings.Add("ID", "ID");
            //sqlBulk.ColumnMappings.Add("Name", "Name");
            sqlBulk.WriteToServer(dReader);
        }
      
}