query.aspx.cs

数据库连接查询

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Xml.Linq;
using System.Data.Linq;
using System.IO;

public partial class Query : System.Web.UI.Page
{
    NorthwindDataContext ctx = new NorthwindDataContext("server=srv-devdbhost;database=Northwind;uid=sa;pwd=Abcd1234");

    protected void Page_Load(object sender, EventArgs e)
    {

        string[] 关键字列表 = new string[] { "object", "operator" };
        Response.Write((from word in 关键字列表 where word.Length>5 && word.Contains('o') select word).Count());

        //StreamWriter sw = new StreamWriter(Server.MapPath("query.txt"), false);
        //ctx.Log = sw;

        //var 构建匿名类型1 = from c in ctx.Customers
        //              select new
        //              {
        //                  公司名 = c.CompanyName,
        //                  地址 = c.Address
        //              };
//SELECT [t0].[CompanyName], [t0].[Address]
//FROM [dbo].[Customers] AS [t0]
//-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1        

//        var 构建匿名类型2 = from emp in ctx.Employees
//                      select new
//                      {
//                          姓名 = emp.LastName + emp.FirstName,
//                          雇用年 = emp.HireDate.Value.Year
//                      };
//SELECT [t0].[LastName] + [t0].[FirstName] AS [value], DATEPART(Year, [t0].[HireDate]) AS [value2]
//FROM [dbo].[Employees] AS [t0]
//-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

//        var 构建匿名类型3 = from c in ctx.Customers
//                      select new
//                      {
//                          ID = c.CustomerID,
//                          联系信息 = new
//                          {
//                              职位 = c.ContactTitle,
//                              联系人 = c.ContactName
//                          }
//                      };
//SELECT [t0].[CustomerID], [t0].[ContactTitle], [t0].[ContactName]
//FROM [dbo].[Customers] AS [t0]
//-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

//        var 多条件 = from c in ctx.Customers
//                  where c.Country == "France" && c.Orders.Count > 5
//                  select new
//                  {
//                      国家 = c.Country,
//                      城市 = c.City,
//                      订单数 = c.Orders.Count
//                  };       
//SELECT [t0].[Country], [t0].[City], (
//    SELECT COUNT(*)
//    FROM [dbo].[Orders] AS [t2]
//    WHERE [t2].[CustomerID] = [t0].[CustomerID]
//    ) AS [value]
//FROM [dbo].[Customers] AS [t0]
//WHERE ([t0].[Country] = @p0) AND (((
//    SELECT COUNT(*)
//    FROM [dbo].[Orders] AS [t1]
//    WHERE [t1].[CustomerID] = [t0].[CustomerID]
//    )) > @p1)
//-- @p0: Input String (Size = 6; Prec = 0; Scale = 0) [France]
//-- @p1: Input Int32 (Size = 0; Prec = 0; Scale = 0) [5]
//-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1        

//        var select带条件 = from o in ctx.Orders
//                        select new
//                        {
//                            订单号 = o.OrderID,
//                            是否超重 = o.Freight > 100 ? "是" : "否"
//                        };
//SELECT [t0].[OrderID], 
//    (CASE 
//        WHEN [t0].[Freight] > @p0 THEN @p1
//        ELSE @p2
//     END) AS [value]
//FROM [dbo].[Orders] AS [t0]
//-- @p0: Input Currency (Size = 0; Prec = 19; Scale = 4) [100]
//-- @p1: Input String (Size = 1; Prec = 0; Scale = 0) [是]
//-- @p2: Input String (Size = 1; Prec = 0; Scale = 0) [否]
//-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

//        var 排序 = from emp in ctx.Employees
//                 where emp.Employees.Count == 0
//                 orderby emp.HireDate.Value.Year descending, emp.FirstName ascending
//                 select new
//                 {
//                     雇用年 = emp.HireDate.Value.Year,
//                     名 = emp.FirstName
//                 };
//SELECT DATEPART(Year, [t0].[HireDate]) AS [value], [t0].[FirstName]
//FROM [dbo].[Employees] AS [t0]
//WHERE ((
//    SELECT COUNT(*)
//    FROM [dbo].[Employees] AS [t1]
//    WHERE [t1].[ReportsTo] = [t0].[EmployeeID]
//    )) = @p0
//ORDER BY DATEPART(Year, [t0].[HireDate]) DESC, [t0].[FirstName]
//-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [0]
//-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

//        var 分页 = (from c in ctx.Customers select c).Skip(10).Take(10);
//SELECT TOP 10 [t1].[CustomerID], [t1].[CompanyName], [t1].[ContactName], [t1].[ContactTitle], [t1].[Address], [t1].[City], [t1].[Region], [t1].[PostalCode], [t1].[Country], [t1].[Phone], [t1].[Fax]
//FROM (
//    SELECT ROW_NUMBER() OVER (ORDER BY [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]) AS [ROW_NUMBER], [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]
//    FROM [dbo].[Customers] AS [t0]
//    ) AS [t1]
//WHERE [t1].[ROW_NUMBER] > @p0
//-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [10]
//-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

//        var 一般分组 = from c in ctx.Customers
//                   group c by c.Country into g
//                   where g.Count() > 5
//                   orderby g.Count() descending
//                   select new
//                   {
//                       国家 = g.Key,
//                       顾客数 = g.Count()
//                   };
//SELECT [t1].[Country], [t1].[value3] AS [顾客数]
//FROM (
//    SELECT COUNT(*) AS [value], COUNT(*) AS [value2], COUNT(*) AS [value3], [t0].[Country]
//    FROM [dbo].[Customers] AS [t0]
//    GROUP BY [t0].[Country]
//    ) AS [t1]
//WHERE [t1].[value] > @p0
//ORDER BY [t1].[value2] DESC
//-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [5]
//-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

//        var 匿名类型分组 = from c in ctx.Customers
//                     group c by new { c.City, c.Country } into g
//                     orderby g.Key.Country, g.Key.City
//                     select new
//                     {
//                         国家 = g.Key.Country,
//                         城市 = g.Key.City
//                     };
//SELECT [t1].[Country], [t1].[City]
//FROM (
//    SELECT [t0].[City], [t0].[Country]
//    FROM [dbo].[Customers] AS [t0]
//    GROUP BY [t0].[City], [t0].[Country]
//    ) AS [t1]
//ORDER BY [t1].[Country], [t1].[City]
//-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

//        var 按照条件分组 = from o in ctx.Orders