|
?一、用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","ab_name"));
??????????? this.txtAddress.DataBindings.Clear();
??????????? this.txtAddress.DataBindings.Add(new Binding("Text","ab_add"));
??????????? this.txtCompany.DataBindings.Clear();
??????????? this.txtCompany.DataBindings.Add(new Binding("Text","ab_com"));
??????????? this.txtPosition.DataBindings.Clear();
??????????? this.txtPosition.DataBindings.Add(new Binding("Text","ab_position"));
??????????? this.txtTel.DataBindings.Clear();
??????????? this.txtTel.DataBindings.Add(new Binding("Text","ab_tel"));
??????????? this.cbSex.DataBindings.Clear();
??????????? this.cbSex.DataBindings.Add(new Binding("Text","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_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的注入也就不存在了。
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
