製品版のみの機能
グリッド - DataTable のバインド
このサンプルは CTP 機能を使用しています。製品版では、API や動作が変更される場合があります。
このサンプルは、より大きい画面サイズのためにデザインされました。
モバイル デバイスで画面を回転、フル サイズ表示、またはその他のデバイスにメールで送信します。
ASP.NET MVC ヘルパーを使用して、igGrid を直接 DataTable オブジェクトにバインドできます。
コード ビュー
クリップボードへコピー
@using Infragistics.Web.Mvc
@using IgniteUI.SamplesBrowser.Models.Northwind
@using System.Data
<!DOCTYPE html>
<html>
<head>
<title></title>
<!-- Ignite UI for jQuery Required Combined CSS Files -->
<link href="http://cdn-na.infragistics.com/igniteui/2023.2/latest/css/themes/infragistics/infragistics.theme.css" rel="stylesheet" />
<link href="http://cdn-na.infragistics.com/igniteui/2023.2/latest/css/structure/infragistics.css" rel="stylesheet" />
<script src="http://ajax.aspnetcdn.com/ajax/modernizr/modernizr-2.8.3.js"></script>
<script src="http://code.jquery.com/jquery-1.11.3.min.js"></script>
<script src="http://code.jquery.com/ui/1.11.1/jquery-ui.min.js"></script>
<!-- Ignite UI for jQuery Required Combined JavaScript Files -->
<script src="http://cdn-na.infragistics.com/igniteui/2023.2/latest/js/infragistics.core.js"></script>
<script src="http://cdn-na.infragistics.com/igniteui/2023.2/latest/js/infragistics.lob.js"></script>
</head>
<body>
<!-- Note: When grid is defined in the View and DataTable is used as DataSource, grid columns can only be auto-generated -->
@(Html.Infragistics().Grid<System.Data.DataTable>()
.ID("grid1")
.Height("400px")
.Width("100%")
.AutoGenerateColumns(true)
.Features(feature =>
{
feature.Responsive()
.EnableVerticalRendering(false)
.ReactOnContainerWidthChanges(true)
.ColumnSettings(cs =>
{
cs.ColumnSetting().ColumnKey("CustomerID").Classes("ui-hidden-phone");
cs.ColumnSetting().ColumnKey("Region").Classes("ui-hidden-phone");
cs.ColumnSetting().ColumnKey("Address").Classes("ui-hidden-phone");
cs.ColumnSetting().ColumnKey("City").Classes("ui-hidden-phone");
cs.ColumnSetting().ColumnKey("Fax").Classes("ui-hidden-phone");
});
feature.Sorting().Type(OpType.Remote).Mode(SortingMode.Multiple);
feature.Paging().Type(OpType.Remote);
})
.DataSource(Model)
.DataSourceUrl(Url.Action("datatable-binding"))
.DataBind()
.Render()
)
</body>
</html>
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Mvc;
using System.Linq;
using Infragistics.Web.Mvc;
using IgniteUI.SamplesBrowser.Models.Repositories;
using IgniteUI.SamplesBrowser.Application.Data;
using IgniteUI.SamplesBrowser.Models.Northwind;
using System.Collections.Generic;
using System;
using System.Collections.Specialized;
using System.Text.RegularExpressions;
using System.Web;
using LinqToTwitter;
using System.Reflection;
using System.Linq.Expressions;
using System.Text;
namespace IgniteUI.SamplesBrowser.Controllers
{
public class GridController : Controller
{
private DataTable GetCustomers
{
get
{
if (Session["Customers"] == null)
{
Session["Customers"] = GetCustomerDataTable();
}
return (DataTable)Session["Customers"];
}
}
[GridDataSourceAction]
[ActionName("datatable-binding")]
public ActionResult BasicMvcHelper()
{
DataTable customers = this.GetCustomers;
NameValueCollection queryString = HttpUtility.ParseQueryString(Request.QueryString.ToString());
// check the query string for sorting expressions
List<SortExpression> sortExpressions = BuildSortExpressions(queryString, "sort", true);
DataView dv = customers.DefaultView;
if (sortExpressions.Count > 0)
{
String sortExpression = "";
foreach (SortExpression expr in sortExpressions)
{
sortExpression += expr.Key + " " + (expr.Mode == SortMode.Ascending ? "asc" : "desc") + ",";
}
dv.Sort = sortExpression.Substring(0, sortExpression.Length - 1);
}
return View("datatable-binding", dv.ToTable());
}
public List<SortExpression> BuildSortExpressions(NameValueCollection queryString, string sortKey, bool isTable)
{
List<SortExpression> expressions = new List<SortExpression>();
List<string> sortKeys = new List<string>();
foreach (string key in queryString.Keys)
{
if (!string.IsNullOrEmpty(key) && key.StartsWith(sortKey))
{
SortExpression e = new SortExpression();
e.Key = key.Substring(key.IndexOf("(")).Replace("(", "").Replace(")", "").Split(':')[0];
e.Logic = "AND";
e.Mode = queryString[key].ToLower().StartsWith("asc") ? SortMode.Ascending : SortMode.Descending;
expressions.Add(e);
sortKeys.Add(key);
}
}
if (sortKeys.Count > 0 && isTable)
{
foreach (string sortedKey in sortKeys)
{
queryString.Remove(sortedKey);
}
string url = Request.Url.AbsolutePath;
string updatedQueryString = "?" + queryString.ToString();
Response.Redirect(url + updatedQueryString);
}
return expressions;
}
[ActionName("aspnet-mvc-helper")]
public ActionResult AspnetMvcHelper()
{
return View("aspnet-mvc-helper");
}
[ActionName("sorting-remote")]
public ActionResult SortingRemote()
{
return View("sorting-remote");
}
[ActionName("summaries-remote")]
public ActionResult SummariesRemote()
{
return View("summaries-remote");
}
[ActionName("bind-web-api")]
public ActionResult BindToWebAPI()
{
return View("bind-web-api");
}
[ActionName("basic-editing")]
public ActionResult BasicEditing()
{
ViewBag.Customers = RepositoryFactory.GetCustomerRepository().Get().AsQueryable();
return View("basic-editing");
}
[ActionName("manual-remote-features-handling")]
public ActionResult GridRemoteFeaturesHandling()
{
return View("manual-remote-features-handling");
}
public JsonResult GridRemoteData()
{
IEnumerable<Order> customers = RepositoryFactory.GetOrderRepository().Get().Take(200);
IQueryable data = customers.AsQueryable();
//apply filtering
var filterExprs = Request.QueryString.AllKeys.Where(x => x.Contains("filter"));
if (filterExprs.Count() != 0)
{
data = ApplyFilterExpr(Request.QueryString, customers.AsQueryable());
}
//apply sorting
var sortExprs = Request.QueryString.AllKeys.Where(x => x.Contains("sort"));
if (sortExprs.Count() != 0)
{
data = ApplySorting(Request.QueryString, data);
}
int totalCount = data.Count();
//apply paging
if (Request.QueryString["$top"] != null && Request.QueryString["$skip"] != null)
{
data = ApplyPaging(Request.QueryString, data);
}
JsonResult result = new JsonResult();
result.JsonRequestBehavior = JsonRequestBehavior.AllowGet;
result.Data = new { Records = data, TotalRecordsCount = totalCount };
return result;
}
[ActionName("rest-editing")]
public ActionResult RestEditing()
{
return View("rest-editing");
}
//[ActionName("grid-performance")]
//public ActionResult GridPerformance()
//{
// return View("grid-performance");
//}
public LargeJsonResult GridPerformance()
{
bool dataBind = bool.Parse(Request.QueryString["databind"]);
int rowsCount = int.Parse(Request.QueryString["rowsCount"]);
int colsCount = int.Parse(Request.QueryString["colsCount"]);
int pageSize, pageIndex;
var persons = RepositoryFactory.GetPersonsRepository(dataBind, rowsCount, colsCount).Get();
bool res;
res = int.TryParse(Request.QueryString["pageSize"], out pageSize);
if (!res)
{
pageSize = -1;
res = false;
}
res = int.TryParse(Request.QueryString["pageIndex"], out pageIndex);
if (!res)
{
pageIndex = -1;
res = false;
}
IQueryable data = persons.AsQueryable();
//apply filtering
var filterExprs = Request.QueryString.AllKeys.Where(x => x.Contains("filter"));
if (filterExprs.Count() != 0)
{
data = ApplyFilterExpr(Request.QueryString, persons.AsQueryable());
}
int totalCount = data.Count();
//apply paging
if (pageSize > -1 && pageIndex > -1)
{
data = data.Skip(pageIndex * pageSize).Take(pageSize); ;
}
LargeJsonResult result = new LargeJsonResult();
result.JsonRequestBehavior = JsonRequestBehavior.AllowGet;
result.Data = new { Records = data, TotalRecordsCount = totalCount };
return result;
}
private IQueryable ApplyPaging(NameValueCollection queryString, IQueryable data)
{
int recCount = Convert.ToInt32(queryString["$top"]);
int startIndex = Convert.ToInt32(queryString["$skip"]);
data = data.Skip(startIndex).Take(recCount);
return data;
}
private IQueryable ApplySorting(NameValueCollection queryString, IQueryable data)
{
List<SortExpression> sortExpressions = BuildSortExpressions(queryString, "sort", false);
string orderBy = "OrderBy";
string orderByDescending = "OrderByDescending";
foreach (SortExpression expr in sortExpressions)
{
data = ApplyOrder(data, expr.Key, expr.Mode == SortMode.Ascending ? orderBy : orderByDescending);
orderBy = "ThenBy";
orderByDescending = "ThenByDescending";
}
return data;
}
public static IQueryable ApplyOrder(IQueryable source, string property, string methodName)
{
string[] props = property.Split('.');
Type type = source.ElementType;
ParameterExpression arg = Expression.Parameter(type, "x");
Expression expr = arg;
foreach (string prop in props)
{
var propName = prop.Split(':')[0];
PropertyInfo pi = type.GetProperty(propName);
expr = Expression.Property(expr, pi);
type = pi.PropertyType;
}
Type delegateType = typeof(Func<,>).MakeGenericType(source.ElementType, type);
LambdaExpression lambda = Expression.Lambda(delegateType, expr, arg);
object result = typeof(Queryable).GetMethods().Single(
method => method.Name == methodName
&& method.IsGenericMethodDefinition
&& method.GetGenericArguments().Length == 2
&& method.GetParameters().Length == 2)
.MakeGenericMethod(source.ElementType, type)
.Invoke(null, new object[] { source, lambda });
return (IQueryable)result;
}
private IQueryable ApplyFilterExpr(NameValueCollection queryString, IQueryable customers)
{
List<FilterExpression> exprs = GetFilterExpressions(queryString);
StringBuilder builder = new StringBuilder();
int count = 0;
for (int i = 0; i < exprs.Count; i++)
{
if (count != 0 && count <= exprs.Count - 1)
{
builder.Append(exprs[i].Logic.ToLower() == "AND".ToLower() ? " AND " : " OR ");
}
count++;
string condition = exprs[i].Condition;
string expr = exprs[i].Expr;
string colKey = exprs[i].Key;
var dt = DateTime.Now;
switch (condition.ToLower())
{
case "startswith":
builder.Append(colKey + ".StartsWith(\"" + expr + "\")");
break;
case "contains":
builder.Append(colKey + ".Contains(\"" + expr + "\")");
break;
case "endswith":
builder.Append(colKey + ".EndsWith(\"" + expr + "\")");
break;
case "equals":
if (colKey == "ShipName") {
//col type is string
builder.Append(colKey + " == \"" + expr + "\"");
}
else
{
//col type is number
builder.Append(colKey + " == " + expr);
}
break;
case "doesnotequal":
if (colKey == "ShipName")
{
//col type is string
builder.Append(colKey + " != \"" + expr + "\"");
}
else
{
//col type is number
builder.Append(colKey + " != " + expr);
}
break;
case "doesnotcontain":
builder.Append("! " + colKey + ".Contains(\"" + expr + "\")");
break;
case "lessthan":
builder.Append(colKey + " < " + expr);
break;
case "greaterthan":
builder.Append(colKey + " > " + expr);
break;
case "lessthanorequalto":
builder.Append(colKey + " <= " + expr);
break;
case "greaterthanorequalto":
builder.Append(colKey + " >= " + expr);
break;
case "on":
dt = new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc).AddMilliseconds(double.Parse(expr)).ToUniversalTime();
builder.Append("(" + colKey + ".Value.Day == " + dt.Day + " AND " + colKey +
".Value.Year == " + dt.Year + " AND " +colKey + ".Value.Month == " + dt.Month + ")");
break;
case "noton":
dt = new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc).AddMilliseconds(double.Parse(expr)).ToUniversalTime();
builder.Append("!("+colKey + ".Value.Day == " + dt.Day + " AND " + colKey +
".Value.Year == " + dt.Year + " AND " + colKey + ".Value.Month == " + dt.Month + ")");
break;
case "after":
dt = new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc).AddMilliseconds(double.Parse(expr)).ToUniversalTime();
builder.Append("((" + colKey + ".Value.Year > " + dt.Year + " OR (" +
colKey + ".Value.Month > " + dt.Month + " AND " + colKey + ".Value.Year == " + dt.Year + ") OR (" +
colKey + ".Value.Day > " + dt.Day + " AND " + colKey + ".Value.Year == " + dt.Year + " AND " +
colKey + ".Value.Month == " + dt.Month + ")))");
break;
case "before":
dt = new DateTime(1970, 1, 1, 0, 0, 0, DateTimeKind.Utc).AddMilliseconds(double.Parse(expr)).ToUniversalTime();
builder.Append("((" + colKey + ".Value.Year < " + dt.Year + " OR (" +
colKey + ".Value.Month < " + dt.Month + " AND " + colKey + ".Value.Year == " + dt.Year + ") OR (" +
colKey + ".Value.Day < " + dt.Day + " AND " + colKey + ".Value.Year == " + dt.Year + " AND " +
colKey + ".Value.Month == " + dt.Month + ")))");
break;
case "today":
builder.Append("(" + colKey + ".Value.Day == " + DateTime.Now.Day + " AND " + colKey +
".Value.Year == " + DateTime.Now.Year + " AND " + colKey + ".Value.Month == " + DateTime.Now.Month + ")");
break;
case "yesterday":
DateTime yesterday = DateTime.Now.AddDays(-1);
builder.Append("(" + colKey + ".Value.Day == " + yesterday.Day + " AND " + colKey +
".Value.Year == " + yesterday.Year + " AND " + colKey + ".Value.Month == " + yesterday.Month + ")");
break;
case "thismonth":
builder.Append("(" + colKey + ".Value.Year == " + DateTime.Now.Year + " AND " + colKey + ".Value.Month == " + DateTime.Now.Month + ")");
break;
case "lastmonth":
builder.Append("(" + colKey + ".Value.Year == " + (DateTime.Now.Year - 1) + " AND " + colKey + ".Value.Month == " + (DateTime.Now.Month - 1) + ")");
break;
case "nextmonth":
builder.Append("(" + colKey + ".Value.Year == " + (DateTime.Now.Year - 1) + " AND " + colKey + ".Value.Month == " + (DateTime.Now.Month + 1) + ")");
break;
case "thisyear":
builder.Append(colKey + ".Value.Year == " + DateTime.Now.Year);
break;
case "lastyear":
builder.Append(colKey + ".Value.Year == " + (DateTime.Now.Year - 1));
break;
case "nextyear":
builder.Append(colKey + ".Value.Year == " + (DateTime.Now.Year + 1));
break;
default:
break;
}
}
if (builder.Length > 0) {
customers = customers.Where(builder.ToString(), new object[0]);
}
return customers;
}
internal List<FilterExpression> GetFilterExpressions(NameValueCollection queryString)
{
List<FilterExpression> expressions = new List<FilterExpression>();
// check the query string for sorting expressions
foreach (string key in queryString.Keys)
{
if (!string.IsNullOrEmpty(key) && key.StartsWith("filter("))
{
string val = key.Substring(key.IndexOf("(")).Replace("(", "").Replace(")", "");
string columnKey = val.Substring(0, val.IndexOf(":"));
string logic = "AND";
if (queryString["filter"] != null && (queryString["filter"].ToLower() == "and" || queryString["filter"].ToLower() == "or"))
{
logic = queryString["filter"];
}
Regex filtersRegex = new Regex(@"[a-z]+\(.*?\)", RegexOptions.IgnoreCase);
MatchCollection m = filtersRegex.Matches(queryString[key]);
string[] filters = new string[m.Count];
int i = 0;
foreach (Match capture in m)
{
filters[i] = capture.Value;
i++;
}
// Handle multiple filters for the same column
for (i = 0; i < filters.Length; i++)
{
FilterExpression e = new FilterExpression();
e.Logic = logic;
e.Key = columnKey;
e.Condition = filters[i].Substring(0, filters[i].IndexOf("("));
if ((filters[i].StartsWith("contains()") || filters[i].StartsWith("equals()") || filters[i].StartsWith("startsWith()")
|| filters[i].StartsWith("endsWith()") || filters[i].StartsWith("doesNotContain()") || filters[i].StartsWith("doesNotEqual()")
)
&& (e.Condition != "null" && e.Condition != "notNull" && e.Condition != "empty" && e.Condition != "notEmpty")
&& (e.Condition != "today" && e.Condition != "yesterday" && e.Condition != "thisMonth" && e.Condition != "nextMonth"
&& e.Condition != "lastMonth" && e.Condition != "thisYear" && e.Condition != "nextYear" && e.Condition != "lastYear")
)
{
continue;
}
else
{
string tmp = filters[i].Substring(filters[i].IndexOf("("));
e.Expr = filters[i].Substring(filters[i].IndexOf("(")).Replace("(", "").Replace(")", "");
}
expressions.Add(e);
}
}
}
return expressions;
}
public ActionResult OrdersSaveData()
{
GridModel gridModel = new GridModel();
List<Transaction<Order>> transactions = gridModel.LoadTransactions<Order>(HttpContext.Request.Form["ig_transactions"]);
var orders = RepositoryFactory.GetOrderRepository();
foreach (Transaction<Order> t in transactions)
{
if (t.type == "newrow")
{
orders.Add(t.row);
}
else if (t.type == "deleterow")
{
orders.Delete(o => o.OrderID == Int32.Parse(t.rowId));
}
else if (t.type == "row")
{
var order = (from o in orders.Get()
where o.OrderID == Int32.Parse(t.rowId)
select o).Single();
if (t.row.OrderDate != null)
{
order.OrderDate = t.row.OrderDate;
}
order.TotalPrice = t.row.TotalPrice;
order.TotalItems = t.row.TotalItems;
if (t.row.CustomerID != null)
{
order.CustomerID = t.row.CustomerID;
}
if (t.row.ShipAddress != null)
{
order.ShipAddress = t.row.ShipAddress;
}
orders.Update(order, o => o.OrderID == Int32.Parse(t.rowId));
}
}
orders.Save();
JsonResult result = new JsonResult();
Dictionary<string, bool> response = new Dictionary<string, bool>();
response.Add("Success", true);
result.Data = response;
return result;
}
[GridDataSourceAction]
public ActionResult ChainingGetData()
{
var employees = RepositoryFactory.GetEmployeeRepository().Get();
return View(employees);
}
[GridDataSourceAction]
public ActionResult GetTweets()
{
var auth = new SingleUserAuthorizer
{
Credentials = new SingleUserInMemoryCredentials
{
ConsumerKey = ConfigurationManager.AppSettings["consumerKey"],
ConsumerSecret = ConfigurationManager.AppSettings["consumerSecret"],
TwitterAccessToken = ConfigurationManager.AppSettings["accessToken"],
TwitterAccessTokenSecret = ConfigurationManager.AppSettings["accessTokenSecret"]
}
};
var twitterCtx = new TwitterContext(auth);
var userStatusResponse =
(from tweet in twitterCtx.Status
where tweet.Type == StatusType.User &&
tweet.ScreenName == "Infragistics" &&
tweet.Count == 100
select tweet);
var data = userStatusResponse.ToArray();
return View(data.AsQueryable()); ;
}
[GridDataSourceAction]
public ActionResult GetEmployees()
{
var employees = RepositoryFactory.GetEmployeeRepository().Get();
return View(employees);
}
[GridDataSourceAction]
public ActionResult GetProducts()
{
var products = RepositoryFactory.GetProductRepository().Get();
return View(products);
}
[GridDataSourceAction]
public ActionResult GetCategories()
{
var categories = RepositoryFactory.GetCategoryRepository().Get();
return View(categories);
}
[GridDataSourceAction]
public ActionResult GetOrders()
{
var orders = RepositoryFactory.GetOrderRepository().Get().OrderBy(o => o.OrderID).Take(50).AsQueryable();
return View(orders);
}
private DataTable GetCustomerDataTable()
{
NorthwindContext ctx = new NorthwindContext();
SqlConnection conn = (SqlConnection)ctx.Database.Connection;
DataTable dt = new DataTable();
using (SqlConnection con = conn)
{
using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM CUSTOMERS", con))
{
adapter.Fill(dt);
}
}
return dt;
}
}
}