public static string GetAddByID(int id)
{
string sql = "SELECT parkAddress From parks WHERE parkID=" + id + ";";
DataTable dt = MyAdoHelper.ExecuteDataTable(fileName, sql);
return(dt.Rows[0].ItemArray[0].ToString());
}
public static int buildDog(Dog d)//מוסיפה כלב ומחזירה את המספר שלו
{
string sqlS = "INSERT INTO dogg (DogName,OwenrID,birthYear,color,pictureDog,dogsize,preferSize,preferAge,Gender,playWithMales,playWithFemale,Dog_breedID) VALUES ('" + d.GetName() + "'," + d.GetOwnerID() + "," + d.GetbirthYear() + ",'" + d.GetColor() + "','" + d.GetPictureDog() + "','" + d.GetSize() + "','" + d.GetPreferSize() + "'," + d.GetPreferAge() + ",'" + d.GetGender() + "'," + d.GetplayWithMale().ToString() + "," + d.GetplayWithFemale().ToString() + "," + d.GetDog_breedID() + ");";
MyAdoHelper.DoQuery(fileName, sqlS);
return(MaxDOG_id());
}
public static DataTable GetDataWithoutCurrent(int ownerID)
{
string sql1 = "SELECT * FROM dogg WHERE OwenrID <> " + ownerID + ";";
DataTable dt = MyAdoHelper.ExecuteDataTable(fileName, sql1);
return(dt);
}
protected void del(Object sender, EventArgs e)
{
string fileName = "db1.mdb";
string sqldel = "DELETE * FROM entrys;";
MyAdoHelper.DoQuery(fileName, sqldel);
}
public static string GetDogFromDogID2(int id)
{
string sql1 = "SELECT * FROM dogg WHERE DogID=" + id + ";";
DataTable dt = MyAdoHelper.ExecuteDataTable(fileName, sql1);
return(dt.Rows[0].ItemArray[0].ToString());
}
protected void Page_Load(object sender, EventArgs e)
{
if (Request.Form["submit"] != null)
{
string user = Request.Form["username"];
string password = Request.Form["password"];
string isExUsername = "******" + tableName + " WHERE username = '******' AND pass = '******'";
if (IsUserAndPaswordExist(user, password))
{
DataTable dt = MyAdoHelper.ExecuteDataTable(fileName, isExUsername);
if (((bool)dt.Rows[0]["isAdmin"]))
{
Session["admin"] = "True";
}
else
{
Session["admin"] = "False";
}
Session["username"] = user;
Session["fname"] = dt.Rows[0]["fname"];
Session["lname"] = dt.Rows[0]["lname"];
Response.Redirect("Default.aspx");
}
else
{
incor = "alert('user or pasword are incorrect')";
}
}
}
protected int formerBreed(int index)
{
string sql = "SELECT breeds.breedID FROM breeds INNER JOIN loveBreed ON loveBreed.breedID = breeds.breedID WHERE loveBreed.dogID = " + ((Dog)Session["dogi"]).GetDogID() + "; ";
DataTable dt = MyAdoHelper.ExecuteDataTable("masadPjct.accdb", sql);
return(int.Parse(dt.Rows[index].ItemArray[0].ToString()));
}
public void SaveRideOfferCoordinate(int rideOfferId, string xml)
{
var conn = MyAdoHelper.ConnectToDb("Database.mdf");
try
{
var param = new System.Data.SqlClient.SqlParameter[2];
param[0] = new System.Data.SqlClient.SqlParameter("rideOfferId", SqlDbType.Int);
param[0].Value = rideOfferId;
param[1] = new System.Data.SqlClient.SqlParameter("coordinatesXml", SqlDbType.NText);
param[1].Value = xml;
SqlHelper.ExecuteScalar(conn, CommandType.StoredProcedure, "SaveRideOfferCoordinates", param);
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (Request.Form["submit"] != null)
{
string uName = Request.Form["username"];
string pWord = Request.Form["password"];
string selectQuery = "SELECT * FROM people WHERE username = '******' AND password = '******';";
if (MyAdoHelper.IsExist(selectQuery))
{
Session["uName"] = uName;
Session["pWord"] = pWord;
string check = "Select * From people Where username = '******' and IsAdmin = 'True'";
if (MyAdoHelper.IsExist(check))
{
Session["IsAdmin"] = "True";
Response.Redirect("adminmanage.aspx");
}
else
{
Session["IsAdmin"] = "False";
Response.Redirect("homepage.aspx");
}
}
else
{
RegStatus = "The username or the password is not correct <br/> <a href='login.aspx'>Try again</a>";
}
}
}
public DataTable getMatchRideOffer(int rideOfferId)
{
var conn = MyAdoHelper.ConnectToDb("Database.mdf");
try
{
DataSet ds = new DataSet();
var param = new System.Data.SqlClient.SqlParameter[1];
param[0] = new System.Data.SqlClient.SqlParameter("rideOfferId", SqlDbType.Int);
param[0].Value = rideOfferId;
ds = SqlHelper.ExecuteDataset(conn, CommandType.StoredProcedure, "GetMatchRideOffer", param);
return(ds.Tables[0]);
}
catch (Exception ex)
{
throw ex;
return(null);
}
finally
{
conn.Close();
}
}
protected void Page_Load(object sender, EventArgs e)
{
name = Request.Form["username"];
String password = Request.Form["password"];
if (name != null && password != null)
{
String nameCheck = String.Format("SELECT * FROM [User] WHERE UserName='******'", name);
bool nameExist = MyAdoHelper.Exists(nameCheck);
if (nameExist)
{
String passwordCheck = String.Format("SELECT [Password] FROM [User] WHERE UserName='******'", name);
passwordCheck = MyAdoHelper.getSelectInString(passwordCheck);
if (password == passwordCheck)
{
Session["username"] = name;
logInErr = "";
Response.Redirect("HomePage.aspx");
}
else
{
logInErr = "password is incorrect";
}
}
else
{
logInErr = "username not found";
}
}
}
protected void AddFriend(string id)
{
if (id == Session["index"].ToString())
{
Response.Redirect("/profile.aspx?id=" + id + "&m=self", false);
HttpContext.Current.ApplicationInstance.CompleteRequest();
}
else if (MyAdoHelper.IsExist(string.Format("SELECT * FROM `tblFriends` WHERE `user1_id` = {0} AND `user2_id` = {1} AND `mode` = 'friends';", int.Parse(Session["index"].ToString()), int.Parse(id))))
{
Response.Redirect("/profile.aspx?id=" + id + "&m=already", false);
HttpContext.Current.ApplicationInstance.CompleteRequest();
}
else if (MyAdoHelper.IsExist(string.Format("SELECT * FROM `tblFriends` WHERE `user1_id` = {0} AND `user2_id` = {1} AND `mode` = 'sent';", int.Parse(Session["index"].ToString()), int.Parse(id))))
{
Response.Redirect("/profile.aspx?id=" + id + "&m=many", false);
HttpContext.Current.ApplicationInstance.CompleteRequest();
}
else if (MyAdoHelper.IsExist("SELECT * FROM `tblFriends` WHERE `user1_id` = " + int.Parse(Session["index"].ToString()) + " AND `user2_id` = " + int.Parse(id) + " AND `mode` = 'pending'"))
{
MyAdoHelper.DoQuery("UPDATE `tblFriends` SET `mode` = 'friends' WHERE `user1_id` = " + int.Parse(Session["index"].ToString()) + "AND `user2_id` = " + int.Parse(id) + ";");
MyAdoHelper.DoQuery("UPDATE `tblFriends` SET `mode` = 'friends' WHERE `user2_id` = " + int.Parse(Session["index"].ToString()) + "AND `user1_id` = " + int.Parse(id) + ";");
Response.Redirect("/profile.aspx?id=" + id + "&m=friends", false);
HttpContext.Current.ApplicationInstance.CompleteRequest();
}
else
{
MyAdoHelper.DoQuery(string.Format("INSERT INTO `tblFriends` (`user1_id`, `user2_id`, `mode`) VALUES ({0}, {1}, 'sent');", int.Parse(Session["index"].ToString()), int.Parse(id)));
MyAdoHelper.DoQuery(string.Format("INSERT INTO `tblFriends` (`user1_id`, `user2_id`, `mode`) VALUES ({0}, {1}, 'pending');", int.Parse(id), int.Parse(Session["index"].ToString())));
Response.Redirect("/profile.aspx?id=" + id + "&m=sent", false);
HttpContext.Current.ApplicationInstance.CompleteRequest();
}
Response.Write("ok");
}
protected void Page_Load(object sender, EventArgs e)
{
string tableName = "personalData"; //שם הטבלה
string selectQuery = "SELECT * FROM " + tableName; // שאילתת בחירה
DataTable table = MyAdoHelper.ExecuteDataTable(selectQuery);
int length = table.Rows.Count; // מספר הרשומות בטבלה
if (length > 0) // אם נמצאו רשומות
{
usersList += "<table style='border:3px double red' ;>"; // פתיחת טבלה והכנת כותרת לנתונים המוצגים
usersList += "<tr >";
usersList += "<th style='border:1px solid blue' ;>תעודת זהות</th>";
usersList += "<th style='border:1px solid blue' ;>שם פרטי</th>";
usersList += "<th style='border:1px solid blue' ;>שם משפחה</th>";
usersList += "<th style='border:1px solid blue' ;>ססמה</th>";
usersList += "</tr>";
for (int i = 0; i < length; i++) // הדפסת הרשומות
{ // הדפסת כל הרשומות בטבלה
usersList += "<tr>";
usersList += "<td style='border:1px solid red' ;>" + table.Rows[i]["idNum"] + "</td>";
usersList += "<td style='border:1px solid red' ;>" + table.Rows[i]["fName"] + "</td>";
usersList += "<td style='border:1px solid red' ;>" + table.Rows[i]["lName"] + "</td>";
usersList += "<td style='border:1px solid red' ;> " + table.Rows[i]["passwd"] + "</td>";
usersList += "</tr>";
}
usersList += "</table>";// סגירת טבלה
usersCount = length.ToString();
}
}
public static DataTable GetNameParksAndID()
{
string sql = "SELECT name_,parkID From parks;";
DataTable dt = MyAdoHelper.ExecuteDataTable(fileName, sql);
return(dt);
}
public void UpdatePictureIdentityInUser(string picture, int userIdentity)
{
var conn = MyAdoHelper.ConnectToDb("Database.mdf");
try
{
var param = new System.Data.SqlClient.SqlParameter[2];
param[0] = new System.Data.SqlClient.SqlParameter("userId", SqlDbType.Int);
param[0].Value = userIdentity;
param[1] = new System.Data.SqlClient.SqlParameter("picture", SqlDbType.NVarChar);
param[1].Value = picture;
SqlHelper.ExecuteScalar(conn, CommandType.StoredProcedure, "UpdatePictureInUser", param);
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
protected void Page_Load(object sender, EventArgs e)
{
string tableName = "personalData";//שם הטבלה
string selectQuery = "";
string sql = "";
string idNum = Request.Form["idEdit"];
string pass = Request.Form["oldpassEdit"];
string repass = Request.Form["newpassEdit"];
if (Request.Form["subEdit"] != null)
{
selectQuery = "SELECT * FROM " + tableName + " WHERE idNum='" + idNum + "' AND passwd='" + pass + "'";
sql = "UPDATE " + tableName + " SET passwd='" + repass + "' WHERE idNum='" + idNum + "' AND passwd='" + pass + "'";
if (MyAdoHelper.IsExist(selectQuery))
{
MyAdoHelper.DoQuery(sql);
userMsg = "הפרטים עודכנו";
}
else
{
userMsg = "מספר תז לא תקין או הססמה שגויה";
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (Request.Form["loginSubmit"] != null)
{
string sql, fname;
string dbFileName = "MyFirstDB.accdb";
string userMail = Request.Form["userMail"];
string userPwd = Request.Form["userPwd"];
//בדיקה האם קיימת רשומה בטבלה עם אותם ערכים בשדות דואל וסיסמה
sql = "SELECT * FROM tbl_users WHERE userMail = '" + userMail + "' AND userPwd = '" + userPwd + "'";
// שליפת השם הפרטי של המשתמש מתוך הרשומה במידה ונמצאה התאמה בבסיס הנתונים
fname = MyAdoHelper.GetItemRowData(dbFileName, sql, 2);
if (fname != "") //הצלחה
{
Session["userName"] = fname;
Response.Redirect("HomePage.aspx"); //ניתוב לדף הבית
}
else //כשלון
{
Response.Redirect("Login.aspx?code=1");
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
string tableName = "people";//שם הטבלה
string selectQuery = "";
string sql = "";
string username = Request.Form["username"];
string fname = Request.Form["fnameedit"];
string lname = Request.Form["lnameedit"];
string age = Request.Form["ageedit"];
if (Request.Form["update"] != null)
{
if (Request.Form["username"] != "admin")
{
selectQuery = "SELECT * FROM " + tableName + " WHERE username='******'";
sql = "UPDATE " + tableName + " SET fname='" + fname + "',lname='" + lname + "', age='" + age + "' WHERE username='******'";
if (MyAdoHelper.IsExist(selectQuery))
{
MyAdoHelper.DoQuery(sql);
userMsg = "The user has updated!";
}
else
{
userMsg = "Wrong username";
}
}
else
{
userMsg = "You can't update an Admin";
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
string selectQuery = "SELECT fName FROM personalData GROUP BY fName";
//string selectQuery = "SELECT * FROM " + tableName.ToString() + " ORDER BY idNum"; // שאילתת בחירה ממויינת לפי תז
//string selectQuery = "SELECT * FROM " + tableName; // שאילתת בחירה
DataTable table = MyAdoHelper.ExecuteDataTable(selectQuery);
int length = table.Rows.Count; // מספר הרשומות בטבלה
if (length > 0) // אם נמצאו רשומות
{
usersList += "<select id='fName' name='fName'>"; // יצירת רשימה נגללת
for (int i = 0; i < length; i++) // הדפסת הרשומות
{ // הדפסת כל הרשומות בטבלה
usersList += "<option >";
usersList += table.Rows[i]["fName"].ToString();
usersList += "</option>";
}
usersList += "</select>";
}
if (Request.Form["showFname"] != null)
{
string fName = Request.Form["fName"];
string sql = "SELECT * FROM personalData WHERE fName = '" + fName + "'";
userMsg = MyAdoHelper.printDataTable(sql).ToString();
}
}
public void UpdateFacebookUserDetails(Users user)
{
var conn = MyAdoHelper.ConnectToDb("Database.mdf");
try
{
var param = new System.Data.SqlClient.SqlParameter[5];
param[0] = new System.Data.SqlClient.SqlParameter("facebookId", SqlDbType.BigInt);
param[0].Value = user.GetFacebookId();
param[1] = new System.Data.SqlClient.SqlParameter("name", SqlDbType.NVarChar);
param[1].Value = user.GetName();
param[2] = new System.Data.SqlClient.SqlParameter("email", SqlDbType.NVarChar);
param[2].Value = user.GetEmail();
param[3] = new System.Data.SqlClient.SqlParameter("picture", SqlDbType.NVarChar);
param[3].Value = user.GetPicture();
param[4] = new System.Data.SqlClient.SqlParameter("phone", SqlDbType.NVarChar);
param[4].Value = user.GetPhone();
SqlHelper.ExecuteScalar(conn, CommandType.StoredProcedure, "UpdateFacebookUser", param);
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
private string UserRegister(string username, string fullName, string password, string email, string re_password)
{
if (MyAdoHelper.IsExist("SELECT * FROM `tblUsers` WHERE `fldUsername` = '" + username + "'"))
{
return("Username Exists");
}
if (MyAdoHelper.IsExist("SELECT * FROM `tblUsers` WHERE `fldEmail` = '" + email + "'"))
{
return("Email Already Exists");
}
if (password != re_password)
{
return("Passwords Do Not Match");
}
if (password.Length < 8)
{
return("Invalid Password Length");
}
if (!IsValidEmail(email))
{
return("Invalid Email");
}
MyAdoHelper.ExecuteDataTable(string.Format("INSERT INTO `tblUsers` (`fldUsername`, `fldEmail`, `fldPassword`, `fldFullName`, `fldBio`, `fldDescription`, `fldProfilePic`) VALUES ('{0}', '{1}', '{2}', '{3}', 'KatomBook User', 'Hey there!\nI am using KatomBook.', 'defprofile.png');", username, email, password, fullName));
return("Created");
}
public bool IsUserEmailExist(string email)
{
var conn = MyAdoHelper.ConnectToDb("Database.mdf");
try
{
var param = new System.Data.SqlClient.SqlParameter[1];
param[0] = new System.Data.SqlClient.SqlParameter("email", SqlDbType.NVarChar);
param[0].Value = email;
int tmp = Convert.ToInt32(SqlHelper.ExecuteScalar(conn, CommandType.StoredProcedure, "IsUserEmailExist", param));
if (tmp > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
public static Dog GetDogFromDogID(int id)
{
string sql1 = "SELECT * FROM dogg WHERE DogID=" + id + ";";
DataTable dt = MyAdoHelper.ExecuteDataTable("masadPjct.accdb", sql1);
return(GetDogFromDataRow(dt.Rows[0]));
}
public DataSet IsUserExistByEmailPass(string email, string password)
{
var conn = MyAdoHelper.ConnectToDb("Database.mdf");
try
{
var param = new System.Data.SqlClient.SqlParameter[2];
param[0] = new System.Data.SqlClient.SqlParameter("email", SqlDbType.NVarChar);
param[0].Value = email;
param[1] = new System.Data.SqlClient.SqlParameter("password", SqlDbType.NVarChar);
param[1].Value = password;
DataSet ds = new DataSet();
ds = SqlHelper.ExecuteDataset(conn, CommandType.StoredProcedure, "IsUserExistByEmailPass", param);
return(ds);
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
public static int MaxDOG_id()
{
string sql1 = "SELECT MAX(DogID) FROM dogg;";
DataTable dt = MyAdoHelper.ExecuteDataTable(fileName, sql1);
return(int.Parse(dt.Rows[0].ItemArray[0].ToString()));
}
public DataSet GetDetails(int userId)
{
var conn = MyAdoHelper.ConnectToDb("Database.mdf");
try
{
var param = new System.Data.SqlClient.SqlParameter[1];
param[0] = new System.Data.SqlClient.SqlParameter("userId", SqlDbType.Int);
param[0].Value = userId;
DataSet ds = new DataSet();
ds = (SqlHelper.ExecuteDataset(conn, CommandType.StoredProcedure, "GetUserDetails", param));
return(ds);
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
public static DataTable GetDogByOwnerID(int ownerID)
{
string sql1 = "SELECT * FROM dogg WHERE OwenrID=" + ownerID + ";";
DataTable dt = MyAdoHelper.ExecuteDataTable(fileName, sql1);
return(dt);
}
public int InsertUserWithoutPicture(Users user)
{
var conn = MyAdoHelper.ConnectToDb("Database.mdf");
try
{
var param = new System.Data.SqlClient.SqlParameter[5];
param[0] = new System.Data.SqlClient.SqlParameter("name", SqlDbType.NVarChar);
param[0].Value = user.GetName();
param[1] = new System.Data.SqlClient.SqlParameter("email", SqlDbType.NVarChar);
param[1].Value = user.GetEmail();
param[2] = new System.Data.SqlClient.SqlParameter("userPassword", SqlDbType.NVarChar);
param[2].Value = user.GetUserPassword();
param[3] = new System.Data.SqlClient.SqlParameter("gender", SqlDbType.Int);
param[3].Value = user.GetGender();
param[4] = new System.Data.SqlClient.SqlParameter("phone", SqlDbType.NVarChar);
param[4].Value = user.GetPhone();
return(Convert.ToInt32(SqlHelper.ExecuteScalar(conn, CommandType.StoredProcedure, "InsertUserWithoutPicture", param).ToString()));
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
public static void change(string tatID, string ID)
{
string tableName = "TAT" + "10_" + ID;
string fileName = "db1.mdb";
DataTable dtID, dtat;
//לעדכן בטבלה אישית ++
//לבדוק שהיה אפס קודם
string sql1 = "SELECT * FROM ID" + ID + " WHERE subjectID='10';"; //עדכון טבלה אישית מרכזית
string sql2 = "SELECT * FROM TAT10_" + ID + " WHERE ID='" + tatID + "';"; //בדיקה האם התרגיל כבר בוצע
dtID = MyAdoHelper.ExecuteDataTable(fileName, sql1);
dtat = MyAdoHelper.ExecuteDataTable(fileName, sql2);
int cou = int.Parse(dtID.Rows[0][4].ToString());
cou++;
bool iff = Convert.ToBoolean(dtat.Rows[0][3].ToString());
if (!iff)
{
string sql = "UPDATE " + tableName + " SET iscomplete='1' WHERE ID='" + tatID + "';";
MyAdoHelper.DoQuery(fileName, sql);
string sql3 = "UPDATE ID" + ID + " SET cou='" + cou + "' WHERE subjectID='10';";
MyAdoHelper.DoQuery(fileName, sql3);
}
}
protected void Page_Load(object sender, EventArgs e)
{
string tableName = "personalData";//שם הטבלה
string selectQuery = "";
if (Request.Form["del_id"] != null)
{
selectQuery = "SELECT * FROM " + tableName;//נבדוק אם מספר תז קיים
selectQuery += " WHERE ";
selectQuery += " idNum = '" + Request.Form["idNum"] + "'";
//בדיקה האם תז קיימת במסד
if (MyAdoHelper.IsExist(selectQuery))
{
//אם קיים נבצע מחיקה
string idNum = Request.Form["idNum"];
string sql = "DELETE FROM personalData WHERE idNum = '" + idNum + "'";
//ביצוע והצגת מספר הרשומות שנמחקו
userMsg = MyAdoHelper.RowsAffected(sql).ToString() + " רשומה/רשומות נמחקו ";
//מחיקת הרשומות בלבד
//MyAdoHelper.DoQuery(sql);
//userMsg = " הרשומה נמחקה";
}
else
{
userMsg = "תז לא קיימת";
}
}
}
