Loop through a column in SQL using C# ( and compare its values with a variable )

Question

I have a large table which has an ID column and contains request/ticket info.

On a win form , I want to let user enter his request ID and get all the request info in a new form.

The place where I am facing a problem is how to loop through the values of only one column.

Here is what I have seen elsewhere and have tried to implement[my datatable name is st]:

DataTable dt = new DataTable("dbo.table_submission");
DataRow[] foundRows = dt.Select("ID =" + i);
        if (foundRows != null )
        {
            Status status = new Status();
            status.Show();
        }

The DataTable has a method called Select that could retrieve matching rows for you without you using an explicit loop — Steve
– Steve, Commented Jul 18, 2016 at 14:49
I am assuming you are talking about a stored procedure.But I need to loop through each value one by one and compare it to a variable. — jokol
– jokol, Commented Jul 18, 2016 at 14:52
No there is a SELECT method of the DataTable object. And you do NOT need to loop row by row for this. msdn.microsoft.com/en-us/library/… — Sean Lange
– Sean Lange, Commented Jul 18, 2016 at 14:54

Steve · Accepted Answer · 2016-07-18 15:06:03Z

1

The DataTable has a Select method that could retrieve matching rows for you without you using an explicit loop.

For example, assuming that the value you are searching for is in a column named RequestID

int yourValueToSearch = 1;

DataRows[] foundRows = dt.Select("RequestID = " + yourValueToSearch);
if(foundRows != null && foundRows.Length > 0)
{
    DataTable result = foundRows.CopyToDataTable();
    // Pass the result table to your form....

}

This could also be a string but you need to add proper quoting

string yourValueToSearch = "ABC123";
DataRows[] foundRows = dt.Select("RequestID = '" + yourValueToSearch + "'");

Eventually you could also use Linq

var foundRows = dt.AsEnumerable()
                  .Where(x => x.Field<int>("RequestID") == yourValueToSearch)
                  .ToArray()

edited Jul 18, 2016 at 15:06

answered Jul 18, 2016 at 14:54

Steve

217k22 gold badges242 silver badges296 bronze badges

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

5 Comments

jokol Over a year ago

I am getting a weird error that my column [ID] was not found (which is the name of column in discussion) . Is this syntax compatible with sql server or mysql? Also , what is this foundRows.Length > 0 for?

Steve Over a year ago

The evaluation of this syntax is done by the NET Framework, not by any database engine (Remember a datatable is a disconnected object). The check on the array foundRows is required if you want to use CopyToDataTable otherwise you will get an exception. About the ID column I can't say. The name should match the column name of the datatable

Steve Over a year ago

The documentation about the syntax used by the filterexpression in the Select method can be found in the DataColumn.Expression docs page

jokol Over a year ago

Sorry to have bothered you , I see you have given me the link to the documentation , I will go and help myself. Thank you.

Steve Over a year ago

No nothing wrong here, but we are talking about columns names. That line just declares the table without any column. How do you fill the datatable. Columns are created before filling the table and usually they takes the names of the column on the database table.

Collectives™ on Stack Overflow

Loop through a column in SQL using C# ( and compare its values with a variable )

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related