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 four columns 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:-

using System;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.IO;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Button1_Click(object sender, EventArgs e)
    {    //Get file name when you upload any file
        string file_name = Path.GetFileName(FileUpload1.PostedFile.FileName);
        // save the file on server(website) 
        FileUpload1.SaveAs(Server.MapPath( file_name));
        // save file name in sessio objet
        Session["name"] = file_name;
        //refresht the page
        Response.Redirect("Default.aspx");
    }
    protected void Button2_Click(object sender, EventArgs e)
    {
        //string myfile_name = Path.GetFileName(FileUpload1.PostedFile.FileName);
        // Assign session object data in myfile_name variable
        string myfile_name = Session["name"].ToString();
        // Get the file(excel)complete path and assign in Excel_path variable
        string Excel_path = Server.MapPath(myfile_name);
        // create connection with excel database  
        OleDbConnection my_con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+Excel_path+";Extended Properties=Excel 8.0;Persist Security Info=False");
        my_con.Open();
        try
        {
            // get the excel file data and assign it in OleDbcoomad object(o_cmd) 
            OleDbCommand o_cmd = new OleDbCommand("select*from [Sheet1$]", my_con);
            //create oledbdataadapter object
            OleDbDataAdapter da = new OleDbDataAdapter();
            // pass o_cmd data to da object
            da.SelectCommand = o_cmd;
            //create a dataset object ds
            DataSet ds = new DataSet();
            // Assign da object data to dataset (virtual table) 
            da.Fill(ds);
            // assign dataset data to gridview control  
            GridView1.DataSource = ds.Tables[0];
            GridView1.DataBind();
            my_con.Close();
        }
        catch (Exception ex)
        {
            Label1.Text = ex.Message;
        }
    }
    protected void Button3_Click(object sender, EventArgs e)
    {
        // create some string variables and assign null values 
        string ex_id = "";
        string ex_name = "";
        string ex_age = "";
        string ex_location = "";
        //string myfile_name = Path.GetFileName(FileUpload1.PostedFile.FileName);
        // assign session object data to myfile_name variable
        string myfile_name = Session["name"].ToString();
        // get complete path of excel sheet and assing it Excel_path variable 
        string Excel_path = Server.MapPath(myfile_name);
        // create connection with excel database  
        OleDbConnection my_con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+Excel_path+";Extended Properties=Excel 8.0;Persist Security Info=False");
        my_con.Open();
        try
        {
         // get the excel file data and assign it in OleDbcoomad object(o_cmd)
        OleDbCommand o_cmd = new OleDbCommand("select*from [Sheet1$]", my_con);
            // read the excel file data and assing it o_dr object
        OleDbDataReader o_dr = o_cmd.ExecuteReader();
        while (o_dr.Read())
        {
            //get first row data and assign it ex_id variable 
            ex_id = o_dr[0].ToString();
            //get second row data and assign it ex_name variable
            ex_name = o_dr[1].ToString();
            //get thirdt row data and assign it ex_name variable
            ex_age = o_dr[2].ToString();
            //get first row data and assign it ex_location variable
            ex_location = o_dr[3].ToString();
            // create a connection string with your sql database 
            SqlConnection con = new SqlConnection("data source=RAMASHANKER-PC;Integrated Security=Yes;Database=master");
            con.Open();
            //insert excel data in student table
            SqlCommand cmd = new SqlCommand("insert into student_excel values(@a,@b,@c,@d)", con);
            cmd.Parameters.AddWithValue("a", ex_id);
            cmd.Parameters.AddWithValue("b", ex_name);
            cmd.Parameters.AddWithValue("c", ex_age);
            cmd.Parameters.AddWithValue("d", ex_location);
            int i = cmd.ExecuteNonQuery();
            if (i > 0)
            {
                Label1.Text = "Data inserted successfully";
            }
            con.Close();
        }
        }
            catch (Exception ex)
        {
                Label1.Text =ex.Message; 
            }
    }
    
}

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:-

Note :-

• You can your excel file data in database.mdf file but you have to change your connection string codes.
• You can add Sql server database(.mdf) on website from here .

You can download whole application and run it directly on your visual studio without facing any problems.
For More..

1. How to implement properties in wpf  application
2. How to implement Namespace in WPF Application
3. How to implement login controls in asp.net application
4. How to display XML data in Listbox sing Linq query
5. How to implement Lambda expression with Linq
6. How to perform read ,write,append and other functionality with this software
7. How to change password in asp.net website
8. How to implement page life cycle concepts in asp.net 
9. How to use trigger concepts in sql server
10. How to Run Notepad program in c#

Download whole Attached file
   Download