C# 使用Linq联合SQL与Oracle查询的例子

本文主要是介绍C# 使用Linq联合SQL与Oracle查询的例子，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

思路就是从SQL拿到本地的表，从Oracle拿到本地的表，用Linq连接本地的两张表，并做一些查询

   private void QueryWithTiptop()
        {
            string sql = @"SELECT jm.Company 
                          ,jm.JobNum 
                          ,jm.AssemblySeq 
                          ,jm.MtlSeq 
                          ,jm.PartNum 
                          ,jm.Description 
                          ,jm.RequiredQty 
                          ,jm.IssuedQty 
                          ,jm.RequiredQty-jm.IssuedQty NeedIssueQty
                          ,jm.IUM 
                          ,ISNULL(t0.OnhandQty,0) StockQty 
                          ,ISNULL(t1.InsQty,0) InsQty 
                          ,(CASE WHEN t2.tiptopPart_c IS NULL THEN ' ' ELSE t2.tiptopPart_c END) tiptopPart_c
                    FROM erp.JobMtl jm 
                    LEFT JOIN (SELECT p.PartNum,p.tiptopPart_c FROM dbo.Part p WHERE  LEN(p.tiptopPart_c) > 0 GROUP BY  p.PartNum,p.tiptopPart_c)t2
                         ON jm.PartNum =  t2.PartNum
                    LEFT JOIN (SELECT pb.Company,pb.PartNum,SUM(pb.OnhandQty) OnhandQty FROM erp.PartBin pb  
where pb.WarehouseCode not in ('MR','CHS','DW4','DW5','DW8','HW4','HW5','HW8')
GROUP BY pb.PartNum,pb.Company) t0
                         ON jm.Company=t0.Company AND jm.PartNum=t0.PartNum
                    LEFT JOIN (SELECT v.Company,v.PartNum,SUM(v.OurQty) InsQty FROM dbo.IQC_AQL_View v  GROUP BY v.Company,v.PartNum )t1
                         ON jm.Company = t1.Company AND jm.PartNum = t1.PartNum
                    WHERE jm.JobNum='" + JobNum + "' AND jm.Company='" + Util.EpicorHelper.GetSession().CompanyID + "'";
            DataTable EpicorTable = new DataTable();
            EpicorTable.Clear();
            EpicorTable = KwonnieUtil.Util.DBHelper.ExecuteDt(sql);
            string InString = "";
            if (EpicorTable.Rows.Count > 0)
            {

                for (int i = 0; i < EpicorTable.Rows.Count; i++)
                {

                    InString = InString + "'" + EpicorTable.Rows[i][12].ToString() + "',";
                }
                InString = InString.Substring(1, InString.Length - 3);
            }
            sql = $@"WITH 自制在检量
                         AS 
                     (SELECT QCF021 AS PartNum,SUM(QC_QTY) AS INS_QTY FROM 
                           (SELECT QCF01,QCF02,QCF021,QCF091,QCF14,QCFACTI,SFB04
                                   ,(QCF091 - SUM(CASE WHEN  SFV01 IS NULL OR SFUPOST = 'N' THEN  0 ELSE SFV09 END)) QC_QTY
                                FROM QCF_FILE INNER JOIN SFB_FILE ON QCF02 = SFB01
                                     LEFT JOIN SFV_FILE ON QCF01 = SFV17  AND QCF02 = SFV11
                                     LEFT JOIN SFU_FILE ON SFV01 = SFU01             
                                WHERE SFB04 <> '8' AND QCFACTI = 'Y'
                                      AND  QCF021 IN ('{InString}')
                                GROUP BY  QCF01,QCF02,QCF021,QCF091,QCF14,QCFACTI,SFB04
                             )
                            GROUP BY QCF021
                            HAVING SUM(QC_QTY) >0
                      ),
                     外购在检量
                        AS                                             
                     (SELECT QCS021 AS PartNum,SUM(QC_QTY) AS INS_QTY FROM  
                            (SELECT QCS01,QCS02,QCS021,QCS03,QCS091,QCS14,QCSACTI
                                   ,(QCS091 - SUM(CASE WHEN T2.RVUCONF IS NULL OR T2.RVUCONF = 'N' THEN 0 ELSE T1.RVV17 END )) QC_QTY
                                 FROM QCS_FILE LEFT JOIN (SELECT RVV01,RVV02,RVV03,RVV04,RVV05,RVV17 FROM RVV_FILE WHERE RVV03 = '1') T1 ON QCS01 = T1.RVV04 AND QCS02 = T1.RVV05
                                               LEFT JOIN (SELECT RVU01,RVUCONF FROM RVU_FILE WHERE RVU00 = '1') T2 ON T1.RVV01 = T2.RVU01
                                 WHERE QCS00 = '1' AND QCS091 > 0 AND QCSACTI = 'Y' 
                                       AND QCS021 IN ('{InString}') 
                                 GROUP BY QCS01,QCS02,QCS021,QCS03,QCS091,QCS14,QCSACTI,T1.RVV17,T1.RVV01,T2.RVUCONF
                             )
                            GROUP BY QCS021 
                            HAVING SUM(QC_QTY) >0
                      ),
                     总在检量 
                        AS
                      (SELECT PartNum ,SUM(INS_QTY) AS INS_QTY
                             FROM (SELECT * FROM 自制在检量 UNION SELECT * FROM 外购在检量) 
                            GROUP BY PartNum 
                      ),
                     库存量
                       AS
                     (SELECT IMG01 AS PartNum,SUM(IMG10) AS STOCK_QTY FROM IMG_FILE 
                           WHERE IMG01 IN ('{InString}')
                           GROUP BY IMG01
                      ),
                      结果集
                        AS
                     (SELECT (CASE WHEN T1.PartNum IS NULL THEN T2.PartNum ELSE T1.PartNum END) AS tiptopPart_c,T1.STOCK_QTY,T2.INS_QTY
                            FROM 库存量 T1 FULL JOIN 总在检量 T2 ON T1.PartNum = T2.PartNum
                      )
                      SELECT tiptopPart_c,to_char(STOCK_QTY) AS STOCK_QTY,to_char(INS_QTY) AS INS_QTY FROM 结果集 ";
            DataTable TiptopTable = new DataTable();
            TiptopTable.Clear();
            TiptopTable = KwonnieUtil.Util.OracleHelper.ExecuteDataTable(sql);
            var Result = from epicor in EpicorTable.AsEnumerable()
                         join tiptop in TiptopTable.AsEnumerable()
                         on epicor.Field<string>("tiptopPart_c") equals tiptop.Field<string>("tiptopPart_c") into joinlist
                         from tiptop in joinlist.DefaultIfEmpty()
                         orderby epicor.Field<int>("MtlSeq")
                         select new
                         {
                             Company = epicor.Field<string>("Company"),
                             JobNum = epicor.Field<string>("JobNum"),
                             AssemblySeq = epicor.Field<int>("AssemblySeq"),
                             MtlSeq = epicor.Field<int>("MtlSeq"),
                             PartNum = epicor.Field<string>("PartNum"),
                             Description = epicor.Field<string>("Description"),
                             RequiredQty = epicor.Field<decimal>("RequiredQty"),
                             IssuedQty = epicor.Field<decimal>("IssuedQty"),
                             NeedIssueQty = epicor.Field<decimal>("NeedIssueQty"),
                             IUM = epicor.Field<string>("IUM"),
                             StockQty = epicor.Field<decimal>("StockQty"),
                             InsQty = epicor.Field<decimal>("InsQty"),
                             tiptopPart_c = epicor.Field<string>("tiptopPart_c"),
                             STOCK_QTY = tiptop != null ? tiptop.Field<string>("STOCK_QTY") : null,
                             INS_QTY = tiptop != null ? tiptop.Field<string>("INS_QTY") : null,
                             //STOCK_QTY = tiptop.Field<string>("STOCK_QTY"),
                             // INS_QTY = tiptop.Field<string>("INS_QTY"),
                         };
            DataTable ResultTable = new DataTable();
            ResultTable.Columns.Add("公司", typeof(string));
            ResultTable.Columns.Add("工單號", typeof(string));
            ResultTable.Columns.Add("次半成品序號", typeof(int));
            ResultTable.Columns.Add("序號", typeof(int));
            ResultTable.Columns.Add("料號", typeof(string));
            ResultTable.Columns.Add("品名规格", typeof(string));
            ResultTable.Columns.Add("需求數量", typeof(decimal));
            ResultTable.Columns.Add("已發料數量", typeof(decimal));
            ResultTable.Columns.Add("需發料數量", typeof(decimal));
            ResultTable.Columns.Add("單位", typeof(string));
            ResultTable.Columns.Add("庫存", typeof(decimal));
            ResultTable.Columns.Add("在检", typeof(decimal));
            ResultTable.Columns.Add("tiptop料号", typeof(string));
            ResultTable.Columns.Add("tiptop库存", typeof(string));
            ResultTable.Columns.Add("tiptop在检", typeof(string));
            foreach (var item in Result)
            {
                ResultTable.Rows.Add(item.Company, item.JobNum, item.AssemblySeq, item.MtlSeq, item.PartNum, item.Description, item.RequiredQty, item.IssuedQty, item.NeedIssueQty, item.IUM, item.StockQty, item.InsQty, item.tiptopPart_c, item.STOCK_QTY, item.INS_QTY);
            }
            dataGridView1.DataSource = ResultTable;
            Util.VSHelper.DataGridViewAutoFit(dataGridView1);
        }

这篇关于C# 使用Linq联合SQL与Oracle查询的例子的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！