ORM， Linq to sql增删改查

對‘Microsof SQL Server’資料源的‘用于 SQL server 的.NET Framework 資料提供程式’支援。

對‘Microsof SQL Server’資料源的‘用于 OLE DB 的.NET Framework 資料提供程式’不支援。

對‘Microsoft ODBC 資料源‘用于 用于 ODBC 的 .NET Framework 資料提供程式’不支援。

對‘Microsoft Access 資料庫檔案’資料源的‘用于OLE DB的.NET Framework 資料提供程式’不支援。

即，對SQL Server的專用資料提供程式支援，對OLE DB、ODBC資料提供程式不支援，對Oralce專用屬于提供程式未做測試

建議：

因 為Linq to sql 的資料持久方式不能跨資料庫平台，是以背景如果是sql server 資料庫，可以使用此orm，調用過程最好是‘業務邏輯層’===》‘資料通路層’====》‘此Linq to sql 的orm對象’，這樣，如果要跨資料庫，則需要重寫‘資料通路層’（此資料通路層相當于一個代理），對上面的各層不會産生影響。

2、調用：

    protected void Page_Load(object sender, EventArgs e)

    {

        DataPersonDataContext dc = new DataPersonDataContext();

        //查詢2

        GridView1.DataSource = from p in dc.Person

                               where p.ID < 100 && p.Name.StartsWith("bei")

                               select new { 編碼 = p.ID, 姓名 = p.Name, 電話 = p.Tel };

        //查詢3

        var varPerson = from p in dc.Person

                         where p.ID < 100 && p.Tel.StartsWith("010")

                         orderby p.Name descending

                         select new { 編碼 = p.ID, 姓名 = p.Name, 電話 = p.Tel };

        GridView1.DataSource = varPerson;

        //添加

        Person person1 = new Person();

        person1.Name = "xianggang";

        person1.Tel = "00852-11111111";

        dc.Person.InsertOnSubmit(person1);

        dc.SubmitChanges();

        //更新

        Person person2 = dc.Person.Where(p => p.Name == "xianggang").First();

        person2.Tel = "00852-11111110";

        dc.SubmitChanges();

        //删除

        Person person3 = dc.Person.Where(p => p.Name == "xianggang").First();

        dc.Person.DeleteOnSubmit(person3);

        dc.SubmitChanges();

        //查詢1

        GridView1.DataSource = dc.Person;

        GridView1.DataBind();

    }

=>

     傳回結果：

ID	Name	Tel
1	beijing	010-0000000
2	shanghai	021-0000000
13	xianggang	00852-11111111
14	xianggang	00852-11111111
15	xianggang	00852-11111111
16	xianggang	00852-11111111

=>

=>

1、建立linq to sql（即DataPerson.dbml檔案）：

#pragma warning disable 1591

//------------------------------------------------------------------------------

// <auto-generated>

//     此代碼由工具生成。

//     運作庫版本:2.0.50727.1433

//

//     對此檔案的更改可能會導緻不正确的行為，并且如果

//     重新生成代碼，這些更改将會丢失。

// </auto-generated>

//------------------------------------------------------------------------------

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Data.Linq;

using System.Data.Linq.Mapping;

using System.Linq;

using System.Linq.Expressions;

using System.Reflection;

[System.Data.Linq.Mapping.DatabaseAttribute(Name="msdb")]

public partial class DataPersonDataContext : System.Data.Linq.DataContext

{

private static System.Data.Linq.Mapping.MappingSource mappingSource = new AttributeMappingSource();

#region Extensibility Method Definitions

partial void OnCreated();

partial void InsertPerson(Person instance);

partial void UpdatePerson(Person instance);

partial void DeletePerson(Person instance);

#endregion

public DataPersonDataContext() :

    base(global::System.Configuration.ConfigurationManager.ConnectionStrings["msdbConnectionString"].ConnectionString, mappingSource)

{

   OnCreated();

}

public DataPersonDataContext(string connection) :

    base(connection, mappingSource)

{

   OnCreated();

}

public DataPersonDataContext(System.Data.IDbConnection connection) :

    base(connection, mappingSource)

{

   OnCreated();

}

public DataPersonDataContext(string connection, System.Data.Linq.Mapping.MappingSource mappingSource) :

    base(connection, mappingSource)

{

   OnCreated();

}

public DataPersonDataContext(System.Data.IDbConnection connection, System.Data.Linq.Mapping.MappingSource mappingSource) :

    base(connection, mappingSource)

{

   OnCreated();

}

public System.Data.Linq.Table<Person> Person

{

   get

   {

    return this.GetTable<Person>();

   }

}

}

[Table(Name="dbo.Person")]

public partial class Person : INotifyPropertyChanging, INotifyPropertyChanged

{

private static PropertyChangingEventArgs emptyChangingEventArgs = new PropertyChangingEventArgs(String.Empty);

private int _ID;

private string _Name;

private string _Tel;

    #region Extensibility Method Definitions

    partial void OnLoaded();

    partial void OnValidate(System.Data.Linq.ChangeAction action);

    partial void OnCreated();

    partial void OnIDChanging(int value);

    partial void OnIDChanged();

    partial void OnNameChanging(string value);

    partial void OnNameChanged();

    partial void OnTelChanging(string value);

    partial void OnTelChanged();

    #endregion

public Person()

{

   OnCreated();

}

[Column(Storage="_ID", AutoSync=AutoSync.OnInsert, DbType="Int NOT NULL IDENTITY", IsPrimaryKey=true, IsDbGenerated=true)]

public int ID

{

   get

   {

    return this._ID;

   }

   set

   {

    if ((this._ID != value))

    {

     this.OnIDChanging(value);

     this.SendPropertyChanging();

     this._ID = value;

     this.SendPropertyChanged("ID");

     this.OnIDChanged();

    }

   }

}

[Column(Storage="_Name", DbType="NChar(10)")]

public string Name

{

   get

   {

    return this._Name;

   }

   set

   {

    if ((this._Name != value))

    {

     this.OnNameChanging(value);

     this.SendPropertyChanging();

     this._Name = value;

     this.SendPropertyChanged("Name");

     this.OnNameChanged();

    }

   }

}

[Column(Storage="_Tel", DbType="NChar(20)")]

public string Tel

{

   get

   {

    return this._Tel;

   }

   set

   {

    if ((this._Tel != value))

    {

     this.OnTelChanging(value);

     this.SendPropertyChanging();

     this._Tel = value;

     this.SendPropertyChanged("Tel");

     this.OnTelChanged();

    }

   }

}

public event PropertyChangingEventHandler PropertyChanging;

public event PropertyChangedEventHandler PropertyChanged;

protected virtual void SendPropertyChanging()

{

   if ((this.PropertyChanging != null))

   {

    this.PropertyChanging(this, emptyChangingEventArgs);

   }

}

protected virtual void SendPropertyChanged(String propertyName)

{

   if ((this.PropertyChanged != null))

   {

    this.PropertyChanged(this, new PropertyChangedEventArgs(propertyName));

   }

}

}

#pragma warning restore 1591

3.

linq多表查詢

sql語句

select t_friend.userid,friendid,truename,formid

from dbo.t_friend left join dbo.t_userinfo

on dbo.t_friend.friendid=dbo.t_userinfo.userid

where dbo.t_friend.userid=5 and applystate=1

linq語句

public void bind()

        {

            blogdatabaseDataContext dc = new blogdatabaseDataContext();

            Table<t_userinfo> login = dc.GetTable<t_userinfo>();

            Table<t_friend> t_f=dc.GetTable<t_friend>();

            var info = from t in  t_f

                   join c in login on  t.friendid equals c.userid

                       where(  t.userid == 5 && t.applystate == true)

                       select new

                       {                          

                           t.formid,

                           t.userid,

                           t.friendid,                         

                           c.truename,

                           c.picture 

                       };

            this.GridView1.DataSource = info;

            this.GridView1.DataBind();

        }