首页 > 学院 > 开发设计 > 正文

LINQ查询返回DataTable类型

2019-11-14 16:33:08
字体:
来源:转载
供稿:网友

个人感觉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);                }            }        }
View Code
 

 

 

LINQ基本语法及其示例  

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);

 

使用 &&|| 带代替AndOr关键字:

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 */

 

SkipTake实现分页查询:

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;            }        }  

发表评论 共有条评论
用户名: 密码:
验证码: 匿名发表