Programer達

查詢

// 查詢
var result = from q in mydt.AsEnumerable()
             select q;
// 防止NULL值
if (result.FirstOrDefault<DataRow>() != null)
{
    DataTable dt = result.CopyToDataTable<DataRow>();
    foreach (DataRow dr in dt.Rows)
    {
        Response.Write(
            string.Format("{0} - {1} - {2} - {3}",
            dr["Id"].ToString(),
            dr["Name"].ToString(),
            dr["Age"].ToString(),
            dr["Sex"].ToString()));
    }
}

WHERE

// 有條件查詢
var result = from q in mydt.AsEnumerable()
              where q.Field<int>("Age") > 30
              select new UserInfo()
              {
                  Id = q.Field<int>("Id"),
                  Name = q.Field<string>("Name"),
                  Age = q.Field<int>("Age"),
                  Sex = q.Field<int>("Sex")
              };
foreach (UserInfo info in result)
{
    Response.Write(string.Format("{0} - {1} - {2} - {3}",
                info.Id, info.Name, info.Age, info.Sex));
}

COUNT

var result = (from q in mydt.AsEnumerable()
              where q.Field<int>("Age") > 30
              select q).Count();
Response.Write(result.ToString());

TOP

// 查詢TOP5
var result = (from q in mydt.AsEnumerable()
             where q.Field<int>("Age") > 30
             select new UserInfo()
             {
                 Id = q.Field<int>("Id"),
                 Name = q.Field<string>("Name"),
                 Age = q.Field<int>("Age"),
                 Sex = q.Field<int>("Sex")
             }).Take(5);
foreach (UserInfo info in result)
{
    Response.Write(string.Format("{0} - {1} - {2} - {3}",
        info.Id, info.Name, info.Age, info.Sex));
}

ORDER BY

// Order bBy
var result = (from q in mydt.AsEnumerable()
              where q.Field<int>("Age") > 30
              orderby q.Field<int>("Age")
              select new UserInfo()
              {
                  Id = q.Field<int>("Id"),
                  Name = q.Field<string>("Name"),
                  Age = q.Field<int>("Age"),
                  Sex = q.Field<int>("Sex")
              });
foreach (UserInfo info in result)
{
    Response.Write(string.Format("{0} - {1} - {2} - {3}",
        info.Id, info.Name, info.Age, info.Sex));
}

JOIN

// JOIN
var result = (from q in mydt.AsEnumerable()
              from j in mydt2.AsEnumerable()
              where q.Field<int>("Id") == j.Field("Pid")
              select new UserJob()
              {
                  Name = q.Field<string>("Name"),
                  Job = j.Field<string>("Job")
              });
foreach (UserJob info in result)
{
    Response.Write(string.Format("{0} - {1}",
        info.Name, info.Job));
}

IN

// IN
string[] names = { "TTName2", "TTName5", "TTName8", "TTName15" };
var result = (from q in mydt.AsEnumerable()
              where names.Contains(q.Field("Name"))
              orderby q.Field<int>("Age")
              select new UserInfo()
              {
                  Id = q.Field<int>("Id"),
                  Name = q.Field<string>("Name"),
                  Age = q.Field<int>("Age"),
                  Sex = q.Field<int>("Sex")
              });
foreach (UserInfo info in result)
{
    Response.Write(string.Format("{0} - {1} - {2} - {3}",
        info.Id, info.Name, info.Age, info.Sex));
}

NOT IN

// NOT IN
string[] names = { "TTName1", "TTName2", "TTName3", "TTName4" };
var result = (from q in mydt.AsEnumerable()
              where !names.Contains(q.Field("Name"))
              orderby q.Field<int>("Age")
              select new UserInfo()
              {
                  Id = q.Field<int>("Id"),
                  Name = q.Field<string>("Name"),
                  Age = q.Field<int>("Age"),
                  Sex = q.Field<int>("Sex")
              });
foreach (UserInfo info in result)
{
    Response.Write(string.Format("{0} - {1} - {2} - {3}",
        info.Id, info.Name, info.Age, info.Sex));
}

資料來源：https://www.dotblogs.com.tw/joysdw12/archive/2012/...