I just converted some code that updates database with dynamic sql to stored procedure calls. It add many lines of code. The previous code use SqlCommandBuilder to build the insert, update and delete statements from the select statement. The SqlCommandBuilder needs an extra trip to the SQL server to fectch the meta data.

To convert the dynamic SQL call to stored procedure call, we need to create the SqlCommand objects manually and populates its parameters collection. It is important to set the SourceColumn property of the parameter because the SqlDataAdaptor uses the SourceColumn to map the dataset columns to the parameters of the stored procedure.