C# Storing SQL Select query into a variable

Question

I am using VS2005 C# and SQL Server 2005.

I have a SQL Query which I will execute, and I wish to store the results that I will be getting from the query.

The following is my SQL query:

SELECT DISTINCT EmployeeID FROM UserRoles WHERE ([Status] = 'DELETED')

This returned me a list of employee IDs whose status are 'DELETED'. I am able to view it via GridView and it is displaying perfectly.

But what I want is to use this result (list of emp ID whose status='DELETED'), and run through another table to see if the same emp ID exist in the other table, if yes, store it in another DB or list.

I am not really sure how do I store the result from my select statement, which is the list of emp IDs whose status='DELETED' into a string or array. After i manage to store it, how do i get one emp ID at a time and run through the other table?

I need some help from experienced.

ChrisWue · Accepted Answer · 2011-11-11 07:14:37Z

2

How about running another query:

SELECT DISTINCT UserRoles.EmployeeID FROM UserRoles 
INNER JOIN OtherTable ON OtherTable.EmployeeID = UserRoles.EmployeeID 
WHERE (UserRoles.[Status] = 'DELETED')

Should return all employees which are deleted and exist in OtherTable.

answered Nov 11, 2011 at 7:14

ChrisWue

19.1k4 gold badges63 silver badges86 bronze badges

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

Comments

Icarus · Accepted Answer · 2011-11-11 08:01:55Z

1

It really depends on how you read this list of employee IDS from the database. I am going to assume that you read it using a SqlDataAdapter which fills a DataSet or a DataTable which in turn you bind to your grid:

~~List employeeIDs = new List(); //dataTable is the DataTable you bind to your grid foreach (DataRow row in dataTable.Rows) { employeeIDs.Add(int.Parse(row[0].ToString()); }~~

  List<int> query =
  (from eid in dataTable.AsEnumerable()
  select eid.Field<int>("EmployeeID")).ToList();

If you use a SqlDataSource....

DataView dv = (DataView)SqlDataSource1.Select(DataSourceSelectArguments.Empty);
List<int> query =
(from eid in dv.ToTable().AsEnumerable()
select eid.Field<int>("EmployeeID")).ToList();

edited Nov 11, 2011 at 8:01

answered Nov 11, 2011 at 7:15

Icarus

64k14 gold badges102 silver badges116 bronze badges

Collectives™ on Stack Overflow

C# Storing SQL Select query into a variable

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related