C# - Call stored procedure with type

Question

I want to call a stored procedure from my database, but it also needs a List of attributes as a type and I placed a cursor on that type. But everytime I execute the command I get an exception back that the cursor is not open.

What I have now already

        using (Connection = new SqlConnection(ConnectionString))
        {
            Connection.Open();

            using (Command = new SqlCommand())
            {
                Command.Connection = Connection;
                Command.CommandText = "CreateCharacter";
                Command.CommandType = CommandType.StoredProcedure;
                Command.Parameters.AddWithValue("@Skills", skills);
                Command.Parameters.AddWithValue("@AccountId", accountId);
                Command.Parameters.AddWithValue("@ClassId", masteryId);
                Command.Parameters.AddWithValue("@RaceId", raceId);
                Command.Parameters.AddWithValue("@Name", name);

                try
                {
                    return character = LoadCharacter(Convert.ToInt32(Command.ExecuteScalar()));
                }

                catch (Exception)
                {
                    return null;
                }
            }
        }

Type

CREATE TYPE dbo.AttributeList AS TABLE
(
    AttributeId integer,
    [Level] integer
);
GO

Stored Procedure

CREATE PROCEDURE dbo.CreateCharacter (@Attributes AS dbo.AttributeList 
READONLY, @AccountId integer, @ClassId integer, @RaceId integer, @Name 
varchar(12))
AS
BEGIN
DECLARE AttributeCursor CURSOR FOR
SELECT
    AttributeId,
    [Level]
FROM @Attributes

INSERT INTO Character
    VALUES (@ClassId, @RaceId, NULL, @Name, 1, 0, 0)
DECLARE @Id integer
DECLARE @AttributeId integer
DECLARE @AttributeLevel integer
SET @Id = IDENT_CURRENT('Character');

UPDATE Account
SET CharacterId = @Id
WHERE Id = @AccountId

WHILE (@@FETCH_STATUS = 0)
BEGIN
    INSERT INTO Attribute_Character
        VALUES (@AttributeId, @Id, @AttributeLevel)
    FETCH NEXT FROM AttributeCursor INTO @AttributeId, @AttributeLevel
END
RETURN @Id;
END

Not related to your problem, but ident_current is probably not what you want, see https://stackoverflow.com/questions/1920558/what-is-the-difference-between-scope-identity-identity-identity-and-ide — James Z, Jun 04 '17 at 16:02
Amazingly, your error is because you don't open the cursor (or close or deallocate it). But you can and should eliminate the cursor anyway. Look into using OUTPUT and inserted.id so you can use set based operations. — Crowcoder, Jun 04 '17 at 18:15

hardkoded · Accepted Answer · 2017-06-04T17:06:14.090

0

In order to send a Table param you just have to build a DataTable on the C# side:

var table = new DataTable();
table.Columns.Add("AttributeID", typeof(int));
table.Columns.Add("Level", typeof(int));

//Add data
table.Rows.Add(new []{1, 2});

//More code
Command.Parameters.AddWithValue("@Attributes", table);

And I think you don't need a cursor in the Procedure. Just insert the data:

INSERT INTO Attribute_Character(AttributeId, AttributeLevel)
SELECT AttributeId, AttributeLevel
FROM @Attributes

edited Jun 04 '17 at 17:06

answered Jun 04 '17 at 16:53

hardkoded

18,915
3
52
64

I dind't downvote your answer, I upvoted it and accepted it as an answer. Thanks! – iub3rskillz Jun 04 '17 at 18:16
Still wrong. I downvoted. You do not NEED a DataTable - there is a better more direct way, which is what DataTable actually uses. – TomTom Jun 04 '17 at 18:24
Cool @tomtom waiting for your answer then – hardkoded Jun 04 '17 at 18:25
@TomTom, downvotes are for unhelpful answers, not answers that aren't the single best way. And if you are talking about using IEnumerable of SqlDatarecord then IMO that is not more direct. – Crowcoder Jun 04 '17 at 18:35

C# - Call stored procedure with type

1 Answers1