Inser data to DB by SqlBulkCopy (.NET)

[Pepee4]

Hello,
Iam trying to fill DB with data from osci, but I did not used .NET yet. (BulkCopy should be the best to upload tousands of rows)
this is how I expect the VI should look like.

The problem is that I dont know how to create these rows/table and execute command.
Have anyone experience with this ? 

Iam using LabVIEW version 2023 Q3 64-bit

[tst]

I'm not familiar with this class, but it might not be as efficient as you might hope, since LV is not a .NET language and building the relevant data types also has overhead. Looking at the documentation, it looks like you can pass a DataTable or an array of DataRows (the case in your example), so you need to build one of those with the relevant data.

I'm not sure if it's possible to create a DataRow on its own, but here's one example of how you can create a DataTable and add rows to it:

I would suggest testing first with standard SQL commands using something like the DB connectivity toolkit or calling the ADO.NET classes in LV directly and only go for this option if the performance doesn't match what you need.

[Pepee4]

This was mine best 

Had there two problems:
when I tried to add column 4 then I get error: column 4 was not found
I did not found out how to insert GUID (uniqueidentifier) (into different table)

[Pepee4 · Accepted solution]

Final solution was to use DLL

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data.SqlClient;
using System.Data;
using System.Linq;
using System.Text;

namespace BulkCopyLabview
{
    public class Bulk
    {
        private string connectionString = null;

        private List<AutomaticFunctionTest_Osci_out> data;
        public Bulk(string _connectionString)
        {
            this.connectionString = _connectionString;
            this.data = new List<AutomaticFunctionTest_Osci_out>();
        }

        public void Insert(InputData obj)
        {
            Guid g = new Guid(obj.Test_ID);
            Guid newone = new Guid();
            this.data.Add(new AutomaticFunctionTest_Osci_out
            {
                Id = newone,
                CH1 = obj.CH1,
                CH2 = obj.CH2,
                CH3 = obj.CH3,
                CH4 = obj.CH4,
                Function_ID = obj.Function_ID,
                Run = obj.Run,
                Test_ID = g
            });
        }
        public string Submit()
        {
            try
            {
                if (this.connectionString == null)
                {
                    return "FAIL: ConnectionString is null";
                }
                using (var bulk = new SqlBulkCopy(this.connectionString))
                {
                    bulk.DestinationTableName = "AutomaticFunctionTest_Osci_out";
                    bulk.BulkCopyTimeout = 60;
                    bulk.WriteToServer(Helper.AsDataTable(this.data));
                }
                return "SUCCESS";
            }
            catch (Exception ex)
            {
                return "FAIL: " + ex.Message;
            }
        }
    }

    public static class Helper
    {
        public static DataTable AsDataTable<T>(this IEnumerable<T> data)
        {
            PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
            var table = new DataTable();
            foreach (PropertyDescriptor prop in properties)
                table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
            foreach (T item in data)
            {
                DataRow row = table.NewRow();
                foreach (PropertyDescriptor prop in properties)
                    row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
                table.Rows.Add(row);
            }
            return table;
        }
    }

    public class InputData
    {
        public string Test_ID { get; set; }
        public int Function_ID { get; set; }
        public int Run { get; set; }
        public double CH1 { get; set; }
        public double CH2 { get; set; }
        public double CH3 { get; set; }
        public double CH4 { get; set; }
    }

    public class AutomaticFunctionTest_Osci_out
    {
        public Guid Id { get; set; }
        public Guid Test_ID { get; set; }
        public int Function_ID { get; set; }
        public int Run { get; set; }
        public double CH1 { get; set; }
        public double CH2 { get; set; }
        public double CH3 { get; set; }
        public double CH4 { get; set; }
    }
}