How To Know DataRow Field DataType

Question

Hello Experts,

I am trying to paste data in a datagridview.

following is the code. 

        public static void Add2Grid(DataGridView dgData)
        {
            try
            {
                //Adam O'Neil's Development Blog
                //Copy paste from Excel into a DataTable
                //http://www.seesharpdot.net/?p=221
                //16.6.2020 Modied to copy to datagridview , actually it was for insert in table copy of :save2Datatable
                DataTable sourcetable = (DataTable)dgData.DataSource;
                DataTable tbl = sourcetable.Clone();
                tbl.PrimaryKey = null;
                if (tbl.Columns.Contains("ui"))
                    tbl.Columns.Remove("ui");
                dgData.DataSource = tbl;
                string s = Clipboard.GetText();
                char[] rowSplitter = { '\r', '\n' };
                string[] lines = s.Split(rowSplitter, StringSplitOptions.RemoveEmptyEntries);

                List<string> data = new List<string>(s.Split(rowSplitter, StringSplitOptions.RemoveEmptyEntries));
                bool firstRow = true;

                //!st Rowheader is read 
                string[] rowData = data[0].Split(new char[] { '\r', '\x09' });

                //Get Index of a column searched by name in a datarow
                //then each field location (ordinals) ar obtained
                int[] fldOrdinals = new int[rowData.Length];
                DataRow newRow = tbl.NewRow();

                //int ScheduleidNdx=newRow.Table.Columns["Scheduleid"].Ordinal	
                //Find column headers being copied withh their column postion (ordinal) where
                // values are to be copied
                for (int i = 0; i < rowData.Length; i++)
                {
                    try
                    {
                        rowData[i] = rowData[i].Trim();
                        fldOrdinals[i] = newRow.Table.Columns[rowData[i]].Ordinal;
                    }
                    catch (Exception)
                    {
                        MessageBox.Show(rowData[i] + ":Column not in Table:" + newRow.Table.TableName + "\nCorrect it");
                        return;
                    }
                }

                //Delete New Blank row at the bottom of copy source if its IsNullOrWhiteSpace
                if (data.Count > 0 && string.IsNullOrWhiteSpace(data[data.Count - 1]))
                {
                    data.RemoveAt(data.Count - 1);
                }

                //data copy from remaining rows as per their ordinals (index)
                int j = -1;
                string failedrows = "";
                foreach (string iterationRow in data)
                {
                    if (firstRow)
                    {
                        firstRow = false;
                        continue;
                    }
                    j++;
                    string row = iterationRow;
                    if (row.EndsWith("\r"))
                    {
                        row = row.Substring(0, row.Length - "\r".Length);
                    }
                    //rowData = row.Split(new char[] { '\r', '\x09' });
                    rowData = row.Split('\t');
                    if (rowData.Length != fldOrdinals.Length)
                    {
                        failedrows += "Row:" + j + " Import Skiped\n";
                        continue;
                    }
                    newRow = tbl.NewRow();
                    for (int i = 0; i < rowData.Length; i++)
                    {
                        if (i >= tbl.Columns.Count)
                            break;
                        if (rowData[i] != null)
                        {
                            try
                            {
                                newRow[fldOrdinals[i]] = rowData[i];
                            }
                            catch (Exception ex)
                            {
                                failedrows += j + " Row " + newRow.Table.Columns[fldOrdinals[i]].ColumnName + ":Skipped";
                            }
                        }
                    }
                    tbl.Rows.Add(newRow);
                    //}
                }
                if (!string.IsNullOrWhiteSpace(failedrows))
                    MessageBox.Show(failedrows);
            }
            catch (FormatException)
            {
                MessageBox.Show("The data you pasted is in the wrong format for the cell");
            }


        }

some time i get error on following line, for example if newRow fields datatype is int and pasted rowData[i] is string, it gives error and value do not get applied to newRow[fldOrdinals[i]].

how can i convert runtime rowData[i] value to newRow[fldOrdinal[i]] datatype ?

newRow[fldOrdinals[i]] = rowData[i];

Answer 1

Hi Sushhil,

You can find the DataType of the columns like this for newRow:

newRow.Table.Columns[fldOrdinals[i]].DataType

and the same for rowData:

rowData.Table.Columns[i].DataType

Is that what you needed to know?

---

~~Bonnie DeWitt [C# MVP]

http://geek-goddess-bonnie.blogspot.com

Answer 2

Thanks Bonnie DeWitt,

i am getting error 

ystem.ArgumentException: Input string was not in a correct format.Couldn't store <> in gl_code Column.  Expected type is Int32. ---> System.FormatException: Input string was not in a correct format.
   at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)
   at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info)
   at System.String.System.IConvertible.ToInt32(IFormatProvider provider)
   at System.Data.Common.Int32Storage.Set(Int32 record, Object value)
   at System.Data.DataColumn.set_Item(Int32 record, Object value)
   --- End of inner exception stack trace ---
   at System.Data.DataColumn.set_Item(Int32 record, Object value)
   at System.Data.DataRow.set_Item(DataColumn column, Object value)
   at Kings.ERP.PasteClipboard.Add2Grid(DataGridView dgData) in E:\19vs\Kings ERP\PasteClipBoard.cs:line 249

if i ttry

	Name	Value	Type
▶	Convert.ToInt32(rowData[i])	'Convert.ToInt32(rowData[i])' threw an exception of type 'System.FormatException'	int {System.FormatException}

i am getting error

how i can solve this

Answer 3

Hi Sushil Agarwal,

Thank you for posting here.

Datatable can automatically perform simple data type conversion, so I think there may be some errors in your data.

Please use breakpoint to check "string[] rowData".

If you want to modify the data type at runtime, you can take a look at Convert.ChangeType Method.

Best Regards,

Timon

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 4

Thanks Timon,

i am getting error when DataTable field type is int and value being assigned is "" empty string.

as you see above in above error post screen the datatable field gl_code in table is int, when bank passbook data copied from excel sheet haing gl_code rowheader but few cells are not having values ther are read as emty string in run time and i get that error.

how can i correctly type convert right side field values to left side tabel field at run time, 

newRow[fldOrdinals[i]] = rowData[i];

for the time being i modified program 

//string clmdt = newRow.Table.Columns[fldOrdinals[i]].DataType.ToString(),
                                //rowData.GetType();
                                if (rowData[i] != null
                                    && !string.IsNullOrWhiteSpace(rowData[i].ToString()))
                                {
                                        newRow[fldOrdinals[i]] = rowData[i];
                                }

now there is no error. assigning empty sting really had no meaning. 

but for academic purpose i want to know can conversions at runtime of data types be done ? and how to do that ?

Answer 5

So, by checking the DataType of the row you now know the data type. The next code can be a switch statement (e.g. it will be its own method that will try to convert the string rowData into desired type)

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

My TechNet articles

Answer 6

Hi Sushhil,

I started writing this earlier today, but I got side-tracked all day by work. Unfortunately, work *always* gets in the way of fun!  ;0)

Anyway, you need a separate method that can take care of the conversions you need to make, based on the DataTypes of both sides. Something like this:

public void ConvertAndSet(ref DataRow SetTo, DataRow ConvertFrom, int SetToColumn = 0, int ConvertFromColumn = 0)
{
    Type typeTo = SetTo.Table.Columns[SetToColumn].DataType;
    Type typeFrom = ConvertFrom.Table.Columns[ConvertFromColumn].DataType;

    // If they're the same Type, just set with a simple = and you're done!
    if (typeTo == typeFrom)
        SetTo[SetToColumn] = ConvertFrom[ConvertFromColumn];
    else // otherwise, use a switch
        switch (true)
        {
            case bool _ when typeTo == typeof(int):
                int i;
                int.TryParse(ConvertFrom[ConvertFromColumn].ToString(), out i);
                SetTo[SetToColumn] = i;
                break;

            case bool _ when typeTo == typeof(long):
                long l;
                long.TryParse(ConvertFrom[ConvertFromColumn].ToString(), out l);
                SetTo[SetToColumn] = i;
                break;

            case bool _ when typeTo == typeof(decimal):
                decimal d;
                decimal.TryParse(ConvertFrom[ConvertFromColumn].ToString(), out d);
                SetTo[SetToColumn] = d;
                break;

            // Add other Types as needed

            default:
                // Display an error message here, to let you know you need to add another Type
                break;
        }
}

And you would call it like this:

this.ConvertAndSet(ref newRow, rowData, fldOrdinals[i], i);

I haven't tested this yet, but I'm about to do that now. If it doesn't work the way I expect it to, I'll let you know.

---

~~Bonnie DeWitt [C# MVP]

http://geek-goddess-bonnie.blogspot.com

Answer 7

There is a typo in the typeof(long) case where I have = i; when it should have been =l;  (a copy/paste missed).

But, there is more of a problem than that ... the compiler doesn't like passing that newRow as a reference, so I'm going to have to work on tweaking this a bit more. Sorry about that. But, unfortunately, it's dinner time right now, so I won't have time to do that right now.

You or someone else are welcome to play around with it ... otherwise, I'll see what I can do later.

---

~~Bonnie DeWitt [C# MVP]

http://geek-goddess-bonnie.blogspot.com

Answer 8

Just get rid of the ref (both in the method and when you call the method) and it will work just fine. I don't know why I used the ref to begin with. Oh well.

The int.TryParse() or long.TryParse() methods are picky though ... they won't convert from a decimal.ToString(). I'll leave it to you to work this out, it's time for bed right now.  *yawn*  I think you'll have to use the Math functions to truncate or round decimal values.

---

~~Bonnie DeWitt [C# MVP]

http://geek-goddess-bonnie.blogspot.com