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
Try to use ZetExcel
will try it soon buddy!!
Nice easy to understand keep it up
Thanks 🙂