select in table where list<int>

There开发者_如何学Go is a table with field age and name

The following data

jack 15
sara 14
mishel 13
sufia 19
green 22
blue 56

We have a presentation with the following members

list<int> age = new list<int>(); 
age.add(15);
 age .add(14);
 age .add(19);

How do I search the database by providing

The following are selected data from Table

jack 15
sara 14
sufia 19

---

Build a SQL query programmatically using the IN clause:

string sql = @"
    SELECT *
      FROM [PeopleTable]
     WHERE [Age] IN (" + age.Join(", ") + ")";

Result:

SELECT *
  FROM [PeopleTable]
 WHERE [Age] IN (15, 14, 19)

---

If you are using MSSQL you can create a table type which will have one column as Age.

CREATE TYPE my_table_type AS TABLE(Age int NOT NULL)
go

CREATE PROCEDURE usp_FetchUser
 @age my_table_type READONLY 
AS
select user_name from my_table where user_age in( select * from @age)
go

---

If you use LINQ to SQL, then you should create this query:

var query = from record in table
            where ages.Contains(record.age)
            select record;
query.ToList();

If you want to use SP, then you gotta create the where clause dynamically:

string whereClause = string.Empty;
foreach (int age in ages)
{
    whereClause += age + ", ";
}
whereClause = whereClause.SubString(query, query.Length - 2) // Removing the last comma;
string query = string.Format ("select * from tableName where age in ({0})", whereClause);