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:

CREATE TABLE[dbo].[glsheetdata]
(
[glid] [INT] IDENTITY(1, 1) NOT NULL,
[countryname] [NVARCHAR] (max) NULL,
[company] [NVARCHAR] (max) NULL,
[desc] [NVARCHAR] (max) NULL,
[acctid] [NVARCHAR] (max) NULL,
[accountdesc] [NVARCHAR] (max) NULL,
[custid] [NVARCHAR] (max) NULL,
[site] [NVARCHAR] (max) NULL,
CONSTRAINT[PK_GLSheetData] PRIMARY KEY CLUSTERED ( [glid] ASC )WITH(
pad_index = OFF, statistics_norecompute = OFF, ignore_dup_key = OFF,
allow_row_locks = on, allow_page_locks = on) ON[PRIMARY]
)
ON[PRIMARY]
textimage_on[PRIMARY]
go

Step 2

Create upload Excel .cs class in project:

public class UploadExcel {
public static string DB_PATH = @ "";
public static List < GLSheet > GLDataList = new List < GLSheet > ();
private static Excel.Workbook MyBook = null;
private static Excel.Application MyApp = null;
private static Excel.Worksheet MySheet = null;
private static int lastRow = 0;
public static void InitializeExcel() {
MyApp = new Excel.Application();
MyApp.Visible = false;
MyBook = MyApp.Workbooks.Open(DB_PATH);
MySheet = (Excel.Worksheet) MyBook.Sheets[1]; // Explict cast is not required here
lastRow = MySheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;
}
public static List < GLSheet > ReadMyExcel() {
try {
GLDataList.Clear();
//First 4 rows are empty and not required. It varies to excel to excel accordingly
for (int rowindex = 5; rowindex <= lastRow; rowindex++) {
//System.Array MyValues = (System.Array)MySheet.get_Range("A" + index.ToString(), "D" + index.ToString()).Cells.Value;
Microsoft.Office.Interop.Excel.Range CountryName = (Microsoft.Office.Interop.Excel.Range) MySheet.Cells[rowindex, 1];
Microsoft.Office.Interop.Excel.Range COMPANY = (Microsoft.Office.Interop.Excel.Range) MySheet.Cells[rowindex, 2];
Microsoft.Office.Interop.Excel.Range Desc = (Microsoft.Office.Interop.Excel.Range) MySheet.Cells[rowindex, 3];
Microsoft.Office.Interop.Excel.Range AcctID = (Microsoft.Office.Interop.Excel.Range) MySheet.Cells[rowindex, 4];
Microsoft.Office.Interop.Excel.Range AccountDesc = (Microsoft.Office.Interop.Excel.Range) MySheet.Cells[rowindex, 5];
Microsoft.Office.Interop.Excel.Range CUSTID = (Microsoft.Office.Interop.Excel.Range) MySheet.Cells[rowindex, 6];
Microsoft.Office.Interop.Excel.Range Site = (Microsoft.Office.Interop.Excel.Range) MySheet.Cells[rowindex, 7];
GLDataList.Add(new GLSheet {
CountryName = Convert.ToString(CountryName.Value),
COMPANY = Convert.ToString(COMPANY.Value),
Desc = Convert.ToString(Desc.Value),
AcctID = Convert.ToString(AcctID.Value),
AccountDesc = Convert.ToString(AccountDesc.Value),
CUSTID = Convert.ToString(CUSTID.Value),
Site = Convert.ToString(Site.Value),
});
//Insert data in slots of 100 rows
UserBusiness userBis = new UserBusiness();
if (rowindex % 100 == 0 || (lastRow - rowindex) < 100) {
bool value = userBis.SaveGLSheetData(GLDataList);
GLDataList = new List < Digiphoto.iMix.ClaimPortal.Model.GLSheet > ();
}
} //For loop completed
} catch (Exception ex) {}
return GLDataList;
}
public static void CloseExcel() {
MyBook.Saved = true;
MyApp.Quit();
}
}

Step 3

Now create the model which is used in the above code:

public class GLSheet
{
public string CountryName { get; set; }
public string COMPANY { get; set; }
public string Desc { get; set; }
public string AcctID { get; set; }
public string AccountDesc { get; set; }
public string CUSTID { get; set; }
public string Site { get; set; }
}

Step 4

Now create a Business logic layer .cs class:

public class UserBusiness : BaseBusiness
{
public bool SaveGLSheetData(List<GLSheet> glSheetData)
{
bool result = false;
this.operation = () =>
{
UserAccess access = new UserAccess(this.Transaction);
result = access.SaveGLSheetData(glSheetData);
};
this.Start(false);
return result;
}
}

Step 5

Create a BaseBusiness .cs class:

public class BaseBusiness
{
#region Declaration
private bool _isTransactionRequired;
public delegate void TransactionMethod();
protected TransactionMethod operation;
public BaseDataAccess m_Access;
private static readonly ILog log = LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
#endregion
#region Public Methods
public BaseDataAccess Transaction
{
get { return m_Access; }
}
public TransactionMethod Operation
{
set { operation = value; }
}
public BaseBusiness()
{
m_Access = new BaseDataAccess();
}
public BaseBusiness(BaseDataAccess transaction)
{
m_Access = transaction;
}
public virtual void ExecuteOperation(bool isTransactionRequired)
{
try
{
_isTransactionRequired = isTransactionRequired;
if (isTransactionRequired)
{
this.BeginTransaction();
this.operation();
this.Commit();
}
else
{
this.OpenConnection();
this.operation();
// this.CloseConnection();
}
}
catch(Exception ex)
{
RollBack();
//CloseConnection();
log.StartMethod();
if (ex.InnerException != null)
log.Error("ExecuteOperation: " + ex.Message + ex.InnerException + ex.StackTrace.ToString());
else
log.Error("ExecuteDataSet: " + ex.Message + ex.StackTrace.ToString());
log.EndMethod();
throw;
}
finally
{
CloseConnection();
}
}
public bool Start(bool isTransactionRequired)
{
bool success = false;
try
{
this.ExecuteOperation(isTransactionRequired);
success = true;
}
catch(Exception ex)
{
log.StartMethod();
if (ex.InnerException != null)
log.Error("Start: " + ex.Message + ex.InnerException + ex.StackTrace.ToString());
else
log.Error("Start: " + ex.Message + ex.StackTrace.ToString());
log.EndMethod();
throw;
}
return (success);
}
#endregion
#region Private Methods
private void OpenConnection()
{
if (this.m_Access != null)
this.m_Access.OpenConnection();
}
private void CloseConnection()
{
if (this.m_Access != null)
this.m_Access.CloseConnection();
}
private void BeginTransaction()
{
if (this.m_Access != null)
this.m_Access.BeginTransaction();
}
private void Commit()
{
if (this.m_Access != null)
this.m_Access.CommitTransaction();
}
private void RollBack()
{
if (!_isTransactionRequired)
return;
if (this.m_Access != null)
this.m_Access.RollbackTransaction();
}
#endregion
}

Step 6

Create a Data Access layer .cs class:

public class UserAccess : BaseDataAccess
{
#region Constrructor
public UserAccess(BaseDataAccess baseaccess)
: base(baseaccess)
{
}
public UserAccess()
{
}
#endregion
/Save data to SQL database
public bool SaveGLSheetData(List<GLSheet> glSheetData)
{
DBParameters.Clear();
AddParameter("@ParamGLSheetDataUdt", DbHelper.ListToDataTable<GLSheet>(glSheetData));
ExecuteNonQuery("usp_INSAndUPD_GLSheetData");
return true;
}
}

Step 7

Create BaseDataAccess .cs file, which you can use in the application for many different methods

public class BaseDataAccess
{
#region Declaration
private SqlConnection _conn = null;
private SqlCommand _command = null;
private SqlTransaction _trans = null;
private SqlDataAdapter _adapter = null;
private static readonly ILog log = LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
#endregion
#region Public Properties
public List<SqlParameter> DBParameters { get; set; }
public virtual string MyConString
{
get
{
return ConfigurationManager.ConnectionStrings["iMixClaimConnection"].ConnectionString;
}
}
public SqlTransaction Transaction { get { return _trans; } }
#endregion
#region Constructor
public BaseDataAccess()
{
DBParameters = new List<SqlParameter>();
log4net.Config.XmlConfigurator.Configure();
}
public BaseDataAccess(BaseDataAccess baseAccess)
{
DBParameters = new List<SqlParameter>();
this._conn = baseAccess._conn;
this._trans = baseAccess._trans;
this._adapter = baseAccess._adapter;
}
#endregion
#region Public Methods
protected DataSet ExecuteDataSet(string spName)
{
try
{
DataSet recordsDs = new DataSet();
_command = _conn.CreateCommand();
_command.CommandTimeout = 180;
_command.CommandText = spName;
_command.CommandType = CommandType.StoredProcedure;
_command.Parameters.AddRange(DBParameters.ToArray());
if (_adapter == null)
_adapter = new SqlDataAdapter();
_adapter.SelectCommand = _command;
_adapter.Fill(recordsDs);
return recordsDs;
}
catch (Exception ex)
{
log.StartMethod();
if (ex.InnerException != null)
log.Error("ExecuteDataSet: " + ex.Message + ex.InnerException + ex.StackTrace.ToString());
else
log.Error("ExecuteDataSet: " + ex.Message + ex.StackTrace.ToString());
log.EndMethod();
throw ex;
}
}
protected IDataReader ExecuteReader(string spName)
{
try
{
if (_conn == null)
{
OpenConnection();
}
_command = _conn.CreateCommand();
_command.CommandTimeout = 180;
_command.CommandText = spName;
_command.CommandType = CommandType.StoredProcedure;
_command.Parameters.AddRange(DBParameters.ToArray());
return _command.ExecuteReader();
}
catch (Exception ex)
{
log.StartMethod();
if (ex.InnerException != null)
log.Error("ExecuteReader: " + ex.Message + ex.InnerException + ex.StackTrace.ToString());
else
log.Error("ExecuteReader: " + ex.Message + ex.StackTrace.ToString());
log.EndMethod();
throw;
}
}
protected object ExecuteScalar(string spName)
{
try
{
_command = _conn.CreateCommand();
_command.CommandText = spName;
_command.CommandTimeout = 120;
_command.CommandType = CommandType.StoredProcedure;
_command.Parameters.AddRange(DBParameters.ToArray());
return _command.ExecuteScalar();
}
catch (Exception ex)
{
log.StartMethod();
if (ex.InnerException != null)
log.Error("ExecuteScalar: " + ex.Message + ex.InnerException + ex.StackTrace.ToString());
else
log.Error("ExecuteScalar: " + ex.Message + ex.StackTrace.ToString());
log.EndMethod();
throw ex;
}
}
protected object ExecuteNonQuery(string spName)
{
try
{
_command = _conn.CreateCommand();
_command.CommandText = spName;
_command.CommandTimeout = 120;
_command.CommandType = CommandType.StoredProcedure;
_command.Parameters.AddRange(DBParameters.ToArray());
return _command.ExecuteNonQuery();
}
catch (Exception ex)
{
log.StartMethod();
if (ex.InnerException != null)
log.Error("ExecuteNonQuery: " + ex.Message + ex.InnerException + ex.StackTrace.ToString());
else
log.Error("ExecuteNonQuery: " + ex.Message + ex.StackTrace.ToString());
log.EndMethod();
throw ex;
}
}
#endregion
#region Parameters
protected void AddParameter(string name, object value)
{
DBParameters.Add(new SqlParameter(name, value));
}
protected void AddParameter(string name, object value, ParameterDirection direction)
{
SqlParameter parameter = new SqlParameter(name, value);
parameter.Direction = direction;
DBParameters.Add(parameter);
}
protected void AddParameter(string name, SqlDbType type, int size, ParameterDirection direction)
{
SqlParameter parameter = new SqlParameter(name, type, size);
parameter.Direction = direction;
DBParameters.Add(parameter);
}
protected object GetOutParameterValue(string parameterName)
{
if (_command != null)
{
return _command.Parameters[parameterName].Value;
}
return null;
}
#endregion
#region SaveData
protected bool SaveData(string spName)
{
try
{
if (_conn == null)
{
OpenConnection();
}
_command = _conn.CreateCommand();
_command.CommandTimeout = 180;
_command.CommandText = spName;
_command.CommandType = CommandType.StoredProcedure;
_command.Parameters.AddRange(DBParameters.ToArray());
int result = _command.ExecuteNonQuery();
if (result > 0)
{
return true;
}
else
return false;
}
catch (Exception ex)
{
log.StartMethod();
if (ex.InnerException != null)
log.Error("SaveData: " + ex.Message + ex.InnerException + ex.StackTrace.ToString());
else
log.Error("SaveData: " + ex.Message + ex.StackTrace.ToString());
log.EndMethod();
throw ex;
}
}
#endregion
#region Transaction Members
public bool BeginTransaction()
{
try
{
bool IsOK = this.OpenConnection();
if (IsOK)
_trans = _conn.BeginTransaction();
}
catch (Exception ex)
{
CloseConnection();
log.StartMethod();
if (ex.InnerException != null)
log.Error("BeginTransaction: " + ex.Message + ex.InnerException + ex.StackTrace.ToString());
else
log.Error("BeginTransaction: " + ex.Message + ex.StackTrace.ToString());
log.EndMethod();
throw ex;
}
return true;
}
public bool CommitTransaction()
{
try
{
_trans.Commit();
}
catch (Exception ex)
{
log.StartMethod();
if (ex.InnerException != null)
log.Error("CommitTransaction: " + ex.Message + ex.InnerException + ex.StackTrace.ToString());
else
log.Error("CommitTransaction: " + ex.Message + ex.StackTrace.ToString());
log.EndMethod();
throw ex;
}
finally
{
this.CloseConnection();
}
return true;
}
public void RollbackTransaction()
{
try
{
_trans.Rollback();
}
catch (Exception ex)
{
log.StartMethod();
if (ex.InnerException != null)
log.Error("RollbackTransaction: " + ex.Message + ex.InnerException + ex.StackTrace.ToString());
else
log.Error("RollbackTransaction: " + ex.Message + ex.StackTrace.ToString());
log.EndMethod();
throw ex;
}
finally
{
this.CloseConnection();
}
return;
}
public bool OpenConnection()
{
try
{
if (this._conn == null)
_conn = new SqlConnection(MyConString);
if (this._conn.State != ConnectionState.Open)
{
this._conn.Open();
}
}
catch (Exception ex)
{
log.StartMethod();
if (ex.InnerException != null)
log.Error("OpenConnection: " + ex.Message + ex.InnerException + ex.StackTrace.ToString());
else
log.Error("OpenConnection: " + ex.Message + ex.StackTrace.ToString());
log.EndMethod();
throw ex;
}
return true;
}
public bool CloseConnection()
{
try
{
if (this._conn.State != ConnectionState.Closed)
this._conn.Close();
}
catch (Exception ex)
{
log.StartMethod();
if (ex.InnerException != null)
log.Error("CloseConnection: " + ex.Message + ex.InnerException + ex.StackTrace.ToString());
else
log.Error("CloseConnection: " + ex.Message + ex.StackTrace.ToString());
log.EndMethod();
throw ex;
}
finally
{
if (_conn != null)
{
_conn.Dispose();
this._conn = null;
}
}
return true;
}
#endregion
#region Utility Functions
protected long GetFieldValue(IDataReader sqlReader, string fieldName, long defaultValue)
{
int pos = sqlReader.GetOrdinal(fieldName);
return sqlReader.IsDBNull(pos) ? 0L : sqlReader.GetInt64(pos);
}
protected int GetFieldValue(IDataReader sqlReader, string fieldName, int defaultValue)
{
int pos = sqlReader.GetOrdinal(fieldName);
return sqlReader.IsDBNull(pos) ? 0 : sqlReader.GetInt32(pos);
}
protected float GetFieldValue(IDataReader sqlReader, string fieldName, float defaultValue)
{
int pos = sqlReader.GetOrdinal(fieldName);
//return sqlReader.IsDBNull(pos) ? 0 : sqlReader.GetFloat(pos);
return sqlReader.IsDBNull(pos) ? 0 : (float)sqlReader.GetDouble(pos);
}
//Added on 3-march
protected double GetFieldValue(IDataReader sqlReader, string fieldName, double defaultValue)
{
int pos = sqlReader.GetOrdinal(fieldName);
return sqlReader.IsDBNull(pos) ? 0 : sqlReader.GetDouble(pos);
}
protected decimal GetFieldValue(IDataReader sqlReader, string fieldName, decimal defaultValue)
{
int pos = sqlReader.GetOrdinal(fieldName);
return sqlReader.IsDBNull(pos) ? 0 : sqlReader.GetDecimal(pos);
}
protected string GetFieldValue(IDataReader sqlReader, string fieldName, string defaultValue)
{
int pos = sqlReader.GetOrdinal(fieldName);
return sqlReader.IsDBNull(pos) ? String.Empty : sqlReader.GetString(pos);
}
protected DateTime GetFieldValue(IDataReader sqlReader, string fieldName, DateTime defaultValue)
{
int pos = sqlReader.GetOrdinal(fieldName);
return sqlReader.IsDBNull(pos) ? new DateTime() : sqlReader.GetDateTime(pos);
}
protected bool GetFieldValue(IDataReader sqlReader, string fieldName, bool defaultValue)
{
int pos = sqlReader.GetOrdinal(fieldName);
return sqlReader.IsDBNull(pos) ? false : sqlReader.GetBoolean(pos);
}
#endregion
}

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.

MyExcel.DB_PATH = @"D:\DEI Docs\Bonus Payout\Solution Automation\GL_Actual.csv"; //Here assigned static path, you can assign dynamically by using fileupload
MyExcel.InitializeExcel();
List<GLSheet> lst = MyExcel.ReadMyExcel();

Step 9

Create the below-stored procedure in the SQL database:

CREATE PROCEDURE [dbo].[usp_INSAndUPD_GLSheetData]
(
@ParamGLSheetDataUdt UDT_GLSheetData READONLY
)
AS
/*-------------------------------------------------------------------------------------
AUTHOR : VINOD SALUNKE
DATE CREATED : 8 Aug 2020
PURPOSE/DESCRIPTION :
---------------------------------------------------------------------------------------
MODIFIED DATE AUTHOR DESCRIPTION
---------------------------------------------------------------------------------------
TEST CASES:
*------------------------------------------------------------------------------------*/
BEGIN
SET NOCOUNT ON;
DECLARE
@TransactionStarted INT,
@ErrorState INT,
@Msg NVARCHAR(MAX),
@ModifiedBy NVARCHAR(125) = SYSTEM_USER,
@ModifiedDateTime DATETIME= GEtDATE()
DECLARE
@GLData UDT_GLSheetData
IF (@@TRANCOUNT = 0)
BEGIN
SET XACT_ABORT ON;
SET @TransactionStarted = 1;
BEGIN TRANSACTION;
END
BEGIN TRY
MERGE GLSheetData AS Target
USING (SELECT mi.CountryName,mi.COMPANY,mi.[Desc],mi.[AcctID],mi.[AccountDesc],mi.[CUSTID],mi.[Site]
FROM @ParamGLSheetDataUdt mi) AS Source
ON (
Target.[CountryName] = Source.[CountryName]
AND Target.[COMPANY] = Source.[COMPANY]
AND Target.[Desc]=Source.[Desc] AND Target.[AcctID] = Source.[AcctID]
AND Target.[AccountDesc] = Source.[AccountDesc]
AND Target.[CUSTID] = Source.[CUSTID]
AND Target.[Site] = Source.[Site]
)
--WHEN MATCHED THEN
-- UPDATE SET Price = Source.Price,
-- Quantity = Source.Quantity
WHEN NOT MATCHED BY TARGET THEN
INSERT ([CountryName], [COMPANY],[Desc],[AcctID],[AccountDesc],[CUSTID],[Site])
VALUES (Source.[CountryName],Source.[COMPANY],Source.[Desc],Source.[AcctID],Source.[AccountDesc],Source.[CUSTID],Source.[Site]
);
IF ((@TransactionStarted = 1) AND (XACT_STATE() = 1))
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
--Catch Start
SELECT @ErrorState = ERROR_STATE();
SET @Msg = IsNull(ERROR_PROCEDURE(), '[dbo].[usp_INSAndUPD_GLSheetData] ') + ': ' + ERROR_MESSAGE() + ', ' +
'Line: ' + CONVERT(VARCHAR, ERROR_LINE()) + ', ' +
'Error: ' + CONVERT(VARCHAR, ERROR_NUMBER()) + ', ' +
'State: ' + CONVERT(VARCHAR, @ErrorState)
IF ((XACT_STATE() = -1) AND (@TransactionStarted = 1))
BEGIN
ROLLBACK TRANSACTION;
END
ELSE
BEGIN
IF ((XACT_STATE() = 1) AND (@TransactionStarted = 1))
BEGIN
COMMIT TRANSACTION;
END;
END;
RAISERROR(@Msg, 16, 1);
END CATCH;
END

Enjoy coding... :)