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()
    {
        String path = HttpContext.Current.Server.MapPath("~/DATAMAP/LINQDB.MAP");
        XmlMappingSource xms = XmlMappingSource.FromXml(File.ReadAllText(path));
        Linqdb ctx = new Linqdb(ConfigurationManager.ConnectionStrings["ConnStr"].ToString(), xms);
        return ctx;
    }
}

这是二个主要方法，用来得到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>();
 }
}

呵呵，今天就写这么多,以后有好的心得再接着写