Post
Ask Question

Upload Any Excel Data Into The SQL Database Table Using C#

Introduction

 
In this blog, we are going to learn how to import Excel data into an SQL database table using C#.  It also shows how bulk data is inserted into the table, checked for duplicate records, then updated. A stored procedure handles the user-defined table and its implementation. In short, this blog will help to understand bulk insertion of Excel data and the implementation of UDT (User Defined Table in SQL). Let's start now.
 
Step 1 - Create a database table in SQL
 
Below is the schema for the table:
  1. CREATE TABLE[dbo].[glsheetdata]   
  2.   (   
  3.      [glid]        [INT] IDENTITY(1, 1) NOT NULL,   
  4.      [countryname] [NVARCHAR] (max) NULL,   
  5.      [company]     [NVARCHAR] (max) NULL,   
  6.      [desc]        [NVARCHAR] (max) NULL,   
  7.      [acctid]      [NVARCHAR] (max) NULL,   
  8.      [accountdesc] [NVARCHAR] (max) NULL,   
  9.      [custid]      [NVARCHAR] (max) NULL,   
  10.      [site]        [NVARCHAR] (max) NULL,   
  11.      CONSTRAINT[PK_GLSheetData] PRIMARY KEY CLUSTERED ( [glid] ASC )WITH(   
  12.      pad_index = OFF, statistics_norecompute = OFF, ignore_dup_key = OFF,   
  13.      allow_row_locks = on, allow_page_locks = on) ON[PRIMARY]   
  14.   )   
  15. ON[PRIMARY]   
  16. textimage_on[PRIMARY]   
  17.   
  18. go    
Step 2
 
Create upload Excel .cs class in project:
  1. public class UploadExcel {  
  2.     public static string DB_PATH = @ "";  
  3.     public static List < GLSheet > GLDataList = new List < GLSheet > ();  
  4.     private static Excel.Workbook MyBook = null;  
  5.     private static Excel.Application MyApp = null;  
  6.     private static Excel.Worksheet MySheet = null;  
  7.     private static int lastRow = 0;  
  8.     public static void InitializeExcel() {  
  9.         MyApp = new Excel.Application();  
  10.         MyApp.Visible = false;  
  11.         MyBook = MyApp.Workbooks.Open(DB_PATH);  
  12.         MySheet = (Excel.Worksheet) MyBook.Sheets[1]; // Explict cast is not required here    
  13.         lastRow = MySheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;  
  14.     }  
  15.     public static List < GLSheet > ReadMyExcel() {  
  16.         try {  
  17.             GLDataList.Clear();  
  18.             //First 4 rows are empty and not required. It varies to excel to excel accordingly    
  19.             for (int rowindex = 5; rowindex <= lastRow; rowindex++) {  
  20.                 //System.Array MyValues = (System.Array)MySheet.get_Range("A" + index.ToString(), "D" + index.ToString()).Cells.Value;    
  21.                 Microsoft.Office.Interop.Excel.Range CountryName = (Microsoft.Office.Interop.Excel.Range) MySheet.Cells[rowindex, 1];  
  22.                 Microsoft.Office.Interop.Excel.Range COMPANY = (Microsoft.Office.Interop.Excel.Range) MySheet.Cells[rowindex, 2];  
  23.                 Microsoft.Office.Interop.Excel.Range Desc = (Microsoft.Office.Interop.Excel.Range) MySheet.Cells[rowindex, 3];  
  24.                 Microsoft.Office.Interop.Excel.Range AcctID = (Microsoft.Office.Interop.Excel.Range) MySheet.Cells[rowindex, 4];  
  25.                 Microsoft.Office.Interop.Excel.Range AccountDesc = (Microsoft.Office.Interop.Excel.Range) MySheet.Cells[rowindex, 5];  
  26.                 Microsoft.Office.Interop.Excel.Range CUSTID = (Microsoft.Office.Interop.Excel.Range) MySheet.Cells[rowindex, 6];  
  27.                 Microsoft.Office.Interop.Excel.Range Site = (Microsoft.Office.Interop.Excel.Range) MySheet.Cells[rowindex, 7];  
  28.                 GLDataList.Add(new GLSheet {  
  29.                     CountryName = Convert.ToString(CountryName.Value),  
  30.                         COMPANY = Convert.ToString(COMPANY.Value),  
  31.                         Desc = Convert.ToString(Desc.Value),  
  32.                         AcctID = Convert.ToString(AcctID.Value),  
  33.                         AccountDesc = Convert.ToString(AccountDesc.Value),  
  34.                         CUSTID = Convert.ToString(CUSTID.Value),  
  35.                         Site = Convert.ToString(Site.Value),  
  36.                 });  
  37.                 //Insert data in slots of 100 rows    
  38.                 UserBusiness userBis = new UserBusiness();  
  39.                 if (rowindex % 100 == 0 || (lastRow - rowindex) < 100) {  
  40.                     bool value = userBis.SaveGLSheetData(GLDataList);  
  41.                     GLDataList = new List < Digiphoto.iMix.ClaimPortal.Model.GLSheet > ();  
  42.                 }  
  43.             } //For loop completed    
  44.         } catch (Exception ex) {}  
  45.         return GLDataList;  
  46.     }  
  47.     public static void CloseExcel() {  
  48.         MyBook.Saved = true;  
  49.         MyApp.Quit();  
  50.     }  
  51. }   
Step 3
 
Now create the model which is used in the above code:
  1. public class GLSheet  
  2.    {  
  3.        public string CountryName { get; set; }  
  4.        public string COMPANY { get; set; }  
  5.        public string Desc { get; set; }  
  6.        public string AcctID { get; set; }  
  7.        public string AccountDesc { get; set; }  
  8.        public string CUSTID { get; set; }  
  9.        public string Site { get; set; }  
  10.    }  
Step 4
 
Now create a Business logic layer .cs class:
  1. public class UserBusiness : BaseBusiness  
  2.    {  
  3.   
  4.       public bool SaveGLSheetData(List<GLSheet> glSheetData)  
  5.        {  
  6.            bool result = false;  
  7.            this.operation = () =>  
  8.            {  
  9.                UserAccess access = new UserAccess(this.Transaction);  
  10.                result = access.SaveGLSheetData(glSheetData);  
  11.            };  
  12.            this.Start(false);  
  13.            return result;  
  14.        }  
  15.   
  16.    }  
Step 5
 
Create a BaseBusiness .cs class:
  1. public class BaseBusiness  
  2.     {  
  3.         #region Declaration  
  4.         private bool _isTransactionRequired;  
  5.         public delegate void TransactionMethod();  
  6.         protected TransactionMethod operation;  
  7.         public BaseDataAccess m_Access;  
  8.         private static readonly ILog log = LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);  
  9.         #endregion  
  10.  
  11.         #region Public Methods  
  12.         public BaseDataAccess Transaction  
  13.         {  
  14.             get { return m_Access; }  
  15.         }  
  16.   
  17.         public TransactionMethod Operation  
  18.         {  
  19.             set { operation = value; }  
  20.         }  
  21.   
  22.         public BaseBusiness()  
  23.         {  
  24.             m_Access = new BaseDataAccess();  
  25.         }  
  26.   
  27.         public BaseBusiness(BaseDataAccess transaction)  
  28.         {  
  29.             m_Access = transaction;  
  30.         }  
  31.  
  32.         public virtual void ExecuteOperation(bool isTransactionRequired)  
  33.         {  
  34.             try  
  35.             {  
  36.                 _isTransactionRequired = isTransactionRequired;  
  37.                 if (isTransactionRequired)  
  38.                 {  
  39.                     this.BeginTransaction();  
  40.                     this.operation();  
  41.                     this.Commit();  
  42.                 }  
  43.                 else  
  44.                 {  
  45.                     this.OpenConnection();  
  46.                     this.operation();  
  47.                    // this.CloseConnection();  
  48.                 }  
  49.             }  
  50.             catch(Exception ex)  
  51.             {  
  52.                 RollBack();  
  53.                 //CloseConnection();  
  54.                 log.StartMethod();  
  55.                 if (ex.InnerException != null)  
  56.                     log.Error("ExecuteOperation: " + ex.Message + ex.InnerException + ex.StackTrace.ToString());  
  57.                 else  
  58.                     log.Error("ExecuteDataSet: " + ex.Message + ex.StackTrace.ToString());  
  59.                 log.EndMethod();  
  60.                 throw;  
  61.             }  
  62.             finally  
  63.             {  
  64.                 CloseConnection();  
  65.             }  
  66.         }  
  67.         public bool Start(bool isTransactionRequired)  
  68.         {  
  69.             bool success = false;  
  70.             try  
  71.             {  
  72.                 this.ExecuteOperation(isTransactionRequired);  
  73.                 success = true;  
  74.             }  
  75.             catch(Exception ex)  
  76.             {  
  77.                 log.StartMethod();  
  78.                 if (ex.InnerException != null)  
  79.                     log.Error("Start: " + ex.Message + ex.InnerException + ex.StackTrace.ToString());  
  80.                 else  
  81.                     log.Error("Start: " + ex.Message + ex.StackTrace.ToString());  
  82.                 log.EndMethod();  
  83.                 throw;  
  84.             }  
  85.             return (success);  
  86.         }  
  87.         #endregion  
  88.  
  89.         #region Private Methods  
  90.         private void OpenConnection()  
  91.         {  
  92.             if (this.m_Access != null)  
  93.                 this.m_Access.OpenConnection();  
  94.         }  
  95.   
  96.         private void CloseConnection()  
  97.         {  
  98.             if (this.m_Access != null)  
  99.                 this.m_Access.CloseConnection();  
  100.         }  
  101.   
  102.         private void BeginTransaction()  
  103.         {  
  104.             if (this.m_Access != null)  
  105.                 this.m_Access.BeginTransaction();  
  106.         }  
  107.   
  108.         private void Commit()  
  109.         {  
  110.             if (this.m_Access != null)  
  111.                 this.m_Access.CommitTransaction();  
  112.         }  
  113.   
  114.         private void RollBack()  
  115.         {  
  116.             if (!_isTransactionRequired)  
  117.                 return;  
  118.   
  119.             if (this.m_Access != null)  
  120.                 this.m_Access.RollbackTransaction();  
  121.         }  
  122.         #endregion  
  123.   
  124.     }  
Step 6
 
Create a Data Access layer .cs class:
  1. public class UserAccess : BaseDataAccess  
  2.    {  
  3.        #region Constrructor  
  4.   
  5.        public UserAccess(BaseDataAccess baseaccess)  
  6.            : base(baseaccess)  
  7.        {  
  8.   
  9.        }  
  10.        public UserAccess()  
  11.        {  
  12.   
  13.        }  
  14.        #endregion  
  15.   
  16. /Save data to SQL database  
  17.        public bool SaveGLSheetData(List<GLSheet> glSheetData)  
  18.        {  
  19.            DBParameters.Clear();  
  20.            AddParameter("@ParamGLSheetDataUdt", DbHelper.ListToDataTable<GLSheet>(glSheetData));  
  21.            ExecuteNonQuery("usp_INSAndUPD_GLSheetData");  
  22.            return true;  
  23.        }  
  24.    }  
Step 7
 
Create BaseDataAccess .cs file, which you can use in the application for many different methods
  1. public class BaseDataAccess  
  2.    {  
  3.        #region Declaration  
  4.        private SqlConnection _conn = null;  
  5.        private SqlCommand _command = null;  
  6.        private SqlTransaction _trans = null;  
  7.        private SqlDataAdapter _adapter = null;  
  8.        private static readonly ILog log = LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);  
  9.        #endregion  
  10.  
  11.        #region Public Properties  
  12.        public List<SqlParameter> DBParameters { get; set; }  
  13.   
  14.        public virtual string MyConString  
  15.        {  
  16.            get  
  17.            {  
  18.                return ConfigurationManager.ConnectionStrings["iMixClaimConnection"].ConnectionString;  
  19.            }  
  20.        }  
  21.   
  22.        public SqlTransaction Transaction { get { return _trans; } }  
  23.        #endregion  
  24.  
  25.        #region Constructor  
  26.        public BaseDataAccess()  
  27.        {  
  28.            DBParameters = new List<SqlParameter>();  
  29.            log4net.Config.XmlConfigurator.Configure();  
  30.        }  
  31.   
  32.        public BaseDataAccess(BaseDataAccess baseAccess)  
  33.        {  
  34.            DBParameters = new List<SqlParameter>();  
  35.            this._conn = baseAccess._conn;  
  36.            this._trans = baseAccess._trans;  
  37.            this._adapter = baseAccess._adapter;  
  38.        }  
  39.        #endregion  
  40.  
  41.        #region Public Methods  
  42.   
  43.        protected DataSet ExecuteDataSet(string spName)  
  44.        {  
  45.            try  
  46.            {  
  47.                DataSet recordsDs = new DataSet();  
  48.                _command = _conn.CreateCommand();  
  49.                _command.CommandTimeout = 180;  
  50.                _command.CommandText = spName;  
  51.                _command.CommandType = CommandType.StoredProcedure;  
  52.                _command.Parameters.AddRange(DBParameters.ToArray());  
  53.   
  54.                if (_adapter == null)  
  55.                    _adapter = new SqlDataAdapter();  
  56.                _adapter.SelectCommand = _command;  
  57.                _adapter.Fill(recordsDs);  
  58.                return recordsDs;  
  59.   
  60.            }  
  61.            catch (Exception ex)  
  62.            {  
  63.                log.StartMethod();  
  64.                if (ex.InnerException != null)  
  65.                    log.Error("ExecuteDataSet: " + ex.Message + ex.InnerException + ex.StackTrace.ToString());  
  66.                else  
  67.                    log.Error("ExecuteDataSet: " + ex.Message + ex.StackTrace.ToString());  
  68.                log.EndMethod();  
  69.                throw ex;  
  70.            }  
  71.        }  
  72.   
  73.        protected IDataReader ExecuteReader(string spName)  
  74.        {  
  75.            try  
  76.            {  
  77.                if (_conn == null)  
  78.                {  
  79.                    OpenConnection();  
  80.                }  
  81.                _command = _conn.CreateCommand();  
  82.                _command.CommandTimeout = 180;  
  83.                _command.CommandText = spName;  
  84.                _command.CommandType = CommandType.StoredProcedure;  
  85.                _command.Parameters.AddRange(DBParameters.ToArray());  
  86.                return _command.ExecuteReader();  
  87.   
  88.            }  
  89.            catch (Exception ex)  
  90.            {  
  91.                log.StartMethod();  
  92.                if (ex.InnerException != null)  
  93.                    log.Error("ExecuteReader: " + ex.Message + ex.InnerException + ex.StackTrace.ToString());  
  94.                else  
  95.                    log.Error("ExecuteReader: " + ex.Message + ex.StackTrace.ToString());  
  96.                log.EndMethod();  
  97.                throw;  
  98.            }  
  99.        }  
  100.   
  101.        protected object ExecuteScalar(string spName)  
  102.        {  
  103.            try  
  104.            {  
  105.                _command = _conn.CreateCommand();  
  106.                _command.CommandText = spName;  
  107.                _command.CommandTimeout = 120;  
  108.                _command.CommandType = CommandType.StoredProcedure;  
  109.                _command.Parameters.AddRange(DBParameters.ToArray());  
  110.                return _command.ExecuteScalar();  
  111.            }  
  112.            catch (Exception ex)  
  113.            {  
  114.                log.StartMethod();  
  115.                if (ex.InnerException != null)  
  116.                    log.Error("ExecuteScalar: " + ex.Message + ex.InnerException + ex.StackTrace.ToString());  
  117.                else  
  118.                    log.Error("ExecuteScalar: " + ex.Message + ex.StackTrace.ToString());  
  119.                log.EndMethod();  
  120.                throw ex;  
  121.            }  
  122.        }  
  123.   
  124.        protected object ExecuteNonQuery(string spName)  
  125.        {  
  126.            try  
  127.            {  
  128.                _command = _conn.CreateCommand();  
  129.                _command.CommandText = spName;  
  130.                _command.CommandTimeout = 120;  
  131.                _command.CommandType = CommandType.StoredProcedure;  
  132.                _command.Parameters.AddRange(DBParameters.ToArray());  
  133.                return _command.ExecuteNonQuery();  
  134.            }  
  135.            catch (Exception ex)  
  136.            {  
  137.                log.StartMethod();  
  138.                if (ex.InnerException != null)  
  139.                    log.Error("ExecuteNonQuery: " + ex.Message + ex.InnerException + ex.StackTrace.ToString());  
  140.                else  
  141.                    log.Error("ExecuteNonQuery: " + ex.Message + ex.StackTrace.ToString());  
  142.                log.EndMethod();  
  143.                throw ex;  
  144.            }  
  145.        }  
  146.        #endregion  
  147.        #region Parameters  
  148.   
  149.        protected void AddParameter(string name, object value)  
  150.        {  
  151.            DBParameters.Add(new SqlParameter(name, value));  
  152.        }  
  153.   
  154.        protected void AddParameter(string name, object value, ParameterDirection direction)  
  155.        {  
  156.            SqlParameter parameter = new SqlParameter(name, value);  
  157.            parameter.Direction = direction;  
  158.            DBParameters.Add(parameter);  
  159.        }  
  160.   
  161.        protected void AddParameter(string name, SqlDbType type, int size, ParameterDirection direction)  
  162.        {  
  163.            SqlParameter parameter = new SqlParameter(name, type, size);  
  164.            parameter.Direction = direction;  
  165.            DBParameters.Add(parameter);  
  166.        }  
  167.   
  168.        protected object GetOutParameterValue(string parameterName)  
  169.        {  
  170.            if (_command != null)  
  171.            {  
  172.                return _command.Parameters[parameterName].Value;  
  173.            }  
  174.            return null;  
  175.        }  
  176.        #endregion  
  177.  
  178.        #region SaveData  
  179.        protected bool SaveData(string spName)  
  180.        {  
  181.            try  
  182.            {  
  183.                if (_conn == null)  
  184.                {  
  185.                    OpenConnection();  
  186.                }  
  187.                _command = _conn.CreateCommand();  
  188.                _command.CommandTimeout = 180;  
  189.                _command.CommandText = spName;  
  190.                _command.CommandType = CommandType.StoredProcedure;  
  191.                _command.Parameters.AddRange(DBParameters.ToArray());  
  192.   
  193.                int result = _command.ExecuteNonQuery();  
  194.                if (result > 0)  
  195.                {  
  196.                    return true;  
  197.                }  
  198.                else  
  199.                    return false;  
  200.            }  
  201.            catch (Exception ex)  
  202.            {  
  203.                log.StartMethod();  
  204.                if (ex.InnerException != null)  
  205.                    log.Error("SaveData: " + ex.Message + ex.InnerException + ex.StackTrace.ToString());  
  206.                else  
  207.                    log.Error("SaveData: " + ex.Message + ex.StackTrace.ToString());  
  208.                log.EndMethod();  
  209.                throw ex;  
  210.            }  
  211.        }  
  212.        #endregion  
  213.  
  214.        #region Transaction Members  
  215.   
  216.        public bool BeginTransaction()  
  217.        {  
  218.            try  
  219.            {  
  220.                bool IsOK = this.OpenConnection();  
  221.   
  222.                if (IsOK)  
  223.                    _trans = _conn.BeginTransaction();  
  224.            }  
  225.            catch (Exception ex)  
  226.            {  
  227.                CloseConnection();  
  228.                log.StartMethod();  
  229.                if (ex.InnerException != null)  
  230.                    log.Error("BeginTransaction: " + ex.Message + ex.InnerException + ex.StackTrace.ToString());  
  231.                else  
  232.                    log.Error("BeginTransaction: " + ex.Message + ex.StackTrace.ToString());  
  233.                log.EndMethod();  
  234.                throw ex;  
  235.            }  
  236.   
  237.            return true;  
  238.        }  
  239.   
  240.        public bool CommitTransaction()  
  241.        {  
  242.            try  
  243.            {  
  244.                _trans.Commit();  
  245.            }  
  246.   
  247.            catch (Exception ex)  
  248.            {  
  249.                log.StartMethod();  
  250.                if (ex.InnerException != null)  
  251.                    log.Error("CommitTransaction: " + ex.Message + ex.InnerException + ex.StackTrace.ToString());  
  252.                else  
  253.                    log.Error("CommitTransaction: " + ex.Message + ex.StackTrace.ToString());  
  254.                log.EndMethod();  
  255.                throw ex;  
  256.            }  
  257.            finally  
  258.            {  
  259.                this.CloseConnection();  
  260.            }  
  261.            return true;  
  262.        }  
  263.   
  264.        public void RollbackTransaction()  
  265.        {  
  266.            try  
  267.            {  
  268.                _trans.Rollback();  
  269.            }  
  270.            catch (Exception ex)  
  271.            {  
  272.                log.StartMethod();  
  273.                if (ex.InnerException != null)  
  274.                    log.Error("RollbackTransaction: " + ex.Message + ex.InnerException + ex.StackTrace.ToString());  
  275.                else  
  276.                    log.Error("RollbackTransaction: " + ex.Message + ex.StackTrace.ToString());  
  277.                log.EndMethod();  
  278.                throw ex;  
  279.            }  
  280.            finally  
  281.            {  
  282.                this.CloseConnection();  
  283.            }  
  284.            return;  
  285.        }  
  286.   
  287.        public bool OpenConnection()  
  288.        {  
  289.            try  
  290.            {  
  291.                if (this._conn == null)  
  292.                    _conn = new SqlConnection(MyConString);  
  293.   
  294.                if (this._conn.State != ConnectionState.Open)  
  295.                {  
  296.                    this._conn.Open();  
  297.                }  
  298.            }  
  299.            catch (Exception ex)  
  300.            {  
  301.                log.StartMethod();  
  302.                if (ex.InnerException != null)  
  303.                    log.Error("OpenConnection: " + ex.Message + ex.InnerException + ex.StackTrace.ToString());  
  304.                else  
  305.                    log.Error("OpenConnection: " + ex.Message + ex.StackTrace.ToString());  
  306.                log.EndMethod();  
  307.                throw ex;  
  308.            }  
  309.            return true;  
  310.        }  
  311.   
  312.        public bool CloseConnection()  
  313.        {  
  314.            try  
  315.            {  
  316.                if (this._conn.State != ConnectionState.Closed)  
  317.                    this._conn.Close();  
  318.            }  
  319.            catch (Exception ex)  
  320.            {  
  321.                log.StartMethod();  
  322.                if (ex.InnerException != null)  
  323.                    log.Error("CloseConnection: " + ex.Message + ex.InnerException + ex.StackTrace.ToString());  
  324.                else  
  325.                    log.Error("CloseConnection: " + ex.Message + ex.StackTrace.ToString());  
  326.                log.EndMethod();  
  327.                throw ex;  
  328.            }  
  329.            finally  
  330.            {  
  331.                if (_conn != null)  
  332.                {  
  333.                    _conn.Dispose();  
  334.                    this._conn = null;  
  335.                }  
  336.   
  337.   
  338.            }  
  339.            return true;  
  340.        }  
  341.  
  342.        #endregion  
  343.  
  344.        #region Utility Functions  
  345.        protected long GetFieldValue(IDataReader sqlReader, string fieldName, long defaultValue)  
  346.        {  
  347.            int pos = sqlReader.GetOrdinal(fieldName);  
  348.            return sqlReader.IsDBNull(pos) ? 0L : sqlReader.GetInt64(pos);  
  349.        }  
  350.   
  351.        protected int GetFieldValue(IDataReader sqlReader, string fieldName, int defaultValue)  
  352.        {  
  353.            int pos = sqlReader.GetOrdinal(fieldName);  
  354.            return sqlReader.IsDBNull(pos) ? 0 : sqlReader.GetInt32(pos);  
  355.        }  
  356.   
  357.        protected float GetFieldValue(IDataReader sqlReader, string fieldName, float defaultValue)  
  358.        {  
  359.            int pos = sqlReader.GetOrdinal(fieldName);  
  360.            //return sqlReader.IsDBNull(pos) ? 0 : sqlReader.GetFloat(pos);  
  361.            return sqlReader.IsDBNull(pos) ? 0 : (float)sqlReader.GetDouble(pos);  
  362.        }  
  363.   
  364.        //Added on 3-march   
  365.        protected double GetFieldValue(IDataReader sqlReader, string fieldName, double defaultValue)  
  366.        {  
  367.            int pos = sqlReader.GetOrdinal(fieldName);  
  368.            return sqlReader.IsDBNull(pos) ? 0 : sqlReader.GetDouble(pos);  
  369.        }  
  370.        protected decimal GetFieldValue(IDataReader sqlReader, string fieldName, decimal defaultValue)  
  371.        {  
  372.            int pos = sqlReader.GetOrdinal(fieldName);  
  373.            return sqlReader.IsDBNull(pos) ? 0 : sqlReader.GetDecimal(pos);  
  374.        }  
  375.   
  376.        protected string GetFieldValue(IDataReader sqlReader, string fieldName, string defaultValue)  
  377.        {  
  378.            int pos = sqlReader.GetOrdinal(fieldName);  
  379.            return sqlReader.IsDBNull(pos) ? String.Empty : sqlReader.GetString(pos);  
  380.        }  
  381.   
  382.        protected DateTime GetFieldValue(IDataReader sqlReader, string fieldName, DateTime defaultValue)  
  383.        {  
  384.            int pos = sqlReader.GetOrdinal(fieldName);  
  385.            return sqlReader.IsDBNull(pos) ? new DateTime() : sqlReader.GetDateTime(pos);  
  386.        }  
  387.   
  388.        protected bool GetFieldValue(IDataReader sqlReader, string fieldName, bool defaultValue)  
  389.        {  
  390.            int pos = sqlReader.GetOrdinal(fieldName);  
  391.            return sqlReader.IsDBNull(pos) ? false : sqlReader.GetBoolean(pos);  
  392.        }  
  393.        #endregion  
  394.    }  
Step 8
 
Now, the final step to call the required method to upload large or small excel file.
 
You can upload excel file or directly assign statically if it's fixed and one time activity.
  1. MyExcel.DB_PATH = @"D:\DEI Docs\Bonus Payout\Solution Automation\GL_Actual.csv"; //Here assigned static path, you can assign dynamically by using fileupload  
  2. MyExcel.InitializeExcel();  
  3. List<GLSheet> lst = MyExcel.ReadMyExcel();  
Step 9
 
Create the below-stored procedure in the SQL database:   
  1. CREATE PROCEDURE [dbo].[usp_INSAndUPD_GLSheetData]         
  2. (        
  3.   @ParamGLSheetDataUdt UDT_GLSheetData READONLY        
  4. )        
  5. AS          
  6. /*-------------------------------------------------------------------------------------          
  7.       AUTHOR    :  VINOD SALUNKE        
  8.     DATE CREATED  :  8 Aug 2020        
  9. PURPOSE/DESCRIPTION :         
  10. ---------------------------------------------------------------------------------------          
  11. MODIFIED DATE      AUTHOR                          DESCRIPTION          
  12. ---------------------------------------------------------------------------------------        
  13. TEST CASES:        
  14. *------------------------------------------------------------------------------------*/         
  15. BEGIN        
  16.   SET NOCOUNT ON;        
  17.         
  18.   DECLARE         
  19.    @TransactionStarted INT,         
  20.    @ErrorState INT,        
  21.    @Msg NVARCHAR(MAX),        
  22.     @ModifiedBy NVARCHAR(125) = SYSTEM_USER,        
  23.     @ModifiedDateTime DATETIME=  GEtDATE()        
  24.         
  25.   DECLARE        
  26.     @GLData UDT_GLSheetData        
  27.         
  28.   IF (@@TRANCOUNT = 0)         
  29.  BEGIN          
  30.   SET XACT_ABORT ON;        
  31.   SET @TransactionStarted = 1;        
  32.   BEGIN TRANSACTION;        
  33.  END        
  34.           
  35. BEGIN TRY            
  36.         
  37. MERGE GLSheetData AS Target    
  38. USING (SELECT mi.CountryName,mi.COMPANY,mi.[Desc],mi.[AcctID],mi.[AccountDesc],mi.[CUSTID],mi.[Site]   
  39.    FROM @ParamGLSheetDataUdt mi) AS Source    
  40. ON (    
  41.          Target.[CountryName] = Source.[CountryName]    
  42.     AND  Target.[COMPANY] = Source.[COMPANY]    
  43.  AND Target.[Desc]=Source.[Desc] AND Target.[AcctID] = Source.[AcctID]    
  44.  AND Target.[AccountDesc] = Source.[AccountDesc]    
  45.  AND Target.[CUSTID] = Source.[CUSTID]    
  46.  AND Target.[Site] = Source.[Site]    
  47.     )    
  48. --WHEN MATCHED THEN     
  49. --     UPDATE SET Price = Source.Price,    
  50. --                Quantity = Source.Quantity    
  51. WHEN NOT MATCHED BY TARGET THEN    
  52.      INSERT ([CountryName], [COMPANY],[Desc],[AcctID],[AccountDesc],[CUSTID],[Site])    
  53.      VALUES (Source.[CountryName],Source.[COMPANY],Source.[Desc],Source.[AcctID],Source.[AccountDesc],Source.[CUSTID],Source.[Site]
  54.   );    
  55.     
  56.     IF ((@TransactionStarted = 1) AND (XACT_STATE() = 1))         
  57.     COMMIT TRANSACTION;        
  58.                  
  59.   END TRY        
  60.  BEGIN CATCH        
  61.           
  62.     --Catch Start        
  63.   SELECT  @ErrorState = ERROR_STATE();        
  64.   SET @Msg = IsNull(ERROR_PROCEDURE(), '[dbo].[usp_INSAndUPD_GLSheetData] ') + ': ' + ERROR_MESSAGE() + ', ' +        
  65.   'Line: '  + CONVERT(VARCHAR, ERROR_LINE()) + ', ' +        
  66.   'Error: ' + CONVERT(VARCHAR, ERROR_NUMBER()) + ', ' +        
  67.   'State: ' + CONVERT(VARCHAR, @ErrorState)        
  68.         
  69.   IF ((XACT_STATE() = -1) AND (@TransactionStarted = 1))         
  70.   BEGIN        
  71.    ROLLBACK TRANSACTION;        
  72.   END         
  73.   ELSE         
  74.   BEGIN        
  75.    IF ((XACT_STATE() = 1) AND (@TransactionStarted = 1))         
  76.    BEGIN        
  77.     COMMIT TRANSACTION;        
  78.    END;        
  79.   END;        
  80.         
  81.   RAISERROR(@Msg, 16, 1);        
  82.         
  83.  END CATCH;        
  84.         
  85. END   
Enjoy coding... :)
Next Recommended Reading Import Excel Data to Database Using C#