Visual Basic

Dim con As New ClassConnection 
If con.Conn.State = ConnectionState.Closed Then con.Conn.Open()

'To get Opening Stock in Full Quantity
    Dim sql As String = "SELECT tblBrand.B_ID AS ID, tblBrand.B_Name AS Brand," & _
    " Sum (tblOp_Details.Net_Qty) AS Quantity FROM tblOp_Stock INNER JOIN (tblBrand INNER JOIN" & _
    " tblOp_Details ON tblBrand.B_ID = tblOp_Details.B_ID) ON tblOp_Stock.Stk_ID = tblOp_Details.Stk_ID" & _
    " WHERE tblOp_Stock.God_ID = @GID GROUP BY tblBrand.B_ID, tblBrand.B_Name"

    'To get Purchases < start date and adding to the opening stock
    Dim sql1 As String = "SELECT tblBrand.B_ID AS ID, tblBrand.B_Name AS Brand," & _
    " Sum (tblPur_Details.Net_Qty) AS Quantity FROM tblPurchase INNER JOIN (tblBrand INNER JOIN" & _
    " tblPur_Details ON tblBrand.B_ID = tblPur_Details.B_ID) ON tblPurchase.Pur_ID = tblPur_Details.Pur_ID" & _
    " WHERE tblPurchase.God_ID = @GID AND tblPurchase.Rec_Date < @SDate GROUP BY tblBrand.B_ID, tblBrand.B_Name"

    'To get Sales < Start date and subtracting to the above
    Dim sql2 As String = "SELECT tblBrand.B_ID AS ID, tblBrand.B_Name AS Brand," & _
    " Sum (tblSales_Details.Net_Qty) AS Quantity FROM tblSales INNER JOIN (tblBrand INNER JOIN" & _
    " tblSales_Details ON tblBrand.B_ID = tblSales_Details.B_ID) ON tblSales.Sale_ID = tblSales_Details.Sale_ID" & _
    " WHERE tblSales.God_ID = @GID AND tblSales.Sale_Date < @SDate GROUP BY tblBrand.B_ID, tblBrand.B_Name"
    'The above 3 condition is for deriving opening stock as on given date

    'To get Purchases >= Start Date and <= Start Date
    Dim sql3 As String = "SELECT tblBrand.B_ID AS ID, tblBrand.B_Name AS Brand," & _
    " Sum(tblPur_Details.Net_Qty) AS Quantity FROM tblBrand INNER JOIN (tblPurchase INNER JOIN" & _
    " tblPur_Details ON tblPurchase.Pur_ID = tblPur_Details.Pur_ID) ON tblBrand.B_ID = tblPur_Details.B_ID" & _
    " WHERE tblPurchase.God_ID = @GID And tblPurchase.Rec_Date >= @SDate And tblPurchase.Rec_Date" & _
    " <= @EDate GROUP BY tblBrand.B_ID, tblBrand.B_Name"
    'The above condition is for deriving Purchases as on given date

'To get Sales >= Start Date and <= Start Date
    Dim sql4 As String = "SELECT tblBrand.B_ID AS ID, tblBrand.B_Name AS Brand," & _
    " Sum(tblSales_Details.Net_Qty) AS Quantity FROM tblBrand INNER JOIN (tblSales INNER JOIN" & _
    " tblSales_Details ON tblSales.Sale_ID = tblSales_Details.Sale_ID) ON tblBrand.B_ID = tblSales_Details.B_ID" & _
    " WHERE tblSales.God_ID = @GID And tblSales.Sale_Date >= @SDate And tblSales.Sale_Date <= @EDate" & _
    " And tblSales_Details.S_Active = @SAct GROUP BY tblBrand.B_ID, tblBrand.B_Name"
    'The above condition is for deriving Sales as on given date

    Dim da As New OleDb.OleDbDataAdapter(sql, con.Conn)
    Dim da1 As New OleDb.OleDbDataAdapter(sql1, con.Conn)
    Dim da2 As New OleDb.OleDbDataAdapter(sql2, con.Conn)
    Dim da3 As New OleDb.OleDbDataAdapter(sql3, con.Conn)
    Dim da4 As New OleDb.OleDbDataAdapter(sql4, con.Conn)

    da.SelectCommand.Parameters.AddWithValue("@GID", Me.stBar_G_ID.Text)
    da1.SelectCommand.Parameters.AddWithValue("@GID", Me.stBar_G_ID.Text)
    da1.SelectCommand.Parameters.AddWithValue("@SDate", Me.dtpStart.ToString)
    da2.SelectCommand.Parameters.AddWithValue("@GID", Me.stBar_G_ID.Text)
    da2.SelectCommand.Parameters.AddWithValue("@SDate", Me.dtpStart.ToString)
    da3.SelectCommand.Parameters.AddWithValue("@GID", Me.stBar_G_ID.Text)
    da3.SelectCommand.Parameters.AddWithValue("@SDate", Me.dtpStart.ToString)
    da3.SelectCommand.Parameters.AddWithValue("@EDate", Me.dtpEnd.ToString)
    da4.SelectCommand.Parameters.AddWithValue("@GID", Me.stBar_G_ID.Text)
    da4.SelectCommand.Parameters.AddWithValue("@SDate", Me.dtpStart.ToString)
    da4.SelectCommand.Parameters.AddWithValue("@EDate", Me.dtpEnd.ToString)
    da4.SelectCommand.Parameters.AddWithValue("@SAct", "Yes")

    Dim dt As New DataTable
    Dim dt1 As New DataTable
    Dim dt2 As New DataTable
    Dim dt3 As New DataTable
    Dim dt4 As New DataTable

For i As Integer = 0 To dt_grid1.Rows.Count - 1
               Dim found As Boolean = False
               For j As Integer = 0 To dtserialleft.Rows.Count - 1
                   If dt_grid1("BarCode").ToString = dtserialleft("SerialNo").ToString Then
                       found = True
                   End If
               Next
               If found = False Then
                   dt_grid1.Rows.RemoveAt(i)
                  
               End If
           Next

Dim p_pageIndex as integer = 1
Dim p_pageOffset as integer = 10
Dim p_SearchQuery as String = "Kneepads"
Using context As New DBContext()

Dim pResults = _
(
    From pi In context.ProductInfo
    Join pik In context.ProductInfo_Keywords On pik.ProductID Equals pi.ProductID
    Where pik.Keywords.Contains(p_SearchQuery.Trim) _
    Or pik.Stream.Contains(p_SearchQuery.Trim) _
    Or pi.PartNumber.Contains(p_SearchQuery.Trim)
    Order By pi.PartNumber, pi.SDescription, pi.Description Descending
    Select
        pi.ProductID,
        pi.CategoryID,
        pi.SubCategoryID,
        pi.SDescription,
        pi.Description,
        pi.Thumbnail,
        pi.PartNumber,
        pi.Price    
)

Dim context As New DBContext()
Dim items As IQueryable(Of PRODUCTINFO) = context.ProductInfo

'Sort
items = items.OrderByDescending(Function(m) m.PartNumber).OrderByDescending(Function(m) m.SDescription).OrderByDescending(Function(m) m.Description)

'Filter by Rank
items = items.Where(Function(m) m.PartNumber.Contains(p_SearchQuery))

'Get a Single Page of Data
If (p_pageIndex = 1) Then
   items = items.Take(p_pageOffset)
Else
   items = items.Skip((p_pageIndex - 1) * p_pageOffset).Take(p_pageOffset)
End If

pResults = _
   items.AsEnumerable().Select(Function(pi) New searchIndex With
   {
       pi.ProductID,
       pi.CategoryID,
       pi.SubCategoryID,
       pi.SDescription,
       pi.Description,
       pi.Thumbnail,
       pi.PartNumber,
       pi.Price
   }
)