public IEnumerable <AccountDTO> FindByStatus(byte status)
{
string queryString = "SELECT * FROM dbo.Account WHERE status = @status";
List <AccountDTO> results = new List <AccountDTO>();
try
{
using (SqlConnection con = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand(queryString, con))
{
cmd.Parameters.AddWithValue("@status", SqlDbType.TinyInt).Value = status;
cmd.CommandType = CommandType.Text;
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
AccountDTO account = new AccountDTO();
AddressDTO address = new AddressDTO();
account = GenerateAccount(reader, account, address);
//return product instance as data object
Debug.Print("AccountDAL: /FindAllUserBy/ " + account.GetID().ToString());
//add data objects to result-list
results.Add(account);
}
}
}
}
catch (Exception e)
{
e.GetBaseException();
}
return(results);
}
public AccountDTO FindByConfID(int ConfID)
{
AccountDTO account = new AccountDTO();
AddressDTO address;
string queryString = "SELECT * FROM dbo.Account WHERE confirmationID = @ConfID AND isConfirmed=0";
try
{
using (SqlConnection con = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand(queryString, con))
{
cmd.Parameters.AddWithValue("@ConfID", SqlDbType.Int).Value = ConfID;
cmd.CommandType = CommandType.Text;
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
address = new AddressDTO();
account = GenerateAccount(reader, account, address);
//return product instance as data object
Debug.Print("AccountDAL: /FindByID/ " + account.GetID());
}
}
}
}
catch (Exception e)
{
Debug.Write("\n\nError with ConfID SQL query, check ConfID Values in DB\n\n");
e.GetBaseException();
Debug.Print(e.ToString());
}
return(account);
}
public AccountDTO FindByName(string fname, string lname)
{
AccountDTO account = new AccountDTO();
string queryString = "SELECT * FROM dbo.Account WHERE firstName = @fname AND lastName = @lname";
try
{
// The connection is automatically closed at the end of the using block.
using (SqlConnection con = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand(queryString, con))
{
cmd.Parameters.AddWithValue("@fname", fname);
cmd.Parameters.AddWithValue("@lname", lname);
cmd.CommandType = CommandType.Text;
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
AddressDTO address = new AddressDTO();
account = GenerateAccount(reader, account, address);
//return product instance as data object
Debug.Print("AccountDAL: /FindByName/ " + fname + " " + lname + " " + account.GetID());
}
}
}
}
catch (Exception e)
{
e.GetBaseException();
Debug.Print(e.ToString());
}
return(account);
}
