public static ProxyList GetSended(Profile user)
{
ProxyList proxies = new ProxyList("Отправленные", user.Id);
LocalSQLConnection sqlconnectionClass = new LocalSQLConnection();
SqlCommand cmd = sqlconnectionClass.DeployConnectionAndCommand();
cmd.CommandText = "select * from Mail where ProfileId='" + user.Id + "' and Category='Outbox'";
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
ProxyLetter proxy = new ProxyLetter(dr[0].ToString(), dr[2].ToString(), Convert.ToDateTime(dr[7]));
proxy.Interlocutor = dr[5].ToString();
proxies.ProxyMailList.Add(proxy);
}
}
sqlconnectionClass.CloseConnection();
return proxies;
}
public Letter GetLetter()
{
Letter letter = new Letter();
LocalSQLConnection sqlconnectionClass = new LocalSQLConnection();
SqlCommand cmd = sqlconnectionClass.DeployConnectionAndCommand();
cmd.CommandText = "select * from Mail where Id='" + this.Id + "'";
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
letter = new Letter(dr);
}
}
sqlconnectionClass.CloseConnection();
return letter;
}
public static void DB_Add(Profile user)
{
LocalSQLConnection sqlconnectionClass = new LocalSQLConnection();
SqlCommand cmd = sqlconnectionClass.DeployConnectionAndCommand();
cmd.CommandText = "insert into Profiles (Id, Name, Adress,Password, LastTimeChecked, Server, SmtpPort, PopPort)"
+ " values (@id, @name, @adr, @pas, @ltc, @s, @smtp, @pop)";
cmd.Parameters.AddWithValue("@id", user.Id);
cmd.Parameters.AddWithValue("@name", user.Name);
cmd.Parameters.AddWithValue("@adr", user.Adress);
cmd.Parameters.AddWithValue("@pas", user.Password);
cmd.Parameters.AddWithValue("@ltc", user.LastTimeChecked);
cmd.Parameters.AddWithValue("@s", user.Server);
cmd.Parameters.AddWithValue("@smtp", user.SmtpPort);
cmd.Parameters.AddWithValue("@pop", user.PopPort);
cmd.ExecuteNonQuery();
sqlconnectionClass.CloseConnection();
}
public static void DB_Delete(Profile user)
{
LocalSQLConnection sqlconnectionClass = new LocalSQLConnection();
SqlCommand cmd = sqlconnectionClass.DeployConnectionAndCommand();
cmd.CommandText = @"delete from Profiles where Id='" + user.Id + "'";
cmd.ExecuteNonQuery();
cmd.CommandText = @"delete from Mail where ProfileId='" + user.Id + "'";
cmd.ExecuteNonQuery();
sqlconnectionClass.CloseConnection();
}
public static Profile DB_GetByID(string id)
{
Profile prof = new Profile();
LocalSQLConnection sqlconnectionClass = new LocalSQLConnection();
SqlCommand cmd = sqlconnectionClass.DeployConnectionAndCommand();
cmd.CommandText = "select * from Profiles where Id='" + id + "'";
SqlDataReader dr = cmd.ExecuteReader();
sqlconnectionClass.CloseConnection();
if (dr.HasRows)
{
while (dr.Read())
{
Profile user = new Profile();
user.Id = dr[0].ToString();
user.Name = dr[1].ToString();
user.Adress = dr[2].ToString();
user.Password = dr[3].ToString();
user.LastTimeChecked = Convert.ToDateTime(dr[4]);
user.Server = dr[5].ToString();
user.SmtpPort = Convert.ToInt32(dr[6]);
user.PopPort = Convert.ToInt32(dr[7]);
prof = user;
}
}
return prof;
}
public static void DB_UpdateTime(Profile user)
{
LocalSQLConnection sqlconnectionClass = new LocalSQLConnection();
SqlCommand cmd = sqlconnectionClass.DeployConnectionAndCommand();
cmd.CommandText = @"Update Profiles SET LastTimeChecked = (@UE) where Id='" + user.Id + "'";
cmd.Parameters.AddWithValue("@UE", user.LastTimeChecked);
cmd.ExecuteNonQuery();
sqlconnectionClass.CloseConnection();
}
public static List<Profile> DB_Load()
{
LocalSQLConnection sqlconnectionClass = new LocalSQLConnection();
SqlCommand cmd = sqlconnectionClass.DeployConnectionAndCommand();
List<Profile> profileList = new List<Profile>();
cmd.CommandText = "select * from Profiles";
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
Profile user = new Profile();
user.Id = dr[0].ToString();
user.Name = dr[1].ToString();
user.Adress = dr[2].ToString();
user.Password = dr[3].ToString();
user.LastTimeChecked = Convert.ToDateTime(dr[4]);
user.Server = dr[5].ToString();
user.SmtpPort = Convert.ToInt32(dr[6]);
user.PopPort = Convert.ToInt32(dr[7]);
profileList.Add(user);
}
}
sqlconnectionClass.CloseConnection();
return profileList;
}
public static void DeleteLetterFromDB(string letterId)
{
LocalSQLConnection sqlconnectionClass = new LocalSQLConnection();
SqlCommand cmd = sqlconnectionClass.DeployConnectionAndCommand();
cmd.CommandText = "delete from Mail where Id='" + letterId + "'";
cmd.ExecuteNonQuery();
sqlconnectionClass.CloseConnection();
MessageBox.Show("Deleted");
}
public static void ChangeLetterFolderInDB(ProxyLetter letter, string folderName)
{
LocalSQLConnection sqlconnectionClass = new LocalSQLConnection();
SqlCommand cmd = sqlconnectionClass.DeployConnectionAndCommand();
cmd.CommandText = "Update Mail SET Category = '" + folderName + "' where Id = '" + letter.Id + "'";
cmd.ExecuteNonQuery();
sqlconnectionClass.CloseConnection();
}
public static void AddLetterToDB(Profile user, Letter letter)
{
LocalSQLConnection sqlconnectionClass = new LocalSQLConnection();
SqlCommand cmd = sqlconnectionClass.DeployConnectionAndCommand();
cmd.CommandText = "insert into Mail (Id, ProfileId, Subject,Body, AdressFrom, AdressTo, Category, Time)"
+ " values (@id, @pid, @s, @b, @af, @at, @c, @t)";
letter.SetId();
cmd.Parameters.AddWithValue("@id", letter.Id);
cmd.Parameters.AddWithValue("@pid", user.Id);
cmd.Parameters.AddWithValue("@s", letter.Subject);
cmd.Parameters.AddWithValue("@b", letter.Body);
cmd.Parameters.AddWithValue("@af", letter.From);
cmd.Parameters.AddWithValue("@at", letter.To);
cmd.Parameters.AddWithValue("@c", letter.Category);
cmd.Parameters.AddWithValue("@t", letter.SendingTime);
cmd.ExecuteNonQuery();
sqlconnectionClass.CloseConnection();
}