public static int GetCustomerID(string UserName, string password)
{
using NpgsqlConnection connection = CommonDAO.CreateNewConnection();
connection.Open();
string sql = $"SELECT \"CustomerID\" FROM \"Account\" WHERE \"LoginName\" LIKE \'{UserName}\' AND \"Password\" LIKE \'{password}\'";
using NpgsqlCommand command = new NpgsqlCommand(sql, connection);
Int32 reader = Convert.ToInt32(command.ExecuteScalar());
return(reader);
}
public string returnCustomerName(int CustomerID)
{
using NpgsqlConnection connection = CommonDAO.CreateNewConnection();
connection.Open();
string sql = $"SELECT \"FirstName\" FROM \"Customer\" WHERE \"CustomerID\" = {CustomerID}";
using NpgsqlCommand command = new NpgsqlCommand(sql, connection);
using NpgsqlDataReader reader = command.ExecuteReader();
reader.Read();
return(reader.GetString(0));
}
public int GetLastID()
{
using NpgsqlConnection connection = CommonDAO.CreateNewConnection();
connection.Open();
string sql = $"SELECT \"CustomerID\" FROM \"Customer\" ORDER BY \"CustomerID\" DESC";
using NpgsqlCommand command = new NpgsqlCommand(sql, connection);
using NpgsqlDataReader reader = command.ExecuteReader();
reader.Read();
return(reader.GetInt32(0));
}
public void printAllRows()
{
using NpgsqlConnection connection = CommonDAO.CreateNewConnection();
connection.Open();
string sql = $"SELECT * FROM \"Customer\"";
using NpgsqlCommand command = new NpgsqlCommand(sql, connection);
using NpgsqlDataReader rdr = command.ExecuteReader();
while (rdr.Read())
{
Console.WriteLine("{0} {1} {2} {3} {4}", rdr.GetInt32(0), rdr.GetInt32(1),
rdr.GetString(2), rdr.GetString(3), rdr.GetString(4));
}
}
public List <Product> GetAllRows()
{
List <Product> productList = new List <Product>();
using NpgsqlConnection connection = CommonDAO.CreateNewConnection();
connection.Open();
string sql = $"SELECT * FROM \"Product\"";
using NpgsqlCommand command = new NpgsqlCommand(sql, connection);
using NpgsqlDataReader reader = command.ExecuteReader();
int countOfData = reader.FieldCount;
while (reader.Read())
{
Product product = new Product(Convert.ToInt32(reader[0]), Convert.ToString(reader[1]), Convert.ToInt32(reader[2]), Convert.ToInt32(reader[3]));
productList.Add(product);
}
return(productList);
}
public List <Order> GetAllRows()
{
List <Order> dataAccount = new List <Order>();
using NpgsqlConnection connection = CommonDAO.CreateNewConnection();
connection.Open();
string sql = $"SELECT * FROM \"Order\"";
using NpgsqlCommand command = new NpgsqlCommand(sql, connection);
using NpgsqlDataReader reader = command.ExecuteReader();
int countOfData = reader.FieldCount;
while (reader.Read())
{
Order order = new Order(Convert.ToInt32(reader[0]), Convert.ToInt32(reader[1]), Convert.ToString(reader[2]), Convert.ToInt32(reader[3]));
dataAccount.Add(order);
}
return(dataAccount);
}
public List <Account> GetAllRows()
{
List <Account> dataAccount = new List <Account>();
using NpgsqlConnection connection = CommonDAO.CreateNewConnection();
connection.Open();
//prepared statement
string sql = $"SELECT * FROM \"Account\"";
using NpgsqlCommand command = new NpgsqlCommand(sql, connection);
using NpgsqlDataReader reader = command.ExecuteReader();
int countOfData = reader.FieldCount;
while (reader.Read())
{
Account account = new Account(Convert.ToInt32(reader[0]), Convert.ToInt32(reader[1]), Convert.ToString(reader[2]), Convert.ToString(reader[3]), Convert.ToString(reader[4]));
dataAccount.Add(account);
}
return(dataAccount);
}
public List <Customer> GetAllRows()
{
List <Customer> dataCustomer = new List <Customer>();
using NpgsqlConnection connection = CommonDAO.CreateNewConnection();
connection.Open();
string sql = $"SELECT * FROM \"Customer\"";
using NpgsqlCommand command = new NpgsqlCommand(sql, connection);
using NpgsqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Customer customer = new Customer(Convert.ToInt32(reader[0]), Convert.ToString(reader[1]), Convert.ToString(reader[2]), Convert.ToString(reader[3]), Convert.ToString(reader[4]), Convert.ToString(reader[5]), Convert.ToString(reader[6]));
dataCustomer.Add(customer);
}
return(dataCustomer);
}