public int UpdateCategory(Categories categories)
{
if (categories == null)
{
throw new NullReferenceException();
}
int categoryID;
try
{
Konekcija kon = new Konekcija();
kon.OpenConnection();
SqlCommand komanda = new SqlCommand();
komanda.Connection = kon.NorthWindKonekcija();
komanda.CommandType = CommandType.StoredProcedure;
komanda.CommandText = "UpdateCategory";
komanda.Parameters.Add("@CategoryID", SqlDbType.NVarChar).Value = categories.CategoryID;
komanda.Parameters.Add("@CategoryName", SqlDbType.NVarChar).Value = categories.CategoryName;
komanda.Parameters.Add("@Description", SqlDbType.NText).Value = categories.Description;
komanda.Parameters.Add("@Picture", SqlDbType.Image).Value = categories.Picture;
categoryID = Convert.ToInt32(komanda.ExecuteScalar());
kon.CloseConnection();
}
catch (Exception ex)
{
throw new Exception("Error while updating category to db");
}
return(categoryID);
}
public int DeleteCategory(int categoryID)
{
Konekcija kon = new Konekcija();
SqlCommand komanda = new SqlCommand();
komanda.Connection = kon.NorthWindKonekcija();
komanda.CommandType = CommandType.StoredProcedure;
komanda.CommandText = "DeleteCategory";
komanda.Parameters.Add("@CategoryID", SqlDbType.Int);
komanda.Parameters["@CategoryID"].Value = categoryID;
try
{
kon.OpenConnection();
komanda.ExecuteNonQuery();
return(0);
}
catch
{
return(-1);
}
finally
{
kon.CloseConnection();
}
}
public List <Product> GetAllProducts()
{
List <Product> listaProdukata = new List <Product>();
Konekcija kon = new Konekcija();
SqlCommand komanda = new SqlCommand();
komanda.Connection = kon.NorthWindKonekcija();
komanda.CommandText = "Select ProductID,ProductName from Products";
kon.OpenConnection();
SqlDataReader dr = komanda.ExecuteReader();
while (dr.Read())
{
Product products = new Product();
products.ProductID1 = int.Parse(dr["ProductID"].ToString());
products.ProductName1 = dr["ProductName"].ToString();
/* products.UnitsOnOrder1 = Convert.ToInt16(dr["UnitsOnOrder"].ToString());
* products.ReorderLevel1 = Convert.ToInt16(dr["ReorderLevel"].ToString());
* products.QuantityPerUnit1 = dr["QuantityPerUnit"].ToString();
* products.UnitsInStock1 = Convert.ToInt16(dr["UnitsInStock"].ToString());
* products.Discontinued1 = Convert.ToBoolean(dr["Discontinued"].ToString());
* products.CategoryID1 = Convert.ToInt32(dr["CategoryID"].ToString());
* products.SupplierID1 =Convert.ToInt32(dr["SupplierID"].ToString());
* products.UnitPrice1 = Convert.ToInt32(dr["UnitPrice"].ToString()); */
listaProdukata.Add(products);
}
kon.CloseConnection();
return(listaProdukata);
}
public int AddCategory(Categories categories)
{
Konekcija kon = new Konekcija();
SqlCommand komanda = new SqlCommand();
komanda.Connection = kon.NorthWindKonekcija();
komanda.CommandType = CommandType.StoredProcedure;
komanda.CommandText = "AddCategory1";
komanda.Parameters.Add("@CategoryName", SqlDbType.NVarChar).Value = categories.CategoryName;
komanda.Parameters.Add("@Description", SqlDbType.NText).Value = categories.Description;
komanda.Parameters.Add("@Picture", SqlDbType.Image).Value = categories.Picture;
try
{
kon.OpenConnection();
komanda.ExecuteNonQuery();
return(0);
}
catch
{
return(-1);
}
finally
{
kon.CloseConnection();
}
}
public Product GetProductById(int productID)
{
Konekcija kon = new Konekcija();
try
{
kon.OpenConnection();
SqlCommand komanda = new SqlCommand();
komanda.Connection = kon.NorthWindKonekcija();
komanda.CommandType = CommandType.Text;
komanda.Parameters.AddWithValue("@ProductID", productID);
komanda.Parameters["@ProductID"].SqlDbType = SqlDbType.Int;
komanda.CommandText =
@"SELECT [ProductID] ,[ProductName] ,[QuantityPerUnit],[UnitPrice], [UnitsInStock],[UnitsOnOrder],[ReorderLevel],[Discontinued],[SupplierID],[CategoryID]
FROM[NORTHWND].[dbo].[Products]
WHERE [ProductID] = @ProductID";
SqlDataReader reader;
Product product = new Product();
reader = komanda.ExecuteReader();
while (reader.Read())
{
product.ProductID1 = (int)reader[0];
product.ProductName1 = reader[1].ToString();
product.QuantityPerUnit1 = reader[2].ToString();
product.UnitsInStock1 = (short)reader[3];
product.UnitsOnOrder1 = (short)reader[3];
product.ReorderLevel1 = (short)reader[4];
product.Discontinued1 = (bool)reader[5];
product.ReorderLevel1 = (short)reader[6];
product.UnitPrice1 = (int)reader[7];
product.CategoryID1 = (int)reader[8];
product.UnitPrice1 = (int)reader[9];
}
kon.CloseConnection();
return(product);
}
catch (Exception ex)
{
throw new Exception("Error ");
}
}
public Supplier GetSupplierByID(int supplierID)
{
Konekcija kon = new Konekcija();
try
{
kon.OpenConnection();
SqlCommand komanda = new SqlCommand();
komanda.Connection = kon.NorthWindKonekcija();
komanda.CommandType = CommandType.Text;
komanda.Parameters.AddWithValue("@SupplierID", supplierID);
komanda.Parameters["@SupplierId"].SqlDbType = SqlDbType.Int;
komanda.CommandText =
@"SELECT [SupplierID] ,[CompanyName] ,[ContactName],[ContactTitle], [Address],[City],[Region],[PostalCode],[Country],[Phone],[Fax],[HomePage]
FROM[NORTHWND].[dbo].[Suppliers]
WHERE [SupplierID] = @SupplierID";
SqlDataReader reader;
Supplier supplier = new Supplier();
reader = komanda.ExecuteReader();
while (reader.Read())
{
supplier.SupplierID1 = (int)reader[0];
supplier.CompanyName1 = reader[1].ToString();
supplier.ContactName1 = reader[2].ToString();
supplier.ContactTitle1 = reader[3].ToString();
supplier.Address1 = reader[4].ToString();
supplier.City1 = reader[5].ToString();
supplier.Region1 = reader[6].ToString();
supplier.PostalCode1 = reader[7].ToString();
supplier.Country1 = reader[8].ToString();
supplier.Phone1 = reader[9].ToString();
supplier.Fax1 = reader[10].ToString();
supplier.HomePage1 = reader[11].ToString();
}
kon.CloseConnection();
return(supplier);
}
catch (Exception ex)
{
throw new Exception("Error ");
}
}
public Categories GetCategoryById(int categoryid)
{
Konekcija kon = new Konekcija();
try
{
kon.OpenConnection();
SqlCommand komanda = new SqlCommand();
komanda.Connection = kon.NorthWindKonekcija();
komanda.CommandType = CommandType.Text;
komanda.Parameters.AddWithValue("@categoryId", categoryid);
komanda.Parameters["@categoryId"].SqlDbType = SqlDbType.Int;
komanda.CommandText =
@"SELECT [CategoryID] ,[CategoryName] ,[Description],[Picture]
FROM[NORTHWND].[dbo].[Categories]
WHERE [CategoryID] = @categoryId";
SqlDataReader reader;
Categories category = new Categories();
reader = komanda.ExecuteReader();
while (reader.Read())
{
category.CategoryID = (int)reader[0];
category.CategoryName = reader[1].ToString();
category.Description = reader[2].ToString();
category.Picture = (byte[])reader[3];
}
kon.CloseConnection();
return(category);
}
catch (Exception ex)
{
throw new Exception("Error");
}
}
public int UpdateProducts(Product product)
{
if (product == null)
{
throw new NullReferenceException();
}
int productId;
try
{
Konekcija kon = new Konekcija();
kon.OpenConnection();
SqlCommand komanda = new SqlCommand();
komanda.Connection = kon.NorthWindKonekcija();
komanda.CommandType = CommandType.StoredProcedure;
komanda.CommandText = "UpdateProduct";
komanda.Parameters.Add("@ProductID", SqlDbType.NVarChar).Value = product.ProductID1;
komanda.Parameters.Add("@ProductName", SqlDbType.NVarChar).Value = product.ProductName1;
komanda.Parameters.Add("@ReorderLevel", SqlDbType.SmallInt).Value = product.ReorderLevel1;
komanda.Parameters.Add("@QuantityPerUnit", SqlDbType.NVarChar).Value = product.QuantityPerUnit1;
komanda.Parameters.Add("@UnitsInStock", SqlDbType.SmallInt).Value = product.UnitsInStock1;
komanda.Parameters.Add("@UnitPrice", SqlDbType.Money).Value = product.UnitPrice1;
komanda.Parameters.Add("@UnitsOnOrder", SqlDbType.SmallInt).Value = product.UnitsOnOrder1;
komanda.Parameters.Add("@Discontinued", SqlDbType.Bit).Value = product.Discontinued1;
komanda.Parameters.Add("@SupplierID", SqlDbType.Int).Value = product.SupplierID1;
komanda.Parameters.Add("@CategoryID", SqlDbType.Int).Value = product.CategoryID1;
productId = Convert.ToInt32(komanda.ExecuteScalar());
kon.CloseConnection();
}
catch (Exception ex)
{
throw new Exception("Error ");
}
return(productId);
}
public List <Categories> GetAllCategories()
{
List <Categories> listaKategorija = new List <Categories>();
Konekcija kon = new Konekcija();
SqlCommand komanda = new SqlCommand();
komanda.Connection = kon.NorthWindKonekcija();
komanda.CommandText = "Select CategoryID,CategoryName from Categories";
kon.OpenConnection();
SqlDataReader dr = komanda.ExecuteReader();
while (dr.Read())
{
Categories category = new Categories();
category.CategoryID = int.Parse(dr["CategoryID"].ToString());
category.CategoryName = dr["CategoryName"].ToString();
listaKategorija.Add(category);
}
kon.CloseConnection();
return(listaKategorija);
}
public List <Supplier> GetAllSuppliers()
{
List <Supplier> listSupplier = new List <Supplier>();
Konekcija kon = new Konekcija();
SqlCommand komanda = new SqlCommand();
komanda.Connection = kon.NorthWindKonekcija();
komanda.CommandText = "Select SupplierID,CompanyName from Suppliers";
kon.OpenConnection();
SqlDataReader dr = komanda.ExecuteReader();
while (dr.Read())
{
Supplier supplier = new Supplier();
supplier.SupplierID1 = int.Parse(dr["SupplierID"].ToString());
supplier.CompanyName1 = dr["CompanyName"].ToString();
listSupplier.Add(supplier);
}
kon.CloseConnection();
return(listSupplier);
}
public int updateSupplier(Supplier supplier)
{
if (supplier == null)
{
throw new NullReferenceException();
}
int supplierID;
try
{
Konekcija kon = new Konekcija();
kon.OpenConnection();
SqlCommand komanda = new SqlCommand();
komanda.Connection = kon.NorthWindKonekcija();
komanda.CommandType = CommandType.StoredProcedure;
komanda.CommandText = "UpdateSupplier";
komanda.Parameters.Add("@SupplierID", SqlDbType.Int).Value = supplier.SupplierID1;
komanda.Parameters.Add("@CompanyName", SqlDbType.NVarChar).Value = supplier.CompanyName1;
komanda.Parameters.Add("@ContactName", SqlDbType.NVarChar).Value = supplier.ContactName1;
komanda.Parameters.Add("@ContactTitle", SqlDbType.NVarChar).Value = supplier.ContactTitle1;
komanda.Parameters.Add("@Address", SqlDbType.NVarChar).Value = supplier.Address1;
komanda.Parameters.Add("@City", SqlDbType.NVarChar).Value = supplier.City1;
komanda.Parameters.Add("@Region", SqlDbType.NVarChar).Value = supplier.Region1;
komanda.Parameters.Add("@PostalCode", SqlDbType.NVarChar).Value = supplier.PostalCode1;
komanda.Parameters.Add("@Fax ", SqlDbType.NVarChar).Value = supplier.Fax1;
komanda.Parameters.Add("@HomePage ", SqlDbType.NVarChar).Value = supplier.HomePage1;
komanda.Parameters.Add("@Country", SqlDbType.NVarChar).Value = supplier.Country1;
komanda.Parameters.Add("@Phone", SqlDbType.NVarChar).Value = supplier.Phone1;
supplierID = Convert.ToInt32(komanda.ExecuteScalar());
kon.CloseConnection();
}
catch (Exception ex)
{
throw new Exception("Error adding supplier");
}
return(supplierID);
}