Thursday, January 20, 2011
upload excel file and upload to sql-server c# asp.net 4.0
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div> <asp:FileUpLoad id="FileUpLoad1" runat="server" /><asp:Button id="UploadBtn" Text="Upload File" OnClick="UploadBtn_Click" runat="server" Width="105px" CssClass="but" />
<br />
<br />
<br />
<br />
<asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
</div>
</form>
</body>
</html>
code behind
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;
using System.Data.Common;
using System.Data.SqlClient;
using System.Configuration;
public partial class Default2 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void UploadBtn_Click(object sender, EventArgs e)
{
if (FileUpLoad1.HasFile)
{
string filePath = FileUpLoad1.PostedFile.FileName;
string filenameStor = FileUpLoad1.FileName;
FileUpLoad1.SaveAs(@"c:\\" + "Book1.xls");
Label1.Text = "File Uploaded : " + FileUpLoad1.PostedFile.FileName;
datatosql();
}
else
{
Label1.Text = "No File Uploaded.";
}
}
void datatosql()
{
string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\Book1.xls;Extended Properties=""Excel 8.0;HDR=NO;IMEX=1""";
using (OleDbConnection connection = new OleDbConnection(strConn))
{
OleDbCommand command = new OleDbCommand("Select * FROM [Sheet1$]", connection);
connection.Open();
using (DbDataReader dr = command.ExecuteReader())
{
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(ConnectionString))
{
bulkCopy.DestinationTableName = "testexceldata";
bulkCopy.WriteToServer(dr);
}
}
}
}
private string ConnectionString
{
get
{
string connectionString = ConfigurationManager.ConnectionStrings["smdbConnectionString"].ConnectionString;
return connectionString;
}
}
}
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment