How do I correctly assign a datatype to a datatable column?

Question

0.00/5 (No votes)

See more:

I have a datatable to which I add many columns with differing datatypes.
When, after setting up the datatable I check the datatype of columns they are all string.
My code is:

public partial class Form1 : Form
    {
        int i; 
        string SelectString;
        string MyConnection = "sssss;database=Workbench;UID=sa;password=xxxx";
        string ParentNumber;
        int dtcounter;
        int dt1counter;

        DataTable dt = new DataTable("MyJobTransactions");
        DataRow NewRow;
        DataRow row;
        DataColumn dc1 = new DataColumn();
        DataColumn dc2 = new DataColumn();
        DataColumn dc3 = new DataColumn();
        DataColumn dc4 = new DataColumn();
        DataColumn dc5 = new DataColumn();
        DataColumn dc6 = new DataColumn();
        DataColumn dc7 = new DataColumn();
        DataColumn dc8 = new DataColumn();
        DataColumn dc9 = new DataColumn();
        DataColumn dc10 = new DataColumn();
	etc etc....
        DataColumn dc108 = new DataColumn();
		
		private void Populate_Columns()
		{
		dc1.ColumnName = "JobTranID";
		dc1.Caption = "JobTranID";
		dc1.DataType = System.Type.GetType("System.Int32");
		dc2.ColumnName = "JobCode";
		dc2.Caption = "JobCode";
		dc2.DataType= System.Type.GetType("System.String");
		dc3.ColumnName = "ActivityCode";
		dc3.Caption = "ActivityCode";
		dc3.DataType = System.Type.GetType("System.String");
		dc4.ColumnName = "WorkCentreCode";
		dc4.Caption = "WorkCentreCode";
		dc4.DataType = System.Type.GetType("System.String");
		dc5.ColumnName = "TranDate";
		dc5.Caption = "Transaction Date";
		dc5.DataType= System.Type.GetType("System.DateTime");
		dc6.ColumnName = "YearNo";
		dc6.Caption = "Year No";
		dc6.DataType = System.Type.GetType("System.Int32");
		dc7.ColumnName = "PeriodNo";
		dc7.Caption = "Period No";
		dc7.DataType = System.Type.GetType("System.Int32");
		dc8.ColumnName = "TranType";
		dc8.Caption = "Transaction Type";
		dc8.DataType = System.Type.GetType("System.String");
		dc9.ColumnName = "SiteID";
		dc9.Caption = "Site ID";
		dc9.DataType = System.Type.GetType("System.Int32");
		dc10.ColumnName = "BatchNo";
		dc10.Caption = "Batch No";
		dc10.DataType = System.Type.GetType("System.Int32");
		....
		dc107.ColumnName ="JobManagerApprovedDate";
		dc107.Caption = "Job ManagerDate"
		;dc107.DataType = System.Type.GetType("System.DateTime");
		}
		public Form1()
		{
		     InitializeComponent();
		}
		     private void Form1_Load(object sender, EventArgs e)
		     {
		     Populate_Columns();
		     dtcounter = 1;
		     string dcnumber;
		     while (dtcounter < 108)
		     {
		         dcnumber = "dc" + dtcounter.ToString();
		         dt.Columns.Add(dcnumber);
		         dtcounter += 1;
		     }
		     MessageBox.Show(dt.Columns[0].DataType.ToString(),"",MessageBoxButtons.OK);
            		
					.......
		}

The final messagebox tells me that dt.Columns[0] is of type string, rather than Int32.

How do I correctly assign a datatype?

What I have tried:

I've tried all the alternatives including typeof().
I've tried listing the type of all dt.Columns - they are all string.

Posted 14-Nov-20 16:51pm

ormonds

Updated 14-Nov-20 20:01pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2020-11-14T20:01:00

What you appear to be trying to do is generate the name of a variable and then use that variable's content to add a column to a DB:

C#

while (dtcounter < 108)
{
    dcnumber = "dc" + dtcounter.ToString();
    dt.Columns.Add(dcnumber);
    dtcounter += 1;
}

That won't work: you cannot access variables via a string (without using Reflection, and that gets pretty hairy for a beginner like you).

So what you add to the dataTable as a column is a string "dc1", "dc2", etc., instead of the column you are trying to add.
If you must add columns like that (and I don't recommend it) create an array of DataColumns:

C#

private DataColumn[] columns = new DataColumn[108];

And add a new DataColumn object instance to each:

private void Populate_Columns()
    {
    DataColumn dc = new DataColumn();
    dc.ColumnName = "JobTranID";
    dc.Caption = "JobTranID";
    dc.DataType = System.Type.GetType("System.Int32");
    columns[0] = dc;
    DataColumn dc = new DataColumn();
    dc.ColumnName = "JobCode";
    dc.Caption = "JobCode";
    dc.DataType= System.Type.GetType("System.String");
    columns[1] = dc;
    dc = new DataColumn();
    dc.ColumnName = "ActivityCode";
    ...

Then you can access them via your index.

But to be honest, that's a pretty poor way to do it all. I'd use a configuration file and load the DataTable directly from that: CSV, XML, JSON - any of those would do.