public static SqlDataAdapter getAll()
{
SqlDataAdapter sda = new SqlDataAdapter("select * from consumer", DBUtil.getSqlConnection());
DataTable dataTable = new DataTable();
sda.Fill(dataTable);
return(sda);
}
public static List <CLogin> getAll()
{
List <CLogin> logins = new List <CLogin>();
SqlCommand cmd = new SqlCommand();
SqlDataReader reader;
cmd.CommandText = "select * from login";
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = DBUtil.getSqlConnection();
reader = cmd.ExecuteReader();
while (reader.Read())
{
CLogin login = new CLogin();
login.Username = reader[0].ToString();
login.Password = reader[1].ToString();
logins.Add(login);
}
return(logins);
}
public static SqlDataAdapter getDefaulter(DateTime date)
{
SqlCommand cmd = new SqlCommand("select * from consumer where duedate < @d and paid = 0", DBUtil.getSqlConnection());
cmd.Parameters.AddWithValue("@d", date);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dataTable = new DataTable();
sda.Fill(dataTable);
return(sda);
}
public static SqlDataAdapter getConsumerCity(String strName)
{
SqlDataAdapter sda = new SqlDataAdapter("select * from consumer where city = '" + strName + "'", DBUtil.getSqlConnection());
DataTable dataTable = new DataTable();
sda.Fill(dataTable);
return(sda);
}
public static SqlDataAdapter getConsumerLastNameLike(String strName)
{
SqlDataAdapter sda = new SqlDataAdapter("select * from consumer where lastname like '" + strName + "%'", DBUtil.getSqlConnection());
DataTable dataTable = new DataTable();
sda.Fill(dataTable);
return(sda);
}
public static SqlDataAdapter getConsumerID(String id)
{
SqlDataAdapter sda = new SqlDataAdapter("select * from consumer where consumerid like '" + id + "%'", DBUtil.getSqlConnection());
DataTable dataTable = new DataTable();
sda.Fill(dataTable);
return(sda);
}
public static SqlDataAdapter deleteID(String id)
{
SqlDataAdapter sda = new SqlDataAdapter("delete from consumer where consumerID = '" + id + "'", DBUtil.getSqlConnection());
DataTable dataTable = new DataTable();
sda.Fill(dataTable);
return(sda);
}
public static SqlDataAdapter getBAE(Double billAmount)
{
SqlDataAdapter sda = new SqlDataAdapter("select * from consumer where billamount = " + billAmount, DBUtil.getSqlConnection());
DataTable dataTable = new DataTable();
sda.Fill(dataTable);
return(sda);
}
public static void add(Consumer consumer)
{
SqlCommand sqlCommand = new SqlCommand("insert into consumer values (@id,@fn,@ln,@cn,@ba,@d,@p)", DBUtil.getSqlConnection());
sqlCommand.Parameters.AddWithValue("@id", consumer.ConsumerID);
sqlCommand.Parameters.AddWithValue("@fn", consumer.FirstName);
sqlCommand.Parameters.AddWithValue("@ln", consumer.LastName);
sqlCommand.Parameters.AddWithValue("@cn", consumer.City);
sqlCommand.Parameters.AddWithValue("@ba", consumer.BillAmount);
sqlCommand.Parameters.AddWithValue("@d", consumer.DueDate);
sqlCommand.Parameters.AddWithValue("@p", 0);
sqlCommand.ExecuteNonQuery();
}
public static void add(CLogin login)
{
SqlCommand sqlCommand = new SqlCommand("insert into login values (@id,@fn)", DBUtil.getSqlConnection());
sqlCommand.Parameters.AddWithValue("@id", login.Username);
sqlCommand.Parameters.AddWithValue("@fn", login.Password);
sqlCommand.ExecuteNonQuery();
}
public static void updatePassword(CLogin login)
{
SqlCommand sqlCommand = new SqlCommand("update login set password = @id where username = @fn", DBUtil.getSqlConnection());
sqlCommand.Parameters.AddWithValue("@id", login.Password);
sqlCommand.Parameters.AddWithValue("@fn", login.Username);
sqlCommand.ExecuteNonQuery();
}