/// <summary>
/// 主要查詢方法
/// </summary>
/// <param name="page">頁數（預設為 1）</param>
/// <param name="ExportMode">匯出模式。（預設為 false）若為真，則取消分頁。</param>
private void Query(long page = 1, bool ExportMode = false)
{
sql.Append(String.Format("SELECT * FROM {0} WHERE 1=1", TableName));
#region 將搜尋控制項的值轉換為 SQL Statement
BackendSearchControl.ConvertControlToSQL(sql, SearchItems, plSearch, this.ViewState);
#endregion
sql.OrderBy("sID DESC");
if (!ExportMode)
{
var data = db.Page <DataModel_a12SupauCheckin>(page, PageSize, sql);
patwGridView1.DataSource = data.Items;
patwGridView1.DataBind();
AspNetPager1.PageSize = (int)data.ItemsPerPage;
AspNetPager1.RecordCount = (int)data.TotalItems;
lbTotal.Text = "依據條件，目前共有 " + data.TotalItems.ToString() + " 筆";
}
else
{
var data = db.Query <DataModel_a12SupauCheckin>(sql);
patwGridView1.DataSource = data;
patwGridView1.DataBind();
AspNetPager1.Visible = false;
lbTotal.Text = "依據條件，目前共有 " + data.Count() + " 筆";
}
}
protected override void PagedEntities(int entityCount)
{
using (var database = new Database("SQLiteTest"))
{
using (var transaction = database.GetTransaction())
{
var results = database.Page<Entity>(1, entityCount, new Sql("SELECT * FROM Entity"));
transaction.Complete();
}
}
}
/// <summary>
/// 执行耗时的语句
/// </summary>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <param name="connName"></param>
/// <param name="totalElapsedTime"> 完成此计划的执行所占用的总时间 </param>
/// <param name="beginDt"> 最后一次执行时间 </param>
/// <param name="endDt"> 最后一次执行时间 </param>
/// <returns></returns>
public static PetaPoco.Page<dynamic> CastLongTimeSqlGetList(int pageSize, int pageIndex, string connName, DateTime beginDt, DateTime endDt)
{
var db = new PetaPoco.Database(connName);
StringBuilder sb = new StringBuilder();
sb.AppendFormat(@" select * from ( SELECT top 10000
total_worker_time/execution_count/1000000 AS 'AvgCpu',
execution_count as '执行次数',
total_worker_time/1000 as '总共占用CPU',
creation_time as '创建时间',
last_execution_time as '最后执行时间',
min_worker_time as '最低每次占用CPU',
max_worker_time as '最高每次占用cpu',
total_physical_reads as '总共io物理读取次数',
total_logical_reads as '总共逻辑读取次数',
total_logical_writes as '总共逻辑写次数',
total_elapsed_time/1000000 as '完成此计划的执行所占用的总时间秒',
(SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset end -statement_start_offset)/2)
FROM sys.dm_exec_sql_text(sql_handle)
) AS 'SQL内容'
FROM sys.dm_exec_query_stats");
if (beginDt.Year > 2000)
{
sb.AppendFormat(" WHERE last_execution_time>='{0}' ", beginDt);
}
if (endDt.Year > 2000)
{
sb.AppendFormat(" WHERE last_execution_time<='{0}' ", endDt);
}
sb.AppendFormat(@" ORDER BY AvgCpu DESC) a");
PetaPoco.Page<dynamic> result = new Page<dynamic>();
try
{
result = db.Page<dynamic>(pageIndex, pageSize, sb.ToString());
}
catch (Exception ex)
{
var mm = ex.Message;
}
return result;
}
public IEnumerable<MixERP.Net.Entities.Core.Account> GetPagedResult(long page=1)
{
ApiAccessPolicy policy = new ApiAccessPolicy(typeof(MixERP.Net.Entities.Core.Account), "GET");
policy.Authorize();
if (!policy.IsAuthorized)
{
throw new HttpResponseException(HttpStatusCode.Forbidden);
}
try
{
using (Database db = new Database(Factory.GetConnectionString(), "Npgsql"))
{
return db.Page<MixERP.Net.Entities.Core.Account>(page, 10, "SELECT * FROM core.accounts ORDER BY account_id").Items;
}
}
catch
{
throw new HttpResponseException(HttpStatusCode.InternalServerError);
}
}
private StringBuilder Aflist(int pageindex)
{
StringBuilder list = new StringBuilder();
string str = string.Empty;
var objs = new List<object>();
List<T_ERP_Aftersales> sales = new List<T_ERP_Aftersales>();
using (var db = new Database(SQLCONN.Conn))
{
var page = db.Page<T_ERP_Aftersales>(pageindex, PageSize, "select * from T_ERP_Aftersales where IsDel = 1 Order by Createdate desc", objs.ToArray());
total.Value = (((int)page.TotalItems % PageSize == 0) ? ((int)page.TotalItems / PageSize) : ((int)page.TotalItems / PageSize + 1)).ToString();//总页数
totalRecords.Value = ((int)page.TotalItems).ToString();
sales = page.Items;
}
if (sales == null || sales.Count < 1) return list;
foreach (var st in sales)
{
str += " <div class='comment_box'><div class='info'><span class='c_info fr'>" + st.CreateDate.ToString("yyyy-MM-dd") + "</span><div><a title='查看详细' target='_blank' href='SalesDetal.aspx?SGD=" + st.Guid + "'><span class='mr40'>姓名：" + st.Applicanter + "</span> 运单号：" + st.CheckNo + " 状态：" + (st.IsRefuse == false ? "已处理" : "已驳回") + "</a></div></div>";
str += "<div class='c_cont'>" + st.Problemdescription + "</div><div class='reply_box'>";
str += "<div class='reply' style='width:767px;'>" +"回复："+ st.Solution + "</div><div class='date'>" + st.ProcessingTime.Value.ToString("yyyy-MM-dd") + "</div></div></div>";
}
list.Append(str);
return list;
}
/// <summary>
///sql任务
/// </summary>
/// <param name="topNum"> 100 </param>
/// <returns></returns>
public static PetaPoco.Page<dynamic> GetCpuPressure(string connName)
{
var db = new PetaPoco.Database(connName);
StringBuilder sb = new StringBuilder();
sb.AppendFormat("SELECT * FROM sys.dm_os_schedulers WHERE scheduler_id < 255");
PetaPoco.Page<dynamic> result = new Page<dynamic>();
try
{
result = db.Page<dynamic>(1, 100, sb.ToString());
}
catch (Exception ex)
{
var mm = ex.Message;
}
return result;
}
/// <summary> 查看缓存中重用次数少，占用内存大的查询语句（当前缓存中未释放的）--全局 bucketid
//int
//存储桶 ID。该值指示从 0 到 1 的范围（目录大小）。目录大小是哈希表的大小。
//refcounts
//int
//引用该缓存对象的其他缓存对象数。计数 1 为基数。
//usecounts
//int
//自开始以来使用该缓存对象的次数。
//pagesused
//int
//缓存对象消耗的内存页数。
//cacheobjtype
//nvarchar(34)
//缓存中的对象类型。以下类型之一：
//编译计划
//可执行计划
//分析树
//扩展存储过程
//memory_object_address
//varbinary(8)
//计划的内存地址。
//objtype
//nvarchar(16)
//对象的类型。可以是下列类型之一：
//Proc--存储过程
//Prepared--预定义语句
//Adhoc--即席查询
//ReplProc--复制筛选过程
//Trigger--触发器
//View--视图
//Default--默认值
//UsrTab--用户表
//SysTab--系统表
//CheckCHECK--约束 Rule规则
//plan_handle
//varbinary(64)
//内存中计划的标识符。该标识符是瞬态的
//仅当计划保留在缓存中时，它才保持不变。
//该值可以与 sys.dm_exec_query_plan 动态管理函数以及sys.dm_exec_plan_attributes 动态管理函数一同使用
/// </summary> <param name="pageIndex"></param> <param name="pageSize"></param> <param
/// name="orderby"> 排序 , usecounts或者 size </param> <returns></returns>
public PetaPoco.Page<object> GetSqlOfCacheReusedFewAndMemeryMany(int pageIndex, int pageSize, string connName, string orderField = "", string order = "asc")
{
var db = new PetaPoco.Database(connName);
StringBuilder sb = new StringBuilder();
sb.AppendFormat(@"SELECT usecounts,
case cacheobjtype
when 'Compiled Plan' then '编译计划'
when 'Parse Tree' then '解析树'
when 'Extended Proc' then '扩展存储过程'
when 'CLR Compiled Func' then 'clr编译函数'
when 'CLR Compiled Proc' then 'clr编译过程'
ELSE '未知' END
cacheobjtype, refcounts,bucketid,
case objtype
when 'proc' then '存储过程'
when 'Prepared' then '预定义语句'
when 'Adhoc' then '即席查询'
when 'ReplProc' then '复制筛选过程'
when 'Trigger' then '触发器'
when 'View' then '视图'
when 'Default' then '默认值'
when 'UsrTab' then '用户表'
when 'SysTab' then '系统表'
when 'Check' then '约束'
when 'Rule' then '规则'
ELSE '未知' END
objtype, p.size_in_bytes,[sql].[text] FROM sys.dm_exec_cached_plans p OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql ORDER BY ");
order = order == "asc" ? "asc" : "desc";
switch (orderField)
{
case "usecounts":
sb.AppendFormat(" usecounts {0} ,p.size_in_bytes desc ", order);
break;
case "size":
sb.AppendFormat(" p.size_in_bytes {0}, usecounts desc ", order);
break;
default:
sb.AppendFormat(" usecounts {0},p.size_in_bytes desc ", order);
break;
}
PetaPoco.Page<dynamic> result = new Page<dynamic>();
try
{
result = db.Page<dynamic>(pageIndex, pageSize, sb.ToString());
}
catch (Exception ex)
{
var mm = ex.Message;
}
return result;
}
/// <summary>
/// 查看当前的数据库用户连接有多少
/// </summary>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <param name="connName"></param>
/// <param name="beginDt"></param>
/// <param name="endDt"></param>
/// <returns></returns>
public static PetaPoco.Page<dynamic> CurrentUserConnGetList(int pageSize, int pageIndex, string connName,
DateTime beginDt, DateTime endDt)
{
var db = new PetaPoco.Database(connName);
StringBuilder sb = new StringBuilder();
sb.AppendFormat(" SELECT * FROM sys.[sysprocesses] WHERE [spid]>50 ");
if (beginDt.Year > 2000)
{
sb.AppendFormat(" and login_time>='{0}' ", beginDt);
}
if (endDt.Year > 2000)
{
sb.AppendFormat(" and login_time<='{0}' ", endDt);
}
PetaPoco.Page<dynamic> result = new Page<dynamic>();
try
{
result = db.Page<dynamic>(pageIndex, pageSize, sb.ToString());
}
catch (Exception ex)
{
var mm = ex.Message;
}
return result;
}
/// <summary>
/// 表空间大小查询
/// </summary>
/// <returns></returns>
public static PetaPoco.Page<dynamic> GetTableSpaceSize(string connName)
{
var db = new PetaPoco.Database(connName);
StringBuilder sb = new StringBuilder();
sb.AppendFormat(" create table #tb(表名 sysname,记录数 int,保留空间 varchar(100),使用空间 varchar(100),索引使用空间 varchar(100),未用空间 varchar(100)) insert into #tb exec sp_MSForEachTable 'EXEC sp_spaceused ''?''' select * from #tb order by 记录数 desc ;DROP TABLE #tb; ");
PetaPoco.Page<dynamic> result = new Page<dynamic>();
try
{
result = db.Page<dynamic>(1, 500, sb.ToString());
}
catch (Exception ex)
{
var mm = ex.Message;
}
return result;
}
/// <summary>
/// 一个信号等待时间过多对资源的等待时间那么你的CPU是目前的一个瓶颈
/// </summary>
/// <returns></returns>
public static PetaPoco.Page<dynamic> GetWaitTimeForResource(string connName)
{
var db = new PetaPoco.Database(connName);
StringBuilder sb = new StringBuilder();
sb.AppendFormat("SELECT SUM(signal_wait_time_ms) AS total_signal_wait_time_ms总信号等待时间 , SUM(wait_time_ms - signal_wait_time_ms) AS resource_wait_time_ms资源的等待时间,");
sb.AppendFormat("SUM(signal_wait_time_ms) * 1.0 / SUM (wait_time_ms) * 100 AS [signal_wait_percent信号等待％],");
sb.AppendFormat("SUM(wait_time_ms - signal_wait_time_ms) * 1.0 / SUM (wait_time_ms) * 100 AS [resource_wait_percent资源等待％]");
sb.AppendFormat("FROM sys.dm_os_wait_stats");
PetaPoco.Page<dynamic> result = new Page<dynamic>();
try
{
result = db.Page<dynamic>(1, 100, sb.ToString());
}
catch (Exception ex)
{
var mm = ex.Message;
}
return result;
}
public static PetaPoco.Page<dynamic> GetSessionCount(string connName)
{
var db = new PetaPoco.Database(connName);
StringBuilder sb = new StringBuilder();
sb.AppendFormat(" exec sp_who 'active' ;print @@rowcount ");
PetaPoco.Page<dynamic> result = new Page<dynamic>();
try
{
result = db.Page<dynamic>(1, 100, sb.ToString());
}
catch (Exception ex)
{
var mm = ex.Message;
}
return result;
}
/// <summary>
/// 查看当前占用 cpu 资源最高的会话和其中执行的语句(及时CPU)
/// </summary>
/// <param name="topNum"> 100 </param>
/// <returns></returns>
public static PetaPoco.Page<dynamic> GetSessionOfCpuHighAndSqlText(string connName)
{
var db = new PetaPoco.Database(connName);
StringBuilder sb = new StringBuilder();
sb.AppendFormat(" select * from ( select spid,cmd,cpu,physical_io,memusage,(select top 1 [text] from ::fn_get_sql(sql_handle)) sql_text from master.dbo.sysprocesses )a where [sql_text] is not null order by cpu desc,physical_io desc ");
PetaPoco.Page<dynamic> result = new Page<dynamic>();
try
{
result = db.Page<dynamic>(1, 100, sb.ToString());
}
catch (Exception ex)
{
var mm = ex.Message;
}
return result;
}
private StringBuilder Aflist(int pageindex)
{
StringBuilder list = new StringBuilder();
string str = string.Empty;
var objs = new List<object>();
List<T_ERP_Message> message = new List<T_ERP_Message>();
using (var db = new Database(SQLCONN.Conn))
{
var page = db.Page<T_ERP_Message>(pageindex, PageSize, "select * from T_ERP_Message where IsDel = 1 Order by Createdate desc", objs.ToArray());
total.Value = (((int)page.TotalItems % PageSize == 0) ? ((int)page.TotalItems / PageSize) : ((int)page.TotalItems / PageSize + 1)).ToString();//总页数
totalRecords.Value = ((int)page.TotalItems).ToString();
message = page.Items;
}
if (message == null || message.Count < 1) return list;
foreach (var st in message)
{
str += " <div class='comment_box'><div class='info'><span class='c_info fr'>" + st.CreateDate.ToString("yyyy-MM-dd") + "</span><div><a><span class='mr40'>姓名：" + st.Applicanter + "</span> 标题：" + st.Title + " 状态：" + (st.IsCancel == false ? "已处理" : "已驳回") + "</a></div></div>";
str += "<div class='c_cont'>" + st.Description + "</div><div class='reply_box'>";
str += "<div class='reply' style='width:767px;'>" + "评论：" + st.Comment + "</div><div class='date'>" + st.CommentTime.Value.ToString("yyyy-MM-dd") + "</div></div></div>";
}
list.Append(str);
return list;
}
private static void TestPaging(Database db, int herd, DateTime? date)
{
Console.WriteLine("sivutettu haku dynaamisella sql:llä...");
Console.WriteLine();
var sql2 = PetaPoco.Sql.Builder
.Append("SELECT * FROM idkarbovine with(nolock)")
.Append("WHERE idkar=@0", herd)
.Append("AND ValidFromDate<=@0", date.Value)
.Append("AND ValidDueDate>=@0", date.Value)
.Append("ORDER BY EarNr");
var page1 = db.Page<Bovine>(1, 10, sql2);
Console.WriteLine("Total items:" + page1.TotalItems);
Console.WriteLine("Total pages:" + page1.TotalPages);
Console.WriteLine("Current page:" + page1.CurrentPage);
foreach (var a in page1.Items)
{
Console.WriteLine("{0} - {1}, {2}", a.BovineId, a.EarNr, a.NameShort);
}
Console.WriteLine();
var page2 = db.Page<Bovine>(2, 10, sql2);
Console.WriteLine("Total items:" + page2.TotalItems);
Console.WriteLine("Total pages:" + page2.TotalPages);
Console.WriteLine("Current page:" + page2.CurrentPage);
foreach (var a in page2.Items)
{
Console.WriteLine("{0} - {1}, {2}", a.BovineId, a.EarNr, a.NameShort);
}
Console.WriteLine();
var page3 = db.Page<Bovine>(3, 10, sql2);
Console.WriteLine("Total items:" + page3.TotalItems);
Console.WriteLine("Total pages:" + page3.TotalPages);
Console.WriteLine("Current page:" + page3.CurrentPage);
foreach (var a in page3.Items)
{
Console.WriteLine("{0} - {1}, {2}", a.BovineId, a.EarNr, a.NameShort);
}
Console.WriteLine();
var page4 = db.Page<Bovine>(4, 10, sql2);
Console.WriteLine("Total items:" + page4.TotalItems);
Console.WriteLine("Total pages:" + page4.TotalPages);
Console.WriteLine("Current page:" + page4.CurrentPage);
foreach (var a in page4.Items)
{
Console.WriteLine("{0} - {1}, {2}", a.BovineId, a.EarNr, a.NameShort);
}
}
private void Refreash(int page, int rows)
{
string[] list = null;
var objs = new List<object>();
string whr = " where IsSendGoods=0";
string prefixWhr = string.Empty;
var idx = 0;
if (Session["queryStr"] != null)
{
string queryStr = Session["queryStr"].ToString();
list = queryStr.Split(',');
}
if (Session["IsManager"] != null && UserName != "")
{
if (Session["IsManager"].ToString() != "True" && UserName != "admin")
{
whr += " and SuppName in (select UserName from T_ERP_MapUser where SuppName = @" + (idx++).ToString() + ")";
objs.Add(UserName);
prefixWhr = "select * from V_ERP_SuppXBatch ";
}
else
{
prefixWhr = "select * from V_ERP_SuppXBatch ";
}
if (list != null && list.Count() > 0)
{
if (!string.IsNullOrEmpty(list[0]))
{
whr += " and BuyerNick=@" + (idx++).ToString();
objs.Add(list[0].ToString());
}
//if (!string.IsNullOrEmpty(list[1]))
//{
// whr += " and OrderFrom=@" + (idx++).ToString();
// objs.Add(list[1].ToString());
//}
if (!string.IsNullOrEmpty(list[1]))
{
whr += " and SellerNick=@" + (idx++).ToString();
objs.Add(list[1].ToString());
}
if (!string.IsNullOrEmpty(list[2]))
{
whr += " and ReceiverName=@" + (idx++).ToString();
objs.Add(list[2].ToString());
}
if (!string.IsNullOrEmpty(list[3]))
{
whr += " and ReceiverMobile=@" + (idx++).ToString();
objs.Add(list[3].ToString());
}
if (!string.IsNullOrEmpty(list[4]))
{
whr += " and ReceiverAddress like @" + (idx++).ToString();
objs.Add("%" + list[4].ToString() + "%");
}
}
using (var db = new Database(SQLCONN.Conn))
{
whr += " order by OrderTime desc";
var pages = db.Page<V_ERP_SuppXBatch>(page, rows, prefixWhr + whr, objs.ToArray());
var grd = new EasyGridData<V_ERP_SuppXBatch>();
grd.Init(pages);
string data = Newtonsoft.Json.JsonConvert.SerializeObject(grd);
Response.Write(data);
}
}
else
{
string data = "{\"IsError\":\"true\",\"ErrMsg\":\"登录超时,请刷新页面重新登录\"}";
Response.Write(data);
}
}
/// <summary> 查询是否由于连接没有释放引起CPU过高 spid<=50的是系统的会话，所以平时查询，最好加>50 </summary> <param
/// name="topNum">100</param> <returns></returns>
public static PetaPoco.Page<dynamic> GetCpuHighForConnectionNoRelease(string connName)
{
var db = new PetaPoco.Database(connName);
StringBuilder sb = new StringBuilder();
sb.AppendFormat("select * from master.dbo.sysprocesses where spid> 50 and waittype = 0x0000 and waittime = 0 and status = 'sleeping ' and last_batch < dateadd(minute, -10, getdate()) and login_time < dateadd(minute, -10, getdate()) ");
PetaPoco.Page<dynamic> result = new Page<dynamic>();
try
{
result = db.Page<dynamic>(1, 100, sb.ToString());
}
catch (Exception ex)
{
var mm = ex.Message;
}
return result;
}
public static PetaPoco.Page<dynamic> GetOpSystemMemory(string connName)
{
StringBuilder sb = new StringBuilder();
var db = new PetaPoco.Database(connName);
sb.AppendFormat("select * from sys.dm_os_sys_memory ");
PetaPoco.Page<dynamic> result = new Page<dynamic>();
try
{
result = db.Page<dynamic>(1, 100, sb.ToString());
}
catch (Exception ex)
{
var mm = ex.Message;
}
return result;
}
private void BindGrid(string sortfield, string sort)
{
if (Users == null)
{
SetSession();
return;
}
int pageIndex = 0;
if (list.PageIndex == 0)
{
pageIndex += 1;
}
else
{
pageIndex = list.PageIndex + 1;
}
int pageSize = list.PageSize;
string whr = string.Empty;
if (Users.SuppName != "admin")
{
whr = string.Format(" where SuppName = '{0}' ", Users.SuppName);
}
else
{
whr = " where 1=1";
}
var objs = new List<object>();
var idx = 0;
if (!string.IsNullOrEmpty(this.txtOuterIid.Text))
{
whr += " and OuterIid like @" + (idx++).ToString();
objs.Add("%" + this.txtOuterIid.Text.Trim() + "%");
}
if (!string.IsNullOrEmpty(this.txtOuterSkuId.Text))
{
whr += " and OuterSkuId like @" + (idx++).ToString();
objs.Add("%" + this.txtOuterSkuId.Text.Trim() + "%");
}
if (!string.IsNullOrEmpty(this.txtModel.Text))
{
whr += " and Model like @" + (idx++).ToString();
objs.Add("%" + this.txtModel.Text.Trim() + "%");
}
whr += " order by " + sortfield + " " + sort;
using (var db = new Database(SQLCONN.Conn))
{
var pages = db.Page<V_ERP_Inventory>(pageIndex, pageSize, "select * from V_ERP_Inventory " + whr, objs.ToArray());
list.RecordCount = (int)pages.TotalItems;
this.list.DataSource = pages.Items;
list.DataBind();
}
}
/// <summary>
/// 搜索sql根据逻辑读写次数等
/// </summary>
/// <param name="dateBegin"></param>
/// <param name="dateEnd"></param>
/// <param name="PhysicalReadsTimes"></param>
/// <param name="ExecutionCount"></param>
/// <param name="LogicalReadsTimes"></param>
/// <param name="LogicalWritesTimes"></param>
/// <param name="ElapsedTime"></param>
/// <param name="keyword"></param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <param name="connName"></param>
/// <returns></returns>
public static PetaPoco.Page<dynamic> GetSqlByReadWrite(DateTime dateBegin, DateTime dateEnd, int PhysicalReadsTimes, int ExecutionCount, int LogicalReadsTimes, int LogicalWritesTimes, int ElapsedTime, string keyword, int pageSize, int pageIndex, string connName)
{
StringBuilder sb = new StringBuilder();
sb.AppendFormat("SELECT creation_time N'语句编译时间' ");
sb.AppendFormat(" ,last_execution_time N'上次执行时间'");
sb.AppendFormat(" ,total_physical_reads N'物理读取总次数'");
sb.AppendFormat(" ,total_logical_reads/execution_count N'每次逻辑读次数'");
sb.AppendFormat(" ,total_logical_reads N'逻辑读取总次数'");
sb.AppendFormat(" ,total_logical_writes N'逻辑写入总次数'");
sb.AppendFormat(" , execution_count N'执行次数'");
sb.AppendFormat(" , total_worker_time/1000 N'所用的CPU总时间ms'");
sb.AppendFormat(" , total_elapsed_time/1000 N'总花费时间ms'");
sb.AppendFormat(" , (total_elapsed_time / execution_count)/1000 N'平均时间ms'");
sb.AppendFormat(" ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,");
sb.AppendFormat(" ((CASE statement_end_offset ");
sb.AppendFormat(" WHEN -1 THEN DATALENGTH(st.text)");
sb.AppendFormat(" ELSE qs.statement_end_offset END ");
sb.AppendFormat(" - qs.statement_start_offset)/2) + 1) N'执行语句'");
sb.AppendFormat(" FROM sys.dm_exec_query_stats AS qs");
sb.AppendFormat(" CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st");
sb.AppendFormat(" where SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,");
sb.AppendFormat(" ((CASE statement_end_offset ");
sb.AppendFormat(" WHEN -1 THEN DATALENGTH(st.text)");
sb.AppendFormat(" ELSE qs.statement_end_offset END ");
sb.AppendFormat(" - qs.statement_start_offset)/2) + 1) not like '%fetch%'");
if (dateBegin > Convert.ToDateTime("2015-01-01"))
{
sb.AppendFormat(" and last_execution_time>='{0}'", dateBegin);
}
if (dateEnd > Convert.ToDateTime("2015-01-01"))
{
sb.AppendFormat(" and last_execution_time<='{0}'", dateEnd);
}
if (PhysicalReadsTimes > 0)
{
sb.AppendFormat(" and total_physical_reads>={0} ", PhysicalReadsTimes);
}
if (ExecutionCount > 0)
{
sb.AppendFormat(" and execution_count>={0}", ExecutionCount);
}
if (LogicalReadsTimes > 0)
{
sb.AppendFormat(" and total_logical_reads>={0}", LogicalReadsTimes);
}
if (LogicalWritesTimes > 0)
{
sb.AppendFormat(" and total_logical_writes>={0}", LogicalWritesTimes);
}
if (ElapsedTime > 0)
{
sb.AppendFormat(" and total_elapsed_time>={0} ", Convert.ToInt32(ElapsedTime / 1000));
}
if (!string.IsNullOrEmpty(keyword))
{
sb.AppendFormat(" and st.text like '%{0}%'", keyword);
}
sb.AppendFormat(" ORDER BY total_elapsed_time / execution_count DESC;");
PetaPoco.Page<dynamic> result = new Page<dynamic>();
var db = new PetaPoco.Database(connName);
try
{
result = db.Page<dynamic>(pageIndex, pageSize, sb.ToString());
}
catch (Exception ex)
{
var mm = ex.Message;
}
return result;
}
/// <summary>
/// 主要查詢方法
/// </summary>
/// <param name="page">頁數（預設為 1）</param>
/// <param name="NewDraw">重新抽獎（預設為 false）</param>
/// <param name="ExportMode">匯出模式。（預設為 false）若為真，則取消分頁。</param>
private void Query(long page = 1, bool NewDraw = false, bool ExportMode = false)
{
patwGridView1.Visible = false;
btnSave.Visible = false;
btnExport.Visible = false;
string DistinctSQLStatement = "";
if (Session["DrawIDs"] == null || NewDraw)
{
// 抽出結果
DrawResult result = MakeDraw(int.Parse(tbDrawCount.Text), MaxDrawQuota, PKColumn, WinFlagColumn, DistinctColumn, TableName, BasicCondition, IsGroup);
// 若發生異常，無法抽出
if (!result.Result)
{
PatwCommon.RegisterClientScriptAlert(this, result.Msg);
return;
}
else // 正常抽出
{
patwGridView1.Visible = true;
btnSave.Visible = false;
btnExport.Visible = true;
// 排除重複後的名單
DistinctSQLStatement = result.Msg;
// 塞入 Session
Session["DrawIDs"] = result.Msg;
}
}
else
{
patwGridView1.Visible = true;
btnSave.Visible = false;
btnExport.Visible = true;
// 排除重複後的名單
DistinctSQLStatement = Convert.ToString(Session["DrawIDs"]);
}
// 組成 SQL 指令, 僅取上面抽出的那幾筆名單
sql.Append(String.Format("SELECT * FROM {0}", TableName));
sql.Append(String.Format("WHERE 1=1 AND {0} IN (SELECT MAX({0}) FROM {1} WHERE {3} IN ({2}) {4} GROUP BY {3})", PKColumn, TableName, DistinctSQLStatement, DistinctColumn, BasicCondition));
if (!ExportMode)
{
var data = db.Page <DataModel_a12SupauCheckin>(page, PageSize, sql);
patwGridView1.DataSource = data.Items;
patwGridView1.DataBind();
AspNetPager1.PageSize = (int)data.ItemsPerPage;
AspNetPager1.RecordCount = (int)data.TotalItems;
lbTotal.Text = "依據條件，目前共有 " + data.TotalItems.ToString() + " 筆";
}
else
{
var data = db.Query <DataModel_a12SupauCheckin>(sql);
patwGridView1.DataSource = data;
patwGridView1.DataBind();
AspNetPager1.Visible = false;
lbTotal.Text = "依據條件，目前共有 " + data.Count() + " 筆";
}
}
/// <summary>
/// 查看数据库链接数
/// </summary>
/// <returns></returns>
public static PetaPoco.Page<dynamic> GetDatabaseConnectNum(string connName)
{
var db = new PetaPoco.Database(connName);
StringBuilder sb = new StringBuilder();
sb.AppendFormat("select count(*) from Master.dbo.SysProcesses where dbid=db_id()");
PetaPoco.Page<dynamic> result = new Page<dynamic>();
try
{
result = db.Page<dynamic>(1, 100, sb.ToString());
}
catch (Exception ex)
{
var mm = ex.Message;
}
return result;
}
private void BindGrid(string sortfield, string sort)
{
if (Users == null)
{
SetSession();
return;
}
int pageIndex = 0;
if (itemlist.PageIndex == 0)
{
pageIndex += 1;
}
else
{
pageIndex = itemlist.PageIndex + 1;
}
int pageSize = itemlist.PageSize;
string whr = string.Empty;
if (Users.SuppName != "admin")
{
whr = string.Format(" where Distributor = '{0}' ", Users.SuppName);
}
else
{
whr = " where 1=1";
}
var objs = new List<object>();
var idx = 0;
if (!string.IsNullOrEmpty(this.txtCheckNo.Text))
{
whr += " and CheckNo like @" + (idx++).ToString();
objs.Add("%" + this.txtCheckNo.Text.Trim() + "%");
}
whr += " and IsDel=0 and IsAudit=1 and IsPrint=1 and ReturnPrice = Freight ";
whr += " order by " + sortfield + " " + sort;
using (var db = new Database(SQLCONN.Conn))
{
var pages = db.Page<V_ERP_Consignment>(pageIndex, pageSize, "select * from V_ERP_Consignment " + whr, objs.ToArray());
itemlist.RecordCount = (int)pages.TotalItems;
this.itemlist.DataSource = pages.Items;
itemlist.DataBind();
}
Ords.DataSource = null;
Ords.DataBind();
}
/// <summary>
/// 当前登陆的用户
/// </summary>
/// <returns></returns>
public static PetaPoco.Page<dynamic> GetLoginUser(string connName)
{
var db = new PetaPoco.Database(connName);
StringBuilder sb = new StringBuilder();
sb.AppendFormat("select * from sys.sql_logins ");
PetaPoco.Page<dynamic> result = new Page<dynamic>();
try
{
result = db.Page<dynamic>(1, 100, sb.ToString());
}
catch (Exception ex)
{
var mm = ex.Message;
}
return result;
}
