/// <summary> /// 前??台???需??要?a点??击??排?序??的??时???候??调???用?? /// </summary> /// <param name="reportName"> XML对?应?|的??名?称? </param> /// <param name="where"> 查??询??条??件t </param> /// <param name="pageSize"> 页?3面?打???小? </param> /// <param name="pageIndex"> 页?3面?索??引?y </param> /// <param name="order"></param> /// <param name="systemID"> 系??统?3ID</param> /// <param name="totalCount"></param> /// <returns></returns> public static DataSet GetReportData(string reportName, GetReportDataParams param, int systemID, ReportConnectionType type, out int totalCount, bool optimize = false) { if (param.Where != null) { foreach (var item in param.Where) { item.Value = item.Value.Trim(); } } DataSet ds; //switch (ConfigContext.SqlAss) //{ // case SqlEnum.MSSql: // ds = MSSqlHelper.GetReportData(reportName, param, systemID, type, out totalCount); // break; // case SqlEnum.MYSql: // ds = MySqlHelper.GetReportData(reportName, param, systemID, type, out totalCount, optimize); // if (ds.Tables[0].Columns.Cast<DataColumn>().Where(q => q.DataType == typeof(Int64)).Count() == 0) // { // break; // } // DataSet resDs = new DataSet(); // resDs.Tables.Add(ds.Tables[0].Clone()); // resDs.Tables[0].Columns.Cast<DataColumn>().Where(q => q.DataType == typeof(Int64)).ToList().ForEach(q => q.DataType = typeof(int)); // foreach (var item in ds.Tables[0].Rows) // { // resDs.Tables[0].ImportRow(item as DataRow); // } // ds = resDs; // break; // default: // ds = MSSqlHelper.GetReportData(reportName, param, systemID, type, out totalCount); // break; //} ds = MySqlHelper.GetReportData(reportName, param, systemID, type, out totalCount, optimize); if (ds.Tables[0].Columns.Cast <DataColumn>().Where(q => q.DataType == typeof(Int64)).Count() == 0) { return(ds); } DataSet resDs = new DataSet(); resDs.Tables.Add(ds.Tables[0].Clone()); resDs.Tables[0].Columns.Cast <DataColumn>().Where(q => q.DataType == typeof(Int64)).ToList().ForEach(q => q.DataType = typeof(int)); foreach (var item in ds.Tables[0].Rows) { resDs.Tables[0].ImportRow(item as DataRow); } ds = resDs; return(ds); }
/// <summary> /// 导??出?报???表??? /// </summary> /// <param name="reportSql"> 导??出?报???表???的??SQL </param> /// <param name="where"> 过y滤?条??件t </param> /// <param name="order"> 排?序??字??段? </param> /// <returns></returns> public static DataSet GetReportExportData(string reportName, List <KeyValue> where, int systemID, ReportConnectionType type) { if (where == null) { where = new List <KeyValue>(); } string reportPath = CReport.GetReportPath(systemID); //报表路??径? if (string.IsNullOrEmpty(reportPath)) { return(new DataSet()); } string reportSql = CReport.GetSql(reportPath, reportName, where); //获?取??要?a查??询??的??SQL语??句? 及??其? 参?数?y if (string.IsNullOrEmpty(reportSql)) { return(new DataSet()); } string conString = CReport.GetConnection(type); //获?取??SQL连??接??串?? if (string.IsNullOrEmpty(conString)) { return(new DataSet()); } string sql = reportSql; //int count = CheckExportCount(sql, conString, where, ""); DataSet ds = new DataSet(); //for (int i = 0; i < Math.Ceiling((double)count / 9000); i++) //{ // var sqlItem = string.Format(sql + " limit {0},{1}", i * 9000, 9000); // if (ds.Tables.Count == 0) // { // ds = ExcuteMyDs(sqlItem, where, conString); // } // else // { // DataTable dt = ExcuteMyDs(sqlItem, where, conString).Tables[0]; // ds.Tables[0].Merge(dt); // } //} //if (count == 0) //{ ds = ExcuteMyDs(sql, where, conString); //} //MonitorManager.SetReturnValue(string.Format("Excel导??出?{0}条??", count)); return(ds); }
/// <summary> /// 导??出?报???表??? /// </summary> /// <param name="reportSql"> 导??出?报???表???的??SQL </param> /// <param name="where"> 过y滤?条??件t </param> /// <param name="order"> 排?序??字??段? </param> /// <returns></returns> public static DataSet GetReportExportData(string reportName, List <KeyValue> where, int systemID, ReportConnectionType type) { if (where != null) { foreach (var item in where) { item.Value = item.Value.Trim(); } } //switch (ConfigContext.SqlAss) //{ // case SqlEnum.MSSql: // return MSSqlHelper.GetReportExportData(reportName, where, systemID, "", type); // case SqlEnum.MYSql: // { // DataSet ds = MySqlHelper.GetReportExportData(reportName, where, systemID, "", type); // if (ds.Tables[0].Columns.Cast<DataColumn>().Where(q => q.DataType == typeof(Int64)).Count() == 0) // return ds; // DataSet resDs = new DataSet(); // resDs.Tables.Add(ds.Tables[0].Clone()); // resDs.Tables[0].Columns.Cast<DataColumn>().Where(q => q.DataType == typeof(Int64)).ToList().ForEach(q => q.DataType = typeof(int)); // foreach (var item in ds.Tables[0].Rows) // { // resDs.Tables[0].ImportRow(item as DataRow); // } // return resDs; // } // default: // return MSSqlHelper.GetReportExportData(reportName, where, systemID, "", type); //} DataSet ds = MySqlHelper.GetReportExportData(reportName, where, systemID, type); if (ds.Tables[0].Columns.Cast <DataColumn>().Where(q => q.DataType == typeof(Int64)).Count() == 0) { return(ds); } DataSet resDs = new DataSet(); resDs.Tables.Add(ds.Tables[0].Clone()); resDs.Tables[0].Columns.Cast <DataColumn>().Where(q => q.DataType == typeof(Int64)).ToList().ForEach(q => q.DataType = typeof(int)); foreach (var item in ds.Tables[0].Rows) { resDs.Tables[0].ImportRow(item as DataRow); } return(resDs); }
/// <summary> /// 报???表???管??理???查??询??语??句? /// </summary> /// <param name="sql"></param> /// <param name="pageSize"></param> /// <param name="pageIndex"></param> /// <param name="type"></param> /// <param name="totalCount"></param> /// <returns></returns> public static DataSet GetReportData(string sql, int pageSize, int pageIndex, string Order, ReportConnectionType type, out int totalCount) { //switch (ConfigContext.SqlAss) //{ // case SqlEnum.MSSql: // return MSSqlHelper.GetReportData(sql, pageSize, pageIndex, Order, type, out totalCount); // case SqlEnum.MYSql: // { // DataSet ds = MySqlHelper.GetReportData(sql, pageSize, pageIndex, Order, type, out totalCount); // if (ds == null || ds.Tables.Count == 0) // return null; // if (ds.Tables[0].Columns.Cast<DataColumn>().Where(q => q.DataType == typeof(Int64)).Count() == 0) // return ds; // DataSet resDs = new DataSet(); // resDs.Tables.Add(ds.Tables[0].Clone()); // resDs.Tables[0].Columns.Cast<DataColumn>().Where(q => q.DataType == typeof(Int64)).ToList().ForEach(q => q.DataType = typeof(int)); // foreach (var item in ds.Tables[0].Rows) // { // resDs.Tables[0].ImportRow(item as DataRow); // } // return resDs; // } // default: // return MSSqlHelper.GetReportData(sql, pageSize, pageIndex, Order, type, out totalCount); //} DataSet ds = MySqlHelper.GetReportData(sql, pageSize, pageIndex, Order, type, out totalCount); if (ds == null || ds.Tables.Count == 0) { return(null); } if (ds.Tables[0].Columns.Cast <DataColumn>().Where(q => q.DataType == typeof(Int64)).Count() == 0) { return(ds); } DataSet resDs = new DataSet(); resDs.Tables.Add(ds.Tables[0].Clone()); resDs.Tables[0].Columns.Cast <DataColumn>().Where(q => q.DataType == typeof(Int64)).ToList().ForEach(q => q.DataType = typeof(int)); foreach (var item in ds.Tables[0].Rows) { resDs.Tables[0].ImportRow(item as DataRow); } return(resDs); }
public static DataSet GetReportData(string sql, int pageSize, int pageIndex, string Order, ReportConnectionType type, out int totalCount) { string order = null; totalCount = 0; if (pageIndex < 1) //不能出现索引页小于1的情况,否则查询语句报错 { return(new DataSet()); } string conString = CReport.GetConnection(); //获取SQL连接串 if (string.IsNullOrEmpty(conString)) { return(new DataSet()); } string rowOrder = Order; if (!string.IsNullOrEmpty(order)) { rowOrder = "order by " + order + ""; } else { rowOrder = "order by (select 0)"; } int start = pageSize * (pageIndex - 1) + 1; int end = pageSize * pageIndex; var match = Regex.Match(sql, @"\s+order\s+", RegexOptions.IgnoreCase); //检查语句中是否含有order by string strCount = sql; if (match.Success) //有order by 则舍去order by { strCount = sql.Substring(0, match.Index); if (string.IsNullOrEmpty(order)) { rowOrder = sql.Substring(match.Index); } } sql = @" SELECT * FROM ( SELECT Row_Number() OVER ({0}) row, * from ( select * FROM (" + strCount + " ) tt) t ) item " + " WHERE item.row BETWEEN " + start + " AND " + end + " "; sql = string.Format(sql, rowOrder); strCount = "select count(0) from (" + strCount + ") item "; sql = sql + ";" + strCount; try { using (SqlConnection conn = new SqlConnection(conString)) { SqlCommand cmd = new SqlCommand(sql, conn); DataSet ds = new DataSet(); SqlDataAdapter adp = new SqlDataAdapter(cmd); adp.Fill(ds); totalCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]); return(ds); } } catch (Exception) { return(null); } }
/// <summary> /// 导出报表 /// </summary> /// <param name="reportSql">导出报表的SQL</param> /// <param name="where">过滤条件</param> /// <param name="order">排序字段</param> /// <returns></returns> public static DataSet GetReportExportData(string reportName, List <KeyValue> where, int systemID, string order, ReportConnectionType type) { if (where == null) { where = new List <KeyValue>(); } string reportPath = CReport.GetReportPath(systemID); //报表路径 if (string.IsNullOrEmpty(reportPath)) { return(new DataSet()); } string sqlCount = ""; string reportSql = CReport.GetSql(reportPath, reportName, where, 1); //获取要查询的SQL语句 及其 参数 if (string.IsNullOrEmpty(reportSql)) { return(new DataSet()); } string conString = CReport.GetConnection(); //获取SQL连接串 if (string.IsNullOrEmpty(conString)) { return(new DataSet()); } string sql = reportSql; int count = CheckExportCount(sql, conString, where, ""); DataSet ds = new DataSet(); ds = ExcuteMSDs(sql, where, conString); //MonitorManager.SetReturnValue(string.Format("Excel导出{0}条", count)); return(ds); }
/// <summary> /// 报???表???统?3计? /// </summary> /// <param name="reportName"> 对?应?|的??名?称? </param> /// <param name="systemID"> 系??统?3ID</param> /// <param name="where"> 查??询??条??件t </param> /// <param name="type"> 报???表???路??径? </param> /// <param name="Totalstartsql"> Sql头???(select order ,count(*) as total from ) </param> /// <param name="Totalendsql"> Sql(group by order)</param> /// <returns></returns> public static DataSet GetReportTotal(string reportName, List <KeyValue> where, int systemID, ReportConnectionType type, bool optimize = false) { string reportPath = CReport.GetReportPath(systemID); //报???表???路??径? if (string.IsNullOrEmpty(reportPath)) { return(new DataSet()); } string sql = CReport.GetTotalSql(reportPath, reportName, where).Trim(); //获?取??要?a查??询??的??SQL语??句? 及??其? 参?数?y if (string.IsNullOrEmpty(sql)) { return(new DataSet()); } string conString = CReport.GetConnection(type); //获取SQL连接串 if (string.IsNullOrEmpty(conString)) { return(new DataSet()); } DataSet ds = new DataSet(); using (MySqlConnection conn = new MySqlConnection(conString)) { MySqlCommand cmd = new MySqlCommand(sql, conn); //where 替换 foreach (var data in where) { cmd.Parameters.Add(new MySqlParameter("@" + data.Key, data.Value)); } MySqlDataAdapter adp = new MySqlDataAdapter(cmd); adp.Fill(ds); return(ds); } }
public static DataSet GetReportData(string sql, int pageSize, int pageIndex, string Order, ReportConnectionType type, out int totalCount) { totalCount = 0; if (pageIndex < 1) //不?能??出?现?索??引?y页?3小?于???1的??情??况?,??否??则??查??询??语??句?报???错??? { return(new DataSet()); } if (sql == null) { return(null); } sql = sql.Trim(); string conString = CReport.GetConnection(type); //获?取??SQL连??接??串?? if (string.IsNullOrEmpty(conString)) { return(new DataSet()); } var order = Order; string rowOrder = ""; if (!string.IsNullOrEmpty(order)) { if (order.ToUpper() == "NEWID()") { //取??随?机??行D数?y order = "RAND()"; } rowOrder = "order by " + order + ""; } int start = pageSize * (pageIndex - 1); int end = pageSize * pageIndex; var match = Regex.Match(sql, @"\s+order\s+", RegexOptions.IgnoreCase); //检??查??语??句?中D是??否??含?有?Dorder by string strCount = sql; sql = string.Format(strCount + " " + rowOrder + " limit {0},{1}", start, pageSize); strCount = "select count(0) from (" + strCount + ") item "; sql = sql + ";" + strCount; try { using (MySqlConnection conn = new MySqlConnection(conString)) { MySqlCommand cmd = new MySqlCommand(sql, conn); DataSet ds = new DataSet(); MySqlDataAdapter adp = new MySqlDataAdapter(cmd); adp.Fill(ds); totalCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]); return(ds); } } catch (Exception) { return(null); } }
public static DataSet GetReportData(string reportName, GetReportDataParams param, int systemID, ReportConnectionType type, out int totalCount, bool optimize = false) { int pageSize = param.PageSize; int pageIndex = param.PageIndex; string order = param.Order; List <KeyValue> where = param.Where; totalCount = 0; if (pageIndex < 1) //不?能??出?现?索??引?y页?3小?于???1的??情??况?,??否??则??查??询??语??句?报???错??? { pageIndex = 1; } if (where == null) { where = new List <KeyValue>(); } string reportPath = CReport.GetReportPath(); //报???表???路??径? if (string.IsNullOrEmpty(reportPath)) { return(new DataSet()); } string sql = CReport.GetSql(reportPath, reportName, where); //获?取??要?a查??询??的??SQL语??句? 及??其? 参?数?y if (string.IsNullOrEmpty(sql)) { return(new DataSet()); } else { sql = sql.Trim(); } string conString = CReport.GetConnection(type); //获取SQL连接串 if (string.IsNullOrEmpty(conString)) { return(new DataSet()); } string rowOrder = ""; if (!string.IsNullOrEmpty(order)) { if (order.ToUpper() == "NEWID()") { //取??随?机??行D数?y order = "RAND()"; } rowOrder = "order by " + order + ""; } int start = pageSize * (pageIndex - 1); // int end = pageSize * pageIndex; var matchs = Regex.Matches(sql, @"\s+order\s+", RegexOptions.IgnoreCase); //检查语句中是否含有order by string strCount = sql; if (matchs.Count > 1) { strCount = sql.Substring(0, matchs[matchs.Count - 1].Index); if (string.IsNullOrEmpty(order)) { rowOrder = sql.Substring(matchs[matchs.Count - 1].Index); } } else if (matchs.Count == 1) { strCount = sql.Substring(0, matchs[0].Index); if (string.IsNullOrEmpty(order)) { rowOrder = sql.Substring(matchs[0].Index); } } sql = string.Format(strCount + " " + rowOrder); if (!optimize) //是否自定义分页 { sql += string.Format(" limit {0},{1}", start, pageSize); sql = sql.Insert(sql.ToLower().IndexOf("select") + 6, " SQL_CALC_FOUND_ROWS "); sql = sql + ";" + "select FOUND_ROWS()"; } else { sql = sql.Replace("@PagerLimit", string.Format(" limit {0},{1}", start, pageSize)); //针对特殊的分页,limit不是在sql的最后面的情况 } var Rundate = DateTime.Now; int RunTime = 0; using (MySqlConnection conn = new MySqlConnection(conString)) { MySqlCommand cmd = new MySqlCommand(sql, conn); //where 替??换? foreach (var data in where) { cmd.Parameters.Add(new MySqlParameter("@" + data.Key, data.Value)); } DataSet ds = new DataSet(); MySqlDataAdapter adp = new MySqlDataAdapter(cmd); adp.Fill(ds); totalCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]); RunTime = (int)DateTime.Now.Subtract(Rundate).TotalMilliseconds; //记录日志 //if (RunTime > 2000) //Clients.LogClient.AddApplicationStartLogAsync(new U1City.ECDRP.Component.LogServices.ApplicationStartLog() { LogType = 1, Message = reportName, StartTime = DateTime.Now, RunTime = RunTime, SystemID = systemID }); return(ds); } }