/*********************************************************************************************
* LINQ TO SQL 的本质作用是让开发人员从原来的直接操作非面向对象的ADO.NET方式变成面向数据库对象实体的操作
* LINQ TO SQL做了这样的事情，按照需要用的数据库建立数据库的实体对象。
* ******************************************************************************************/
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;
using System.Data.Linq;
using System.Data.SqlClient;
namespace LinqExample
{
public partial class Form1 : Form
{
public DataClasses1DataContext dc;
public Form1()
{
InitializeComponent();
dc = new DataClasses1DataContext();
}
private void button1_Click(object sender, EventArgs e)
{
dc = new DataClasses1DataContext(); //刷新的土办法，就是重新将整个实体类和数据库重新做映射
dataGridView1.DataSource = dc.Customers;
}
private void AddBtn_Click(object sender, EventArgs e)
{
#region 增加
Customers c = new Customers();
c.Address = "仙鹤山庄";
c.City = "南京";
c.CompanyName = "公司名称";
c.ContactName = "协议名称";
c.ContactTitle = "协议标题";
c.Country = "中国";
c.CustomerID = "客户I";
c.Fax = "02552888888";
c.Phone = "13851485111";
c.PostalCode = "210006";
c.Region = "栖霞区";
dc.Customers.InsertOnSubmit(c); //添加到C#的模型库中
dc.Transaction = dc.Connection.BeginTransaction(); //显式启用事务
dc.SubmitChanges(); //模型库提交到类库中,SubmitChanges在提交时会自动启动一个事务
dc.Transaction.Commit(); //提交事务
//dc.Refresh(RefreshMode.KeepCurrentValues, dc.Customers); //有问题，不能提交？
//添加数据后如何即时刷新显示数据的控件如datagridview
#endregion
}
private void ModifyBtn_Click(object sender, EventArgs e)
{
#region 修改，修改也是针对实体类进行修改然后将改变提交到数据库
//方法一
//foreach (var cus in dc.Customers) //循环操作和增加记录里的 dc.Customers.InsertOnSubmit(c)本质是一样的，都是对实体类进行操作
//{
// if (cus.Country == "中国")
// {
// cus.CompanyName = "阿修罗网络公司";
// }
//}
//方法二
//这边罗嗦几句，微软的linq查询语句越来越像是人在说话，Where其实是表达的一种什么情况下
//foreach后的{}中的内容表示会发生什么事
foreach (var cus in dc.Customers.Where(
cus => (cus.Country == "中国")))
{
cus.CompanyName = cus.CompanyName = "阿修罗网络公司"; //可能有多条记录
}
dc.SubmitChanges(); //LINQ本身提供了即时更新数据的功能，一修改就能看见效果,一次性提交能否一次性改多条呢？
#endregion
}
private void DelBtn_Click(object sender, EventArgs e)
{
#region 删除 和修改一样，是针对某种情况下做出删除动作，注意是某种情况下
foreach (var cus in dc.Customers.Where(
cus => (cus.Country == "中国")))
{
dc.Customers.DeleteOnSubmit(cus);
}
dc.SubmitChanges();
#endregion
}
private void button3_Click(object sender, EventArgs e)
{
//创建查询
IQueryable<Customers> query =
from cust in dc.Customers
where cust.CompanyName == "Alfreds Futterkiste"
select cust;
//执行查询
foreach (var customer in query) //query结果是一个集
{
listView1.Items.Add(customer.CustomerID);
}
//熟悉LINQ语句结构
}
private void button4_Click(object sender, EventArgs e)
{
//练习复杂一点的查询，如何返回两个对象中某个字段相同的记录
//一对多
dc.Log = Console.Out;
var q =
from c in dc.Customers
from o in c.Orders
where c.CustomerID == "HANAR"
select o;
foreach (var oo in q)
{
listView1.Items.Add(oo.OrderID.ToString());
}
}
private void button5_Click(object sender, EventArgs e)
{
var q =
from c in dc.Customers
join o in dc.Orders on c.CustomerID equals o.CustomerID
select new { 客户ID = c.CustomerID, 公司名称 = c.CompanyName, 订单某列 = o.EmployeeID };
//用join语句以后select 的可选择范围是2个表的按c.CustomerID equals o.CustomerID组成的合集
//select 这边的select 决定了q的表现结果
//select o 那么 q表现的就是Orders表的所有字段,select c 一样
//select new {} 里的字段是要取的字段，可以随意写一个或多个
dataGridView1.DataSource = q;
//foreach (var oo in q)
//{
// listView1.Items.Add(oo.OrderID.ToString());
//}
}
private void button2_Click(object sender, EventArgs e)
{
//如何高效分页，类似存储过程分页
}
private void button6_Click(object sender, EventArgs e)
{
//如何使用存储过程
/**单一标量返回
create procedure GetOneRecordProcedure
(@param1 nvarchar(15))
as
begin
set nocount on
declare @count int
select @count = count(*) from customers
where customers.region = @param1
return @count
end
* */
MessageBox.Show("BC区有客户" + dc.MyProcedure("BC").ToString() + "个");
/**单一结果集
* create procedure GetOneDatasetProcedure
(@param1 nvarchar(15))
as
begin
set nocount on
select * from customers
where customers.region = @param1
end
*
* **/
//dataGridView1.DataSource = dc.GetOneDatasetProcedure("BC");
/**多个结果集
* 默认是返回一个 结果集，需要重新ORM生成器生成的部分代码
* http://www.cnblogs.com/lyj/archive/2008/03/17/1110374.html
* 可以参考这个哥们的写法即可
*
* **/
dataGridView1.DataSource = dc.ManyDatasetProcedure("ANTON");
/**输出参数
* create procedure GetOutputArgsProcedure
(@param1 nvarchar(15),@totalsales money output)
as
begin
set nocount on
select @totalsales = sum(employeeid)
from orders
where orders.customerid=@param1
end
*
* **/
decimal? totalSales = 0;
string customerID = "ALFKI";
dc.GetOutputArgsProcedure(customerID, ref totalSales);
MessageBox.Show(totalSales.ToString());
}
}
public static class extensions //类名可以随便写 这是扩展方法
{
public static void foo(this string s) //this 实例方法转为静态方法
{
//有什么意义？
}
}
public class test
{
public void testmethod()
{
string t = "hello";
t.foo();
}
}
}
浙公网安备 33010602011771号