Tuesday, January 17, 2012

To Save the data from Excel into Database

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: