public WItemQuantity GetBYProductCodeAndShelfId(int shelfId, string procode)
{
var obj = new WItemQuantity();
SqlConnection conn = new SqlConnection();
conn.ConnectionString = CONNSTRING;
var strSqlContactStmt = @"SELECT Id, ShelfItemId, Qty, ProductCode,SupplierName FROM dbo.WItemQuantity
WHERE ShelfItemId = @id AND ProductCode=@procode AND Active=@active ";
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandText = strSqlContactStmt;
cmd.Parameters.Add("@id", SqlDbType.Int).Value = shelfId;
cmd.Parameters.Add("@procode", SqlDbType.NVarChar).Value = procode;
cmd.Parameters.Add("@active", SqlDbType.Bit).Value = true;
cmd.Connection = conn;
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
obj.Id = Convert.ToInt32(reader["Id"]);
obj.ProductCode = Convert.ToString(reader["ProductCode"]);
obj.SupplierName = Convert.ToString(reader["SupplierName"]);
obj.Qty = Convert.ToInt32(reader["Qty"]);
obj.ShelfItemId = Convert.ToInt32(reader["ShelfItemId"].ToString());
}
}
conn.Close();
return(obj);
}
public void UpdateWItemActiveStatus(WItemQuantity obj, int shelfId, bool status)
{
SqlConnection conn = new SqlConnection();
conn.ConnectionString = CONNSTRING;
var strSqlContactStmt = @"UPDATE WItemQuantity SET Active=@active WHERE ProductCode=@procode AND ShelfItemId=@shelfItemId";
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = conn;
conn.Open();
cmd.Parameters.Add("@shelfItemId", SqlDbType.Int).Value = obj.ShelfItemId;
cmd.Parameters.Add("@active", SqlDbType.Bit).Value = status;
cmd.Parameters.Add("@procode", SqlDbType.NVarChar).Value = obj.ProductCode;
cmd.CommandText = strSqlContactStmt;
cmd.ExecuteNonQuery();
}
conn.Close();
}
public void CreateWItemQuantity(WItemQuantity obj)
{
SqlConnection conn = new SqlConnection();
conn.ConnectionString = CONNSTRING;
var strSqlContactStmt = @"INSERT INTO WItemQuantity(ShelfItemId, Qty, ProductCode,SupplierName,Active)
VALUES(@shelfItemId, @qty, @productCode,@supplierName,@active)";
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = conn;
conn.Open();
cmd.Parameters.Add("@shelfItemId", SqlDbType.Int).Value = obj.ShelfItemId;
cmd.Parameters.Add("@qty", SqlDbType.Int).Value = obj.Qty;
cmd.Parameters.Add("@productCode", SqlDbType.NVarChar).Value = obj.ProductCode;
cmd.Parameters.Add("@supplierName", SqlDbType.NVarChar).Value = obj.SupplierName;
cmd.Parameters.Add("@active", SqlDbType.Bit).Value = true;
cmd.CommandText = strSqlContactStmt;
cmd.ExecuteNonQuery();
}
conn.Close();
}
public IList <WItemQuantity> GetAllItemQuantiyByShelfId(int id)
{
var list = new List <WItemQuantity>();
SqlConnection conn = new SqlConnection();
conn.ConnectionString = CONNSTRING;
var strSqlContactStmt = @"SELECT Id, ShelfItemId, Qty, ProductCode,SupplierName FROM dbo.WItemQuantity
WHERE ShelfItemId = @id AND Active=@active ";
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandText = strSqlContactStmt;
cmd.Parameters.Add("@id", SqlDbType.Int).Value = id;
cmd.Parameters.Add("@active", SqlDbType.Bit).Value = true;
cmd.Connection = conn;
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
var obj = new WItemQuantity()
{
Id = Convert.ToInt32(reader["Id"]),
ProductCode = Convert.ToString(reader["ProductCode"]),
SupplierName = Convert.ToString(reader["SupplierName"]),
Qty = Convert.ToInt32(reader["Qty"]),
ShelfItemId = Convert.ToInt32(reader["ShelfItemId"].ToString())
};
list.Add(obj);
}
}
conn.Close();
return(list);
}