個人感覺Linq實用靈活性很大，參考一篇大牛的文章LINQ查詢返回DataTable類型
http://xuzhihong1987.blog.163.com/blog/static/26731587201101853740294/
附上自己寫的一個測試程序源代碼。
下載
//創建自定義DataTable String[] _sFiled = new String[] { "ID", "PC", "EPC", "CRC", "rssI", "FREQANT", "INVCOUNT" };# region Linq寫法 // LinQ寫法1： //var numbers_1 = from number in numbers where (number % 2 == 0) orderby number descending select number;//orderby number descending 這是對篩選出來的數值進行排序 // foreach (var i in numbers_1) // { // Console.WriteLine(i); // } //LinQ寫法2： // var numbers_1 = numbers.Where(i => i % 2 == 0).Select(i => i);//輸出用寫法1一樣#endregion DataTable _dtAudit = CreateSelfDataTable(_sFiled); _dtAudit.Rows.Add("a1", "b1", "c1","","","",""); _dtAudit.Rows.Add("a1", "b2", "c2", "", "", "", ""); _dtAudit.Rows.Add("a2", "b3", "c3", "", "", "", ""); _dtAudit.Rows.Add("a3", "b4", "c4", "", "", "", ""); _dtAudit.Rows.Add("a1", "b5", "c5", "", "", "", ""); _dtAudit.Rows.Add("a2", "b6", "c6", "", "", "", ""); var query1 = (from contact in _dtAudit.AsEnumerable() //查詢 orderby contact.Field<string>("ID") descending //排序 group contact by contact.Field<string>("PC") into g //分組 select new {ID=g.FirstOrDefault().Field<string>("ID"),PC=g.FirstOrDefault().Field<string>("PC"),EPC= g.FirstOrDefault().Field<string>("EPC"),CRC=g.FirstOrDefault().Field<string>("CRC"),RSSI=g.FirstOrDefault( ).Field<string>("RSSI")}).ToList(); DataTable contacts1 =ToDataTable(query1); SetListView(listView1, contacts1); } /// <summary> /// 創建自定義列DataTable /// </summary> /// <param name="sList"></param> /// <returns></returns> public DataTable CreateSelfDataTable(String[] sList) { DataTable _dtSelf = new DataTable(); foreach (String s in sList) { _dtSelf.Columns.Add(s); } _dtSelf.AcceptChanges(); return _dtSelf; } /// <summary> /// 下面通過一個方法來實現返回DataTable類型 /// 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; } /// <summary> /// 綁定ListView /// </summary> private void SetListView(System.Windows.Forms.ListView listView, DataTable dt) { if (listView.Items.Count > 50) { listView.Items.Clear(); } if (dt != null && dt.Rows.Count > 0) { ListViewItem lv = null; for (int i = 0; i < dt.Rows.Count; i++) { lv = new ListViewItem(dt.Rows[i][0].ToString()); //創建一列的多行對象 lv.Tag = dt.Rows[i][0].ToString(); //該lv對象綁定多行一列值 for (int j = 1; j < dt.Columns.Count; j++) { lv.SubItems.Add(dt.Rows[i][j].ToString()); //一行添加多列的值 } listView.Items.Add(lv); } } }
http://xuzhihong1987.blog.163.com/blog/static/26731587201111342756123/
說明：
1、在未作任何說明的情況下，以下用到的db變量都是DataContext的實例對象，聲明語法類似如下：
using (BP_DataClassesDataContext db = new BP_DataClassesDataContext(ConnectionStrings.GetInstance("ZJ").Con))
{
//Code
}
2、默認q為Iqueryable類型變量
簡單查詢：
var q = from p in db.view_PreTestResultAuditList
select p;
簡單條件查詢：
方式一：直接使用where關鍵字
var q = from c in db.view_PrTestList
where c.FTestmanID.Equals(UserContext.CurrentUser.FID)
select c;
方式二：使用lambda 表達式
var q = db.TOriInfoAutoMemory.Where(c => c.FSampleName == sampleName);
使用 &&和|| 帶代替And或Or關鍵字：
var entity = db.TSampleOriginalInfo.FirstOrDefault(c => c.FFromID.Equals(fromID) && c.FSampleID.Equals(sampleID));
like模糊條件查詢：
//使用C#中的Contains關鍵字代替SQL中的like關鍵字
q = q.Where(c => c.FTaskCode.Contains(condition.Name));
In包含查詢：
/// <param name="certificateIDs"> string[] </param>
var certi = (from c in db.TMSCertificate
where certificateIDs.ToList().Contains(c.FID.ToString())
select c).ToList();
等價于：select * from TMSCertificate where FID in /* certificateIDs */
var q = from c in db.view_PrTestList
where c.FTestmanID.Equals(UserContext.CurrentUser.FID)
select c;
twi.data = (q.Skip(paging.startIndex).Take(paging.pageSize)).ToList();
//其中startIndex：跳過的系列中指定的條數
// pageSize：每頁顯示條數（記錄數）
Distinct方法去除重復：
var p = (from c in dc.TSampleOriginalInfo
where sampleIDs.Contains(c.FSampleID.ToString())
select new
{
c.FFromID,
c.FName
}).Distinct();
查詢指定列使new構造集合：
var p = (from c in dc.TSampleOriginalInfo
where sampleIDs.Contains(c.FSampleID.ToString())
select new
{
c.FFromID,
c.FName
}).Distinct();
join連接查詢：詳見下面的【join查詢實例】
//此處省略……
FirstOrDefault查詢滿足條件的第一條記錄或不存在是返回null，不發生異常：
foreach (var fromID in fromIDs)
{
var entity = db.TSampleOriginalInfo.FirstOrDefault(c => c.FFromID.Equals(fromID) && c.FSampleID.Equals(sampleID));
if (entity != null)
{
entities.Add(entity);
}
}
LINQ to SQL 的重要方法：SubmitChanges方法：
無論您對對象做了多少項更改，都只是在更改內存中的副本。您并未對數據庫中的實際數據做任何更改。直到您對 DataContext 顯式調用 SubmitChanges方法，您所做的更改才會傳輸到服務器。
db.SubmitChanges();
新增/刪除/修改后都需要顯式的調用該方法！
以下是示例代碼
/// <summary> /// 添加 /// </summary> /// <param name="varCustomer">要添加的對象</param> /// <returns></returns> public bool New(TTrademark idc) { using (CP_DataClassesDataContext db = new CP_DataClassesDataContext(GS.MODEL.ConnectionStrings.GetInstance("ZJ").Con)) { if (idc != null) { db.TTrademark.InsertOnSubmit(idc); db.SubmitChanges(); return true; } else { return false; } } } /// <summary> /// 新增多個【使用InsertAllOnSubmit】 /// </summary> /// <param name="model"></param> public void New(GS.MODEL.TItem model) { using (CP_DataClassesDataContext db = new CP_DataClassesDataContext(GS.MODEL.ConnectionStrings.GetInstance("ZJ").Con)) { List<GS.MODEL.TItem> entities = new List<GS.MODEL.TItem>(); GS.MODEL.TItem entity = new GS.MODEL.TItem() { FID = Guid.NewGuid(), FName =model.FName , FParentName = model.FParentName, FFastCode = model.FFastCode, FRemark=model.FRemark, fsort=model.FSort }; entities.Add(entity); db.TItem.InsertAllOnSubmit(entities); db.SubmitChanges(); } } /// <summary> /// 修改 /// </summary> /// <param name="model"></param> public void Edit(GS.MODEL.TItem model) { using(CP_DataClassesDataContext db=new CP_DataClassesDataContext(GS.MODEL.ConnectionStrings.GetInstance("ZJ").Con)) { GS.MODEL.TItem items = db.TItem.Single(c => c.FID == model.FID); items.FName = model.FName; items.FParentName = model.FParentName; items.FRemark = model.FRemark; items.FSort = model.FSort; items.FFastCode = model.FFastCode; db.SubmitChanges(); } } /// <summary> /// 刪除 /// </summary> /// <param name="IDs"></param> public void Delete(string[] ids) { using (CP_DataClassesDataContext db = new CP_DataClassesDataContext(GS.MODEL.ConnectionStrings.GetInstance("ZJ").Con)) { List<GS.MODEL.TItem> entities = new List<GS.MODEL.TItem>(); foreach (var id in ids) { var entity = db.TItem.FirstOrDefault(c => c.FID.Equals(id)); if (entity != null) { entities.Add(entity); } } db.TItem.DeleteAllOnSubmit(entities); db.SubmitChanges(); } } /// <summary> /// 【簡單查詢】獲取指定計量標準考核證書下的計量標準器/檢定規程/主要配套設備 /// </summary> /// <param name="certifiacteID"></param> /// <returns></returns> public IList<view_CertificateOriginalInfo> GetViewByCertificateID(Guid certifiacteID) { using (BPDataContext db = new BPDataContext(TCTC_ConnectionStrings.connStrJL)) { var d = from c in db.view_CertificateOriginalInfo where c.FCertificateID == certifiacteID select c; return d.ToList(); } } /// <summary> /// 【綜合查詢】我的待檢測結果任務單 /// </summary> /// <param name="paging"></param> /// <param name="condition"></param> /// <returns></returns> public TwiReturn GetMyWorkList(PagingCondition paging, ConditionModel condition) { TwiReturn twi = new TwiReturn(); using (BP_DataClassesDataContext db = new BP_DataClassesDataContext(ConnectionStrings.GetInstance("ZJ").Con)) { var q = from c in db.view_PrTestList where c.FTestmanID.Equals(UserContext.CurrentUser.FID) select c; if (condition != null) { if (condition.NameSign == "TaskCode" && condition.Name != "") { q = q.Where(c => c.FTaskCode.Contains(condition.Name)); } else if (condition.NameSign == "TestItemName" && condition.Name != "") { q = q.Where(c => c.FTestItemName.Contains(condition.Name)); } } twi.totalCount = q.Count(); if (paging.needPaging) { twi.data = (q.Skip(paging.startIndex).Take(paging.pageSize)).ToList(); } else { twi.data = q.ToList(); } return twi; } } /// <summary> /// 選擇計量標準考核證書 /// </summary> /// <param name="sampleIDs"></param> /// <param name="certificateIDs"></param> public void SelectCertificate(string[] sampleIDs, string[] certificateIDs) { BPDataContext bpDC = new BPDataContext(TCTC_ConnectionStrings.connStrJL); CPDataContext cpDc = new CPDataContext(TCTC_ConnectionStrings.connStrJL); var certi = (from c in cpDc.TMSCertificate where certificateIDs.ToList().Contains(c.FID.ToString()) select c).ToList(); List<TSampleOriginalInfo> entities = new List<TSampleOriginalInfo>(); foreach (var sampleID in sampleIDs) { foreach (var c in certi) { TSampleOriginalInfo entity = new TSampleOriginalInfo() { FID = Guid.NewGuid(), FFromID = c.FID, FType = 1, FSampleID = new Guid(sampleID), FName = c.FCertificateName, FCode = c.FCode, FRange = c.FMeasurementRange, FUncertainty = c.FLevel, FCertificateNo = c.FCode,//c.FNumber,zjw修改 FValidDateTo = c.FPeriodDate }; entities.Add(entity); } } bpDC.TSampleOriginalInfo.InsertAllOnSubmit(entities); bpDC.SubmitChanges(); bpDC.Dispose(); cpDc.Dispose(); } /// <summary> /// 獲取樣品之標準/計量標準器/主要配套設備/檢定規程 /// </summary> /// <param name="sampleIDs"></param> /// <returns></returns> public DataTable GetBySampleIDs(List<string> sampleIDs) { using (BPDataContext dc = new BPDataContext(TCTC_ConnectionStrings.connStrJL)) { var p = (from c in dc.TSampleOriginalInfo where sampleIDs.Contains(c.FSampleID.ToString()) select new { c.FFromID, c.FType, c.FName, c.FCode, c.FRange, c.FModel, c.FUncertainty, c.FCertificateNo, c.FValidDateTo, c.FManufacturer }).Distinct(); return LinqToDataTable.ToDataTable(p.ToList()); } } /// <summary> /// 【join查詢實例】獲取帶檢測的器具信息 /// </summary> /// <returns></returns> public DataTable GetBySampleName(string sampleName) { using (BPDataContext dc = new BPDataContext(TCTC_ConnectionStrings.connStrJL)) { var sample = from c in dc.view_Sample_WithVCItem where c.FEndTag == 1 && c.FTaskEndTag == 1 && c.FOutSourcingTag == 0 && c.FAssignTag == 1 && c.FTestCompleteTag == 0 && c.FIsOuter == "否" select c; var r = from s in sample join v in dc.view_GetSampleLeastOrginalRecord on s.FID equals v.FSampleID into recs from v in recs.DefaultIfEmpty() join t in dc.TTask on s.FTaskID equals t.FID into ts from t in ts where (sampleName == "" ? true : s.FName == sampleName) && (s.FReceiverID == UserContext.CurrentUser.FID || TCTC_ConnectionStrings.TestResultFilterByTester == "0") orderby s.FName, t.FCode, s.FCode select new { SampleID = s.FID, SampleCode = s.FCode, s.FName, s.FAnotherName, s.FTaskID, s.FIsRepair, s.FIsNeedOriginalRecord, s.FTestProperty, s.FRepairTag, s.VCItemID, s.CertiID, s.CertiName, s.TechID, s.TechName, s.TechCode, s.FReTestTag, s.FReTestFromTag, v.FNumber, v.FFileName, v.FCertificateNo, v.FOriginalRecordID, v.FTag, TaskCode = t.FCode, t.FRemark, t.FIsUrgent, s.FTaskType, s.FTaskTypeID, s.FMeasurementRange, s.FLevel, s.FSigPrice }; DataTable dt = LinqToDataTable.ToDataTable(r.ToList()); return dt; } }
新聞熱點
疑難解答
