public static string executeSQL(ScriptsLogDTO scriptLogDTO)
{
DataTable dataTable = new DataTable();
using (SqlConnection connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["connectionRRHHDatabase"].ConnectionString))
{
SqlCommand command = new SqlCommand(scriptLogDTO.script, connection);
command.Connection.Open();
if (scriptLogDTO.script.ToLower().Contains("select"))
{
SqlDataAdapter da = new SqlDataAdapter(command);
da.Fill(dataTable);
JavaScriptSerializer serializer = new JavaScriptSerializer();
List <Dictionary <string, object> > rows = new List <Dictionary <string, object> >();
Dictionary <string, object> row;
foreach (DataRow dr in dataTable.Rows)
{
row = new Dictionary <string, object>();
foreach (DataColumn col in dataTable.Columns)
{
row.Add(col.ColumnName, dr[col]);
}
rows.Add(row);
}
return(serializer.Serialize(rows));
}
else
{
return(command.ExecuteNonQuery().ToString());
}
};
}
public string executeAgain(ScriptsLogDTO scriptDTO)
{
string result = "-1";
if ((result = ScriptsLogData.executeSQL(scriptDTO)) != "")
{
return(result);
}
return(result);
}
public string Post(ScriptsLogDTO scriptDTO)
{
string result = "-1";
if ((result = ScriptsLogData.executeSQL(scriptDTO)) != "")
{
if (!ScriptsLogData.insertScriptLog(scriptDTO))
{
return(result);
}
}
return(result);
}
public ActionResult _AddScript(Model.AddScriptModel model)
{
if (ModelState.IsValid)
{
ScriptsLogDTO script = new ScriptsLogDTO();
if (model.scriptType == "file")
{
HttpFileCollectionBase files = Request.Files;
for (int i = 0; i < files.Count; i++)
{
HttpPostedFileBase file = files[i];
string fname = file.FileName;
string fileName = Path.GetFileName(fname);
string fileExtension = Path.GetExtension(fileName).ToLower();
int fileSize = file.ContentLength;
Stream stream = file.InputStream;
BinaryReader binaryReader = new BinaryReader(stream);
byte[] bytes = binaryReader.ReadBytes((int)stream.Length);
script.script = System.Text.Encoding.UTF8.GetString(bytes);
}
}
else
{
script.script = model.scriptText;
}
script.userLog = Request.Cookies["user_id"].Value;
string result;
if ((result = scriptProvider.postScript(script).Result) != "-1")
{
if (script.script.ToLower().Contains("select"))
{
return(Json(new { type = "table", result = PartialView("/Views/Scripts/_ScriptResult.cshtml", new Model.ScriptResultTable(result)).RenderToString() }));
}
else
{
return(Json(new { type = "affectedRows", result = result }));
}
}
}
return(new HttpStatusCodeResult(404, "Can't find that"));
}
//--------------------------------------------- Inserts --------------------------------------------
public static bool insertScriptLog(ScriptsLogDTO scriptLogDTO)
{
using (SqlConnection connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["connectionRRHHDatabase"].ConnectionString))
{
SqlCommand command = new SqlCommand("usp_insert_scriptLog", connection);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.Add("@script", SqlDbType.NVarChar);
command.Parameters["@script"].Value = scriptLogDTO.script;
command.Parameters.Add("@userLog", SqlDbType.Int);
command.Parameters["@userLog"].Value = scriptLogDTO.userLog;
command.Connection.Open();
int result = command.ExecuteNonQuery();
if (result != 0)
{
return(true);
}
return(false);
};
}
public async Task <string> getScriptResult(ScriptsLogDTO scriptDTO)
{
using (var client = new HttpClient())
{
client.BaseAddress = new Uri(_BaseAddress);
JavaScriptSerializer serializer = new JavaScriptSerializer();
serializer.MaxJsonLength = Int32.MaxValue;
var userJson = serializer.Serialize(scriptDTO);
HttpContent contentPost = new StringContent(userJson, Encoding.UTF8, "application/json");
client.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", getToken());
HttpResponseMessage response = client.PostAsync("api/scripts/reexecute", contentPost).Result;
if (response.IsSuccessStatusCode)
{
var result = await response.Content.ReadAsStringAsync();
JavaScriptSerializer serializerr = new JavaScriptSerializer();
serializerr.MaxJsonLength = Int32.MaxValue;
return(serializer.Deserialize <String>(result));
}
return("-1");
}
}
public static List <ScriptsLogDTO> getScriptsLog()
{
List <ScriptsLogDTO> templates = new List <ScriptsLogDTO>();
using (SqlConnection connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["connectionRRHHDatabase"].ConnectionString))
{
SqlCommand command = new SqlCommand("usp_get_scriptsLog", connection);
command.CommandType = CommandType.StoredProcedure;
command.Connection.Open();
SqlDataReader rdr = command.ExecuteReader();
while (rdr.Read())
{
ScriptsLogDTO scriptLog = new ScriptsLogDTO();
scriptLog.id_scriptLog = rdr["id_scriptLog"].ToString();
scriptLog.script = rdr["script"].ToString();
scriptLog.ejecutedBy = rdr["ejecutedBy"].ToString();
scriptLog.ejecutedDate = rdr["ejecutedDate"].ToString();
scriptLog.ejecutedBy_name = rdr["name"].ToString() + " " + rdr["fLastName"].ToString() + " " + rdr["sLastName"].ToString();
templates.Add(scriptLog);
}
};
return(templates);
}