c#visual-studiooopentity-framework-coreoledb
Efficiency Suggestions for Moving Data from Excel to SQL Server Database using EF Core
I have a program that takes sheets of mixed data from an Excel file and places it into a SQL Server Database. It already runs relatively fast, but I wish to make it more dynamic. Currently, I use OleDb to select data from an Excel file and Entity Framework Core to add the data to a SQL Server Database.
I have a class for each sheet in the Excel file. The class I'm going to use as an example here is my vHost class. After I use OleDb to select all the data from the vHost Excel sheet (using SELECT *) I place the data into a Data Table object. I then go through each column in the Data Table to create a List<string> object of the column names in the Excel sheet. These are crucial for the program to understand where to insert data into the SQL Server Database. Once the list of column names is created, the program then indexes through all the data in the Data Table and creates a vHost object to place in the SQL Server Database. This is the portion that I wish to make more dynamic.
In order for the program to decide what object in the vHost class to set some data equal to, it compares an index in the Column List to a static string. If the column index we are on is equal or contains the same string for the column name, the object correlating to that in the vHost class is set equal to some data. This portion is long and grueling to type, but it allows me to create a program that will run with minimal errors.
Here is the code I have for the vHost section:
public static int Insert(string Sheet, OleDbConnection conn, int assessment_id, int objectCount)
{
//OleDb selects all the data inside the vHost sheet and places it inside a DataTable object
OleDbCommand command = new OleDbCommand("SELECT * FROM [" + Sheet + "$]", conn);
DataTable Data = new DataTable();
OleDbDataAdapter adapter = new OleDbDataAdapter(command);
adapter.Fill(Data);
//A for loop is used to add the column names into a List<string> object for reference
List<string> ColumnNames = new List<string>();
for (int i = 0; i < Data.Columns.Count; i++)
{
ColumnNames.Add(Data.Columns[i].ColumnName.ToUpper());
}
using (var context = new DataWarehouseContext()) //Allows access to Database Migrations
{
foreach (DataRow dataRow in Data.Rows) //Goes into each Row in the vHost DataTable
{
RvtoolsVHost vHost = new RvtoolsVHost();
for (int i = 0; i < dataRow.ItemArray.Length; i++) //Indexes through each item in the Rows
{
//Whatever the index of 'i' can be used to check the ColumnName of the object with the list object
//Need to check if vHost Migration has an object for row item we want to import
//Decide which item in a vHost object is being currently accessed and add it into the vHost object
if (ColumnNames[i].Equals("HOST"))
{
vHost.Name = dataRow[i].ToString();
}
if (ColumnNames[i].Contains("DATACENTER")) //For the Datacenter_ID in vHost
{
try
{
vHost.DatacenterId = vDatacenter.GetID(dataRow[i].ToString(), assessment_id);
}
catch (Exception)
{
}
}
if (ColumnNames[i].Contains("CLUSTER")) //For the Cluster_ID in vHost
{
try
{
vHost.VClusterId = vCluster.GetID(dataRow[i].ToString(), assessment_id);
}
catch (Exception)
{
vHost.VClusterId = null;
}
}
if (ColumnNames[i].Contains("CONFIG STATUS"))
{
vHost.ConfigStatus = dataRow[i].ToString();
}
if (ColumnNames[i].Contains("CPU MODEL"))
{
vHost.CpuModel = dataRow[i].ToString();
}
if (ColumnNames[i].Contains("SPEED"))
{
vHost.Speed = Convert.ToInt32(dataRow[i]);
}
if (ColumnNames[i].Contains("HT AVAILABLE"))
{
vHost.HtAvailable = bool.Parse(dataRow[i].ToString());
}
if (ColumnNames[i].Contains("HT ACTIVE"))
{
vHost.HtActive = bool.Parse(dataRow[i].ToString());
}
if (ColumnNames[i].Contains("# CPU"))
{
vHost.NumCpus = Convert.ToInt32(dataRow[i]);
}
if (ColumnNames[i].Contains("CORES PER CPU"))
{
vHost.CoresPerCpu = Convert.ToInt32(dataRow[i]);
}
if (ColumnNames[i].Contains("# CORES"))
{
vHost.NumCpuCores = Convert.ToInt32(dataRow[i]);
}
if (ColumnNames[i].Contains("CPU USAGE %"))
{
vHost.CpuUsage = Convert.ToInt32(dataRow[i]);
}
if (ColumnNames[i].Contains("# MEMORY"))
{
vHost.NumMemory = Convert.ToInt32(dataRow[i]);
}
if (ColumnNames[i].Contains("MEMORY USAGE %"))
{
vHost.MemoryUsage = Convert.ToInt32(dataRow[i]);
}
if (ColumnNames[i].Contains("CONSOLE"))
{
vHost.Console = Convert.ToInt32(dataRow[i]);
}
if (ColumnNames[i].Contains("# NICS"))
{
vHost.NumNics = Convert.ToInt32(dataRow[i]);
}
if (ColumnNames[i].Contains("# HBAS"))
{
vHost.NumHbas = Convert.ToInt32(dataRow[i]);
}
if (ColumnNames[i].Contains("# VMS"))
{
vHost.NumVms = Convert.ToInt32(dataRow[i]);
}
if (ColumnNames[i].Contains("VMS PER CORE"))
{
vHost.VmsPerCore = Convert.ToInt32(dataRow[i]);
}
if (ColumnNames[i].Contains("# VCPUS"))
{
vHost.NumVCpus = Convert.ToInt32(dataRow[i]);
}
if (ColumnNames[i].Contains("VCPUS PER CORE"))
{
vHost.VCpusPerCore = Convert.ToInt32(dataRow[i]);
}
if (ColumnNames[i].Contains("VRAM"))
{
vHost.VRam = Convert.ToInt32(dataRow[i]);
}
if (ColumnNames[i].Contains("VM USED MEMORY"))
{
vHost.VmUsedMemory = Convert.ToInt32(dataRow[i]);
}
if (ColumnNames[i].Contains("VM MEMORY SWAPPED"))
{
vHost.VmMemorySwapped = Convert.ToInt32(dataRow[i]);
}
if (ColumnNames[i].Contains("VM MEMORY BALLOONED"))
{
vHost.VmMemoryBallooned = Convert.ToInt32(dataRow[i]);
}
if (ColumnNames[i].Contains("VMOTION SUPPORT"))
{
vHost.VmotionSupport = bool.Parse(dataRow[i].ToString());
}
if (ColumnNames[i].Contains("STORAGE VMOTION SUPPORT"))
{
vHost.VmotionSupportStorage = bool.Parse(dataRow[i].ToString());
}
if (ColumnNames[i].Contains("CURRENT EVC"))
{
vHost.EvcCurrent = dataRow[i].ToString();
}
if (ColumnNames[i].Contains("MAX EVC"))
{
vHost.EvcMax = dataRow[i].ToString();
}
if (ColumnNames[i].Contains("ESX VERSION"))
{
vHost.EsxVersion = dataRow[i].ToString();
}
if (ColumnNames[i].Contains("BOOT TIME"))
{
vHost.BootTime = Convert.ToDateTime(dataRow[i].ToString());
}
if (ColumnNames[i].Contains("DNS SERVERS"))
{
vHost.DnsServers = dataRow[i].ToString();
}
if (ColumnNames[i].Contains("DHCP"))
{
vHost.Dhcp = bool.Parse(dataRow[i].ToString());
}
if (ColumnNames[i].Contains("DOMAIN"))
{
vHost.Domain = dataRow[i].ToString();
}
if (ColumnNames[i].Contains("DNS SEARCH ORDER"))
{
vHost.DnsSearchOrder = dataRow[i].ToString();
}
if (ColumnNames[i].Contains("NTP SERVER(S)"))
{
vHost.NtpServers = dataRow[i].ToString();
}
if (ColumnNames[i].Contains("NTPD RUNNING"))
{
vHost.NtpdRunning = bool.Parse(dataRow[i].ToString());
}
if (ColumnNames[i].Contains("TIME ZONE"))
{
vHost.TimeZone = dataRow[i].ToString();
}
if (ColumnNames[i].Contains("TIME ZONE NAME"))
{
vHost.TimeZoneName = dataRow[i].ToString();
}
if (ColumnNames[i].Contains("GMT OFFSET"))
{
vHost.GmtOffset = dataRow[i].ToString();
}
if (ColumnNames[i].Contains("VENDOR"))
{
vHost.Vendor = dataRow[i].ToString();
}
if (ColumnNames[i].Contains("MODEL"))
{
vHost.Model = dataRow[i].ToString();
}
if (ColumnNames[i].Contains("SERVICE TAG"))
{
vHost.ServiceTag = dataRow[i].ToString();
}
if (ColumnNames[i].Contains("OEM SPECIFIC STRING"))
{
vHost.OemSpecificString = dataRow[i].ToString();
}
if (ColumnNames[i].Contains("BIOS VERSION"))
{
vHost.BiosVersion = dataRow[i].ToString();
}
if (ColumnNames[i].Contains("BIOS DATE"))
{
vHost.BiosDate = Convert.ToDateTime(dataRow[i].ToString());
}
if (ColumnNames[i].Contains("OBJECT ID"))
{
vHost.ObjectId = dataRow[i].ToString();
}
}
vHost.AssessmentId = assessment_id;
context.RvtoolsVHost.Add(vHost);
context.SaveChanges();
objectCount += 47;
}
return objectCount;
}
}
Hopefully, my comments help you to understand the program too! The ObjectCount can be ignored along with anything with the assessment_id or any GetID() functions.
Also, if this helps, here is the vHost class. It was created by an EF Core Migration:
public partial class RvtoolsVHost
{
public RvtoolsVHost()
{
Location = new HashSet<Location>();
RvtoolsVHba = new HashSet<RvtoolsVHba>();
RvtoolsVInfo = new HashSet<RvtoolsVInfo>();
RvtoolsVMultiPath = new HashSet<RvtoolsVMultiPath>();
RvtoolsVNic = new HashSet<RvtoolsVNic>();
RvtoolsVRp = new HashSet<RvtoolsVRp>();
RvtoolsVScVmk = new HashSet<RvtoolsVScVmk>();
VHostToVSwitch = new HashSet<VHostToVSwitch>();
}
[Key]
public int VHostId { get; set; }
public string Name { get; set; }
public string ConfigStatus { get; set; }
public string CpuModel { get; set; }
public int? Speed { get; set; }
public bool? HtAvailable { get; set; }
public bool? HtActive { get; set; }
public int? NumCpus { get; set; }
public int? CoresPerCpu { get; set; }
public int? NumCpuCores { get; set; }
public int? CpuUsage { get; set; }
public int? NumMemory { get; set; }
public int? MemoryUsage { get; set; }
public int? Console { get; set; }
public int? NumNics { get; set; }
public int? NumHbas { get; set; }
public int? NumVms { get; set; }
public int? VmsPerCore { get; set; }
public int? NumVCpus { get; set; }
public int? VCpusPerCore { get; set; }
public int? VRam { get; set; }
public int? VmUsedMemory { get; set; }
public int? VmMemorySwapped { get; set; }
public int? VmMemoryBallooned { get; set; }
public bool? VmotionSupport { get; set; }
public bool? VmotionSupportStorage { get; set; }
public string EvcCurrent { get; set; }
public string EvcMax { get; set; }
public string EsxVersion { get; set; }
public DateTime? BootTime { get; set; }
public string DnsServers { get; set; }
public bool? Dhcp { get; set; }
public string Domain { get; set; }
public string DnsSearchOrder { get; set; }
public string NtpServers { get; set; }
public bool? NtpdRunning { get; set; }
public string TimeZone { get; set; }
public string TimeZoneName { get; set; }
public string GmtOffset { get; set; }
public string Vendor { get; set; }
public string Model { get; set; }
public string ServiceTag { get; set; }
public string OemSpecificString { get; set; }
public string BiosVersion { get; set; }
public DateTime? BiosDate { get; set; }
public string ObjectId { get; set; }
public int DatacenterId { get; set; }
public int? VClusterId { get; set; }
public int AssessmentId { get; set; }
public virtual Assessment Assessment { get; set; }
public virtual Datacenter Datacenter { get; set; }
public virtual RvtoolsVCluster VCluster { get; set; }
public virtual ICollection<Location> Location { get; set; }
public virtual ICollection<RvtoolsVHba> RvtoolsVHba { get; set; }
public virtual ICollection<RvtoolsVInfo> RvtoolsVInfo { get; set; }
public virtual ICollection<RvtoolsVMultiPath> RvtoolsVMultiPath { get; set; }
public virtual ICollection<RvtoolsVNic> RvtoolsVNic { get; set; }
public virtual ICollection<RvtoolsVRp> RvtoolsVRp { get; set; }
public virtual ICollection<RvtoolsVScVmk> RvtoolsVScVmk { get; set; }
public virtual ICollection<VHostToVSwitch> VHostToVSwitch { get; set; }
}
If there is a way to simplify the amount of static string comparisons or if statements that anyone can think of, that would be amazing!
Solution
Based on your description, you want to move data from excel to sql server by using efcore.
I suggest that you can convert the datatable to the list.
Here is a code example you can refer to:
static void Main(string[] args)
{
OleDbConnection connection = new OleDbConnection((@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + "D:\\test.xlsx" + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';"));
connection.Open();
OleDbCommand command = new OleDbCommand("SELECT * FROM [" + "Sheet1" + "$]", connection);
DataTable Data = new DataTable();
OleDbDataAdapter adapter = new OleDbDataAdapter(command);
adapter.Fill(Data);
connection.Close();
for (int i = Data.Rows.Count - 1; i >= 0; i--)
{
if (Data.Rows[i][0].ToString() == String.Empty)
{
Data.Rows.RemoveAt(i);
}
}
List<Customer> list = new List<Customer>();
list = (from DataRow dr in Data.Rows
select new Customer()
{
CustomerId = Convert.ToInt32(dr["CustomerId"]),
FirstName = dr["FirstName"].ToString(),
LastName = dr["LastName"].ToString()
}).ToList();
using (var context = new MyContext())
{
foreach (Customer item in list)
{
context.Customers.Add(item);
}
context.SaveChanges();
}
}
Customer.cs:
public class Customer
{
public int CustomerId { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
}
Mycontext.cs:
public class MyContext:DbContext
{
public DbSet<Customer> Customers { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder
.UseSqlServer(@"Connectionstring");
}
}
Excel file:
Data in database:
