LINQ to SQL 与 ADO.NET

• 下载源代码 - 37.4 KB

引言

本文展示了 LINQ to SQL 和 ADO.NET 在从 SQL Server 数据库中选择数据执行时间方面的简单比较。那么，LINQ to SQL 是什么？LINQ to SQL 提供了一个运行时基础设施，用于将关系数据作为对象进行管理，同时不会失去查询数据的能力。您的应用程序可以自由地操作这些对象，而 LINQ to SQL 会在后台自动跟踪您的更改。LINQ to SQL 将您的数据库、表和列作为对象提供，您可以在 C# 或 VB.NET 代码中直接使用它们。在这里，我们将用 ADO.NET 来测试它。

myDatabase.CustomersTable.CustomerNameColumn;

如何添加 LINQ

• LINQ 随 Microsoft Visual Studio 2008 一起发布，您可以从 Microsoft 免费下载 Beta 版本。
• 打开 Visual Studio 并创建一个新的 Windows 应用程序项目。
• 添加新项目并选择 LINQ to SQL 类。



• 您将获得一个从 DataContext 继承的类 .dbml。
• 现在，从服务器资源管理器中，连接到 AdventureWorks 数据库，然后将表 Product 拖放到设计器中。



• 移动到代码..

使用代码

此应用程序同时使用 ADO 和 LINQ，并计算它们从 AdventureWorks 数据库中选择数据所花费的时间。我们将要做类似 select * from Products 的事情。在 LINQ 按钮单击事件中..

//AdventureWorksDataContext is the dbml class we have already added above

AdventureWorksDataContext AdventureDB = new AdventureWorksDataContext(
            "Data Source=ISSY;Initial Catalog=AdventureWorks;Integrated Security=True");
//Products is the table we draged and dropped in the dbml designer
var _data = from _products in AdventureDB.Products 
            select _products;
dataGridViewLinq.DataSource = _data;
//Thats it!

在 ADO 按钮单击事件中

SqlDataAdapter adapter = new SqlDataAdapter("select * from Production.Products",
            "Data Source=ISSY;Initial Catalog=AdventureWorks;Integrated Security=True");
DataSet ds = new DataSet();
int x = adapter.Fill(ds);
dataGridViewADO.DataSource = ds.Tables[0];

让我们使其更复杂；将 ProductCategroy 表和 ProductSubCategory 表拖放到 DBML 设计器中

使用 LINQ

var _t = from o in AdventureDB.ProductSubcategories
                     //here we use the relations between these tables

                     where o.ProductCategory.ProductSubcategories.Count > 3
                     select new
                     {
                         o.ProductCategoryID,
                         o.Name,
                     };
                     
dataGridViewLinq.DataSource = _t;

使用 ADO.NET

SqlDataAdapter adapter = new SqlDataAdapter("SELECT [t0].[ProductCategoryID],
     [t0].[Name]"
    + "FROM [Production].[ProductSubcategory] AS [t0]"
    + "WHERE (("
    + "SELECT COUNT(*)"
    + "FROM [Production].[ProductCategory] AS [t1],
         [Production].[ProductSubcategory] AS [t2]"
    + "WHERE ([t1].[ProductCategoryID] = [t0].[ProductCategoryID]) AND (
         [t2].[ProductCategoryID] = [t1].[ProductCategoryID])"
    + ")) > 3",
         "Data Source=ISSY;Initial Catalog=AdventureWorks;Integrated Security=True");
DataSet ds = new DataSet();
int x = adapter.Fill(ds);

dataGridViewADO.DataSource = ds.Tables[0];

代码

private void buttonLinq_Click(object sender, EventArgs e)
{
    int startS = DateTime.Now.Second;
    int startM = DateTime.Now.Millisecond;


    AdventureWorksDataContext AdventureDB = new AdventureWorksDataContext(
   "Data Source=ISSY;Initial Catalog=AdventureWorks;Integrated Security=True");
    //var _data = from _products in AdventureDB.Products
    //                 select _products;
    //dataGridViewLinq.DataSource = _data;

    var _t = from o in AdventureDB.ProductSubcategories
             where o.ProductCategory.ProductSubcategories.Count > 3
             select new
             {
                 o.ProductCategoryID,
                 o.Name,
             };
    dataGridViewLinq.DataSource = _t;

    #region time_Calculation
    int endS = DateTime.Now.Second;
    int endM = DateTime.Now.Millisecond;
    if (endS == startS)
    {
        labelLinqTime.Text = (endM - startM).ToString();
    }
    else if (endS > startS && endS < startS + 2)
    {
        labelLinqTime.Text = (endM + 1000 - startM).ToString();
    }
    else
    {
        //if it took more than 2 seconds.. 
        throw new Exception("timeout");
    }
    #endregion

}

private void buttonADO_Click(object sender, EventArgs e)
{
    int startS = DateTime.Now.Second;
    int startM = DateTime.Now.Millisecond;

    SqlDataAdapter adapter = new SqlDataAdapter(
          "SELECT [t0].[ProductCategoryID], [t0].[Name]"
        + "FROM [Production].[ProductSubcategory] AS [t0]"
        + "WHERE (("
        + "SELECT COUNT(*)"
        + "FROM [Production].[ProductCategory] AS [t1],
            [Production].[ProductSubcategory] AS [t2]"
        + "WHERE ([t1].[ProductCategoryID] = [t0].[ProductCategoryID]) AND (
            [t2].[ProductCategoryID] = [t1].[ProductCategoryID])"
        + ")) > 3",
          "Data Source=ISSY;Initial Catalog=AdventureWorks;Integrated Security=True");
    DataSet ds = new DataSet();
    int x = adapter.Fill(ds);
    dataGridViewADO.DataSource = ds.Tables[0];

    #region time_calculation
    int endS = DateTime.Now.Second;
    int endM = DateTime.Now.Millisecond;
    if (endS == startS)
    {
        labelADOTime.Text = (endM - startM).ToString();
    }
    else if (endS > startS && endS < startS + 2)
    {
        labelADOTime.Text = (endM + 1000 - startM).ToString();
    }
    else
    {
        //if it took more than 2 seconds.. 

        throw new Exception("timeout");
    }
    #endregion
}

每次更改查询以避免缓存。在此处了解有关 LINQ to SQL 的更多信息。