public bool deleteKategori(Kategori kategori)
{
//TODO: Try catch - Done
//TODO : Write to debug.txt
try
{
SqlCommand dbCmd = DBConnection.GetDbCommand("DELETE FROM [kategori] WHERE kategori = '" + kategori.FullKategori + "'");
int rows = dbCmd.ExecuteNonQuery();
bool returnResult = false;
if (rows == 1)
{
returnResult = true;
}
else
{
throw new Exception("No rows affected");
}
return(returnResult);
}
catch (SqlException sqlE)
{
// Write to debug.txt
DebugToTxt.DebugMethod("Method: KategoriDB.deleteKategori\r\n" +
"catch Exeption sqlE\r\n" +
"Is SQL Syntax OK?\r\n" +
sqlE.ToString());
}
catch (Exception e)
{
DebugToTxt.DebugMethod("Metod: KategoriDB.addKategori\r\n" +
"catch Exeption e\r\n" +
"Is user logged in to DB or has connection to SQL?\r\n" +
e.ToString());
}
finally
{
DBConnection.Close();
}
return(false);
}
public bool addKategori(Kategori kategori)
{
//TODO:
//try catch
try
{
// UNIQUE in table kategories
SqlCommand dbCmd = DBConnection.GetDbCommand("INSERT INTO [kategori] VALUES ('" + kategori.FullKategori + "')");
int rows = dbCmd.ExecuteNonQuery();
bool returnResult = false;
if (rows == 1)
{
returnResult = true;
}
return(returnResult);
}
catch (SqlException sqlE)
{
//write to debug.txt
DebugToTxt.DebugMethod("Method: KategoriDB.addKategori\r\n" +
"catch Exeption sqlE\r\n" +
"Is SQL Syntax OK?\r\n" +
sqlE.ToString());
}
catch (Exception e)
{
//Write to debug.txt
DebugToTxt.DebugMethod("Metod: KategoriDB.addKategori\r\n" +
"catch Exeption e\r\n" +
"Is user logged in to DB or has connection to SQL?\r\n" +
e.ToString());
}
finally
{
DBConnection.Close();
}
return(false);
}
//Print out what's in minimum-stock
public List <Stock> getStockByMin()
{
try
{
string sqlString = "SELECT Stock.bvarenr, Stock.antal, Stock.minimum, Stock.bkatId, Kategori.kategori FROM Stock, Kategori WHERE Stock.antal < Stock.minimum and kategori.id = Stock.bkatId";
SqlCommand dbCmd = DBConnection.GetDbCommand(sqlString);
SqlDataReader reader = dbCmd.ExecuteReader();
List <Stock> stocks = new List <Stock>();
//Console.WriteLine("Rigt before the while-loop - Debug");
while (reader.Read())
{
Stock stock = new Stock(Int32.Parse(reader["bvarenr"].ToString()), Int32.Parse(reader["antal"].ToString()), Int32.Parse(reader["minimum"].ToString()), Int32.Parse(reader["bkatId"].ToString()), reader["kategori"].ToString());
//Stock stock = new Stock(Int32.Parse(reader["antal"].ToString()), Int32.Parse(reader["minimum"].ToString()), Int32.Parse(reader["bvarenr"].ToString()), Int32.Parse(reader["bkatId"].ToString()));
stocks.Add(stock);
}
return(stocks);
}
catch (SqlException sqlE)
{
DebugToTxt.DebugMethod("Method: StockDB.getStockByMin\r\n" +
"catch Exeption sqlE \r\n" +
"Is SQL syntax OK?\r\n" +
sqlE.ToString());
}
catch (Exception e)
{
DebugToTxt.DebugMethod("Method: StockDB.getStockByMin\r\n" +
"catch Exeption e\r\n" +
"Is user logged in to DB or connected to DB?\r\n" +
e.ToString());
}
finally
{
DBConnection.Close();
}
return(null);
} // end of getStockByMin
//search Product by 'Beskrivelse'
// SELECT * FROM Product WHERE beskrivelse LIKE '%string%';
public List <Product> getProductByDesc(string str)
{
List <Product> products = new List <Product>();
try
{
String sql = "SELECT product.varenummer, product.beskrivelse, Product.pris, Kategori.kategori FROM Product, Kategori WHERE beskrivelse LIKE '%" + str + "%' AND Product.katId = Kategori.id";
Console.WriteLine("Output: " + sql);
SqlCommand dbCmd = DBConnection.GetDbCommand("SELECT product.varenummer, product.beskrivelse, Product.pris, Kategori.kategori FROM Product, Kategori WHERE beskrivelse LIKE '%" + str + "%' AND Product.katId = Kategori.id");
SqlDataReader reader = dbCmd.ExecuteReader();
//List<Product> products = new List<Product>();
while (reader.Read())
{
Product product = new Product(Int32.Parse(reader["varenummer"].ToString()), reader["beskrivelse"].ToString(), Int32.Parse(reader["pris"].ToString()), reader["kategori"].ToString());
products.Add(product);
}
// DBConnection.Close();
//return products;
}
catch (SqlException sqlE)
{
DebugToTxt.DebugMethod("Method: ProductDB.getProductByDesc\r\n" +
"catch Exeption sqlE \r\n" +
"Is SQL syntax OK?\r\n" +
sqlE.ToString());
}
catch (Exception e)
{
DebugToTxt.DebugMethod("Method: ProductDB.getProductByDesc\r\n" +
"catch Exeption e\r\n" +
"Is user logged in to DB or connected to DB?\r\n" +
e.ToString());
}
finally
{
DBConnection.Close();
}
return(products);
}
public List <Kategori> getAllKats()
{
try
{
SqlCommand dbCmd = DBConnection.GetDbCommand("SELECT * FROM [kategori] ORDER BY id");
SqlDataReader reader = dbCmd.ExecuteReader();
List <Kategori> kate1 = new List <Kategori>();
while (reader.Read())
{
Kategori kate2 = new Kategori(Int32.Parse(reader["id"].ToString()), reader["Kategori"].ToString());
kate1.Add(kate2);
}
DBConnection.Close();
return(kate1);
}
catch (SqlException sqlE)
{
//write SQL-debugging to debug.txt
DebugToTxt.DebugMethod("Method: KategoriDB.getAllKats\r\n" +
"catch Exeption sqlE \r\n" +
"Is SQL syntax OK?\r\n" +
sqlE.ToString());
}
catch (Exception e)
{
//write to debug.txt
DebugToTxt.DebugMethod("Method: KategoriDB.getAllKats\r\n" +
"catch Exeption e\r\n" +
"Is user logged in to the DB or has connection to SQL?\r\n" +
e.ToString());
}
finally
{
DBConnection.Close();
}
return(null);
}
} // end of getStockByMin
//Update antal in stock
//Syntax:
//UPDATE Stock SET antal=10 WHERE bvarenr=12049
public bool updateAntal(Stock stock)
{
try
{
SqlCommand dbCmd = DBConnection.GetDbCommand("UPDATE [Stock] SET antal='" + stock.Antal + "' WHERE bvarenr='" + stock.ProductObj.Varenummer + "'");
int rows = dbCmd.ExecuteNonQuery();
//Console.WriteLine(dbCmd.ToString()); //debugging
bool returnResult = false;
if (rows == 1)
{
returnResult = true;
}
return(returnResult);
}
catch (SqlException sqlE)
{
//write to debug.txt
DebugToTxt.DebugMethod("Method: StockDB.updateAntal\r\n" +
"catch Exeption sqlE\r\n" +
"Is SQL Syntax OK?\r\n" +
sqlE.ToString());
}
catch (Exception e)
{
//Write to debug.txt
DebugToTxt.DebugMethod("Metod: StockDB.updateAntal\r\n" +
"catch Exeption e\r\n" +
"Is user logged in to DB or has connection to SQL?\r\n" +
e.ToString());
}
finally
{
DBConnection.Close();
}
return(false);
}
public Kategori getKategoriByID(int id)
{
try
{
SqlCommand dbCmd = DBConnection.GetDbCommand("SELECT * FROM [kategori] WHERE kategori.id = " + id);
SqlDataReader reader = dbCmd.ExecuteReader();
reader.Read();
Kategori kat = new Kategori(Int32.Parse(reader["id"].ToString()), reader["kategori"].ToString());
//DBConnection.Close();
return(kat);
}
catch (SqlException sqlE)
{
//write SQL-debug to debug.txt
DebugToTxt.DebugMethod("Method: KategoriDB.getKategoriByID\r\n" +
"catch SqlExeption sqlE\r\n" +
"Is SQL Syntax OK?\r\n" +
sqlE.ToString());
}
catch (Exception e)
{
//wrtite to debug.txt
DebugToTxt.DebugMethod("Method: KategoriDB.getKategoriByID\r\n" +
"catch Exeption e\r\n" +
"Is user logged in to DB or has connection to SQL?\r\n" +
e.ToString());
}
finally
{
DBConnection.Close();
}
return(null);
}
//Method for teh view dbo.Se_sales in the Database...
//Output are the following:
//Kategori, Varenummer, beskrivelse, antal, pris
public List <ViewSe_Sales> getViewSe_Sales()
{
try
{
string sqlString = "SELECT * FROM dbo.Se_sales";
SqlCommand dbCmd = DBConnection.GetDbCommand(sqlString);
SqlDataReader reader = dbCmd.ExecuteReader();
List <ViewSe_Sales> viewSe_Sales = new List <ViewSe_Sales>();
while (reader.Read())
{
ViewSe_Sales viewSe_Sale = new ViewSe_Sales(reader["kategori"].ToString(), Int32.Parse(reader["varenummer"].ToString()), reader["beskrivelse"].ToString(), Int32.Parse(reader["antal"].ToString()), Int32.Parse(reader["pris"].ToString()));
viewSe_Sales.Add(viewSe_Sale);
}
return(viewSe_Sales);
}
catch (SqlException sqlE)
{
DebugToTxt.DebugMethod("Method: ViewSe_Sales.getViewSe_Sales\r\n" +
"catch Exeption sqlE \r\n" +
"Is SQL syntax OK?\r\n" +
sqlE.ToString());
}
catch (Exception e)
{
DebugToTxt.DebugMethod("Method: ViewSe_Sales.getViewSe_Sales\r\n" +
"catch Exeption e\r\n" +
"Is user logged in to DB or connected to DB?\r\n" +
e.ToString());
}
finally
{
DBConnection.Close();
}
return(null);
}