Friday, December 2, 2011

To Save the data from Excel into Databas

using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Data.OleDb;

public partial class _Default : System.Web.UI.Page
{
    private string strConnection = "Persist Security Info=False;User ID=sa;Password=123456aA;Data Source=DI-PALLAVI; Initial Catalog=Practice";

    protected void Page_Load(object sender, EventArgs e)
    {


    }
    protected void btn_Click(object sender, EventArgs e)
    {
        //Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";
        string excelConnectionString =
@"Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=D:\Employee.xls;
Extended Properties=""Excel 8.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 [EmpID],[EmpName],[ESal] from [Sheet1$]",// sheet1 is name of the sheet in Excel Emp.xls
        excelConnection);

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

        SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
        sqlBulk.DestinationTableName = "Emp";  // Name of the existing table
        //sqlBulk.ColumnMappings.Add("ID", "ID");
        //sqlBulk.ColumnMappings.Add("Name", "Name");
        sqlBulk.WriteToServer(dReader);

    }
}

No comments:

Post a Comment