LINQ to ADO.NET

 一、用LINQ技术查询关系型数据库，需要使用LINQ to ADO.NET

分为：LINQ to SQL,LINQ to Dataset ，LINQ to Entities

LINQ to SQL 是通过开发者建立一个映射Sqlserver中的数据表，被称作实体类的类型，来完成对数据库的查询、修改、删除等操作

LINQ to Dataset 用来查询Dataset 或从数据库断开连接的数据表

LINQ to Entities的用途跟LINQ to SQL 类似，但比LINQ to SQL 功能要更加复杂和强大。ex:在一个实体类中映射多个数据表格和他们的关系，以及查询除Sqlserver 系列产品以外的关系数据库等。注：LINQ to Entities 需要安装ServerPack 才能使用

二、.实例：

1.数据库里新建一个表（比如一个人的姓名，年龄，住址，职位等通讯簿信息。）表名：TabAddressBook

2.新建一个LINQ-SQL 的类，后缀名为.dbml，名称为：AddressBookClasses

3.在服务器资源管理器，中把该表格拖到AddressBookClasses.dbml文件上，保存；

接下来我们需要写一个类是用来把界面和LINQ-SQL搭建起来，实现一个通讯录的新增，删除，保存

代码如下：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel;
namespace MainForm
{
    /// <summary>
    /// 电话簿类(负责用(LINQ to SQL)在界面和数据库之间进行数据传递）
    /// </summary>
    class AddressBookController
    {
        /// <summary>
        /// LINQ to SQL 数据库上下文
        /// </summary>
        public AddressBookClassesDataContext dataContext;
        /// <summary>
        /// 提供给控件的数据绑定对象
        /// </summary>
        public IBindingList BookBindingList { get; private set; }
        public AddressBookController()
        {
            this.dataContext = new AddressBookClassesDataContext();
            this.BookBindingList = this.dataContext.TabAddressBook.GetNewBindingList();
        }
        /// <summary>
        /// 析构函数
        /// </summary>
        ~AddressBookController()
        {
            if (this.dataContext != null)
                this.dataContext.Dispose();
        }
        /// <summary>
        /// 增加
        /// </summary>
        /// <param name="item"></param>
        public void New(TabAddressBook item)
        {
            if (item != null)
            {
                this.dataContext.TabAddressBook.InsertOnSubmit(item);
                this.dataContext.SubmitChanges();
                this.BookBindingList = this.dataContext.TabAddressBook.GetNewBindingList();
            }
        }
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="item"></param>
        public void Update(TabAddressBook item)
        {
            if (item != null)
            {
                var query = from i in this.dataContext.TabAddressBook where i.ab_id == item.ab_id select i;
                foreach (var q in query)
                {
                    q.ab_add = item.ab_add;
                    q.ab_age = item.ab_age;
                    q.ab_com = item.ab_com;
                    q.ab_position = item.ab_position;
                    q.ab_name = item.ab_name;
                    q.ab_sex = item.ab_sex;
                    q.ab_tel = item.ab_tel;
                }
                this.dataContext.SubmitChanges();
                this.BookBindingList = this.dataContext.TabAddressBook.GetNewBindingList();
            }
        }
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="item"></param>
        public void Delete(TabAddressBook item)
        {
            if (item != null)
            {
                var query = from v in this.dataContext.TabAddressBook where v.ab_id == item.ab_id select v;
                foreach (var q in query)
                    this.dataContext.TabAddressBook.DeleteOnSubmit(q);
                this.dataContext.SubmitChanges();
                this.BookBindingList = this.dataContext.TabAddressBook.GetNewBindingList();
            }
        }
        /// <summary>
        /// 搜所
        /// </summary>
        /// <param name="p"></param>
        /// <returns></returns>
        public IEnumerable<TabAddressBook> Search(string p)
        {
            var query = from v in this.dataContext.TabAddressBook where v.ab_name.IndexOf(p) > -1 select v;
            return query;
        }
    }
}
界面如下：

后台代码如下：

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace MainForm
{
    public partial class Form1 : Form
    {
        /// 初始化电话簿控制类
        private AddressBookController abc = new AddressBookController();
        //存储正在操作的数据
        private TabAddressBook nowItem = null;
        public Form1()
        {
            InitializeComponent();
            Bindct(abc.BookBindingList);
        }
        /// <summary>
        /// 控件数据绑定
        /// </summary>
        /// <param name="datasource"></param>
        private void Bindct(object datasource)
        {
            this.lstAdd.DataSource = null;
            this.lstAdd.DataSource = datasource;
            this.lstAdd.DisplayMember = "ab_name";
            this.txtAge.DataBindings.Clear();
            this.txtAge.DataBindings.Add(new Binding("Text",datasource,"ab_age",true));
            this.txtName.DataBindings.Clear();
            this.txtName.DataBindings.Add(new Binding("Text", datasource, "ab_name"));
            this.txtAddress.DataBindings.Clear();
            this.txtAddress.DataBindings.Add(new Binding("Text", datasource, "ab_add"));
            this.txtCompany.DataBindings.Clear();
            this.txtCompany.DataBindings.Add(new Binding("Text", datasource, "ab_com"));
            this.txtPosition.DataBindings.Clear();
            this.txtPosition.DataBindings.Add(new Binding("Text", datasource, "ab_position"));
            this.txtTel.DataBindings.Clear();
            this.txtTel.DataBindings.Add(new Binding("Text", datasource, "ab_tel"));
            this.cbSex.DataBindings.Clear();
            this.cbSex.DataBindings.Add(new Binding("Text", datasource, "ab_sex"));
        }
        /// <summary>
        /// 新建        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnAdd_Click(object sender, EventArgs e)
        {
            TabAddressBook tb = new TabAddressBook() { ab_name = txtName.Text, ab_add = txtAddress.Text, ab_position = txtPosition.Text, ab_com = txtCompany.Text, ab_tel = txtTel.Text, ab_sex = cbSex.SelectedText, ab_age = Convert.ToByte(txtAge.Text) };
            this.nowItem = tb;
            this.abc.New(tb);
            this.Bindct(abc.BookBindingList);
        }
        /// <summary>
        /// 保存

       /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnSave_Click(object sender, EventArgs e)
        {
            this.nowItem = this.lstAdd.SelectedItem as TabAddressBook;
            TabAddressBook tb = new TabAddressBook() {ab_id = this.nowItem.ab_id ,ab_name = txtName.Text, ab_add = txtAddress.Text, ab_position = txtPosition.Text, ab_com = txtCompany.Text, ab_tel = txtTel.Text, ab_sex = cbSex.SelectedText, ab_age = Convert.ToByte(txtAge.Text) };
            this.nowItem = tb;
            this.abc.Update(tb);
            this.Bindct(abc.BookBindingList);
        }
        /// <summary>
        /// 删除        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnDelete_Click(object sender, EventArgs e)
        {
            //转到控制器中使用LINQ to SQL 从数据库删除
            if (this.lstAdd.SelectedItem != null)
            {
                this.abc.Delete((TabAddressBook)this.lstAdd.SelectedItem);
                this.Bindct(abc.BookBindingList);
            }
        }

        private void txtSearch_TextChanged(object sender, EventArgs e)
        {
            if (this.txtSearch.Text.Length > 0)
            {
                //使用LINQ搜索
                IEnumerable<TabAddressBook> book = this.abc.Search(this.txtSearch.Text);
                this.Bindct(book);
            }
        }
    }
}

体会一下，原先通过三次架构处理，建一个表的映射为model这就不需要，挺简单的，而且，SQL的注入也就不存在了。