Hi All,
I have used Database connection, data reader to read the values from my database into a list but as nop commerce has Entity Framework installed in it, I want to use it. But I am pretty new to Entity framework. Can someone help me regarding this.
public class InventoryUpdate : ITask
{
//Variable Declarations
private readonly List<Product> _productList = new List<Product>();
private readonly List<ProductWarehouseInventory> _productListWarehouseInventory = new List<ProductWarehouseInventory>();
private readonly List<Warehouse> _warehouseList = new List<Warehouse>();
private string _warehouseName;
int _useMultiWarehouseBoolValue;
public void Execute()
{
//Enabling SQL Database Connection for Product Table
const string connstr = @"Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=Test_Dev;server=Test\SQLEXPRESS";
SqlConnection sqlConnection = new SqlConnection(connstr);
sqlConnection.Open();
const string sqlQuery = "Select p.Id,p.StockQuantity,p.Sku,p.ClientCode,p.UseMultipleWarehouses,p.WarehouseId,w.Name from (Product p left join ProductWarehouseInventory pi on p.Id = pi.ProductId) inner join Warehouse w on p.WarehouseId = w.Id where p.ClientCode='Test'";
SqlCommand sqlCommand = new SqlCommand(sqlQuery, sqlConnection);
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
//Retrieving values from Data Reader and adding them to product List
if (sqlDataReader.HasRows)
{
while (sqlDataReader.Read())
{
var product = new Product();
var warehouse = new Warehouse();
var productWarehouseInventory = new ProductWarehouseInventory();
product.Id = Convert.ToInt32(sqlDataReader["Id"]);
product.StockQuantity = Convert.ToInt32(sqlDataReader["StockQuantity"]);
product.ClientCode = sqlDataReader["ClientCode"].ToString().Trim();
product.Sku = sqlDataReader["Sku"].ToString();
product.WarehouseId = Convert.ToInt32(sqlDataReader["WarehouseID"]);
product.UseMultipleWarehouses = Convert.ToBoolean(sqlDataReader["UseMultipleWarehouses"]);
warehouse.Name = sqlDataReader["Name"].ToString().Trim();
warehouse.Id = Convert.ToInt32(sqlDataReader["WarehouseId"]);
if (product.UseMultipleWarehouses)
{
warehouse.Name = sqlDataReader["Name"].ToString().Trim();
productWarehouseInventory.WarehouseId = Convert.ToInt32(sqlDataReader["WarehouseId"]);
_productListWarehouseInventory.Add(productWarehouseInventory);
_warehouseList.Add(warehouse);
_productList.Add(product);
}
else
{
_productList.Add(product);
_warehouseList.Add(warehouse);
}
}
}
else
{
Console.WriteLine("No Rows Found.");
}
sqlDataReader.Close();
sqlConnection.Close();
}
