CODEWITHSUNDEEP
c#sql
user1495962
user1495962

Reputation: 95

Using GetSchemaTable() to retrieve only column names

Is it possible to use GetSchemaTable() to retrieve only column names?

I have been trying to retrieve Column names (only) using this method, is it possible.

  DataTable table = myReader.GetSchemaTable();

  foreach (DataRow myField in table.Rows)
  {
      foreach (DataColumn myProperty in table.Columns)
      {
          fileconnectiongrid.Rows.Add(myProperty.ColumnName + " = " 
                            + myField[myProperty].ToString());
      }
  }

This code retrieves a lot of table data unwanted, I only need a list containing column names!:

Upvotes: 9

Views: 29850

Answers (5)

Romita Dinda
Romita Dinda

Reputation: 81

//Retrieve column schema into a DataTable.
schemaTable = reader.GetSchemaTable();

int index = schemaTable.Columns.IndexOf("ColumnName");
DataColumn columnName = schemaTable.Columns[index];

//For each field in the table...
foreach (DataRow myField in schemaTable.Rows)
{
    String columnNameValue = myField[columnName].ToString();
    Console.WriteLine("ColumnName " + columnNameValue);
}

Upvotes: 0

schlebe
schlebe

Reputation: 3716

I use same technics to add MAX-STRING-LENGTH constraint on custom TextBox in my VB.Net program.

I use a SQL SELECT command to get 4 column's values

    SELECT code_pays
          ,nom
          ,code_pays_short
          ,default_devise
      FROM pays
      ORDER BY nom

I use the result returned by an IDataReader object to fill a DataGridView.

And finally, I display each row's field in a Panel that contains 4 TextBox.

To avoid that SQL UPDATE command used to save some record's changes done in TextBox return error message due to column value too long, I have added a property in custom Textbox to inform directly user that value's size is overlapped.

Here is my Form

enter image description here

Here is VB.Net code used to initialize MaxStringLength properties

    Private Sub PushColumnConstraints(dr As IDataReader)
        Dim tb As DataTable = dr.GetSchemaTable()
    
        Dim nColIndex As Integer = -1
    
        For Each col As DataColumn In tb.Columns
            If col.ColumnName = "ColumnSize" Then
                nColIndex = col.Ordinal
                Exit For
            End If
        Next
    
        If nColIndex < 0 Then
            oT.ThrowException("[ColumnSize] columns's index not found !")
            Exit Sub
        End If
    
        txtCodePays.MaxStringLength = tb.Rows(0).Item(nColIndex)
        txtPays.MaxStringLength = tb.Rows(1).Item(nColIndex)
        txtShortCodePays.MaxStringLength = tb.Rows(2).Item(nColIndex)
        txtDefaultDevise.MaxStringLength = tb.Rows(3).Item(nColIndex)
    End Sub

In For loop, program search index of field contained in ColumnSize column's value.

MaxStringLength property is assigned using following syntax

tb.Rows(%TEXT-BOX-INDEX%).Item(nColIndex)

.Rows(%TEXT-BOX-INDEX%) is used to identify column's metadata in SQL SELECT !

.Item(nColIndex) is used to get a specific column's metadata value

Item(n) can return a String or an Integer but VB.Net do implicit conversion when necessary.

This line of code can also be written shortly

tb.Rows(%TEXT-BOX-INDEX%)(nColIndex)
tb(%TEXT-BOX-INDEX%)(nColIndex)

but it is not readable !

Caution: MaxStringLength is a custom property. It is not part of normal TextBox.

In print screen above, you can see that program indicates to user that length is too big for Code Pays (3 lettres) TextBox. Error's message is displayed in StatusBar at bottom of Form.

This information is displayed before clicking on SAVE button that generates an SQL UPDATE command.

Code used that call PushColumnConstraints method is following

    Public Sub FillPanel()
    
        SQL =
    <sql-select>
    
        SELECT code_pays
              ,nom
              ,code_pays_short
              ,default_devise
          FROM pays
          ORDER BY nom
            
    </sql-select>
    
        Dim cmd As New NpgsqlCommand(SQL, cn)
        Dim dr As NpgsqlDataReader
    
        Try
            dr = cmd.ExecuteReader()
        Catch ex As Exception
            ThrowException(ex)
        End Try
    
        Call PushColumnConstraints(dr)

Upvotes: 0

Nathan Smith
Nathan Smith

Reputation: 8347

This will give you all column names, you can place them in a string[] and do with them what you like. 

foreach(var columnName in DataTable.Columns)
{
  Console.WriteLine(columnName);
}

Upvotes: 0

code4life
code4life

Reputation: 15794

Change your code to below if all you want is to display the column names. Your original code was trying to not only display column names, but also trying to display the actual data values as well.

DataTable table = myReader.GetSchemaTable();

foreach (DataRow myField in table.Rows)
{
    foreach (DataColumn myProperty in table.Columns)
    {
        fileconnectiongrid.Rows.Add(myProperty.ToString());
    }
}

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460068

You need to use ExecuteReader(CommandBehavior.SchemaOnly)):

DataTable schema = null;
using (var con = new SqlConnection(connection))
{
    using (var schemaCommand = new SqlCommand("SELECT * FROM table", con))
    {
        con.Open();
        using (var reader = schemaCommand.ExecuteReader(CommandBehavior.SchemaOnly))
        {
            schema = reader.GetSchemaTable();
        }
    }
}

SchemaOnly:

The query returns column information only. When using SchemaOnly, the .NET Framework Data Provider for SQL Server precedes the statement being executed with SET FMTONLY ON.

The column name is in the first column of every row. I don't think that it's possible to omit the other column informations like ColumnOrdinal,ColumnSize,NumericPrecision and so on since you cannot use reader.GetString but only reader.GetSchemaTable in this case.

But your loop is incorrect if you only want the column names:

foreach (DataRow col in schema.Rows)
{
    Console.WriteLine("ColumnName={0}", col.Field<String>("ColumnName"));
}

Upvotes: 25

Related Questions