쿼리 식 구문 예제: 제한(LINQ to DataSet)
이 항목의 예제에서는 Where 메서드에서 쿼리 식 구문을 사용하여 DataSet을 쿼리하는 방법을 보여 줍니다.
이러한 예제에서 사용되는 FillDataSet 메서드는 데이터집합에 데이터를 로드하는데 지정됩니다.
이 항목의 예제에서는 AdventureWorks 샘플 데이터베이스의 Contact, Address, Product, SalesOrderHeader 및 SalesOrderDetail 테이블을 사용합니다.
이 항목의 예제에서는 다음과 같은 using/Imports 문을 사용합니다.
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Linq;
using System.Windows.Forms;
자세한 내용은 방법: Visual Studio에서 LINQ to DataSet 프로젝트 만들기를 참조하세요.
Where
예시
이 예제에서는 모든 온라인 주문을 반환합니다.
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable orders = ds.Tables["SalesOrderHeader"];
var query =
from order in orders.AsEnumerable()
where order.Field<bool>("OnlineOrderFlag") == true
select new
{
SalesOrderID = order.Field<int>("SalesOrderID"),
OrderDate = order.Field<DateTime>("OrderDate"),
SalesOrderNumber = order.Field<string>("SalesOrderNumber")
};
foreach (var onlineOrder in query)
{
Console.WriteLine("Order ID: {0} Order date: {1:d} Order number: {2}",
onlineOrder.SalesOrderID,
onlineOrder.OrderDate,
onlineOrder.SalesOrderNumber);
}
' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
Dim orders As DataTable = ds.Tables("SalesOrderHeader")
Dim query = _
From order In orders.AsEnumerable() _
Where order.Field(Of Boolean)("OnlineOrderFlag") = True _
Select New With { _
.SalesOrderID = order.Field(Of Integer)("SalesOrderID"), _
.OrderDate = order.Field(Of DateTime)("OrderDate"), _
.SalesOrderNumber = order.Field(Of String)("SalesOrderNumber") _
}
For Each onlineOrder In query
Console.Write("Order ID: " & onlineOrder.SalesOrderID)
Console.Write(" Order date: " & onlineOrder.OrderDate)
Console.WriteLine(" Order number: " & onlineOrder.SalesOrderNumber)
Next
예시
이 예제에서는 주문 수량이 2보다 크고 6보다 작은 주문을 반환합니다.
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable orders = ds.Tables["SalesOrderDetail"];
var query =
from order in orders.AsEnumerable()
where order.Field<Int16>("OrderQty") > 2 &&
order.Field<Int16>("OrderQty") < 6
select new
{
SalesOrderID = (int)order.Field<int>("SalesOrderID"),
OrderQty = order.Field<Int16>("OrderQty")
};
foreach (var order in query)
{
Console.WriteLine("Order ID: {0} Order quantity: {1}",
order.SalesOrderID, order.OrderQty);
}
' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
Dim orders As DataTable = ds.Tables("SalesOrderDetail")
Dim query = _
From order In orders.AsEnumerable() _
Where order.Field(Of Short)("OrderQty") > 2 And _
order.Field(Of Short)("OrderQty") < 6 _
Select New With _
{ _
.SalesOrderID = order.Field(Of Integer)("SalesOrderID"), _
.OrderQty = order.Field(Of Short)("OrderQty") _
}
For Each order In query
Console.Write("Order ID: " & order.SalesOrderID)
Console.WriteLine(" Order quantity: " & order.OrderQty)
Next
예시
이 예제에서는 모든 빨간색 제품을 반환합니다.
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable products = ds.Tables["Product"];
var query =
from product in products.AsEnumerable()
where product.Field<string>("Color") == "Red"
select new
{
Name = product.Field<string>("Name"),
ProductNumber = product.Field<string>("ProductNumber"),
ListPrice = product.Field<Decimal>("ListPrice")
};
foreach (var product in query)
{
Console.WriteLine("Name: {0}", product.Name);
Console.WriteLine("Product number: {0}", product.ProductNumber);
Console.WriteLine("List price: ${0}", product.ListPrice);
Console.WriteLine("");
}
' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
Dim products As DataTable = ds.Tables("Product")
Dim query = _
From product In products.AsEnumerable() _
Where product.Field(Of String)("Color") = "Red" _
Select New With _
{ _
.Name = product.Field(Of String)("Name"), _
.ProductNumber = product.Field(Of String)("ProductNumber"), _
.ListPrice = product.Field(Of Decimal)("ListPrice") _
}
For Each product In query
Console.WriteLine("Name: " & product.Name)
Console.WriteLine("Product number: " & product.ProductNumber)
Console.WriteLine("List price: $ " & product.ListPrice & vbNewLine)
Next
예시
이 예제에서는 Where 메서드를 사용하여 2002년 12월 1일 이후 주문을 찾은 다음 GetChildRows 메서드를 사용하여 각 주문의 세부 정보를 가져옵니다.
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable orders = ds.Tables["SalesOrderHeader"];
IEnumerable<DataRow> query =
from order in orders.AsEnumerable()
where order.Field<DateTime>("OrderDate") >= new DateTime(2002, 12, 1)
select order;
Console.WriteLine("Orders that were made after 12/1/2002:");
foreach (DataRow order in query)
{
Console.WriteLine("OrderID {0} Order date: {1:d} ",
order.Field<int>("SalesOrderID"), order.Field<DateTime>("OrderDate"));
foreach (DataRow orderDetail in order.GetChildRows("SalesOrderHeaderDetail"))
{
Console.WriteLine(" Product ID: {0} Unit Price {1}",
orderDetail["ProductID"], orderDetail["UnitPrice"]);
}
}
' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
Dim orders As DataTable = ds.Tables("SalesOrderHeader")
Dim query As IEnumerable(Of DataRow) = _
From order In orders.AsEnumerable() _
Where order.Field(Of DateTime)("OrderDate") >= New DateTime(2002, 12, 1) _
Select order
Console.WriteLine("Orders that were made after 12/1/2002:")
For Each order As DataRow In query
Console.WriteLine("OrderID {0} Order date: {1:d} ", _
order.Field(Of Integer)("SalesOrderID"), order.Field(Of DateTime)("OrderDate"))
For Each orderDetail As DataRow In order.GetChildRows("SalesOrderHeaderDetail")
Console.WriteLine(" Product ID: {0} Unit Price {1}", _
orderDetail("ProductID"), orderDetail("UnitPrice"))
Next
Next