Import Data to DataBase Asp.net
<%@ Page Language="C#"
AutoEventWireup="true"
CodeBehind="ImportdataToserver.aspx.cs"
Inherits="LastSessionApp.RestrCP.ImportdataToserver"
%>
<!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>Untitled
Page</title>
</head>
<title>::
Importing Data From Excel Sheet to SQL Server ::</title>
<body>
<form id="form1"
runat="server">
<div>
<div>
<asp:FileUpload ID="FileUpload1"
runat="server"
/>
<asp:Button ID="Button1" runat="server" Text="Button" onclick="Button1_Click" />
</div>
<asp:GridView ID="grdImpordata" runat="server">
<Columns>
<asp:BoundField DataField="Id" HeaderText="Id" />
<asp:BoundField DataField="EmpName" HeaderText="Name" />
<asp:BoundField DataField="Location" HeaderText="Location" />
</Columns>
</asp:GridView>
<asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
</div>
</form>
</body>
</html>
create table EmpImport (Id int identity(1,1) Not Null, EmpName varchar(50), Location varchar(50) )
insert into EmpImport values
('Shahajan','Dheli')
select * from EmpImport
Create Procedure Usp_GetImportedData
as
begin
select Id,EmpName,Location from EmpImport
end
using System;
using
System.Collections;
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;
using System.IO;
using
System.Data.Common;
namespace
LastSessionApp.RestrCP
{
public partial class ImportdataToserver : System.Web.UI.Page
{
DataAdapter
objDataAdapter = null;
SqlCommand
cmd = null;
private
string saveLocation;
public
ImportdataToserver()
{
string
strConnection = ConfigurationManager.AppSettings["constr"].ToString();
}
protected
void Page_Load(object
sender, EventArgs e)
{
grdImpordata.DataSource =
getImporteddata();
grdImpordata.DataBind();
}
protected
void Button1_Click(object
sender, EventArgs e)
{
if
(FileUpload1.HasFile)
{
string
tempFileName = FileUpload1.FileName;
string
tempFileLocation = HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["FileLocation"]);
saveLocation = Path.Combine(tempFileLocation, tempFileName);
FileUpload1.SaveAs(saveLocation);
if
(File.Exists(saveLocation))
{
string
strExcelConnection = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data
Source={0};Extended Properties=Excel 12.0;", saveLocation);
try
{
using (OleDbConnection
conncetion = new OleDbConnection(strExcelConnection))
{
OleDbCommand command = new
OleDbCommand("SELECT
ID,Name,Location FROM [Sheet1$]", conncetion);
conncetion.Open();
using (DbDataReader
dr = command.ExecuteReader())
{
string sqlConncetionString = ConfigurationManager.AppSettings["constr"].ToString();
using (SqlBulkCopy
bulkCopy = new SqlBulkCopy(sqlConncetionString))
{
bulkCopy.DestinationTableName = "EmpImport";
bulkCopy.WriteToServer(dr);
}
}
}
}
catch
(Exception ex)
{
Label1.Text =
ex.ToString();
}
}
}
}
public DataSet getImporteddata()
{
SqlConnection
con = new SqlConnection();
con=new
System.Data.SqlClient.SqlConnection (ConfigurationManager.AppSettings["constr"].ToString());
cmd= new
SqlCommand("Usp_GetImportedData",con);
cmd.CommandType=CommandType.StoredProcedure;
SqlDataAdapter
objDataAdapter=new SqlDataAdapter
(cmd);
DataSet
ds=new DataSet
();
objDataAdapter.Fill(ds);
return
ds;
}
}
}
No comments:
Post a Comment