CODEWITHSUNDEEP
c#sqlsql-servervisual-studiovisual-studio-2005
gymcode
gymcode

Reputation: 4633

C# Storing SQL Select query into a variable

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.

Upvotes: 0

Views: 1022

Answers (2)

Icarus
Icarus

Reputation: 63970

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();

Upvotes: 1

ChrisWue
ChrisWue

Reputation: 19060

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.

Upvotes: 2

Related Questions