If you are using SQL2008 or later, you could think about using 'Table Value Parameters'
C# and Table Value Parameters[
^]
So, you create a Table type to hold your keys in SQL Server, e.g.
CREATE TYPE [dbo].[ItemKeyList] AS TABLE(
ItemId [varchar](50) NULL
)
In SQL, you could write a stored procedure that uses this data type - eg, inserts some data into some table
CREATE PROCEDURE [dbo].[something_AddKeysToTable]
(
@MainId BIGINT,
@KeyList AS KeyList READONLY
)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [SomeLookupTable]
([MainId]
,[KeyId])
SELECT
@MainId, ItemId
FROM
@KeyList
WHERE
ItemId
NOT IN
(
SELECT [KeyId] FROM [SomeLookupTable] WHERE MainId = @MainId
)
END
In c#, you would create a List type that represents your table data
public class ItemList : List<string>, IEnumerable<sqldatarecord>
{
IEnumerator<sqldatarecord> IEnumerable<sqldatarecord>.GetEnumerator()
{
var sdr = new SqlDataRecord(new SqlMetaData("ItemId", SqlDbType.VarChar, 50));
foreach (string item in this)
{
sdr.SetString(0, item);
yield return sdr;
}
}
}
</sqldatarecord></sqldatarecord></sqldatarecord></string>
In c#, when you want to send this data, you just use as per normal, we just need to inform that the SQL Parameter is a Structured datatype. This example converts a c# array into the SQL List type, then sends to the server
public void AssignItemsToMain(long mainId, string[] itemIds)
{
var list = new ItemList();
list.AddRange(itemIds);
using (var cmd = connection.CreateCommand() as SqlCommand)
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "something_AddKeysToTable";
cmd.Parameters.AddWithValue("@MainId", mainId);
var p = cmd.Parameters.Add("@ItemList", SqlDbType.Structured);
p.Direction = ParameterDirection.Input;
p.TypeName = "ItemKeyList";
p.Value = list;
cmd.ExecuteNonQuery();
}
}
It's a bit of code to write up front, but obviously you can make this quite generic and reuse for all your list data :)