Use EF Stored Procedure for Grid Insert

[Nick]

Hi,

I'm new to the Telerik controls and I'm using Entity Framework. I'm wondering how I can use my insert stored procedure for the insert command of my rad grid? I've seen documentation on how to do manual CRUD via SQLDataSource, but I'm looking for a way to avoid that and use my EF procedure instead.

Fairly new to EF, too, so if anyone has experience implementing this successfully I'd appreciate the help.

Thanks,
Nick 

[Andrey]

Hi,

I answered your question in the support ticket you have opened, but I will post the link here also.

The easiest way to achieve your goal is to use manual CRUD operations with the appropriate command events(Update/Delete/InsertCommand). In the event handlers body you could call manually the necessary stored procedures.

Greetings,
Andrey
the Telerik team

[Nick]

Thanks, Andrey

One other quick question...I've been following this link 
http://www.telerik.com/community/code-library/aspnet-ajax/general/using-related-radcomboboxes-in-radgrid.aspx 

Trying to get some related combo boxes to work within my grid's insert/edit operations.

However, when I used the code converter to take it from C# to VB and pasted it into my page's code behind I get a "SQLDataSource1" is not declared warning on the "Insert" and "Update" commands. For example:

Protected Sub RadGrid1_UpdateCommand(source As Object, e As GridCommandEventArgs)
        Dim editedItem As GridEditableItem = TryCast(e.Item, GridEditableItem)
        Dim UserID As String = editedItem.OwnerTableView.DataKeyValues(editedItem.ItemIndex)("UserID").ToString()
        Dim continentID As String = DirectCast(editedItem("ContinentName").FindControl("rcbProductType"), RadComboBox).SelectedValue
        Dim countryID As String = DirectCast(editedItem("CountryName").FindControl("rcbManf"), RadComboBox).SelectedValue
        Dim cityID As String = DirectCast(editedItem("CityName").FindControl("rcbCities"), RadComboBox).SelectedValue
        Dim userName As String = DirectCast(editedItem("UserName").FindControl("txtUserName"), RadTextBox).Text
        Dim query As String = "Update Users SET ContinentID=" + continentID + ", CountryID=" + countryID + ", CityID=" + cityID + ", [Name]='" + userName + "'" + " Where ID=" + UserID
        SqlDataSource1.UpdateCommand = query
        SqlDataSource1.Update()
    End Sub

The error I get is "SqlDataSource1" is not declared. 

[Nick]

Here's the whole vb code behind for the page. I should mention I do have "SqlDataSource1" on my aspx page.

Imports System
Imports System.Data
Imports System.Configuration
Imports System.Collections
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls
Imports Telerik.Web.UI
Imports System.Data.SqlClient

Public Class Products
    Inherits System.Web.UI.Page


    Private Sub RadGrid1_ItemCommand(ByVal source As Object, ByVal e As Telerik.Web.UI.GridCommandEventArgs) Handles RadGrid1.ItemCommand
        Dim QTYRecColumn As GridBoundColumn = CType(e.Item.OwnerTableView.GetColumnSafe("QTY_REC"), GridBoundColumn)
        If (e.CommandName = RadGrid.InitInsertCommandName) Then
            e.Canceled = True
            If (e.Item.OwnerTableView.EditMode = GridEditMode.InPlace) Then
                QTYRecColumn.Display = True
            End If
            QTYRecColumn.ReadOnly = False
            e.Item.OwnerTableView.InsertItem()
            Dim insertedItem As GridEditableItem = RadGrid1.MasterTableView.GetInsertItem()
            CType(insertedItem("QTY_REC").Controls(0), TextBox).Text = String.Empty
        ElseIf (e.CommandName = RadGrid.EditCommandName Or e.CommandName = RadGrid.CancelCommandName) Then
            QTYRecColumn.ReadOnly = True
            QTYRecColumn.Display = False
        End If
    End Sub
End Class
Partial Class RelatedCombos_Grid
    Inherits System.Web.UI.Page
    Protected Sub Page_Load(sender As Object, e As EventArgs)

    End Sub
    Protected Sub rcbProductType_SelectedIndexChanged(o As Object, e As Telerik.Web.UI.RadComboBoxSelectedIndexChangedEventArgs)
        Dim editedItem As GridEditableItem = TryCast(TryCast(o, RadComboBox).NamingContainer, GridEditableItem)

        Dim ProductTypeCombo As RadComboBox = TryCast(o, RadComboBox)
        Dim ManfCombo As RadComboBox = TryCast(editedItem("ManfName").FindControl("rcbManf"), RadComboBox)
        Dim ModelCombo As RadComboBox = TryCast(editedItem("ModelName").FindControl("rcbModel"), RadComboBox)

        ManfCombo.DataSource = LoadManf(e.Value)
        ManfCombo.DataBind()

        ModelCombo.DataSource = LoadModel(ManfCombo.SelectedValue)
        ModelCombo.DataBind()
    End Sub
    Protected Sub rcbManf_SelectedIndexChanged(o As Object, e As RadComboBoxSelectedIndexChangedEventArgs)
        Dim editedItem As GridEditableItem = TryCast(TryCast(o, RadComboBox).NamingContainer, GridEditableItem)

        Dim ManfCombo As RadComboBox = TryCast(o, RadComboBox)
        Dim ModelCombo As RadComboBox = TryCast(editedItem("ModelName").FindControl("rcbModel"), RadComboBox)

        ModelCombo.DataSource = LoadModel(e.Value)
        ModelCombo.DataBind()
    End Sub
    Protected Function LoadManf(ProductTypeID As String) As DataTable
        Dim connection As New SqlConnection(ConfigurationManager.ConnectionStrings("LEHUBConnStr").ConnectionString)

        'select a country based on the continentID
        Dim adapter As New SqlDataAdapter("SELECT [ID] AS ManfID, [NAME] FROM Manufacturers WHERE ProductTypeID=@ProductTypeID ORDER By Name", connection)
        adapter.SelectCommand.Parameters.AddWithValue("@ProductTypeID", ProductTypeID)

        Dim dt As New DataTable()
        adapter.Fill(dt)

        Return dt
    End Function
    Protected Function LoadModels(ManfID As String) As DataTable
        Dim connection As New SqlConnection(ConfigurationManager.ConnectionStrings("LEHUBConnStr").ConnectionString)

        'select a city based on the countryID
        Dim adapter As New SqlDataAdapter("SELECT * FROM Models WHERE ManfID=@ManfID ORDER By Name", connection)
        adapter.SelectCommand.Parameters.AddWithValue("@ManfID", ManfID)

        Dim dt As New DataTable()
        adapter.Fill(dt)

        Return dt
    End Function
    Protected Sub RadGrid1_ItemDataBound(sender As Object, e As GridItemEventArgs)
        If TypeOf e.Item Is GridEditableItem AndAlso e.Item.IsInEditMode Then
            Dim editedItem As GridEditableItem = TryCast(e.Item, GridEditableItem)

            If e.Item.OwnerTableView.IsItemInserted Then
                Dim ProductTypeCombo As RadComboBox = TryCast(editedItem.FindControl("rcbProductType"), RadComboBox)
                Dim ManfCombo As RadComboBox = TryCast(editedItem.FindControl("rcbManf"), RadComboBox)
                Dim ModelCombo As RadComboBox = TryCast(editedItem.FindControl("rcbModel"), RadComboBox)

                ManfCombo.DataSource = LoadManf(ProductTypeCombo.SelectedValue)
                ManfCombo.DataBind()

                ModelCombo.DataSource = LoadModels(ManfCombo.SelectedValue)
                ModelCombo.DataBind()
            Else
                Dim dataSourceRow As DataRowView = DirectCast(e.Item.DataItem, DataRowView)

                Dim ProductTypeCombo As RadComboBox = TryCast(editedItem.FindControl("rcbProductType"), RadComboBox)
                Dim ManfCombo As RadComboBox = TryCast(editedItem.FindControl("rcbManf"), RadComboBox)
                Dim ModelCombo As RadComboBox = TryCast(editedItem.FindControl("rcbCities"), RadComboBox)

                ManfCombo.DataSource = LoadManf(ProductTypeCombo.SelectedValue)
                ManfCombo.DataBind()
                ManfCombo.SelectedValue = dataSourceRow("ManfID").ToString()

                ModelCombo.DataSource = LoadModels(ManfCombo.SelectedValue)
                ModelCombo.DataBind()
                ModelCombo.SelectedValue = dataSourceRow("ModelID").ToString()
            End If
        End If
    End Sub
    Protected Sub RadGrid1_UpdateCommand(source As Object, e As GridCommandEventArgs)

        Dim editedItem As GridEditableItem = TryCast(e.Item, GridEditableItem)

        Dim UserID As String = editedItem.OwnerTableView.DataKeyValues(editedItem.ItemIndex)("UserID").ToString()

        Dim continentID As String = DirectCast(editedItem("ContinentName").FindControl("rcbProductType"), RadComboBox).SelectedValue
        Dim countryID As String = DirectCast(editedItem("CountryName").FindControl("rcbManf"), RadComboBox).SelectedValue
        Dim cityID As String = DirectCast(editedItem("CityName").FindControl("rcbCities"), RadComboBox).SelectedValue
        Dim userName As String = DirectCast(editedItem("UserName").FindControl("txtUserName"), RadTextBox).Text

        Dim query As String = "Update Users SET ContinentID=" + continentID + ", CountryID=" + countryID + ", CityID=" + cityID + ", [Name]='" + userName + "'" + " Where ID=" + UserID

        SqlDataSource1.UpdateCommand = query
        SqlDataSource1.Update()
    End Sub
    Protected Sub RadGrid1_InsertCommand(source As Object, e As GridCommandEventArgs)
        Dim editedItem As GridEditableItem = TryCast(e.Item, GridEditableItem)

        Dim continentID As String = DirectCast(editedItem("ContinentName").FindControl("rcbProductType"), RadComboBox).SelectedValue
        Dim countryID As String = DirectCast(editedItem("CountryName").FindControl("rcbManf"), RadComboBox).SelectedValue
        Dim cityID As String = DirectCast(editedItem("CityName").FindControl("rcbCities"), RadComboBox).SelectedValue
        Dim userName As String = DirectCast(editedItem("UserName").FindControl("txtUserName"), RadTextBox).Text

        Dim query As String = String.Format("INSERT INTO USERS([Name], ContinentID, CountryID, CityID) VALUES ('{0}', {1}, {2}, {3})", userName, continentID, countryID, cityID)

        SqlDataSource1.InsertCommand = query
        SqlDataSource1.Insert()
    End Sub

End Class

[Andrey]

Hello,

If you have the SqlDatasource in ASPX page then the next reason that could cause such behavior is that the value of the CodeFile and Inherits properties does not match the name of the code-behind file and the name of the class you have specified.

Verify that and check whether the issue is resolved.


All the best,
Andrey
the Telerik team