North-wind DB Get column properties from schema

Question

Im using the following code to get the columns data from DB tables When I check in Debug mode I saw that Im getting in every iteration the right table name but for all the tables I Get the same properties which are the first table properties

what Im missing here ?

    foreach (System.Data.DataRow row in schema.Rows)
        {

        currentTableName = row["TABLE_NAME"].ToString();
        currentTableName = currentTableName = currentTableName.Replace(" ", "");
        currentTableName = currentTableName.Replace("[", "");
        currentTableName = currentTableName.Replace("]", "");

        command.CommandText = selectQuery.Replace("@tableName", row["TABLE_NAME"].ToString());
        ad.FillSchema(ds, SchemaType.Mapped, row["TABLE_NAME"].ToString());

        foreach (DataColumn dc in ds.Tables[0].Columns)
            {
            var typeName = dc.DataType.Name;
            var propName = dc.ColumnName.Replace(dc.ColumnName[0].ToString(), dc.ColumnName[0].ToString().ToLower());
            propName = propName.Replace(" ", "");


            }

Answer 1

Problem : You are always using same table (first table) using below statement:

 foreach (DataColumn dc in ds.Tables[0].Columns)

in the above statement Tables[0] always refer to first table in DataSet.

Solution 1: you have to change the index value from 0 to current table index.

you can keep an index variable and increment for each iteration.

Try This:

           int index=0;

           foreach (System.Data.DataRow row in schema.Rows)
            {        
            currentTableName = row["TABLE_NAME"].ToString();
            currentTableName = currentTableName = currentTableName.Replace(" ", "");
            currentTableName = currentTableName.Replace("[", "");
            currentTableName = currentTableName.Replace("]", "");

            command.CommandText = selectQuery.Replace("@tableName", row["TABLE_NAME"].ToString());
            ad.FillSchema(ds, SchemaType.Mapped, row["TABLE_NAME"].ToString());

            foreach (DataColumn dc in ds.Tables[index].Columns)
            {
              var typeName = dc.DataType.Name;
              var propName = dc.ColumnName.Replace(dc.ColumnName[0].ToString(), dc.ColumnName[0].ToString().ToLower());
              propName = propName.Replace(" ", "");           
             }
             index++;
            }

Solution 2: you can provide new TableName for-each iteration

Try This:

        foreach (System.Data.DataRow row in schema.Rows)
        {

        currentTableName = row["TABLE_NAME"].ToString();
        currentTableName = currentTableName = currentTableName.Replace(" ", "");
        currentTableName = currentTableName.Replace("[", "");
        currentTableName = currentTableName.Replace("]", "");

        command.CommandText = selectQuery.Replace("@tableName", row["TABLE_NAME"].ToString());
        ad.FillSchema(ds, SchemaType.Mapped, row["TABLE_NAME"].ToString());

        foreach (DataColumn dc in ds.Tables[row["TABLE_NAME"].ToString()].Columns)
            {
            var typeName = dc.DataType.Name;
            var propName = dc.ColumnName.Replace(dc.ColumnName[0].ToString(), dc.ColumnName[0].ToString().ToLower());
            propName = propName.Replace(" ", "");
           }

            }