/// <summary> /// 获取结构 /// </summary> /// <param name="context"></param> /// <returns></returns> public JObject GetSchema(HttpContext context) { JObject obj = new JObject(); try { YZRequest request = new YZRequest(context); int connectId = request.GetInt32("connectId"); string sheetName = request.GetString("sheetName"); string titleIndex = request.GetString("titleIndex"); string query = string.Format("{0}|{1}", sheetName, titleIndex); ConnectInfo connectInfo = ConnectInfoManager.GetConnectInfo(connectId); ExcelLogin login = JObject.Parse(connectInfo.connectStr).ToObject <ExcelLogin>(); ISourceHandle handle = new ExcelHandle(login); QueryDepictInfo info = handle.GetQueryInfo(sheetName); info.query = query; info = handle.GetQueryInfo(info); obj["schema"] = GetColumnJson(info); obj[YZJsonProperty.success] = true; return(obj); } catch (Exception) { obj[YZJsonProperty.success] = false; obj[YZJsonProperty.errorMessage] = "没有可供查询的表或视图!"; return(obj); } }
/// <summary> /// 返回参数 /// </summary> /// <param name="context"></param> /// <returns></returns> public JObject GetParameter(HttpContext context) { JObject obj = new JObject(); try { YZRequest request = new YZRequest(context); //获取数据连接信息 int connectId = request.GetInt32("connectId"); ConnectInfo con = ConnectInfoManager.GetConnectInfo(connectId); SapLogin login = JObject.Parse(con.connectStr).ToObject <SapLogin>(); ISourceHandle handle = new SapHandle(login); //接口名称 string rfcName = request.GetString("rfcName"); QueryDepictInfo info = handle.GetQueryInfo(rfcName); //序列化 JArray parameterArray = new JArray(); obj["parameter"] = parameterArray; if (info.parameter != null && info.parameter.Count > 0) { if (CommonHandle.IsParameterNoType(info.parameter)) { obj[YZJsonProperty.success] = false; obj[YZJsonProperty.errorMessage] = "输入参数中有不符合规定的类型,无法进行配置!"; return(obj); } foreach (ColumnInfo item in info.parameter) { parameterArray.Add(BizCommon.GetColumnJson(item)); } } //输出参数 JArray schemaArray = new JArray(); obj["schema"] = schemaArray; if (info.schema != null && info.schema.Count > 0) { foreach (var item in info.schema) { schemaArray.Add(BizCommon.GetColumnJson(item)); } } else { obj[YZJsonProperty.success] = false; obj[YZJsonProperty.errorMessage] = "该接口没有返回值,不可调用!"; return(obj); } obj[YZJsonProperty.success] = true; return(obj); } catch (Exception ex) { obj[YZJsonProperty.success] = false; obj[YZJsonProperty.errorMessage] = ex.ToString(); return(obj); } }
/// <summary> /// 返回数据连接信息 /// </summary> /// <param name="context"></param> /// <returns></returns> public JObject EditConnect(HttpContext context) { JObject obj = new JObject(); YZRequest request = new YZRequest(context); ConnectInfo info = ConnectInfoManager.GetConnectInfo(request.GetInt32("connectId", -1)); obj = JObject.FromObject(info); obj["connectStr"] = JObject.Parse(obj["connectStr"].ToString()); return(obj); }
/// <summary> /// 测试数据连接 /// </summary> /// <param name="context"></param> /// <returns></returns> public JObject TestConnect(HttpContext context) { JObject obj = new JObject(); YZRequest request = new YZRequest(context); bool flag = false; string message = null; ConnectInfo entity = ConnectInfoManager.GetConnectInfo(request.GetInt32("connectId", -1)); switch (entity.connectType) { case SourceTypeEnum.Excel: flag = CommonHandle.IsExcelJoin(JObject.Parse(entity.connectStr).ToObject <ExcelLogin>(), out message); break; case SourceTypeEnum.Oracle: flag = CommonHandle.IsOracleJoin(JObject.Parse(entity.connectStr).ToObject <OracleLogin>(), out message); break; case SourceTypeEnum.SAP: flag = CommonHandle.IsSapJoin(JObject.Parse(entity.connectStr).ToObject <SapLogin>(), out message); break; case SourceTypeEnum.SqlServer: flag = CommonHandle.IsSqlServerJoin(JObject.Parse(entity.connectStr).ToObject <SqlServerLogin>(), out message); break; case SourceTypeEnum.WebService: flag = CommonHandle.IsWebServiceJoin(JObject.Parse(entity.connectStr).ToObject <WebServiceLogin>(), out message); break; default: break; } obj[YZJsonProperty.success] = flag; obj[YZJsonProperty.errorMessage] = flag ? "连接成功!" : String.Format("连接失败!原因:<br/>{0}", message); return(obj); }
/// <summary> /// 保存数据源 /// </summary> /// <param name="context"></param> /// <returns></returns> public JObject SaveSource(HttpContext context) { JObject obj = new JObject(); try { YZRequest request = new YZRequest(context); //操作类型 string joinType = request.GetString("joinType"); string sourceName = request.GetString("sourceName"); string caption = request.GetString("caption", ""); int connectId = request.GetInt32("connectId"); int sourceId = request.GetInt32("sourceId", 0); string queryStr = request.GetString("queryStr"); //判断是否重名 bool isNameOnly = SourceInfoManager.IsSourceNameRepeat(sourceName, SourceTypeEnum.Oracle, sourceId); if (isNameOnly) { obj[YZJsonProperty.success] = false; obj[YZJsonProperty.errorMessage] = "已存在同名的数据源,请重命名数据源名称!"; return(obj); } //执行 ConnectInfo con = ConnectInfoManager.GetConnectInfo(connectId); OracleLogin login = JObject.Parse(con.connectStr).ToObject <OracleLogin>(); ISourceHandle handle = new OracleHandle(login); QueryDepictInfo info = new QueryDepictInfo(); info.query = queryStr; StreamReader reader = new StreamReader(context.Request.InputStream); using (JsonTextReader streamReader = new JsonTextReader(reader)) { //获取参数配置 JsonSerializer serializer = new JsonSerializer(); JArray array = serializer.Deserialize(streamReader) as JArray; JObject jo = array[0] as JObject; JArray parameterArray = jo["parameter"] as JArray; if (parameterArray != null && parameterArray.Count > 0) { List <ColumnInfo> paramList = new List <ColumnInfo>(); foreach (var item in parameterArray) { ColumnInfo column = new ColumnInfo(); column.columnName = item["columnName"].ToString(); column.rename = item["rename"].ToString(); column.isShow = item["isShow"].ToString().ToUpper().Equals("TRUE") ? 1 : 0; column.defaultValue = item["defaultValue"].ToString(); paramList.Add(column); } info.parameter = paramList; } JArray schemaArray = jo["schema"] as JArray; if (schemaArray != null && schemaArray.Count > 0) { List <ColumnInfo> schemaList = new List <ColumnInfo>(); foreach (var item in schemaArray) { ColumnInfo column = new ColumnInfo(); column.columnName = item["columnName"].ToString(); column.rename = item["rename"].ToString(); column.isShow = item["isShow"].ToString().ToUpper().Equals("TRUE") ? 1 : 0; column.defaultValue = item["defaultValue"].ToString(); schemaList.Add(column); } info.schema = schemaList; } } if (joinType.ToUpper().Equals("THEEDIT")) { SourceInfo sourceInfo = SourceInfoManager.GetSourceInfo(sourceId); sourceInfo.sourceName = sourceName; sourceInfo.caption = caption; sourceInfo.updateTime = DateTime.Now; sourceInfo.sourceStr = JObject.FromObject(info).ToString(); SourceInfoManager.EditSource(sourceInfo); } else { SourceInfo sourceInfo = new SourceInfo(); sourceInfo.sourceName = sourceName; sourceInfo.sourceType = SourceTypeEnum.Oracle; sourceInfo.connectInfo = ConnectInfoManager.GetConnectInfo(connectId); sourceInfo.caption = caption; sourceInfo.isvalid = true; sourceInfo.createTime = DateTime.Now; sourceInfo.updateTime = DateTime.Now; sourceInfo.sourceStr = JObject.FromObject(info).ToString(); SourceInfoManager.AddSource(sourceInfo); } obj[YZJsonProperty.success] = true; obj[YZJsonProperty.errorMessage] = "保存成功"; return(obj); } catch (Exception ex) { obj[YZJsonProperty.success] = false; obj[YZJsonProperty.errorMessage] = ex.Message.ToString(); return(obj); } }
/// <summary> /// 连接到服务器 /// </summary> /// <param name="context"></param> /// <returns></returns> public JObject JoinService(HttpContext context) { JObject obj = new JObject(); try { List <ElementInfo> structList; OracleLogin login; ISourceHandle handle; YZRequest request = new YZRequest(context); string joinType = request.GetString("jointype"); if (joinType.ToUpper().Equals("THEADD")) { //新增模式 int connectId = request.GetInt32("connectId"); ConnectInfo connectInfo = ConnectInfoManager.GetConnectInfo(connectId); //生成连接对象 login = JObject.Parse(connectInfo.connectStr).ToObject <OracleLogin>(); //实例化连接对象 handle = new OracleHandle(login); //获取接口集合 structList = handle.GetElementList(); } else { //编辑模式 int sourceId = request.GetInt32("sourceId"); //模糊查询表或视图的名称 string name = request.GetString("name", ""); SourceInfo sourceInfo = SourceInfoManager.GetSourceInfo(sourceId); //生成连接对象 login = JObject.Parse(sourceInfo.connectInfo.connectStr).ToObject <OracleLogin>(); //实例化连接对象 handle = new OracleHandle(login); //获取接口集合 structList = handle.GetElementList(name); } if (structList != null && structList.Count > 0) { List <ElementInfo> tableList = structList.Where(n => n.elementType.Trim().ToUpper().Equals("U")).ToList(); JArray tableArray = new JArray(); foreach (ElementInfo item in tableList) { JObject jo = new JObject(); jo["name"] = item.elementName; tableArray.Add(jo); } List <ElementInfo> viewList = structList.Where(n => n.elementType.Trim().ToUpper().Equals("V")).ToList(); JArray viewArray = new JArray(); foreach (ElementInfo item in viewList) { JObject jo = new JObject(); jo["name"] = item.elementName; viewArray.Add(jo); } obj["table"] = tableArray; obj["view"] = viewArray; obj[YZJsonProperty.success] = true; return(obj); } else { obj[YZJsonProperty.success] = false; obj[YZJsonProperty.errorMessage] = "没有可供查询的表或视图!"; return(obj); } } catch (Exception ex) { obj[YZJsonProperty.success] = false; obj[YZJsonProperty.errorMessage] = ex.ToString(); return(obj); } }
/// <summary> /// 执行sql语句返回值 /// </summary> /// <param name="context"></param> /// <returns></returns> public JObject RunSql(HttpContext context) { JObject obj = new JObject(); try { YZRequest request = new YZRequest(context); string queryStr = request.GetString("queryStr"); QueryDepictInfo info = new QueryDepictInfo(); info.query = queryStr; StreamReader reader = new StreamReader(context.Request.InputStream); using (JsonTextReader streamReader = new JsonTextReader(reader)) { //获取参数配置 JsonSerializer serializer = new JsonSerializer(); JArray array = serializer.Deserialize(streamReader) as JArray; if (array != null && array.Count > 0) { List <ColumnInfo> paramList = new List <ColumnInfo>(); foreach (var item in array) { ColumnInfo column = new ColumnInfo(); column.columnName = item["columnName"].ToString(); column.rename = item["rename"].ToString(); column.isShow = item["isShow"].ToString().ToUpper().Equals("TRUE") ? 1 : 0; column.defaultValue = item["defaultValue"].ToString(); paramList.Add(column); } info.parameter = paramList; } //执行 int connectId = request.GetInt32("connectId"); ConnectInfo con = ConnectInfoManager.GetConnectInfo(connectId); OracleLogin login = JObject.Parse(con.connectStr).ToObject <OracleLogin>(); ISourceHandle handle = new OracleHandle(login); DataTable dt = handle.GetResult(info); if (dt == null) { obj[YZJsonProperty.success] = false; obj[YZJsonProperty.errorMessage] = "没有可供查询的结果!"; return(obj); } //保存输出参数 List <ColumnInfo> schemaList = new List <ColumnInfo>(); foreach (DataColumn item in dt.Columns) { schemaList.Add(new ColumnInfo() { columnName = item.ColumnName, rename = item.ColumnName, columnType = item.DataType.Name, isShow = 1 }); } info.schema = schemaList; JArray results = new JArray(); if (dt.Rows.Count > 0) { foreach (DataRow row in dt.Rows) { JObject item = new JObject(); foreach (DataColumn column in dt.Columns) { item[column.ColumnName] = row[column.ColumnName].ToString(); } results.Add(item); } obj["children"] = results; } else { JObject item = new JObject(); foreach (DataColumn column in dt.Columns) { item[column.ColumnName] = ""; } results.Add(item); obj["children"] = results; } obj["children"] = results; //执行数据 obj[YZJsonProperty.success] = true; return(obj); } } catch (Exception ex) { obj[YZJsonProperty.success] = false; obj[YZJsonProperty.errorMessage] = ex.Message.ToString(); return(obj); } }
/// <summary> /// 保存数据源 /// </summary> /// <param name="context"></param> /// <returns></returns> public JObject SaveSource(HttpContext context) { JObject obj = new JObject(); YZRequest request = new YZRequest(context); try { //操作类型 string joinType = request.GetString("joinType"); string sourceName = request.GetString("sourceName"); string caption = request.GetString("caption", ""); int connectId = request.GetInt32("connectId"); int sourceId = request.GetInt32("sourceId", 0); //判断是否重名 bool isNameOnly = SourceInfoManager.IsSourceNameRepeat(sourceName, SourceTypeEnum.SAP, sourceId); if (isNameOnly) { obj[YZJsonProperty.success] = false; obj[YZJsonProperty.errorMessage] = "已存在同名的数据源,请重命名数据源名称!"; return(obj); } ConnectInfo con = ConnectInfoManager.GetConnectInfo(connectId); SapLogin login = JObject.Parse(con.connectStr).ToObject <SapLogin>(); ISourceHandle handle = new SapHandle(login); //获取接口信息 string rfcName = request.GetString("rfcName"); QueryDepictInfo info = handle.GetQueryInfo(rfcName); StreamReader reader = new StreamReader(context.Request.InputStream); using (JsonTextReader streamReader = new JsonTextReader(reader)) { //获取参数配置 JsonSerializer serializer = new JsonSerializer(); JArray array = serializer.Deserialize(streamReader) as JArray; JObject jo = array[0] as JObject; JArray parameterArray = jo["parameter"] as JArray; //清空输入参数原始值 CommonHandle.ClearParameter(info.parameter); //配置输入参数值 foreach (JObject item in parameterArray) { ColumnInfo column = info.parameter.First(n => n.columnName == item["columnName"].ToString()); SetParameterValue(column, item); } JArray schemaArray = jo["schema"] as JArray; //清空输出参数原始值 CommonHandle.ClearParameter(info.schema); //配置输出参数值 foreach (JObject item in schemaArray) { ColumnInfo columnInfo = info.schema.First(n => n.columnName == item["columnName"].ToString()); SetOutputInfo(columnInfo, item); } } if (joinType.ToUpper().Equals("THEEDIT")) { SourceInfo sourceInfo = SourceInfoManager.GetSourceInfo(sourceId); sourceInfo.sourceName = sourceName; sourceInfo.caption = caption; sourceInfo.updateTime = DateTime.Now; sourceInfo.sourceStr = JObject.FromObject(info).ToString(); SourceInfoManager.EditSource(sourceInfo); } else { SourceInfo sourceInfo = new SourceInfo(); sourceInfo.sourceName = sourceName; sourceInfo.sourceType = SourceTypeEnum.SAP; sourceInfo.connectInfo = ConnectInfoManager.GetConnectInfo(connectId); sourceInfo.caption = caption; sourceInfo.isvalid = true; sourceInfo.createTime = DateTime.Now; sourceInfo.updateTime = DateTime.Now; sourceInfo.sourceStr = JObject.FromObject(info).ToString(); SourceInfoManager.AddSource(sourceInfo); } obj[YZJsonProperty.success] = true; obj[YZJsonProperty.errorMessage] = "保存成功"; return(obj); } catch (Exception ex) { obj[YZJsonProperty.success] = false; obj[YZJsonProperty.errorMessage] = ex.ToString(); return(obj); } }
/// <summary> /// 连接服务 /// </summary> /// <param name="context"></param> /// <returns></returns> public JObject JoinService(HttpContext context) { JObject obj = new JObject(); try { List <ElementInfo> structList; SapLogin login; ISourceHandle handle; YZRequest request = new YZRequest(context); string joinType = request.GetString("jointype"); if (joinType.ToUpper().Equals("THEADD")) { //新增模式 int connectId = request.GetInt32("connectId"); string rfcName = request.GetString("rfcName", ""); ConnectInfo connectInfo = ConnectInfoManager.GetConnectInfo(connectId); //生成连接对象 login = JObject.Parse(connectInfo.connectStr).ToObject <SapLogin>(); //实例化连接对象 handle = new SapHandle(login); structList = handle.GetElementList(rfcName); } else { //编辑模式 int sourceId = request.GetInt32("sourceId"); string rfcName = request.GetString("rfcName", ""); SourceInfo sourceInfo = SourceInfoManager.GetSourceInfo(sourceId); //生成连接对象 login = JObject.Parse(sourceInfo.connectInfo.connectStr).ToObject <SapLogin>(); //实例化连接对象 handle = new SapHandle(login); //获取接口集合 structList = handle.GetElementList(rfcName); } if (structList != null && structList.Count > 0) { JArray funcArray = new JArray(); foreach (var item in structList) { JObject jo = new JObject(); jo["name"] = item.elementName; funcArray.Add(jo); } obj["funcarray"] = funcArray; obj[YZJsonProperty.success] = true; } else { obj[YZJsonProperty.success] = false; obj[YZJsonProperty.errorMessage] = "没有可供调用的接口!"; return(obj); } return(obj); } catch (Exception ex) { obj[YZJsonProperty.success] = false; obj[YZJsonProperty.errorMessage] = ex.Message; return(obj); } }
/// <summary> /// 返回运行结果 /// </summary> /// <param name="context"></param> /// <returns></returns> public JObject GetResult(HttpContext context) { JObject obj = new JObject(); try { YZRequest request = new YZRequest(context); //获取数据连接信息 int connectId = request.GetInt32("connectId"); string rfcName = request.GetString("rfcName"); ConnectInfo con = ConnectInfoManager.GetConnectInfo(connectId); SapLogin login = JObject.Parse(con.connectStr).ToObject <SapLogin>(); ISourceHandle handle = new SapHandle(login); QueryDepictInfo info = handle.GetQueryInfo(rfcName); StreamReader reader = new StreamReader(context.Request.InputStream); using (JsonTextReader streamReader = new JsonTextReader(reader)) { //获取参数配置 JsonSerializer serializer = new JsonSerializer(); JArray array = serializer.Deserialize(streamReader) as JArray; JObject jo = array[0] as JObject; JArray parameterArray = jo["parameter"] as JArray; //清空输入参数原始值 CommonHandle.ClearParameter(info.parameter); //配置输入参数值 foreach (JObject item in parameterArray) { ColumnInfo column = info.parameter.First(n => n.columnName == item["columnName"].ToString()); SetParameterValue(column, item); } JArray schemaArray = jo["schema"] as JArray; //清空输出参数原始值 CommonHandle.ClearParameter(info.schema); //配置输出参数值 foreach (JObject item in schemaArray) { ColumnInfo columnInfo = info.schema.First(n => n.columnName == item["columnName"].ToString()); SetOutputInfo(columnInfo, item); } } //获取运行结果 DataTable dt = handle.GetResult(info); JArray results = new JArray(); if (dt.Rows.Count > 0) { foreach (DataRow row in dt.Rows) { JObject item = new JObject(); foreach (DataColumn column in dt.Columns) { item[column.ColumnName] = row[column.ColumnName].ToString(); } results.Add(item); } } else { JObject item = new JObject(); foreach (DataColumn column in dt.Columns) { item[column.ColumnName] = ""; } results.Add(item); } obj["children"] = results; //执行数据 obj[YZJsonProperty.success] = true; return(obj); } catch (Exception ex) { obj[YZJsonProperty.success] = false; obj[YZJsonProperty.errorMessage] = ex.Message; return(obj); } }
/// <summary> /// 保存excel数据连接 /// </summary> /// <param name="context"></param> /// <returns></returns> public JObject SaveExcelConnect(HttpContext context) { JObject obj = new JObject(); YZRequest request = new YZRequest(context); try { //获取操作模式 string joinType = request.GetString("joinType"); //获取连接数据 ExcelLogin login = new ExcelLogin(); login.excelPath = request.GetString("excelPath"); string message = null; if (!CommonHandle.IsExcelJoin(login, out message)) { obj[YZJsonProperty.success] = false; obj[YZJsonProperty.errorMessage] = "服务无法连接!"; return(obj); } if (joinType.ToUpper().Equals("THEEDIT")) { //编辑模式 ConnectInfo info = ConnectInfoManager.GetConnectInfo(request.GetInt32("connectId", -1)); info.caption = request.GetString("caption", ""); info.connectStr = JObject.FromObject(login).ToString(); info.updateTime = DateTime.Now; info.isvalid = true; //更新 ConnectInfoManager.EditConnect(info); obj["connectId"] = info.connectId; } else { //新增模式 if (ConnectInfoManager.IsConnectRepeat(request.GetString("connectName", ""), SourceTypeEnum.Excel)) { obj[YZJsonProperty.success] = false; obj[YZJsonProperty.errorMessage] = "已存在同名的数据源"; return(obj); } ConnectInfo info = new ConnectInfo(); info.connectName = request.GetString("connectName", ""); info.connectType = SourceTypeEnum.Excel; info.caption = request.GetString("caption", ""); info.connectStr = JObject.FromObject(login).ToString(); info.createTime = DateTime.Now; info.updateTime = DateTime.Now; info.isvalid = true; //新增 ConnectInfoManager.AddConnect(info); obj["connectId"] = ConnectInfoManager.GetConnectInfo(SourceTypeEnum.Excel, info.connectName).connectId; } obj[YZJsonProperty.success] = true; obj[YZJsonProperty.errorMessage] = "数据连接保存成功"; return(obj); } catch (Exception ex) { string msg = string.Format("保存失败:{0}", ex.ToString()); obj[YZJsonProperty.success] = false; obj[YZJsonProperty.errorMessage] = msg; return(obj); } }
/// <summary> /// 返回输出参数 /// </summary> /// <param name="context"></param> /// <returns></returns> public JObject GetSchema(HttpContext context) { JObject obj = new JObject(); try { YZRequest request = new YZRequest(context); //获取数据连接信息 int connectId = request.GetInt32("connectId"); ConnectInfo con = ConnectInfoManager.GetConnectInfo(connectId); WebServiceLogin login = JObject.Parse(con.connectStr).ToObject <WebServiceLogin>(); ISourceHandle handle = new WebServiceHandle(login); //获取接口信息 string functionName = request.GetString("functionName"); QueryDepictInfo info = handle.GetQueryInfo(functionName); //获取输入值 StreamReader reader = new StreamReader(context.Request.InputStream); using (JsonTextReader streamReader = new JsonTextReader(reader)) { //获取参数配置 JsonSerializer serializer = new JsonSerializer(); JArray array = serializer.Deserialize(streamReader) as JArray; //清空输入参数原始值 CommonHandle.ClearParameter(info.parameter); //更新输入参数 foreach (JObject item in array) { ColumnInfo column = info.parameter.First(n => n.columnName == item["columnName"].ToString()); SetParameterValue(column, item); } } //如果输入参数无法识别 if (CommonHandle.IsSchemaNoType(info.schema)) { //当输出参数无法识别通过访问再次进行解析 info = handle.GetQueryInfo(info); } //返回输出参数结构 JArray schemaArray = new JArray(); obj["schema"] = schemaArray; if (info.schema != null && info.schema.Count > 0) { foreach (var item in info.schema) { schemaArray.Add(BizCommon.GetColumnJson(item)); } obj[YZJsonProperty.success] = true; } else { obj[YZJsonProperty.success] = false; obj[YZJsonProperty.errorMessage] = "该接口没有返回值,不可调用!"; } return(obj); } catch (Exception ex) { obj[YZJsonProperty.success] = false; obj[YZJsonProperty.errorMessage] = ex.ToString(); return(obj); } }
/// <summary> /// 连接服务 /// </summary> /// <param name="context"></param> /// <returns></returns> public JObject JoinService(HttpContext context) { JObject obj = new JObject(); try { List <ElementInfo> structList; ExcelLogin login; ISourceHandle handle; QueryDepictInfo info; YZRequest request = new YZRequest(context); string joinType = request.GetString("jointype"); if (joinType.ToUpper().Equals("THEADD")) { //新增模式 int connectId = request.GetInt32("connectId"); ConnectInfo connectInfo = ConnectInfoManager.GetConnectInfo(connectId); //生成连接对象 login = JObject.Parse(connectInfo.connectStr).ToObject <ExcelLogin>(); //实例化连接对象 handle = new ExcelHandle(login); //获取接口集合 structList = handle.GetElementList(); info = new QueryDepictInfo(); } else { //编辑模式 int sourceId = request.GetInt32("sourceId"); //模糊查询表或视图的名称 string sheetName = request.GetString("sheetName", ""); SourceInfo sourceInfo = SourceInfoManager.GetSourceInfo(sourceId); //生成连接对象 login = JObject.Parse(sourceInfo.connectInfo.connectStr).ToObject <ExcelLogin>(); //实例化连接对象 handle = new ExcelHandle(login); //获取接口集合 structList = handle.GetElementList(sheetName); info = JObject.Parse(sourceInfo.sourceStr).ToObject <QueryDepictInfo>(); } //获取接口集合 if (structList != null && structList.Count > 0) { JArray sheetArray = new JArray(); foreach (var item in structList) { JObject jo = new JObject(); jo["name"] = item.elementName; sheetArray.Add(jo); } obj["sheetArray"] = sheetArray; obj["schema"] = GetColumnJson(info); obj[YZJsonProperty.success] = true; } else { obj[YZJsonProperty.success] = false; obj[YZJsonProperty.errorMessage] = "没有可供查询的sheet表!"; return(obj); } return(obj); } catch (Exception ex) { obj[YZJsonProperty.success] = false; obj[YZJsonProperty.errorMessage] = ex.ToString(); return(obj); } }
/// <summary> /// 保存数据源 /// </summary> /// <param name="context"></param> /// <returns></returns> public JObject SaveSource(HttpContext context) { JObject obj = new JObject(); try { YZRequest request = new YZRequest(context); string joinType = request.GetString("joinType"); string sourceName = request.GetString("sourceName"); string caption = request.GetString("caption", ""); int connectId = request.GetInt32("connectId"); int sourceId = request.GetInt32("sourceId", 0); string sheetName = request.GetString("sheetName"); string titleIndex = request.GetString("titleIndex"); string query = string.Format("{0}|{1}", sheetName, titleIndex); //判断是否重名 bool isNameOnly = SourceInfoManager.IsSourceNameRepeat(sourceName, SourceTypeEnum.Excel, sourceId); if (isNameOnly) { obj[YZJsonProperty.success] = false; obj[YZJsonProperty.errorMessage] = "已存在同名的数据源,请重命名数据源名称!"; return(obj); } ConnectInfo connectInfo = ConnectInfoManager.GetConnectInfo(connectId); ExcelLogin login = JObject.Parse(connectInfo.connectStr).ToObject <ExcelLogin>(); ISourceHandle handle = new ExcelHandle(login); QueryDepictInfo info = handle.GetQueryInfo(sheetName); info.query = query; info = handle.GetQueryInfo(info); StreamReader reader = new StreamReader(context.Request.InputStream); using (JsonTextReader streamReader = new JsonTextReader(reader)) { //获取参数配置 JsonSerializer serializer = new JsonSerializer(); JArray array = serializer.Deserialize(streamReader) as JArray; List <ColumnInfo> param = new List <ColumnInfo>(); foreach (var item in array) { if (item["inputIsShow"].ToString().ToUpper().Equals("TRUE")) { ColumnInfo column = new ColumnInfo(); column.columnName = item["columnName"].ToString(); column.columnType = item["columnType"].ToString(); column.defaultValue = item["defaultValue"].ToString(); column.isShow = 1; column.rename = item["inputRename"].ToString(); param.Add(column); } ColumnInfo schemaColumn = info.schema.First(n => n.columnName == item["columnName"].ToString()); if (item["outputIsShow"].ToString().ToUpper().Equals("TRUE")) { schemaColumn.rename = item["outputRename"].ToString(); schemaColumn.isShow = 1; } else { schemaColumn.isShow = 0; } } info.parameter = param; } if (joinType.ToUpper().Equals("THEEDIT")) { SourceInfo sourceInfo = SourceInfoManager.GetSourceInfo(sourceId); sourceInfo.sourceName = sourceName; sourceInfo.caption = caption; sourceInfo.updateTime = DateTime.Now; sourceInfo.sourceStr = JObject.FromObject(info).ToString(); SourceInfoManager.EditSource(sourceInfo); } else { SourceInfo sourceInfo = new SourceInfo(); sourceInfo.sourceName = sourceName; sourceInfo.sourceType = SourceTypeEnum.Excel; sourceInfo.connectInfo = ConnectInfoManager.GetConnectInfo(connectId); sourceInfo.caption = caption; sourceInfo.isvalid = true; sourceInfo.createTime = DateTime.Now; sourceInfo.updateTime = DateTime.Now; sourceInfo.sourceStr = JObject.FromObject(info).ToString(); SourceInfoManager.AddSource(sourceInfo); } obj[YZJsonProperty.success] = true; obj[YZJsonProperty.errorMessage] = "保存成功"; return(obj); } catch (Exception ex) { obj[YZJsonProperty.success] = false; obj[YZJsonProperty.errorMessage] = ex.Message.ToString(); return(obj); } }
/// <summary> /// 返回结果 /// </summary> /// <param name="context"></param> /// <returns></returns> public JObject GetResult(HttpContext context) { JObject obj = new JObject(); try { YZRequest request = new YZRequest(context); int connectId = request.GetInt32("connectId"); string sheetName = request.GetString("sheetName"); string titleIndex = request.GetString("titleIndex"); string query = string.Format("{0}|{1}", sheetName, titleIndex); ConnectInfo connectInfo = ConnectInfoManager.GetConnectInfo(connectId); ExcelLogin login = JObject.Parse(connectInfo.connectStr).ToObject <ExcelLogin>(); ISourceHandle handle = new ExcelHandle(login); QueryDepictInfo info = handle.GetQueryInfo(sheetName); info.query = query; info = handle.GetQueryInfo(info); StreamReader reader = new StreamReader(context.Request.InputStream); using (JsonTextReader streamReader = new JsonTextReader(reader)) { //获取参数配置 JsonSerializer serializer = new JsonSerializer(); JArray array = serializer.Deserialize(streamReader) as JArray; List <ColumnInfo> param = new List <ColumnInfo>(); foreach (var item in array) { if (item["inputIsShow"].ToString().ToUpper().Equals("TRUE")) { ColumnInfo column = new ColumnInfo(); column.columnName = item["columnName"].ToString(); column.columnType = item["columnType"].ToString(); column.defaultValue = item["defaultValue"].ToString(); column.isShow = 1; column.rename = item["inputRename"].ToString(); param.Add(column); } ColumnInfo schemaColumn = info.schema.First(n => n.columnName == item["columnName"].ToString()); if (item["outputIsShow"].ToString().ToUpper().Equals("TRUE")) { schemaColumn.rename = item["outputRename"].ToString(); schemaColumn.isShow = 1; } else { schemaColumn.isShow = 0; } } info.parameter = param; } //访问 DataTable dt = handle.GetResult(info); JArray results = new JArray(); if (dt.Rows.Count > 0) { foreach (DataRow row in dt.Rows) { JObject item = new JObject(); foreach (DataColumn column in dt.Columns) { item[column.ColumnName] = row[column.ColumnName].ToString(); } results.Add(item); } } else { JObject item = new JObject(); foreach (DataColumn column in dt.Columns) { item[column.ColumnName] = ""; } results.Add(item); } obj["children"] = results; //执行数据 obj[YZJsonProperty.success] = true; return(obj); } catch (Exception ex) { obj[YZJsonProperty.success] = false; obj[YZJsonProperty.errorMessage] = ex.ToString(); return(obj); } }