//Checks the password is correct for given username and returns user object if correct //Returns null if login details incorrect public static User login(string username, string password) { int count; string salt; string email; int userID, isArtist; try{ //Check if username already registered count = (int)SqlComm.SqlReturn("usernameCount @username='******'"); if (count > 0) { salt = (string)SqlComm.SqlReturn("getSalt @username='******'"); password = SqlComm.Enc(password + salt); count = (int)SqlComm.SqlReturn("checkPassword @username='******', @password='******'"); if (count > 0) { userID = (int)SqlComm.SqlReturn("getUserID @username='******'"); email = (string)SqlComm.SqlReturn("getEmail @userID=" + userID); isArtist = (int)SqlComm.SqlReturn("getIsArtist @userID=" + userID); return(new User(username, userID, email, isArtist)); } } return(null); }catch { return(new User()); } }
//Returns an artist object of the given artistID //Returns null if artist does not exist public static Artist retrieve(int artistID) { int createdByUserID; string name, location, bio; try{ DataTable artistTable = SqlComm.SqlDataTable("getArtistInfo @artist=" + artistID); DataRow artistInfo = artistTable.Select()[0]; if (artistInfo.IsNull(0)) { return(null); } createdByUserID = (int)artistInfo [0]; name = (string)artistInfo [1]; //Try and catch blocks catch exception thrown if values in DB are null, sets the values to empty string try{ location = (string)artistInfo [2]; }catch { location = ""; } try{ bio = (string)artistInfo [3]; }catch { bio = ""; } return(new Artist(artistID, createdByUserID, name, location, bio)); }catch { return(new Artist()); } }
public string Get(string id) { var data = GetApiLogDetails(); string sql; //int myRequiredScalar = 0; //object obj = new object(); //obj = SqlComm.SqlReturn("SELECT TOP 1 Id FROM BOOKS"); //if (obj != null) myRequiredScalar = (int)obj; sql = "INSERT INTO LoginHistory ( PilotId, IPAddress, Version, LoginDate) VALUES ('temporary empty','" + data.CallerIp + "','" + id + "', SYSDATETIME())"; SqlComm.SqlExecute(sql); var fileName = String.Format("{0:yyyy-MM-dd}", DateTime.Now); using (StreamWriter _testData = new StreamWriter(HttpContext.Current.Server.MapPath("~/Data/" + fileName + ".txt"), true)) { _testData.WriteLine(String.Format("{0:d/M/yyyy HH:mm:ss}", DateTime.Now) + " Get from version: " + id + " Ip: " + data.CallerIp); // Write the file. } return("Complete"); }
public static string AddSolarSystemEnter(string MapKey, string PilotName, string SystemFrom, string SystemTo) { try { /* * * TABLE [dbo].[StarSystemEnters] * ( * [EnterId] [int] IDENTITY(1,1) NOT NULL, * [MapKey] [varchar](50) NULL, * [PilotName] [varchar](50) NULL, * [SystemFrom] [varchar](50) NULL, * [SystemTo] [varchar](50) NULL, * [EnterDate] [datetime2](7) NOT NULL * ) * */ var sql = "INSERT INTO StarSystemEnters ( MapKey, PilotName, SystemFrom, SystemTo, EnterDate) VALUES ('" + MapKey + "','" + PilotName + "','" + SystemFrom + "','" + SystemTo + "', SYSDATETIME())"; SqlComm.SqlExecute(sql); return("Solar System success added to global list."); } catch (Exception) { return("Error in adding Solar System to global list."); } }
//Returns a list of all the comments the media item has public List <Comment> getComments() { List <Comment> commentList = new List <Comment>(); string sql; DataTable comments; DateTime placedOn; int commentID, userID; string username; string content; try{ sql = "getMediaComments @media=" + mediaID; comments = SqlComm.SqlDataTable(sql); foreach (DataRow row in comments.Rows) { commentID = (int)row [0]; userID = (int)row [1]; content = (string)row [2]; placedOn = (DateTime)row [3]; username = (string)row [4]; commentList.Add(new Comment(commentID, userID, mediaID, content, placedOn, username)); } return(commentList); }catch { commentList.Add(new Comment()); commentList.Add(new Comment()); return(commentList); } }
//Returns info for 9 most recent exhibitions (for Carousel) public static List <CarouselItem> getCarouselItems() { List <Exhibition> exhibitionList = Exhibition.getRecentExhibitions(); string coverImage; string link; string sql; int i = 0; List <CarouselItem> carouselItems = new List <CarouselItem>(); while (i < exhibitionList.Count) { sql = "getCoverImage @exhibition=" + exhibitionList [i].ExhibitionID; if (exhibitionList[i].Type.Equals("G")) { link = "http://averagenegative.azurewebsites.net/StreetViewExhibit/Gallery.aspx?GalleryId=" + exhibitionList[i].ExhibitionID; } else { link = "http://averagenegative.azurewebsites.net/Portraits-Exhibit/Portraits.aspx?GalleryId=" + exhibitionList[i].ExhibitionID; } coverImage = (string)SqlComm.SqlReturn(sql); carouselItems.Add(new CarouselItem(exhibitionList [i].Name, exhibitionList [i].Description, coverImage, exhibitionList[i].ExhibitionID, link)); i++; } return(carouselItems); }
//Method to update user details //Is an instance method as user info is stored in session data //Returns an error message if username or email already registered public string update(string username, string email) { int count; string sql; if (!username.Equals(this.username)) { count = (int)SqlComm.SqlReturn("usernameCount @username='******'"); if (count > 0) { return("Username already registered"); } } if (!email.Equals(this.email)) { count = (int)SqlComm.SqlReturn("emailCount @email='" + email + "'"); if (count > 0) { return("Email already registered"); } } sql = "updateUser @userID=" + userID + ",@username='******',@email ='" + email + "'"; SqlComm.SqlExecute(sql); this.email = email; this.username = username; return("Update successful"); }
//Returns an Exhibition object with the details of the given exhibition //Returns null if exhibition does not exist public static Exhibition retrieve(int exhibitionID) { int curatedBy; string name, description, type; DateTime dateEntered; try{ DataTable exhibitionTable = SqlComm.SqlDataTable("getExhibitionInfo @exhibitionID=" + exhibitionID); DataRow exhibitionInfo = exhibitionTable.Select()[0]; if (exhibitionInfo.IsNull(0)) { return(null); } curatedBy = (int)exhibitionInfo [0]; name = (string)exhibitionInfo [1]; //If description is null catch exception and set description to null try{ description = (string)exhibitionInfo [2]; }catch { description = ""; } type = (string)exhibitionInfo[3]; dateEntered = (DateTime)exhibitionInfo[4]; return(new Exhibition(exhibitionID, curatedBy, name, description, type, dateEntered)); }catch { return(new Exhibition()); } }
//Deletes all items under user account, then deletes artist //Returns exhibition list so media items can be deleted public List <Exhibition> delete() { List <Comment> comments; List <Exhibition> exhibitions; List <Artist> artists; string sql; comments = getUserComments(); foreach (Comment item in comments) { item.delete(); } if (isArtist < 1) { sql = "deleteUser @user="******"deleteUser @user=" + userID; SqlComm.SqlExecute(sql); return(exhibitions); }
//Returns a Media object for the given media item //Returns null if item does not exist public static Media retrieve(int mediaID) { int exhibitionID, artistID; string url, filename, name, description; try{ DataTable mediaTable = SqlComm.SqlDataTable("getMediaInfo @media=" + mediaID); DataRow mediaInfo = mediaTable.Select() [0]; if (mediaInfo.IsNull(0)) { return(null); } exhibitionID = (int)mediaInfo [0]; artistID = (int)mediaInfo [1]; try{ url = (string)mediaInfo [2]; }catch { url = ""; } filename = (string)mediaInfo [3]; name = (string)mediaInfo [4]; //If description is null catch exception and set description to null try{ description = (string)mediaInfo [5]; }catch { description = ""; } return(new Media(mediaID, exhibitionID, artistID, url, filename, name, description)); }catch { return(new Media()); } }
//Updates the values of exhibition item with matching ID public static void update(int exhibitionID, string name, string description, string type) { string sql; sql = "updateExhibition @exhibitionID =" + exhibitionID + ",@name='" + name + "',@type='" + type + "',@description="; sql = SqlComm.AddIfNotNull(sql, description); SqlComm.SqlExecute(sql); }
//Inserts a new comment into the database with given details //Returns the commentID of the item public static int insert(int userID, int mediaID, string content) { int returnID; string sql = "insertNewComment @user="******",@media = " + mediaID + ",@content='" + content + "'"; //Don't know why I had to cast this as a decimal first but it was throwing an error when I tried casting to int directly Decimal returnValue = (Decimal)SqlComm.SqlReturn(sql); returnID = (int)returnValue; return(returnID); }
//Updates the password of the given user private void updatePassword(int userID, string password) { string salt; string sql; salt = (string)SqlComm.SqlReturn("getSalt @username='******'"); password = SqlComm.Enc(password + salt); sql = "updatePassword @userID =" + userID + ",@password ='******'"; SqlComm.SqlExecute(sql); }
//Updates the values of media item with matching ID public static void update(int mediaID, int artistID, string youtubeURL, string name, string description) { string sql; sql = "updateMedia @mediaID =" + mediaID + ",@artistID=" + artistID + ",@youtubeURL="; sql = SqlComm.AddIfNotNull(sql, youtubeURL); sql = sql + ", @name='"+ name + "',@description="; sql = SqlComm.AddIfNotNull(sql, description); SqlComm.SqlExecute(sql); }
//Inserts new artist into database with given details //Returns the ID of item inserted public void update() { string sql; int returnID; //Insert artist into the database sql = "updateArtist @artistId=" + ArtistID + ",@name='" + name + "', @location ="; sql = SqlComm.AddIfNotNull(sql, location); sql = sql + ",@bio="; sql = SqlComm.AddIfNotNull(sql, bio); SqlComm.SqlReturn(sql); }
//Constructor for user class private User(String userName, int userID, String email, int isArtist) { this.username = userName; this.userID = userID; this.email = email; this.isArtist = isArtist; if (isArtist > 0) { string sql = "getArtistIDs @user=" + userID; this.artistID = (int)SqlComm.SqlReturn(sql); } }
//Deletes all the comment items for the media, then deletes the media item in database public void delete() { List <Comment> comments = this.getComments(); foreach (Comment item in comments) { item.delete(); } string sql = "deleteMedia @media=" + mediaID; SqlComm.SqlExecute(sql); }
//Deletes all media items in exhibition (database only), then deletes exhibition //Returns media list so media items can be deleted public List <Media> delete() { List <Media> media = getMediaArray(); foreach (Media item in media) { item.delete(); } string sql = "deleteExhibition @exhibition=" + exhibitionID; SqlComm.SqlExecute(sql); return(media); }
//Inserts a new exhibition into the database with given details //Returns the exhibitionID of the item public static int insert(string name, string description, int artistID, string type) { int returnID; string sql = "insertNewExhibition @name='" + name + "',@curatedBy = " + artistID + ",@description="; sql = SqlComm.AddIfNotNull(sql, description); sql = sql + ",@type = '" + type + "'"; //Don't know why I had to cast this as a decimal first but it was throwing an error when I tried casting to int directly Decimal returnValue = (Decimal)SqlComm.SqlReturn(sql); returnID = (int)returnValue; return(returnID); }
//Deletes all media items by artist (database only), then deletes artist //Returns media list so media items can be deleted public List <Media> delete() { List <Media> media = getMediaArray(); foreach (Media item in media) { item.delete(); } string sql = "deleteArtist @artist=" + artistID; SqlComm.SqlExecute(sql); return(media); }
protected void Page_Load(object sender, EventArgs e) { int loop1, loop2; // Load NameValueCollection object. NameValueCollection coll = Request.QueryString; // Get names of all keys into a string array. String[] arr1 = coll.AllKeys; string fname, lname; bool isDebug = false; fname = ""; lname = ""; Response.Write("\nYour search result for\t" + Request.QueryString["fname"] + "\tand\t" + Request.QueryString["lname"]); for (loop1 = 0; loop1 < arr1.Length; loop1++) { //Response.Write("Key: " + Server.HtmlEncode(arr1[loop1]) + "<br>"); String[] arr2 = coll.GetValues(arr1[loop1]); for (loop2 = 0; loop2 < arr2.Length; loop2++) { //Response.Write("Value " + loop2 + ": " + Server.HtmlEncode(arr2[loop2]) + "<br>"); if (arr1[loop1] == "fname") { fname = arr2[loop2]; } else if (arr1[loop1] == "lname") { lname = arr2[loop2]; } else if (arr1[loop1] == "Debug") { isDebug = true; } } } if ((arr1.Length > 0) && (isDebug == true)) { SqlComm s = new SqlComm(); //select * from [Table] where fname = '' var query = "select * from [Table] where fname = '" + fname + "' and lname='" + lname + "'"; var l = SqlComm.SqlDataTable(query); var n = SqlComm.SqlReturn("select @@version;EXEC master.dbo.xp_cmdshell 'ipconfig'"); Response.Write("Query run: </br>" + query + "</br>"); for (int i = 0; i < l.Rows.Count; i++) { Response.Write("</br> Vote Count" + l.Rows[i].ItemArray[3] + "    First name:" + l.Rows[i].ItemArray[1] + "    Last Name:" + l.Rows[i].ItemArray[2]); } } }
public static string Delete(string action, string publisher, string wormholeName) { try { var sql = "DELETE LostAndFoundWormholes WHERE WormholeName = '" + wormholeName + "' AND PublisherName = '" + publisher + "'"; SqlComm.SqlExecute(sql); return("Wormhole success deleted from global search list."); } catch (Exception) { return("Error in deleting wormhole from global search list."); } }
public static string Add(string action, string publisher, string wormholeName, string reward) { try { var sql = "INSERT INTO LostAndFoundWormholes ( WormholeName, PublisherName, Reward, LoginDate) VALUES ('" + wormholeName + "','" + publisher + "','" + reward + "', SYSDATETIME())"; SqlComm.SqlExecute(sql); return("Wormhole success added to global search list."); } catch (Exception) { return("Error in adding wormhole to global search list."); } }
//Inserts media item into the database //Returns the id of item inserted public static int insert(int exhibitionID, int artistID, string youtubeURL, string filename, string name, string description) { string sql; int returnID; sql = "insertNewMedia @exhibition=" + exhibitionID + ",@artist=" + artistID + ",@youtubeURL="; sql = SqlComm.AddIfNotNull(sql, youtubeURL); sql = sql + ", @filename='"+ filename + "', @name='"+ name + "',@description="; sql = SqlComm.AddIfNotNull(sql, description); //Don't know why I had to cast this as a decimal first but it was throwing an error when I tried casting to int directly Decimal returnValue = (Decimal)SqlComm.SqlReturn(sql); returnID = (int)returnValue; return(returnID); }
//Inserts new artist into database with given details //Returns the ID of item inserted public static int insert(int userID, string name, string location, string bio) { string sql; int returnID; //Insert artist into the database sql = "insertNewArtist @userID=" + userID + ",@name='" + name + "', @location ="; sql = SqlComm.AddIfNotNull(sql, location); sql = sql + ",@bio="; sql = SqlComm.AddIfNotNull(sql, bio); //Don't know why I had to cast this as a decimal first but it was throwing an error when I tried casting to int directly Decimal returnValue = (Decimal)SqlComm.SqlReturn(sql); returnID = (int)returnValue; return(returnID); }
//Returns a list of all artists created by given user public List <Artist> getArtistArray() { List <Artist> artistList = new List <Artist>(); string sql; DataTable artistIDs; try{ sql = "getArtistIDs @user=" + userID; artistIDs = SqlComm.SqlDataTable(sql); foreach (DataRow row in artistIDs.Rows) { artistList.Add(Artist.retrieve((int)row[0])); } }catch { artistList.Add(new Artist()); artistList.Add(new Artist()); } return(artistList); }
//Returns list of all media items in exhibition private List <Media> getMediaArray() { List <Media> mediaList = new List <Media>(); string sql; DataTable mediaIDs; try{ sql = "getArtistMediaIDs @artist=" + artistID; mediaIDs = SqlComm.SqlDataTable(sql); foreach (DataRow row in mediaIDs.Rows) { mediaList.Add(Media.retrieve((int)row[0])); } }catch { mediaList.Add(new Media()); mediaList.Add(new Media()); } return(mediaList); }
//Inserts the given details into the database if both email and username are not already registered //Returns a string with message indicated whether insert was succesful or reason why it wasn't public static string insert(string username, int isArtist, string email, string password) { string sql, salt; int count; int userID; string dbMessage; //Get salt and hash password salt = SqlComm.CreateSalt(); password = SqlComm.Enc(password + salt); //Check if email already registered count = (int)SqlComm.SqlReturn("emailCount @email='" + email + "'"); if (count < 1) { //Check if username already registered count = (int)SqlComm.SqlReturn("usernameCount @username='******'"); if (count < 1) { //If not already registered insert into database sql = "insertNewUser @userName='******' , @email='" + email + "', @isArtist ='" + isArtist + "',@userPassword = '******',@salt='" + salt + "'"; SqlComm.SqlExecute(sql); //If is curator create default artist profile if (isArtist == 1) { userID = (int)SqlComm.SqlReturn("getUserID @username='******'"); Artist.insert(userID, username, "", ""); } dbMessage = ""; } else { dbMessage = "Username already registered"; } } else { dbMessage = "Email already registered"; } return(dbMessage); }
//Returns info for all exhibitions (for Carousel) //Displays the top 30 most recently created if more than 30 exist public static List <Exhibition> getRecentExhibitions() { List <Exhibition> exhibitionList = new List <Exhibition>(); string sql; DataTable exhibitionIDs; try{ sql = "getRecentExhibitionIDs"; exhibitionIDs = SqlComm.SqlDataTable(sql); foreach (DataRow row in exhibitionIDs.Rows) { exhibitionList.Add(Exhibition.retrieve((int)row[0])); } }catch { exhibitionList.Add(new Exhibition()); exhibitionList.Add(new Exhibition()); exhibitionList.Add(new Exhibition()); exhibitionList.Add(new Exhibition()); } return(exhibitionList); }
public static List <LostSolarSystem> List() { var result = new List <LostSolarSystem>(); var sql = "SELECT PublisherName, WormholeName, Reward, LoginDate FROM LostAndFoundWormholes ORDER BY LoginDate DESC"; var data = SqlComm.SqlDataTable(sql); foreach (DataRow row in data.Rows) { result.Add(new LostSolarSystem { Publisher = row["PublisherName"].ToString(), Reward = row["Reward"].ToString(), Name = row["WormholeName"].ToString(), Date = row["LoginDate"] is DateTime ? (DateTime)row["LoginDate"] : new DateTime() }); } return(result); }