CODEWITHSUNDEEP
c#stored-procedures
user11410351
user11410351

Reputation:

Display @@ROWCOUNT in C#

How to display @@ROWCOUNT in method after I execute a stored procedure? I want to count how many rows are affected after INSERT and UPDATE statements have been executed:

CREATE PROCEDURE [dbo].[ADProcTemp]
    @Username varchar(250),
    @DisplayName varchar(70),
    @isEnabled tinyint,
    @PassNevExp tinyint,
    @rowcount int OUT
AS
    --SET NOCOUNT ON
BEGIN
    IF NOT EXISTS (SELECT TOP 1 PrezimeIme FROM [dbo].[tblZaposleni_AD] WITH (NOLOCK) 
                   WHERE NetworkLogin = @Username)
    BEGIN
        IF(@isEnabled = 1 OR @PassNevExp = 1)
             INSERT INTO [dbo].[tblZaposleni_AD](NetworkLogin, PrezimeIme, Status, PassNevExp)
             VALUES (@Username, @DisplayName, @isEnabled, @PassNevExp)

        SET @rowcount = @@ROWCOUNT;
        RETURN @@ROWCOUNT;   
    END
    ELSE
    BEGIN
        UPDATE [dbo].[tblZaposleni_AD]
        SET Status = @isEnabled,        
            PassNevExp = @PassNevExp
        WHERE NetworkLogin = @Username 
          AND (Status <> @isEnabled OR [PassNevExp] <>@PassNevExp)
    END
END

C# code:

public void ExcStrPrc(string Username, string DisplayName, bool isEnable, bool PassNevExp)
{
    SqlConnection conn = new SqlConnection(@"Data Source=(LocalDb)\MSSQLLocalDB;Initial Catalog=DesignSaoOsig1;Integrated Security=True");
    conn.Open();

    SqlCommand cmd = new SqlCommand("ADProcTemp", conn);          
    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.AddWithValue("@Username", Username.ToString().Trim());
    cmd.Parameters.AddWithValue("@DisplayName", DisplayName.ToString().Trim());
    cmd.Parameters.AddWithValue("@isEnabled", Convert.ToInt32(isEnable));
    cmd.Parameters.AddWithValue("@PassNevExp", Convert.ToInt32(PassNevExp));
    //cmd.Parameters.AddWithValue("@rowcount", SqlDbType.Int);

    SqlParameter rowsAffected = new SqlParameter("@rowcount", SqlDbType.Int);
    rowsAffected.Direction = ParameterDirection.Output;
    cmd.Parameters.Add(rowsAffected);

    int k = cmd.ExecuteNonQuery();

    if (k != 0)
    {
        Console.WriteLine("Uspjesno insertovano ! ");
    }

    int result = Convert.ToInt32(cmd.ExecuteScalar());          
    conn.Close();
}

Upvotes: 0

Views: 1319

Answers (1)

sr28
sr28

Reputation: 5106

You need to get the value of the OUT parameter once the sp has been executed:

var effectedRows = Convert.ToInt32(cmd.Parameters["@rowCount"].Value);

Upvotes: 2

Related Questions