public IDictionary <string, object> ParamsPage(string apiIdCode, int pageIndex, int pageSize) { IDbHelper db = this._dbContext.Use("config_database"); db.AddInputParameter("ApiIdCode", apiIdCode); string sql = "select count(1) as v_count from ApiParams where ApiIdCode=@ApiIdCode"; long count = (long)db.ExecuteScalar(sql); if (pageSize <= 0) { pageSize = 10; } int maxPage = (int)Math.Ceiling((decimal)count / pageSize); if (pageIndex > maxPage) { pageIndex = maxPage; } if (pageIndex <= 0) { pageIndex = 1; } db.AddInputParameter("ApiIdCode", apiIdCode); db.AddInputParameter("Offset", (pageIndex - 1) * pageSize); db.AddInputParameter("Limit", pageSize); var list = db.ExecuteQuery(@"select t.*,case t.ParamType when 0 then 'String' when 1 then 'Integer' when 2 then 'Long' when 3 then 'Double' when 4 then 'Float' when 5 then 'Decimal' when 6 then 'Boolean' when 7 then 'Date' when 8 then 'DateTime' when 9 then 'ULong' when 10 then 'Key/Value' when 11 then 'List' when 12 then 'File' end as ParamTypeText from ApiParams t where ApiIdCode=@ApiIdCode limit @Limit offset @Offset"); return(new Dictionary <string, object> { { "TotalRows", count }, { "TotalPage", maxPage }, { "PageSize", pageSize }, { "PageIndex", pageIndex }, { "List", list } }); }
public void DeleteParam(string apiIdCode, int isFixed) { IDbHelper db = this._dbContext.Use("config_database"); db.AddInputParameter("ApiIdCode", apiIdCode); db.AddInputParameter("IsFixed", isFixed); db.ExecuteNonQuery("Delete From ApiParams where ApiIdCode=@ApiIdCode And IsFixed=@IsFixed"); }
public IDictionary <string, object> ApiConfigPage(int pageIndex, int pageSize, IDictionary <string, object> searchKeys) { StringBuilder whereSql = new StringBuilder(); IDictionary <string, object> searchParams = new Dictionary <string, object>(); foreach (var pair in searchKeys) { if (whereSql.Length > 0) { whereSql.Append(" and "); } whereSql.Append(pair.Key + " like @" + pair.Key); searchParams[pair.Key] = "%" + pair.Value + "%"; } IDbHelper db = this._dbContext.Use("config_database"); db.AddInputParameter(searchParams); string sql = "select count(1) as v_count from ApiConfig where 1=1 " + (whereSql.Length > 0 ? " and " + whereSql : ""); long count = (long)db.ExecuteScalar(sql); if (pageSize <= 0) { pageSize = 10; } int maxPage = (int)Math.Ceiling((decimal)count / pageSize); if (pageIndex > maxPage) { pageIndex = maxPage; } if (pageIndex <= 0) { pageIndex = 1; } db.AddInputParameter(searchParams); db.AddInputParameter("Offset", (pageIndex - 1) * pageSize); db.AddInputParameter("Limit", pageSize); var list = db.ExecuteQuery(@"select t.*,case when t.Status=1 then '启用' else '停用' end StatusText,case when t.ApiKind=1 then '对内接口' else '对外接口' end as ApiKindText,CASE WHEN t.CodeKind = 1 THEN '分页' WHEN t.CodeKind = 2 THEN '结果集-列表' WHEN t.CodeKind = 3 THEN '结果集-列表' WHEN t.CodeKind = 4 THEN '脚本执行结果' ELSE '其它' END CodeKindText from ApiConfig t where 1=1 " + (whereSql.Length > 0 ? " and " + whereSql : "") + " limit @Limit offset @Offset"); return(new Dictionary <string, object> { { "TotalRows", count }, { "TotalPage", maxPage }, { "PageSize", pageSize }, { "PageIndex", pageIndex }, { "List", list } }); }
public IDictionary <string, object> GetGroupRow(long groupId) { IDbHelper db = this._dbContext.Use("config_database"); db.AddInputParameter("GroupId", groupId); return(db.SelectRow("select * from ApiGroup where GroupId=@GroupId")); }
public void DeleteGroup(string groupId) { IDbHelper db = this._dbContext.Use("config_database"); db.AddInputParameter("GroupId", groupId); db.ExecuteNonQuery("Delete From ApiGroup where GroupId=@GroupId"); }
public void DeleteParam(long paramId) { IDbHelper db = this._dbContext.Use("config_database"); db.AddInputParameter("ParamId", paramId); db.ExecuteNonQuery("Delete From ApiParams where ParamId=@ParamId"); }
public IDictionary <string, object> GetParamRow(long paramId) { IDbHelper db = this._dbContext.Use("config_database"); db.AddInputParameter("ParamId", paramId); return(db.SelectRow("select * from ApiParams where ParamId=@ParamId")); }
public void DeleteApi(string apiIdCode) { IDbHelper db = this._dbContext.Use("config_database"); db.AddInputParameter("ApiIdCode", apiIdCode); db.ExecuteNonQuery("Delete From ApiConfig where ApiIdCode=@ApiIdCode"); }
public IList <IDictionary <string, object> > GetApiParams(string code) { IDbHelper db = this._dbContext.Use("config_database"); db.AddInputParameter("ApiIdCode", code); //return db.ExecuteQuery("select t.*,case t.ParamType when 0 then 'String' when 1 then 'Integer' when 2 then 'Long' when 3 then 'Boolean' when 4 then 'Date' when 5 then 'DateTime' when 6 then 'Key/Value' when 7 then 'List' end as ParamTypeText from ApiParams t where ApiIdCode=@ApiIdCode"); return(db.ExecuteQuery("select t.*,case t.ParamType when 0 then 'String' when 1 then 'Integer' when 2 then 'Long' when 3 then 'Double' when 4 then 'Float' when 5 then 'Decimal' when 6 then 'Boolean' when 7 then 'Date' when 8 then 'DateTime' when 9 then 'ULong' when 10 then 'Key/Value' when 11 then 'List' when 12 then 'File' end as ParamTypeText from ApiParams t where ApiIdCode=@ApiIdCode")); }
public IDictionary <string, object> GetApiConfig(string code) { IDbHelper db = this._dbContext.Use("config_database"); db.AddInputParameter("ApiIdCode", code); IDictionary <string, object> config = db.SelectRow("select t.*,g.GroupName,g.SystemPramsPluginClassName,g.SystemPramsPluginAssemblyPath,g.DataBaseKey,g.Issuer,g.Audience,g.Expires,g.ClockSkew,g.SecurityKey from ApiConfig t left join ApiGroup g on t.GroupId=g.GroupId where ApiIdCode=@ApiIdCode"); return(config); }
public IDictionary <string, object> GetApiRow(string apiIdCode) { IDbHelper db = this._dbContext.Use("config_database"); db.AddInputParameter("ApiIdCode", apiIdCode); db.AddInputParameter("Path", "/api"); return(db.SelectRow(@"select t.*,CASE LOWER( t.Method ) WHEN 'post' THEN CASE lower(t.PostFrom) WHEN 'frombody' THEN @Path || '/PostBody/' || t.ApiIdCode WHEN 'fromform' THEN @Path || '/PostForm/' || t.ApiIdCode END WHEN 'put' THEN CASE lower(t.PostFrom) WHEN 'frombody' THEN @Path || '/PutBody/' || t.ApiIdCode WHEN 'fromform' THEN @Path || '/PutForm/' || t.ApiIdCode END WHEN 'get' THEN @Path || '/Get/' || t.ApiIdCode WHEN 'delete' THEN @Path || '/Delete/' || t.ApiIdCode END AS ApiUrl from ApiConfig t where ApiIdCode=@ApiIdCode")); }
public IDictionary <string, object> GroupPage(int pageIndex, int pageSize, IDictionary <string, object> searchKeys) { StringBuilder whereSql = new StringBuilder(); IDictionary <string, object> searchParams = new Dictionary <string, object>(); foreach (var pair in searchKeys) { if (whereSql.Length > 0) { whereSql.Append(" and "); } whereSql.Append(pair.Key + " like @" + pair.Key); searchParams[pair.Key] = "%" + pair.Value + "%"; } IDbHelper db = this._dbContext.Use("config_database"); db.AddInputParameter(searchParams); string sql = "select count(1) as v_count from ApiGroup where 1=1 " + (whereSql.Length > 0 ? " and " + whereSql : ""); long count = (long)db.ExecuteScalar(sql); if (pageSize <= 0) { pageSize = 10; } int maxPage = (int)Math.Ceiling((decimal)count / pageSize); if (pageIndex > maxPage) { pageIndex = maxPage; } if (pageIndex <= 0) { pageIndex = 1; } db.AddInputParameter(searchParams); db.AddInputParameter("Limit", pageSize); db.AddInputParameter("Offset", (pageIndex - 1) * pageSize); var list = db.ExecuteQuery(@"SELECT t.GroupId,t.GroupName,t.Status,t.DataBaseKey,t.SystemPramsPluginAssemblyPath,t.SystemPramsPluginClassName,t.Remark,case when t.Status=1 then '启用' else '停用' end StatusText FROM ApiGroup t WHERE 1 = 1 " + (whereSql.Length > 0 ? " and " + whereSql : "") + " limit @Limit offset @Offset"); return(new Dictionary <string, object> { { "TotalRows", count }, { "TotalPage", maxPage }, { "PageSize", pageSize }, { "PageIndex", pageIndex }, { "List", list } }); }
public string BuildIn <T>(IDbHelper db, T[] paramData, string fieldName) { StringBuilder builder = new StringBuilder(); int index = 0; foreach (T param in paramData) { if (builder.Length > 0) { builder.Append(","); } string paramName = fieldName + index; builder.Append("@" + paramName); db.AddInputParameter(paramName, param); index++; } return(builder.ToString()); }
//ParamsKind 0=普通参数;1=系统参数;2=Id值 public IList <IDictionary <string, object> > GetApiParaqms(string apiIdCode, params int[] paramsKind) { IDbHelper db = this._dbContext.Use("config_database"); string inStr = this.BuildIn <int>(db, paramsKind, "ParamsKind"); db.AddInputParameter("ApiIdCode", apiIdCode); IList <IDictionary <string, object> > list; if (inStr.Trim() != "") { list = db.ExecuteQuery($@"select t.*,case t.ParamType when 0 then 'String' when 1 then 'Integer' when 2 then 'Long' when 3 then 'Double' when 4 then 'Float' when 5 then 'Decimal' when 6 then 'Boolean' when 7 then 'Date' when 8 then 'DateTime' when 9 then 'ULong' when 10 then 'Key/Value' when 11 then 'List' when 12 then 'File' end as ParamTypeText from ApiParams t where ApiIdCode=@ApiIdCode And ParamsKind in ({inStr})"); } else { list = db.ExecuteQuery($@"select t.*,case t.ParamType when 0 then 'String' when 1 then 'Integer' when 2 then 'Long' when 3 then 'Double' when 4 then 'Float' when 5 then 'Decimal' when 6 then 'Boolean' when 7 then 'Date' when 8 then 'DateTime' when 9 then 'ULong' when 10 then 'Key/Value' when 11 then 'List' when 12 then 'File' end as ParamTypeText from ApiParams t where ApiIdCode=@ApiIdCode"); } return(list); }
private void AnalyseSqlParameters(IDbHelper db, ref string sql, IDictionary <string, object> parameters) { if (parameters == null) { return; } //允许mysql变量 sql = sql.Replace("@@", "#$#"); MatchCollection matches = Regex.Matches(sql, @"@(?<p>\w+)", RegexOptions.Multiline | RegexOptions.IgnoreCase); IDictionary <string, object> paramDic = new Dictionary <string, object>(); foreach (Match m in matches) { var paramName = m.Groups["p"].Value; paramDic[paramName] = parameters[paramName]; } db.AddInputParameter(paramDic); sql = sql.Replace("#$#", "@"); }
public IDictionary <string, object> QueryApiConfigPage(int pageIndex, int pageSize, int apiKind, IDictionary <string, object> searchKeys) { StringBuilder whereSql = new StringBuilder(); IDictionary <string, object> searchParams = new Dictionary <string, object>(); foreach (var pair in searchKeys) { if (whereSql.Length > 0) { whereSql.Append(" and "); } whereSql.Append(pair.Key + " like @" + pair.Key); searchParams[pair.Key] = "%" + pair.Value + "%"; } IDbHelper db = this._dbContext.Use("config_database"); db.AddInputParameter(searchParams); db.AddInputParameter("ApiKind", apiKind); string sql = "select count(1) as v_count from ApiConfig where Status=1 and ApiKind=@ApiKind " + (whereSql.Length > 0 ? " and " + whereSql : ""); long count = (long)db.ExecuteScalar(sql); if (pageSize <= 0) { pageSize = 10; } int maxPage = (int)Math.Ceiling((decimal)count / pageSize); if (pageIndex > maxPage) { pageIndex = maxPage; } if (pageIndex <= 0) { pageIndex = 1; } db.AddInputParameter(searchParams); db.AddInputParameter("Path", "/api"); db.AddInputParameter("ApiKind", apiKind); db.AddInputParameter("Offset", (pageIndex - 1) * pageSize); db.AddInputParameter("Limit", pageSize); var list = db.ExecuteQuery(@"SELECT t.*, CASE WHEN t.ApiKind = 1 THEN '对内接口' ELSE '对外接口' END AS ApiKindText , CASE WHEN t.CodeKind = 1 THEN '分页' WHEN t.CodeKind = 2 THEN '结果集-列表' WHEN t.CodeKind = 3 THEN '结果集-列表' WHEN t.CodeKind = 4 THEN '返回脚本执行结果' ELSE '其它' END CodeKindText, CASE LOWER( t.Method ) WHEN 'post' THEN CASE lower(t.PostFrom) WHEN 'frombody' THEN @Path || '/PostBody/' || t.ApiIdCode WHEN 'fromform' THEN @Path || '/PostForm/' || t.ApiIdCode END WHEN 'put' THEN CASE lower(t.PostFrom) WHEN 'frombody' THEN @Path || '/PutBody/' || t.ApiIdCode WHEN 'fromform' THEN @Path || '/PutForm/' || t.ApiIdCode END WHEN 'get' THEN @Path || '/Get/' || t.ApiIdCode WHEN 'delete' THEN @Path || '/Delete/' || t.ApiIdCode END AS ApiUrl FROM ApiConfig t WHERE Status = 1 AND ApiKind = @ApiKind" + (whereSql.Length > 0 ? " and " + whereSql : "") + " limit @Limit offset @Offset"); return(new Dictionary <string, object> { { "TotalRows", count }, { "TotalPage", maxPage }, { "PageSize", pageSize }, { "PageIndex", pageIndex }, { "List", list } }); }