In this article, I will explain how to import data by selected column from excel to SQL using Asp.net C#.
Open visual studio, create a new website, and drag and drop file upload control, command button, label and double click on command button and paste c# code.
ASPX Code
<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”/>
<br/>
<br/>
<asp:Button ID=”Button1″runat=”server”onclick=”Button1_Click”
Text=”Import Excel To SQL” />
<asp:Label ID=”Label1″ runat=”server”></asp:Label>
</div>
</form>
</body>
</html>
//C# Source Code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Web.Configuration;
public partialclass _Default: System.Web.UI.Page
{
string filepath=“”;
protected voidPage_Load(object sender, EventArgs e)
{
}
protected voidButton1_Click(object sender, EventArgs e)
{
string[] FileFormat = {“.xls”};
string FileExt = System.IO.Path.GetExtension(FileUpload1.FileName);
// HasFile check to make sure a file selected or not
if (FileUpload1.HasFile)
{
// checking file extesion
if (FileFormat.Contains(FileExt))
{
// save the file to server path
filepath = “~/ExcelFile/” + FileUpload1.FileName;
filepath = MapPath(filepath);
FileUpload1.SaveAs(filepath);
ImportDataExcelTOSQL(filepath);
// Label1.Text = “File Uploaded Successfully”;
}
else
{
Label1.Text = “Invalid File Format”;
}
}
}
private voidImportDataExcelTOSQL(string filepth)
{
string Excelstr=“”;
SqlConnection cnn = new SqlConnection();
SqlCommand cmd;
string connectionString = “Data Source=(local) ;Initial Catalog=Test;Integrated Security=True”;
cnn.ConnectionString = connectionString;
cnn.Open();
string strsql = “select * from Studmarks”;
Excelstr=WebConfigurationManager.ConnectionStrings[“ExcelCnn”].ConnectionString;
Excelstr = string.Format(Excelstr,filepth, “Yes”);
OleDbConnection Excelcon=new OleDbConnection(Excelstr);
OleDbCommand Excelcmd=new OleDbCommand();
OleDbDataAdapter excelda=new OleDbDataAdapter();
DataTable exceldt=new DataTable();
Excelcon.Open();
DataTable excelschema;
excelschema = Excelcon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string Sheetnm = excelschema.Rows[0][“Table_Name”].ToString();
Excelcon.Close();
Excelcon.Open();
Excelcmd.Connection = Excelcon;
Excelcmd.CommandText=“SELECT * FROM [“ + Sheetnm +“]”;
excelda.SelectCommand=Excelcmd;
excelda.Fill(exceldt);
Excelcon.Close();
int k,rwcount;
rwcount=exceldt.Rows.Count;
String Name=“”; int slno,Marks;
for(k=0;k<rwcount;k++)
{
slno=Convert.ToInt32(exceldt.Rows[k][0].ToString());
Name=exceldt.Rows[k][1].ToString();
Marks=Convert.ToInt32(exceldt.Rows[k][2].ToString());
strsql = “INSERT INTO Studmarks([idx],[name],[marks]) Values(“ + slno +“,'”+ Name +“‘,”+ Marks +“)”;
cmd = new SqlCommand(strsql,cnn);
cmd.ExecuteNonQuery();
}
cnn.Close();
Label1.Text=“Data Imported Successfully…!”;
}
}
What is in ConnestionSting 'ExcelCnn'