ASP.NET MVC

Import Excel Data To SQL Using C#

In this post , We will learn how to Import excel data to SQL using C# with the help of OLEDB . This is a time saver especially if you want to add tons and tons of data to your table within a fraction of second a so let’s get started.

First thing first , Create a table in SQL-Server database (you can copy paste following code to your SQL-Server to relate flawless with the following article).

CREATE TABLE [dbo].[tbl_registration](
[Sr_no] [int] IDENTITY(1,1) NOT NULL,
[Email] [nvarchar](100) NULL,
[Password] [nvarchar](max) NULL,
[Name] [varchar](max) NULL,
[Address] [nvarchar](max) NULL,
[City] [nvarchar](max) NULL
)

Now, Add connection string to your Web.config file.

<connectionStrings>
   <add name="con" connectionString="Server=YourServerName;Initial Catalog=YourDatabaseName;Persist Security Info=False;User ID=YourUserName;Password=YourUserPassword;MultipleActiveResultSets=True;Encrypt=False;TrustServerCertificate=False;Connection Timeout=30;" providerName="System.Data.SqlClient" />
 </connectionStrings>

Note :- Change Server,Initial Catalog,User ID,Password values as per your server properties

Now, Add the following code to your Index.cshtml file

<!DOCTYPE html>
<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
</head>
<body>
    <div>
        <h2>Excel Import</h2>
        <form method="post" enctype="multipart/form-data" class="form-group">
            <div>
                <input name="file" type="file" class="form-control-file" required />
                <button type="submit" class="btn btn-primary">Import</button>
            </div>
        </form>
    </div>
</body>

Next step , Add Following Code to your Home Controller

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);
        OleDbConnection econ;
        public ActionResult Index()
        {
            return View();
        }

        [HttpPost]
        public ActionResult Index(HttpPostedFileBase file)
        {
            string filename = Guid.NewGuid() + Path.GetExtension(file.FileName);
            string filepath = "/excelfolder/" + filename;
            file.SaveAs(Path.Combine(Server.MapPath("/excelfolder"), filename));
            InsertExceldata(filepath, filename);
            return View();
        }
        private void ExcelConn(string filepath)
        {
            string constr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", filepath);
            econ = new OleDbConnection(constr);            
        }

        private void InsertExceldata(string fileepath, string filename)
        {
            string fullpath = Server.MapPath("/excelfolder/") + filename;
            ExcelConn(fullpath);
            string query = string.Format("Select * from [{0}]", "Sheet1$");
            OleDbCommand ecom = new OleDbCommand(query, econ);
            econ.Open();
            DataSet ds = new DataSet();
            OleDbDataAdapter oda = new OleDbDataAdapter(query, econ);
            econ.Close();
            oda.Fill(ds);
            DataTable dt = ds.Tables[0];
            SqlBulkCopy objbulk = new SqlBulkCopy(con);
            objbulk.DestinationTableName = "tbl_registration";
            objbulk.ColumnMappings.Add("Email", "Email");
            objbulk.ColumnMappings.Add("Password", "Password");
            objbulk.ColumnMappings.Add("Name", "Name");
            objbulk.ColumnMappings.Add("Address", "Address");
            objbulk.ColumnMappings.Add("City", "City");
            con.Open();
            objbulk.WriteToServer(dt);
            con.Close();        
        }

Note :- Add a folder namely excelfolder in your project.

And that’s it You can run the Project

Nisarg Patel

View Comments

Share
Published by
Nisarg Patel

Recent Posts

Testing hk

Testing

2 years ago

Create and Used PIPE in angular

In this article, we have to show Create and Used PIPE in angular

2 years ago

Operation

Testing

2 years ago

Create and Used PIPE in angular

In this article, we have to show Create and Used PIPE in angular

2 years ago

Create and Used PIPE in angular

In this article, we have to show Create and Used PIPE in angular

2 years ago

TETS NEW

test

3 years ago