LTQ等各种
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace 练习LinQ入门
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private mydbDataContext context = new mydbDataContext();
private void Form1_Load(object sender, EventArgs e)
{
FillInfo();
cbx_sex.SelectedIndex = 0;
}
private void FillInfo()
{
//法一：
//List<Info> list = context.Info.ToList();
//dataGridView1.DataSource = list;
//法二：
//var query = from p in context.Info select p;
//dataGridView1.DataSource = query;
//法三：
var query = context.Info;
dataGridView1.DataSource = query;
}
private void btn_ADD_Click(object sender, EventArgs e)
{
Info data = new Info();
data.Code = txt_code.Text;
data.Name = txt_name.Text;
data.Nation = txt_nation.Text;
data.Sex = Convert.ToBoolean(txt_sex.Text);
data.Birthday = Convert.ToDateTime(txt_b.Text);
context.Info.InsertOnSubmit(data);
context.SubmitChanges();//提交
FillInfo();
}
private void button2_Click(object sender, EventArgs e)
{
var query = from p in context.Info where p.Code == txt_code.Text select p;
if (query.Count() > 0)
{
// query.Single();//第一条
Info data = query.First();
context.Info.DeleteOnSubmit(data);
context.SubmitChanges();
}
FillInfo();
//Form p in context.Info where
// Info data = context.Info.Where( => );
}
private void button1_Click(object sender, EventArgs e)
{
var a = from p in context.Info where p.Code == txt_code.Text select p;
if (a.Count() > 0)
{
Info data = a.First();
data.Name = txt_name.Text;
data.Sex = Convert.ToBoolean(txt_sex.Text);
data.Nation = txt_nation.Text;
data.Birthday = Convert.ToDateTime(txt_b.Text);
context.SubmitChanges();
}
FillInfo();
}
private void btn_select_Click(object sender, EventArgs e)
{
#region meiyong
//// e.Equals();
//Type t = e.GetType();
////t.GetProperties();
////t.GetMethods();
//Type s = sender.GetType();
#endregion
Button btn = (Button)sender;
if (btn.Name == "btn_select")
{
if (tbx_code2.Text != "")
{
//单条件等值查询
//var query = from p in context.Info where p.Code == tbx_code2.Text.Trim() select p;
var query = context.Info.Where(p => p.Code == tbx_code2.Text.Trim());//扩展方法 Lambda表达式
dataGridView1.DataSource = query;
}
}
else if (btn.Name == "btn_select2")
{
if (tbx_code2.Text != "")
{
//单条件不等值查询
//var query = from p in context.Info where p.Code != tbx_code2.Text.Trim() select p;
//>= <= 同样可以用于筛选
var query = context.Info.Where(p => p.Code != tbx_code2.Text.Trim());
dataGridView1.DataSource = query;
}
}
else if (btn.Name == "btn_duo")
{
//多条件查询
var query = from p in context.Info where p.Sex == Convert.ToBoolean(cbx_sex.Text.Trim()) && p.Birthday.Value.Year > Convert.ToInt32(tbx_b_duo.Text.Trim()) select p;
//或者
//var query1 = context.Info.Where(p => p.Sex == Convert.ToBoolean(cbx_sex.Text.Trim()) && p.Birthday.Value.Year > Convert.ToInt32(tbx_b_duo.Text.Trim()));
//var a=context.Info.Where (p=>p.Sex==Convert.ToBoolean(cbx_sex.Text.Trim())).Where(p=>p.Birthday.Value.Year> Convert.ToInt32(tbx_b_duo.Text.Trim()));//链式表达式 （&& 逻辑与可以用）
dataGridView1.DataSource = query;
}
else if (btn.Name == "btn_select_mohu")
{
//var query = from p in context.Info where p.Name.Contains(tbx_xing.Text.Trim()) select p;//相当于like'%某'
//var query = from p in context.Info where p.Name.StartsWith(tbx_xing.Text.Trim()) select p;//以什么开头
//var query = from p in context.Info where p.Name.EndsWith(tbx_xing.Text.Trim()) select p;//以什么结尾
//var query = from p in context.Info where p.Name.Substring(1, tbx_xing.TextLength) == tbx_xing.Text.Trim() select p;//截取第一位往后
var query = context.Info.Where(p => p.Name.StartsWith(tbx_xing.Text.Trim()));
dataGridView1.DataSource = query;
}
else if (btn.Name == "btn_shua")
{
FillInfo();
}
else if (btn.Name == "btn_jiao")
{
//交集相当于逻辑与 取公共部分
var p1 = from p in context.Info where p.Sex == true select p;
var p2 = context.Info.Where(p => p.Birthday.Value.Year > 1985);
var query = p1.Intersect(p2);
dataGridView1.DataSource = query;
}
else if (btn.Name == "btn_bing")
{
//并集相当于逻辑或
var p1 = from p in context.Info where p.Sex == true select p;
var p2 = context.Info.Where(p => p.Birthday.Value.Year > 1985);
var query = p1.Union(p2);
dataGridView1.DataSource = query;
}
else if (btn.Name == "btn_bu")
{
//差集 从一个集合减去另外一个集合 看谁减谁
var p1 = from p in context.Info where p.Sex == true select p;
var p2 = context.Info.Where(p => p.Birthday.Value.Year > 1985);
var query = p1.Except(p2);
dataGridView1.DataSource = query;
}
}
private void btn_jige_Click(object sender, EventArgs e)
{
Button btn =(Button)sender;
if (btn.Name=="btn_jige")
{
var query = context.Info.Where(p => p.Birthday.Value.Year > 1985);
this.Text = query.Count().ToString();//个数
}
else if (btn.Name=="btn_sum")
{
var query = context.Info.Where(p => p.Birthday.Value.Year > 1985);
this.Text = query.Sum(p=>DateTime.Now.Year-p.Birthday.Value.Year).ToString();//求和
}
else if (btn.Name == "btn_avg")
{
var query = context.Info.Where(p => p.Birthday.Value.Year > 1985);
this.Text = query.Average(p => DateTime.Now.Year - p.Birthday.Value.Year).ToString();//求平均值
}
}
private void button5_Click(object sender, EventArgs e)
{
var query = context.Info.Where(p => p.Birthday.Value.Year > 1985);
List<Info> list = query.ToList();//转集合
dataGridView1.DataSource = list;
}
private void button7_Click(object sender, EventArgs e)
{
var query = context.Info.Where(p => p.Birthday.Value.Year > 1985);
Info[] list = query.ToArray();//转数组
dataGridView1.DataSource = list;
}
private void button6_Click(object sender, EventArgs e)
{
var query = context.Info.Where(p => p.Birthday.Value.Year > 1985);
//Info data = query.Single();//单一
Info data = query.First();
dataGridView1.DataSource = data;
}
private void button3_Click(object sender, EventArgs e)
{
//每页多少条pagesize，要找第几页pageno
var query=context.Info.Skip(1*1).Take(2);//skip跳过多少行，take取多少行
dataGridView1.DataSource=query;
}
}
}
练习源码：http://pan.baidu.com/s/1c0wJ8i8
LinQ to sql 入门小教程：http://pan.baidu.com/s/1dD2NkcD
1.查询所有的
var query = from p in _Context.Info select p;
var query = _Context.Info;
2.单条件查询
等值查
var query = from p in _Context.Info where p.Code == "p002" select p;
注意：双等号，双引号。---C#语法。
var query = _Context.Info.Where(p => p.Code == "p002" ); //Lambda表达式
不等值查
//var query = from p in _Context.Info where p.Code != "p002" select p;
//var query = _Context.Info.Where(p => p.Code != "p002"); //Lambda表达式
//var query = from p in _Context.Info where p.Birthday.Value.Year > 1985 select p;
var query = _Context.Info.Where(p=>p.Birthday.Value.Year < 1985);
3.多条件查询
//var query = from p in _Context.Info where p.Sex == true && p.Birthday.Value.Year > 1985 select p;
//var query = _Context.Info.Where(p=>p.Sex==true && p.Birthday.Value.Year>1985);
var query = _Context.Info.Where(p => p.Sex == true).Where(p=>p.Birthday.Value.Year>1985) ; //多条件逻辑与可以用链式表达式写
4.模糊查询
//var query = from p in _Context.Info where p.Name.Contains("张") select p; //相当于like '%张%'
//var query = from p in _Context.Info where p.Name.StartsWith("张") select p; // like '张%'
//var query = from p in _Context.Info where p.Name.EndsWith("张") select p; //like '%张'
//var query = from p in _Context.Info where p.Name.Substring(1,1) == "张" select p;// like '_张%'
var query = _Context.Info.Where(p => p.Name.StartsWith("张"));
5.集合操作
var q1 = from p in _Context.Info where p.Sex== true select p;
var q2 = _Context.Info.Where(p => p.Birthday.Value.Year > 1985);
交
var query = q1.Intersect(q2);
并
var query = q1.Union(q2);
差
var query = q2.Except(q1);
6.统计函数：
var query = _Context.Info.Where(p => p.Birthday.Value.Year > 1985);
//this.Text = query.Count().ToString();
//this.Text = query.Sum(p=>DateTime.Now.Year - p.Birthday.Value.Year).ToString();
//this.Text = query.Average(p => DateTime.Now.Year - p.Birthday.Value.Year).ToString();
//this.Text = query.Max(p => DateTime.Now.Year - p.Birthday.Value.Year).ToString();
this.Text = query.Min(p => DateTime.Now.Year - p.Birthday.Value.Year).ToString();
7.转换操作
ToList(),ToArray(),First()/Single()
8.分页
var query = _Context.Info.Skip(2*2).Take(2);
