Ado.Net and Async insert of many rows to db

Question

I have implemented repository in Ado.Net which insert data to many tables. I insert all in one transaction and use async/await to improve performances. If you have any suggestions have to improve my code I would be grateful.

using MonitoringTool.Common.DomainModelsNew;
using MonitoringTool.Common.Extensions;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Threading.Tasks;
using Telexis.Util;
namespace DatabaseAccessNew.Repositories
{
 /// <summary>
 /// Import with Ado.Net
 /// </summary>
 internal class ImportRepository
 {
 private readonly ILogWrapper _logWrapper;
 private static readonly string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["MonitoringTool"].ConnectionString;
 public ImportRepository(ILogWrapper logWrapper)
 {
 _logWrapper = logWrapper;
 }
 /// <summary>
 /// Import statuses from monitoring information to database with ADO.NET
 /// </summary>
 /// <param name="monitoringInformation"></param>
 /// <returns>True if insert is successful else false</returns>
 public async Task<bool> ImportStatuses(MonitoringInformation monitoringInformation)
 {
 bool success = false;
 SqlTransaction trans = null;
 try
 {
 using (var connection = new SqlConnection(connectionString))
 {
 await connection.OpenAsync();
 using (trans = connection.BeginTransaction())
 {
 success = await ImportStatuses(monitoringInformation, trans);
 trans.Commit();
 }
 }
 }
 catch (Exception ex)
 {
 _logWrapper.Error(ex.Message);
 if (ex.InnerException != null)
 {
 _logWrapper.Error(ex.InnerException.Message);
 }
 _logWrapper.Debug(ex.ToString());
 // Handle the exception if the transaction fails to commit.
 try
 {
 // Attempt to roll back the transaction.
 trans.Rollback();
 }
 catch (Exception exRollback)
 {
 _logWrapper.Error(exRollback.Message);
 _logWrapper.Debug(exRollback.ToString());
 }
 }
 return success;
 }
 /// <summary>
 /// Import all statuses to db
 /// </summary>
 /// <param name="monitoringInformation"></param>
 /// <param name="trans">Transaction</param>
 /// <returns>True if insert is successful else false</returns>
 private async Task<bool> ImportStatuses(MonitoringInformation monitoringInformation, SqlTransaction trans)
 {
 int monitoringInformationId = await ImportMonitoringInformation(monitoringInformation, trans);
 var importEquipmentStatusTask = ImportEquipmentStatus(monitoringInformation.Equipment, monitoringInformationId, trans);
 var importDeviceStausesTask = ImportDeviceStatuses(monitoringInformation.Devices, monitoringInformationId, trans);
 var importInterfaceStausTask = ImportInterfaceStatuses(monitoringInformation.Interfaces, monitoringInformationId, trans);
 var importHardwareComponentStatusTask = ImportHardwareComponentStatuses(monitoringInformation.HardwareComponents, monitoringInformation.EODs, monitoringInformationId, trans);
 await Task.WhenAll(importEquipmentStatusTask, importDeviceStausesTask, importInterfaceStausTask, importHardwareComponentStatusTask);
 return true;
 }
 /// <summary>
 /// Import basic information about Monitoring
 /// </summary>
 /// <param name="monitoringInformation">Monitoring information</param>
 /// <param name="trans">Transaction</param>
 /// <returns>Monitoring information identifier</returns>
 private async Task<int> ImportMonitoringInformation(MonitoringInformation monitoringInformation, SqlTransaction trans)
 {
 if (monitoringInformation == null)
 {
 throw new ArgumentNullException(nameof(monitoringInformation));
 }
 const string query = @" INSERT INTO dbo.MonitoringInformation ([Filename] ,FileType ,GeneratedDate ,Version ,InsertedDateTime)
 VALUES (@Filename,@FileType,@GeneratedDate,@Version,@InsertedDateTime);
 SELECT CAST(scope_identity() AS int)";
 try
 {
 using (var cmd = new SqlCommand(query, trans.Connection, trans))
 {
 cmd.Parameters.Add("@Filename", SqlDbType.NVarChar);
 cmd.Parameters.Add("@FileType", SqlDbType.Int);
 cmd.Parameters.Add("@GeneratedDate", SqlDbType.DateTime);
 cmd.Parameters.Add("@Version", SqlDbType.NVarChar);
 cmd.Parameters.Add("@InsertedDateTime", SqlDbType.DateTime);
 cmd.Parameters["@Filename"].Value = monitoringInformation.Filename;
 cmd.Parameters["@FileType"].Value = monitoringInformation.FileType;
 cmd.Parameters["@GeneratedDate"].Value = monitoringInformation.GeneratedDate;
 cmd.Parameters["@Version"].Value = monitoringInformation.Version;
 cmd.Parameters["@InsertedDateTime"].Value = monitoringInformation.InsertedDateTime;
 return (int)await cmd.ExecuteScalarAsync();
 }
 }
 catch
 {
 _logWrapper.Error($"Error in {nameof(ImportMonitoringInformation)}");
 throw;
 }
 }
 /// <summary>
 /// Import equipment status
 /// </summary>
 /// <param name="equipment">Equipment</param>
 /// <param name="monitoringInformationId">Monitoring information identifier</param>
 /// <param name="trans">Transaction</param>
 private async Task ImportEquipmentStatus(Equipment equipment, int monitoringInformationId, SqlTransaction trans)
 {
 if (equipment == null)
 {
 return;
 }
 const string query = @" INSERT INTO dbo.EquipmentStatus (EquipmentId,MonitoringInformationId,Status,LastUpdate,GpsTime,Position,Trip,GpsData)
 SELECT e.EquipmentId,@MonitoringInformationId,@Status,@LastUpdate,@GpsTime,@Position,@Trip,@GpsData
 FROM dbo.Equipment e
 WHERE e.Name = @EquipmentName;";
 try
 {
 using (var cmd = new SqlCommand(query, trans.Connection, trans))
 {
 cmd.Parameters.Add("@MonitoringInformationId", SqlDbType.Int);
 cmd.Parameters.Add("@Status", SqlDbType.NVarChar);
 cmd.Parameters.Add("@LastUpdate", SqlDbType.DateTime);
 cmd.Parameters.Add("@GpsTime", SqlDbType.DateTime);
 cmd.Parameters.Add("@Position", SqlDbType.NVarChar);
 cmd.Parameters.Add("@Trip", SqlDbType.Int);
 cmd.Parameters.Add("@GpsData", SqlDbType.NVarChar);
 cmd.Parameters.Add("@EquipmentName", SqlDbType.NVarChar);
 cmd.Parameters["@MonitoringInformationId"].Value = monitoringInformationId;
 cmd.Parameters["@Status"].Value = equipment.EquipmentStatus.Status;
 cmd.Parameters["@LastUpdate"].Value = equipment.EquipmentStatus.LastUpdate;
 cmd.Parameters["@GpsTime"].Value = equipment.EquipmentStatus.GpsTime.DbValueOrNull();
 cmd.Parameters["@Position"].Value = equipment.EquipmentStatus.Position;
 cmd.Parameters["@Trip"].Value = equipment.EquipmentStatus.Trip.DbValueOrNull();
 cmd.Parameters["@GpsData"].Value = equipment.EquipmentStatus.GpsData;
 cmd.Parameters["@EquipmentName"].Value = equipment.Name;
 await cmd.ExecuteNonQueryAsync();
 }
 }
 catch
 {
 _logWrapper.Error($"Error in {nameof(ImportEquipmentStatus)}");
 throw;
 }
 }
 /// <summary>
 /// Import device statuses
 /// </summary>
 /// <param name="devices">Devices</param>
 /// <param name="monitoringInformationId">Monitoring information identifier</param>
 /// <param name="trans">Transaction</param>
 private async Task ImportDeviceStatuses(IEnumerable<Device> devices, int monitoringInformationId, SqlTransaction trans)
 {
 if (devices?.Any() != true)
 {
 return;
 }
 const string query = @" INSERT INTO dbo.DeviceStatus (DeviceId,MonitoringInformationId,Status,[File])
 SELECT d.DeviceId, @MonitoringInformationId , @Status, @File
 FROM dbo.Device d
 WHERE d.Name = @DeviceName; ";
 try
 {
 var importTasks = new List<Task<int>>();
 foreach (var device in devices)
 {
 using (var cmd = new SqlCommand(query, trans.Connection, trans))
 {
 cmd.Parameters.Add("@DeviceName", SqlDbType.NVarChar);
 cmd.Parameters.Add("@MonitoringInformationId", SqlDbType.Int);
 cmd.Parameters.Add("@Status", SqlDbType.NChar);
 cmd.Parameters.Add("@File", SqlDbType.NChar);
 cmd.Parameters["@DeviceName"].Value = device.Name;
 cmd.Parameters["@MonitoringInformationId"].Value = monitoringInformationId;
 cmd.Parameters["@Status"].Value = device.DeviceStatus.Status;
 cmd.Parameters["@File"].Value = device.DeviceStatus.File;
 importTasks.Add(cmd.ExecuteNonQueryAsync());
 }
 }
 await Task.WhenAll(importTasks);
 }
 catch
 {
 _logWrapper.Error($"Error in {nameof(ImportDeviceStatuses)}");
 throw;
 }
 }
 /// <summary>
 /// Import eod statuses
 /// </summary>
 /// <param name="eods">Eods</param>
 /// <param name="monitoringInformationId">Monitoring information identifier</param>
 /// <param name="hardwareComponentId">Hardware component identifier</param>
 /// <param name="trans">Transaction</param>
 private async Task ImportEodStatuses(IEnumerable<Eod> eods, int monitoringInformationId, int hardwareComponentId, SqlTransaction trans)
 {
 const string query = @" INSERT INTO dbo.EODStatus (EodId,MonitoringInformationId,HardwareComponent_HardwareComponentId,FileType,FileVersion,Location,EffectiveDate,Versiontype)
 SELECT e.EODId,@MonitoringInformationId,@HardwareComponent_HardwareComponentId,@FileType,@FileVersion,@Location,@EffectiveDate,@Versiontype
 FROM dbo.EOD e
 WHERE e.FileVersion = @Name; ";
 try
 {
 var importTasks = new List<Task<int>>();
 foreach (var eod in eods)
 {
 using (var cmd = new SqlCommand(query, trans.Connection, trans))
 {
 cmd.Parameters.Add("@MonitoringInformationId", SqlDbType.Int);
 cmd.Parameters.Add("@HardwareComponent_HardwareComponentId", SqlDbType.Int);
 cmd.Parameters.Add("@FileType", SqlDbType.Int);
 cmd.Parameters.Add("@FileVersion", SqlDbType.SmallInt);
 cmd.Parameters.Add("@Location", SqlDbType.NVarChar);
 cmd.Parameters.Add("@EffectiveDate", SqlDbType.DateTime);
 cmd.Parameters.Add("@Versiontype", SqlDbType.SmallInt);
 cmd.Parameters.Add("@Name", SqlDbType.NVarChar);
 cmd.Parameters["@MonitoringInformationId"].Value = monitoringInformationId;
 cmd.Parameters["@HardwareComponent_HardwareComponentId"].Value = hardwareComponentId;
 cmd.Parameters["@FileType"].Value = eod.EodStatus.FileType;
 cmd.Parameters["@FileVersion"].Value = eod.EodStatus.FileVersion;
 cmd.Parameters["@Location"].Value = eod.EodStatus.Location;
 cmd.Parameters["@EffectiveDate"].Value = eod.EodStatus.EffectiveDate;
 cmd.Parameters["@Versiontype"].Value = eod.EodStatus.VersionType;
 cmd.Parameters["@Name"].Value = eod.FileVersion;
 importTasks.Add(cmd.ExecuteNonQueryAsync());
 }
 }
 await Task.WhenAll(importTasks);
 }
 catch
 {
 _logWrapper.Error($"Error in {nameof(ImportEodStatuses)}");
 throw;
 }
 }
 /// <summary>
 /// Import interface statuses
 /// </summary>
 /// <param name="interfaces">Interfaces</param>
 /// <param name="monitoringInformationId">Monitoring information identifier</param>
 /// <param name="trans">Transaction</param>
 private async Task ImportInterfaceStatuses(IEnumerable<Interface> interfaces, int monitoringInformationId, SqlTransaction trans)
 {
 if (interfaces?.Any() != true)
 {
 return;
 }
 const string query = @" INSERT INTO dbo.InterfaceStatus (InterfaceId,MonitoringInformationId,Status,StatusTime,LastUpdate)
 SELECT i.InterfaceId, @MonitoringInformationId , @Status, @StatusTime, @LastUpdate
 FROM dbo.Interface i
 WHERE i.Name = @InterfaceName; ";
 try
 {
 var importTasks = new List<Task<int>>();
 foreach (var interf in interfaces)
 {
 using (var cmd = new SqlCommand(query, trans.Connection, trans))
 {
 cmd.Parameters.Add("@MonitoringInformationId", SqlDbType.Int);
 cmd.Parameters.Add("@Status", SqlDbType.NChar);
 cmd.Parameters.Add("@StatusTime", SqlDbType.DateTime);
 cmd.Parameters.Add("@LastUpdate", SqlDbType.DateTime);
 cmd.Parameters.Add("@InterfaceName", SqlDbType.NChar);
 cmd.Parameters["@MonitoringInformationId"].Value = monitoringInformationId;
 cmd.Parameters["@Status"].Value = interf.InterfaceStatus.Status;
 cmd.Parameters["@StatusTime"].Value = interf.InterfaceStatus.StatusTime;
 cmd.Parameters["@LastUpdate"].Value = interf.InterfaceStatus.LastUpdate;
 cmd.Parameters["@InterfaceName"].Value = interf.Name;
 importTasks.Add(cmd.ExecuteNonQueryAsync());
 }
 }
 await Task.WhenAll(importTasks);
 }
 catch
 {
 _logWrapper.Error($"Error in {nameof(ImportInterfaceStatuses)}");
 throw;
 }
 }
 /// <summary>
 /// Import hardware component statuses
 /// </summary>
 /// <param name="hardwareComponents">hardware components</param>
 /// <param name="eods">Eods</param>
 /// <param name="monitoringInformationId">Monitoring information identifier</param>
 /// <param name="trans">Transaction</param>
 private async Task ImportHardwareComponentStatuses(IEnumerable<HardwareComponent> hardwareComponents, IEnumerable<Eod> eods, int monitoringInformationId, SqlTransaction trans)
 {
 if (hardwareComponents?.Any() != true)
 {
 return;
 }
 const string query = @" INSERT INTO dbo.HardwareStatusInformation(MonitoringInformationId,HardwareComponentId,UpTime,RamTotal,RamFree,RamUnit,DiskTotal,DiskFree,LastUpdate,GroupId)
 SELECT @MonitoringInformationId,h.HardwareComponentId,@UpTime,@RamTotal,@RamFree,@RamUnit,@DiskTotal,@DiskFree,@LastUpdate,@GroupId
 FROM dbo.HardwareComponent h
 WHERE h.DeviceID = @Name; 
 SELECT HardwareComponentId FROM dbo.HardwareComponent WHERE DeviceID = @Name";
 try
 {
 var importSoftwareComponentStatusesTasks = new List<Task>();
 var importEodStatusesTasks = new List<Task>();
 foreach (var hardwarecomponent in hardwareComponents)
 {
 int hardwareComponentId;
 using (var cmd = new SqlCommand(query, trans.Connection, trans))
 {
 cmd.Parameters.Add("@MonitoringInformationId", SqlDbType.Int);
 cmd.Parameters.Add("@UpTime", SqlDbType.BigInt);
 cmd.Parameters.Add("@RamTotal", SqlDbType.BigInt);
 cmd.Parameters.Add("@RamFree", SqlDbType.BigInt);
 cmd.Parameters.Add("@RamUnit", SqlDbType.NVarChar);
 cmd.Parameters.Add("@DiskTotal", SqlDbType.BigInt);
 cmd.Parameters.Add("@DiskFree", SqlDbType.BigInt);
 cmd.Parameters.Add("@LastUpdate", SqlDbType.DateTime);
 cmd.Parameters.Add("@GroupId", SqlDbType.Int);
 cmd.Parameters.Add("@Name", SqlDbType.NVarChar);
 cmd.Parameters["@MonitoringInformationId"].Value = monitoringInformationId;
 cmd.Parameters["@UpTime"].Value = hardwarecomponent.HardwareStatusInformation.UpTime;
 cmd.Parameters["@RamTotal"].Value = hardwarecomponent.HardwareStatusInformation.RamTotal;
 cmd.Parameters["@RamFree"].Value = hardwarecomponent.HardwareStatusInformation.RamFree;
 cmd.Parameters["@DiskTotal"].Value = hardwarecomponent.HardwareStatusInformation.DiskTotal;
 cmd.Parameters["@DiskFree"].Value = hardwarecomponent.HardwareStatusInformation.DiskFree;
 cmd.Parameters["@LastUpdate"].Value = hardwarecomponent.HardwareStatusInformation.LastUpdate;
 cmd.Parameters["@RamUnit"].Value = hardwarecomponent.HardwareStatusInformation.RamUnit;
 cmd.Parameters["@GroupId"].Value = hardwarecomponent.GroupId;
 cmd.Parameters["@Name"].Value = hardwarecomponent.DeviceId;
 hardwareComponentId = (int)await cmd.ExecuteScalarAsync();
 }
 importSoftwareComponentStatusesTasks.Add(ImportSoftwareComponentStatuses(hardwarecomponent.SoftwareComponents, monitoringInformationId, hardwareComponentId, trans));
 importEodStatusesTasks.Add(ImportEodStatuses(eods, monitoringInformationId, hardwareComponentId, trans));
 }
 await Task.WhenAll(importSoftwareComponentStatusesTasks);
 await Task.WhenAll(importEodStatusesTasks);
 }
 catch
 {
 _logWrapper.Error($"Error in {nameof(ImportHardwareComponentStatuses)}");
 throw;
 }
 }
 /// <summary>
 /// Import software component statuses
 /// </summary>
 /// <param name="softwareComponents">Software components</param>
 /// <param name="monitoringInformationId">Monitoring information identifier</param>
 /// <param name="hardwareComponentId">Hardware component identifier</param>
 /// <param name="trans">Transaction</param>
 private async Task ImportSoftwareComponentStatuses(IEnumerable<SoftwareComponent> softwareComponents, int monitoringInformationId, int hardwareComponentId, SqlTransaction trans)
 {
 const string query = @" INSERT INTO dbo.SoftwareComponentStatus(SoftwareComponentId,LastUpdate,Status,Version,MonitoringInformationId,HardwareComponentId)
 SELECT s.SoftwareComponentId,@LastUpdate,@Status,@Version,@MonitoringInformationId,@HardwareComponentId
 FROM dbo.SoftwareComponent s
 WHERE s.Name = @Name; ";
 try
 {
 var importTasks = new List<Task<int>>();
 foreach (var softwarecomponent in softwareComponents)
 {
 using (var cmd = new SqlCommand(query, trans.Connection, trans))
 {
 cmd.Parameters.Add("@LastUpdate", SqlDbType.DateTime);
 cmd.Parameters.Add("@Status", SqlDbType.NVarChar);
 cmd.Parameters.Add("@Version", SqlDbType.NVarChar);
 cmd.Parameters.Add("@Name", SqlDbType.NVarChar);
 cmd.Parameters.Add("@MonitoringInformationId", SqlDbType.Int);
 cmd.Parameters.Add("@HardwareComponentId", SqlDbType.Int);
 cmd.Parameters.Add("@SoftwareComponentId", SqlDbType.Int);
 cmd.Parameters["@LastUpdate"].Value = softwarecomponent.SoftwareComponentStatus.LastUpdate;
 cmd.Parameters["@Status"].Value = softwarecomponent.SoftwareComponentStatus.Status;
 cmd.Parameters["@Version"].Value = softwarecomponent.SoftwareComponentStatus.Version;
 cmd.Parameters["@Name"].Value = softwarecomponent.Name;
 cmd.Parameters["@MonitoringInformationId"].Value = monitoringInformationId;
 cmd.Parameters["@HardwareComponentId"].Value = hardwareComponentId;
 cmd.Parameters["@SoftwareComponentId"].Value = softwarecomponent.SoftwareComponentId;
 importTasks.Add(cmd.ExecuteNonQueryAsync());
 }
 }
 await Task.WhenAll(importTasks);
 }
 catch
 {
 _logWrapper.Error($"Error in {nameof(ImportSoftwareComponentStatuses)}");
 throw;
 }
 }
 }
}

• dfhwze: using (trans = connection.BeginTransaction()) -> does the Dispose need to run on the same thread as the initializer?

Answer 1

New command in the loop is wasteful. Create it once and loop on the values.

foreach (var softwarecomponent in softwareComponents)
{
 using (var cmd = new SqlCommand(query, trans.Connection, trans))

SQL has async methods SqlCommand.BeginExecuteNonQuery;

It is one set of write heads on the other end. My experience is a single insert command will perform best. For sure it has less impact on resources.

I am not even sure this will work. You don't mention enabling MARS.

MARS operations are not thread-safe.

• Raskolnikov: Are you sure? I am running insert in parallel with WhenAll
• paparazzo: I can see what you are doing and in my opinion it is wasteful.
• Raskolnikov: MARS is true. I don't see why would be important in my case that MARS is not thread safe. I am doing just inserts.
• paparazzo: Uh, what if you catch an exception from another thread?
• Raskolnikov: What then? In this case everything is in same transaction and it will rollback. Another thread from some another repository is another connection.If i get exception there than I will handle error there. Please give me more details what you think that is problem.