public User FindUserEmailID(int userEmailID)
{
User user = null;
string sql = string.Format("SELECT * FROM UserEmails WHERE UserEmailID = '{0}' ", userEmailID);
using (Connection cn = new Connection())
{
using (SqlCommand cmd = new SqlCommand())
{
try
{
cmd.Connection = cn.SqlConnection;
cmd.CommandText = sql;
int recordsAffected = cmd.ExecuteNonQuery();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
user.UserID = (int)reader["UserID"];
user = userDAO.FindById(user.UserID);
}
return user;
}
else
return user;
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.Message);
return user;
}
}
}
}
//public void Insert(string sql)
//{
// using (SqlCommand cmd = new SqlCommand())
// {
// try
// {
// cmd.Connection = SqlConnection;
// cmd.CommandText = sql;
// int recordsAffected = cmd.ExecuteNonQuery();
// }
// catch (Exception)
// {
// }
// }
//}
public bool Insert(string sql)
{
using (Connection cn = new Connection())
{
using (SqlCommand cmd = new SqlCommand())
{
try
{
cmd.Connection = cn.SqlConnection;
cmd.CommandText = sql;
int recordsAffected = cmd.ExecuteNonQuery();
return true;
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.Message);
return false;
}
}
}
}
public Stock FindBySku(string sku)
{
Stock s = null;
string sql = string.Format("SELECT * FROM Stock WHERE SKU = '{0}' ", sku);
using (Connection cn = new Connection())
{
using (SqlCommand cmd = new SqlCommand())
{
try
{
cmd.Connection = cn.SqlConnection;
cmd.CommandText = sql;
int recordsAffected = cmd.ExecuteNonQuery();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
s = new Stock();
s.StockID = (int)reader["StockID"];
s.StockGuid = (Guid)reader["StockGuid"];
s.SKU = reader["SKU"].ToString();
s.Description = reader["Description"].ToString();
}
return s;
}
else
return s;
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.Message);
return s;
}
}
}
}
public Stock Insert(string sku, string description)
{
Stock s = null;
string sql = string.Format("INSERT INTO Stock(SKU, Description) VALUES ('{0}', '{1}') ", sku, description);
using (Connection cn = new Connection())
{
using (SqlCommand cmd = new SqlCommand())
{
try
{
cmd.Connection = cn.SqlConnection;
cmd.CommandText = sql;
int recordsAffected = cmd.ExecuteNonQuery();
s = FindBySku(sku);
return s;
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.Message);
return s;
}
}
}
}
public User FindById(int id)
{
User s = null;
string sql = string.Format("SELECT * FROM Users WHERE UserID = '{0}' ", id);
using(Connection cn = new Connection())
{
using (SqlCommand cmd = new SqlCommand())
{
try
{
cmd.Connection = cn.SqlConnection;
cmd.CommandText = sql;
int recordsAffected = cmd.ExecuteNonQuery();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
s = new User();
s.UserID = (int)reader["UserID"];
s.LoginName = reader["LoginName"].ToString();
s.EmailList = userEmailDAO.GetEmailList(s.UserID);
}
return s;
}
else
return s;
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.Message);
return s;
}
}
}
}
public bool Insert(UserEmail userEmail)
{
string sql = string.Format("INSERT INTO UserEmails(UserID, Email, Password) VALUES ('{0}', '{1}', '{2}') ", userEmail.UserID, userEmail.Email, userEmail.Password);
using (Connection cn = new Connection())
{
using (SqlCommand cmd = new SqlCommand())
{
try
{
cmd.Connection = cn.SqlConnection;
cmd.CommandText = sql;
int recordsAffected = cmd.ExecuteNonQuery();
return true;
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.Message);
return false;
}
}
}
}
public List<UserEmail> GetEmailList(int userID)
{
List<UserEmail> list = new List<UserEmail>();
string sql = string.Format("SELECT * FROM UserEmails WHERE UserID = '{0}' ", userID);
using (Connection cn = new Connection())
{
using (SqlCommand cmd = new SqlCommand())
{
try
{
cmd.Connection = cn.SqlConnection;
cmd.CommandText = sql;
int recordsAffected = cmd.ExecuteNonQuery();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
UserEmail ue = new UserEmail();
ue.UserID = (int)reader["UserID"];
ue.UserEmailID = (int)reader["UserEmailID"];
ue.Email = reader["Email"].ToString();
ue.Password = reader["Password"].ToString();
list.Add(ue);
}
return list;
}
else
return list;
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.Message);
return list;
}
}
}
}
public User Insert(string loginName)
{
if (FindLoginName(loginName) != null)
return null;
string sql = string.Format("INSERT INTO Users(LoginName) VALUES ('{0}') ", loginName);
using (Connection cn = new Connection())
{
using (SqlCommand cmd = new SqlCommand())
{
try
{
cmd.Connection = cn.SqlConnection;
cmd.CommandText = sql;
int recordsAffected = cmd.ExecuteNonQuery();
return FindLoginName(loginName);
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.Message);
return null;
}
}
}
}