CODEWITHSUNDEEP
c#datatableado.net
Jim Brad
Jim Brad

Reputation: 393

select certain columns of a data table

I have a datatable and would like to know if its possible for me to select certain columns and input the data on a table. the columns are set out as below

|col1 |col2 |col3|col4 |col5 |col6|col7 |col8 |col9 |col10 |col11 |

I want to select column col1, col2 col6, col7,col3. and dispay the data in a gridview of the rows within the datatable.. currently the code that i am using is below and onmly selects certain data. I am not selecting the data from sql its data being selected from another excel which is stored in a datatable.. but i am in need of the other columns in another area as well.. this data is being written into a table in word

 for (int i = 1; i < table.Rows.Count; i++)
            {
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    if (j == 0)
                    {
                        val = filteredData.Rows[row][col].ToString();
                    }
                    else
                    {
                        val = filteredData.Rows[row][col].ToString();

                        if (val == "-" || val == "")
                        {
                            val = filteredData.Rows[row][col].ToString();

                        }
                        else
                        {
                            val = Convert.ToString(Math.Round(Convert.ToDouble(filteredData.Rows[row][col]), MidpointRounding.AwayFromZero));

                        }
                    }

                    table[j, i].TextFrame.Text = val;
                    col++;
                }

Upvotes: 32

Views: 155952

Answers (6)

MrFox
MrFox

Reputation: 5116

First store the table in a view, then select columns from that view into a new table.

// Create a table with abitrary columns for use with the example
System.Data.DataTable table = new System.Data.DataTable();
for (int i = 1; i <= 11; i++)
    table.Columns.Add("col" + i.ToString());

// Load the table with contrived data
for (int i = 0; i < 100; i++)
{
    System.Data.DataRow row = table.NewRow();
    for (int j = 0; j < 11; j++)
        row[j] = i.ToString() + ", " + j.ToString();
    table.Rows.Add(row);
}

// Create the DataView of the DataTable
System.Data.DataView view = new System.Data.DataView(table);
// Create a new DataTable from the DataView with just the columns desired - and in the order desired
System.Data.DataTable selected = view.ToTable("Selected", false, "col1", "col2", "col6", "col7", "col3");

Used the sample data to test this method I found: Create ADO.NET DataView showing only selected Columns

Upvotes: 37

pawciu
pawciu

Reputation: 945

Here's working example with anonymous output record, if you have any questions place a comment below:                    

public partial class Form1 : Form
{
    DataTable table;
    public Form1()
    {
        InitializeComponent();
        #region TestData
        table = new DataTable();
        table.Clear();
        for (int i = 1; i < 12; ++i)
            table.Columns.Add("Col" + i);
        for (int rowIndex = 0; rowIndex < 5; ++rowIndex)
        {
            DataRow row = table.NewRow();
            for (int i = 0; i < table.Columns.Count; ++i)
                row[i] = String.Format("row:{0},col:{1}", rowIndex, i);
            table.Rows.Add(row);
        }
        #endregion
        bind();
    }

    public void bind()
    {
        var filtered = from t in table.AsEnumerable()
                       select new
                       {
                           col1 = t.Field<string>(0),//column of index 0 = "Col1"
                           col2 = t.Field<string>(1),//column of index 1 = "Col2"
                           col3 = t.Field<string>(5),//column of index 5 = "Col6"
                           col4 = t.Field<string>(6),//column of index 6 = "Col7"
                           col5 = t.Field<string>(4),//column of index 4 = "Col3"
                       };
        filteredData.AutoGenerateColumns = true;
        filteredData.DataSource = filtered.ToList();
    }
}

Upvotes: 1

Shivnandan Chauhan
Shivnandan Chauhan

Reputation: 1

DataView dv = new DataView(Your DataTable);

DataTable dt = dv.ToTable(true, "Your Specific Column Name");

The dt contains only selected column values.

Upvotes: -1

Sunday Efeh
Sunday Efeh

Reputation: 81

You can create a method that looks like this:

  public static DataTable SelectedColumns(DataTable RecordDT_, string col1, string col2)
    {
        DataTable TempTable = RecordDT_;

        System.Data.DataView view = new System.Data.DataView(TempTable);
        System.Data.DataTable selected = view.ToTable("Selected", false, col1, col2);
        return selected;
    }

You can return as many columns as possible.. just add the columns as call parameters as shown below:

public DataTable SelectedColumns(DataTable RecordDT_, string col1, string col2,string col3,...)

and also add the parameters to this line:

System.Data.DataTable selected = view.ToTable("Selected", false,col1, col2,col3,...);

Then simply implement the function as:

 DataTable myselectedColumnTable=SelectedColumns(OriginalTable,"Col1","Col2",...);

Thanks...

Upvotes: 0

Ramkumar Srinivasan
Ramkumar Srinivasan

Reputation: 783

Also we can try like this,

 string[] selectedColumns = new[] { "Column1","Column2"};

 DataTable dt= new DataView(fromDataTable).ToTable(false, selectedColumns);

Upvotes: 65

Derek
Derek

Reputation: 8628

The question I would ask is, why are you including the extra columns in your DataTable if they aren't required?

Maybe you should modify your SQL select statement so that it is looking at the specific criteria you are looking for as you are populating your DataTable.

You could also use LINQ to query your DataTable as Enumerable and create a List Object that represents only certain columns.

Other than that, hide the DataGridView Columns that you don't require.

Upvotes: 1

Related Questions