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));
}
