This is a very simple example that does what you want. You would not want to use hard-coded connection strings, especially in-line, and you would want error-handling, but I am going for as much clarity as possible. You would also probably want to make the column length greater than 50 characters, but I made it match your column definition.
Also, I would recommend a generic approach, passing keys (column names) and values, so as to be able to use it for any sort of criteria, but you asked that I keep it to exactly what you require, so I trimmed it down to the essential.
This example returns all the Employees with FirstName matching any in the list passed to the stored procedure (as a user-defined table type).
First, create a user-defined table type (to hold the values you want to pass to the stored procedure) in your SQL Server database as follows:
CREATE TYPE [dbo].[FilterValues] AS TABLE(
[Value] [varchar](50) NOT NULL,
PRIMARY KEY CLUSTERED
(
[Value] ASC
)
)
The stored procedure to return the Employees looks as follows (note that it has the user-defined table type as the type of the single parameter passed in):
CREATE PROCEDURE [dbo].[GetEmployees] (
@FirstNameFilterValues dbo.FilterValues READONLY
)
AS
BEGIN
SELECT * FROM Employees
INNER JOIN @FirstNameFilterValues fv ON fv.Value = Employees.FirstName;
END
That's the SQL Server side done. To call it from C#, you can create a DataTable with a single column matching the column name and populate it with the values you want. In this simple example, I populate it with two names, but it could be as many as you want.
var filterValuesDataTable = new DataTable();
filterValuesDataTable.Columns.Add(new DataColumn("Value", typeof(string)) { AllowDBNull = false });
filterValuesDataTable.Rows.Add("Frodo");
filterValuesDataTable.Rows.Add("Sam");
using (var connection = new SqlConnection("server=.;Initial Catalog=Test;Integrated Security=True;"))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = "GetEmployees";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@FirstNameFilterValues", filterValuesDataTable);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine("{0} {1}", reader["FirstName"], reader["LastName"]);
}
reader.Close();
}
}
connection.Close();
}
