C# DataTable.AsEnumerable方法代码示例

    /// <summary>
    /// ค้นหาข้อความใน DataTable
    /// </summary>
    /// <param name="dtSearch">DataTable ที่ต้องการค้นหา</param>
    /// <param name="strWord">คำที่ต้องการค้นหา</param>
    /// <param name="strField">Field ที่ต้องการค้นหา</param>
    /// <returns>True=พบ , False=ไม่พบ</returns>
    /// <remarks>2013-08-16</remarks>
    public bool Search(DataTable dtSearch, string strWord, string strField)
    {
        bool rtnBool = false;

        #region LINQ
        var varResult = (from word in dtSearch.AsEnumerable()
                         where word[strField].ToString() == strWord
                         select word);
        if (varResult != null && varResult.Count() > 0)
        {
            DataTable dtResult = new DataTable();
            dtResult = varResult.CopyToDataTable();
            rtnBool = true;
        }
        #endregion

        return rtnBool;
    }

 // returns a List<String> which has a row of CV information of the user who has the given ID
 public String[] getDetailsToForm(String id)
 {
     DataTable myTable = new DataTable();
     myTable = this.getCV(id);
     DataRow[] rowArray = myTable.AsEnumerable().Take(1).ToArray();
     object[] objectArray = rowArray[0].ItemArray;
     String[] stringArray = Array.ConvertAll(objectArray, (p => Convert.ToString(p)));
     return stringArray;
 }

    protected void Page_Load(object sender, EventArgs e) //获取全部的Family
    {
        if (!this.IsPostBack)
        {
            DataTable fy = new DataTable();
            fy = uph.GetUPHFamily();
            var q = from r in fy.AsEnumerable()
                    select new ListItem(r[0].ToString());

            string FY = fy.ToString();
            this.Select3.Items.AddRange(q.ToArray());
            bindbullTable(null,20);
        }
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {

            var dsgetMenu = CacheUtils.GetCacheFromFile("dsGetMenu" + Session["userid"]) as DataSet;
            if (dsgetMenu == null) return;
            DataView dvgetMenu = new DataView(dsgetMenu.Tables[0]);
            dtMenu = dsgetMenu.Tables[0];
            dvgetMenu.RowFilter = string.Format("[parentid] IS NULL");
            dtRoot = dvgetMenu.ToTable();

            StringBuilder sb = new StringBuilder();
            string urlWithSessionID = "~" + Response.ApplyAppPathModifier(Request.Url.PathAndQuery);
            GetMenuHTML(ref sb, dtRoot.AsEnumerable().ToList(), urlWithSessionID);

            HTMLMenu = sb.ToString();
        }
    }

    /// <summary>
    /// 安节点排序，并格式化点组的 IPC 如1点组，2点组
    /// </summary>
    /// <param name="dt"></param>
    /// <returns></returns>
    public static DataTable FormatTable(DataTable dt)
    {
        if (dt.Rows.Count > 0)
        {
            var x = from y in dt.AsEnumerable()
                    orderby y["NodeId"] ascending
                    select y;

            DataTable dtreuslt = x.CopyToDataTable();
            foreach (DataRow row in dtreuslt.Rows)
            {
                int tmplive = Convert.ToInt32(row["live"]);
                if (tmplive > 3)
                {
                    row["des"] = "<b>" + "".PadLeft(tmplive - 3, '.') + "</b>" + row["des"].ToString();
                }
            }
            return dtreuslt;
        }
        else
        {
            return dt;
        }
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        bindbullTable(null, 20);
        if (!IsPostBack)
        {
            
                DataTable fy = new DataTable();
                fy = uph.GetLine1();
                var q = from r in fy.AsEnumerable()
                        select new ListItem(r[0].ToString());

                string FY = fy.ToString();
                this.DL_PdLine.Items.AddRange(q.ToArray());
                bindbullTable(null, 20);
        }
        DataTable sn = new DataTable();
        sn = uph.GetAlarmALL();
        bindbullTable(sn, 5);

    }

    private bool VerificareFisierReceptie(DataTable rows, int dataPrimiriiIndex, int dataPrelevareIndex)
    {
        bool valid = true;

        foreach (var coduriDuplicate in rows.AsEnumerable().GroupBy(x => x[2]).Where(g => g.Count() > 1).Select(y => y.Key).ToList().Where(coduriDuplicate => coduriDuplicate.ToString() != ""))
        {
            loggerReceptie.Error("EROARE: Id zilnic: {0} se gaseste de mai multe ori: ", coduriDuplicate);
            valid = false;
        }

        DateTime outValue = new DateTime();
        int header = 0;

        foreach (DataRow row in rows.Rows)
        {
            header++;

            if (header > 2 && row[3].ToString().Trim() != "")
            {
                if (!DateTime.TryParseExact(row[dataPrelevareIndex].ToString().Trim(), "dd/MM/yyyy", new CultureInfo("fr-FR"), DateTimeStyles.None, out outValue))
                {
                    loggerReceptie.Error("EROARE: Data Prelevare: {0} este in format gresit: ", row[dataPrelevareIndex].ToString());
                    valid = false;
                }

                if (!DateTime.TryParseExact(row[dataPrimiriiIndex].ToString().Trim(), "dd/MM/yyyy", new CultureInfo("fr-FR"), DateTimeStyles.None, out outValue))
                {
                    loggerReceptie.Error("EROARE: Data Primirii: {0} este in format gresit: ", row[dataPrimiriiIndex].ToString());
                    valid = false;
                }
            }
        }

        return valid;
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection(WebConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString);
        SqlCommand cmd = new SqlCommand();
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "select * from Products";
        cmd.Connection = con;
        SqlDataAdapter adapter = new SqlDataAdapter();
        adapter.SelectCommand = cmd;
        DataSet dataSet = new DataSet();
        try
        {
            con.Open();
            adapter.Fill(dataSet, "Products");
            var result = from item in dataSet.Tables["Products"].AsEnumerable()
                         where item.Field<int>("ProductID") == 826388
                         select new { ProductName = item.Field<string>("ProductName"), UnitPrice = item.Field<decimal>("UnitPrice"), UnitsInStock = item.Field<int>("UnitsInStock") };
            GridView1.DataSource = result;
            GridView1.DataBind();
        }
        catch (Exception)
        {
            throw new ApplicationException("Data error");
        }
        finally
        {
            con.Close();
        }

        DataTable tab = new DataTable();
        tab.Columns.Add("FileName", typeof(string));
        tab.Columns.Add("FileSize", typeof(Int64));
        tab.Columns.Add("CreationTime", typeof(DateTime));

        DataRow row = null;

        DirectoryInfo dirInfo = new DirectoryInfo(@"C:\");
        foreach (var item in dirInfo.GetFiles())
        {
            row = tab.NewRow();
            row[0] = item.Name;
            row[1] = item.Length;
            row[2] = item.CreationTime;
            tab.Rows.Add(row);
        }

        var resultFiles = from item in tab.AsEnumerable()
                          where item.Field<Int64>("FileSize") > 10
                          select new { FileName = item.Field<string>("FileName"), FileSize = item.Field<Int64>("FileSize"), CreationTime = item.Field<DateTime>("CreationTime") };
        GridView2.DataSource = resultFiles;
        GridView2.DataBind();

        XDocument xmlFile = XDocument.Load(@"C:\test.xml");
        var resultXml = from item in xmlFile.Descendants("Product")
                        select new
                        {
                            ProductName = item.Element("ProductName").Value,
                            Cost = item.Element("Cost").Value
                        };
        DataTable table = new DataTable();
        table.Columns.Add("ProductName", typeof(string));
        table.Columns.Add("Cost", typeof(int));

        DataRow RowX = null;
        foreach (var item in resultXml)
        {
            RowX = table.NewRow();
            RowX[0] = item.ProductName;
            RowX[1] = item.Cost;
            table.Rows.Add(RowX);
        }

        var finalOut = from item in table.AsEnumerable()
                       where item.Field<int>("Cost") > 10
                       select new { ProductName = item.Field<string>("ProductName"), Cost = item.Field<int>("Cost") };

        GridView3.DataSource = finalOut;
        GridView3.DataBind();

        JoinQuery();
        StronglyTypedDataSet();
        DataViewDemo();
    }

//.........这里部分代码省略.........
            DataRow drTicketTemp = null;

            foreach (DataRow drTicket in dtTicket.Rows)
            {
                StrTickets = drTicket[0].ToString().Split(',');

                if (StrTickets.Length != 7)
                {
                    continue;
                }

                drTicketTemp = dtTicketTemp.NewRow();

                drTicketTemp["ID"] = StrTickets[0].ToString();
                drTicketTemp["AwardValue"] = StrTickets[3].ToString().Insert(StrTickets[3].ToString().Length - 2, ".");

                dtTicketTemp.Rows.Add(drTicketTemp);
                dtTicketTemp.AcceptChanges();
            }

            DataTable dtSchemes = MSSQL.Select("SELECT SchemeID, SchemesMultiple as Multiple, Identifiers FROM V_SchemesSendToCenter WHERE (IsuseID = " + dtIsuse.Rows[0]["ID"].ToString() + ")");

            if (dtSchemes == null)
            {
                new Log("ElectronTicket\\CTTCSD").Write("电子票开奖，第 " + IssueName + " 期，读取本地方案错误。");

                this.Response.End();

                return;
            }

            try
            {
                var query1 = from NewDtTickets in dtTicketTemp.AsEnumerable()
                             join NewdtScheme in dtSchemes.AsEnumerable()
                             on NewDtTickets.Field<string>("ID") equals NewdtScheme.Field<string>("Identifiers")
                             select new
                             {
                                 ID = NewdtScheme.Field<long>("SchemeID"),
                                 Multiple = NewdtScheme.Field<int>("Multiple"),
                                 Bonus = Shove._Convert.StrToDouble(NewDtTickets.Field<string>("AwardValue"), 0)
                             };

                var query2 = from NewDt in query1.AsQueryable()
                             group NewDt by new { NewDt.ID, NewDt.Multiple } into gg
                             select new
                             {
                                 ID = gg.Key.ID,
                                 Multiple = gg.Key.Multiple,
                                 Bonus = gg.Sum(NewDt => NewDt.Bonus)
                             };

                var query3 = from NewDt in query2.AsQueryable()
                             group NewDt by new { NewDt.ID, NewDt.Multiple } into t_dtSchemes
                             select new
                             {
                                 SchemeID = t_dtSchemes.Key.ID,
                                 Multiple = t_dtSchemes.Key.Multiple,
                                 Bonus = t_dtSchemes.Sum(NewDt => NewDt.Bonus)
                             };

                foreach (var Scheme in query3)
                {
                    BonusXML += "<Scheme SchemeID=\"" + Scheme.SchemeID.ToString() + "\" WinMoney=\"" + Scheme.Bonus.ToString() + "\" WinDescription=\"\" />";
                }
            }

    /// <summary>
    /// 多个DataTable 合成一个 DataTable
    /// </summary>
    /// <param name="dts"></param>
    /// <returns></returns>
    public static DataTable MargeTable(List<DataTable> dts)
    {
        DataTable dt = new DataTable();
        DataColumn col1 = new DataColumn("NodeId", typeof(int));
        DataColumn col2 = new DataColumn("NodeName");
        DataColumn col3 = new DataColumn("des");
        DataColumn col4 = new DataColumn("ipcs");
        DataColumn col5 = new DataColumn("live");
        DataColumn col6 = new DataColumn("showtype");
        DataColumn col7 = new DataColumn("IsParent");

        dt.Columns.Add(col1); dt.Columns.Add(col2); dt.Columns.Add(col3); dt.Columns.Add(col4); dt.Columns.Add(col5); dt.Columns.Add(col6); dt.Columns.Add(col7);
        foreach (var dt1 in dts)
        {
            for (int i = 0; i < dt1.Rows.Count; i++)
            {
                string nodeid = dt1.Rows[i]["NodeId"].ToString();
                int x = (from y in dt.AsEnumerable()
                         where y["NodeId"].ToString() == nodeid
                         select y).Count();
                if (x > 0) continue;
                DataRow row = dt.NewRow();
                row["NodeId"] = dt1.Rows[i]["NodeId"];
                row["NodeName"] = dt1.Rows[i]["NodeName"];
                row["des"] = dt1.Rows[i]["des"];
                row["ipcs"] = dt1.Rows[i]["ipcs"];
                row["live"] = dt1.Rows[i]["live"];
                row["IsParent"] = dt1.Rows[i]["IsParent"];
                row["showtype"] = dt1.Rows[i]["showtype"];
                dt.Rows.Add(row);
            }
        }

        return dt;
    }

    public String recoverPassword2(String SendtoAddress)
    {
        String query = String.Format("SELECT u.u_name, u.passd FROM UserAcc AS u, Candidate AS c WHERE c.email= '{0}' AND u.u_id=c.u_id", SendtoAddress);
        DataTable myTable = new DataTable();
        DBConnection myConnection = new DBConnection();
        myConnection.connect("LILY-PC", "projectdb1", "admin", "123");
        myTable = myConnection.executeReturningQuery(query);
        DataRow[] rowArray = myTable.AsEnumerable().Take(1).ToArray();
        object[] objectArray = rowArray[0].ItemArray;
        String[] stringArray = Array.ConvertAll(objectArray, (p => Convert.ToString(p)));
        String body = String.Format("Your login credentials for the project by DILIGENTIA are as follows, <br /><br /><b>Username:</b> {0}<br /><b>Password:</b> {1}", stringArray[0], stringArray[1]);
        //String body = stringArray[0] + stringArray[1];

        MailMessage msg = new MailMessage();
        System.Net.Mail.SmtpClient client = new System.Net.Mail.SmtpClient();
        try
        {
            msg.Subject = "Recover Password";
            msg.Body = body;
            msg.From = new MailAddress("[email protected]");
            msg.To.Add(SendtoAddress);
            msg.IsBodyHtml = true;
            client.Host = "smtp.gmail.com";
            System.Net.NetworkCredential basicauthenticationinfo = new System.Net.NetworkCredential("[email protected]", "diligentia");
            client.Port = int.Parse("587");
            client.EnableSsl = true;
            client.UseDefaultCredentials = false;
            client.Credentials = basicauthenticationinfo;
            client.DeliveryMethod = SmtpDeliveryMethod.Network;
            client.Send(msg);
            return "Sucessful Completion";
        }
        catch (Exception ex)
        {
            return (ex.Message);
        }
    }

    public String recoverPassword(String SendtoAddress)
    {
        String fromAddress = "lilylakshi[email protected]";
        String toAddress = SendtoAddress;
        const String password = "diligentia";
        const String subject = "Retrieving Password";
        String query = String.Format("SELECT u.u_name, u.passd FROM UserAcc AS u, Candidate AS c WHERE c.email= '{0}' AND u.u_id=c.u_id", SendtoAddress);
        DataTable myTable = new DataTable();
        DBConnection myConnection = new DBConnection();
        myConnection.connect("LILY-PC", "projectdb1", "admin", "123");
        myTable = myConnection.executeReturningQuery(query);
        DataRow[] rowArray = myTable.AsEnumerable().Take(1).ToArray();
        object[] objectArray = rowArray[0].ItemArray;
        String[] stringArray = Array.ConvertAll(objectArray, (p => Convert.ToString(p)));
        String body = stringArray[0] + stringArray[1];

        try
        {
            SmtpClient smtp = new SmtpClient
            {
                Host = "smtp.gmail.com",
                Port = 587,
                EnableSsl = true,
                DeliveryMethod = SmtpDeliveryMethod.Network,
                Credentials = new NetworkCredential(fromAddress, password),
                Timeout = 3000
            };
            MailMessage message = new MailMessage(fromAddress, toAddress, subject, body);
            smtp.Send(message);
        }
        catch (Exception ex)
        {
        }
        return stringArray[0];
    }

    public static ArrayList GetAutoCompleteData(string username)
    {
        ArrayList result = new ArrayList();
        if (HttpContext.Current.Session["downline"] != null)
        {
            DataTable dtSelect = new DataTable();
            dtSelect = (DataTable) HttpContext.Current.Session["downline"];
            List<string> list =  (from a in dtSelect.AsEnumerable().Where(a => a["NAME"].ToString().StartsWith(username)) select a.Field<string>("NAME")).ToList();
            //List<string> list = (from row in dtSelect.AsEnumerable().Where(row=>row["NAME"].ToString().StartsWith(username))).ToList(); //select row.Field<string>("NAME")).ToList();

            foreach (DataRow dr in dtSelect.Rows)
            {
                if (dr["NAME"].ToString().ToLower().StartsWith(username))
                {
                    result.Add(dr["NAME"]);
                }
            }
        }
        return result;
    }

    /// <summary>
    /// Top like operation
    /// </summary>
    private DataTable GetTopRows(DataTable dt, int top)
    {
        if (top == -1) return dt;

        List<DataRow> listRows = dt.AsEnumerable().Take(top).ToList();

        DataTable newDt = dt.Clone();

        foreach (DataRow row in listRows)
            newDt.Rows.Add(row.ItemArray);

        return newDt;
    }

    private void Load_Data_Prestation()
    {
        try
        {

            Dictionary<object, object> param = new Dictionary<object, object>();
            param.Add("ID_CLIENT", Session.GET_AND_DECRYPT(CYS_SESSION_TAGS.SESSION_LOGGED_S_IDCLIENT));
            dt = CYS_UTIL.getDatatable("PRESTATIONS_GROUPEget", param, SCYS_GEN);
            var result = from row in dt.AsEnumerable()
                         group row by row.Field<int>("ID_PRESTATION_THEME") into grp
                         select new
                         {
                             ID_PRESTATION_THEME = grp.Key
                         };
            param.Clear();
            param.Add("ID_PRESTATAIRE", Session.GET_AND_DECRYPT(CYS_SESSION_TAGS.SESSION_LOGGED_S_IDETABLISSEMENT));

            DataTable dtPrest = CYS_UTIL.getDatatable("PRESTATIONSget", param, SCYS_GEN);

            repeater_prestation_theme_laboratoire.DataSource = result;
            repeater_prestation_theme_laboratoire.DataBind();
            foreach (RepeaterItem i2 in repeater_prestation_theme_laboratoire.Items)
            {
                HtmlGenericControl prestation_them_div = i2.FindControl("prestation_them_div") as HtmlGenericControl;
                HtmlAnchor anchor_accordion1 = prestation_them_div.FindControl("anchor_accordion") as HtmlAnchor;
                HtmlGenericControl prestation_group_div1 = prestation_them_div.FindControl("prestation_groupe_div") as HtmlGenericControl;
                Label lab_id_prestation_theme = i2.FindControl("lab_id_prestation_theme") as Label;
                Label lab_libelle_prestation_theme = prestation_them_div.FindControl("lab_libelle_prestation_theme") as Label;
                anchor_accordion1.HRef = "#" + prestation_group_div1.ClientID;
                anchor_accordion1.Attributes["data-parent"] = prestation_them_div.ClientID;
                switch (Convert.ToInt32(lab_id_prestation_theme.Text))
                {
                    //TRADUCTION
                    case 4:
                    //TRANSPORT
                    case 1:
                        lab_libelle_prestation_theme.Text = dt.Select("ID_PRESTATION_THEME = " + lab_id_prestation_theme.Text)[0]["LIBELLE_THEME"].ToString(); 
                        param = new Dictionary<object, object>();
                        param.Add("ID_PRESTATION_THEME", lab_id_prestation_theme.Text);
                        param.Add("ID_ETABLISSEMENT", Session.GET_AND_DECRYPT(CYS_SESSION_TAGS.SESSION_LOGGED_S_IDETABLISSEMENT));
                        DataTable dtPrestationSource = CYS_UTIL.getDatatable("PRESTATIONSgetSource", param, SCYS_GEN);
                        Repeater repeater_prestation_source = prestation_group_div1.FindControl("repeater_prestation_source") as Repeater;
                        repeater_prestation_source.DataSource = dtPrestationSource;
                        repeater_prestation_source.DataBind();
                        break;
                    default:
                        Repeater repeater_prestation_laboratoire = prestation_group_div1.FindControl("repeater_prestation_laboratoire") as Repeater;
                        var resultPrest = from row in dt.AsEnumerable()
                                          where row.Field<int>("ID_PRESTATION_THEME") == Convert.ToInt32(lab_id_prestation_theme.Text)
                                          select new
                                          {
                                              ID_PRESTATION_GROUPE = row.Field<int>("ID_PRESTATION_GROUPE"),
                                              ID_PRESTATION_THEME = row.Field<int>("ID_PRESTATION_THEME"),
                                              LIBELLE = row.Field<string>("LIBELLE"),
                                              LIBELLE_THEME = row.Field<string>("LIBELLE_THEME"),
                                              MASQUE_PRIX = row.Field<string>("MASQUE_PRIX"),
                                              LIBELLE_PRIX = row.Field<string>("LIBELLE_PRIX")
                                          };
                        lab_libelle_prestation_theme.Text = resultPrest.First().LIBELLE_THEME;
                        repeater_prestation_laboratoire.DataSource = resultPrest;
                        repeater_prestation_laboratoire.DataBind();
                        foreach (RepeaterItem i in repeater_prestation_laboratoire.Items)
                        {
                            HtmlGenericControl prestation_groupe_div = i.FindControl("prestation_groupe_div") as HtmlGenericControl;
                            HtmlGenericControl prestation_detail_div = prestation_groupe_div.FindControl("prestation_detail_div") as HtmlGenericControl;
                            Label ID_PRESTATION_GROUPE = prestation_groupe_div.FindControl("ID_PRESTATION_GROUPE") as Label;
                            HtmlAnchor anchor_accordion = prestation_groupe_div.FindControl("anchor_accordion") as HtmlAnchor;
                            Repeater repeater_prestation_detail = prestation_detail_div.FindControl("repeater_prestation_detail") as Repeater;
                            //LIEN ACCORDION
                            anchor_accordion.HRef = "#" + prestation_detail_div.ClientID;
                            anchor_accordion.Attributes["data-parent"] = prestation_groupe_div.ClientID;
                            param = new Dictionary<object, object>();
                            param.Add("ID_PRESTATION_GROUPE", ID_PRESTATION_GROUPE.Text);
                            param.Add("ID_PRESTATAIRE", Session.GET_AND_DECRYPT(CYS_SESSION_TAGS.SESSION_LOGGED_S_IDETABLISSEMENT));
                            DataTable dtPrestationDetail = CYS_UTIL.getDatatable("PRESTATIONSget", param, SCYS_GEN);
                            repeater_prestation_detail.DataSource = dtPrestationDetail;
                            repeater_prestation_detail.DataBind();
                        }
                        break;
                }
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {

        }
    }