C# SQL存储程序删除异常：如何仅删除单条数据而非所有列？

Hey there! Let's break down what's going on here and fix that accidental full-table delete issue you're facing.

The reason all your data gets deleted every time is almost certainly because your DELETE statement doesn't include a WHERE clause to target specific rows. A bare DELETE FROM YourTableName will wipe every single row in the table—definitely not what you want!

Here's how to fix this and safely delete only a single row:

1. Add a precise WHERE clause to your DELETE statement #

Always use a unique identifier (like a primary key) to target the exact row you want to delete. Primary keys are guaranteed to be unique, so you'll never accidentally delete multiple rows.

Example SQL for deleting a single row:

DELETE FROM YourTargetTable 
WHERE PrimaryKeyColumn = @TargetRowId; -- @TargetRowId is the unique ID of the row to delete

2. Wrap this in a parameterized stored procedure #

To make this safe (prevent SQL injection) and reusable, create a stored procedure like this:

CREATE PROCEDURE DeleteSingleTableRow
    @TargetRowId INT -- Adjust the data type to match your primary key (e.g., UNIQUEIDENTIFIER for GUIDs)
AS
BEGIN
    SET NOCOUNT ON; -- Suppress extra row count messages

    -- Verify the row exists first (optional but helpful for debugging)
    IF EXISTS(SELECT 1 FROM YourTargetTable WHERE PrimaryKeyColumn = @TargetRowId)
    BEGIN
        DELETE FROM YourTargetTable 
        WHERE PrimaryKeyColumn = @TargetRowId;
    END
    ELSE
    BEGIN
        -- Optional: Throw an error or return a message if the row doesn't exist
        RAISERROR('Target row not found', 16, 1);
    END
END

3. Call the stored procedure from C# (with parameterization) #

In your C# code, pass the target row ID as a parameter to avoid mistakes and SQL injection:

using (SqlConnection connection = new SqlConnection("YourDatabaseConnectionString"))
{
    connection.Open();
    using (SqlCommand deleteCmd = new SqlCommand("DeleteSingleTableRow", connection))
    {
        deleteCmd.CommandType = CommandType.StoredProcedure;
        // Add the parameter matching the stored procedure's input
        deleteCmd.Parameters.AddWithValue("@TargetRowId", 42); // Replace with your actual row ID

        try
        {
            int rowsAffected = deleteCmd.ExecuteNonQuery();
            if (rowsAffected == 1)
            {
                Console.WriteLine("Single row deleted successfully!");
            }
            else if (rowsAffected == 0)
            {
                Console.WriteLine("No matching row found—nothing was deleted.");
            }
        }
        catch (SqlException ex)
        {
            Console.WriteLine($"Error deleting row: {ex.Message}");
        }
    }
}

• Always test with SELECT first: Before running a DELETE, run SELECT * FROM YourTable WHERE PrimaryKeyColumn = @TargetRowId to confirm you're targeting the right row.
• Use transactions if needed: If you're making multiple changes, wrap them in a transaction so you can roll back if something goes wrong.
• Never run unfiltered DELETE/UPDATE: Unless you explicitly intend to modify every row, always include a WHERE clause.

内容的提问来源于stack exchange，提问作者Codex Guy