Linq to DataSet

[索引页]

我想我的blogs现在也有些常客,要谢谢你们的来访,我会尽力写好出好的文章.也努力像园子里的兄弟们学习.
我将会在大概4天内就会将linq搞定.所以要快,要更快!加油!

---

一 Linq概念

---

    (1)来先看这里:Language-Integrated Query (LINQ) 直接译就是整合了查询的语言,再看LINQ makes a query a first-class language construct in C# and Visual Basic.是以类为主的语言,传统的query是retrieved from a data source,而且是在C#,VB.NET中的,

---

    (2) You write queries against strongly typed collections of objects by using language keywords and familiar operators.可以使用熟悉的操作符号和关键字,是强类型的!

---

    (3)in C# with full type checking and IntelliSense support.支持类型检测和IntelliSense.

---

    (4)you can write LINQ queries in Visual Basic or C# with SQL Server databases, XML documents, ADO.NET Datasets, and any collection of objects that supports IEnumerable or the generic IEnumerable(T) interface.
       你能在Visual Basic or C#中写LINQ查询SQL Server databases, XML documents, ADO.NET Datasets(DataSet,SQL)和支持IEnumerable 或generic IEnumerable(T) interface 的任何集合对象.

---

上面整理之后.就比你看MSDN中文版好理解多拉.

---

    (5)还有一句值得关注:a query that returns a sequence of values,在查询返回值, the query variable itself never holds the query results and only stores the query commands.(这个var query=...变量从来就不会持有查询结果,只是存储查询命令) Execution of the query is deferred until the query variable is iterated over in a foreach or For Each loop. This is called deferred execution;执行查询回延迟到在你的变量被foreach 或For Each loop语句使用时,它才会执行.

---

(6)如何创建一个LINQ工程.
   这里详细介绍
   第一个LINQ程序

using System;
using System.Linq;

class LinqDemo
{
  static void Main(  )
  {
    string[] names = { "Tom", "Dick", "Harry" };


    IEnumerable<string> filteredNames = names.Where (n => n.Length >= 4);
    foreach (string name in filteredNames) Console.Write (name + "|");
  }
}

---

    (7)LINQ有些关键字.如from,where,select,group,into,orderby,join.let大家都很熟悉,再这里Join我以后单独写,现在谈let
    You can do this with the let keyword, which creates a new range variable and initializes it with the result of the expression you supply该关键字可以创建一个新的范围变量，并且用您提供的表达式的结果初始化该变量.
   

class LetSample1
{
    static void Main()
    {
        string[] strings =
        {
            "A penny saved is a penny earned.",
            "The early bird catches the worm.",
            "The pen is mightier than the sword."
        };

        // Split the sentence into an array of words
        // and select those whose first letter is a vowel.
        var earlyBirdQuery =
            from sentence in strings
            let words = sentence.Split(' ')
            from word in words
            let w = word.ToLower()
            where w[0] == 'a' || w[0] == 'e'
                || w[0] == 'i' || w[0] == 'o'
                || w[0] == 'u'
            select word;

        // Execute the query.
        foreach (var v in earlyBirdQuery)
        {
            Console.WriteLine("\"{0}\" starts with a vowel", v);
        }

        // Keep the console window open in debug mode.
        Console.WriteLine("Press any key to exit.");
        Console.ReadKey();
    }
}

   

---

二 LINQ to DataSet Overview

---

    首先你要到这个地址不知道大家发现没http://msdn2.microsoft.com/en-us/library/bb399365.aspx,
    LINQ to ADO.NET分为LINQ to DataSet,LINQ to SQL.使用这个的目的是因为我们以前都常常处理a high-level language和query language之间的一些问题,如我们的SQL是写在C#语言中的用" "包裹着("select * from products"),编译器不会编译它,不能检测错误,而且是不能检测类型,没有IntelliSens;而LINQ是without having to use a separate query language.不需要使用独立的查询语言,能debug等优势；LINQ to DataSet就是利用LINQ来优化和丰富DataSet,而LINQ to SQL着允许你查询SQL Server database.
    这里我们只先讨论LINQ to DataSet;LINQ to DataSet就是让我们更容易,更快的查询在缓存中的DataSet对象.
 


   
    看过这两个就会很清楚里.再看向下
   
   
   

---

三 回顾DataSet

---

    DataSet,简单说就是一个在内存中数据缓存.an in-memory cache of data.它是由DataTable对象集合组成,你还可以在DataSet中建立表关系或数据的关系,如外键等,而DataTable又是由DataTableCollection组成,通过DataRelationCollection 来连接他们的关系;其实DataSet可以读取/写XML.还是看图:





    这里为什么要谈DataSet,因为DataSet是ADO.NET里主要的组件,ADO中常用的命名空间是System.Data,而我们还要谈到System.Data中的DataTableExtensions和DataRowExtensions类,他们是分别为DataTable类和DataRow类定义一组扩展方法(extension methods ).他们的Assembly是在System.Data.DataSetExtensions.dll中,他们都是静态类.

    我们先看DataTableExtensions Class,
    public static class DataTableExtensions:
    有三个方法:

---

  (1)AsDataView:

---

允许LINQ创建和返回一个DataView对象
 通常使用方式;与GridView结合
   

DataTable orders = dataSet.Tables["SalesOrderDetail"];
    DataView view = orders.AsDataView();
    bindingSource1.DataSource = view;
    dataGridView1.AutoResizeColumns();

//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();

---

(2)DataTableExtensions.AsEnumerable方法

---

    返回IEnumerable(T) objcet,这里的泛性参数T为DataRow.这个对象能被LINQ表达式或查询方法使用.
这个方法常常使用，就不列代码拉。

---

    (3)DataTableExtensions.CopyToDataTable(T) Generic Method

---

    返回一个DataTable,里面包括DataRow对象.
   

public static DataTable CopyToDataTable<T>(
        this IEnumerable<T> source
    ) where T : DataRow 

//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

---

    (1)Field(T)

---

    提供一个强类访问指定row的中所有的column的值.

---

    (2)SetField(T)

---

    为在DataRow中指定的column设置一个新值.
    

//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();

---

四  实践:

---

    这里我们分为五个部分:

---

    (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 connectionString = "Data Source=localhost;Initial Catalog=Northwind;"
                                                + "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";
        //}
    }

worksguo