How can i get size of column of database using datatable in c#

Question

I want to get max size of any column of a table using datatable in C#. Example is that if i have a table in database named dept and its attributes are NAME and ADDRESS and i have set size of NAME as varchar(50) and Address as varchar(30).

Now i want to get 50 and 30.....

i have written this code

DataSet ds1 = new DataSet();
    DataSet ds = new DataSet();
        string query = Form1.query1Pass;

        SqlCommand cmd = new SqlCommand(query.ToString(), con);
        this.tablename1 = query.Substring(query.LastIndexOf("from")+4); ;
        cmd.ExecuteNonQuery();

        SqlDataAdapter adp = new SqlDataAdapter(cmd);


        adp.Fill(this.ds);
          string query2 = Form1.query2Pass;
        cmd = new SqlCommand(query2, con);
        adp = new SqlDataAdapter(cmd);

        adp.Fill(this.ds1);

            StringBuilder datatype2 = new StringBuilder();
            this.datatype2.Append("Data Type Conflict: ");
            this.datetimeCon.Append("DateTime Format Conflict: ");
            DataTable table1 = this.ds.Tables[0];
            DataTable table2 = this.ds1.Tables[0]; ;
            DataColumnCollection col1 = table1.Columns;
            DataColumnCollection col2 = table2.Columns;
            foreach (DataColumn ds in col1) {
                foreach (DataColumn ds2 in col2) {
                    if (ds.ColumnName.Equals(ds2.ColumnName)) {
                        if (!ds.DataType.Equals(ds2.DataType)) {
                            this.datatype2.Append(ds.ColumnName + "<" + ds.DataType + ">, " + ds2.ColumnName + "<" + ds2.DataType + ">,");
                        }
                        else 
                            if (ds.MaxLength!=ds2.MaxLength) {
                            maxLength.Append(ds.ColumnName+"<"+ds.MaxLength+">, "+ds2.ColumnName+"<"+ds2.MaxLength+">,");
                        }

                    }
                }
            }

MaxLength return each time -1... this is problem

Mujtaba
– Mujtaba

2012-07-06 11:15:49 +00:00
Commented Jul 6, 2012 at 11:15 — Mujtaba
– Mujtaba, Commented Jul 6, 2012 at 11:15
Could you show how you have filled the Datasets ds and ds1?

Steve
– Steve

2012-07-06 11:22:29 +00:00
Commented Jul 6, 2012 at 11:22 — Steve
– Steve, Commented Jul 6, 2012 at 11:22

Tim Schmelter · Accepted Answer · 2014-10-13 07:15:49Z

1

I assume that the columns are not Text columns. The MaxLength property is only set on columns with DataType=String. MSDN:

The MaxLength property is ignored for non-text columns. A ArgumentException exception is raised if you assign MaxLength to a non-string column.

By the way, you might want to use a LINQ approach, but that's a matter of taste:

var t1Cols = table1.Columns.Cast<DataColumn>();
var t2Cols = table2.Columns.Cast<DataColumn>();

var diffTypes =  
    from t1Col in t1Cols
    join t2Col in t2Cols on t1Col.ColumnName equals t2Col.ColumnName
    where t1Col.DataType != t2Col.DataType
    select string.Format("{0}<{1}>,{2}<{3}>",
        t1Col.ColumnName, t1Col.DataType, t2Col.ColumnName, t2Col.DataType);
var diffMaxLength = 
    from t1Col in t1Cols
    join t2Col in t2Cols on t1Col.ColumnName equals t2Col.ColumnName
    where t1Col.MaxLength != t2Col.MaxLength
    select string.Format("{0}<{1}>,{2}<{3}>",
        t1Col.ColumnName, t1Col.MaxLength, t2Col.ColumnName, t2Col.MaxLength);

Console.WriteLine("diff. Types: " + string.Join(", ", diffTypes));
Console.WriteLine("diff. max Length: " + string.Join(", ", diffMaxLength));

edited Oct 13, 2014 at 7:15

answered Jul 6, 2012 at 12:18

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mujtaba Over a year ago

max length is still at problem

Collectives™ on Stack Overflow

How can i get size of column of database using datatable in c#

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related