When calling a stored procedure in ADO.NET, you can add parameters using either SqlParameterCollection.Add (explicit type) or AddWithValue (type inferred from the supplied value). Both approaches bind parameters by name and are independent of the parameter order defined in the stored procedure; the main difference is whether you explicitly control the database type and size.

Explicit parameter definition with Add

Use Add when you want full control over the SQL type (and optionally length/precision). This helps prevent subtle type/size mismatches.

using System;
using System.Data;
using System.Data.SqlClient;

class Demo
{
    static void InsertAuditRecord()
    {
        var connStr = "Server=.;Database=HISDB;Trusted_Connection=True;";

        using var conn = new SqlConnection(connStr);
        using var cmd = new SqlCommand("AuditMessageInsert", conn)
        {
            CommandType = CommandType.StoredProcedure
        };

        // Define parameters explicitly
        cmd.Parameters.Add("@Target", SqlDbType.NChar, 20);
        cmd.Parameters.Add("@Description", SqlDbType.NChar, 50);
        cmd.Parameters.Add("@Actor", SqlDbType.NChar, 20);
        cmd.Parameters.Add("@Time", SqlDbType.DateTime);
        cmd.Parameters.Add("@Computer", SqlDbType.NChar, 20);

        // Assign values by name
        cmd.Parameters["@Target"].Value = "ATarget";
        cmd.Parameters["@Description"].Value = "Description";
        cmd.Parameters["@Actor"].Value = "Actor";
        cmd.Parameters["@Time"].Value = DateTime.Now;
        cmd.Parameters["@Computer"].Value = "PC-Computer";

        conn.Open();
        cmd.ExecuteNonQuery();
    }
}

Notes:

• The CommandType must be set to StoredProcedure when invoking a stored procedure.
• Using Add with explicti SqlDbType (and length for character types) avoids implicit conversions and truncation.

Implicit type inference with AddWithValue

AddWithValue captures the parameter name and value in a single call and infers the database type from the value’s .NET type. Parameter order in code does not need to match the stored procedure.

using System;
using System.Data;
using System.Data.SqlClient;

class Demo
{
    static void InsertAuditRecord_WithInference()
    {
        var connStr = "Server=.;Database=HISDB;Trusted_Connection=True;";

        using var conn = new SqlConnection(connStr);
        using var cmd = new SqlCommand("AuditMessageInsert", conn)
        {
            CommandType = CommandType.StoredProcedure
        };

        // Add parameters with inferred types (order here is arbitrary)
        cmd.Parameters.AddWithValue("@Actor", "Actor");
        cmd.Parameters.AddWithValue("@Target", "Target");
        cmd.Parameters.AddWithValue("@Description", "Description");
        cmd.Parameters.AddWithValue("@Computer", "Computer");
        cmd.Parameters.AddWithValue("@Time", DateTime.Now);

        conn.Open();
        cmd.ExecuteNonQuery();
    }
}

Key differences:

• Add requires you to specify SqlDbType (and optionally size), then assign Value; AddWithValue assigns both name and value at once.
• AddWithValue chooses the parameter type automatically. This is convenient, but it may infer sizes or types that do not perfect match your schema (for example, mapping strings to NVARCHRA(MAX) in some providers). When type/size fidelity matters, prefer Add with explicit definitions.