public List <MovieListData> GetMovieList(string SearchText)
{
List <MovieListData> movieCollection = new List <MovieListData>();
SqlConnection sqlconnection = sqlsetup.SqlConnectionSetup();
try
{
SearchText.Trim();
String search = "\'%" + SearchText + "%\'";
sqlconnection.Open();
SqlCommand sqlTableFetch = new SqlCommand("SELECT Name,movie_id from IMDB.dbo.Movie Where Name like " + search);
sqlTableFetch.Connection = sqlconnection;
SqlDataReader reader = sqlTableFetch.ExecuteReader();
while (reader.Read())
{
MovieListData movielist = new MovieListData();
movielist.Name = reader["Name"].ToString();
movielist.movie_id = Guid.Parse(reader["movie_id"].ToString());
movieCollection.Add(movielist);
}
reader.Close();
}
catch (Exception e)
{
throw e;
}
finally
{
sqlconnection.Close();
}
return(movieCollection);
}
public IActionResult GetMovies(string searchParam, string column, int pageNumber, int itemsPerPage, string sortKey, string sortOrder)
{
if (searchParam == null || column == null || pageNumber == 0 || itemsPerPage == 0 || sortKey == null || sortOrder == null)
{
return(BadRequest());
}
//sanitizing Inputs
char[] Sanitizer = searchParam.ToCharArray();
Sanitizer = Array.FindAll <char>(Sanitizer, c => char.IsLetterOrDigit(c) || char.IsWhiteSpace(c));
searchParam = new string(Sanitizer);
Sanitizer = column.ToCharArray();
Sanitizer = Array.FindAll <char>(Sanitizer, c => char.IsLetterOrDigit(c));
column = new string(Sanitizer);
Sanitizer = sortKey.ToCharArray();
Sanitizer = Array.FindAll <char>(Sanitizer, c => char.IsLetterOrDigit(c));
sortKey = new string(Sanitizer);
Sanitizer = sortOrder.ToCharArray();
Sanitizer = Array.FindAll <char>(Sanitizer, c => char.IsLetterOrDigit(c));
sortOrder = new string(Sanitizer);
//alternative to Sanitizing, but apparently slower?
/*
* Regex sanitizer = new Regex("[A-Za-z0-9]");
* searchParam = sanitizer.Replace(searchParam, "");
* column = sanitizer.Replace(column, "");
* sortKey = sanitizer.Replace(sortKey, "");
* sortOrder = sortOrder.Replace(sortOrder, "");
*/
#pragma warning disable EF1000 // Possible SQL injection vulnerability.
var result = database.Movie.FromSql("" +
"SELECT * FROM Movie " +
"WHERE " + column + " " +
"LIKE '%" + searchParam + "%' " +
"ORDER BY " + sortKey + " " + sortOrder)
#pragma warning restore EF1000 // Possible SQL injection vulnerability.
.ToList();
int hits = result.Count;
result = result.Skip((pageNumber - 1) * itemsPerPage)
.Take(itemsPerPage)
.ToList();
MovieListData returnValue = new MovieListData(result, hits);
return(Ok(returnValue));
}
