Sunday, February 1, 2009

Import Excel TO Sequel database BY click on asp.net page button

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using Microsoft.VisualBasic;
using System.IO;
using System.Net;
using System.Text;
using System.Data.OleDb;
using System.Data.Common;
using System.Diagnostics;



public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{


}


protected void Button1_Click(object sender, EventArgs e)
{
// Connection String to Excel Workbook
//string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=Book1.xls;Extended Properties=""Excel 8.0; HDR=YES;""";
String strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=C:\\tblprod.xls;" + "Extended Properties=Excel 8.0;";
// Create Connection to Excel Workbook
//using (OleDbConnection = new OleDbConnection(excelConnectionString)) ;
OleDbConnection connection = new OleDbConnection(strConn);
OleDbCommand command = new OleDbCommand("Select * FROM [Data$]", connection);
connection.Open();
// Create DbDataReader to Data Worksheet
//using (SqlDataReader dr = command.ExecuteReader())
DbDataReader dr = command.ExecuteReader();


//// SQL Server Connection String
//string sqlConnectionString = "Data Source=.;Initial Catalog=Test;Integrated Security=True";
//string sqlConnectionString = "Data Source=.;Initial Catalog=exceldata;Integrated Security=True";
string sqlConnectionString = "server=system2;database=goelco;uid=sa";
// Bulk Copy to SQL Server
//using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString);
bulkCopy.DestinationTableName = "tblprod";
bulkCopy.WriteToServer(dr);
dr.Close();
}

No comments:

Post a Comment