四 实践:
--------------------------------------------------------------------------------
这里我们分为五个部分:
--------------------------------------------------------------------------------
(1)首先先熟悉LINQ:
--------------------------------------------------------------------------------
int[] integers = { 1, 6, 2, 27, 10, 33, 12, 8, 14, 5 };
IEnumerable<int> twoDigits =
from numbers in integers
where numbers >= 10
select numbers;
Console.WriteLine("Integers > 10:");
foreach (var number in twoDigits)
{
Console.WriteLine(number);
}
--------------------------------------------------------------------------------
(2)简单的查询
--------------------------------------------------------------------------------
//Usage generally LINQ Query
DataTable products = ds.Tables["Products"];
Console.WriteLine("Mapping Name of Table{0}", ds.Tables["Products"].TableName.ToString());
IEnumerable<DataRow> query = from product in products.AsEnumerable() select product;
Console.WriteLine("Product Names");
foreach (DataRow p in query)
{
Console.WriteLine(p.Field<string>("ProductName"));
}
Console.ReadLine();
--------------------------------------------------------------------------------
(3)基于Lambda Expressions表达式的查询
--------------------------------------------------------------------------------
//Usage Method-Based Query Syntax
//Use DataSet ds = new DataSet();
var queryInfo = products.AsEnumerable().Select(product => new
{
ProductName = product.Field<string>("ProductName"),
ProductNumber = product.Field<int>("ProductID")
});
Console.WriteLine("Product Info:");
foreach (var productInfo in queryInfo)
{
Console.WriteLine("Product name: {0} Product number: {1} ",
productInfo.ProductName, productInfo.ProductNumber);
}
Console.ReadLine();
--------------------------------------------------------------------------------
(4)组合式查询
--------------------------------------------------------------------------------
//Usage Composing Queries
//Use DataSet ds = new DataSet();
IEnumerable<DataRow> productsQuery = from product in products.AsEnumerable() select product;
IEnumerable<DataRow> largeProducts = productsQuery.Where(p => p.Field<string>("ProductName") == "Chai");
Console.WriteLine("ProductName =='Chai'");
foreach (DataRow product in largeProducts)
{
Console.WriteLine(product.Field<int>("ProductID"));
}
Console.ReadLine();
--------------------------------------------------------------------------------
(5)最后就是使用ToList(TSource), ToArray(TSource),ToDictionary(TSource, TKey)
ToList(TSource), ToArray(TSource)就是分别从IEnumerable(T)中创建一个List,Array,因为我们使用LINQ插叙回来的数据都是基于IEnumerable(T)泛性接口,返回值的类型为T的指定类型.
而ToDictionary(TSource, TKey)是从IEnumerable(T)中创建一个字典.
//toList(TSource),ToArray(TSource),ToLookup,
// Use DataSet ds = new DataSet();but we use newly Table as name is Suppliers
DataTable Suppliers = ds.Tables["Suppliers"];
IEnumerable<DataRow> SuppliersQuery = from Supplier in Suppliers.AsEnumerable()
orderby
Supplier.Field<string>("City") descending
select Supplier;
IEnumerable<DataRow> SuppliersArray = SuppliersQuery.ToArray();
IEnumerable<DataRow> SupplierstoList = SuppliersQuery.ToList();
foreach (DataRow SuppArray in SuppliersArray)
{
Console.WriteLine(SuppArray.Field<String>("City"));
}
foreach (DataRow supplist in SupplierstoList)
{
Console.WriteLine(supplist.Field<String>("City"));
}
--------------------------------------------------------------------------------
最后是所有代码：
表的的结构是Northwind中的Products和Suppliers这两张表,Products表中使用拉ProductName,ProductID,UnitPrice字段,而Suppliers中使用
SupplierID,CompanyName,City字段.
完整代码：
Code
class LinqToADOApp1
{
public static void Main(String[] args)
{
try {
//Query Expression Syntax
//As mentioned earlier in this topic, the query variable itself
//only stores the query commands when the query is designed to return
//a sequence of values. If the query does not contain a method that
// will cause immediate execution, the actual execution of the query is
// deferred until you iterate over the query variable in a foreach or For Each loop
// Deferred execution enables multiple queries to be combined or a query to be extended.
//
DataSet ds = new DataSet();
LinqToADOApp1 ltaa = new LinqToADOApp1();
ds=ltaa.FillDataSet();
ds.Locale = CultureInfo.InstalledUICulture;
//DataTableExtensions class
//Usage DataTableExtensions.AsDataView Method
Console.WriteLine("-------------------DataTableExtensions.AsDataView()");
DataTable myProducts = ds.Tables["Products"];
DataView view = myProducts.AsDataView();
//Query
IEnumerable<DataRow> viewQuery = from myView in view.Table.AsEnumerable() select myView;
foreach (DataRow myDataRow in viewQuery)
{
Console.WriteLine(myDataRow.Field<String>("ProductName"));
}
Console.ReadLine();
//Usage CopyToDataTable(T)
//Use DataTable myProducts = ds.Tables["Products"];
//
Console.WriteLine("-------------------CopyToDataTable(T)");
IEnumerable<DataRow> myProductsQuery = from myProduct in myProducts.AsEnumerable()
where
myProduct.Field<int>("ProductID") > 30
select myProduct;
DataTable boundTables = myProductsQuery.CopyToDataTable<DataRow>();
Console.WriteLine("---------------------boundTables");
IEnumerable<DataRow> newProductsQuery = from boundTable in boundTables.AsEnumerable()
select boundTable;
foreach (DataRow myDataRow in newProductsQuery)
{
Console.WriteLine(myDataRow.Field<int>("ProductID"));
Console.WriteLine(myDataRow.Field<string>("ProductName"));
}
Console.ReadLine();
//DataRowExtensions Class
//Use DataTable myProducts = ds.Tables["Products"];
Console.WriteLine("---------------------------------------DataRowExtensions Class");
IEnumerable<DataRow> boundFieldQuery = from myProduct in myProducts.AsEnumerable()
where myProduct.Field<int>("ProductID") <= 30&&
myProduct.Field<Decimal>("UnitPrice")==10
select myProduct;
foreach (DataRow myDataRow in boundFieldQuery)
{
Console.WriteLine(myDataRow.Field<string>("ProductName"));
myDataRow.SetField<string>(2,"worksguo");
Console.WriteLine(myDataRow.Field<string>("ProductName"));
}
Console.ReadLine();
//Usage generally LINQ Query
DataTable products = ds.Tables["Products"];
Console.WriteLine("Mapping Name of Table{0}", ds.Tables["Products"].TableName.ToString());
IEnumerable<DataRow> query = from product in products.AsEnumerable() select product;
Console.WriteLine("Product Names");
foreach (DataRow p in query)
{
Console.WriteLine(p.Field<string>("ProductName"));
}
Console.ReadLine();
//UsageMethod-Based Query Syntax
//Use DataSet ds = new DataSet();
var queryInfo = products.AsEnumerable().Select(product => new
{
ProductName = product.Field<string>("ProductName"),
ProductNumber = product.Field<int>("ProductID")
});
Console.WriteLine("Product Info:");
foreach (var productInfo in queryInfo)
{
Console.WriteLine("Product name: {0} Product number: {1} ",
productInfo.ProductName, productInfo.ProductNumber);
}
Console.ReadLine();
//Usage Composing Queries
//Use DataSet ds = new DataSet();
IEnumerable<DataRow> productsQuery = from product in products.AsEnumerable() select product;
IEnumerable<DataRow> largeProducts = productsQuery.Where(p => p.Field<string>("ProductName") == "Chai");
Console.WriteLine("ProductName =='Chai'");
foreach (DataRow product in largeProducts)
{
Console.WriteLine(product.Field<int>("ProductID"));
}
Console.ReadLine();
//toList(TSource),ToArray(TSource),ToLookup,
// Use DataSet ds = new DataSet();but we use newly Table as name is Suppliers
DataTable Suppliers = ds.Tables["Suppliers"];
IEnumerable<DataRow> SuppliersQuery = from Supplier in Suppliers.AsEnumerable()
orderby
Supplier.Field<string>("City") descending
select Supplier;
IEnumerable<DataRow> SuppliersArray = SuppliersQuery.ToArray();
IEnumerable<DataRow> SupplierstoList = SuppliersQuery.ToList();
foreach (DataRow SuppArray in SuppliersArray)
{
Console.WriteLine(SuppArray.Field<String>("City"));
}
foreach (DataRow supplist in SupplierstoList)
{
Console.WriteLine(supplist.Field<String>("City"));
}
}
catch (Exception ex)
{
Console.WriteLine("SQL exception occurred: " + ex.Message);
Console.ReadLine();
}
}
public DataSet FillDataSet()
{
string c
+ "Integrated Security=true;";
SqlConnection connection = new SqlConnection(connectionString);
//Create a SqlDataAdapter for the Suppliers table.
SqlDataAdapter adapter = new SqlDataAdapter();
// A table mapping names the DataTable.
adapter.TableMappings.Add("Table", "Suppliers");
// Open the connection.
connection.Open();
Console.WriteLine("The SqlConnection is open.");
// Create a SqlCommand to retrieve Suppliers data.
SqlCommand command = new SqlCommand(
"SELECT SupplierID, CompanyName,City FROM dbo.Suppliers;",
connection);
command.CommandType = CommandType.Text;
// Set the SqlDataAdapter's SelectCommand.
adapter.SelectCommand = command;
// Fill the DataSet.
DataSet dataSet = new DataSet("Suppliers");
adapter.Fill(dataSet);
// Create a second Adapter and Command to get
// the Products table, a child table of Suppliers.
SqlDataAdapter productsAdapter = new SqlDataAdapter();
productsAdapter.TableMappings.Add("Table", "Products");
SqlCommand productsCommand = new SqlCommand(
"SELECT ProductID, SupplierID,ProductName,UnitPrice FROM dbo.Products;",
connection);
productsAdapter.SelectCommand = productsCommand;
// Fill the DataSet.
productsAdapter.Fill(dataSet);
// Close the connection.
connection.Close();
Console.WriteLine("The SqlConnection is closed.");
// Create a DataRelation to link the two tables
// based on the SupplierID.
DataColumn parentColumn =
dataSet.Tables["Suppliers"].Columns["SupplierID"];
DataColumn childColumn =
dataSet.Tables["Products"].Columns["SupplierID"];
DataRelation relation =
new System.Data.DataRelation("SuppliersProducts",
parentColumn, childColumn);
dataSet.Relations.Add(relation);
Console.WriteLine(
"The {0} DataRelation has been created.",
relation.RelationName);
return dataSet;
}
//static private string GetConnectionString()
//{
// return "Data Source=(local);Initial Catalog=Northwind;"
// + "Integrated Security=SSPI";
//}
}
