s

Home > c# > pass list of objects to stored procedure from c# to sql server

pass list of objects to stored procedure from C# to sql server edit button Edit

author
Murugan Andezuthu Dharmaratnam | calendar 22 September 2020 | 11889

I wrote this article while I was writing code for Recipes in code4beginner.com. I have a recipe table and RecipeIngredients table which stores the list of ingredients for a recipe. When I am adding a new recipe I have a list of ingredients that I have to save to the RecipeIngredients table. When a user adds a new Ingredient, I am storing it in a javascript object, I am stringifying the recipe ingredients array of objects to JSON and passing it to the Controller where I am converting it into a C# List<recipeingredients>. And in this article, we will discuss about how to move a C# list of objects to a stored procedure in sql server.

Here is the RecipeIngredients class. 

public partial class RecipeIngredients
{
    public String IngredientName { get; set; }
    public String Quantity { get; set; }
    public String UnitName { get; set; }
}

And I want to pass the below list to a stored procedure 

List<RecipeIngredients> lstRecipeIngredients

This can be done by creating a user-defined table type in your ms SQL database and passing the C# List of objects as a SqlParameter. I will provide the step by step instructions to do the same.

Summary of Codes 

CREATE TYPE [dbo].[type_yourtypename] AS TABLE(

)

CREATE PROCEDURE [dbo].[sp_name]
  @SomeName [type_yourtypename] READONLY
AS

C#

using (SqlConnection con = new SqlConnection(Common.Common.StoreCartConnectionString.getConnectionString()))
{
  SqlCommand cmd = new SqlCommand("sp_name", con);
  cmd.CommandType = CommandType.StoredProcedure;

  var SomeName = new SqlParameter("@SomeName",   SqlDbType.Structured);
  ItemManufacturerList.TypeName = "[type_yourtypename]";
  ItemManufacturerList.Value = DataTableData;

  cmd.Parameters.Add(SomeName); 
  con.Open();
  cmd.ExecuteNonQuery();
  con.Close();
}

Step 1: Create a user-defined table type

The first thing to do is to create a user-defined data type. The easiest way to create the script is to generate the script for the table and copy-paste the code for generated columns, you can leave the columns you do not need

CREATE TYPE [dbo].[type_recipeingredients] AS TABLE(
	[IngredientName] [nvarchar](128) NULL,
	[Quantity] [nvarchar](64) NULL,
	[UnitName] [nvarchar](64) NULL
)

Step 2: Create / Modify the stored procedure to accept list of objects

Here in the stored procedure, you have to add parameter @RecipeIngredients of type type_recipeingredients to receive the list sent from the C# code. 

CREATE PROCEDURE [dbo].[sp_recipe_edit_update]
	@RecipeId uniqueidentifier,
	@RecipeIngredients [type_recipeingredients] READONLY
AS
BEGIN
BEGIN TRANSACTION
    SET NOCOUNT ON;

	--You can write your Insert / Update Code here

	
COMMIT
END
GO

Step 3: C# Code call the stored procedure

public void UpdateRecipeIngredients(Guid recipeId, DataTable dtRecipeIngredients)
{
    using (SqlConnection con = new SqlConnection(Common.ConnectionString.getConnectionString()))
    {
        SqlCommand cmd = new SqlCommand("sp_recipe_edit_update", con);
        cmd.CommandType = CommandType.StoredProcedure;

        var RecipeId = new SqlParameter("@RecipeId", SqlDbType.UniqueIdentifier) { Value = recipeId };
        var RecipeIngredients = new SqlParameter("@RecipeIngredients", SqlDbType.Structured) { Value = dtRecipeIngredients, TypeName = "[type_recipeingredients]" };

        cmd.Parameters.Add(RecipeId);
        cmd.Parameters.Add(RecipeIngredients);
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
    }
}

You need to pass recipeid and RecipeIngredients data table to the function. check the code below. 

List<recipeingredientssp> lstRecipeIngredientsSP = new List<recipeingredientssp>()
{
    new RecipeIngredientsSP() { IngredientName="Mango", Quantity="10", UnitName="Kg" },
    new RecipeIngredientsSP() { IngredientName="Coconut", Quantity="1", UnitName="Number" },
    new RecipeIngredientsSP() { IngredientName="Chillies", Quantity="2", UnitName="Table Spoon" }
};

DataTable dtRecipeIngredients = lstRecipeIngredientsSP.ToDataTable();
dtRecipeIngredients.TableName = "RecipeIngredients";

new Recipe(Common.ConnectionString).UpdateRecipeIngredients(recipe.Id, dtRecipeIngredients);




check this article to convert from list to data table C# list to datatable