FormView and SQL Datasource is awesome in my opinion. You can just bind your controls to data field in the markup, its great.
Becomes a bit of a challenge when you want all your data related stuff to be done in the Data Access Layer, but hey, you have to bind the data to the UI one way or the other, right.
Anyway, if you want to create your SQLDataSource in the code behind, you can easily do that. Here is an example:
Function GetSQLDataSource() As SqlDataSource
Dim sqlReturn As SqlDataSource = Nothing
Try
sqlReturn = New SqlDataSource
sqlReturn.ConnectionString = MyConnectionString
'Build Select Command and Parameter List
sqlReturn.SelectCommandType = SqlDataSourceCommandType.StoredProcedure
sqlReturn.SelectCommand = "P_SELECT_SP"
With sqlReturn.SelectParameters
.Clear()
.Add(New Parameter("ID", DbType.Int32)
End With
'Build Update Command and Parameter List
sqlReturn.UpdateCommandType = SqlDataSourceCommandType.StoredProcedure
sqlReturn.UpdateCommand = "P_UPDATE_SP"
With sqlReturn.UpdateParameters
.Clear()
.Add(New Parameter("ID", DbType.Int32)
'Build Delete Command and Parameter List
'Build Insert Command and Parameter List
' You get the idea...
Return sqlReturn
Catch ex As Exception
Throw ex
End Try
End Function
The problem is that how does databinding work? It won't work the same way as having the sql data source in the markup. You will not get any values in the update event. Well here is a trick to do it:
For example, to Update, handle the FormView's ItemUpdating event, and then loop through each value in the UpdateParameters list in the sqldatasource, and manually assign each value from e.NewValues list where the parameter name matches.
Private Sub frmData_ItemUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.FormViewUpdateEventArgs) Handles frmData.ItemUpdating
Also, don't forget to assign the sqldatasource as the formview's datasource in each postback, otherwise your formview's datasource would be nothing.