Obsidian Obsidian -4 years ago 72
SQL Question

Why does the sqlParameter request a sourceColumn?

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")


enter image description here

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download