CODEWITHSUNDEEP
c#sqlsql-serverstored-procedures
Jun Zheng
Jun Zheng

Reputation: 677

Error executing stored procedure with datatable parameter in c#

I'm trying to create a stored procedure to add or update a collection of object executed in c#, but not sure why it isn't executing correctly in c# while it works fine in sql.

I'm not sure how generate some useful error message to help with debugging either.

Any tip or pointer to guide me to the right direction is greatly appreciated.

Here's the sample code:

SQL script:

CREATE TYPE CodeList
AS TABLE
(
    Code varchar(255) NOT NULL UNIQUE,
    Name varchar(max) NOT NULL,
    GeneratedDate date NULL
);
GO

PRINT 'Created CodeList Type';
GO

CREATE PROCEDURE AddOrUpdateCodes
    @List AS CodeList READONLY
AS
BEGIN
    SET NOCOUNT ON;

    MERGE dbo.Code AS tgt
    USING @List AS src
    ON tgt.Code = src.Code
    WHEN MATCHED THEN
        UPDATE SET Name = src.Name, GeneratedDate = src.GeneratedDate
    WHEN NOT MATCHED THEN
        INSERT (Code, Name, GeneratedDate) VALUES (src.Code, src.Name, src.GeneratedDate);
END
GO

PRINT 'Created AddOrUpdateCodes Stored Procedure';
GO

DECLARE @List AS CodeList;

INSERT INTO @List (Code, Name, GeneratedDate) VALUES ('SQLTEST', 'SQLTEST', '2018-07-30')

EXEC AddOrUpdateCodes @List
GO

SELECT * FROM Symbol;

C# Code:

public int AddOrUpdateCodes(List<Code> codes)
{
    using (var entity = new CodeEntities())
    {
        var dataTable = new DataTable("CodeList");
        using (var reader = ObjectReader.Create(codes, "Code", "Name", "GeneratedDate"))
        {
            dataTable.Load(reader);
        }

        var sqlParameter = new SqlParameter("@List", dataTable);
        sqlParameter.SqlDbType = SqlDbType.Structured;
        sqlParameter.TypeName = "dbo.CodeList";
        var result = entity.Database.ExecuteSqlCommand("AddOrUpdateCodes",
            sqlParameter);
        entity.SaveChanges();
        return result;
    }
}

Upvotes: 2

Views: 176

Answers (1)

Muhammad Saqlain
Muhammad Saqlain

Reputation: 2212

The DbContext.Database.ExecuteSqlCommand() method helps to executes the given DDL/DML command against the database. Try using this line passing SqlParameters:

var result = entity.Database.ExecuteSqlCommand("AddOrUpdateCodes @List",
    sqlParameter);
    entity.SaveChanges();
        return result;

Upvotes: 2

Related Questions