How can I create properties in a class dynamically

Question

For importing an Excel sheet in SQL Table I used this code, it uses SqlBulkCopy. How can I Remove the mapper class and create the columns dynamically?

using SQL;

namespace Reader
{
    Public partial class Form1 :Form
    {
        /// <summary>
        /// Import Excel document into the SQL Database and Datagridview
        /// </summary>
        private void ImportExcel_Click(object sender, EventArgs e)
        {
            try
            {
                using (OpenFileDialog ImportExcelFileDialog = new OpenFileDialog() { Filter = "Excel Workbook|*.xlsx|Excel 97 -2003 Workbook|*.xls" })
                {
                    if (ImportExcelFileDialog.ShowDialog() == DialogResult.OK)
                    {
                        using (var stream = File.Open(ImportExcelFileDialog.FileName, FileMode.Open, FileAccess.Read))
                        {
                            using (IExcelDataReader Reader = ExcelReaderFactory.CreateReader(stream))
                            {
                                DataSet result = Reader.AsDataSet(new ExcelDataSetConfiguration()
                                {
                                    ConfigureDataTable = (_) => new ExcelDataTableConfiguration() { UseHeaderRow = true }
                                });
                                SqlConnection Connection = new SqlConnection(SQL_Commands._Connectionstring);
                                Connection.Open();

                                //SqliteDatabase[3] will give the table name that is used in SQLExpress
                                sqlcommands.DeleteTable(SqliteDatabase[3]);

                                //this is created from a SQL Query file there is only one column and that is ID
                                sqlcommands.RecreateDatabase(Connection);

                                //Get result from Excel file and create a Table from it.
                                tableCollection = result.Tables;
                                DataTable dt = tableCollection[SqliteDatabase[3]];

                                //Create columns in SQL Database 
                                foreach(DataColumn column in dt.Columns)
                                {
                                    if(column.ColumnName != "ID")
                                    {
                                        string columnName = "[" + column.ColumnName + "]";
                                        sqlcommands.AddColumn(columnName, SQLite.SqliteDatabase[3], "Text");
                                    }
                                }

                                //write already the values to datagridview 
                                InstrumentsBindingSource.DataSource = dt;

                                //Convert Datatable to IEnumerable(Instruments is a Mapper class)
                                var parts = BulkHelper.DataTableToIEnumerable<Instruments>(dt);
                                sqlcommands.ImportToExcel(parts, Connection);
                                sqlcommands.UpdateTableTotal(SQLite.SqliteDatabase[3], InstrumentsBindingSource, dataGridView1);
                            }
                        }
                    }
                }
            }
            catch (Exception EX)
            {
                MessageBox.Show(EX.ToString(), "Import Excel Sheet", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }
    }
}

Convert DataTable to IEnumerable

namespace SQL
{
    public static class BulkHelper
    {
        public static IEnumerable<T> DataTableToIEnumerable<T>(this DataTable table) where T : class, new()
        {
            try
            {
                var objType = typeof(T);
                ICollection<PropertyInfo> properties;

                lock (_Properties)
                {
                    if (!_Properties.TryGetValue(objType, out properties))
                    {
                        properties = objType.GetProperties().Where(property => property.CanWrite).ToList();
                        _Properties.Add(objType, properties);
                    }
                }

                var list = new List<T>(table.Rows.Count);

                foreach (var row in table.AsEnumerable().Skip(1))
                {
                    var obj = new T();

                    foreach (var prop in properties)
                    {
                        try
                        {
                            var propType = Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType;
                            var SafeValue = row[prop.Name] == null ? null : Convert.ChangeType(row[prop.Name], propType);

                            prop.SetValue(obj, SafeValue, null);
                        }
                        catch
                        {
                            // ignored
                        }
                    }
                    list.Add(obj);
                }
                return list;
            }
            catch
            {
                return Enumerable.Empty<T>();
            }
        }
    }
}

Create BulkData and write it to SQL Table

namespace SQL
{
    public class SQL_Commands
    {
        public void ImportToExcel(IEnumerable<Instruments> Parts, SqlConnection connection)
        {
            try
            {
                var bulkcopy = new SqlBulkCopy(connection);
                bulkcopy.DestinationTableName = "Instruments";
                bulkcopy.ColumnMappings.Add("Tag", "Tag");
                bulkcopy.ColumnMappings.Add("Area", "Area");
                bulkcopy.ColumnMappings.Add("Number", "Number");
                bulkcopy.ColumnMappings.Add("Tag_Name", "Tag_Name");
                bulkcopy.ColumnMappings.Add("Component_Description", "Component_Description");
                bulkcopy.ColumnMappings.Add("Function", "Function");
                bulkcopy.ColumnMappings.Add("Brand", "Brand");
                bulkcopy.ColumnMappings.Add("Type", "Type");
                bulkcopy.ColumnMappings.Add("M_Connection", "M_Connection");
                bulkcopy.ColumnMappings.Add("E_Connection", "E_Connection");
                bulkcopy.ColumnMappings.Add("Range", "Range");
                bulkcopy.ColumnMappings.Add("Remark", "Remark");
                bulkcopy.ColumnMappings.Add("Ordering_Code", "Ordering_Code");
                bulkcopy.ColumnMappings.Add("Panel", "Panel");
                bulkcopy.ColumnMappings.Add("DI", "DI");
                bulkcopy.ColumnMappings.Add("DO", "DO");
                bulkcopy.ColumnMappings.Add("AI", "AI");
                bulkcopy.ColumnMappings.Add("AO", "AO");
                bulkcopy.ColumnMappings.Add("Ethernet", "Ethernet");
                bulkcopy.ColumnMappings.Add("ASI", "ASI");

                using (var datareader = new ObjectDataReader<Instruments>(Parts))
                {
                    bulkcopy.WriteToServer(datareader);
                }
            }
            catch (Exception EX)
            {
                MessageBox.Show(EX.ToString(), "InsertBulk", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }
    }
}

Mapper Class

namespace SQL
{
    public class Instruments
    {
        public string Tag { get; set; }
        public string Area { get; set; }
        public string Number { get; set; }
        public string Tag_Name { get; set; }
        public string Component_Description { get; set; }
        public string Function { get; set; }
        public string Brand { get; set; }
        public string Type { get; set; }
        public string M_Connection { get; set; }
        public string E_Connection { get; set; }
        public string Range { get; set; }
        public string Remark { get; set; }
        public string Ordering_Code { get; set; }
        public string Panel { get; set; }
        public string DI { get; set; }
        public string DO { get; set; }
        public string AI { get; set; }
        public string AO { get; set; }
        public string Ethernet { get; set; }
        public string ASI { get; set; }
    }
}

I tried to search on the web, but I could not find a good solution for it.

Please see this not a direct solution but related https://dapper-tutorial.net/knowledge-base/38234218/dapper---map-result-set-with-unknown-number-of-columns-to-object-with-array-property — Soumen Mukherjee, Sep 21 '19 at 11:53
possible suggestion https://stackoverflow.com/q/16944038/125981 — Mark Schultheiss, Sep 21 '19 at 11:57
or the second part of https://stackoverflow.com/a/2978440/125981 THIS might even be exactly what you seek. — Mark Schultheiss, Sep 21 '19 at 11:59
Yes thats it, I don't even have to convert it now. I didn't know you could use DataTable directly in bulkcopy.WriteToServer (DataTable). — Dick Hof, Sep 22 '19 at 07:48

score 0 · Answer 1 · edited Feb 17 '20 at 20:25

I changed it to this and it works. Thanks

/// <summary>
/// Import Excel document into the SQL Database and Datagridview
/// </summary>
private void ImportExcel_Click(object sender, EventArgs e)
{
        try
        {
            using (OpenFileDialog ImportExcelFileDialog = new OpenFileDialog() { Filter = "Excel Workbook|*.xlsx|Excel 97 -2003 Workbook|*.xls" })
            {
                if (ImportExcelFileDialog.ShowDialog() == DialogResult.OK)
                {
                    using (var stream = File.Open(ImportExcelFileDialog.FileName, FileMode.Open, FileAccess.Read))
                    {
                        using (IExcelDataReader Reader = ExcelReaderFactory.CreateReader(stream))
                        {
                            DataSet result = Reader.AsDataSet(new ExcelDataSetConfiguration()
                            {
                                ConfigureDataTable = (_) => new ExcelDataTableConfiguration() { UseHeaderRow = true }
                            });
                            SqlConnection Connection = new SqlConnection(SQL_Commands._Connectionstring);
                            Connection.Open();
                            sqlcommands.DeleteTable(SqliteDatabase[3]);
                            //this is created from a SQL Query file there is only one column and that is ID
                            sqlcommands.RecreateDatabase(Connection);
                            //Get result from Excel file and create a Table from it.
                            tableCollection = result.Tables;
                            DataTable dt = tableCollection[SqliteDatabase[3]];

                           // Create new List
                            List<string> ListColums = new List<string>();

                            //Create columns in SQL Database 
                            foreach(DataColumn column in dt.Columns)
                            {
                                if(column.ColumnName != "ID")
                                {
                                    string columnName = "[" + column.ColumnName + "]";
                                    sqlcommands.AddColumn(columnName, SQLite.SqliteDatabase[3], "Text");
                                    //Add Column Names to List<string>
                                    ListColums.Add(column.ColumnName);
                                }
                            }

                            //write already the values to datagridview 
                            InstrumentsBindingSource.DataSource = dt;

                            //Create a connection
                            sqlcommands.ImportFromExcel(Connection,dt, ListColums);
                            sqlcommands.UpdateTableTotal(SQLite.SqliteDatabase[3], InstrumentsBindingSource, dataGridView1);
                        }
                    }
                }
            }
        }
        catch (Exception EX)
        {
            MessageBox.Show(EX.ToString(), "UpdateTableTotal", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
}

public void ImportFromExcel(SqlConnection connection,DataTable _dt,List<string> ColumnNames )
{
    try
    {
            // Get the DataTable 
            DataTable dtInsertRows = _dt;

            using (SqlBulkCopy bulkcopy = new SqlBulkCopy(connection.ConnectionString, SqlBulkCopyOptions.KeepIdentity))
            {
                bulkcopy.DestinationTableName = "Instruments";
                bulkcopy.BatchSize = _dt.Rows.Count;

                foreach (string Column in ColumnNames)
                {
                    var split = Column.Split(new[] { ',' });
                    bulkcopy.ColumnMappings.Add(split.First(), split.Last());
                }
                bulkcopy.WriteToServer(dtInsertRows);
            }
    }
    catch (Exception EX)
    {
        MessageBox.Show(EX.ToString(), "InsertBulk", MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
}

How can I create properties in a class dynamically

1 Answers1