Using SQL Server 2012
Assuming we have a sample “TestDB” with the following scheme:
Create a type which will use for the input parameter:
Create a new stored procedure
Fill in the stored procedure name, the parameter name and the query
Notice the CostumersIDs is the parameter name and CostumersIdsType is it’s type which we created earlier.
For simplicity, the query I used just returns the received input parameter.
Test the stored procedure in sql server:
What happened here is:
– We created a temporary table of our declared type (CostumersIdsType), and inserted into it a few value.
– Called our stored procedure with the temporary table as a parameter.
Testing the stored procedure
Call the stored procedure from C#
- //Call the stored procedure from C#
- //Prepare the data
- //Build a demo table, containing 3 costumers with different ID’s
- table.Columns.Add(“Id”, typeof(int));
- //Prepare the query
- varspParameters=newDictionary<string, object>(1);
- spParameters.Add(storedProcParamName, table);
- query.ExecuteSP(storedProcName, spParameters);
I won’t expose all the details since it’s out of scope;
Just to mention, I’m using my own class ‘BuildQuery’, which is a simple wrapper over System.Data.SqlClient type: SqlCommand, SqlConnection and SqlDataAdapter. There are nicer ways to do that, check the link at the bottom.
Notice I’m using the same naming for the stored procedure and the parameter.
What happens next is building a DataTable with data, setting it as an input parameter and executing the query.
Results from C# stored procedure call.
More info about stored procedure mapping to entity framework can be found here.