jokol jokol - 1 year ago 85
SQL Question

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

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]:

DataColumn dtcol = st.Columns[0];

// On all tables' rows
foreach (DataRow dtRow in st.Rows)
// On all tables' columns
for (DataColumn dtcol in st.Columns)


Answer Source

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)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download