public DataRow[] DisplayAllUsersActive()
{
DataRow[] allaccount;
mydb = new sqlClass();
sSql = "select Email, Active from User";
allaccount = mydb.drExecute(sPath, sSql);
if (allaccount == null)
{
mydb = null;
return null;
}
return allaccount;
}
public Dictionary<string, int> GetAccountMessages()
{
accauntsMessages = new Dictionary<string, int>();
mydb = new sqlClass();
sSql = "select Email, LastCountMessage from User;";
DataRow[] datarows = mydb.drExecute(sPath, sSql);
if (datarows == null)
{
mydb = null;
return new Dictionary<string, int>();
}
else
{
foreach (DataRow dr in datarows)
{
accauntsMessages.Add(dr["Email"].ToString(), int.Parse(dr["LastCountMessage"].ToString()));
}
return accauntsMessages;
}
}
public void CreateDB()
{
if (File.Exists(sPath))
return;
using (File.Create(sPath)) { }
mydb = new sqlClass();
sSql = @"CREATE TABLE [User] (
[Email] TEXT(50) NOT NULL ON CONFLICT ROLLBACK,
[Password] TEXT(50) NOT NULL ON CONFLICT ROLLBACK,
[Active] BOOL,
[LastCountMessage] INTEGER,
CONSTRAINT [sqlite_autoindex_User_1] PRIMARY KEY ([Email]));
CREATE TABLE [Story] (
[EmailUser] TEXT(50) NOT NULL ON CONFLICT ROLLBACK CONSTRAINT [UserStory] REFERENCES [User]([Email]),
[EmailSender] TEXT(50) NOT NULL ON CONFLICT ROLLBACK,
[Theme] TEXT,
[TextLetter] TEXT,
[Name] TEXT);";
//Пытаемся создать таблицу
mydb.iExecuteNonQuery(sPath, sSql, 0);
mydb = null;
}
public void DeleteMessage(string emailUser, string emailSender, string theme, string textLetter, string name)
{
mydb = new sqlClass();
sSql = @"delete from Story(EmailUser,EmailSender,Theme,TextLetter, Name) where EmailUser="******"and EmailSender="+emailSender+" and Theme="+theme+" and TextLetter="+textLetter+" and Name= "+name;
//Проверка работы
if (mydb.iExecuteNonQuery(sPath, sSql, 1) == 0)
{
MessageBox.Show("Не удалось получить соединение с базой данных");
mydb = null;
return;
}
else
{
mydb = null;
}
return;
}
//Работа с историей сообщений
public void InsertMessage(string emailUser, string emailSender, string theme, string textLetter, string name)
{
mydb = new sqlClass();
sSql = @"insert into Story(EmailUser,EmailSender,Theme,TextLetter, Name) values('" + emailUser + "','" + emailSender + "','"+theme+ "','"+textLetter+ "','"+name + "');";
//Проверка работы
if (mydb.iExecuteNonQuery(sPath, sSql, 1) == 0)
{
MessageBox.Show("Не удалось получить соединение с базой данных");
mydb = null;
return;
}
else
{
mydb = null;
}
return;
}
public Dictionary<string, string> ActiveAccount()
{
activeaccount = new Dictionary<string, string>();
mydb = new sqlClass();
sSql = "select Email, Password from User where Active<>0";
DataRow[] datarows = mydb.drExecute(sPath, sSql);
if (datarows == null)
{
mydb = null;
return new Dictionary<string,string>();
}
else
{
foreach (DataRow dr in datarows)
{
activeaccount.Add(dr["Email"].ToString(), dr["Password"].ToString());
}
return activeaccount;
}
}
public void DeleteAllEmail()
{
mydb = new sqlClass();
sSql = "delete from User";
if (mydb.iExecuteNonQuery(sPath, sSql, 1) == 0)
{
//MessageBox.Show("Не удалось получить соединение с базой данных");
mydb = null;
return;
}
else
{
//MessageBox.Show("Все учетные записи удалены");
mydb = null;
}
return;
}
public List<string> DisplayAllUsers()
{
users = new List<string>();
mydb = new sqlClass();
sSql = "select Email from User";
DataRow[] datarows = mydb.drExecute(sPath, sSql);
if (datarows == null)
{
mydb = null;
return new List<string>();
}
else
{
foreach (DataRow dr in datarows)
{
users.Add(dr["Email"].ToString());
}
return users;
}
}
public void UpdateActiveUser(string email, string active)
{
mydb = new sqlClass();
sSql = @"Update User set Active=" + active + ",LastCountMessage =-1 where Email='" + email + "';";
//Проверка работы
if (mydb.iExecuteNonQuery(sPath, sSql, 1) == 0)
{
mydb = null;
return;
}
else
{
//MessageBox.Show("Запись обновлена!");
mydb = null;
}
}
public void UpdateEmail(string oldEmail, string newEmail, string password)
{
mydb = new sqlClass();
sSql = @"Update User set Email='" + newEmail + "', Password='******',LastCountMessage =-1 where Email='" + oldEmail + "';";
//Проверка работы
if (mydb.iExecuteNonQuery(sPath, sSql, 1) == 0)
{
mydb = null;
return;
}
else
{
//MessageBox.Show("Запись обновлена!");
mydb = null;
}
}
public void InsertUser(string email, string password,bool active)
{
mydb = new sqlClass();
sSql = @"insert into User(Email,Password,Active,LastCountMessage) values('" + email + "','" + password + "','" + active + "',-1);";
//Проверка работы
if (mydb.iExecuteNonQuery(sPath, sSql, 1) == 0)
{
//MessageBox.Show("Не удалось получить соединение с базой данных");
mydb = null;
return;
}
else
{
//MessageBox.Show("Пользователь-"+email+", успешно добавлен");
mydb = null;
}
return;
}
//Работа с аккаунтами пользователя
public void DeleteEmail(string email)
{
mydb = new sqlClass();
sSql = "delete from User where Email='"+email+"'";
if (mydb.iExecuteNonQuery(sPath, sSql, 1) == 0)
{
//MessageBox.Show("Не удалось получить соединение с базой данных");
mydb = null;
return;
}
else
{
//MessageBox.Show("Пользователь-"+email+", успешно удален");
mydb = null;
}
return;
}
public void UpdateAccountMessages(string email, int i)
{
mydb = new sqlClass();
sSql = @"Update User set LastCountMessage =" + i + " where Email='" + email + "'";
DataRow[] datarows = mydb.drExecute(sPath, sSql);
if (datarows == null)
{
mydb = null;
return;
}
else
{
mydb = null;
}
}