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]
  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. {
  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. }
  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
  11. #region Public Methods
  12. public BaseDataAccess Transaction
  13. {
  14. get { return m_Access; }
  15. }
  17. public TransactionMethod Operation
  18. {
  19. set { operation = value; }
  20. }
  22. public BaseBusiness()
  23. {
  24. m_Access = new BaseDataAccess();
  25. }
  27. public BaseBusiness(BaseDataAccess transaction)
  28. {
  29. m_Access = transaction;
  30. }
  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
  89. #region Private Methods
  90. private void OpenConnection()
  91. {
  92. if (this.m_Access != null)
  93. this.m_Access.OpenConnection();
  94. }
  96. private void CloseConnection()
  97. {
  98. if (this.m_Access != null)
  99. this.m_Access.CloseConnection();
  100. }
  102. private void BeginTransaction()
  103. {
  104. if (this.m_Access != null)
  105. this.m_Access.BeginTransaction();
  106. }
  108. private void Commit()
  109. {
  110. if (this.m_Access != null)
  111. this.m_Access.CommitTransaction();
  112. }
  114. private void RollBack()
  115. {
  116. if (!_isTransactionRequired)
  117. return;
  119. if (this.m_Access != null)
  120. this.m_Access.RollbackTransaction();
  121. }
  122. #endregion
  124. }
Step 6
Create a Data Access layer .cs class:
  1. public class UserAccess : BaseDataAccess
  2. {
  3. #region Constrructor
  5. public UserAccess(BaseDataAccess baseaccess)
  6. : base(baseaccess)
  7. {
  9. }
  10. public UserAccess()
  11. {
  13. }
  14. #endregion
  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
  11. #region Public Properties
  12. public List<SqlParameter> DBParameters { get; set; }
  14. public virtual string MyConString
  15. {
  16. get
  17. {
  18. return ConfigurationManager.ConnectionStrings["iMixClaimConnection"].ConnectionString;
  19. }
  20. }
  22. public SqlTransaction Transaction { get { return _trans; } }
  23. #endregion
  25. #region Constructor
  26. public BaseDataAccess()
  27. {
  28. DBParameters = new List<SqlParameter>();
  29. log4net.Config.XmlConfigurator.Configure();
  30. }
  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
  41. #region Public Methods
  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());
  54. if (_adapter == null)
  55. _adapter = new SqlDataAdapter();
  56. _adapter.SelectCommand = _command;
  57. _adapter.Fill(recordsDs);
  58. return recordsDs;
  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. }
  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();
  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. }
  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. }
  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
  149. protected void AddParameter(string name, object value)
  150. {
  151. DBParameters.Add(new SqlParameter(name, value));
  152. }
  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. }
  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. }
  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
  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());
  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
  214. #region Transaction Members
  216. public bool BeginTransaction()
  217. {
  218. try
  219. {
  220. bool IsOK = this.OpenConnection();
  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. }
  237. return true;
  238. }
  240. public bool CommitTransaction()
  241. {
  242. try
  243. {
  244. _trans.Commit();
  245. }
  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. }
  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. }
  287. public bool OpenConnection()
  288. {
  289. try
  290. {
  291. if (this._conn == null)
  292. _conn = new SqlConnection(MyConString);
  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. }
  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. }
  338. }
  339. return true;
  340. }
  342. #endregion
  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. }
  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. }
  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. }
  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. }
  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. }
  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. }
  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;
  18. DECLARE
  19. @TransactionStarted INT,
  20. @ErrorState INT,
  21. @Msg NVARCHAR(MAX),
  22. @ModifiedBy NVARCHAR(125) = SYSTEM_USER,
  23. @ModifiedDateTime DATETIME= GEtDATE()
  25. DECLARE
  26. @GLData UDT_GLSheetData
  28. IF (@@TRANCOUNT = 0)
  29. BEGIN
  30. SET XACT_ABORT ON;
  31. SET @TransactionStarted = 1;
  32. BEGIN TRANSACTION;
  33. END
  35. BEGIN TRY
  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. );
  56. IF ((@TransactionStarted = 1) AND (XACT_STATE() = 1))
  57. COMMIT TRANSACTION;
  59. END TRY
  60. BEGIN CATCH
  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)
  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;
  81. RAISERROR(@Msg, 16, 1);
  83. END CATCH;
  85. END
Enjoy coding... :)