C# DataTable.AsEnumerable方法代碼示例

        public static DataTable CompareDataTables(DataTable firstDataTable, DataTable secondDataTable, bool isCaseSensitive = false, bool enforceOrder = false)
        {
            firstDataTable.CaseSensitive = isCaseSensitive;
            secondDataTable.CaseSensitive = isCaseSensitive;

            if (enforceOrder)
            {
                firstDataTable.Columns.Add(new DataColumn("CSTestSortOrder", typeof (int)));
                secondDataTable.Columns.Add(new DataColumn("CSTestSortOrder", typeof(int)));

                for (var i = 0; i < firstDataTable.Rows.Count; i++)
                {
                    firstDataTable.Rows[i]["CSTestSortOrder"] = i;
                }
                for (var i = 0; i < secondDataTable.Rows.Count; i++)
                {
                    secondDataTable.Rows[i]["CSTestSortOrder"] = i;
                }
            }

            var differences = firstDataTable.AsEnumerable().Except(secondDataTable.AsEnumerable(), DataRowComparer.Default)
                                .Union(secondDataTable.AsEnumerable().Except(firstDataTable.AsEnumerable(), DataRowComparer.Default));

            var resultDataTable = differences.Any() ? differences.CopyToDataTable() : new DataTable();
            resultDataTable.TableName = "ResultDataTable";

            return resultDataTable;
        }

        public void CreateDT()
        {
            DataTable table = new DataTable();

            table.Columns.Add(new DataColumn("Id", typeof(int)));
            table.Columns.Add(new DataColumn("Name"));
            table.Columns.Add(new DataColumn("Price", typeof(float)));

            string[] names = {"Dima","Vanyz","Kolyan","Anton","Maks","Oleg","Volodya","Misha","Sasha","Roma"};
            AddNewRow(table,names);

            Console.WriteLine("\n");
               var task1 = table.AsEnumerable().Where(x => (int)x["Id"] > 3).ToList();
               var task2 = table.AsEnumerable().Where(x => (int)x["Id"] > 4).Select(x => x["Price"]).ToArray();
               var  task3 = table.AsEnumerable().OrderBy(x => (float)x["Price"]).ToList();
               var task4 = table.AsEnumerable().Select(x => new { IdField = x["Id"], NameField = x["Name"], PriceField = x["Price"] }).ToList();
               var task5 = table.AsEnumerable().Where(x => (int)x["Id"] > 2 && (int)x["Id"] < 8).OrderByDescending(x => (float)x["Price"]).ToArray();

               foreach (DataRow row in table.Rows)
               {
               foreach (DataColumn column in table.Columns)
                   Console.WriteLine("{0}:{1} ", column.ColumnName, row[column]);

               }

            Console.ReadKey();
        }

        static void Test()
        {
            DataTable dt = new DataTable();
            dt.Columns.Add(new DataColumn("ID"));
            dt.Columns.Add(new DataColumn("NAME"));
            dt.Columns.Add(new DataColumn("VALUE"));

            DataRow dr1 = dt.NewRow();
            dr1["ID"] = "01";
            dr1["NAME"] = "張三";
            dr1["VALUE"] = 1024;
            dt.Rows.Add(dr1);

            DataRow dr2 = dt.NewRow();
            dr2["ID"] = "02";
            dr2["NAME"] = "李四";
            dr2["VALUE"] = 1014;
            dt.Rows.Add(dr2);

            DataRow dr3 = dt.NewRow();
            dr3["ID"] = "03";
            dr3["NAME"] = "王五";
            dr3["VALUE"] = 1025;
            dt.Rows.Add(dr3);

            DataRow dr4 = dt.NewRow();
            dr4["ID"] = "04";
            dr4["NAME"] = "趙六";
            dr4["VALUE"] = 1004;
            dt.Rows.Add(dr4);

            // 獲取DataTable中某一列的最大最小值
            var maxValue = dt.AsEnumerable().Max(p => { return p["VALUE"]; });
            var minValue = dt.AsEnumerable().Min(p => { return p["VALUE"]; });
        }

        static void Main(string[] args)
        {
            DataTable table = new DataTable();
            DataColumn a = new DataColumn("id", typeof(int));
            a.Unique = true;
            table.Columns.Add(a);
            table.Columns.Add("name", typeof(string));
            table.Columns.Add("price", typeof(float));

            Random r = new Random();
            for (int i = 1; i <= 10; i++)
            {
                table.Rows.Add(new object[]{i,i.ToString() + "name", r.Next(10000)/100f});
            }

            IEnumerable<DataRow> q1 = table.AsEnumerable()
                .Where(x => (int)x["id"] > 3);
            Console.WriteLine("q1:");
            foreach (var item in q1)
            {
                Console.WriteLine("id - {0},\t name - {1},\t price - {2}", item[0], item[1], item[2]);
            } Console.ReadKey();

            IEnumerable<float> q2 = table.AsEnumerable()
                .Where(x => (int)x["id"] > 4)
                .Select(x => (float)x["price"]);
            Console.WriteLine("q2:");
            foreach (var item in q2)
            {
                Console.WriteLine(item);
            } Console.ReadKey();

            IEnumerable<float> q3 = table.AsEnumerable()
                .Select(x => (float)x["price"])
                .OrderBy(x => x);
            Console.WriteLine("q3:");
            foreach (var item in q3)
            {
                Console.WriteLine(item);
            } Console.ReadKey();

            var q4 = table.AsEnumerable()
                .Select(x => new { IdField = x["id"], NameField = x["name"], PriceField = x["price"]});
            Console.WriteLine("q4:");
            foreach (var item in q4)
            {
                Console.WriteLine(item);
            } Console.ReadKey();

            IEnumerable<DataRow> q5 = table.AsEnumerable()
                .Where(x => (int)x["id"] > 2 && (int)x["id"] < 8)
                .OrderByDescending(x => (float)x["price"]);
            Console.WriteLine("q5:");
            foreach (var item in q5)
            {
                Console.WriteLine("id - {0},\t name - {1},\t price - {2}", item[0],item[1],item[2]);
            } Console.ReadKey();
        }

        private static void PerformBinning(TypeOfNumericProbabilityEnum probabilityEnum, DataTable result, List<int> numericColumns)
        {
            switch (probabilityEnum)
            {
                    case TypeOfNumericProbabilityEnum.Binning:

                    foreach (int numericColumn in numericColumns)
                    {
                        double[] values = result.AsEnumerable().Select(m => double.Parse(m[numericColumn].ToString())).ToArray();
                        double[] bins = IntervalCreation.MakeBins(values, 4);
                        string[] newValues = values.Select(m => IntervalCreation.InBin(m, bins)).ToArray();

                        for (int i = 0; i < result.Rows.Count; i++)
                        {
                            DataRow row = result.Rows[i];
                            row[numericColumn] = newValues[i];
                        }
                    }

                    break;

                    case TypeOfNumericProbabilityEnum.NormalDistribution:

                    string[] targetValues = result.AsEnumerable().Select(m => m[StaticStorage.TargetColum].ToString().Format()).ToArray();
                    string[] targetDistinctValues = targetValues.Distinct().ToArray();

                    List<NormalDistributionValueItem> valueItems = new List<NormalDistributionValueItem>();

                    foreach (int numericColumn in numericColumns)
                    {
                        foreach (string targetDistinctValue in targetDistinctValues)
                        {
                            NormalDistributionValueItem normalDistributionValue = new NormalDistributionValueItem();

                            List<double> values =
                                (from DataRow dataRow in result.Rows
                                    where dataRow[StaticStorage.TargetColum].Equals(targetDistinctValue)
                                    select double.Parse(dataRow[numericColumn].ToString()))
                                    .ToList();

                            normalDistributionValue.ColumnId = numericColumn;
                            normalDistributionValue.TargetValue = targetDistinctValue;
                            normalDistributionValue.Mean = values.Average();
                            normalDistributionValue.Variance =
                                values.Sum(m => Math.Pow(m - normalDistributionValue.Mean, 2))/values.Count - 1;

                            normalDistributionValue.Std = Math.Sqrt(normalDistributionValue.Variance);

                            valueItems.Add(normalDistributionValue);
                        }
                    }

                    StaticStorage.NormalDistributionValueItems = valueItems;
                    break;
                default:
                    throw new ArgumentOutOfRangeException(nameof(probabilityEnum), probabilityEnum, null);
            }
        }

        private static DataTable GetPivotTable(DataTable dataTable)
        {
            if (dataTable == null)
            {
                return null;
            }

            if (dataTable.Rows.Count.Equals(0))
            {
                return null;
            }

            const string ITEM_NAME = "ItemName";
            const string item_name = "item_name";
            const string office_code = "office_code";
            const string total_sales = "total_sales";

            using (DataTable table = new DataTable())
            {
                table.Columns.Add(ITEM_NAME);

                var items = dataTable.AsEnumerable().Select(s => s.Field<string>(item_name)).Distinct().ToList();
                var offices = dataTable.AsEnumerable().Select(s => s.Field<string>(office_code)).Distinct().ToList();

                foreach (var office in offices)
                {
                    table.Columns.Add(Conversion.TryCastString(office));
                }

                foreach (var item in items)
                {
                    DataRow row = table.NewRow();
                    string itemName = Conversion.TryCastString(item);
                    row[ITEM_NAME] = itemName;

                    foreach (DataColumn column in table.Columns)
                    {
                        string columnName = column.ColumnName;

                        if (columnName != ITEM_NAME)
                        {
                            decimal results = dataTable.AsEnumerable().
                                Where(r => r.Field<string>(office_code) == columnName
                                    && r.Field<string>(item_name) == itemName)
                           .Select(r => r.Field<decimal>(total_sales))
                           .Sum();

                            row[columnName] = results;
                        }
                    }

                    table.Rows.Add(row);
                }

                return table;
            }
        }

 private void FindMissingRows(DataTable TableA, DataTable TableB)
 {
     IEnumerable<int> idsNotInB = TableA.AsEnumerable().Select(r => r.Field<int>("id"))
         .Except(TableB.AsEnumerable().Select(r => r.Field<int>("id")));
     DataTable TableC = (from row in TableA.AsEnumerable()
         join id in idsNotInB
             on row.Field<int>("id") equals id
         select row).CopyToDataTable();
 }

        private void btnSearch_Click(object sender, EventArgs e)
        {
            try
            {
                strTestData = "";
                foreach (GridEXRow row in grdStatistics.GetCheckedRows())
                {
                    strTestData += Utility.sDbnull(row.Cells["TestData_Name"].Value) + ",";
                }

                dtResult = SPs.SpGetHivHbsiiHcv(dtpFromDate.Value.Date, dtpToDate.Value.Date.AddDays(1).AddMilliseconds(-1),
                                         Utility.Int16Dbnull(cboSex.SelectedValue),strTestData).GetDataSet().Tables[0];
                if (dtResult.Rows.Count <= 0)
                {
                    Utility.ShowMsg("Không tìm thấy dữ liệu. Chọn lại điều kiện tìm kiếm !");
                    dtResult.Clear();
                    dtResult.AcceptChanges();
                    return;
                }
                grdResult.DataSource = dtResult;

                string sTestName;
                for (int i = 0; i < dtStatistics.Rows.Count; i++)
                {
                    sTestName = Utility.sDbnull(dtStatistics.Rows[i]["TestData_Name"]);
                    dtStatistics.Rows[i]["Negative"] = (from dr in dtResult.AsEnumerable()
                                                        where(dr.Field<object>(sTestName+"_Diagnose") != null) &&
                                                            (dr.Field<string>(sTestName +"_Diagnose") == "Âm tính")
                                                        select dr).Count();
                    dtStatistics.Rows[i]["Positive"] = (from dr in dtResult.AsEnumerable()
                                                        where (dr.Field<object>(sTestName + "_Diagnose") != null) &&
                                                            (dr.Field<string>(sTestName + "_Diagnose") == "Dương tính")
                                                        select dr).Count();
                    dtStatistics.Rows[i]["Total"] = Utility.Int32Dbnull(dtStatistics.Rows[i]["Negative"]) + Utility.Int32Dbnull(dtStatistics.Rows[i]["Positive"]);
                }

                foreach (GridEXRow row in grdStatistics.GetRows())
                {
                    sTestName = Utility.sDbnull(row.Cells["TestData_Name"].Value);
                    grdResult.RootTable.Columns[sTestName].Visible = row.IsChecked;
                    grdResult.RootTable.Columns[sTestName + "_Diagnose"].Visible = row.IsChecked;
                }

                dtStatistics.AcceptChanges();
            }
            catch (Exception ex)
            {
                Utility.ShowMsg(ex.Message);
            }
        }

        /// <summary>
        /// Get a single Product from the Product table
        /// </summary>
        /// <param name="productId">A Product ID to find</param>
        /// <returns>A Product object</returns>
        public Product GetProduct(int productId)
        {
            DataTable dt = new DataTable();
              SqlDataAdapter da = null;
              SqlCommand cmd = null;

              cmd = new SqlCommand("SELECT * FROM Product WHERE ProductId = @ProductId",
            new SqlConnection(AppSettings.Instance.ConnectString));
              cmd.Parameters.Add(new SqlParameter("@ProductId", productId));

              da = new SqlDataAdapter(cmd);
              da.Fill(dt);

              Product entity =
            (from dr in dt.AsEnumerable()
             select new Product
             {
               ProductId = Convert.ToInt32(dr["ProductId"]),
               ProductName = dr["ProductName"].ToString(),
               IntroductionDate =
             DataConvert.ConvertTo<DateTime>(dr["IntroductionDate"],
               default(DateTime)),
               Cost = DataConvert.ConvertTo<decimal>(
             dr["Cost"], default(decimal)),
               Price = DataConvert.ConvertTo<decimal>(
             dr["Price"], default(decimal)),
               IsDiscontinued = DataConvert.ConvertTo<bool>(
             dr["IsDiscontinued"], default(bool))
             }).FirstOrDefault();

              return entity;
        }

        public List<string> GetUsersFavouriteFolders(string username)
        {
            string sql = SqlPatternUsersFavouriteFolders();
            DataTable res = new DataTable();
            using (SqlConnection conn = new SqlConnection(GetReportServerConnectionString()))
            {
                SqlCommand comm = new SqlCommand(sql, conn);
                comm.Parameters.Add("@UserName", SqlDbType.NVarChar, 520).Value = username;
                SqlDataAdapter adapt = new SqlDataAdapter(comm);
                adapt.Fill(res);
            }
            List<string> topFolders = res.AsEnumerable().Select(r => r.Field<string>("Folder")).ToList();
            // if less than 5 top up from global most used
            if (topFolders.Count() < 5)
            {
                int shortfall = 5 - topFolders.Count();
                List<string> globalFolders = GetGlobalFavouriteFolders();
                for (int i = 0; i < shortfall; i++)
                {
                    if (i < globalFolders.Count())
                        topFolders.Add(globalFolders[i]);
                }

            }
            return topFolders;
        }

        static void Main(string[] args)
        {
            DataSet ds = GetDataSet();
            DataTable full = new DataTable();
            foreach (DataTable table in ds.Tables) 
            {
                full.Merge(table);
            }

            var result = from tab in full.AsEnumerable()
                         group tab by tab["time"]
                             into groupDt
                             select new
                             {
                                 Group = groupDt.Key,
                                 Sum = groupDt.Sum(r => decimal.Parse(r["count"].ToString()))
                             };
            var max = result.OrderByDescending(x => x.Sum).First();
            DateTime maxTime = (DateTime)max.Group;
            CultureInfo ci = CultureInfo.InvariantCulture;

            Console.WriteLine("Максимальное количество посетителей "
                + max.Sum + " было в " + maxTime.ToString("HH:mm:ss", ci));

            Console.ReadKey();
        }

        public List<MysteryGame> GameList(string ConnectionStringValue, string databasename, string filter)
        {
            List<MysteryGame> returnValue = new List<MysteryGame>();
            SqlConnection sqlConn = new SqlConnection(ConnectionStringValue);
            if (sqlConn.State == ConnectionState.Closed)
                sqlConn.Open();
            sqlConn.ChangeDatabase(databasename);
            SqlCommand spHandler = new SqlCommand("spGames", sqlConn);
            spHandler.Parameters.AddWithValue("@step", 3);
            spHandler.Parameters.AddWithValue("@filter", filter);
            spHandler.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter daGameHandler = new SqlDataAdapter(spHandler);
            DataTable dtGameHandler = new DataTable();
            daGameHandler.Fill(dtGameHandler);

            var convertedList = (from rw in dtGameHandler.AsEnumerable()
                                 select new MysteryGame()
                                 {
                                     gameid = Convert.ToString(rw["gameid"]),
                                     download = Convert.ToString(rw["download"]),
                                     drawdate = Convert.ToString(rw["drawdate"]),
                                     submitter = Convert.ToString(rw["submitter"]),
                                     name = Convert.ToString(rw["name"]),
                                     platform = Convert.ToString(rw["platform"]),
                                     goal = Convert.ToString(rw["goal"]),
                                     specialrequirements = Convert.ToString(rw["specialrequirements"]),
                                     tournamentraceresult = Convert.ToString(rw["tournamentraceresult"]),
                                     notes = Convert.ToString(rw["notes"]),
                                     draws = Convert.ToString(rw["draws"]),
                                     pastebin = (Utilities.PasteBinPass(Convert.ToString(rw["pastebin"]))) ? Convert.ToString(rw["pastebin"]) : "pastebin not provided"
                                 }).ToList();

            return convertedList;
        }

 public SourceInfo(DataTable tessResults)
 {
     var results = (from rows in tessResults.AsEnumerable()
                    select new
                    {
                        SourceId = rows.Field<int?>("source_no"),
                        PromoCode = rows.Field<string>("promo_code"),
                        ModeOfSaleId = rows.Field<int?>("mos"),
                        OverridesRank = rows.Field<string>("override_rank_ind"),
                        SourceName = rows.Field<string>("source_name"),
                        AppealId = rows.Field<int?>("appeal_no"),
                        AppealDescription = rows.Field<string>("appeal_desc"),
                        Text1 = rows.Field<string>("text1"),
                        Text2 = rows.Field<string>("text2"),
                        Text3 = rows.Field<string>("text3"),
                        Text4 = rows.Field<string>("text4"),
                        Text5 = rows.Field<string>("text5"),
                        Text6 = rows.Field<string>("text6"),
                        Date = rows.Field<string>("promote_dt")
                    }).Single();
     SourceId = results.SourceId;
     PromoCode = results.PromoCode;
     ModeOfSaleId = (short?)results.ModeOfSaleId;
     OverridesRank = ToBool(results.OverridesRank);
     SourceName = results.SourceName;
     AppealId = results.AppealId;
     AppealDescription = results.AppealDescription;
     Text1 = results.Text1;
     Text2 = results.Text2;
     Text3 = results.Text3;
     Text4 = results.Text4;
     Text5 = results.Text5;
     Text6 = results.Text6;
     Date = ToDateTime(results.Date);
 }

        private void btnLoad_Click(object sender, EventArgs e)
        {
            //MessageBox.Show(Util.TaskInfo.TaskSetting.InputFilePath);


            ExlAddin.Read2Dt dt = new ExlAddin.Read2Dt();
            dt.FilePrepare();
            dtForm = dt.Exl2Dt();
            dataGridView1.DataSource = dtForm;



            //MessageBox.Show(test.symType);
            //Console.WriteLine(dataGridView1.Left.ToString());


            // filter SymbolType to Listbox to select
            var query = from c in dtForm.AsEnumerable()
                        group c by c.Field<string>("SymbolType");

            foreach (var item in query)
            {
                if (item.Key == null)
                    continue;
                //Console.WriteLine(item.Key);
                listBoxSymbol.Items.Add(item.Key);
            }
        }

 private static IEnumerable<DataRow> GetRows(DataTable funcTable, int parentId)
 {
     var result = from row in funcTable.AsEnumerable()
                  where row.Field<int>("FN_PARENT_ID") == parentId
                  select row;
     return result;
 }