//insert 表的时候,避免表主键重复 /// <summary> /// insert 表的时候,避免表主键重复 /// </summary> /// <param name="strTable" > 表名 </param> /// <param name="strCol" > 主键ID名 </param> /// <returns></returns> public string getTableMaxId(string strTable, string strCol) { //定义参数 string strMaxID = string.Empty; Database dbbase = DataBaseHelper.CreateDataBase(DataBaseHelper.typedb.zlyjgisdb); using (DbConnection conn = dbbase.CreateConnection()) { if (conn.State != ConnectionState.Open) { conn.Open(); } try { //*********根据ID编码查询指标数据信息 -----****************// string strSql = @"select(decode(max(" + strCol + "), null, 1000000000, max(" + strCol + "))) + 1 from " + strTable; strMaxID = Convert.ToString(dbbase.ExecuteScalar(dbbase.GetSqlStringCommand(strSql))); } catch (Exception ex) { JNJC.Utility.Help.HelpUtils.ErrorLog("查询表主键ID -查询表主键ID! ||" + ex.ToString()); } return(strMaxID); } }
public void CreateAndVerifyNewWareHouseDb(string warehouseDbName) { //Create a new database. DataBaseHelper.CreateDataBase(warehouseDbName); //The initial table count would be 0. int count = DataBaseHelper.GetTableCount(warehouseDbName); Assert.IsTrue((count == 0), "Initial count of tables is not 0 right after creating warehouse DB. Actual : {0}", count); //Invoke the task. TaskInvocationHelper.InvokeCreateWarehouseArtifactTask(DataBaseHelper.GetConnectionStringForDataBase(warehouseDbName), false); //The task should be have created 8 tables in the database. count = DataBaseHelper.GetTableCount(warehouseDbName); Assert.IsTrue((count == 8), "Count of tables is not 8 in the warehouse DB after executing the CreateWarehouseArtifactTask. Actual : {0}", count); //To do : Add more validations around the data that has to be present in the tables. }
/// <summary> /// 王岩松 /// 2017年8月8日18:51:21 /// excel 导入到 数据库中 /// </summary> /// <param name="filePath"></param> /// <param name="isColumnName"></param> /// <returns></returns> public static DataTable InpExcelToDataBase(string filePath, bool isColumnName) { DataTable dataTable = null; FileStream fs = null; //IWorkbook workbook = null; //ISheet sheet = null; //IRow row = null; try { //生产GUID System.Guid guid = new Guid(); guid = Guid.NewGuid(); string strGuid = Convert.ToString(guid); Database dbbase = DataBaseHelper.CreateDataBase(DataBaseHelper.typedb.mirs); using (DbConnection dbconns = dbbase.CreateConnection()) { if (!File.Exists(filePath)) { } //判断连接是否打开。如果连接被关闭,才Open 。 if (dbconns.State != ConnectionState.Open) { dbconns.Open(); } using (DbTransaction tran = dbconns.BeginTransaction()) { using (fs = File.OpenRead(filePath)) { tran.Commit(); } return(dataTable); } } } catch (Exception ex) { string strEx = ex.ToString(); if (fs != null) { fs.Close(); } return(null); } }
public bool setQYTotal(string strtype) { Database dbbase = DataBaseHelper.CreateDataBase(DataBaseHelper.typedb.zlyjgisdb); using (DbConnection conn = dbbase.CreateConnection()) using (DbCommand cmd = conn.CreateCommand()) { conn.Open(); try { #region /******************************************* * 功能:更新企业的排放量信息 * 作者:wys * 日期:2016年8月29日16:31:42 ********************************************/ using (DbTransaction trans = conn.BeginTransaction()) { string sqlupdate = ""; if (strtype == "P") { sqlupdate = " update G_POLLUTANTSITE g set g.total='' "; } else { sqlupdate = " update G_POLLUTANTSITE g set g.dbl='' "; } using (DbCommand cmdss = dbbase.GetSqlStringCommand(sqlupdate)) { dbbase.ExecuteNonQuery(cmdss, trans); } trans.Commit(); } #endregion return(true); } catch { return(false); } } }
public object AddMenu(dynamic obj) { Dictionary <string, object> result = new Dictionary <string, object>(); Database dbbase = DataBaseHelper.CreateDataBase(); using (DbConnection conn = dbbase.CreateConnection()) { if (conn.State != ConnectionState.Open) { conn.Open(); } string strSql = "insert into Menus(MENUID,CODE,NAME,ADDRESS,ICONS,ORDER) values (:MENUID,:CODE,:NAME,:ADDRESS,:ICONS,:ORDER)"; using (DbTransaction tran = conn.BeginTransaction()) { try { using (DbCommand cmd = dbbase.GetSqlStringCommand(strSql)) { dbbase.AddInParameter(cmd, "MENUID", DbType.String, ""); dbbase.AddInParameter(cmd, "CODE", DbType.String, ""); dbbase.AddInParameter(cmd, "NAME", DbType.DateTime, ""); dbbase.AddInParameter(cmd, "ADDRESS", DbType.String, ""); dbbase.AddInParameter(cmd, "ICONS", DbType.DateTime, ""); dbbase.AddInParameter(cmd, "ORDER", DbType.String, ""); dbbase.ExecuteNonQuery(cmd, tran); } tran.Commit(); result.Add("result", true); result.Add("msg", "添加数据成功"); return(null); } catch (Exception e) { tran.Rollback(); Utils.ErrorLog("HomeController-AddMenu! ||" + e.ToString()); result.Add("result", false); result.Add("msg", "添加数据失败"); return(result); } } } }
public object LoadMenu(dynamic obj) { //Dictionary<string, object> result = new Dictionary<string, object>(); string strSql = "SELECT SYS_PERMISSION_ID as ID,CNAME AS NAME,PID,CURL,IORDER FROM SYS_PERMISSION order by iorder "; Database db = DataBaseHelper.CreateDataBase(); DataTable dt; //DataTable reDataTable; using (DbConnection conn = db.CreateConnection()) { if (conn.State == ConnectionState.Closed) { conn.Open(); } dt = db.ExecuteDataSet(CommandType.Text, strSql).Tables[0]; } ArrayList list = FormatJsonExtension.DataTableArrayList(dt); return(this.JsonFormat(list)); }
public object LoadMenu(dynamic obj) { //Dictionary<string, object> result = new Dictionary<string, object>(); string strSql = "SELECT SYS_PERMISSION_ID as ID,CNAME AS NAME,PID,CURL,IORDER FROM SYS_PERMISSION order by iorder "; var db = DataBaseHelper.CreateDataBase(); DataTable dt; using (DbConnection conn = db.CreateConnection()) { if (conn.State == ConnectionState.Closed) { conn.Open(); } dt = db.ExecuteDataSet(CommandType.Text, strSql).Tables[0]; } var result = new List <MenuModel>(); var memo = new MenuModel(); DiGuiDataTable(dt, memo, result, -1); return(this.JsonFormat(result)); }
public ActionResult loadlist() { Dictionary <string, object> result = new Dictionary <string, object>(); string strSql = "select T.Sys_Permission_Id as ID,T.CNAME,T.CURL,T.PID,IORDER,IISAVAILABLE from sys_permission t"; var dbbase = DataBaseHelper.CreateDataBase(); DataTable dt; using (DbConnection conn = dbbase.CreateConnection()) { if (conn.State == ConnectionState.Closed) { conn.Open(); } dt = dbbase.ExecuteDataSet(CommandType.Text, strSql).Tables[0]; } ArrayList list = FormatJsonExtension.DataTableArrayList(dt); result.Add("code", "0"); result.Add("msg", ""); result.Add("count", list.Count); result.Add("data", list); return(this.JsonFormat(result)); }
/// <summary> /// 查询数据分页显示 /// </summary> /// <returns></returns> public ActionResult loadlistpage() { //页码 int page = Convert.ToInt32(Request["page"]); //页条数 int rows = Convert.ToInt32(Request["limit"]); //where string sqlwhere = "1=1"; string aaa = Request["aaa"]; //排序 string sort = "ID desc"; //表名 string tableName = "MT_WORKORDER"; //列 string columns = "ID,CCODE,CCODENAME,DDATE,CMAKER,DCREATESYSTIME,CSOUCE,CTHINGADDRESS"; //总条数 int toalCount = 0; Database db = DataBaseHelper.CreateDataBase(); //DatabaseFactory.CreateDatabase("DEFAULT_CONNECTION_STRING"); SqlPage sqlpage = new SqlPage(); DataTable dt = sqlpage.GetDataSet(tableName, columns, sqlwhere, sort, page, rows, out toalCount).Tables[0]; ArrayList list = FormatJsonExtension.DataTableArrayList(dt); Dictionary <String, object> result = new Dictionary <string, object>(); result.Add("code", "0"); result.Add("msg", ""); result.Add("count", toalCount); result.Add("data", list); return(this.JsonFormat(result)); }
public object DelMenu(dynamic obj) { Dictionary <string, object> result = new Dictionary <string, object>(); Database dbbase = DataBaseHelper.CreateDataBase(); using (DbConnection conn = dbbase.CreateConnection()) { if (conn.State != ConnectionState.Open) { conn.Open(); } string strSql = "Delete from Menus where MENUID=:MENUID"; using (DbTransaction tran = conn.BeginTransaction()) { try { using (DbCommand cmd = dbbase.GetSqlStringCommand(strSql)) { dbbase.AddInParameter(cmd, "MENUID", DbType.String, ""); dbbase.ExecuteNonQuery(cmd, tran); } tran.Commit(); result.Add("result", true); result.Add("msg", "删除数据成功"); return(null); } catch (Exception e) { tran.Rollback(); Utils.ErrorLog("HomeController-DelMenu! ||" + e.ToString()); result.Add("result", false); result.Add("msg", "删除数据失败"); return(result); } } } }
/// <summary> /// 查询全省行政区域的初始用能权 /// </summary> /// <param name="CATEGORY">查询类别</param> /// <param name="REGIONCODE">行政区域code</param> /// <param name="EXECUTEDATE">执行时间</param> /// <param name="page">页数</param> /// <param name="rowpage">行数</param> /// <returns>返回2种情况(行政区,数量;企业,数量)</returns> public static Dictionary <String, Object> SearchCS(string CATEGORY) { List <EnergyConsumptionModel> oList = new List <EnergyConsumptionModel>(); //创建对象和变量。 int toalCount; string col = string.Empty; SqlPage oPage = new SqlPage(); //new 分页查询对象 string strWhere = string.Empty; //where条件 string strSql = string.Empty; //sql语句 string strCol = string.Empty; //查询的字段 string strGb = string.Empty; //group by 分组 string strOb = string.Empty; //order by 排序 ModelHandler <EnergyConsumptionModel> opp = new ModelHandler <EnergyConsumptionModel>(); Dictionary <String, Object> result = new Dictionary <string, object>(); DataTable dt = new DataTable(); //new DataTable对象 Database dbbase = DataBaseHelper.CreateDataBase(DataBaseHelper.typedb.zlyjgisdb); using (DbConnection dbconn = dbbase.CreateConnection()) using (DbCommand cmd = dbconn.CreateCommand()) { if (dbconn.State != ConnectionState.Open) { dbconn.Open(); } try { logs.Info("555555"); return(result); } catch (Exception ex) { JNJC.Utility.Help.HelpUtils.ErrorLog("初始用能权-加载GRID数据错误! ||" + ex.ToString()); return(result); } } }
public object LoadStoredProcedureMenu(dynamic obj) { Dictionary <string, object> result = new Dictionary <string, object>(); string strSql = "存储过程"; Database db = DataBaseHelper.CreateDataBase(); DataTable dt; using (DbConnection conn = db.CreateConnection()) { if (conn.State == ConnectionState.Closed) { conn.Open(); } dt = db.ExecuteDataSet(CommandType.StoredProcedure, strSql).Tables[0]; } ArrayList list = FormatJsonExtension.DataTableArrayList(dt); result.Add("code", "200"); result.Add("msg", "ok"); result.Add("data", list); result.Add("count", list.Count); return(result); }
/// <summary> /// 查询全省行政区域的用能权统计 /// </summary> /// <param name="STRATTIME">开始时间</param> /// <param name="ENDTIME">结束时间</param> /// <param name="CATEGORY">查询类别</param> /// <param name="REGIONCODE">行政区CODE</param> /// <param name="page">页数</param> /// <param name="rowpage">行数</param> /// <returns>返回2种情况(行政区,数量;企业,数量)</returns> public static Dictionary <String, Object> SearchTJ(string STRATTIME, string ENDTIME, string CATEGORY, string REGIONCODE, int page, int rowpage) { List <EnergyConsumptionModel> oList = new List <EnergyConsumptionModel>(); //创建对象和变量。 int toalCount; string col = string.Empty; SqlPage oPage = new SqlPage(); //new 分页查询对象 string strWhere = string.Empty; //where条件 string strSql = string.Empty; //sql语句 string strCol = string.Empty; //查询的字段 string strGb = string.Empty; //group by 分组 string strOb = string.Empty; //order by 排序 ModelHandler <EnergyConsumptionModel> opp = new ModelHandler <EnergyConsumptionModel>(); Dictionary <String, Object> result = new Dictionary <string, object>(); DataTable dt = new DataTable(); //new DataTable对象 Database dbbase = DataBaseHelper.CreateDataBase(DataBaseHelper.typedb.zlyjgisdb); using (DbConnection dbconn = dbbase.CreateConnection()) using (DbCommand cmd = dbconn.CreateCommand()) { if (dbconn.State != ConnectionState.Open) { dbconn.Open(); } try { List <ColumnModel> cm = new List <ColumnModel>(); if (CATEGORY == "CATEGORY") { strWhere = " XZQ.PYJC is not null"; strSql = @" T_NYXFZLKZ_DQJBXXB XZQ left join (select XZQHBM, sum(nvl(RTOTAL, 0)) as RTOTAL from M_QYJCXXB QY left join T_ENERGY_RECORD JL on QY.FRDWDM = JL.RENTERPRISECODE --where 界面查询条件 where RDATE >= to_date('" + STRATTIME + @"', 'yyyy-MM-dd') and RDATE <= to_date('" + ENDTIME + @"', 'yyyy-MM-dd') and RTYPE in (0, 1, 2) group by XZQHBM) C --CUN 存有 on XZQ.XZQHBM = C.XZQHBM left join (select XZQHBM, sum(nvl(RTOTAL, 0)) as RTOTAL from M_QYJCXXB QY left join T_ENERGY_RECORD JL on QY.FRDWDM = JL.RENTERPRISECODE --where 界面查询条件 where RDATE >= to_date('" + STRATTIME + @"', 'yyyy-MM-dd') and RDATE <= to_date('" + ENDTIME + @"', 'yyyy-MM-dd') and RTYPE in (3) group by XZQHBM) M --MAI 出售 on XZQ.XZQHBM = C.XZQHBM "; strCol = @" XZQ.XZQHBM as REGIONCODE , XZQ.XZQHM as REGIONNAME , nvl(C.RTOTAL, 0) c --全部用能权 , NVL(M.RTOTAL, 0) M --卖出用能权 , nvl(C.RTOTAL, 0) - NVL(M.RTOTAL, 0) as ENERGYNUMBER "; strOb = " XZQ.XZQHBM "; strGb = ""; cm = new List <ColumnModel>(); //用能权统计--行政区 cm.Add(new ColumnModel { title = "行政区编码", field = "REGIONCODE", align = "center", hidden = "hidden" }); cm.Add(new ColumnModel { title = "行政区名称", field = "REGIONNAME", align = "center", width = "40%" }); cm.Add(new ColumnModel { title = "用能权统计(t)", field = "ENERGYNUMBER", sortable = "true", align = "right", width = "60%" }); if (cm.Count == 1) { cm.Clear(); } } else { strWhere = "1=1"; strSql = @" (select qy.dwxxmc, sum(nvl(rtotal, 0)) as rtotal from m_qyjcxxb qy left join t_energy_record jl on qy.frdwdm = jl.renterprisecode --where 界面查询条件 where RDATE >= to_date('" + STRATTIME + @"', 'yyyy-MM-dd') and RDATE <= to_date('" + ENDTIME + @"', 'yyyy-MM-dd') and rtype in (0, 1, 2) and qy.xzqhbm='" + REGIONCODE + @"' group by qy.dwxxmc) c --cun 存有 left join (select qy.dwxxmc, sum(nvl(rtotal, 0)) as rtotal from m_qyjcxxb qy left join t_energy_record jl on qy.frdwdm = jl.renterprisecode --where 界面查询条件 where RDATE >= to_date('" + STRATTIME + @"', 'yyyy-MM-dd') and RDATE <= to_date('" + ENDTIME + @"', 'yyyy-MM-dd') and rtype in (3) and qy.xzqhbm='" + REGIONCODE + @"' group by qy.dwxxmc) m --mai 出售 on c.dwxxmc=m.dwxxmc"; strCol = @" c.dwxxmc as COMPANYNAME , nvl(c.rtotal, 0) c --全部用能权 , nvl(m.rtotal, 0) m --卖出用能权 , nvl(c.rtotal, 0) - nvl(m.rtotal, 0) as ENERGYNUMBER"; strOb = ""; strGb = ""; cm = new List <ColumnModel>(); cm.Add(new ColumnModel { title = "企业名称", field = "COMPANYNAME", align = "left", width = "40%" }); //判断查询的监测因子项目,,动态生成grid的列头。 //初始用能 cm.Add(new ColumnModel { title = "初始用能(t)", field = "ENERGYNUMBER", sortable = "true", align = "right", width = "60%" }); if (cm.Count == 1) { cm.Clear(); } } //返回dt数据。 dt = oPage.GetDataSetGroupBy(DataBaseHelper.typedb.zlyjgisdb, strSql, strCol , strWhere, strOb, strGb, page, rowpage, out toalCount).Tables[0]; // 返回Sql语句,目的是绑定excel。 string strSqlT = ""; string strSqlP = ""; strSqlP = oPage.GetListSQLS(strSql, strCol, 0, 0, strOb, strWhere, strOb, out strSqlT); //绑定数据到list。 oList = opp.FillModel(dt); // GP 服务 废气 王岩松 2016年6月23日17:44:43 string stringGp = " SELECT g.city,gscode, g.name, G.x, g.y ORDER BY g.city "; if (dt != null && dt.Rows.Count > 0) { oList = opp.FillModel(dt); var oLists = from tGride in oList select new { tGride.REGIONCODE, tGride.REGIONNAME, tGride.COMPANYNAME, tGride.ENERGYNUMBER, }; result.Add("rows", oLists); result.Add("total", toalCount); result.Add("cols", cm); result.Add("sql", strSqlT); result.Add("GP", stringGp); } else { result.Add("total", 0); result.Add("rows", oList); result.Add("cols", cm); result.Add("sql", strSqlT); result.Add("GP", stringGp); } return(result); } catch (Exception ex) { JNJC.Utility.Help.HelpUtils.ErrorLog("初始用能权-加载GRID数据错误! ||" + ex.ToString()); return(result); } } }
/// <summary> /// 查询全省行政区域的交易统计 /// </summary> /// <param name="STRATTIME">开始时间</param> /// <param name="ENDTIME">结束时间</param> /// <param name="CATEGORY">查询类别</param> /// <param name="REGIONCODE">行政区CODE</param> /// <param name="page">页数</param> /// <param name="rowpage">行数</param> /// <returns>返回2种情况(行政区,数量;企业,数量)</returns> public static Dictionary <String, Object> SearchJY(string STRATTIME, string ENDTIME, string CATEGORY, string REGIONCODE, int page, int rowpage) { List <EnergyConsumptionModel> oList = new List <EnergyConsumptionModel>(); //创建对象和变量。 int toalCount; string col = string.Empty; SqlPage oPage = new SqlPage(); //new 分页查询对象 string strWhere = string.Empty; //where条件 string strSql = string.Empty; //sql语句 string strCol = string.Empty; //查询的字段 string strGb = string.Empty; //group by 分组 string strOb = string.Empty; //order by 排序 ModelHandler <EnergyConsumptionModel> opp = new ModelHandler <EnergyConsumptionModel>(); Dictionary <String, Object> result = new Dictionary <string, object>(); DataTable dt = new DataTable(); //new DataTable对象 Database dbbase = DataBaseHelper.CreateDataBase(DataBaseHelper.typedb.zlyjgisdb); using (DbConnection dbconn = dbbase.CreateConnection()) using (DbCommand cmd = dbconn.CreateCommand()) { if (dbconn.State != ConnectionState.Open) { dbconn.Open(); } try { List <ColumnModel> cm = new List <ColumnModel>(); if (CATEGORY == "CATEGORY") { strWhere = " XZQ.PYJC is not null"; strSql = @" T_NYXFZLKZ_DQJBXXB XZQ left join (select XZQHBM, sum(nvl(tmnumber, 0)) as RTOTAL from M_QYJCXXB QY left join T_TRANSACTION_MANAGEMENT JY on QY.FRDWDM = JY.Tmenterrisecode --where 界面查询条件 where tmtime >= to_date('" + STRATTIME + @"', 'yyyy-MM-dd') and tmtime <= to_date('" + ENDTIME + @"', 'yyyy-MM-dd') group by XZQHBM) C --CUN 存有 on XZQ.XZQHBM = C.XZQHBM"; strCol = @" XZQ.XZQHBM as REGIONCODE, XZQ.XZQHM as REGIONNAME, nvl(C.RTOTAL, 0) as ENERGYNUMBER"; strOb = "XZQ.XZQHBM"; strGb = ""; cm = new List <ColumnModel>(); //用能权统计--行政区 cm.Add(new ColumnModel { title = "行政区编码", field = "REGIONCODE", align = "center", hidden = "hidden" }); cm.Add(new ColumnModel { title = "行政区名称", field = "REGIONNAME", align = "center", width = "40%" }); cm.Add(new ColumnModel { title = "用能权统计(t)", field = "ENERGYNUMBER", sortable = "true", align = "right", width = "60%" }); if (cm.Count == 1) { cm.Clear(); } } else { // add 行政区划编码 strWhere = @"QY.XZQHBM ='" + REGIONCODE + "' and tmtime >= to_date('" + STRATTIME + @"', 'yyyy-MM-dd') and tmtime <= to_date('" + ENDTIME + @"', 'yyyy-MM-dd')"; strSql = @"M_QYJCXXB QY left join T_TRANSACTION_MANAGEMENT JY on QY.FRDWDM = JY.Tmenterrisecode"; strCol = @" qy.dwxxmc as COMPANYNAME, sum(nvl(tmnumber, 0)) as ENERGYNUMBER"; strOb = ""; strGb = "qy.dwxxmc"; cm = new List <ColumnModel>(); cm.Add(new ColumnModel { title = "企业名称", field = "COMPANYNAME", align = "left", width = "40%" }); //初始用能 动态生成grid的列头。 cm.Add(new ColumnModel { title = "初始用能(t)", field = "ENERGYNUMBER", sortable = "true", align = "right", width = "60%" }); if (cm.Count == 1) { cm.Clear(); } } //返回dt数据。 dt = oPage.GetDataSetGroupBy(DataBaseHelper.typedb.zlyjgisdb, strSql, strCol , strWhere, strOb, strGb, page, rowpage, out toalCount).Tables[0]; // 返回Sql语句,目的是绑定excel。 string strSqlT = ""; string strSqlP = ""; strSqlP = oPage.GetListSQLS(strSql, strCol, 0, 0, strOb, strWhere, strOb, out strSqlT); //绑定数据到list。 oList = opp.FillModel(dt); // GP 服务 废气 王岩松 2016年6月23日17:44:43 string stringGp = " SELECT g.city,gscode, g.name, G.x, g.y ORDER BY g.city "; if (dt != null && dt.Rows.Count > 0) { oList = opp.FillModel(dt); var oLists = from tGride in oList select new { tGride.REGIONCODE, tGride.REGIONNAME, tGride.COMPANYNAME, tGride.ENERGYNUMBER, }; result.Add("rows", oLists); result.Add("total", toalCount); result.Add("cols", cm); result.Add("sql", strSqlT); result.Add("GP", stringGp); } else { result.Add("total", 0); result.Add("rows", oList); result.Add("cols", cm); result.Add("sql", strSqlT); result.Add("GP", stringGp); } return(result); } catch (Exception ex) { JNJC.Utility.Help.HelpUtils.ErrorLog("初始用能权-加载GRID数据错误! ||" + ex.ToString()); return(result); } } }