This is a migrated thread and some comments may be shown as answers.

Use EF Stored Procedure for Grid Insert

4 Answers 54 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Nick
Top achievements
Rank 1
Nick asked on 10 May 2012, 09:14 PM
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 

4 Answers, 1 is accepted

Sort by
0
Andrey
Telerik team
answered on 15 May 2012, 03:06 PM
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
If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now.
0
Nick
Top achievements
Rank 1
answered on 15 May 2012, 03:53 PM
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. 

0
Nick
Top achievements
Rank 1
answered on 15 May 2012, 03:56 PM
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
0
Andrey
Telerik team
answered on 16 May 2012, 04:35 PM
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
If you want to get updates on new releases, tips and tricks and sneak peeks at our product labs directly from the developers working on the RadControls for ASP.NET AJAX, subscribe to their blog feed now.
Tags
Grid
Asked by
Nick
Top achievements
Rank 1
Answers by
Andrey
Telerik team
Nick
Top achievements
Rank 1
Share this question
or