How To Import Excel File Data In Database And Display It In Gridview Control In Asp.Net.

Introduction:-
In this tutorial ,we can easily import Your Excel file data in your database (Sql server ,Oracle,MySQL ,etc) and display it in gridview control using c# in asp.net .Here i have inserted an excel file data in sql server database, you can apply same concepts with other database also but you have to change your connection strings codes only. Here you can solve your visual studio connection error problem also as given below:-
""The Microsoft Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data."
Description:- 
First i was thought different concept to make this application but i am getting above an error as shown in red color latter. To solve this error i have applied many techniques such as

• Change my excel database connection strings. 
• Change my excel file folder permissions also.
• Share the file on other network also. etc .

But i have unable to solve this error.I have spent one day for this post,but finally got a solution. which i am going to share with you. I was wanted to develop an user friendly application so that any user can be uploaded your excel files from your desktop and got your required output. 

Here any user will be performed some operations such as

1. First upload an excel file from your desktop and press Save file on server Button.
2. After that press Insert Data in sql database button.
3. After that press show in gridview  button

Here i have not deleted server file (website folder),you can delete it manually or using c# codes after complete the whole steps.But you can save only one copy of it in this application (project). So no need to delete this file(excel) on server.
There are some step by step procedure to develop this whole application which are given below:-
Step 1 :- First open your visual studio --> File --> New --> website --->Select ASP.NET Empty Website -->OK --> Now Add a new web form (Default.aspx) in your solution Explorer --> Drag and drop Label ,FileUpload,Button and Gridview controls from toolbox on the page as shown below:-



Step 2 :- Now create an Excel file (ExcelBook1) --> Create columns ( SID ,NAME, AGE,LOCATION ) and insert some data also as shown below:-  

Step 3 :- Now Open your sql server Management studio database and create a table (student_excel) with fourcolumns as shown below:-

Note:- Number of column should be same as your excel sheet.

Step 4 :-  Now write the c# codes on each buttons behind (default.aspx.cs) as given below:-

001	using System;

002	using System.Web;

003	using System.Web.UI;

004	using System.Web.UI.WebControls;

005	using System.Data;

006	using System.Data.SqlClient;

007	using System.Data.OleDb;

008	using System.IO;

009

010	public partial class _Default : System.Web.UI.Page

011

{

012	protected void Page_Load(object sender, EventArgs e)

013

{

014

015

}

016	protected void Button1_Click(object sender, EventArgs e)

017	{    //Get file name when you upload any file

018	string file_name = Path.GetFileName(FileUpload1.PostedFile.FileName);

019	// save the file on server(website)

020	FileUpload1.SaveAs(Server.MapPath( file_name));

021	// save file name in sessio objet

022	Session["name"] = file_name;

023	//refresht the page

024	Response.Redirect("Default.aspx");

025

}

026	protected void Button2_Click(object sender, EventArgs e)

027

{

028	//string myfile_name = Path.GetFileName(FileUpload1.PostedFile.FileName);

029	// Assign session object data in myfile_name variable

030	string myfile_name = Session["name"].ToString();

031	// Get the file(excel)complete path and assign in Excel_path variable

032	string Excel_path = Server.MapPath(myfile_name);

033	// create connection with excel database

034	OleDbConnection my_con = newOleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+Excel_path+";Extended Properties=Excel 8.0;Persist Security Info=False");

035	my_con.Open();

036

try

037

{

038	// get the excel file data and assign it in OleDbcoomad object(o_cmd)

039	OleDbCommand o_cmd = new OleDbCommand("select*from [Sheet1$]", my_con);

040	//create oledbdataadapter object

041	OleDbDataAdapter da = new OleDbDataAdapter();

042	// pass o_cmd data to da object

043	da.SelectCommand = o_cmd;

044	//create a dataset object ds

045	DataSet ds = new DataSet();

046	// Assign da object data to dataset (virtual table)

047	da.Fill(ds);

048	// assign dataset data to gridview control

049	GridView1.DataSource = ds.Tables[0];

050	GridView1.DataBind();

051	my_con.Close();

052

}

053	catch (Exception ex)

054

{

055	Label1.Text = ex.Message;

056

}

057

}

058	protected void Button3_Click(object sender, EventArgs e)

059

{

060	// create some string variables and assign null values

061	string ex_id = "";

062	string ex_name = "";

063	string ex_age = "";

064	string ex_location = "";

065	//string myfile_name = Path.GetFileName(FileUpload1.PostedFile.FileName);

066	// assign session object data to myfile_name variable

067	string myfile_name = Session["name"].ToString();

068	// get complete path of excel sheet and assing it Excel_path variable

069	string Excel_path = Server.MapPath(myfile_name);

070	// create connection with excel database

071	OleDbConnection my_con = newOleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+Excel_path+";Extended Properties=Excel 8.0;Persist Security Info=False");

072	my_con.Open();

073

try

074

{

075	// get the excel file data and assign it in OleDbcoomad object(o_cmd)

076	OleDbCommand o_cmd = new OleDbCommand("select*from [Sheet1$]", my_con);

077	// read the excel file data and assing it o_dr object

078	OleDbDataReader o_dr = o_cmd.ExecuteReader();

079	while (o_dr.Read())

080

{

081	//get first row data and assign it ex_id variable

082	ex_id = o_dr[0].ToString();

083	//get second row data and assign it ex_name variable

084	ex_name = o_dr[1].ToString();

085	//get thirdt row data and assign it ex_name variable

086	ex_age = o_dr[2].ToString();

087	//get first row data and assign it ex_location variable

088	ex_location = o_dr[3].ToString();

089	// create a connection string with your sql database

090	SqlConnection con = new SqlConnection("data source=RAMASHANKER-PC;Integrated Security=Yes;Database=master");

091	con.Open();

092	//insert excel data in student table

093	SqlCommand cmd = new SqlCommand("insert into student_excel values(@a,@b,@c,@d)", con);

094	cmd.Parameters.AddWithValue("a", ex_id);

095	cmd.Parameters.AddWithValue("b", ex_name);

096	cmd.Parameters.AddWithValue("c", ex_age);

097	cmd.Parameters.AddWithValue("d", ex_location);

098	int i = cmd.ExecuteNonQuery();

099	if (i > 0)

100

{

101	Label1.Text = "Data inserted successfully";

102

}

103	con.Close();

104

}

105

}

106	catch (Exception ex)

107

{

108	Label1.Text =ex.Message;

109

}

110

}

111

112

}

Note:-

• Here I have already mentioned each Namespaces in above codes .
• I have already explained each codes statements in comment section.
• Here i have used a session variable to transfer the file name from one method to another method when user uploaded any file.

Step 5 :- Now Run the Application (Press F5) --> Upload your excel file --> press Save on Server Button --> you will see following output as shown below:-

Step 6 :- Now Press Inset data in Sql database  button or Show in Gridview  button --> then you will see the following output as shown:-

Step 7 :- Now open your sql server Management studio --> Check your Student_excel table data --> You will see that excel file data is inserted in sql database as shown below:-