/// <summary>
/// Это метод используется чаще всего
/// </summary>
/// <param name="ImageID"></param>
/// <param name="CommentPeopleID"></param>
/// <param name="text"></param>
public void AddCommentToImage(int ImageID, int CommentPeopleID, string text)
{
if (text == null || text == "")
{
return;//если пустая строка то не добавляем
}
SQLConnectionClass oConnection = new SQLConnectionClass();
SQLCommandClass oCommand = new SQLCommandClass();
TableOpreatorClass TablePerator = new TableOpreatorClass();
int ID = this.GenerateNewID(ImageID);//Последовательность номеров для каждой картинки
TableOpreatorClass oTable = new TableOpreatorClass();
int ImgCommentID = oTable.GenerateNewID("ImgCommentID", "ImageComment");//Уникальный номер для каждой картинки
string sqlIns = "INSERT INTO ImageComment( ID, ImgCommentID, ImageID, CommentPeopleID, Text ) VALUES (@ID ,@ImgComID, @ImageID, @CommentPeopleID, @Text )";
//использовать только в такой последовательности
oCommand.PrepareInsertQuery(sqlIns, oConnection.connection);
oCommand.AddInsertParameter("@ID", ID); //заносим новый айди
oCommand.AddInsertParameter("@ImgComID", ImgCommentID); //заносим новый айди
oCommand.AddInsertParameter("@ImageID", ImageID);
oCommand.AddInsertParameter("@CommentPeopleID", CommentPeopleID);
oCommand.AddInsertParameter("@Text", text);
oCommand.ExecuteQuery();
oConnection.closeConnection();
}
public int GetImgCommentID(int ID, int imgID)
{
int returnVal = 0;
SQLConnectionClass oConnection = new SQLConnectionClass();
SQLCommandClass oCommand = new SQLCommandClass();
// string sqlIns = "INSERT INTO table (name, information, other) VALUES (@name, @information, @other)";
string queryString = "SELECT ImgCommentID FROM ImageComment Where (ID=@ID) AND (ImageID=@ImageID)";
oCommand.PrepareSelectQuery(queryString, oConnection.connection);
oCommand.AddSelectParam("@ID", ID); //Select Parameter
oCommand.AddSelectParam("@ImageID", imgID); //Select Parameter
SqlDataReader reader = oCommand.ExectuteReader();
while (reader.Read())
{
if ((reader[0] != null) && (!Convert.IsDBNull(reader[0])))
{
returnVal = Convert.ToInt32(reader[0]);//
}
}
oConnection.closeConnection();
return(returnVal);
}
public bool isFriendsExist(int ID_1, int ID_2)
{
SQLConnectionClass oConnection = new SQLConnectionClass();
SQLCommandClass oCommand = new SQLCommandClass();
// string sqlIns = "INSERT INTO table (name, information, other) VALUES (@name, @information, @other)";
// string queryString = "SELECT ID FROM FriendTable" + " Where( (Friend_1_ID=@Fr_1) AND (Friend_2_ID=@Fr_2) ) or ( (Friend_1_ID=@Fr_2) AND (Friend_2_ID=@Fr_1) )";
string queryString = "SELECT ID FROM FriendTable Where( (Friend_1_ID=@Fr_1) AND (Friend_2_ID=@Fr_2) )";
oCommand.PrepareSelectQuery(queryString, oConnection.connection);
oCommand.AddSelectParam("@Fr_1", ID_1); //Select Parameter
oCommand.AddSelectParam("@Fr_2", ID_2); //Select Parameter
// oCommand.PrepareSelectQuery(queryString, oConnection.connection);
SqlDataReader reader = oCommand.ExectuteReader();
bool returnValue = false;
if (reader.Read())
{
returnValue = true; //найдены друзья
}
oConnection.closeConnection();
return(returnValue);
}
/// <summary>
/// возвращает номера таблиц в которых содержатся номера друзей
/// </summary>
/// <param name="PeopleID"></param>
/// <returns></returns>
public List <int> GetFriendIDList(int PeopleID)
{
List <int> FriendListIDs = new List <int>();;//очищаем чтобы заполнить заново
SQLConnectionClass oConnection = new SQLConnectionClass();
SQLCommandClass oCommand = new SQLCommandClass();
// string sqlIns = "INSERT INTO table (name, information, other) VALUES (@name, @information, @other)";
string queryString = "SELECT ID FROM FriendTable Where(Friend_1_ID=@PeopleID) OR (Friend_2_ID=@PeopleID)";
oCommand.PrepareSelectQuery(queryString, oConnection.connection);
oCommand.AddSelectParam("@PeopleID", PeopleID);//Select Parameter
SqlDataReader reader = oCommand.ExectuteReader();
while (reader.Read())
{
int id = Convert.ToInt32(reader[0]);
FriendListIDs.Add(id);//считывание в массив юрэлов
}
oConnection.closeConnection();
return(FriendListIDs);
}
public bool isLoginExist(string Login)
{
SQLConnectionClass oConnection = new SQLConnectionClass();
SQLCommandClass oCommand = new SQLCommandClass();
// string sqlIns = "INSERT INTO table (name, information, other) VALUES (@name, @information, @other)";
string queryString = "SELECT Phone FROM Peoples";
oCommand.PrepareSelectQuery(queryString, oConnection.connection);
SqlDataReader reader = oCommand.ExectuteReader();
bool returnValue = false;
while (reader.Read())
{
string phone = Convert.ToString(reader[0]);
if (phone == Login)
{
//совпадение найдено
returnValue = true;// такой логин существует
continue;
}
}
oConnection.closeConnection();
return(returnValue);
}
public void getPeopleIDs(string name, string surname, string tableName)
{
if (PeopleIDs != null)
{
PeopleIDs = new List <int>();;//очищаем чтобы заполнить заново
}
SQLConnectionClass oConnection = new SQLConnectionClass();
SQLCommandClass oCommand = new SQLCommandClass();
// string sqlIns = "INSERT INTO table (name, information, other) VALUES (@name, @information, @other)";
string queryString = "SELECT ID FROM " + tableName + " Where(Name=@Name) AND (Surname=@Surname)";
oCommand.PrepareSelectQuery(queryString, oConnection.connection);
oCommand.AddSelectParam("@Name", name); //Select Parameter
oCommand.AddSelectParam("@Surname", surname); //Select Parameter
SqlDataReader reader = oCommand.ExectuteReader();
while (reader.Read())
{
int id = Convert.ToInt32(reader[0]);
PeopleIDs.Add(id);//считывание в массив юрэлов
}
counter = 0;
oConnection.closeConnection();
}
/// <summary>
/// Возвращает номер друга
/// </summary>
/// <param name="userID"></param>
/// <param name="FriendListID"></param>
/// <returns></returns>
public int GetFriendID(int userID, int FriendListID)
{
SQLConnectionClass oConnection = new SQLConnectionClass();
SQLCommandClass oCommand = new SQLCommandClass();
int returnValue = 0;
// string sqlIns = "INSERT INTO table (name, information, other) VALUES (@name, @information, @other)";
string queryString = "SELECT Friend_1_ID, Friend_2_ID FROM FriendTable Where(ID=@FriendListID)";
oCommand.PrepareSelectQuery(queryString, oConnection.connection);
oCommand.AddSelectParam("@FriendListID", FriendListID);//Select Parameter
SqlDataReader reader = oCommand.ExectuteReader();
if (reader.Read())
{
int FirstId = Convert.ToInt32(reader[0]);
int SecondId = Convert.ToInt32(reader[1]);
if (userID == FirstId)
{
returnValue = SecondId;
}
else
{
returnValue = FirstId;
}
}
oConnection.closeConnection();
return(returnValue);
}
public void AddFriend(int ID_1, int ID_2)
{
SQLConnectionClass oConnection = new SQLConnectionClass();
SQLCommandClass oCommand = new SQLCommandClass();
TableOpreatorClass TablePerator = new TableOpreatorClass();
if (isFriendsExist(ID_1, ID_2))
{
return;// они уже друзья. Не надо заново добавлять друзей
}
int ID = TablePerator.GenerateNewID("ID", "FriendTable");
string sqlIns = "INSERT INTO FriendTable( ID, Friend_1_ID, Friend_2_ID ) VALUES (@ID, @Friend_1, @Friend_2 )";
//использовать только в такой последовательности
oCommand.PrepareInsertQuery(sqlIns, oConnection.connection);
int newIDPeoples = ID_OPerator.createNewTableID("ID", "Peoples");
if (newIDPeoples == -2)
{
//некорректные параметры
}
oCommand.AddInsertParameter("@ID", ID);//заносим новый айди
oCommand.AddInsertParameter("@Friend_1", ID_1);
oCommand.AddInsertParameter("@Friend_2", ID_2);
oCommand.ExecuteQuery();
oConnection.closeConnection();
}
public string ReadeDialogMessage(int dialogID, int messageID)
{
SQLConnectionClass oConnection = new SQLConnectionClass();
SQLCommandClass oCommand = new SQLCommandClass();
// string sqlIns = "INSERT INTO table (name, information, other) VALUES (@name, @information, @other)";
string queryString = "SELECT MessageText FROM Messages Where (DialogID=@DialogID) and (MessageID = @MessageID)";
oCommand.PrepareSelectQuery(queryString, oConnection.connection);
oCommand.AddSelectParam("@DialogID", dialogID); //Select Parameter
oCommand.AddSelectParam("@MessageID", messageID); //Select Parameter
SqlDataReader reader = oCommand.ExectuteReader();
string messageText = null;
if (reader.Read())
{
messageText = Convert.ToString(reader[0]);
}
oConnection.closeConnection();
return(messageText);
// listBox.Items.add
}
//получаем максимальный номер
public int GetMaxDialogID()
{
SQLConnectionClass oConnection = new SQLConnectionClass();
SQLCommandClass oCommand = new SQLCommandClass();
// string sqlIns = "INSERT INTO table (name, information, other) VALUES (@name, @information, @other)";
string queryString = "SELECT DialogID FROM Messages";
oCommand.PrepareSelectQuery(queryString, oConnection.connection);
SqlDataReader reader = oCommand.ExectuteReader();
int max = 0;
int DialogID = -1;
while (reader.Read())
{
DialogID = Convert.ToInt32(reader[0]);
if (DialogID > max)
{
max = DialogID;
}
}
//если диалогов нет то будет нулевым диалогом
oConnection.closeConnection();
return(max);
}
public string ReadePersonSurname(int dialogID, int messageID)
{
SQLConnectionClass oConnection = new SQLConnectionClass();
SQLCommandClass oCommand = new SQLCommandClass();
// string sqlIns = "INSERT INTO table (name, information, other) VALUES (@name, @information, @other)";
string queryString = "SELECT FromUserID FROM Messages Where (DialogID=@DialogID) and (MessageID = @MessageID)";
oCommand.PrepareSelectQuery(queryString, oConnection.connection);
oCommand.AddSelectParam("@DialogID", dialogID); //Select Parameter
oCommand.AddSelectParam("@MessageID", messageID); //Select Parameter
SqlDataReader reader = oCommand.ExectuteReader();
int PersonID = 0;
string PersonSurname = null;
if (reader.Read())
{
PersonID = Convert.ToInt32(reader[0]);
}
oConnection.closeConnection();
PeoplesClass oPeoples = new PeoplesClass();
PersonSurname = Convert.ToString(oPeoples.GetPeopleValue(PersonID, "Surname"));
return(PersonSurname);
}
public int getMaxMessageID(int dialogID)
{
SQLConnectionClass oConnection = new SQLConnectionClass();
SQLCommandClass oCommand = new SQLCommandClass();
// string sqlIns = "INSERT INTO table (name, information, other) VALUES (@name, @information, @other)";
string queryString = "SELECT MessageID FROM Messages Where (DialogID=@DialogID)";
oCommand.PrepareSelectQuery(queryString, oConnection.connection);
oCommand.AddSelectParam("@DialogID", dialogID);//Select Parameter
SqlDataReader reader = oCommand.ExectuteReader();
int maxID = 0;
int messageID = 0;
while (reader.Read())
{
messageID = Convert.ToInt32(reader[0]);
if (messageID > maxID)
{
maxID = messageID;
}
}
oConnection.closeConnection();
return(maxID);
}
public int GetDialogID(int FirstPersonID, int SecondPersonID) //проверить
{
SQLConnectionClass oConnection = new SQLConnectionClass();
SQLCommandClass oCommand = new SQLCommandClass();
// string sqlIns = "INSERT INTO table (name, information, other) VALUES (@name, @information, @other)";
string queryString = "SELECT DialogID FROM Messages Where((FromUserID=@FirstID) and (ToUserID = @SecondID) ) or ( (FromUserID = @SecondID) and (ToUserID = @FirstID))";
oCommand.PrepareSelectQuery(queryString, oConnection.connection);
oCommand.AddSelectParam("@FirstID", FirstPersonID); //Select Parameter
oCommand.AddSelectParam("@SecondID", SecondPersonID); //Select Parameter
SqlDataReader reader = oCommand.ExectuteReader();
int DialogID = -1;
if (reader.Read())
{
DialogID = Convert.ToInt32(reader[0]);
}
oConnection.closeConnection();
return(DialogID);
}
// считаем количество лайков для картинки или для поста
public int GetLikeCount(int GroupID /*ImageID or PublicID*/, string GroupTableName /*Images or Publics*/)
{
SQLConnectionClass oConnection = new SQLConnectionClass();
SQLCommandClass oCommand = new SQLCommandClass();
// string sqlIns = "INSERT INTO table (name, information, other) VALUES (@name, @information, @other)";
string queryString = "SELECT IDinGroup FROM Like WHERE( GroupTableName=@GroupTableName ) and ( GroupID=@GroupID )";
// string queryString = "SELECT GroupID FROM Like";// WHERE( GroupTableName=@GroupTableName ) and ( GroupID=@GroupID )";
oCommand.PrepareSelectQuery(queryString, oConnection.connection);
oCommand.AddSelectParam("@GroupTableName", GroupTableName); //Select Parameter
oCommand.AddSelectParam("@GroupID", GroupID); //Select Parameter
SqlDataReader reader = oCommand.ExectuteReader();
int likeCount = 0;
while (reader.Read())
{
likeCount++;// считаем количество лайков для картинки или для поста
}
oConnection.closeConnection();
return(likeCount);
}
public int GetMaxID(int ImageID)
{
SQLConnectionClass oConnection = new SQLConnectionClass();
SQLCommandClass oCommand = new SQLCommandClass();
// string sqlIns = "INSERT INTO table (name, information, other) VALUES (@name, @information, @other)";
string queryString = "SELECT ID FROM ImageComment Where ImageID=@ImageID";
oCommand.PrepareSelectQuery(queryString, oConnection.connection);
oCommand.AddSelectParam("@ImageID", ImageID);//Select Parameter
SqlDataReader reader = oCommand.ExectuteReader();
int maxID = -1;//если в таблице совсем нет записей
int ID = 0;
while (reader.Read())
{
if ((reader[0] != null) && (!Convert.IsDBNull(reader[0])))
{
ID = Convert.ToInt32(reader[0]);//
if (maxID < ID)
{
maxID = ID;
}
}
}
oConnection.closeConnection();
return(maxID);
}
public String GetMessageText(int MessageID)// этот метод удалить
{
SQLConnectionClass oConnection = new SQLConnectionClass();
SQLCommandClass oCommand = new SQLCommandClass();
// string sqlIns = "INSERT INTO table (name, information, other) VALUES (@name, @information, @other)";
string queryString = "SELECT MessageText FROM Messages Where(MessageID=@MessageID)";
oCommand.PrepareSelectQuery(queryString, oConnection.connection);
oCommand.AddSelectParam("@MessageID", MessageID);//Select Parameter
SqlDataReader reader = oCommand.ExectuteReader();
string messageText = null;
if (reader.Read())
{
messageText = Convert.ToString(reader[0]);
}
oConnection.closeConnection();
return(messageText);
}
public void createNewUserIDCounter(int userID, string counterName, int value)//работает строго с таблицей UserCounters
{
SQLConnectionClass oConnection = new SQLConnectionClass();
SQLCommandClass oCommand = new SQLCommandClass();
string sqlIns = "INSERT INTO UserCounters( UserID, " + counterName + " ) " + " VALUES(@UserID, @Value )";
//использовать только в такой последовательности
oCommand.PrepareInsertQuery(sqlIns, oConnection.connection);
int newIDPeoples = ID_OPerator.createNewTableID("ID", "Peoples");
if (newIDPeoples == -2)
{
//некорректные параметры
}
oCommand.AddInsertParameter("@UserID", userID);//заносим новый айди
oCommand.AddInsertParameter("@Name", value);
oCommand.ExecuteQuery();
oConnection.closeConnection();
}
public void sendMessageFromUserID(int DialogId, int FromID, int ToID, String textMessage)
{
SQLConnectionClass oConnection = new SQLConnectionClass();
SQLCommandClass oCommand = new SQLCommandClass();
string sqlIns = "INSERT INTO Messages(DialogID, FromUserID, ToUserID, MessageID, MessageText) VALUES(@DialogID,@FromID, @ToID, @MessageID, @textMessage ) ";
//использовать только в такой последовательности
oCommand.PrepareInsertQuery(sqlIns, oConnection.connection);
oCommand.AddInsertParameter("@DialogID", DialogId);
oCommand.AddInsertParameter("@FromID", FromID);
oCommand.AddInsertParameter("@ToID", ToID);
//======это чтобы вычислить айди нового сообщения
this.getMessagesIDs(FromID, ToID);//получаем номера сообщений в прямую сторону и в обратную сторону
//айди сообщений получены
int maxMesID = this.GetMaxMessageID();//вычисляем айди нового сообщения. максим + 1.
//===============================================
oCommand.AddInsertParameter("@MessageID", maxMesID + 1);//new message id;
oCommand.AddInsertParameter("@textMessage", textMessage);
oCommand.ExecuteQuery();
oConnection.closeConnection();
}
/*
* поиск людей которым пользователь когда либо писал
*/
public String GetMessageText(int FromID, int ToID, int messageID /*1....N*/)
{
SQLConnectionClass oConnection = new SQLConnectionClass();
SQLCommandClass oCommand = new SQLCommandClass();
// string sqlIns = "INSERT INTO table (name, information, other) VALUES (@name, @information, @other)";
string queryString = "SELECT MessageText FROM Messages Where(FromUserID=@FromID) and (ToUserID = @ToID) and (MessageID = @MesID) ";
oCommand.PrepareSelectQuery(queryString, oConnection.connection);
oCommand.AddSelectParam("@FromID", FromID); //Select Parameter
oCommand.AddSelectParam("@ToID", ToID); //Select Parameter
oCommand.AddSelectParam("@MesID", messageID); //Select Parameter
SqlDataReader reader = oCommand.ExectuteReader();
string messageText = null;
if (reader.Read())
{
messageText = Convert.ToString(reader[0]);
}
oConnection.closeConnection();
return(messageText);
}
public int getMaxMessageID(int FromID, int ToID)
{
SQLConnectionClass oConnection = new SQLConnectionClass();
SQLCommandClass oCommand = new SQLCommandClass();
// string sqlIns = "INSERT INTO table (name, information, other) VALUES (@name, @information, @other)";
string queryString = "SELECT MessageID FROM Messages Where(FromUserID=@FromID) AND (ToUserID=@ToID)";
oCommand.PrepareSelectQuery(queryString, oConnection.connection);
oCommand.AddSelectParam("@FromID", FromID); //Select Parameter
oCommand.AddSelectParam("@ToID", ToID); //Select Parameter
SqlDataReader reader = oCommand.ExectuteReader();
int max = 0;
while (reader.Read())
{
int id = Convert.ToInt32(reader[0]);
if (max < id)
{
max = id;
}
}
oConnection.closeConnection();
return(max);
}
private void getNextMessagesIDs(int FromID, int ToID)
{
if (MessagesIDs == null)
{
MessagesIDs = new List <int>();
}
SQLConnectionClass oConnection = new SQLConnectionClass();
SQLCommandClass oCommand = new SQLCommandClass();
// string sqlIns = "INSERT INTO table (name, information, other) VALUES (@name, @information, @other)";
string queryString = "SELECT MessageID FROM Messages Where(FromUserID=@FromID) AND (ToUserID=@ToID)";
oCommand.PrepareSelectQuery(queryString, oConnection.connection);
oCommand.AddSelectParam("@FromID", FromID); //Select Parameter
oCommand.AddSelectParam("@ToID", ToID); //Select Parameter
SqlDataReader reader = oCommand.ExectuteReader();
while (reader.Read())
{
int id = Convert.ToInt32(reader[0]);
MessagesIDs.Add(id);//считывание в массив юрэлов
}
counter = 0;
oConnection.closeConnection();
}
public object GetValueFromTableByIDName(int ID, string IDName, String ColumnName, string tableName)
{
SQLConnectionClass oConnection = new SQLConnectionClass();
SQLCommandClass oCommand = new SQLCommandClass();
// string sqlIns = "INSERT INTO table (name, information, other) VALUES (@name, @information, @other)";
string queryString = "SELECT " + ColumnName + " FROM " + tableName + " Where " + IDName + "=@ID ";
oCommand.PrepareSelectQuery(queryString, oConnection.connection);
oCommand.AddSelectParam("@ID", ID);//Select Parameter
SqlDataReader reader = oCommand.ExectuteReader();
object returnValue = null;
if (reader.Read())
{
returnValue = reader[0] as object;
}
oConnection.closeConnection();
return(returnValue);
}
public int GetMaxIDInGroup(string IDName, string tableName, string GroupIDName, int GroupID)
{
SQLConnectionClass oConnection = new SQLConnectionClass();
SQLCommandClass oCommand = new SQLCommandClass();
// string sqlIns = "INSERT INTO table (name, information, other) VALUES (@name, @information, @other)";
string queryString = "SELECT " + IDName + " FROM " + tableName + " Where " + GroupIDName + "=@GroupID";
oCommand.PrepareSelectQuery(queryString, oConnection.connection);
oCommand.AddSelectParam("@GroupID", GroupID);//Select Parameter
SqlDataReader reader = oCommand.ExectuteReader();
int maxID = -1;
while (reader.Read())
{
int readVal = Convert.ToInt32(reader[0]);
if (readVal > maxID)
{
maxID = readVal;
}
}
oConnection.closeConnection();
return(maxID);
}
public int GetMaxID(string IDName, string TableName)// -1 если не найден ни один айди
{
SQLConnectionClass oConnection = new SQLConnectionClass();
SQLCommandClass oCommand = new SQLCommandClass();
// string sqlIns = "INSERT INTO table (name, information, other) VALUES (@name, @information, @other)";
string queryString = "SELECT " + IDName + " FROM " + TableName;
oCommand.PrepareSelectQuery(queryString, oConnection.connection);
SqlDataReader reader = oCommand.ExectuteReader();
int maxID = -1;
int ID = 0;
while (reader.Read())
{
if ((reader[0] != null) && (!Convert.IsDBNull(reader[0])))
{
ID = Convert.ToInt32(reader[0]); //
if (maxID < ID)
{
maxID = ID;
}
}
}
oConnection.closeConnection();
return(maxID);
}
public void readeImageIDs(int PeopleID, List <int> ImageIDsOut)
{
// ImageIDsOut = new List<int>();
SQLConnectionClass oConnection = new SQLConnectionClass();
SQLCommandClass oCommand = new SQLCommandClass();
// string sqlIns = "INSERT INTO table (name, information, other) VALUES (@name, @information, @other)";
string queryString = "SELECT ImageID FROM Images Where PeopleID=@PeopleID";
oCommand.PrepareSelectQuery(queryString, oConnection.connection);
oCommand.AddSelectParam("@PeopleID", PeopleID);//Select Parameter
SqlDataReader reader = oCommand.ExectuteReader();
while (reader.Read())
{
if (reader[0] != DBNull.Value)
{
ImageIDsOut.Add(Convert.ToInt32(reader[0]));//считывание в массив юрэлов
}
}
oConnection.closeConnection();
}
public string readeImageURl(int ImageID, int PeopleID) //reade from database to List
{
SQLConnectionClass oConnection = new SQLConnectionClass();
SQLCommandClass oCommand = new SQLCommandClass();
string returnValue = null;
// string sqlIns = "INSERT INTO table (name, information, other) VALUES (@name, @information, @other)";
string queryString = "SELECT imgURL_Path FROM Images Where (ImageID = @ImageID) AND ( PeopleID=@PeopleID )";
oCommand.PrepareSelectQuery(queryString, oConnection.connection);
oCommand.AddSelectParam("@PeopleID", PeopleID); //Select Parameter
oCommand.AddSelectParam("@ImageID", ImageID); //Select Parameter
SqlDataReader reader = oCommand.ExectuteReader();
while (reader.Read())
{
returnValue = (reader[0] as string);//считывание в массив юрэлов
}
oConnection.closeConnection();
return(returnValue);
}
public void readeImageURls(int UserID, string tableName) //reade from database to List
{
SQLConnectionClass oConnection = new SQLConnectionClass();
SQLCommandClass oCommand = new SQLCommandClass();
userID = UserID;
// string sqlIns = "INSERT INTO table (name, information, other) VALUES (@name, @information, @other)";
string queryString = "SELECT PeopleID, imgURL_Path FROM " + tableName + " Where PeopleID=@PeopleID";
oCommand.PrepareSelectQuery(queryString, oConnection.connection);
oCommand.AddSelectParam("@PeopleID", UserID);//Select Parameter
SqlDataReader reader = oCommand.ExectuteReader();
while (reader.Read())
{
imageUrls.Add(reader[1] as string);//считывание в массив юрэлов
}
counter = 0;
oConnection.closeConnection();
}
protected void enter(object sender, EventArgs e)//почемуто не переходит на другую страницу
{
SQLConnectionClass oConnection = new SQLConnectionClass();
SQLCommandClass oCommand = new SQLCommandClass();
// string sqlIns = "INSERT INTO table (name, information, other) VALUES (@name, @information, @other)";
string queryString = "SELECT ID, Name, Surname, Phone, Password FROM Peoples Where Phone=@phone";
oCommand.PrepareSelectQuery(queryString, oConnection.connection);
oCommand.AddSelectParam("@phone", this.TextBox6.Text);//Для сравнения
SqlDataReader reader = oCommand.ExectuteReader();
string phoneNumb;
string Password;
while (reader.Read())
{
phoneNumb = reader[3] as string; //приведение типов
Password = reader[4] as string; //приведение типов
if (TextBox6.Text == phoneNumb)
{ //проверка логина
if (TextBox5.Text == Password) //проверка пароля
{
//Запоминаем в куки файл
Response.Cookies[ConstantNames.UserID].Value = Convert.ToString(reader[0]);//UserID to cookie
Response.Cookies[ConstantNames.UserID].Expires = DateTime.Now.AddDays(1);
// Response.Redirect("C:\Users\)_(\Documents\Visual Studio 2010\WebSites\WebSite5\main.aspx.cs");
Response.Redirect("Login.aspx");
}
else
{
//Invalid password
}
}
}
/*
* id = reader.GetValue(0);
* name = reader.GetValue(1);
* surname = reader.GetValue(2);
* age = reader.GetValue(3);
*/
// cmdIns.Parameters.Add("@name", info);
// cmdIns.ExecuteNonQuery();//выполнить команду
oConnection.closeConnection();
}
public void SelectFromTable(List <TableValue> SelectedValues, List <TableValue> WhereValues, string TableName)
{
SQLConnectionClass oConnection = new SQLConnectionClass();
SQLCommandClass oCommand = new SQLCommandClass();
// string sqlIns = "INSERT INTO table (name, information, other) VALUES (@name, @information, @other)";
string SelectString = null;
string WhereString = null;
foreach (TableValue element in SelectedValues) //Select Values
{
if (element == SelectedValues.ElementAt(SelectedValues.Count - 1)) //Если элемент последний то запятые не ставим
{
SelectString += element.ValueName;
}
else//Если элемент не последний то ставим запятые
{
SelectString += element.ValueName + ",";
}
}
foreach (TableValue element in WhereValues)
{
if (element == WhereValues.ElementAt(WhereValues.Count - 1))
{
WhereString += element.ValueName + "=@" + element.ValueName;
}
else
{
WhereString += element.ValueName + "=@" + element.ValueName + ",";//Where ID = @ID
}
}
string queryString = "SELECT " + "(" + SelectString + ") FROM " + TableName + "(" + WhereString + " ) ";
oCommand.PrepareSelectQuery(queryString, oConnection.connection);
foreach (TableValue element in WhereValues) // WhereValues
{
oCommand.AddSelectParam("@" + element.ValueName, element.Value); //Add Select Parameter
}
SqlDataReader reader = oCommand.ExectuteReader();
object returnValue = null;
if (reader.Read())
{
returnValue = reader[0] as object;
}
oConnection.closeConnection();
}
protected void ButtonRegistration_Click(object sender, EventArgs e)
{
PeoplesClass oPeople = new PeoplesClass();
bool loginExist = false;
if (oPeople.isLoginExist(this.TextBoxPhone.Text))
{
this.LabelMessage.Text = " логин уже привязан к другой странице. Укажите другой логин.";
loginExist = true;
}
if (this.TextBoxPassw.Text.Length < 5)
{
this.LabelMessage.Text = " пароль слишком короткий. введите пароль не менее 5 символов";
loginExist = true;
}
if (!loginExist)
{
SQLConnectionClass oConnection = new SQLConnectionClass();
SQLCommandClass oCommand = new SQLCommandClass();
string sqlIns = "INSERT INTO Peoples( ID, Name, Surname, Phone, Password) VALUES (@ID, @Name, @Surname, @Phone, @Password)";
//использовать только в такой последовательности
oCommand.PrepareInsertQuery(sqlIns, oConnection.connection);
int newIDPeoples = ID_OPerator.createNewTableID("ID", "Peoples");
if (newIDPeoples == -2)
{
//некорректные параметры
}
oCommand.AddInsertParameter("@ID", Convert.ToString(newIDPeoples));//заносим новый айди
oCommand.AddInsertParameter("@Name", this.TextBoxName.Text);
oCommand.AddInsertParameter("@Surname", this.TextBoxSurname.Text);
oCommand.AddInsertParameter("@Phone", this.TextBoxPhone.Text);
oCommand.AddInsertParameter("@Password", this.TextBoxPassw.Text);
oCommand.ExecuteQuery();
oConnection.closeConnection();
Response.Cookies[ConstantNames.UserID].Value = Convert.ToString(newIDPeoples);//UserID to cookie
Response.Cookies[ConstantNames.UserID].Expires = DateTime.Now.AddDays(1);
Response.Redirect("Login.aspx");
}
}
