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

Dynamically Selecting Rows from LinqDatasource

3 Answers 296 Views

Grid

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

gc_0620 asked on 15 Mar 2012, 10:03 PM

Folks, Using ASP.Net Ajax Q1 2012 with VS 2010.

I am using below link as a prototype.

http://demos.telerik.com/aspnet-ajax/grid/examples/performance/linq/defaultcs.aspx

My question's are:

1) I would like to have an option just to show 1st 2000 rows or all rows in the Form based on user input. I will create a Text Box where users will enter # of rows to Show in Radgrid. if Textbox is null then show all rows.
.
2) Possible to select rows (may be 2000 or all rows) from mutiple tables (i.e. Products and Category) based on a certain Catergory Description? Ex: I will have a Text Box in the form for users to enter Category description and based on that Text Box value, show only Radgrid rows that meets the criteria. But insert/update/deletions will be limitted to Products Table only not in Category Table.

The soutions I am looking from LINQ to SQL, not SQL.

Thanks

gc_0620

3 Answers, 1 is accepted

answered on 21 Mar 2012, 09:01 AM

Hi,

Could you elaborate on what problems exactly you have in achieving this with RadGrid. With advanced data-binding the first part of your requirement depends only on the way you query your datasource. You just need to handle the change of value in the textbox, use its value to query your DB, set the result as DataSource of RadGrid and rebind it.
As for editing, again it depends on your own logic how you are going to form the update statements in the UpdateCommand event.

Kind regards,
Tsvetina
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.

answered on 22 Mar 2012, 04:24 PM

RadgridFiltering.jpg

Hi Tsvetina

Basically I would like to filter the Grid Linq Datasource at run time based on the Selection of a Rad Combo Box. Please refer to attached screen shot file for my desire. For Example: if in RadcomboBox I select the Category Name = Seafood, at runtime the Grid will Filter only rows with that Category Name (i.e. CategoryId = 8, linking column between Categories and Products Table for that Category Name). if the selection in RadComboBox Category Name = All, the RadGrid will show all Rows from Products Table. Either way (Grid is filtered or not), I would like to perform Insert/Update/Delete as usual.

I modified below link to fit my needs.

http://demos.telerik.com/aspnet-ajax/grid/examples/dataediting/programaticlinqupdates/defaultcs.aspx

If Datasource is SQLDatasource, I can easily filter the Grid at run time but having some hardtime to Filter the LINQDatasource at run time. Below is my complete code. Another question is it possible to have Parent Table LinqDatasource and Child Table SQLDatasource?

Thanks

gc_0620

<%@ Page Language="C#" Inherits="Telerik.GridExamplesCSharp.DataEditing.ProgramaticLinqUpdates.DefaultCS"

CodeFile="defaultcs.aspx.cs" %>

<%@ Register TagPrefix="telerik" Namespace="Telerik.Web.UI" Assembly="Telerik.Web.UI" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

</head>

<body class="BODY">

<form id="form1" runat="server">

<div>

<telerik:RadScriptManager ID="RadScriptManager1" runat="server" />

<telerik:RadCodeBlock ID="RadCodeBlock1" runat="server">

<script type="text/javascript">

function rowDblClick(sender, eventArgs) {

sender.get_masterTableView().editItem(eventArgs.get_itemIndexHierarchical());

}

</script>

</telerik:RadCodeBlock>

<telerik:RadAjaxManager runat="server" ID="RadAjaxManager1" DefaultLoadingPanelID="RadAjaxLoadingPanel1">

<AjaxSettings>

<telerik:AjaxSetting AjaxControlID="RadGrid1">

<UpdatedControls>

<telerik:AjaxUpdatedControl ControlID="RadGrid1" />

<telerik:AjaxUpdatedControl ControlID="RadWindowManager1" />

<telerik:AjaxUpdatedControl ControlID="RadInputManager1" />

</UpdatedControls>

</telerik:AjaxSetting>

</AjaxSettings>

</telerik:RadAjaxManager>

<telerik:RadAjaxLoadingPanel runat="server" ID="RadAjaxLoadingPanel1" />

<br /> <br />

<asp:Label ID="Label1" runat="server" Text="Filter Grid by Category Name: "></asp:Label>

<telerik:RadComboBox ID="RadComboBox1" runat="server" AppendDataBoundItems="true" AutoPostBack = "true"

DataSourceID="SqlDataSource1" DataTextField="CategoryName"

DataValueField="CategoryID" Skin="Office2007">

<Items>

<telerik:RadComboBoxItem Text="All" Value="0" Selected="true" />

</Items>

</telerik:RadComboBox>

<br /><br /> <br />

<telerik:RadGrid runat="server" ID="RadGrid1" AutoGenerateColumns="false"

AllowPaging="true" OnNeedDataSource="RadGrid1_NeedDataSource" OnUpdateCommand="RadGrid1_UpdateCommand"

OnItemCreated="RadGrid1_ItemCreated" OnDeleteCommand="RadGrid1_DeleteCommand"

OnInsertCommand="RadGrid1_InsertCommand">

<MasterTableView DataKeyNames="ProductID" CommandItemDisplay="Top" InsertItemPageIndexAction="ShowItemOnCurrentPage">

<Columns>

<telerik:GridEditCommandColumn ButtonType="ImageButton" />

<telerik:GridBoundColumn DataField="ProductID" HeaderText="Product ID" ReadOnly="true"

ForceExtractValue="Always" ConvertEmptyStringToNull="true" />

<telerik:GridBoundColumn DataField="ProductName" HeaderText="Product Name" />

<telerik:GridBoundColumn DataField="CategoryID" HeaderText="Category Id" />

<telerik:GridBoundColumn DataField="UnitsInStock" HeaderText="Units In Stock" />

<telerik:GridBoundColumn DataField="UnitPrice" HeaderText="Price" DataFormatString="{0:c}"/>

<telerik:GridButtonColumn ConfirmText="Delete this product?" ConfirmDialogType="RadWindow"

ConfirmTitle="Delete" ButtonType="ImageButton" CommandName="Delete" />

</Columns>

<EditFormSettings>

<EditColumn ButtonType="ImageButton" />

</EditFormSettings>

</MasterTableView>

<PagerStyle Mode="NextPrevAndNumeric" />

<ClientSettings>

<ClientEvents OnRowDblClick="rowDblClick" />

</ClientSettings>

</telerik:RadGrid>

<telerik:RadInputManager runat="server" ID="RadInputManager1" Enabled="true">

<telerik:TextBoxSetting BehaviorID="TextBoxSetting1">

</telerik:TextBoxSetting>

<telerik:NumericTextBoxSetting BehaviorID="NumericTextBoxSetting1" Type="Currency"

AllowRounding="true" DecimalDigits="2">

</telerik:NumericTextBoxSetting>

<telerik:NumericTextBoxSetting BehaviorID="NumericTextBoxSetting2" Type="Number"

AllowRounding="true" DecimalDigits="0" MinValue="0">

</telerik:NumericTextBoxSetting>

</telerik:RadInputManager>

<telerik:RadWindowManager ID="RadWindowManager1" runat="server" />

<br />

<asp:SqlDataSource ID="SqlDataSource1" runat="server"

ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"

SelectCommand="SELECT CategoryName, CategoryID FROM Categories ORDER BY CategoryName"></asp:SqlDataSource>

</div>

</form>

</body>

</html>

---------------------------

using System;

using System.Collections;

using System.Data.Linq;

using System.Web.UI;

using System.Web.UI.WebControls;

using LinqToSql;

using System.Linq;

using Telerik.Web.UI;

namespace Telerik.GridExamplesCSharp.DataEditing.ProgramaticLinqUpdates

{

public partial class DefaultCS : Page

{

private NorthwindDataContext _dataContext;

protected NorthwindDataContext DbContext

{

get

{

if (_dataContext == null)

{

_dataContext = new NorthwindDataContext();

}

return _dataContext;

}

public override void Dispose()

{

if (_dataContext != null)

{

_dataContext.Dispose();

}

base.Dispose();

}

protected void RadGrid1_NeedDataSource(object source, GridNeedDataSourceEventArgs e)

{

RadGrid1.DataSource = DbContext.Products;

}

protected void RadGrid1_UpdateCommand(object source, GridCommandEventArgs e)

{

var editableItem = ((GridEditableItem)e.Item);

var productId = (int)editableItem.GetDataKeyValue("ProductID");

//retrive entity form the Db

var product = DbContext.Products.Where(n => n.ProductID == productId).FirstOrDefault();

if (product != null)

{

//update entity's state

editableItem.UpdateValues(product);

try

{

//submit chanages to Db

DbContext.SubmitChanges();

}

catch (System.Exception)

{

ShowErrorMessage();

}

/// <summary>

/// Shows a <see cref="RadWindow"/> alert if an error occurs

/// </summary>

private void ShowErrorMessage()

{

RadAjaxManager1.ResponseScripts.Add(string.Format("window.radalert(\"Please enter valid data!\")"));

}

protected void RadGrid1_ItemCreated(object sender, GridItemEventArgs e)

{

if (e.Item is GridEditableItem && (e.Item.IsInEditMode))

{

GridEditableItem editableItem = (GridEditableItem)e.Item;

SetupInputManager(editableItem);

}

private void SetupInputManager(GridEditableItem editableItem)

{

// style and set ProductName column's textbox as required

var textBox =

((GridTextBoxColumnEditor)editableItem.EditManager.GetColumnEditor("ProductName")).TextBoxControl;

textBox.ID = "TextBox1";

InputSetting inputSetting = RadInputManager1.GetSettingByBehaviorID("TextBoxSetting1");

inputSetting.TargetControls.Add(new TargetInput(textBox.UniqueID, true));

inputSetting.InitializeOnClient = true;

inputSetting.Validation.IsRequired = true;

// style UnitPrice column's textbox

textBox =

((GridTextBoxColumnEditor)editableItem.EditManager.GetColumnEditor("UnitPrice")).TextBoxControl;

textBox.ID = "TextBox2";

inputSetting = RadInputManager1.GetSettingByBehaviorID("NumericTextBoxSetting1");

inputSetting.InitializeOnClient = true;

inputSetting.TargetControls.Add(new TargetInput(textBox.UniqueID, true));

// style UnitsInStock column's textbox

textBox =

((GridTextBoxColumnEditor)editableItem.EditManager.GetColumnEditor("UnitsInStock")).TextBoxControl;

textBox.ID = "TextBox3";

inputSetting = RadInputManager1.GetSettingByBehaviorID("NumericTextBoxSetting2");

inputSetting.InitializeOnClient = true;

inputSetting.TargetControls.Add(new TargetInput(textBox.UniqueID, true));

}

protected void RadGrid1_InsertCommand(object source, GridCommandEventArgs e)

{

var editableItem = ((GridEditableItem)e.Item);

//create new entity

var product = new LinqToSql.Product();

//populate its properties

Hashtable values = new Hashtable();

editableItem.ExtractValues(values);

product.ProductName = (string)values["ProductName"];

if (values["UnitsInStock"] != null)

{

product.UnitsInStock = short.Parse(values["UnitsInStock"].ToString());

}

if (values["UnitPrice"] != null)

{

product.UnitPrice = decimal.Parse(values["UnitPrice"].ToString());

}

DbContext.Products.InsertOnSubmit(product);

try

{

//submit chanages to Db

DbContext.SubmitChanges();

}

catch (System.Exception)

{

ShowErrorMessage();

}

protected void RadGrid1_DeleteCommand(object source, GridCommandEventArgs e)

{

var productId = (int)((GridDataItem)e.Item).GetDataKeyValue("ProductID");

//retrive entity form the Db

var product = DbContext.Products.Where(n => n.ProductID == productId).FirstOrDefault();

if (product != null)

{

//add the category for deletion

DbContext.Products.DeleteOnSubmit(product);

try

{

//submit chanages to Db

DbContext.SubmitChanges();

}

catch (System.Exception)

{

ShowErrorMessage();

}

answered on 28 Mar 2012, 07:11 AM

Hi,

You can see how filtering based on a dropdown value can be performed with LinqDataSource here:
http://weblogs.asp.net/scottgu/archive/2007/07/16/linq-to-sql-part-5-binding-ui-using-the-asp-linqdatasource-control.aspx

You can see that this same datasource control allows editing at the same time.

As for binding the grid to different types of datasource controls, this should be fine, as long as you correctly pass the key value used to filter the hierarchy.

All the best,
Tsvetina
the Telerik team