how to change the structure of a datatable baesd on another datatable in C#

Question

User1094269964 posted

Hi Friends,

We have excel upload and download functionality.

Lets say in a database we have oracle table lets call it as "tblProduct" and it has some 25 columns of different datatypes.

When the user clicks on download we will just get metadata of the table and create a excel file and user will get the excel file.

User will fill the excel file and upload the file. In the C# code we will read the excel and put those values in  Datatable lets say "tblExcel".

And in this "tblExcel" all the column will be of type string only.

so we want to change the datatype of columns in the "tblExcel" to datatype of columns in the "tblProduct" so that the structure will match.

How to convert the "tblExcel" columns types based on "tblProduct"

Answer 1

User475983607 posted

How to convert the "tblExcel" columns types based on "tblProduct"

Use standard PL/SQL cast syntax.

https://www.techonthenet.com/oracle/functions/to_number.php.

Answer 2

User1094269964 posted

its a complete C# problem statement

class Program
    {
        static void Main(string[] args)
        {

            DataTable originalSchema = new DataTable();
            originalSchema.Columns.Add("Dosage", typeof(int));
            originalSchema.Columns.Add("Drug", typeof(string));
            originalSchema.Columns.Add("Patient", typeof(string));
            originalSchema.Columns.Add("Date", typeof(DateTime));



            DataTable someDatareadFromExcel = new DataTable();
            someDatareadFromExcel.Columns.Add("Dosage", typeof(string));
            someDatareadFromExcel.Columns.Add("Drug", typeof(string));
            someDatareadFromExcel.Columns.Add("Patient", typeof(string));
            someDatareadFromExcel.Columns.Add("Date", typeof(string));
            someDatareadFromExcel.Rows.Add("25", "Indocin", "David", "30-04-2020 18:53:00");
            someDatareadFromExcel.Rows.Add("50", "Enebrel", "Sam", "30-05-2020 18:53:00");
            someDatareadFromExcel.Rows.Add("10", "Hydralazine", "Christoff", "30-06-2020 18:53:00");
            someDatareadFromExcel.Rows.Add("21", "Combivent", "Janet", "30-03-2020 18:53:00");
            someDatareadFromExcel.Rows.Add("100", "Dilantin", "Melanie", "30-07-2020 18:53:00");

            /**
           Here I want to move the data from someDatareadFromExcel datatable to originalSchema datatable.
           The challenge here is the all the column in someDatareadFromExcel are string but we want to move all rows to originalSchema table which has different column datatype
           Need a generic solution here i put some sample table with data. But we may get datatable with more number of columns withdifferent datatype for originalSchema.
           But the all the columns in someDatareadFromExcel are string type only
           **/

        }
    }

Answer 3

User475983607 posted

Here I want to move the data from someDatareadFromExcel datatable to originalSchema datatable. The challenge here is the all the column in someDatareadFromExcel are string but we want to move all rows to originalSchema table which has different column datatype Need a generic solution here i put some sample table with data. But we may get datatable with more number of columns withdifferent datatype for originalSchema. But the all the columns in someDatareadFromExcel are string type only

Use PL/SQL to query the column data types on demand.  Of course, you need to know the table and the column.  

SELECT DATA_TYPE 
FROM user_tab_columns 
WHERE table_name = 'THE_TABLE' 
    AND column_name = 'THE_COLUMN';

Answer 4

User1094269964 posted

we need to do this in C#.

I created some logic but but not sure how far it will perform better if we have more number of columns and rows. please have look

 class Program
    {
        static void Main(string[] args)
        {
            
            DataTable originalSchema = new DataTable();
            originalSchema.Columns.Add("Dosage", typeof(int));
            originalSchema.Columns.Add("Drug", typeof(string));
            originalSchema.Columns.Add("Patient", typeof(string));
            originalSchema.Columns.Add("Date", typeof(DateTime));



            DataTable someDatareadFromExcel = new DataTable();
            someDatareadFromExcel.Columns.Add("Dosage", typeof(string));
            someDatareadFromExcel.Columns.Add("Drug", typeof(string));
            someDatareadFromExcel.Columns.Add("Patient", typeof(string));
            someDatareadFromExcel.Columns.Add("Date", typeof(string));
            someDatareadFromExcel.Rows.Add("25", "Indocin", "David", "30-04-2020 18:53:00");
            someDatareadFromExcel.Rows.Add("50", "Enebrel", "Sam", "30-05-2020 18:53:00");
            someDatareadFromExcel.Rows.Add("10", "Hydralazine", "Christoff", "30-06-2020 18:53:00");
            someDatareadFromExcel.Rows.Add("21", "Combivent", "Janet", "30-03-2020 18:53:00");
            someDatareadFromExcel.Rows.Add("100", "Dilantin", "Melanie", "30-07-2020 18:53:00");

            var original = originalSchema.Columns.Cast<DataColumn>().ToList();
            var data = someDatareadFromExcel.Columns.Cast<DataColumn>().ToList();

            List<DataColumn> result = original.Where(item => !data.Any(e => item.ColumnName == e.ColumnName & item.DataType ==e.DataType)).ToList();


            foreach (DataRow dr in someDatareadFromExcel.Rows)
            {
                foreach (DataColumn col in result)
                {
                   if(col.DataType.Name==DbType.Int32.ToString())
                    {                       
                        dr[col.ColumnName] = Convert.ToInt32(dr[col.ColumnName]);
                        
                      
                    }
                    else if (col.DataType.Name == DbType.DateTime.ToString())
                    {
                        dr[col.ColumnName] = Convert.ToDateTime(dr[col.ColumnName]);

                     
                    }

                }
                originalSchema.ImportRow(dr);


            }



        }
    }

    public static class IntegerExtensions
    {
        public static int ParseInt(this string value, int defaultIntValue = 0)
        {
            int parsedInt;
            if (int.TryParse(value, out parsedInt))
            {
                return parsedInt;
            }

            return defaultIntValue;
        }

        public static int? ParseNullableInt(this string value)
        {
            if (string.IsNullOrEmpty(value))
                return null;

            return value.ParseInt();
        }
    }

Answer 5

User475983607 posted

I think it is important to realize, importing data from a file or stream has been around for a very very long time.  We're talking the 70s.  The general rule of thumb is having a well-known data definition that everyone agrees to.  Although data formats have been added over the years, fixed width, delimited, XML, JSON, etc, there remains a need to define the data types.  In databases it's done with DDL, Data Definition Language.

Back you your question.  You asked how to dynamically get the types.  Well, the types are in the database - the DDL.  You can get to the column types by executing a query similar to the one above.  Clearly you are able to execute an ADO.NET query so what's the difference?  

Perhaps you can drop the dynamic requirement and plainly define the data interface.  C# is a strongly typed language.  Craft a class that matches the table definition.