How does SqlCommand with parameters work?

Question

public string InsertStudent(Student student)
{
    string message = "";
    SqlConnection connection = new SqlConnection(connectionString);

    string query = "insert into Students values(@regNo, @name, @email, @departmentId)";

    SqlCommand command = new SqlCommand(query, connection);
    command.Parameters.Clear();

    command.Parameters.Add("regNo", SqlDbType.VarChar, 50).Value = student.RegNo;

    //////////////////or//////////////
    command.Parameters.Add("name", SqlDbType.VarChar);
    command.Parameters["name"].Value = student.Name;

    command.Parameters.Add("email", SqlDbType.VarChar);
    command.Parameters["email"].Value = student.Email;

    command.Parameters.Add("departmentId", SqlDbType.Int);
    command.Parameters["departmentId"].Value = student.DepartmentId;

    connection.Open();
    int rowAffected = command.ExecuteNonQuery();
    connection.Close();
}

My question: when I write

command.ExecuteNonQuery()

or

command.ExecuteReader()

how command reference find out the references or memory locations of newly created SqlParameter objects?

May you draw pictures of references of these objects and their relations in heap memory and stack memory?

probably my answer is following image:

What makes you think the different ways of adding a parameter make any difference at all? By the time the compiler's finished, they look like they may well all end out the same anyway. And you really don't want me to draw a picture.. — stuartd, Aug 19 '16 at 20:48
You could use .NET reflector to look at the IL if you're really that curious — reggaeguitar, Aug 19 '16 at 20:53
Your parameter names are missing the @ symbol. It has to match the SQL text which *has* the @ symbol. — LarsTech, Aug 19 '16 at 21:16

score 1 · Answer 1 · answered Aug 19 '16 at 21:13

1

The parameters are stored in command.Parameters. The command uses that collection to enumerate all parameters and send them over the wire.

answered Aug 19 '16 at 21:13

usr

168,620
35
240
369

Alvimar · Answer 2 · 2016-08-20T01:44:17.413

There are a few ways. See this link:

https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters(v=vs.110).aspx

The following example demonstrates how to create a SqlCommand and add parameters:

private static void UpdateDemographics(Int32 customerID,
    string demoXml, string connectionString)
{
    // Update the demographics for a store, which is stored 
    // in an xml column. 
    string commandText = "UPDATE Sales.Store SET Demographics = @demographics "
        + "WHERE CustomerID = @ID;";

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlCommand command = new SqlCommand(commandText, connection);
        command.Parameters.Add("@ID", SqlDbType.Int);
        command.Parameters["@ID"].Value = customerID;

        // Use AddWithValue to assign Demographics.
        // SQL Server will implicitly convert strings into XML.
        command.Parameters.AddWithValue("@demographics", demoXml);

        try
        {
            connection.Open();
            Int32 rowsAffected = command.ExecuteNonQuery();
            Console.WriteLine("RowsAffected: {0}", rowsAffected);
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }
}

How does SqlCommand with parameters work?

2 Answers2