locked
LINQ or C# - How to find particular column and its values RRS feed

  • Question

  • User-1024101449 posted

    Hi,

    I want to find the columns from my table based on my input string.

    for example,

    my table as below.

    Lesson1 x SUB A Lesson1 x SUB B Lesson1 x SUB C
    400 500 600

    string Findcolumns = "Lesson x SUB A"

    So, First i want to find the columns of "Lesson x SUB A" and the get the values of that..

    My Result is = 400..

    How to write LINQ Query or C# code..?

    Tuesday, August 29, 2017 1:23 PM

Answers

  • User-1838255255 posted

    Hi gani7787,

    I make a modify of my previous reply through your needs, please check:

    Sample Code:

    DataTable table = new DataTable();
    
            protected void Page_Load(object sender, EventArgs e)
            {
                table.Columns.Add("Lesson1 x SUB A");
                table.Columns.Add("Lesson1 x SUB B");
                table.Columns.Add("Lesson1 x SUB C");
                table.Columns.Add("UNIT");
    
                table.Rows.Add(400, 500, 600, 205);
                table.Rows.Add(300, 400, 500, 300);
                table.Rows.Add(500, 600, 700, 400);
            }
    
            protected void Button1_Click(object sender, EventArgs e)
            {
                string columnvalue = TextBox1.Text;
                var query = from r in table.AsEnumerable()
                            where r.Field<string>("UNIT") == "300"
                            let objectArray = new object[]
                            {r.Field<string>("Lesson1 x SUB A"),
                              r.Field<string>("Lesson1 x SUB B"),
                              r.Field<string>("Lesson1 x SUB C"),
                                r.Field<string>("UNIT")
                            }
                            select objectArray;
                DataTable newDataTable = new DataTable();
                newDataTable.Columns.Add("Lesson1 x SUB A");
                newDataTable.Columns.Add("Lesson1 x SUB B");
                newDataTable.Columns.Add("Lesson1 x SUB C");
                newDataTable.Columns.Add("UNIT");
    
                foreach (var array in query)
                {
                    newDataTable.Rows.Add(array);
                }
                for (int i = 0; i < newDataTable.Rows.Count; i++)
                {
                    Response.Write("Lesson1 x SUB A: " + newDataTable.Rows[i][0].ToString() + "Lesson1 x SUB B: " + newDataTable.Rows[i][1].ToString() + "Lesson1 x SUB C: " + newDataTable.Rows[i][2].ToString() + "UNIT: " + newDataTable.Rows[i][3].ToString() + "</br>");
                }
            }

    Best Regards,

    Eric Du

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, August 30, 2017 9:07 AM

All replies

  • User475983607 posted

    Execute SQL using Linq.  That allows you to format the SQL anyway you like.

    https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/linq/how-to-directly-execute-sql-queries

    The syntax might look like this using string interpolation.

    IEnumerable<MyObject> results = db.ExecuteQuery<MyObject>
    ($"SELECT {columnA}, {columnB}, {columnC}
        FROM MyTable
        WHERE {columnA} = {somevar}" 
    );

    https://docs.microsoft.com/en-us/dotnet/csharp/language-reference/keywords/interpolated-strings

    Tuesday, August 29, 2017 2:31 PM
  • User303363814 posted

    What sort of table?  An sql table or a DataTable (or maybe a GridView or some other table like object)?  Can you show your data types?

    In your example the Findcolumns string does not match any of the sample data you have given.  Why should the result be 400?

    Wednesday, August 30, 2017 1:27 AM
  • User-1024101449 posted

    DataTable table = new DataTable();
    table.Columns.Add("Lesson1 x SUB A");
    table.Columns.Add("Lesson1 x SUB B");
    table.Columns.Add("Lesson1 x SUB C");
    table.Rows.Add(400,500,600);
    table.Rows.Add(300, 400, 500);
    table.Rows.Add(500, 600, 700);
    string Findcolumns = "Lesson1 x SUB A";

    Below is the simple Query.

     string result=table.Rows[0][Findcolumns].ToString();

    But, how to give where condition in the query.

    because, i have "n" number of rows and i want to filter based on the conditions..

    for example,

     DataTable table = new DataTable();
       table.Columns.Add("Lesson1 x SUB A");
       table.Columns.Add("Lesson1 x SUB B");
        table.Columns.Add("Lesson1 x SUB C");

          table.Columns.Add("UNIT");

        table.Rows.Add(400,500,600,205);
       table.Rows.Add(300, 400, 500,300);
       table.Rows.Add(500, 600, 700,400);

    Where unit = 400.

    Is it possible for where condition here...?

    Wednesday, August 30, 2017 5:52 AM
  • User-1838255255 posted

    Hi gani7787,

    According to your description and needs, you could use the datatable select method, for more details, please check the following sample code:

    Sample Code:

    <div>
                ColumnName:<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
                <asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" />
    </div>
    
    DataTable table = new DataTable();
    
            protected void Page_Load(object sender, EventArgs e)
            {
                table.Columns.Add("Lesson1 x SUB A");
                table.Columns.Add("Lesson1 x SUB B");
                table.Columns.Add("Lesson1 x SUB C");
                table.Columns.Add("UNIT");
    
                table.Rows.Add(400, 500, 600, 205);
                table.Rows.Add(300, 400, 500, 300);
                table.Rows.Add(500, 600, 700, 400);
            }
    
            protected void Button1_Click(object sender, EventArgs e)
            {
                string columnvalue = TextBox1.Text;
                DataRow[] result = table.Select(String.Format("{0} >= 300", columnvalue));
                foreach (DataRow row in result)
                {
                    Response.Write("Lesson1 x SUB A:" + row[0].ToString() + "Lesson1 x SUB B:" + row[1].ToString() + "Lesson1 x SUB C:" + row[2].ToString() + "UNIT:" + row[3].ToString() + "</br>");
                }
            }

    Best Regards,

    Eric Du

    Wednesday, August 30, 2017 7:22 AM
  • User-1024101449 posted

    Is it possible to write a code in LINQ C#..?

    Wednesday, August 30, 2017 8:15 AM
  • User-1838255255 posted

    Hi gani7787,

    I make a modify of my previous reply through your needs, please check:

    Sample Code:

    DataTable table = new DataTable();
    
            protected void Page_Load(object sender, EventArgs e)
            {
                table.Columns.Add("Lesson1 x SUB A");
                table.Columns.Add("Lesson1 x SUB B");
                table.Columns.Add("Lesson1 x SUB C");
                table.Columns.Add("UNIT");
    
                table.Rows.Add(400, 500, 600, 205);
                table.Rows.Add(300, 400, 500, 300);
                table.Rows.Add(500, 600, 700, 400);
            }
    
            protected void Button1_Click(object sender, EventArgs e)
            {
                string columnvalue = TextBox1.Text;
                var query = from r in table.AsEnumerable()
                            where r.Field<string>("UNIT") == "300"
                            let objectArray = new object[]
                            {r.Field<string>("Lesson1 x SUB A"),
                              r.Field<string>("Lesson1 x SUB B"),
                              r.Field<string>("Lesson1 x SUB C"),
                                r.Field<string>("UNIT")
                            }
                            select objectArray;
                DataTable newDataTable = new DataTable();
                newDataTable.Columns.Add("Lesson1 x SUB A");
                newDataTable.Columns.Add("Lesson1 x SUB B");
                newDataTable.Columns.Add("Lesson1 x SUB C");
                newDataTable.Columns.Add("UNIT");
    
                foreach (var array in query)
                {
                    newDataTable.Rows.Add(array);
                }
                for (int i = 0; i < newDataTable.Rows.Count; i++)
                {
                    Response.Write("Lesson1 x SUB A: " + newDataTable.Rows[i][0].ToString() + "Lesson1 x SUB B: " + newDataTable.Rows[i][1].ToString() + "Lesson1 x SUB C: " + newDataTable.Rows[i][2].ToString() + "UNIT: " + newDataTable.Rows[i][3].ToString() + "</br>");
                }
            }

    Best Regards,

    Eric Du

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, August 30, 2017 9:07 AM