How to use SQL parameters with IF conditions and what datatype is equal to the NVARCHAR. OLeDB, VB.NET

Question

So my question actually has more than 2 parts but all linked with protecting my application against SQL injection.

Lately I am rebuilding my application to make it SQL injection proof. My application is written in VB.NET and I am using Parameters.Add to protect the queries.

UPDATE first question:

Found out that the NVARCHAR datatype is equal to the WChar datatype in OLEDB. When hovering over WChar it says in the description it is WSTR.

I am having a few questions though of which I cant find the answer on the web. The first one, and I guess the easiest one is what the datatype in OleDB is of the NVARCHAR. When doing some research I found this link https://msdn.microsoft.com/en-us/library/ms130984.aspx which states that in OleDB the DBTYPE_WSTR datatype is the same as the NVARCHAR (which I am using in my t-SQL server). However when adding parameters in my VB application there is no such datatype available (WSTR). Some options are the VarChar, VarWChar, WChar, LongVarChar, LongVarWChar.

The question is, which one is the right one? I assume it is VarChar.

-------UPDATED UNTIL HERE-----

The second question is about how to handle parameters when a IF condition is involved in the query (see the example below).

sSQL1 = "SELECT [Omschrijving], [Nummer], [OrderType], [Orderdatum] FROM [Orders] WHERE [OrderDatum]>dateadd(""ww"",-4,GetDate()) "
        If sOrderType <> "" Then    
            sSQL1 &= "AND ([OrderType]=""" & sOrderType & """)"

        End If
        sSQL1 &= "ORDER BY [Orders].[Nummer] DESC;"

        lstOrder.Items.Clear()
        Try
            OpenConn()
            cmd = New OleDbCommand(sSQL1, cn)
            dr = cmd.ExecuteReader

            While dr.Read()
                lstOrder.Items.Add(dr("nummer") & " - " & dr("Omschrijving"))
            End While

        Catch
            Debug.Print(sSQL1)
            MsgBox("Error: " & sSQL1)
        End Try

        dr.Close()
        CloseConn()

How should I rewrite this with using parameters? Do I need to instantiate the cmd = New OleDbCommand(sSQL1, cn) in the if condition together with the Parameters.Add ? But I guess this would also mean I have to open the connection earlier?

I thought about rewriting in something like this:

OpenConn()

        sSQL1 = "SELECT [Omschrijving], [Nummer], [OrderType], [Orderdatum] FROM [Orders] WHERE [OrderDatum]>dateadd(""ww"",-4,GetDate()) "
        If sOrderType <> "" Then    
            sSQL1 &= "AND ([OrderType]=?)"

            cmd = New OleDbCommand(sSQL1, cn)
            cmd.CommandType = CommandType.StoredProcedure

            cmd.Parameters.Add("@OrderType", OleDbType.VarChar).Value = sOrderType
        Else
            cmd = New OleDbCommand(sSQL1, cn)
        End If
        sSQL1 &= "ORDER BY [Orders].[Nummer] DESC;"

        lstOrder.Items.Clear()
        Try

            dr = cmd.ExecuteReader

            While dr.Read()
                lstOrder.Items.Add(dr("nummer") & " - " & dr("Omschrijving"))
            End While

        Catch
            Debug.Print(sSQL1)
            MsgBox("Error: " & sSQL1)
        End Try

No idea if this works though (and for now I sadly can't test it).

Another question is: Do I need to use parameters for the dateadd (see query above) in the query as well? And if I do, how?

The last question is, when I am using the Parameters.Add, is it best to give a size value as well or is this not necessary?

Thank you in advance!

Answer 1

My answer to your first question that I think this is the list of equal data-types you want:

SQL Server | OLEDB (ADO => ad+...)
-----------+---------------
char       | Char 
nchar      | WChar
varchar    | VarChar
nvarchar   | VarWChar
text       | LongVarChar
ntext      | LongWVarChar