Check if datatable column name is default column name C#

Question

I had to get excel data into DataTable example copied from this. If the column name is an empty string, when data is getting into DataTable the empty name is converted into a dynamic name like F1, F3, F22. If the column name has a valid value it's okay, but when I try to get column name of empty column name, dynamic F22 name is getting. F22 doesn't exist in the excel sheet, I have to inform the user that he has empty column name which is not valid.

So the question is how to tell if column name is dynamic set by the OLEDB?

DataColumnCollection excelDataColumns;
....
for (int i = 3; i < excelDataColumns.Count; i++) {
    var columnName = excelDataColumns[i].ColumnName;

    // Check if column name is blank
    if (IsExcelDefaultColumnName(excelDataColumns[i], i))
    {
        invalidEntries.Add("Blank column name");
    }
    else
    {
        var result = IsValidData(GetFullData(columnName));
        if (!result.Item1 && !columnName.Any(char.IsDigit))
        {
             invalidEntries.Add(columnName);
        }
    }
}

score 0 · Answer 1 · answered Sep 27 '19 at 14:39

I think I understand this question as you need to read the database schema to tell if a column name is in a table? (is that correct?)

If you have access to the SQL Server database via a connectionstring, I have a tool that reads the database schema.

This is a sample from my open source project DataTier.Net https://github.com/DataJuggler/DataTier.Net (end plug)

This uses Nuget Package: DataJuggler.Net

// read database schema
database = sqlConnector.LoadDatabaseSchema(database);

The database has a tables collection and each table has a Fields collection.

The class SQL Database Connector performs the LoadDatabaseSchema:

The Nuget package code is here: https://github.com/DataJuggler/DataJuggler.Net

Or Dot Net Core version: https://github.com/DataJuggler/DataJuggler.Net.Core

The above Nuget package is also used in DB Compare to compare the database schema of two SQL Server databases and report any differences:

https://github.com/DataJuggler/DBCompare

Hopefully no one considers this spam as I do give it all away for free and I am only trying to answer the question of 'How do you read the database schema', so in this case he can determine if a column name is a real column name. I think I interpreted this question correctly, if not explain what you need better please.

I am getting data from Excel sheet not from SQL Server. Plus we have no restriction for the excel sheet hence we have to develop a strong validation — JB's, Sep 27 '19 at 14:43

score 0 · Answer 2 · answered Sep 27 '19 at 15:13

if you're able to use Excel.Application interop, then selecting and checking first row will show if column(s) missing a name, for example:

    public  void CheckExcelColumnName(string sourceFile)
    {
            if (!System.IO.File.Exists(sourceFile))
            {
            throw new System.ArgumentException("source file is missing");
            }

        try
        {
            Type excelType = Type.GetTypeFromProgID("Excel.Application");
            dynamic excel = Activator.CreateInstance(excelType);

            if (excel == null)
            {
                throw new System.Exception("Excel is missing");
            }

            excel.Workbooks.Open(sourceFile);
            try
            {
                dynamic workbook = excel.Workbooks[1];
                dynamic worksheets = workbook.Sheets;
                dynamic worksheet = worksheets[1];
                dynamic cells = worksheet.Cells;

                int xlCellTypeLastCell = 11;
                dynamic lastfilledcell = cells.SpecialCells(xlCellTypeLastCell, Type.Missing);
                int lastcolumn = lastfilledcell.Column;

                dynamic range = worksheet.Range("A1", "A1");//
                var v = range.EntireRow.Value;
                for(int c=1;c<= lastcolumn;c++)
                {
                    if ( v[1,c]==null)
                    {
                        Console.WriteLine("excel sheet is missing column name for column #" + c);
                        break;
                    }
                }

                workbook.Close();

                lastfilledcell = null;
                cells = null;
                range = null;
                worksheet = null;
                worksheets = null;
                workbook = null;

            }
            catch {; }

            excel.Quit();
            //try { System.Runtime.InteropServices.Marshal.ReleaseComObject(excel); } catch {; }
            try { System.Runtime.InteropServices.Marshal.FinalReleaseComObject(excel); } catch {; }
            excel = null;

        }
        catch(Exception ex)
        {

        }
    }

score 0 · Answer 3 · answered Sep 27 '19 at 17:30

0

Try to use EPPLUS, You can solve your issue by using Epplus very easily.

answered Sep 27 '19 at 17:30

Shahnawaz Ahmad

11
3

Check if datatable column name is default column name C#

3 Answers3