首页 > > 网络编程 > Asp.Net编程 >

LINQ查询返回DataTable类型

2018-06-22 06:32:58来源：未知 阅读 ()

新老客户大回馈,云服务器低至5折

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

 

使用 &&和|| 带代替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 */

 

Skip和Take实现分页查询：

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;

            }

        }

标签：

版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点，本站所提供的摄影照片，插画，设计作品，如需使用，请与原作者联系，版权归原作者所有

上一篇：接口和抽象类有什么区别

下一篇：递归实现阶乘

相关文章

IDC资讯： 主机资讯 注册资讯 托管资讯 vps资讯 网站建设

网站运营： 建站经验 策划盈利 搜索优化 网站推广 免费资源

网站联盟： 联盟新闻 联盟介绍 联盟点评 网赚技巧

行业资讯： 搜索引擎 网络游戏 电子商务 广告传媒

网络编程： Asp.Net编程 Asp编程 Php编程 Xml编程 Access Mssql Mysql 其它

服务器技术： Web服务器 Ftp服务器 Mail服务器 Dns服务器 安全防护

软件技巧： 其它软件 Word Excel Powerpoint Ghost Vista QQ空间 QQ FlashGet 迅雷

网页制作： FrontPages Dreamweaver Javascript css photoshop fireworks Flash

程序设计： Java技术 C/C++ VB delphi

网络知识： 网络协议 网络安全 网络管理 组网方案 Cisco技术

操作系统： Win2000 WinXP Win2003 Mac OS Linux FreeBSD

热门词条
最新资讯
热门关注
热门标签