private void AddParameters(SqlScriptModel sqlScript) { foreach (var parameter in sqlScript.Parameters) { sqlScript.SqlRequest.SqlScript = sqlScript.SqlRequest.SqlScript.Replace($"[@{parameter.Name}@]", parameter.Value); } }
public ActionResult RunScriptExcel(string scriptname, string parameter = null) { var model = new SqlScriptModel(CurrentDatabase); var content = CurrentDatabase.ContentOfTypeSql(scriptname); if (content == null) { return(Message("no content")); } var d = Request.QueryString.AllKeys.ToDictionary(key => key, key => Request.QueryString[key]); var p = new DynamicParameters(); foreach (var kv in d) { p.Add("@" + kv.Key, kv.Value); } string script = model.AddParametersForSql(parameter, content, p, ViewBag); if (script.StartsWith("Not Authorized")) { return(Message(script)); } using (var cn = CurrentDatabase.ReadonlyConnection()) { cn.Open(); return(cn.ExecuteReader(script, p, commandTimeout: 1200).ToExcel("RunScript.xlsx", fromSql: true)); } }
public PagedQueryResultModel ProcessSqlScript(SqlScriptModel sqlScript) { var sqlRequest = sqlScript.SqlRequest; AddParameters(sqlScript); var connectionString = string.IsNullOrEmpty(sqlRequest.ConnectionStringName) ? config.ConnectionString : config.GetUserConnectionString(sqlRequest.ConnectionStringName); //var connectionString = "Data Source = 193.93.216.233;Initial Catalog = TILT2_DEV;Integrated Security = False;User Id = sae;Password=Devabit1@"; var offset = (sqlRequest.PaginationModel.PageNumber - 1) * sqlRequest.PaginationModel.PageSize + sqlRequest.PaginationModel.Skip; var pageSize = sqlRequest.PaginationModel.PageSize; using (var connection = new SqlConnection(connectionString)) { connection.Open(); //var totalCount = new SqlCommand(SqlExpression.CountExpression(sqlRequest.SqlScript), connection).ExecuteScalar(); // sqlRequest.SqlScript.AddPaginationToScript(offset, pageSize); var sqlCommand = new SqlCommand(sqlRequest.SqlScript, connection); var sqlDataAdapter = new SqlDataAdapter(sqlCommand); var dataSet = new DataSet(); sqlDataAdapter.Fill(dataSet); var parser = new DataSetParser(dataSet); var result = parser.ToQueryResultModel(); var totalCount = result.Rows.Count; result.Rows = result.Rows.Skip(Convert.ToInt32(offset)).Take(Convert.ToInt32(pageSize)).ToList(); return(new PagedQueryResultModel { TotalCount = totalCount,// (int?)totalCount ?? 0, PageCount = (long)Math.Ceiling((double)totalCount / sqlRequest.PaginationModel.PageSize), PageNumber = sqlRequest.PaginationModel.PageNumber, HasNext = Math.Ceiling((double)totalCount / sqlRequest.PaginationModel.PageSize) > sqlRequest.PaginationModel.PageNumber, Result = result }); } }
public PagedQueryResultModel ProcessSavedSqlScript(SavedScriptProcessModel script, bool getAllRecords = false) { var savedScript = dal.ProcessSavedSqlScript(script.Id); savedScript.PaginationModel = script.Pagination; if (getAllRecords) { savedScript.PaginationModel.PageSize = int.MaxValue; savedScript.PaginationModel.PageNumber = 1; } savedScript.ConnectionStringName = script.ConnectionStringName; var scriptModel = new SqlScriptModel { SqlRequest = savedScript, Parameters = script.Parameters }; var result = tableDataManager.ProcessSqlScript(scriptModel); return(result); }
public ActionResult RunScript(string name, string parameter = null, string title = null) { var m = new SqlScriptModel(CurrentDatabase); var sql = m.FetchScript(name); if (sql == null) { return(Message("no sql script named " + name)); } if (!SqlScriptModel.CanRunScript(sql)) { return(Message("Not Authorized to run this script")); } var p = m.FetchParameters(); var pSql = m.AddParametersForSql(parameter, sql, p, ViewBag); ViewBag.Report = name; ViewBag.Name = title ?? $"{name.SpaceCamelCase()} {parameter}"; if (sql.Contains("pagebreak")) { ViewBag.report = PythonModel.PageBreakTables(CurrentDatabase, sql, p); return(View("RunScriptPageBreaks")); } ViewBag.Url = Request.Url?.PathAndQuery; string html; using (var cn = CurrentDatabase.ReadonlyConnection()) { cn.Open(); var rd = cn.ExecuteReader(sql, p, commandTimeout: 1200); ViewBag.ExcelUrl = Request.Url?.AbsoluteUri.Replace("RunScript/", "RunScriptExcel/"); html = GridResult.Table(rd, ViewBag.Name2); } return(View(new HtmlHolder { html = html })); }
public PagedQueryResultModel ProcessSqlScript([FromBody] SqlScriptModel querie) { return(manager.ProcessSqlScript(querie)); }
//TODO: implement method public PagedQueryResultModel ProcessSqlScript(SqlScriptModel sqlScriptModel) { return(dal.ProcessSqlScript(sqlScriptModel)); }