public static int deleteID(String id)
{
String sql = "delete from consumer where consumerID = '" + id + "'";
SqlCommand sqlCommand = new SqlCommand(sql, DBUtil.getSqlConnection());
return(sqlCommand.ExecuteNonQuery());
}
public static List <Consumer> getRegular(DateTime date)
{
String sql = "select * from consumer where duedate >= @d and (paid = 1 or paid = 0)";
SqlCommand cmd = new SqlCommand(sql, DBUtil.getSqlConnection());
cmd.Parameters.AddWithValue("@d", date);
return(getStudent(cmd));
}
public static List <Login> getAll()
{
List <Login> logins = new List <Login>();
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())
{
Login login = new Login();
login.Username = reader[0].ToString();
login.Password = reader[1].ToString();
logins.Add(login);
}
return(logins);
}
public static List <Consumer> getConsumerByBA(Double billAmount, String condition)
{
String sql = "select * from consumer where billamount " + condition + " " + billAmount;
return(getStudent(new SqlCommand(sql, DBUtil.getSqlConnection())));
}
public static List <Consumer> getConsumerCity(String strName)
{
String sql = "select * from consumer where city = '" + strName + "'";
return(getStudent(new SqlCommand(sql, DBUtil.getSqlConnection())));
}
public static List <Consumer> getConsumerLastNameLike(String strName)
{
String sql = "select * from consumer where lastname like '" + strName + "%'";
return(getStudent(new SqlCommand(sql, DBUtil.getSqlConnection())));
}
public static List <Consumer> getConsumerID(String id)
{
String sql = "select* from consumer where consumerid like '" + id + "%'";
return(getStudent(new SqlCommand(sql, DBUtil.getSqlConnection())));
}
public static int updateConsumer(Consumer consumer)
{
SqlCommand sqlCommand = new SqlCommand("UPDATE HydroConsumer.dbo.consumer SET firstname = @fname, lastname = @lname, city = @c, billamount = @bamount where consumerid = @cid", DBUtil.getSqlConnection());
sqlCommand.Parameters.AddWithValue("@cid", consumer.ConsumerID);
sqlCommand.Parameters.AddWithValue("@fname", consumer.FirstName);
sqlCommand.Parameters.AddWithValue("@lname", consumer.LastName);
sqlCommand.Parameters.AddWithValue("@c", consumer.City);
sqlCommand.Parameters.AddWithValue("@bamount", consumer.BillAmount);
//sqlCommand.Parameters.AddWithValue("@ddate", consumer.DueDate);
return(sqlCommand.ExecuteNonQuery());
}
public static int 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);
return(sqlCommand.ExecuteNonQuery());
}
public static List <Consumer> getAll()
{
String sql = "select * from consumer";
return(getStudent(new SqlCommand(sql, DBUtil.getSqlConnection())));
}
public static int authenticate(Login login)
{
SqlCommand sqlCommand = new SqlCommand("select * from login where username = @u and password = @p", DBUtil.getSqlConnection());
sqlCommand.Parameters.AddWithValue("@u", login.Username);
sqlCommand.Parameters.AddWithValue("@p", login.Password);
SqlDataReader reader = sqlCommand.ExecuteReader();
int count = 0;
while (reader.Read())
{
count++;
}
return(count);
}
public static int add(Login 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);
return(sqlCommand.ExecuteNonQuery());
}
public static int updatePassword(Login 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);
return(sqlCommand.ExecuteNonQuery());
}
