Sample Code to Import Excel data to a table in Database :
Open Source and Detination Connections. Here Source Connection is to read data from Excel File.So to work with Excel file we need to use OleDB Connection. Destination is Database . So open SqlConnection.
Then make sure that Destination table into which Excel data is imported is present the database (else create it).The column names of this table must match with those in the Excel file.
Aspx Code :
| <%@ Page Language="C#" AutoEventWireup="true" CodeFile="UploadExcelFile_StoreinDB.aspx.cs" Inherits="UploadExcelFile_StoreinDB" %> <!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 EXCEL DATA AND STORE ITS CONTENTS IN DATABASE TABLE</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table>
<tr>
<td colspan="2">
<asp:Label ID="LblMsg" runat="server" ForeColor="Red" ></asp:Label>
</td>
</tr>
<tr>
<td colspan="2" align="center">
<asp:Button ID="btnImport" runat="server" Text="Import Excel to DB" OnClick="btnImport_Click" />
</td>
</tr>
</table>
</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.Configuration;
using System.Data.OleDb;
using System.Data.SqlClient; public partial class UploadExcelFile_StoreinDB : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{ }
protected void btnImport_Click(object sender, EventArgs e)
{
string SourceConnStr = "PROVIDER=Microsoft.ACE.OLEDB.12.0;Data Source=C:/Users/Administrator/Desktop/SavetoDB.xlsx;Extended Properties='Excel 12.0 Xml;HDR=YES'";
string DestConnStr = ConfigurationManager.ConnectionStrings["MyConString"].ToString();
OleDbConnection SourceConn = new OleDbConnection(SourceConnStr);
using (SourceConn)
{
string sql = string.Format("Select [ID],[Name],[Age],[Address] FROM [{0}]", "Sheet1$");
OleDbCommand command = new OleDbCommand(sql, SourceConn);
SourceConn.Open();
using (OleDbDataReader dr = command.ExecuteReader())
{
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(DestConnStr))
{
bulkCopy.DestinationTableName = "tblImportfromExcel";
/*COLUMNN MAPPING STARTS*/
/*YOU CAN MANNUALY SET THE COLUMN MAPPING BY THE FOLLOWING WAY , IF COLUMN ORDER IS DIFFERENT FROM EXCEL.
IF DESTINATION TABLE HAS COLUMNS IN THE SAME ORDER AS EXCEL (ID , NAME,AGE,ADDRESS)
,THEN U CAN COMMENT THE BELOW CODE FOR MAPPING COLUMNS*/
bulkCopy.ColumnMappings.Add("ID", "ID");
bulkCopy.ColumnMappings.Add("Name", "Name");
bulkCopy.ColumnMappings.Add("Age", "Age");
bulkCopy.ColumnMappings.Add("Address", "Address");
/*COLUMNN MAPPING END*/
bulkCopy.WriteToServer(dr);
LblMsg.Text = "Import Data from Excel To DB is Successful";
}
}
}
} } |
Output :
Error in connection string
ReplyDelete