Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Partial Public Class StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure> _
Public Shared Sub search_orders_vb ( _
       Orderid    As SqlInt32, _
       Fromdate   As SqlDateTime, _
       Todate     As SqlDateTime, _
       Minprice   As SqlMoney,    _
       Maxprice   As SqlMoney,    _
       Custid     As SqlString,   _
       Custname   As SqlString,   _
       City       As SqlString,   _
       Region     As SqlString,   _
       Country    As SqlString,   _
       Prodid     As SqlInt32,    _
       Prodname   As SqlString,   _
       Debug      As SqlBoolean)

    Dim Query As String
    Dim Command As SqlCommand = new SqlCommand()

    Query = "SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity, " & VbCrLf & _
            "        c.CustomerID, c.CompanyName, c.Address, c.City,   " & VbCrLf & _
            "        c.Region, c.PostalCode, c.Country, c.Phone, " & VbCrLf & _
            "        p.ProductID, p.ProductName, p.UnitsInStock, " & VbCrLf & _
            "        p.UnitsOnOrder " & VbCrLf & _
            "  FROM   dbo.Orders o " & VbCrLf & _
            "  JOIN   dbo.[Order Details] od ON o.OrderID = od.OrderID " & VbCrLf & _
            "  JOIN   dbo.Customers c ON o.CustomerID = c.CustomerID " & VbCrLf & _
            "  JOIN   dbo.Products p ON p.ProductID = od.ProductID " & VbCrLf & _
            "  WHERE  1 = 1 " & VbCrLf

    If Not Orderid.IsNull Then
       Query &= " AND o.OrderID = @orderid " & _
                " AND od.OrderID = @orderid" & VbCrLf
       Command.Parameters.Add("@orderid", SqlDbType.Int)
       Command.Parameters("@orderid").Value = Orderid
       Command.Parameters("@orderid").Direction = ParameterDirection.Input
    End if

    If Not Fromdate.IsNull Then
       Query &= " AND o.OrderDate >= @fromdate" & VbCrLf
       Command.Parameters.Add("@fromdate", SqlDbType.DateTime)
       Command.Parameters("@fromdate").Value = Fromdate
       Command.Parameters("@fromdate").Direction = ParameterDirection.Input
    End If

    If Not Todate.IsNull Then
       Query &= " AND o.OrderDate <= @todate" & VbCrLf
       Command.Parameters.Add("@todate", SqlDbType.DateTime)
       Command.Parameters("@todate").Value = Todate
       Command.Parameters("@todate").Direction = ParameterDirection.Input
    End If

    If Not Minprice.IsNull Then
       Query &= " AND od.UnitPrice >= @minprice" & VbCrLf
       Command.Parameters.Add("@minprice", SqlDbType.Money)
       Command.Parameters("@minprice").Value = Minprice
       Command.Parameters("@minprice").Direction = ParameterDirection.Input
    End If

    If Not Maxprice.IsNull Then
       Query &= " AND od.UnitPrice <= @maxprice" & VbCrLf
       Command.Parameters.Add("@maxprice", SqlDbType.Money)
       Command.Parameters("@maxprice").Value = Maxprice
       Command.Parameters("@maxprice").Direction = ParameterDirection.Input
    End If

    If Not Custid.IsNull Then
       Query &= " AND o.CustomerID = @custid" & _
                " AND c.CustomerID = @custid" & VbCrLf
       Command.Parameters.Add("@custid", SqlDbType.NChar, 5)
       Command.Parameters("@custid").Value = Custid
       Command.Parameters("@custid").Direction = ParameterDirection.Input
    End If

    If Not Custname.IsNull Then
       Query &= " AND c.CompanyName LIKE @custname + '%'" & VbCrLf
       Command.Parameters.Add("@custname", SqlDbType.NVarChar, 40)
       Command.Parameters("@custname").Value = Custname
       Command.Parameters("@custname").Direction = ParameterDirection.Input
    End If

    If Not City.IsNull Then
       Query &= " AND c.City = @city" & VbCrLf
       Command.Parameters.Add("@city", SqlDbType.NVarChar, 15)
       Command.Parameters("@city").Value = City
       Command.Parameters("@city").Direction = ParameterDirection.Input
    End If

    If Not Region.IsNull Then
       Query &= " AND c.Region = @region" & VbCrLf
       Command.Parameters.Add("@region", SqlDbType.NVarChar, 15)
       Command.Parameters("@region").Value = Region
       Command.Parameters("@region").Direction = ParameterDirection.Input
    End If

    If Not Country.IsNull Then
       Query &= " AND c.Country = @country" & VbCrLf
       Command.Parameters.Add("@country", SqlDbType.NVarChar, 15)
       Command.Parameters("@country").Value = Country
       Command.Parameters("@country").Direction = ParameterDirection.Input
    End If

    If Not Prodid.IsNull Then
       Query &= " AND od.ProductID = @prodid" & _
                " AND p.ProductID = @prodid" & VbCrLf
       Command.Parameters.Add("@prodid", SqlDbType.Int)
       Command.Parameters("@prodid").Value = Prodid
       Command.Parameters("@prodid").Direction = ParameterDirection.Input
    End If

    If Not Prodname.IsNull Then
       Query &= " AND p.ProductName LIKE @prodname + '%'" & VbCrLf
       Command.Parameters.Add("@prodname", SqlDbType.NVarChar, 40)
       Command.Parameters("@prodname").Value = Prodname
       Command.Parameters("@prodname").Direction = ParameterDirection.Input
    End If

    Query &= " ORDER BY o.OrderID"

    Using Connection As new SqlConnection("context connection=true")
        Connection.Open()

        if Debug Then
           SqlContext.Pipe.Send(Query)
        End If

        Command.CommandType = CommandType.Text
        Command.CommandText = Query
        Command.Connection  = Connection
        SqlContext.Pipe.ExecuteAndSend(Command)
    End Using
End Sub
End Class