public int Updateinventory_item(BO.AssetInventoryTracking.inventory_item inventory_item)
{
int RowsAffected = -1;
string query = "UPDATE dbo.[inventory_item] SET inventoryID = @inventoryID,length_of_warranty = @length_of_warranty,[cost]=@cost,[name]=@name,make = @make,model = @model,date_purchased = @date_purchased,percent_change_cost = @percent_change_cost,status_of_item = @status_of_item WHERE inventory_itemID=@inventory_itemID";
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["db_AssetInventoryTracking"].ConnectionString)) {
using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query, conn)) {
if ((inventory_item.inventoryID == -1))
{
cmd.Parameters.AddWithValue("@inventoryID", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@inventoryID", inventory_item.inventoryID);
}
cmd.Parameters.AddWithValue("@length_of_warranty", inventory_item.length_of_warranty);
cmd.Parameters.AddWithValue("@cost", inventory_item.cost);
cmd.Parameters.AddWithValue("@name", inventory_item.name);
cmd.Parameters.AddWithValue("@make", inventory_item.make);
cmd.Parameters.AddWithValue("@model", inventory_item.model);
cmd.Parameters.AddWithValue("@date_purchased", inventory_item.date_purchased);
cmd.Parameters.AddWithValue("@percent_change_cost", inventory_item.percent_change_cost);
cmd.Parameters.AddWithValue("@status_of_item", inventory_item.status_of_item);
conn.Open();
RowsAffected = cmd.ExecuteNonQuery();
}
}
return(RowsAffected);
}
public List <BO.AssetInventoryTracking.inventory_item> GetAllinventory_item()
{
List <BO.AssetInventoryTracking.inventory_item> xinventory_itemList = new List <BO.AssetInventoryTracking.inventory_item>();
string query = "SELECT [inventoryID],[length_of_warranty],[cost],[name],[make],[model],[date_purchased],[status_of_item],[date_modified],[date_added] FROM dbo.[inventory_item]";
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["db_AssetInventoryTracking"].ConnectionString)) {
using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query, conn)) {
conn.Open();
using (System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)) {
while (reader.Read())
{
BO.AssetInventoryTracking.inventory_item xinventory_item = new BO.AssetInventoryTracking.inventory_item();
if (!object.ReferenceEquals(reader["inventoryID"], DBNull.Value))
{
xinventory_item.inventoryID = int.Parse(reader["inventoryID"].ToString());
}
if (!object.ReferenceEquals(reader["length_of_warranty"], DBNull.Value))
{
xinventory_item.length_of_warranty = int.Parse(reader["length_of_warranty"].ToString());
}
if (!object.ReferenceEquals(reader["make"], DBNull.Value))
{
xinventory_item.make = reader["make"].ToString();
}
if (!object.ReferenceEquals(reader["model"], DBNull.Value))
{
xinventory_item.model = reader["model"].ToString();
}
if (!object.ReferenceEquals(reader["name"], DBNull.Value))
{
xinventory_item.name = reader["name"].ToString();
}
if (!object.ReferenceEquals(reader["cost"], DBNull.Value))
{
xinventory_item.cost = Decimal.Parse(reader["cost"].ToString());
}
if (!object.ReferenceEquals(reader["date_purchased"], DBNull.Value))
{
xinventory_item.date_purchased = DateTime.Parse(reader["date_purchased"].ToString());
}
if (!object.ReferenceEquals(reader["date_modified"], DBNull.Value))
{
xinventory_item.date_modified = DateTime.Parse(reader["date_modified"].ToString());
}
if (!object.ReferenceEquals(reader["date_added"], DBNull.Value))
{
xinventory_item.date_added = DateTime.Parse(reader["date_added"].ToString());
}
if (!object.ReferenceEquals(reader["status_of_item"], DBNull.Value))
{
xinventory_item.status_of_item = reader["status_of_item"].ToString();
}
xinventory_itemList.Add(xinventory_item);
}
}
}
}
return(xinventory_itemList);
}
public BO.AssetInventoryTracking.inventory_item GetByIDinventory_item(int inventory_itemID)
{
BO.AssetInventoryTracking.inventory_item xinventory_item = new BO.AssetInventoryTracking.inventory_item();
string query = "SELECT [inventoryID],[length_of_warranty],[cost],[name],[make],[model],[date_purchased],[percent_change_cost],[status_of_item] FROM dbo.[inventory_item] WHERE inventory_itemID=@inventory_itemID";
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["db_AssetInventoryTracking"].ConnectionString)) {
using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query, conn)) {
cmd.Parameters.AddWithValue("@inventory_itemID", inventory_itemID);
conn.Open();
using (System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)) {
if (reader.Read())
{
if (!object.ReferenceEquals(reader["inventoryID"], DBNull.Value))
{
xinventory_item.inventoryID = int.Parse(reader["inventoryID"].ToString());
}
if (!object.ReferenceEquals(reader["length_of_warranty"], DBNull.Value))
{
xinventory_item.length_of_warranty = int.Parse(reader["length_of_warranty"].ToString());
}
if (!object.ReferenceEquals(reader["name"], DBNull.Value))
{
xinventory_item.make = reader["name"].ToString();
}
if (!object.ReferenceEquals(reader["cost"], DBNull.Value))
{
xinventory_item.cost = Decimal.Parse(reader["cost"].ToString());
}
if (!object.ReferenceEquals(reader["make"], DBNull.Value))
{
xinventory_item.make = reader["make"].ToString();
}
if (!object.ReferenceEquals(reader["model"], DBNull.Value))
{
xinventory_item.model = reader["model"].ToString();
}
if (!object.ReferenceEquals(reader["date_purchased"], DBNull.Value))
{
xinventory_item.date_purchased = DateTime.Parse(reader["date_purchased"].ToString());
}
if (!object.ReferenceEquals(reader["percent_change_cost"], DBNull.Value))
{
xinventory_item.percent_change_cost = int.Parse(reader["percent_change_cost"].ToString());
}
if (!object.ReferenceEquals(reader["status_of_item"], DBNull.Value))
{
xinventory_item.status_of_item = reader["status_of_item"].ToString();
}
}
}
}
}
return(xinventory_item);
}
public int Addinventory_item(BO.AssetInventoryTracking.inventory_item inventory_item)
{
int inventory_itemID = 0;
string query = "INSERT INTO dbo.[inventory_item] ([length_of_warranty],[cost],[name],[make],[model],[date_purchased],[percent_change_cost],[status_of_item]) VALUES ( @length_of_warranty, @cost,@name,@make, @model, @date_purchased, @percent_change_cost, @status_of_item) ; SELECT SCOPE_IDENTITY();";
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["db_AssetInventoryTracking"].ConnectionString)) {
using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query, conn)) {
cmd.Parameters.AddWithValue("@length_of_warranty", inventory_item.length_of_warranty);
cmd.Parameters.AddWithValue("@cost", inventory_item.cost);
cmd.Parameters.AddWithValue("@name", inventory_item.name);
cmd.Parameters.AddWithValue("@make", inventory_item.make);
cmd.Parameters.AddWithValue("@model", inventory_item.model);
cmd.Parameters.AddWithValue("@date_purchased", inventory_item.date_purchased);
cmd.Parameters.AddWithValue("@percent_change_cost", inventory_item.percent_change_cost);
cmd.Parameters.AddWithValue("@status_of_item", inventory_item.status_of_item);
conn.Open();
inventory_itemID = int.Parse(cmd.ExecuteScalar().ToString());
}
}
return(inventory_itemID);
}
public List <BO.AssetInventoryTracking.inventory_item> Searchinventory_item(string name, string ID, string make, string model, string lengthwarranty, string cost, string status, string fromdate, string todate)
{
List <BO.AssetInventoryTracking.inventory_item> xinventory_itemList = new List <BO.AssetInventoryTracking.inventory_item>();
string query = "SELECT [inventoryID],[length_of_warranty],[cost],[name],[make],[model],[date_purchased],[percent_change_cost],[status_of_item] FROM dbo.[inventory_item] ";
if (name != "" || ID != "" || make != "" || model != "" || lengthwarranty != "" || cost != "" || status != "" || fromdate != "" || todate != "")
{
query += " WHERE ";
}
if (name != "")
{
if (query.Contains("like") || query.Contains("="))
{
query += " AND ";
}
query += " name like '%" + name + "%'";
}
if (ID != "")
{
if (query.Contains("like") || query.Contains("="))
{
query += " AND ";
}
query += " inventoryID = '" + ID + "'";
}
if (lengthwarranty != "")
{
if (query.Contains("like") || query.Contains("="))
{
query += " AND ";
}
query += " length_of_warranty = '" + lengthwarranty + "'";
}
if (cost != "")
{
if (query.Contains("like") || query.Contains("="))
{
query += " AND ";
}
query += " cost = '" + cost + "'";
}
if (make != "")
{
if (query.Contains("like") || query.Contains("="))
{
query += " AND ";
}
query += " make like '%" + make + "%'";
}
if (model != "")
{
if (query.Contains("like") || query.Contains("="))
{
query += " AND ";
}
query += " model like '%" + model + "%'";
}
if (status != "")
{
if (query.Contains("like") || query.Contains("="))
{
query += " AND ";
}
query += " [status_of_item] = '" + status + "'";
}
if (fromdate != "" && todate != "")
{
if (query.Contains("like") || query.Contains("="))
{
query += " AND ";
}
query += " date_purchased >= '" + fromdate + "' AND date_purchased <= '" + todate + "'";
}
using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["db_AssetInventoryTracking"].ConnectionString))
{
using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(query, conn))
{
conn.Open();
using (System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (reader.Read())
{
BO.AssetInventoryTracking.inventory_item xinventory_item = new BO.AssetInventoryTracking.inventory_item();
if (!object.ReferenceEquals(reader["inventoryID"], DBNull.Value))
{
xinventory_item.inventoryID = int.Parse(reader["inventoryID"].ToString());
}
if (!object.ReferenceEquals(reader["length_of_warranty"], DBNull.Value))
{
xinventory_item.length_of_warranty = int.Parse(reader["length_of_warranty"].ToString());
}
if (!object.ReferenceEquals(reader["name"], DBNull.Value))
{
xinventory_item.make = reader["name"].ToString();
}
if (!object.ReferenceEquals(reader["cost"], DBNull.Value))
{
xinventory_item.cost = Decimal.Parse(reader["cost"].ToString());
}
if (!object.ReferenceEquals(reader["make"], DBNull.Value))
{
xinventory_item.make = reader["make"].ToString();
}
if (!object.ReferenceEquals(reader["model"], DBNull.Value))
{
xinventory_item.model = reader["model"].ToString();
}
if (!object.ReferenceEquals(reader["date_purchased"], DBNull.Value))
{
xinventory_item.date_purchased = DateTime.Parse(reader["date_purchased"].ToString());
}
if (!object.ReferenceEquals(reader["percent_change_cost"], DBNull.Value))
{
xinventory_item.percent_change_cost = int.Parse(reader["percent_change_cost"].ToString());
}
if (!object.ReferenceEquals(reader["status_of_item"], DBNull.Value))
{
xinventory_item.status_of_item = reader["status_of_item"].ToString();
}
xinventory_itemList.Add(xinventory_item);
}
}
}
}
return(xinventory_itemList);
}
