SQL/C# Get ID of the row affected by Insert

Question

I'm trying to get the ID of the row affected in "real time". I could check it for the parameters used or the last row, but I want to do it with the least delay possible, so that there isn't multiple users mixing information in the tables.

Tried so far:

public int setFileInfo(string fileName, int filePrivacy, string filePassword, string fileDesc, string fileOwner)
{
    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["bitRain"].ConnectionString))
    {
        SqlCommand cmd = new SqlCommand("dbo.Upload", conn);
        cmd.CommandType = System.Data.CommandType.StoredProcedure;

        if(!String.IsNullOrEmpty(filePassword))
        {
            filePassword = FormsAuthentication.HashPasswordForStoringInConfigFile(filePassword, "MD5");
        }

        try
        {
            conn.Open();

            cmd.Parameters.Add("@fileName", fileName);
            cmd.Parameters.Add("@filePrivacy", filePrivacy);
            cmd.Parameters.Add("@filePassword", filePassword);
            cmd.Parameters.Add("@fileDescription", fileDesc);
            cmd.Parameters.Add("@fileOwner", fileOwner);

            int fileID = (int)cmd.ExecuteScalar();
            return fileID;
        }
        catch (Exception ex)
        { }
        finally
        {
            conn.Close();
        }

        return -1;
    }
}

Stored Procedure:

CREATE PROCEDURE [dbo].[Upload]
@fileName nvarchar(20),
@filePrivacy int,
@filePassword nvarchar(50),
@fileDescription nvarchar(200),
@fileOwner nvarchar(14)

AS
INSERT INTO Files ([FileName], FilePrivacy, FilePassword, FileDescription, FileOwner)
VALUES (@fileName, @filePrivacy, @filePassword, @fileDescription, @fileOwner)
RETURN 0

I need some OUTPUT parameter, but I don't know how to use it and msdn examples aren't clear enough for me.

the answer you are looking for is right on this page check the first link in the `Related` section — MethodMan, Jun 26 '13 at 17:07
possible duplicate of [C# calling SQL Server stored procedure with return value](http://stackoverflow.com/questions/6210027/c-sharp-calling-sql-server-stored-procedure-with-return-value) and [SQL Server Stored Procedure Get a value from a row just inserted](http://stackoverflow.com/q/16868986/62576), both from the `Related` list to the right (there's a third at the bottom of the list). Please at least search the site for existing answers, and look at the list of possibly related questions that appears as you enter yours, to see if the question has already been asked and answered here. Thanks. — Ken White, Jun 26 '13 at 17:12
@KenWhite I did search, I just didn't find how it would be similar to my code as I would use it and come out of it with the same knowledge as before. Maybe I didn't read them properly. Plausible. — datalekz, Jun 26 '13 at 20:41
@evilmonstah: Both questions I linked refer to `stored procedure` and `value`, one actually says `return value`, and one says `value from row just inserted`. They also both clearly are visible in the `Related` list to the right, most of which would have been shown to you as you were typing your question. :-) You can always right-click the link and open in a new browser or window to see if it applies before clicking the submit button on your question. — Ken White, Jun 26 '13 at 20:47
@KenWhite I aknowledge that this is a duplicate as you say, I've seen some of the questions, yet I didn't learn from them, it was my mistake for not paying more attention to the questions and answers themselves. :) BUT I learned were I was wrong and a easy way to implement this method, that I couldn't understand from other peoples example or code. — datalekz, Jun 26 '13 at 21:19

score 7 · Accepted Answer · answered Jun 26 '13 at 17:13

Stored Procedure:

CREATE PROCEDURE [dbo].[Upload]
@fileName nvarchar(20),
@filePrivacy int,
@filePassword nvarchar(50),
@fileDescription nvarchar(200),
@fileOwner nvarchar(14),
@id int out

AS
INSERT INTO Files ([FileName], FilePrivacy, FilePassword, FileDescription, FileOwner)
VALUES (@fileName, @filePrivacy, @filePassword, @fileDescription, @fileOwner) 
set @id = SCOPE_IDENTITY()
END

In cs add

cmd.Parameters["@id"].Direction = ParameterDirection.Output;

score 2 · Answer 2 · answered Jun 26 '13 at 17:14

2

try to use SCOPE_IDENTITY function

answered Jun 26 '13 at 17:14

DreamChild

411
2
3

SQL/C# Get ID of the row affected by Insert

2 Answers2