public JsonResult UpdateQuery(Query QueriesModel)
{
try
{
if (!ModelState.IsValid)
{
return(Json(new { Result = "ERROR", Message = "Form is not valid! Please correct it and try again." }));
}
//string constring = ConfigurationManager.AppSettings["AutoAppDatabase"];
//SqlConnection con = new SqlConnection(constring);
//string updateQuery = "update Queries set Query= '" + QueriesModel.Query1 + "', QueryAlias= '" + QueriesModel.QueryAlias + "' where QueryID ='"+ QueriesModel.QueryID + "'";
//con.Open();
//SqlCommand cmd = new SqlCommand(updateQuery, con);
//cmd.ExecuteNonQuery();
//con.Close();
var database = new QueryDBAppEntities1();
database.Entry(QueriesModel).State = EntityState.Modified;
database.SaveChanges();
return(Json(new { Result = "OK" }));
}
catch (Exception ex)
{
return(Json(new { Result = "ERROR", Message = ex.Message }));
}
}
public JsonResult CreateQuery(Query QueriesModel)
{
try
{
if (!ModelState.IsValid)
{
return(Json(new { Result = "ERROR", Message = "Form is not valid! Please correct it and try again." }));
}
//string constring = ConfigurationManager.AppSettings["AutoAppDatabase"];
//SqlConnection con = new SqlConnection(constring);
//string insertQuery = "Insert into Queries (Query, QueryAlias) values(@query, @queryAlias)";
//con.Open();
//SqlCommand cmd = new SqlCommand(insertQuery, con);
//cmd.Parameters.AddWithValue("@query", QueriesModel.Query1);
//cmd.Parameters.AddWithValue("@queryAlias", QueriesModel.QueryAlias);
//cmd.ExecuteNonQuery();
//con.Close();
var database = new QueryDBAppEntities1();
database.Queries.Add(QueriesModel);
database.SaveChanges();
return(Json(new { Result = "OK", Record = QueriesModel }, JsonRequestBehavior.AllowGet));
}
catch (Exception ex)
{
return(Json(new { Result = "ERROR", Message = ex.Message }));
}
}
public ActionResult Login(User user, string returnUrl)
{
using (var database = new QueryDBAppEntities1())
{
SHA1 sha = new SHA1CryptoServiceProvider();
ASCIIEncoding encoder = new ASCIIEncoding();
byte[] combined = encoder.GetBytes(user.Password);
string hash = BitConverter.ToString(sha.ComputeHash(combined)).Replace("-", "");
//return result;
var Verify = database.Users.FirstOrDefault(usr => usr.UserName == user.UserName && usr.Password == hash);
if (Verify != null)
{
FormsAuthentication.SetAuthCookie(Verify.UserName, false);
if (Url.IsLocalUrl(returnUrl) && returnUrl.Length > 1 && returnUrl.StartsWith("/") && !returnUrl.StartsWith("//") && !returnUrl.StartsWith("/\\"))
{
return(Redirect(returnUrl));
}
else
{
return(RedirectToAction("AddQuery"));
}
}
else
{
ModelState.AddModelError("CustomError", "Either your credentials are not correct or you do not have access as you are not an admin");
return(View("Login"));
}
}
}
public ActionResult Index()
{
//return encodedData;
#region OldCode
//string strConString = ConfigurationManager.AppSettings["AutoAppDatabase"];
//SqlConnection con = new SqlConnection(strConString);
//con.Open();
//SqlCommand cmd = new SqlCommand("Select * from Queries", con);
//SqlDataAdapter sd = new SqlDataAdapter(cmd);
//DataTable dt = new DataTable();
//sd.Fill(dt);
//List<QueriesModel> getQueryList = new List<QueriesModel>();
//for (int i = 0; i < dt.Rows.Count; i++)
//{
// QueriesModel student = new QueriesModel();
// student.QueryID = Convert.ToInt32(dt.Rows[i]["QueryID"]);
// student.Query1 = dt.Rows[i]["Query"].ToString();
// student.QueryAlias = dt.Rows[i]["QueryAlias"].ToString();
// getQueryList.Add(student);
//}
#endregion
QueryModel queryModel = new QueryModel();
var database = new QueryDBAppEntities1();
var getQueryList = database.Queries.ToList();
getQueryList = getQueryList.OrderBy(p => p.QueryAlias).ToList();
List <SelectListItem> list = null;
IEnumerable <SelectListItem> queryList = getQueryList.Select(f => new SelectListItem
{
Value = f.Query1.ToString(),
Text = f.QueryAlias
});
list = queryList.ToList();
var newItem = new SelectListItem {
Value = "", Text = "Select a Query"
};
list.Insert(0, newItem);
queryModel.QueryItems = list;
var getDBList = database.EPIDatabases.ToList();
List <SelectListItem> dblist = null;
IEnumerable <SelectListItem> queryDBList = getDBList.Select(f => new SelectListItem
{
Value = f.DatabaseFullName,
Text = f.DatabaseShortName
});
dblist = queryDBList.ToList();
var newDBItem = new SelectListItem {
Value = "", Text = "Select a Query"
};
dblist.Insert(0, newDBItem);
queryModel.DatabaseItems = dblist;
return(View(queryModel));
}
public JsonResult DeleteDB(int DatatabseID)
{
try
{
var database = new QueryDBAppEntities1();
EPIDatabases dbDetail = database.EPIDatabases.Find(DatatabseID);
database.EPIDatabases.Remove(dbDetail);
database.SaveChanges();
return(Json(new { Result = "OK" }));
}
catch (Exception ex)
{
return(Json(new { Result = "ERROR", Message = ex.Message }));
}
}
public JsonResult DisplayDBList(int jtStartIndex = 0, int jtPageSize = 0, string jtSorting = null)
{
//List<Dictionary<string, object>> expandolist = new List<Dictionary<string, object>>();
List <EPIDatabases> list = new List <EPIDatabases>();
//expandolist = getResult();
var database = new QueryDBAppEntities1();
var expandolist = database.EPIDatabases.ToList();
if (jtSorting.Equals("DatatabseID DESC"))
{
expandolist = expandolist.OrderByDescending(p => p.DatatabseID).ToList();
}
list = expandolist.Skip(jtStartIndex).Take(jtPageSize).ToList();
var queryCount = expandolist.Count();
return(Json(new { Result = "OK", Records = list, TotalRecordCount = queryCount }, JsonRequestBehavior.AllowGet));
}
public JsonResult UpdateDB(EPIDatabases DBModel)
{
try
{
if (!ModelState.IsValid)
{
return(Json(new { Result = "ERROR", Message = "Form is not valid! Please correct it and try again." }));
}
var database = new QueryDBAppEntities1();
database.Entry(DBModel).State = EntityState.Modified;
database.SaveChanges();
return(Json(new { Result = "OK" }));
}
catch (Exception ex)
{
return(Json(new { Result = "ERROR", Message = ex.Message }));
}
}
public JsonResult CreateDB(EPIDatabases DBModel)
{
try
{
if (!ModelState.IsValid)
{
return(Json(new { Result = "ERROR", Message = "Form is not valid! Please correct it and try again." }));
}
var database = new QueryDBAppEntities1();
database.EPIDatabases.Add(DBModel);
database.SaveChanges();
return(Json(new { Result = "OK", Record = DBModel }, JsonRequestBehavior.AllowGet));
}
catch (Exception ex)
{
return(Json(new { Result = "ERROR", Message = ex.Message }));
}
}
public JsonResult DeleteQuery(int QueryID)
{
try
{
//string constring = ConfigurationManager.AppSettings["AutoAppDatabase"];
//SqlConnection con = new SqlConnection(constring);
//string updateQuery = "delete from Queries where QueryID = '" + QueryID + "'";
//con.Open();
//SqlCommand cmd = new SqlCommand(updateQuery, con);
//cmd.ExecuteNonQuery();
//con.Close();
var database = new QueryDBAppEntities1();
Query queriesDetail = database.Queries.Find(QueryID);
database.Queries.Remove(queriesDetail);
database.SaveChanges();
return(Json(new { Result = "OK" }));
}
catch (Exception ex)
{
return(Json(new { Result = "ERROR", Message = ex.Message }));
}
}
