Best way to insert a list of objects into a sql #temp table and then do a merge insert and delete with an existing sql table

Question

Hi,

I have got a list of objects in my C# web application. Upon execution (button click), the data in the list of objects should be stored into a temporary table and the values cross checked and subsequently if data does not exist, these data from the temp table should be inserted, otherwise, updated and if the data in the existing table does not exist in the temp table, then the data should be deleted from the existing table.

How do I pass the list of objects into the temp table, now I use this code, but this code just runs the stored procedure row by row and this is not how I want it.

 for (int i = 0; i < DetailedPlanList.Count; i++)
                {
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        cmd.Connection = con;
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = "InsertDetTables";
                        cmd.Parameters.Add("@PlanDate", SqlDbType.Date).Value = DetailedPlanList[i].PlanDate;
                        cmd.Parameters.Add("@ProcessUnitID", SqlDbType.NVarChar).Value = DetailedPlanList[i].ProcessUnitID;
                        cmd.Parameters.Add("@CashierID", SqlDbType.NVarChar).Value = DetailedPlanList[i].DTargetcumUnit;

                        con.Open();
                        cmd.ExecuteNonQuery();
                        con.Close();
                    }
                }

The stored procedure

 IF EXISTS (SELECT 1 FROM DetailFPlantbl WHERE IDtoUpdate=@IDtoUpdate)
    BEGIN
        UPDATE DetailFPlantbl set DTargetcum = @DTargetcum, DTargetperday = @DTargetperday, DTargetcumtotal = @DTargetcumtotal, DActualcumSAP = @DActualcumSAP,DActualperdaySAP = @DActualperdaySAP, DActualcumtotal = @DActualcumtotal, TimeStampFP = CURRENT_TIMESTAMP WHERE IDtoUpdate=@IDtoUpdate and DActualcumSAP IS NULL and DActualcumtotal IS NULL and DActualperdaySAP IS NULL

    END
ELSE
    BEGIN
        INSERT into DetailFPlantbl(PlanDate,ProcessUnitID,IDtoUpdate) Values (@PlanDate, @ProcessUnitID,@IDtoUpdate)

    END

Please advice, should I use table valued parameter, should I convert my list of objects into a data table.?

Answer 1

A table-valued parameter sounds like a good idea, but you don't need a DataTable. You can also pass a List<SqlDataRecord>. You can also stream the data.

I have primer on using table-valued parameters which discuss these options here: https://www.sommarskog.se/arrays-in-sql-2008.html.

Answer 2

should I use table valued parameter, should I convert my list of objects into a data table.?

"best" is a bit subjective but the list of objects can be provided to the proc as a TVP, JSON string, or XML for a set-based operation in the proc.

A TVP requires a table type in the database with columns matching those of the object fields. The proc TVP declaration specifies that type as a READONLY parameter where it can be used in queries like any other table. The client parameter type for a TVP is SqlDbType.Structured with a value of either IEnumerable<SqlDataRecord>, DataTable, or DbDataReader. A DataTable is most commonly used, at least in my experience, with the columns matching those of the table type. See the ADO.NET Table-Valued Parameters (https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters) topic for code DataTable examples and Erland's Using Table-Valued Parameters article for more info on IEnumerable<SqlDataRecord>.

Another option in SQL 2016 and later is JSON, which avoids the need to create a table type. The proc JSON parameter can be of type nvarchar(MAX) and used in T-SQL queries with the OPENJSON table-valued function. The app code can simply serialize the object collection as a JSON string (array of json objects) and pass the string value for the parameter value.

The proc could use a MERGE statement for the INSERT/UPDATE. The T-SQL code in your question looks a bit odd since I don't understand your schema or use case, but below are untested examples with dummy data types to show how you could use MERGE with a TVP or JSON.

--TVP example  
MERGE dbo.DetailFPlantbl AS target  
USING @DetailFPlantbl AS source ON   
	    source.IDtoUpdate = target.IDtoUpdate  
	AND target.DActualcumSAP IS NULL   
	AND target.DActualcumtotal IS NULL  
	AND target.DActualperdaySAP IS NULL  
WHEN MATCHED THEN UPDATE SET  
	  DTargetcum = source.DTargetcum  
	, DTargetperday = source.DTargetperday  
	, DTargetcumtotal = source.DTargetcumtotal  
	, DActualcumSAP = source.DActualcumSAP  
	, DActualperdaySAP = source.DActualperdaySAP  
	, DActualcumtotal = source.DActualcumtotal  
	, TimeStampFP = CURRENT_TIMESTAMP  
WHEN NOT MATCHED BY TARGET THEN INSERT (  
	  PlanDate  
	, ProcessUnitID  
	, IDtoUpdate  
	)  
VALUES (  
	  source.PlanDate  
	, source.ProcessUnitID  
	, source.IDtoUpdate  
);  
  
  
--JSON example  
MERGE dbo.DetailFPlantbl AS target  
USING (  
	SELECT  
		  IDtoUpdate  
		, DTargetcum  
		, DTargetperday  
		, DTargetcumtotal  
		, DActualcumSAP  
		, DActualperdaySAP  
		, DActualcumtotal  
		, TimeStampFP  
		, PlanDate  
		, ProcessUnitID  
	FROM OPENJSON(@DetailFPlantbl)  
	WITH (  
		  IDtoUpdate int  
		, DTargetcum int  
		, DTargetperday int  
		, DTargetcumtotal int  
		, DActualcumSAP int  
		, DActualperdaySAP int  
		, DActualcumtotal int  
		, TimeStampFP datetime2  
		, PlanDate datetime2  
		, ProcessUnitID int		  
	)  
)  
 AS source ON   
	    source.IDtoUpdate = target.IDtoUpdate  
	AND target.DActualcumSAP IS NULL   
	AND target.DActualcumtotal IS NULL  
	AND target.DActualperdaySAP IS NULL  
WHEN MATCHED THEN UPDATE SET  
	  DTargetcum = source.DTargetcum  
	, DTargetperday = source.DTargetperday  
	, DTargetcumtotal = source.DTargetcumtotal  
	, DActualcumSAP = source.DActualcumSAP  
	, DActualperdaySAP = source.DActualperdaySAP  
	, DActualcumtotal = source.DActualcumtotal  
	, TimeStampFP = CURRENT_TIMESTAMP  
WHEN NOT MATCHED BY TARGET THEN INSERT (  
	  PlanDate  
	, ProcessUnitID  
	, IDtoUpdate  
	)  
VALUES (  
	  source.PlanDate  
	, source.ProcessUnitID  
	, source.IDtoUpdate  
);  

Answer 3

Inserting a value and using SP should be two operations, the latter should not be in a for loop.

Use ADO.Net to insert the value first and execute SP once after the execution is complete.

In addition, there is no need to repeatedly open and then close the connection in the for loop.

It is better to put sqlcommand in a for loop, creating too many objects will impress performance to a certain extent.

            using (SqlConnection connection = new SqlConnection(connString))  
            {  
                connection.Open();  
                using (SqlCommand command = new SqlCommand())  
                {  
                    command.Connection = connection;  
                    command.CommandType = System.Data.CommandType.Text;  
                    for (int i = 0; i < length; i++)  
                    {  
                        command.CommandText = "your sql";  
                        ...  
                        command.ExecuteNonQuery();  
                    }  
  
                    command.CommandType = CommandType.StoredProcedure;  
                    command.CommandText = "UpdateScore";  
                    command.ExecuteNonQuery();  
  
                }  
            }  

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Thank you all for the insight and taking time to answer my question.