最近闲来无事,整理LINQ的一简单用法,对新学者有一些帮助。
主要是涉及:查询、修改、删除、多表连接、左右连接,多条件动态,模糊检索
例表: 一个促销计划的表两个,一个商品档案表
1、促销主表
create table SPPROMO (
JLBH int NOT NULL,
DEPTID varchar(20) not null,
KSRQ datetime not null,
JSRQ datetime not null,
DJR int not null,
DJRQ datetime not null,
SHR int null,
SHRQ datetime null,
ZXR int null,
ZXRQ datetime null,
CXZT varchar(100) null, --主题
DJZT int null
)
2、促销明细表
create table SPPROMOITEM(
JLBH int NOT NULL,
INX INT NOT NULL,
ZCID int NOT NULL,
OLD_LSDJ float default 0 null,
NEW_LSDJ float default 0 null)
3、商品档案范表
create table ZCXX (
ZCID INT not null,
SPCODE varchar(20) not null,
BARCODE varchar(20) null,
NAME varchar(100) null,
GGXH varchar(100) null,
LSDJ decimal default 0 null
)
4、LINQ返回DataTable类型调用方法
/// <summary>
/// LINQ返回DataTable类型
/// </summary>
/// <typeparam name="T"> </typeparam>
/// <param name="varlist"> </param>
/// <returns> </returns>
public static DataTable ToDataTable<T>(IEnumerable<T> varlist)
{
DataTable dtReturn = new DataTable();
// column names
PropertyInfo[] oProps = null;
if (varlist == null)
return dtReturn;
foreach (T rec in varlist)
{
if (oProps == null)
{
oProps = ((Type)rec.GetType()).GetProperties();
foreach (PropertyInfo pi in oProps)
{
Type colType = pi.PropertyType;
if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition()
== typeof(Nullable<>)))
{
colType = colType.GetGenericArguments()[0];
}
dtReturn.Columns.Add(new DataColumn(pi.Name, colType));
}
}
DataRow dr = dtReturn.NewRow();
foreach (PropertyInfo pi in oProps)
{
dr[pi.Name] = pi.GetValue(rec, null) == null ? DBNull.Value : pi.GetValue
(rec, null);
}
dtReturn.Rows.Add(dr);
}
return dtReturn;
}
一、查询
var dblinq = new DataClassesYYGRDataContext();
var query = from A in dblinq.SPPROMOITEM
join B in dblinq.ZCXX on A.ZCID equals B.ZCID
where A.JLBH == 记录号
select new
{
A.JLBH,
A.INX,
A.ZCID,
B.SPCODE,
B.BARCODE,
B.NAME,
B.GGXH,
A.OLD_LSDJ,
A.NEW_LSDJ
};
DataTable cxdtable = ToDataTable(query); // linq 转 datatable
二、删除
/// <summary>
/// 删除数据 促销明细表
/// </summary>
/// <param name="pJLBH"></param>
/// <param name="pZCID"></param>
public void delcxd(int pJLBH)
{
DataClassesYYGRDataContext db = new DataClassesYYGRDataContext();
var UP1 = from A in db.SPPROMOITEM
where (A.JLBH == pJLBH)
select A;
foreach (var s in UP1)
{
db.SPPROMOITEM.DeleteOnSubmit(s);
}
db.SubmitChanges();
}
三、修改
/// <summary>
/// 更新目标数据
/// </summary>
/// <param name="pJLBH"></param>
/// <param name="pZCID"></param>
public void upcxd(int pZCID)
{
DataClassesYYGRDataContext db = new DataClassesYYGRDataContext();
for (int i = 0; i < cxdtable.Rows.Count; i++)
{
if (Convert.ToInt32(cxdtable.Rows[i]["ZCID"])==pZCID)
{
var result = from A in db.SPPROMOITEM
where (A.JLBH == (记录号) && (A.ZCID == (代码ID)))
//两个条件给合,可以传值
select A;
foreach (SPPROMOITEM info in result)
{
info.ZCID = Convert.ToInt32(cxdtable.Rows[i]["ZCID"]);
info.OLD_LSDJ = Convert.ToDouble(cxdtable.Rows[i]["OLD_LSDJ"]);
info.NEW_LSDJ = Convert.ToDouble(cxdtable.Rows[i]["NEW_LSDJ"]);
db.SubmitChanges();
}
}
}
}
四、左边接,及模糊查询,进行 LIKE 查询
LIKE 查询 : StartsWith Contains EndsWith
例: string s1 = '01'
query.Where(A => A.DEPTID.StartsWith(s1 )); // where A.DEPTID LIKE '01%'
query.Where(A => A.DEPTID.Contains (s1 )); // where A.DEPTID LIKE '%01%'
query.Where(A => A.DEPTID.EndsWith(s1 )); // where A.DEPTID LIKE '%01'
/// <summary> 查询促销主表信息
/// 获取原始数据源,填充
/// </summary>
/// <param name="P1"></param>
/// <returns></returns>
private void GetSourceData()
{
var dblinq = new DataClassesYYGRDataContext();
var query = from A in dblinq.SPPROMO
join B in dblinq.BM_KS on A.DEPTID equals B.DEPTID
join C in dblinq.RYXX on A.DJR equals C.RYID into joindjrlist //左连接
from C in joindjrlist.DefaultIfEmpty()
join E in dblinq.RYXX on A.SHR equals E.RYID into joinshrlist
from E in joinshrlist.DefaultIfEmpty()
join F in dblinq.RYXX on A.ZXR equals F.RYID into joinzxrlist
from F in joinzxrlist.DefaultIfEmpty()
select new
{
A.JLBH,
B.DEPTID,
B.DEPT_NAME,
KSRQ=Convert.ToString(A.KSRQ.Date),
JSRQ = Convert.ToString(A.JSRQ.Date),
DJR = C.RYMC,
DJRQ=Convert.ToString(A.DJRQ.Date),
SHR = E.RYMC,
A.SHRQ,
ZXR=F.RYMC,
A.ZXRQ
};
if ((jlbhbox.Text.Trim().Length) > 0)
{
int p1 = int.Parse(jlbhbox.Text.Trim());
query = query.Where(A => A.JLBH == p1);
}
if (ddlist_bm.SelectedValue != "-1") //模糊检索
{
query = query.Where(A => A.DEPTID.StartsWith(ddlist_bm.SelectedValue));
}
dbp.View_show_table(ToDataTable(query), grid_mx);
//有排序功能,无合计合功能,返回页面GRID显示
}
//返回SQL示例
exec sp_executesql N'SELECT [t5].[JLBH], [t5].[DEPTID], [t5].[DEPT_NAME], [t5].[value] AS [KSRQ], [t5].[value2] AS [JSRQ], [t5].[value3] AS [DJR], [t5].[value4] AS [DJRQ], [t5].[value5] AS [SHR], [t5].[SHRQ], [t5].[value6] AS [ZXR], [t5].[ZXRQ]
FROM (
SELECT [t0].[JLBH], [t1].[DEPTID], [t1].[DEPT_NAME], CONVERT(NVarChar(MAX),CONVERT(DATE, [t0].[KSRQ])) AS [value], CONVERT(NVarChar(MAX),CONVERT(DATE, [t0].[JSRQ])) AS [value2], [t2].[RYMC] AS [value3], CONVERT(NVarChar(MAX),CONVERT(DATE, [t0].[DJRQ])) AS [value4], [t3].[RYMC] AS [value5], [t0].[SHRQ], [t4].[RYMC] AS [value6], [t0].[ZXRQ]
FROM [dbo].[SPPROMO] AS [t0]
INNER JOIN [dbo].[BM_KS] AS [t1] ON [t0].[DEPTID] = [t1].[DEPTID]
LEFT OUTER JOIN [dbo].[RYXX] AS [t2] ON [t0].[DJR] = [t2].[RYID]
LEFT OUTER JOIN [dbo].[RYXX] AS [t3] ON [t0].[SHR] = ([t3].[RYID])
LEFT OUTER JOIN [dbo].[RYXX] AS [t4] ON [t0].[ZXR] = ([t4].[RYID])
) AS [t5]
WHERE [t5].[DEPTID] LIKE @p0',N'@p0 varchar(8000)',@p0='01%'
5、新增
// 新增数据 , 直接执行,偷懒,利用数据库唯一索引判断记录重复。
int iJLBH = int.Parse(jlbhbox.Text.Trim());
List<Dictionary<string, object>> newAddedList = grid_mx.GetNewAddedList(); //取GRID新增的数据。
for (int i = newAddedList.Count - 1; i >= 0; i--)
{
DataClassesYYGRDataContext db = new DataClassesYYGRDataContext();
SPPROMOITEM s = new SPPROMOITEM();
s.JLBH = iJLBH;
s.INX = GetNextRowID();
s.ZCID = int.Parse(newAddedList[i]["ZCID"].ToString());
s.OLD_LSDJ = Convert.ToDouble((newAddedList[i]["OLD_LSDJ"].ToString()));
s.NEW_LSDJ = Convert.ToDouble((newAddedList[i]["NEW_LSDJ"].ToString()));
try
{
db.SPPROMOITEM.InsertOnSubmit(s);
db.SubmitChanges();
}
catch (Exception)
{
ShowNotify(newAddedList[i]["SPCODE"].ToString()+"记录重复");
return;
}