LINQ to SQL 使用小结

　　借助LINQ to SQL，我们可以便捷的与数据库进行交互，本文演示了如何在.NET开发环境中使用LINQ to SQL

使用过程：　　

　　1.在数据库中创好一张表
　　

　　2.在VS环境中链接到数据库

　　  

　　3.在工程中创建一个新的L2S文件

      

　　4.将步骤2中的表，“拖入”DBML文件中的layout文件，如图，确认拖入的表即为我们刚创建的之后，保存
　　

　　5.VS自动生designer.cs成了如下代码

#pragma warning disable 1591
//------------------------------------------------------------------------------
// <auto-generated>
//     This code was generated by a tool.
//     Runtime Version:4.0.30319.34014
//
//     Changes to this file may cause incorrect behavior and will be lost if
//     the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------

namespace LinqToDB
{
    using System.Data.Linq;
    using System.Data.Linq.Mapping;
    using System.Data;
    using System.Collections.Generic;
    using System.Reflection;
    using System.Linq;
    using System.Linq.Expressions;
    using System.ComponentModel;
    using System;
    
    
    [global::System.Data.Linq.Mapping.DatabaseAttribute(Name="ClientInfo")]
    public partial class ClientInfoDataContext : 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 ClientInfoDataContext() : 
                base(global::LinqToDB.Properties.Settings.Default.ClientInfoConnectionString, mappingSource)
        {
            OnCreated();
        }
        
        public ClientInfoDataContext(string connection) : 
                base(connection, mappingSource)
        {
            OnCreated();
        }
        
        public ClientInfoDataContext(System.Data.IDbConnection connection) : 
                base(connection, mappingSource)
        {
            OnCreated();
        }
        
        public ClientInfoDataContext(string connection, System.Data.Linq.Mapping.MappingSource mappingSource) : 
                base(connection, mappingSource)
        {
            OnCreated();
        }
        
        public ClientInfoDataContext(System.Data.IDbConnection connection, System.Data.Linq.Mapping.MappingSource mappingSource) : 
                base(connection, mappingSource)
        {
            OnCreated();
        }
        
        public System.Data.Linq.Table<Person> Persons
        {
            get
            {
                return this.GetTable<Person>();
            }
        }
    }
    
    [global::System.Data.Linq.Mapping.TableAttribute(Name="dbo.Person")]
    public partial class Person : INotifyPropertyChanging, INotifyPropertyChanged
    {
        
        private static PropertyChangingEventArgs emptyChangingEventArgs = new PropertyChangingEventArgs(String.Empty);
        
        private string _ID;
        
        private string _Name;
        
        private string _Date;
        
    #region Extensibility Method Definitions
    partial void OnLoaded();
    partial void OnValidate(System.Data.Linq.ChangeAction action);
    partial void OnCreated();
    partial void OnIDChanging(string value);
    partial void OnIDChanged();
    partial void OnNameChanging(string value);
    partial void OnNameChanged();
    partial void OnDateChanging(string value);
    partial void OnDateChanged();
    #endregion
        
        public Person()
        {
            OnCreated();
        }
        
        [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_ID", DbType="VarChar(50) NOT NULL", CanBeNull=false, IsPrimaryKey=true)]
        public string ID
        {
            get
            {
                return this._ID;
            }
            set
            {
                if ((this._ID != value))
                {
                    this.OnIDChanging(value);
                    this.SendPropertyChanging();
                    this._ID = value;
                    this.SendPropertyChanged("ID");
                    this.OnIDChanged();
                }
            }
        }
        
        [global::System.Data.Linq.Mapping.ColumnAttribute(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();
                }
            }
        }
        
        [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_Date", DbType="VarChar(50)")]
        public string Date
        {
            get
            {
                return this._Date;
            }
            set
            {
                if ((this._Date != value))
                {
                    this.OnDateChanging(value);
                    this.SendPropertyChanging();
                    this._Date = value;
                    this.SendPropertyChanged("Date");
                    this.OnDateChanged();
                }
            }
        }
        
        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

　　对于数据库而言，最基本的操作自然是增删改查，接下来即为利用L2S实现这4个功能。
　　以下是功能代码

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace LinqToDB
{
    class ClientDBUtility
    {
        internal void Add(string Name)
        {
            using(var clientInfoTable = new ClientInfoDataContext())
            {
                var person = new Person() 
                { 
                    ID = Guid.NewGuid().ToString(),
                    Name = Name,
                    Date = DateTime.Now.ToString()
                };
                clientInfoTable.Persons.InsertOnSubmit(person);
                clientInfoTable.SubmitChanges();
            }
        }

        internal void remove(string keyword)
        {
            using (var clientInfoTable = new ClientInfoDataContext())
            {
                var person = clientInfoTable.Persons.FirstOrDefault(item => item.Name.Contains(keyword));
                if(person != null)
                    clientInfoTable.Persons.DeleteOnSubmit(person);
                clientInfoTable.SubmitChanges();
            }
        }

        internal void change(string perosonID, string Name)
        {
            using (var clientInfoTable = new ClientInfoDataContext())
            {
                var person = clientInfoTable.Persons.SingleOrDefault(item => item.ID == perosonID);
                if (person != null)
                    person.Name = Name;
                clientInfoTable.SubmitChanges();
            }
        }

        internal string retrieve(string keyword)
        {
            using (var clientInfoTable = new ClientInfoDataContext())
            {
                var person = clientInfoTable.Persons.FirstOrDefault(item => item.Name.Contains(keyword));
                if (person != null)
                    return ("This person's name is " + person.Name);
                else
                    return null;
            }
        }
    }
}

　　　以下是测试代码

            var clientDBUtility = new ClientDBUtility();
            clientDBUtility.Add("小明");
            clientDBUtility.Add("David Guan");
            clientDBUtility.Add("小红");
            clientDBUtility.remove("明");
            clientDBUtility.change("b48b0059-9c98-445c-937e-8223cac2f71b", "ming");
            Console.WriteLine(clientDBUtility.retrieve("D"));

　　   以下是数据库与控制台的输出结果

　　

　　  LINQ2SQL会大大简化我们对数据库的交互过程，还是很方便的。