[Solved] I want to insert a record in DB and then need to return a row

by


One way to do what you want is to modify @voucherNo to be an OUTPUT parameter, and add a new OUTPUT parameter to your query to return the value of SCOPE_IDENTITY().

@voucherNo varchar(max)
@ScopeIdentity numeric(38,0)

And modify that last SELECT statement to set the value of @ScopeIdentity parameter.

SELECT @ScopeIdentity = SCOPE_IDENTITY()

Then use SqlCommand.ExecuteNonQuery to execute the query, and on completion, read the values from the output parameters.

using (SqlCommand command = new SqlCommand(connection, commandText))
{
    int rowsAffected = command.ExecuteNonQuery();
    if (rowsAffected > 0)
    {
        var voucherNo = (string)command.Parameters["@voucherNo].Value;
        var identity = (decimal)command.Parameters["@ScopeIdentity"].Value;
    }
}

solved I want to insert a record in DB and then need to return a row