Thursday, 20 April 2017

Import MS Excel data to SQL Server table using C#

If you already have data in MS Excel file, and want to migrate your MS Excel data to SQL Server table, follow the below steps:
MY xls file.



CREATE a SQL Table


CREATE TABLE [dbo].[tblProduct](
 [PID] [int] NULL,
 [ProdName] [varchar](50) NULL,
 [ProdDesc] [varchar](50) NULL,
 [ProdCost] [varchar](50) NULL,
 [CREATED_DATE] [datetime] NULL
)

Your MS Excel sheet and SQL table are ready, now it’s time to write C# code to import the Excel sheet into the tblProduct table.

Create a MVC Project. 
In the homecontroller class add these namespaces.


using System.Data.OleDb;
using System.Data.SqlClient;

In the homecontroller class add these variables in class level


        SqlConnection Sqlcon;
        public static string strFileName = "";
        string strfileLocation = "";
        string constr, Query, sqlconn;

In the homecontroller  create a function below.


 public void UploadBulkData(string FilePath)
        {
            string OleDBConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties=Excel 12.0;Persist Security Info=False";
            string Query = string.Empty;
            int BatchId = 0;
            sqlconn = ConfigurationManager.ConnectionStrings["TransactionDB"].ConnectionString;
            Sqlcon = new SqlConnection(sqlconn);
            #region Query
           // Query = @"SELECT * FROM [Sheet1$] WHERE PID IS NOT NULL";
            Query = @"SELECT
                             UCASE(RTRIM(LTRIM(PID))) AS PID,
                             UCASE(RTRIM(LTRIM(ProdName))) AS ProdName,
                             UCASE(RTRIM(LTRIM(ProdDesc))) AS ProdDesc,
                             UCASE(RTRIM(LTRIM(ProdCost))) AS ProdCost
                             FROM [Sheet1$]";
            #endregion

            using (OleDbConnection oledbConn = new OleDbConnection(OleDBConnectionString))
            {
                OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(Query, oledbConn);
                oledbConn.Open();

                DataSet ds = new DataSet();
                OleDbDataAdapter oda = new OleDbDataAdapter(Query, oledbConn);
                oledbConn.Close();
                oda.Fill(ds);
                DataTable Exceldt = ds.Tables[0];

                Exceldt.Columns.Add("CREATED_DATE", typeof(DateTime));
                foreach (DataRow row in Exceldt.Rows)
                {
                    row["CREATED_DATE"] = DateTime.Now.ToShortDateString();
                }

                //creating object of SqlBulkCopy    
                SqlBulkCopy objbulk = new SqlBulkCopy(Sqlcon);

                //assigning Destination table name    
                objbulk.DestinationTableName = "tblProduct";

                // inserting Datatable Records to DataBase
                Sqlcon.Open();
                objbulk.WriteToServer(Exceldt);
                Sqlcon.Close();
            }
        }


Now, It's time to call the above function.

        [HttpPost]
        public ActionResult Index(ProductModel model)
        {
            strfileLocation = @"D:\Projects\WebApplication1\WebApplication1\ProductSheet.xls"; //Server.MapPath("~/ProductSheet.xls");
            UploadBulkData(strfileLocation);
            return View();
        }

When you post the Index function, then it'll import all data from xls to your Sql table.

0 comments:

Post a Comment

Topics

ADO .Net (2) Ajax (1) Angular Js (17) Angular2 (24) ASP .Net (14) Azure (1) Breeze.js (1) C# (49) CloudComputing (1) CMS (1) CSS (2) Design_Pattern (3) DI (3) Dotnet (21) Entity Framework (3) ExpressJS (4) Html (3) IIS (1) Javascript (6) Jquery (9) Lamda (3) Linq (11) Mongodb (1) MVC (48) NodeJS (7) RDLC (1) Report (1) Sql Server (29) SSIS (3) SSRS (2) UI (1) WCF (12) Web Api (9) Web Service (1) XMl (1)