Linq to SQL Like Operator(转载)
As a response for customer's question, I decided to write about using Like Operator in Linq to SQL queries.
Starting from a simple query from Northwind Database;
var query = from c in ctx.Customers
where c.City == "London"
select c;
The query that will be sent to the database will be:
SELECT CustomerID, CompanyName, ...
from dbo.Customers
WHERE City = [London]
There are some ways to write a Linq query that reaults in using Like Operator in the SQL statement:
1. Using String.StartsWith or String.Endswith
Writing the following query:
var query = from c in ctx.Customers
where c.City.StartsWith("Lo")
select c;
will generate this SQL statement:
SELECT CustomerID, CompanyName, ...
from dbo.Customers
WHERE City LIKE [Lo%]
which is exactly what we wanted. Same goes with String.EndsWith.
But, what is we want to query the customer with city name like "L_n%"? (starts with a Capital 'L', than some character, than 'n' and than the rest of the name). Using the query
var query = from c in ctx.Customers
where c.City.StartsWith("L") && c.City.Contains("n")
select c;
generates the statement:
SELECT CustomerID, CompanyName, ...
from dbo.Customers
WHERE City LIKE [L%]
AND City LIKE [%n%]
which is not exactly what we wanted, and a little more complicated as well.
2. Using SqlMethods.Like method
Digging into System.Data.Linq.SqlClient namespace, I found a little helper class called SqlMethods, which can be very usefull in such scenarios. SqlMethods
相关文档:
--exec [P_AutoGenerateNumber] 'reception_apply','generate_code','',7
/*
过程说明:生成自动编号
创建时间:2010年1月12日
作者:feng
debug:尚未考虑编号溢出情况
*/
ALTER proc [P_AutoGenerateNumber]
(
@table ......
/*
触发器获取SQL语句增量传输
功能:捕捉修改表的SQL语句
使用说明: 1、先新建一表手动写入主键信息或者唯一索引
Create table prmary_key
(tab_name varchar(255),
key_name varchar(255))
--此表仅在建立触发器时使用,建完所有触发器后 记得 ......
1.集合操作
学习oracle
中集合操作的有关语句,
掌握union,union
all,minus,interest的使用,能够描述结合运算,并且能够将多个查询组合到一个查询中去,能够控制行返回的顺序。
包含集合运算的
查询称为复合查询。见表格1-1
表1-1
Operator Returns &nb ......
Repeater在前台使用比较灵活自由,但有一个问题就是Repeater不支持直接分页,这个很多人看起来就有点不想用了,但我想大家都知道GridView控件或DataGrid控件在启用自带分页的时候其实效率是非常低的,大的不说,一但到了百万级数据以后,就会感觉是多么的痛苦和无耐了,所以即使是用DataGrid(GridView)控件,高手们还是只会 ......
【AUTOTRACE】SQL优化的重要工具--AUTOTRACE
提到SQL优化,不能不提AUTOTRACE的强大功能。使用起来非常便捷,不过在是使用之前,需要做一些配置的工作。简要的描述一下这个过程,供没有使用过的朋友参考。
1.使用sys用户执行plustrce脚本
sys@ora10g> @?/sqlplus/admin/plustrce
sys@ora10g> drop role plustrace ......