0

Using VS2019 and SQL Server 2019 i cannot get a stored procedure to update all expected rows when called from C#, it updates 1 row only, the lowest value.

I am using a TVP to pass in multiple rows, I want to use a value from this to update RunnerOdds table. So when MarketId and SelectionId match, I want to append the Odds column with the value from TVP.

This works fine from SSMS using:

declare @NewTableParam OddsType
insert into @NewTableParam(MarketId,SelectionId,Odds)
values ('1.172222777',16732924,'1'),
('1.172222777',10227602,'1'),
('1.172222777',35610986,'3'),
('1.172222777',16764874,'16'),
('1.172222777',35537128,'2'),
('1.172222777',34000814,'9')

exec usp_RunnerOddsUpdate @OddsLines = @NewTableParam

But when i run from C# i only get 1 row updated.

This is the TVP:

CREATE TYPE [dbo].[OddsType] AS TABLE(
    [MarketId] [nvarchar](20) NOT NULL,
    [SelectionId] [bigint] NOT NULL,
    [Odds] [nvarchar](1000) NULL
)

This is the sproc:

ALTER PROCEDURE [dbo].[usp_RunnerOddsUpdate]
    @OddsLines OddsType READONLY
AS
BEGIN

    update ro
    set 
        ro.Odds += ',' + ol.Odds
    from 
        @OddsLines ol
    join RunnerOdds ro on
        ro.SelectionId = ol.SelectionId and
        ro.SelectionId = ol.SelectionId

END

This is the C# ado.net: (odds is a List)

        var dt = odds.ToDataTable(); // convert list object to datatable for use with TVP

        using (SqlConnection conn = SqlServerConn.GetConnection)
        {
            conn.Open();

            using (SqlCommand cmd = new SqlCommand("usp_RunnerOddsUpdate", conn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                var param1 = cmd.Parameters.AddWithValue("@OddsLines", dt);
                param1.SqlDbType = SqlDbType.Structured;
                param1.TypeName = "OddsType"; // TVP

                // execute query
                int numberRecordsAffected = cmd.ExecuteNonQuery();

                if (numberRecordsAffected == 0)
                {
                    return false; // failed to insert
                }
                else
                {
                    log.Info(string.Format("Updated {0} lines", numberRecordsAffected));
                    return true;
                }
            }
        }

This is probably something simple but i can't get it. Has anyone any ideas?

Thanks for reading.

IAT
  • 1
  • You say it fails, how do you know? Are you getting an error, or are you saying that `numberRecordsAffected` has a value of `0`? Have you checked the database to see if the data *has* been affected? – Thom A Aug 21 '20 at 10:26
  • Side note: A `SET` statement like `ro.Odds += ',' + ol.Odds` implies you're storing delimited data in your database. I suggest fixing that and using a normalised approach if that is the case. – Thom A Aug 21 '20 at 10:28
  • It doesn't fail, it just does not update all rows when called from code even though numberRecordsAffected = 6. – IAT Aug 21 '20 at 10:29
  • You have `join RunnerOdds ro on ro.SelectionId = ol.SelectionId and ro.SelectionId = ol.SelectionId`. Look at that again ... and again. Does that make ANY sense? Your TVP has 3 columns but you only reference 2 - why does that 3rd column exist? Storing numbers as strings - why? – SMor Aug 21 '20 at 11:18
  • and of course - don't use [addwithvalue](http://www.dbdelta.com/addwithvalue-is-evil/) – SMor Aug 21 '20 at 11:19
  • Thanks SMor. Not sure i understand you or TVP's, probably the later. The third column is a value i want to use to update RunnerOdds with. Surely matching on MatchId and SelectionId is sufficient as SelectionId is unique. Regardless, why does it work from SSMS? – IAT Aug 21 '20 at 11:33

0 Answers0