public decimal checkBalance(int customerID)
{
string query = "select Balance from BankAccounts where CustomerID = @customerID";
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.CommandText = query;
sqlCommand.Parameters.AddWithValue("@customerID", customerID);
DataAccesLayer dal = new DataAccesLayer();
if (dal.connctionOpen())
{
SqlDataReader reader = dal.returnReader(sqlCommand);
if (reader.HasRows)
{
reader.Read();
this.Balance = Convert.ToDecimal(reader[0].ToString());
reader.Close();
dal.connctionClosed();
return(this.Balance);
}
dal.connctionClosed();
return(-1);
}
else
{
return(-2);
}
}
public string checkBalanceByCardNumber(int cardNumber)
{
string query = "select Balance from BankAccounts where CardNumber = @cardNumber";
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.CommandText = query;
sqlCommand.Parameters.AddWithValue("@cardNumber", cardNumber);
DataAccesLayer dal = new DataAccesLayer();
if (dal.connctionOpen())
{
SqlDataReader reader = dal.returnReader(sqlCommand);
if (reader.HasRows)
{
reader.Read();
this.Balance = Convert.ToDecimal(reader[0].ToString());
reader.Close();
dal.connctionClosed();
return(this.Balance.ToString());
}
dal.connctionClosed();
return("-1");
}
else
{
return("-2");
}
}
public void depositmoney(decimal amount, int cartNumber)
{
string query = "update BankAccounts set Balance = Balance + @currnetbalance where CardNumber=@cartNumber";
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.CommandText = query;
sqlCommand.Parameters.AddWithValue("@currnetbalance", amount);
sqlCommand.Parameters.AddWithValue("@cartNumber", cartNumber);
DataAccesLayer dal = new DataAccesLayer();
try
{
dal.connctionOpen();
dal.queryExecution(sqlCommand);
dal.connctionClosed();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
MessageBox.Show("WPŁACONO " + amount.ToString() + " zł na konto");
}
public int getCustomerID(long pesel)
{
string query = "select CustomerID from Customers where PersonalID = @pesel";
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.CommandText = query;
sqlCommand.Parameters.AddWithValue("@pesel", pesel);
DataAccesLayer dal = new DataAccesLayer();
if (dal.connctionOpen())
{
SqlDataReader reader = dal.returnReader(sqlCommand);
if (reader.HasRows)
{
reader.Read(); // wczytuje pierwszy wiersz
this.CustomerID = Convert.ToInt32(reader[0].ToString());
reader.Close();
dal.connctionClosed();
return(this.CustomerID);
}
else
{
dal.connctionClosed();
return(-1);
}
}
else
{
return(-2);
}
}
public string getNameSurname(int cardNumber)
{
string query = "SELECT Name, Surname from Customers, CreditCards where CardNumber = @cardNumber";
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.CommandText = query;
sqlCommand.Parameters.AddWithValue("@cardNumber", cardNumber);
DataAccesLayer dal = new DataAccesLayer();
if (dal.connctionOpen())
{
SqlDataReader reader = dal.returnReader(sqlCommand);
if (reader.HasRows)
{
reader.Read();
this.Name = reader[0].ToString();
this.Surname = reader[1].ToString();
reader.Close();
dal.connctionClosed();
return(Name + " " + Surname); //klient został znaleziony
}
else
{
dal.connctionClosed();
return("Brak w bazie"); //brak klienta w bazie danych
}
}
else
{
return("Brak połączenia z bazą"); //brak połaczenia z bazą danych
}
}
public string addCustomer()
{
string query = "insert into Customers values(@name, @surname,@phone,@Addres, @personaLID)";
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.CommandText = query;
sqlCommand.Parameters.AddWithValue("@name", this.Name);
sqlCommand.Parameters.AddWithValue("@surname", this.Surname);
sqlCommand.Parameters.AddWithValue("@phone", this.PhoneNumber);
sqlCommand.Parameters.AddWithValue("@Addres", this.Address);
sqlCommand.Parameters.AddWithValue("@personaLID", this.PersonalID);
DataAccesLayer dal = new DataAccesLayer();
try
{
dal.connctionOpen();
dal.queryExecution(sqlCommand);
dal.connctionClosed();
}
catch (Exception e)
{
return(e.ToString());
}
return("Customer Inserted");
}
public int getIDbyCardNumber(int cardNumber)
{
string query = "select CustomerId from Cusomers, CreditCards where CardNumber = @cardNumber";
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.CommandText = query;
sqlCommand.Parameters.AddWithValue("@cardNumber", cardNumber);
DataAccesLayer dal = new DataAccesLayer();
if (dal.connctionOpen())
{
SqlDataReader reader = dal.returnReader(sqlCommand);
if (reader.HasRows)
{
reader.Read(); // wczytuje pierwszy wiersz
this.CardNumber = Convert.ToInt32(reader[0].ToString());
reader.Close();
dal.connctionClosed();
return(this.CardNumber);
}
else
{
dal.connctionClosed();
return(-1);
// MessageBox.Show("Nie ma takiego rekordu w bazie!");
}
}
else
{
return(-2);
}
}
public int getCustoemrInfo(long pesel)
{
string query = "SELECT Name, Surname, PhoneNumber, Address, PersonalID from Customers where PersonalID = @pesel";
//"SELECT name, Surname, PhoneName, Address, PersonalID FROM Customers where PersonalID = @pesel";
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.CommandText = query;
sqlCommand.Parameters.AddWithValue("@pesel", pesel);
DataAccesLayer dal = new DataAccesLayer();
if (dal.connctionOpen())
{
SqlDataReader reader = dal.returnReader(sqlCommand);
if (reader.HasRows)
{
reader.Read();
this.Name = reader[0].ToString();
this.Surname = reader[1].ToString();
this.PhoneNumber = Convert.ToInt64(reader[2].ToString());
this.Address = reader[3].ToString();
this.PersonalID = Convert.ToInt64(reader[4].ToString());
reader.Close();
dal.connctionClosed();
return(1); //klient został znaleziony
}
else
{
dal.connctionClosed();
return(-1); //brak klienta w bazie danych
}
}
else
{
return(-2); //brak połaczenia z bazą danych
}
}