本文详细介绍如何使用LINQ进行复杂的数据查询操作，包括模糊查询、时间段查询、数据分页和排序，以及如何将查询结果转化为DataTable进行进一步处理。同时，文章还提供了将List集合转换为DataTable的方法，以及在SQL查询中比较日期部分的技巧。
// 模糊查询、时间段查询、查询后数据分页和排序
// 模糊查询、时间段查询、查询后数据分页和排序
// 1. 查询和条件
JKEntities1 db = new JKEntities1();
var allpages = from c in db.JK_MOM_MileStoneReport
where
(
(c.OSBID.Contains(TextBox1.Text) || string.IsNullOrEmpty(TextBox1.Text.Trim())) &&
(c.ORDNO.Contains(TextBox2.Text) || string.IsNullOrEmpty(TextBox2.Text.Trim())) &&
(c.ZVIN.Contains(TextBox3.Text) || string.IsNullOrEmpty(TextBox3.Text.Trim())) &&
(c.ZTP.Contains(TextBox4.Text) || string.IsNullOrEmpty(TextBox4.Text.Trim())) &&
(((c.ZPASS_S >= DatePicker1.SelectedDate.Value)) || string.IsNullOrEmpty(DatePicker1.Text.Trim())) &&
(((c.ZPASS_S <= DatePicker2.SelectedDate.Value)) || string.IsNullOrEmpty(DatePicker2.Text.Trim()))
)
select new { OSBID = c.OSBID, ORDNO = c.ORDNO, WERKS = c.WERKS, ZVIN = c.ZVIN, ZTP = c.ZTP, ZPASS_S = c.ZPASS_S.ToString() +" "+c.ZPASS_T.ToString() };
// 2.总记录数RecordCount
Grid1.RecordCount = allpages.Count();
// 3.排序和分页数据
var onepage = allpages.OrderBy(Grid1.SortField + " " + Grid1.SortDirection).Skip(Grid1.PageIndex * Grid1.PageSize).Take(Grid1.PageSize);
//---------------------------------------
DataTable t = mytools.ListToDataTable(onepage.ToList());
// 4. 绑定到Grid
Grid1.DataSource = t;
Grid1.DataBind();
//全局变量，下载xls用
dt = mytools.ListToDataTable(allpages.ToList());
//list转为DateTable
//list转为DateTable
public static DataTable ListToDataTable(IList list)
{
DataTable result = new DataTable();
if (list.Count > 0)
{
PropertyInfo[] propertys = list[0].GetType().GetProperties();
foreach (PropertyInfo pi in propertys)
{
//获取类型
Type colType = pi.PropertyType;
//当类型为Nullable<>时
if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>)))
{
colType = colType.GetGenericArguments()[0];
}
result.Columns.Add(pi.Name, colType);
}
for (int i = 0; i < list.Count; i++)
{
ArrayList tempList = new ArrayList();
foreach (PropertyInfo pi in propertys)
{
object obj = pi.GetValue(list[i], null);
tempList.Add(obj);
}
object[] array = tempList.ToArray();
result.LoadDataRow(array, true);
}
}
return result;
}
//数据库是日期时间类型，条件也是日期时间类型，只比较2个日期类型中日期部分
//数据库是日期时间类型，条件也是日期时间类型，只比较2个日期类型中日期部分
Entities1 db_osb = new Entities1();
var q_dms = from c in db_osb.OSB_MON_TRANSACTION_T
where
(
(c.INTERFACE_NAME.Contains("PS_MOM_MileStoneReport")) &&
(c.TARGET.Contains("SAP")) &&
(c.TYPE.Contains("BS")) &&
(((DbFunctions.TruncateTime(c.CREATE_DATE.Value) >= d7.Value.Date)) || string.IsNullOrEmpty(d7.Value.Date.ToString().Trim())) &&
(((DbFunctions.TruncateTime(c.CREATE_DATE.Value) <= d8.Value.Date)) || string.IsNullOrEmpty(d8.Value.Date.ToString().Trim()))
)
orderby c.CREATE_DATE descending
select new { ID = c.ID, IS_READ = c.IS_READ, INTERFACE_NAME = c.INTERFACE_NAME, SOURCE = c.SOURCE, TARGET = c.TARGET, CREATE_DATE = c.CREATE_DATE };
//数据库里是日期类型，条件是字符串日期，按日期条件删除数据库的数据
//数据库里是日期类型，条件是字符串日期，按日期条件删除数据库的数据
PDAEntities db_sql = new PDAEntities();
string tmp = toolStripComboBox2.SelectedItem.ToString().Trim();
DateTime DT = Convert.ToDateTime(tmp);
var one = (from c in db_sql.JK_ESB_ONEDAY
where c.mdate.Value.Equals(DT)
select c).FirstOrDefault();
if (one != null)
{
db_sql.JK_ESB_ONEDAY.Remove(one);
db_sql.SaveChanges();
listBox4.Items.Add("删除了:" + tmp);
}
//执行sql语句
PDAEntities db_sql = new PDAEntities();
int num = db_sql.Database.ExecuteSqlCommand("delete from JK_ESB_ONEDAY");
MessageBox.Show("删除了"+num.ToString()+"条");
//取日期最大的一条
var onebig = (from u in db_sql.JK_ESB_ONEDAY orderby u.mdate descending select u).FirstOrDefault();
//多表查询
var some = from w in po.db.RE_USER_ROLE.AsEnumerable()
from t in po.db.RE_ROLE_TCODE.AsEnumerable()
from p in po.db.BA_TCODE.AsEnumerable()
where
(
w.ZUSER.Equals(username) &&
w.ROLE.Equals(t.ROLE) &&
t.TCODE.Equals(p.TCODE)
)
select new
{
w.ZUSER,
w.ROLE,
t.TCODE,
p.TCODE_NAME,
p.PAGE,
p.CATALOG
};
//只取一条记录
var someone = (from t in db.BA_USER
where
( t.ZUSER.Equals(user) )
select t).FirstOrDefault();
//最简单的参数查询
Owner1 db = new Owner1();
var someone = from t in db.BA_USER
where
(
t.ZUSER.Equals(tbxUserName.Text) &&
t.PASSWORD.Equals(tbxPassword.Text)
)
select t;
//%%通配符查询
var q = from c in db.MM_T001L
where
(
// (SqlMethods.Like( c.WERKS.ToString(),TextBox1.Text.Trim() ) || string.IsNullOrEmpty(TextBox1.Text.Trim()) ) &&
// (SqlMethods.Like(c.LGORT.ToString(), TextBox2.Text.Trim() ) || string.IsNullOrEmpty(TextBox2.Text.Trim()) )&&
// (SqlMethods.Like(c.LGOBE.ToString(), TextBox3.Text.Trim() ) || string.IsNullOrEmpty(TextBox3.Text.Trim()))
( c.WERKS.Contains(TextBox1.Text.Trim()) || string.IsNullOrEmpty(TextBox1.Text.Trim()) ) &&
(c.LGORT.Contains(TextBox2.Text.Trim()) || string.IsNullOrEmpty(TextBox2.Text.Trim())) &&
(c.LGOBE.Contains(TextBox3.Text.Trim()) || string.IsNullOrEmpty(TextBox3.Text.Trim()))
)
select c;
//常用变量转换
public static int i(object x1)
{
return Convert.ToInt32(x1);
}
public static string s(object x1)
{
return Convert.ToString(x1);
}
public static Decimal d(object x1)
{
return Convert.ToDecimal(x1);
}
public static DateTime t(object x1)
{
if (s(x1).Equals("0000-00-00")) { x1 = null; }
return Convert.ToDateTime(x1);
}
public static DateTime t2(object d, object t)
{
if (s(d).Equals("0000-00-00")) { d = null; }
if (s(t).Equals("00:00:00")) { t = null; }
return Convert.ToDateTime(d.ToString() +" "+ t.ToString());
}
//在select中比较日期
int n = (from t in db_sql.PO_MSG_LOG where
(
SqlFunctions.DateDiff("day", t.LOG_TIME, oneday) == 0
)
select t).Count();
扫一扫
940
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
