Author : Prakash Pradeep Gopu
bulkCopy.WriteToServer();
The Import the Excel data to Sql server Table we need to follow the following steps :
1) Create Excel sheet data and same structure you need to create a Sql server Table
2) Load the Excel Data in to Data Reader and Using SqlBulkCopy load the Reader data in to Sql server.
The following post will explain how to import the Excel sheet data to the Sql server Table using SqlBulkcopy.
The SqlBulkCopy class can be used to write bulk data only to SQL Server tables. However, the data source is not limited to SQL Server; any data source can be used, as long as the data can be loaded to a DataTable instance or read with a IDataReader instance. To copy the source data to destination table in the sql server, need to set destination table name and execute “WriteToServer” method.The following three steps you need to do
// create sqlBulkCopy object
SqlBulkCopy bulkCopy = new SqlBulkCopy(connString);
SqlBulkCopy bulkCopy = new SqlBulkCopy(connString);
// set destination table name for sqlBulkcopy object
bulkCopy.DestinationTableName = "[Table Name]";
// write source data to destination sql table bulkCopy.DestinationTableName = "[Table Name]";
bulkCopy.WriteToServer();
The Import the Excel data to Sql server Table we need to follow the following steps :
1) Create Excel sheet data and same structure you need to create a Sql server Table
2) Load the Excel Data in to Data Reader and Using SqlBulkCopy load the Reader data in to Sql server.
Create Excel sheet data and same structure you need to create a Sql server Table
First we will create a Excel sheet as same as below image :
Second we will create a Table Same structure(with column names) in sqlserver as shown in the figure
Design your aspx page like this
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table>
<tr>
<td>
<span style="color: Red">*</span>Attach Excel file
</td>
<td>
<asp:FileUpload ID="fileuploadExcel" runat="server" />
</td>
</tr>
<tr>
<td></td>
<td>
<asp:Button ID="btnSend" runat="server" Text="Export" onclick="btnSend_Click" />
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
Load the Excel Data in to Data Reader and Using SqlBulkCopy load the Reader data in to Sql server
Copy the following code in the Button click
protected void btnSend_Click(object sender, EventArgs e)
{
String strConnection = ConfigurationManager.ConnectionStrings["Bloggerconnection"].ToString();
//file upload path
string path = fileuploadExcel.PostedFile.FileName;
//Create connection string to Excel work book
string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=False";
//Create Connection to Excel work book
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
//Create OleDbCommand to fetch data from Excel
OleDbCommand cmd = new OleDbCommand("Select [EmpID],[Name],[EmpDesignation] from [Sheet1$]", excelConnection);
excelConnection.Open();
OleDbDataReader dReader;
dReader = cmd.ExecuteReader();
//Create a bulkcopy object
SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
//Give your Destination table name
sqlBulk.DestinationTableName = "Tbl_Excelread";
//write to the values to the DB
sqlBulk.WriteToServer(dReader);
excelConnection.Close();
}
The following statement will read the connection string from the Web.config
String strConnection = ConfigurationManager.ConnectionStrings["Bloggerconnection"].ToString();
So you need to add the connection string in web.config as follows under the <connectionStrings> xml tag :
<add name="Bloggerconnection" connectionString="Data Source=PRAKASH\SQLEXPRESS;Initial Catalog=Practice;Integrated Security=True"/>
The following is the connection string for the Excel sheet
string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=False";
Where Path is the Excel sheet file path this we are uploading the file using the FileUpload control in asp.net
OleDbCommand cmd = new OleDbCommand("Select [EmpID],[Name],[EmpDesignation] from [Sheet1$]", excelConnection);
By using this query we are getting data from Sheet1 of Excel sheet that's why i have circled Sheet1 in Excel sheet and column names must be same as the Excel sheet column names.
No comments:
Post a Comment