public List <Inventory> GetInInventories(int locationId, int departmentId, int categoryId, int subCategoryId) { List <Inventory> list = new List <Inventory>(); IDbConnection connection = null; IDbTransaction transaction = null; IDbCommand dataCommand = null; IDataReader dataReader = null; StringBuilder sqlQuery = new StringBuilder(); sqlQuery.Append(" with Subcategory_result as "); sqlQuery.Append(" (select sc.SubCategoryId,sc.SubCategoryName,c.CategoryName,d.DepartmentName,l.LocationName from SubCategory sc "); sqlQuery.Append(" inner join(select CategoryId, CategoryName, DepartmentId from Category "); sqlQuery.Append(" where CategoryId = @categoryId "); //sqlQuery.Append(" where CategoryName = 'Bakery Bread' "); sqlQuery.Append(" ) c on c.CategoryId = sc.CategoryId "); sqlQuery.Append(" inner join(select DepartmentId, DepartmentName, LocationId from Department "); sqlQuery.Append(" where DepartmentId = @departmentId "); //sqlQuery.Append(" where DepartmentName = 'Bakery' "); sqlQuery.Append(" ) d on d.DepartmentId = c.DepartmentId "); sqlQuery.Append(" inner join(select LocationId, LocationName from Location "); sqlQuery.Append(" where LocationId = @locationId "); //sqlQuery.Append(" where LocationName = 'Perimeter' "); sqlQuery.Append(" ) l on l.LocationId = d.LocationId "); sqlQuery.Append(" where sc.SubCategoryId = @subCategoryId "); //sqlQuery.Append(" where sc.SubCategoryName = 'Bagels' "); sqlQuery.Append(" ) "); sqlQuery.Append(" select ii.SkuId,ii.SkuName,scr.LocationName,scr.DepartmentName,scr.CategoryName,scr.SubCategoryName from Inventory ii "); sqlQuery.Append(" inner join Subcategory_result scr on ii.SubCategoryId = scr.SubCategoryId; "); try { connection = DBUtil.GetOpenConnection(); if (connection.State != ConnectionState.Open) { connection.Open(); } transaction = connection.BeginTransaction(IsolationLevel.ReadUncommitted); dataCommand = connection.CreateCommand(); dataCommand.CommandText = sqlQuery.ToString(); dataCommand.Transaction = transaction; SetIntParam(dataCommand, "@locationId", locationId); SetIntParam(dataCommand, "@departmentId", departmentId); SetIntParam(dataCommand, "@categoryId", categoryId); SetIntParam(dataCommand, "@subCategoryId", subCategoryId); dataReader = dataCommand.ExecuteReader(); while (dataReader.Read()) { Inventory details = new Inventory(); details.SkuId = dataReader.IsDBNull(0) ? int.MinValue : dataReader.GetInt32(0); details.SkuName = dataReader.IsDBNull(1) ? null : dataReader.GetString(1); details.LocationName = dataReader.IsDBNull(2) ? null : dataReader.GetString(2); details.DepartmentName = dataReader.IsDBNull(3) ? null : dataReader.GetString(3); details.CategoryName = dataReader.IsDBNull(4) ? null : dataReader.GetString(4); details.SubCategoryName = dataReader.IsDBNull(5) ? null : dataReader.GetString(5); list.Add(details); } } catch (Exception ex) { throw new Exception(ex.Message); } finally { if (dataReader != null) { dataReader.Close(); dataReader = null; } if (dataCommand != null) { dataCommand.Dispose(); dataCommand = null; } } return(list); }
public Inventory GetSku(int skuId) { Inventory details = null; IDbConnection connection = null; IDbTransaction transaction = null; IDbCommand dataCommand = null; IDataReader dataReader = null; StringBuilder sqlQuery = new StringBuilder(); sqlQuery.Append(" select ii.SkuId,ii.SkuName,l.LocationName,d.DepartmentName,c.CategoryName,sc.SubCategoryName from Inventory ii "); sqlQuery.Append(" left join SubCategory sc on sc.SubCategoryId = ii.SubCategoryId "); sqlQuery.Append(" left join Category c on c.CategoryId = sc.CategoryId "); sqlQuery.Append(" left join Department d on d.DepartmentId = c.DepartmentId "); sqlQuery.Append(" left join Location l on l.LocationId = d.LocationId "); sqlQuery.Append(" where ii.SkuId = @SkuId"); try { connection = DBUtil.GetOpenConnection(); if (connection.State != ConnectionState.Open) { connection.Open(); } transaction = connection.BeginTransaction(IsolationLevel.ReadUncommitted); dataCommand = connection.CreateCommand(); dataCommand.CommandText = sqlQuery.ToString(); dataCommand.Transaction = transaction; //SetIntParam(dataCommand, "@locationId", locationId); //SetIntParam(dataCommand, "@departmentId", departmentId); //SetIntParam(dataCommand, "@categoryId", categoryId); SetIntParam(dataCommand, "@SkuId", skuId); dataReader = dataCommand.ExecuteReader(); while (dataReader.Read()) { details = new Inventory { SkuId = dataReader.IsDBNull(0) ? int.MinValue : dataReader.GetInt32(0), SkuName = dataReader.IsDBNull(1) ? null : dataReader.GetString(1), LocationName = dataReader.IsDBNull(2) ? null : dataReader.GetString(2), DepartmentName = dataReader.IsDBNull(3) ? null : dataReader.GetString(3), CategoryName = dataReader.IsDBNull(4) ? null : dataReader.GetString(4), SubCategoryName = dataReader.IsDBNull(5) ? null : dataReader.GetString(5) }; } } catch (Exception ex) { throw new Exception(ex.Message); } finally { if (dataReader != null) { dataReader.Close(); dataReader = null; } if (dataCommand != null) { dataCommand.Dispose(); dataCommand = null; } } return(details); }