How to pass List<Int> to SQL Server stored procedure

This is a solved problem and properly documented in - cough - the documentation.

YOu will need to define the table on the server side and then can pass in a table valued parameter.

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters

This runs down to using the SqlDbType Structured.

// Configure the command and parameter.  
SqlCommand insertCommand = new SqlCommand(sqlInsert, connection);  
SqlParameter tvpParam = insertCommand.Parameters.AddWithValue("@tvpNewCategories", addedCategories);  
tvpParam.SqlDbType = SqlDbType.Structured;  
tvpParam.TypeName = "dbo.CategoryTableType";

You CAN use a DataTable, but then you introduce the most overhead approach possible as object model - or you just use...

https://forums.asp.net/t/1845039.aspx?Table+Value+Parameter+Use+With+C+without+using+DataTable

Basically you transform your data into SqlDataRecords and pass them in. Needs some metadata - but generally this can be generalized and fits in below a page of code. The link has the code (which I can not copy here due to - well - it not being MY code).

I always use XML to pass these type of DATA to sqlserver sproc.

When you pass your XML to sproc you can use something like this to have it as a table in your sproc:

(commented lines are my XML's structure. manipulate it for your own use.)

EXEC sp_xml_preparedocument @XmlHandle output,@FieldPermissionAccessXML
    --'<FieldPermissions>
    --<FieldPermission FieldName="" RoleID="1" Access="1" />
    --<FieldPermission FieldName="" RoleID="2" Access="1" />'
    --select * from JMP_FieldPermissions
INSERT INTO #FieldPermissionsTable 
SELECT *--ID,Value, Value2, Navi_User 
FROM  OPENXML (@XmlHandle, './Row_ID/Elements') 
      WITH (TE_ID VARCHAR(MAX) '@ID',
    Value VARCHAR(MAX) '@Value',
    Value2 VARCHAR(MAX) '@Value2',
    NAVI_USER VARCHAR(MAX) '@Navi_User'     
            )

Create the DataTable variable in c#, put the data in the DataTable, pass it to sp:

List<int> lstNew = MyList.Select(o => o.Key).ToList();
DataTable lstNewTable = new ListNew();
foreach (var id in lstNew )
{
    lstNewTable.Rows.Add(id);
}

List<XXXView> lstView = db.Database.SqlQuery<XXXView>("MyStoredProcedure @lstNew,@UserName",new SqlParameter("@lstNew", lstNewTable),
              new SqlParameter("@UserName", userName)).ToList();

Several answers were close but none gave a full working model. @Alison Niu just needs to add a column & name to populate the DataTable. @Saurabh Gadani the reflection is very flexible but the Props are Null. Also the cmd.Parameters needs some special values set for Sql to find the table definition. I am grateful for these answers that got me closer to a solution.

Populate a DataTable from a List of Integers

    private DataTable ListInt_ToTable(List<int> tableThese)
    {
        DataTable lstNewTable = new DataTable();
        //Columns
        lstNewTable.Columns.Add("ID", typeof(int));

        //Values
        foreach (var id in tableThese)
        {
            lstNewTable.Rows.Add(id);   
        }
        return lstNewTable;
    }

The TYPE defines the table layout for SQL to receive the parameter. It can be written within the Stored Procedure but this is how I prefer to keep it in the application that built the data:

public static string SqlCmd_spStackOverflow_Loaded_ListIDs = @"
            IF TYPE_ID(N'IdLoadedTableType') IS NULL
                Begin
                    CREATE TYPE dbo.IdLoadedTableType
                       AS TABLE ( ID INT );
                End

            EXEC dbo.spStackOverflow_Loaded_ListIDs
                   @TblIds
    ";

Sending the Sql to run the Stored Procedure. Notice the parameters **

DataTable mappingTbl = ListInt_ToTable(mappingData);
using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))
        {
            try
            {
                DataTable results = new DataTable();
                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(SqlCmd_spStackOverflow_Loaded_ListIDs, sqlConnection);
                //apply parameters
                //NOT VIA sqlDataAdapter.SelectCommand.Parameters.AddWithValue(...);
                var specialParm = new SqlParameter();    // **
                specialParm.ParameterName = "@TblIds";   // acts like a Declare in SQL
                specialParm.Value = (object)mappingTbl;  // sets the data valaes                                                           
                specialParm.SqlDbType = SqlDbType.Structured; // unique to User-Defined Table parameters
                specialParm.TypeName = "dbo.IdLoadedTableType"; // refers to created type
                sqlDataAdapter.SelectCommand.Parameters.Add(specialParm); // done

                sqlDataAdapter.Fill(results);
                return results;
            }
            catch (Exception ex)
            {
                throw new Exception("An error occurred while executing a SQL Read statement with Parameters. SQL Statement: " + SqlCmd_spStackOverflow_Loaded_ListIDs + " Exception: " + ex.Message);
            }
        }

This model is working in my solution, except I use long in C# & BigInt in sql. Please improve options as you find something.

You can not pass any generic type list as SP parameter, must have to pass it as DataTable instead of List. Here is an eaxample:

DataTable mappingTbl = ListToDataTable(mappingData);

SqlConnection con = new SqlConnection(conStr);
            con.Open();
            SqlCommand cmd = new SqlCommand(StoredProcedure.GetSavedFormList, con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@UDT_EBFromMappingTable", mappingTbl);


public DataTable ListToDataTable<T>(List<T> items)
        {
            DataTable dataTable = new DataTable(typeof(T).Name);

            //Get all the properties
            PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
            foreach (PropertyInfo prop in Props)
            {
                //Defining type of data column gives proper data table 
                var type = (prop.PropertyType.IsGenericType && prop.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>) ? Nullable.GetUnderlyingType(prop.PropertyType) : prop.PropertyType);
                //Setting column names as Property names
                dataTable.Columns.Add(prop.Name, type);
            }

            foreach (T item in items)
            {
                var values = new object[Props.Length];
                for (int i = 0; i < Props.Length; i++)
                {
                    //inserting property values to datatable rows
                    values[i] = Props[i].GetValue(item, null);
                }
                dataTable.Rows.Add(values);
            }
            //put a breakpoint here and check datatable
            return dataTable;
        }

May this helps you. :)