linq學習筆記(二)

試用了幾天linq,感覺确實友善，而且生成的sql也還不錯,下面是幾點體會

1.幾種常見的等效select寫法

var s = from c in ctx.T_Users select new { c.F_Name, c.F_Sex, c.F_Birthday, c.F_ID }

這種寫法對于初學者來說，最容易了解

var s = ctx.T_Users.Select(p => new { p.F_Name, p.F_Sex, p.F_Birthday, p.F_ID })

這種寫法利用T_Users這一泛型Table集合的擴充方法

var s = ctx.T_User.Select(p => new { p.F_Name, p.F_Sex, p.F_Birthday, p.F_ID })

這種寫法幾乎與第二種寫法完全一樣，看清楚，這裡T_User而不是T_Users！這是直接利用T_User的擴充方法

經調試，這三種方法生成的sql語句完全一樣

2.快速更新資料庫結構

如果資料庫的表結構或視圖結構等對象做了改動，而又不想在dbml可視化環境裡，删除原來的對象，再拖拉同樣的對象到可視環境中，有一個相對簡單點的辦法,利用sqlmetal.exe工具(開始-->程式-->Microsoft Visual Studio 2008-->Visual Studio Tools-->Visual Studio 2008 Command Prompt)

該工具可友善快速的将整個資料庫的表，視圖，存儲過程生成cs類，完整參數如下:

Microsoft (R) Database Mapping Generator 2008 version 1.00.21022

for Microsoft (R) .NET Framework version 3.5

Copyright (C) Microsoft Corporation. All rights reserved.

SqlMetal [options] [<input file>]

  Generates code and mapping for the LINQ to SQL component of the .NET framework. SqlMetal can:

  - Generate source code and mapping attributes or a mapping file from a database.

  - Generate an intermediate dbml file for customization from the database.

  - Generate code and mapping attributes or mapping file from a dbml file.

Options:

  /server:<name>             Database server name.

  /database:<name>           Database catalog on server.

  /user:<name>               Login user ID (default: use Windows Authentication).

  /password:<password>       Login password (default: use Windows Authentication).

  /conn:<connection string>  Database connection string. Cannot be used with /server, /database, /user or /password options.

  /timeout:<seconds>         Timeout value to use when SqlMetal accesses the database (default: 0 which means infinite).

  /views                     Extract database views.

  /functions                 Extract database functions.

  /sprocs                    Extract stored procedures.

  /dbml[:file]               Output as dbml. Cannot be used with /map option.

  /code[:file]               Output as source code. Cannot be used with /dbml option.

  /map[:file]                Generate mapping file, not attributes. Cannot be used with /dbml option.

  /language:<language>       Language for source code: VB or C# (default: derived from extension on code file name).

  /namespace:<name>          Namespace of generated code (default: no namespace).

  /context:<type>            Name of data context class (default: derived from database name).

  /entitybase:<type>         Base class of entity classes in the generated code (default: entities have no base class).

  /pluralize                 Automatically pluralize or singularize class and member names using English language rules.

  /serialization:<option>    Generate serializable classes: None or Unidirectional (default: None).

  /provider:<type>           Provider type: SQLCompact, SQL2000, or SQL2005. (default: provider is determined at run time).

  <input file>               May be a SqlExpress mdf file, a SqlCE sdf file, or a dbml intermediate file.

Create code from SqlServer:

  SqlMetal /server:myserver /database:northwind /code:nwind.cs /namespace:nwind

Generate intermediate dbml file from SqlServer:

  SqlMetal /server:myserver /database:northwind /dbml:northwind.dbml /namespace:nwind

Generate code with external mapping from dbml:

  SqlMetal /code:nwind.cs /map:nwind.map northwind.dbml

Generate dbml from a SqlCE sdf file:

  SqlMetal /dbml:northwind.dbml northwind.sdf

Generate dbml from SqlExpress local server:

  SqlMetal /server:.\sqlexpress /database:northwind /dbml:northwind.dbml

Generate dbml by using a connection string in the command line:

  SqlMetal /conn:"server='myserver'; database='northwind'" /dbml:northwind.dbml

個人感覺，直接生成map和cs檔案比較實用

比如生成LINQDB.MAP和LINQDB.CS後，如何使用呢?往下看

将LINQDB.MAP 複制到 網站根目錄/DATAMAP目錄下(當然DATAMAP這個名字，你可以随意指定)

将LINQDB.CS 複制到 ／APP_CODE/目錄下

為了友善以後代碼重用，我習慣在APP_CODE目錄下建立一個公用的類檔案CONFIG.CS(用來放一些常用的公共方法)

/// <summary>

/// Summary description for CONFIG

/// </summary>

public static class CONFIG

{

    public static Linqdb GetDBContext(string ConnString)

    {

        String path = HttpContext.Current.Server.MapPath("~/DATAMAP/LINQDB.MAP");

        XmlMappingSource xms = XmlMappingSource.FromXml(File.ReadAllText(path));

        Linqdb ctx = new Linqdb(ConnString, xms);

        return ctx;

    }

    public static Linqdb GetDBContext()

        Linqdb ctx = new Linqdb(ConfigurationManager.ConnectionStrings["ConnStr"].ToString(), xms);

}

這是二個主要方法，用來得到DataContext對象的執行個體

終于進到關鍵地方了，以下是使用的代碼:

protected void Page_Load(object sender, EventArgs e)

    ShowData();

void ShowData()

    using (Linqdb ctx = CONFIG.GetDBContext())

        var s = ctx.T_User.Select(p => new { p.F_Name, p.F_Sex, p.F_Birthday, p.F_ID });

        this.GridView1.DataSource = s;

        this.GridView1.DataBind();          

}  

值得注意的是:如果用metasql.exe生成的cs檔案，裡面不會有"表名s"這個類，而在可視化環境中，拖放出來的表，除會生成"表名"這個類外，還會同步生成一個"表名s"的類

即:假設資料庫中有一個表T_User,用metasql.exe生成的cs檔案中，僅包含T_User這個對應該表的類，而在可視化環境中，除生成T_User類外，還會有一個T_Users類，代碼類似下面這樣:

public System.Data.Linq.Table<T_User> T_Users

 get

 {

  return this.GetTable<T_User>();

 }

呵呵，今天就寫這麼多,以後有好的心得再接着寫