Why does the sqlParameter request a sourceColumn?

Question

I was examining a sqlParameter example from Microsoft and am trying to understand:

What are the reasons and benefits for specifying a SourceColumn?

The sql command already specifies the target column.

    command = New SqlCommand( _
        "INSERT INTO Customers (CustomerID, CompanyName) " & _
        "VALUES (@CustomerID, @CompanyName)", connection)

    command.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID")
    command.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName")

i don't get the question. The adapter has to know somehow which column to use in order to set values. — A ツ, Feb 05 '16 at 09:29
@Aツ: imo this is a fair question. I've never used this property although i use ADO.NET for 14 years — Tim Schmelter, Feb 05 '16 at 09:30
@Aツ In the example above the insert statement provides the source column. — DreamTeK, Feb 05 '16 at 09:32
"The sql command already specifies the target column." - yes, but the name of this parameter/property is *source* column. Which column *from the `DataSet`* should be used to populate this parameter when the `DataAdapter` is automatically populating and using the command that this parameter is attached to? — Damien_The_Unbeliever, Feb 05 '16 at 10:08

score 2 · Accepted Answer · edited Jun 12 '17 at 08:17

Specifying the SourceColumn in this instance means specifying the column name property (DataColumn.ColumnName) in a DataTable or Dataset. This is only important when you're using a SqlDataAdapter object in conjunction with your SqlCommand like so:

--first, fill a DataTable with data from the database:
Dim dt As New DataTable
Dim cmdSelect = New SqlCommand("SELECT CustomerID, CompanyName FROM Customers;", connection)
Dim daCustomers As New SqlDataAdapter
daCustomers.SelectCommand = cmdSelect
daCustomers.Fill(dt)

--Now our DataTable has two columns: "CustomerID" and "CompanyName"
--We can help out our DataAdapter by telling it which columns in the database
--correspond with which columns in the DataTable 
--which is what you've done with your fourth argument of .Parameters.Add()
command = New SqlCommand( _
    "INSERT INTO Customers (CustomerID, CompanyName) " & _
    "VALUES (@CustomerID, @CompanyName)", connection)

command.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID")
command.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName")
daCustomers.InsertCommand = command

If we do it this way, then we won't have to ever specify the values of the two parameters (in this case, when the InsertCommand fires), because the data adapter will just look at the values in the appropriate DataColumn & DataRow automatically when you call daCustomers.Update(dt).

Likewise, if you want your SqlDataAdapter to be even more useful, you would want to also specify the SourceColumn for the parameters of your defined UpdateCommand and DeleteCommands. Note that a lot of people prefer to use a SqlCommandBuilder object to automatically configure their SqlDataAdapters for this and other simple adapters without specifying much of anything for their SqlCommand objects, but I prefer this more explicit approach for anything but the simplest cases.

I am unaware of any benefit to be gained by specifying SourceColumn when you're not using a SqlDataAdapter with your SqlCommand.

Why does the sqlParameter request a sourceColumn?

1 Answers1