Hi guys I got one requirement from one on my client like he want to upload excel sheet the what ever data is there in the excel that will insert in your database, so to do this I used sql server bulk insert concept.
Here I will show you step by step how to use Sql Server Bulk
Insert.
Step – 1
Create a table in the sql server like this
Step -2
Create a excel file like this
Step – 3
Create a website in .net 4.0 and design your page like this
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default"
%>
<!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="Button1" runat="server" Text="Upload" onclick="Button1_Click" />
<br />
<br />
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
<br />
</div>
</form>
</body>
</html>
|
Step – 5
Create a folder in your application like this
Step – 6
Now add the following code in your aspx.cs file
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;
using System.Data;
public partial
class _Default
: System.Web.UI.Page
{
protected
void Page_Load(object
sender, EventArgs e)
{
if
(!IsPostBack)
{
GetData();
}
}
private void GetData()
{
SqlConnection
sql = new SqlConnection(ConfigurationManager.ConnectionStrings["conn"].ToString());
SqlCommand
cmd = new SqlCommand("Select * from tbl_Employee", sql);
SqlDataAdapter
da = new SqlDataAdapter(cmd);
DataSet
ds = new DataSet();
da.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
}
protected
void Button1_Click(object
sender, EventArgs e)
{
if
(FileUpload1.HasFile)
{
FileUpload1.SaveAs(Server.MapPath("~/Excel/"
+ FileUpload1.FileName));
string
excelConnection = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data
Source={0};Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';",
Server.MapPath("~/Excel/" +
FileUpload1.FileName));
try
{
using
(OleDbConnection conncetion = new OleDbConnection(excelConnection))
{
OleDbCommand
command = new OleDbCommand("SELECT ID,NAME,SALARY FROM [Sheet1$]",
conncetion);
conncetion.Open();
using
(DbDataReader dr =
command.ExecuteReader())
{
string sqlConncetionString = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
using (SqlBulkCopy
bulkCopy = new SqlBulkCopy(sqlConncetionString))
{
bulkCopy.DestinationTableName = "tbl_Employee";
bulkCopy.WriteToServer(dr);
}
}
}
}
catch
(Exception ex)
{
}
finally
{
GetData();
}
}
}
}
|
No comments:
Post a Comment