<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ImportDataExcelToSql.aspx.cs"
Inherits="ImportDataExceltoSqlServer.ImportDataExcelToSql" %>
<!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>Import data from excel to sql server</title>
</head>
<body>
<form id="frmUpload" runat="server">
<div id="divupload" style="margin: auto; width: 600px;">
<table style="width: 100%;">
<tr>
<td style="width: 60%;">
Enter File Name(Ex : Exact Path)
</td>
<td style="text-align: left;">
<asp:TextBox ID="TxtFileName" runat="server"></asp:TextBox>
<asp:RequiredFieldValidator ID="RFVFileName" runat="server" ErrorMessage="*" ControlToValidate="TxtFileName"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td style="text-align: left;">
Enter Sheet No(Ex : sheet1,sheet2..etc)
</td>
<td style="text-align: left;">
<asp:TextBox ID="TxtSheetNo" runat="server"></asp:TextBox>
<asp:RequiredFieldValidator ID="RFVSheetNo" runat="server" ErrorMessage="*" ControlToValidate="TxtSheetNo"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td style="text-align: left;">
Enter Server Name
</td>
<td style="text-align: left;">
<asp:TextBox ID="TxtServerName" runat="server"></asp:TextBox>
<asp:RequiredFieldValidator ID="RFVServerName" runat="server" ErrorMessage="*" ControlToValidate="TxtServerName"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td style="text-align: left;">
Enter Data Base Name
</td>
<td style="text-align: left;">
<asp:TextBox ID="TxtDataBase" runat="server"></asp:TextBox>
<asp:RequiredFieldValidator ID="RFVDataBase" runat="server" ErrorMessage="*" ControlToValidate="TxtDataBase"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td style="text-align: left;">
Enter Table Name
</td>
<td style="text-align: left;">
<asp:TextBox ID="TxtTableName" runat="server"></asp:TextBox>
<asp:RequiredFieldValidator ID="RFVTableName" runat="server" ErrorMessage="*" ControlToValidate="TxtTableName"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td style="text-align: left;">
Enter UserId
</td>
<td style="text-align: left;">
<asp:TextBox ID="TxtUserId" runat="server"></asp:TextBox>
<asp:RequiredFieldValidator ID="RFVUserId" runat="server" ErrorMessage="*" ControlToValidate="TxtUserId"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td style="text-align: left;">
Enter Password
</td>
<td style="text-align: left;">
<asp:TextBox ID="TxtPassword" runat="server" TextMode="Password"></asp:TextBox>
<asp:RequiredFieldValidator ID="RFVPassword" runat="server" ErrorMessage="*" ControlToValidate="TxtPassword"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td style="text-align: left;">
<asp:Literal ID="LtrMessage" runat="server"></asp:Literal>
</td>
<td style="text-align: left;">
<asp:Button ID="BtnImport" runat="server" Text="Import" OnClick="BtnImport_Click"
Width="100px" />
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
================================================================
C # Code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.IO;
using System.Data.SqlClient;
using System.Data.Common;
using System.Data.OleDb;
namespace ImportDataExceltoSqlServer
{
public partial class ImportDataExcelToSql : System.Web.UI.Page
{
String ExcelConnectionString, SqlConnectionString;
OleDbConnection OleConn;
OleDbCommand OleCmd;
SqlBulkCopy SqlCopy;
DbDataReader Dr;
protected void Page_Load(object sender, EventArgs e)
{
}
protected void BtnImport_Click(object sender, EventArgs e)
{
try
{
ExcelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + TxtFileName.Text + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
OleConn = new OleDbConnection(ExcelConnectionString);
OleConn.Open();
OleCmd = new OleDbCommand("SELECT * FROM ["+TxtSheetNo.Text+"$]", OleConn);
Dr = OleCmd.ExecuteReader();
SqlConnectionString = "Data Source="+TxtServerName.Text+"; User id="+TxtUserId.Text+"; Password="+TxtPassword.Text+"; Initial Catalog="+TxtDataBase.Text+"";
SqlCopy = new SqlBulkCopy(SqlConnectionString);
SqlCopy.DestinationTableName = TxtTableName.Text;
SqlCopy.WriteToServer(Dr);
LtrMessage.Text = "Data imported successfully...";
}
catch (Exception ex)
{
LtrMessage.Text = ex.Message;
}
}
}
}
No comments:
Post a Comment