Geeks With Blogs
Razi Syed Professional Developer

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

            sqlReturn = New SqlDataSource
            sqlReturn.ConnectionString = MyConnectionString

            'Build Select Command and Parameter List
            sqlReturn.SelectCommandType = SqlDataSourceCommandType.StoredProcedure
            sqlReturn.SelectCommand = "P_SELECT_SP"
            With sqlReturn.SelectParameters
                .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
                .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.


Posted on Friday, September 30, 2011 12:12 PM ASP .Net , ADO .Net | Back to top

Comments on this post: SQLDataSource in code behind?

No comments posted yet.
Your comment:
 (will show your gravatar)

Copyright © razisyed | Powered by: