How to make a userfriendly sql query structure 2

[Xsi]

Hello people,

I have got a very long Query.
my goal is to make a very user-friendly interface where the user can select from lots different query criteria.

I did try to create an arraylist where each item presents a SQL column/SELECT/WHERE from the db example:

            Dim QueryList As New ArrayList()
            QueryList.Add("SQLStr =")
            QueryList.Add("SELECT")
            QueryList.Add("tt.ordernr 'Order number'")
            QueryList.Add("tt.offertnr 'Quotation number'")
            QueryList.Add("WHERE")
            QueryList.Add("(DATEPART(YEAR,tt.date)=@datum")

I found the problem I need to add a comma "," between all the elements to except the first and the last.

which is the best way to create a such of program?
I believe it must be a better way to make this?
I thought about use a datagridview/datatable
also dataset and sql parameters.

Thank you in advance

 

[pmegan]

The best way to do this is to make your query a stored procedure in the database, and pass the user selections in as parameters. Running dynamic queries against a database can cause security problems.

If you can't create a stored procedure, I find it easier to create a template for the query and fill in the values using string.format. The nice thing about doing it this way is that you can write the query on a database and test it, then just change your test values to placeholders, ({0}, {1}, etc), for your application.

I'd use brackets instead of single quotes for the field names. Single quotes are used to identify literal strings and can throw errors when used like this.

Dim strTmplt as string = "SELECT tt.ordernr [Order number], tt.offertnr [Quotation number], WHERE (DATEPART(YEAR,tt.date)={0}"
Dim strQuery as string = [String].Format(strTmplt, @datum)

 

[softhemc]

That's easily done using your code.


For each column AFTER THE FIRST column in your SELECT clause, prefix the column name with a comma as:

[tt]
QueryList.Add("SELECT")
QueryList.Add("tt.ordernr 'Order number'")
QueryList.Add(",tt.offertnr 'Quotation number'")
[/tt]

By adding it before the second and subsequent columns you will not end up with a trailing comma.

 

[softhemc]

Also don't forget when you are building your query to ensure that spaces are entered appropriately eg

" WHERE "

This save you having to handle that when you put all the parts together.

 

[Xsi]

could you help me with a code snippet for that?

as you know its never a comma in beginning before the first element either the last element.

Thank you in advance.

 

[softhemc]

Unfortunately I'm doing a VS update at the moment and that is likely to take an hour or more it's a 12GB install

So the following has not been tested. Keeping to your method of storing these elements - which will involve the least changes to your code:

[tt]
With QueryList
.Add("SELECT "
.Add("ordernr 'Order number' ")
.Add(",offertnr 'Quotation number' ")
.Add(",Column3 'Column 3' ")
.Add(",tt.Column4 'Colum 4' ")
.Add(" FROM ")
.Add(" tt ")
.Add(" WHERE ")
.Add(DATEPART(YEAR,tt.date)=@datum")
.Add(" AND ")
.Add(" ordernr = 12345 ")
End With
[/tt]

Using the With construct saves you from having to type QueryList on each line.

Then when you are ready to put it all together use a StringBuilder, which I think is in System.Text
So either add System.Text to your Imports section (which will save you having to reference System.Text as I've done below

[tt]Dim sb as System.Text.StringBuilder = New System.Text.Stringbuilder[/tt]

'If you have added System.Text to your Imports then the above line will be
'[tt]Dim sb As StringBuilder = New StringBuilder[/tt]
[tt]
With QueryList
For a As Integer = 0 To .Count - 1 'it might be .Length - 1 (I can't remember, but Intellisense will show you)
sb.Add(.Items(a))
Next
End With
[/tt]

Then to use your query
[tt]
Dim MyQuery As String = sb.ToString
[/tt]

Although you don't need a new string variable, in your CommandText???? parameter for the connection you could simply use sb.ToString directly.

Please remember the above snippets where written around your code structure and that I can't run Visual Studio for the moment, so you may need to make some changes, though hopefully not too many.

 

[pmegan]

Just out of curiosity, why use the ArrayList at all if you're going to feed all the elements into a StringBuilder? It seems like extra work and processing to add all the array elements, then loop through them to add them to a StringBuilder. Why not just bypass the ArrayList with something like this?

Dim sb As New System.Text.StringBuilder
        With sb
            .AppendLine("SELECT")
            .AppendLine("ordernr [Order number]")
            .AppendLine(",offertnr [Quotation number]")
            .AppendLine(",Column3 [Column 3]")
            .AppendLine(",tt.Column4 [Colum 4]")
            .AppendLine("FROM")
            .AppendLine("tt")
            .AppendLine("WHERE")
            .AppendLine("DATEPART(YEAR,tt.date)= @datum")
            .AppendLine("AND")
            .AppendLine("ordernr=12345")
        End With

        Dim MyQuery As String = sb.ToString.Replace(VbCrLf, " ")

Whenever I build SQL queries, I use AppendLine and skip the spaces, then use sb.ToString.Replace(VbCrLf, " ") to build the final query. It just helps avoid SQL syntax errors at runtime.

You may also have syntax issues with this when the query runs: " DATEPART(YEAR,tt.date) = @datum ", but it's unclear what @datum is. Passing a variable into a SQL query can't be done inside double quotes. If it's just a placeholder you added for demonstration purposes then it's not problem, but if you're going to use a variable from your VB code in the query, it won't work. You'll have to change that line to .Append(" DATEPART(YEAR,tt.date) = ").Append(myYearVariable.ToString()).Append(" ").

 

[softhemc]

I tried to keep as close as possible to elmnas's current code layout - and without VS available to test any other format I felt that that was the safest approach to provide a working/workable solution. I agree I was unsure of @datum, but decided to stick with it at least for now.

Additionally an SP would still require the parameters to be collected in some way.

I generally use a template select/update/insert string into which I place the required elements. I have a function that I wrote many years ago to handle this. The template consists of SELECT/UPDATE/INSERT placeholder for column list FROM placeholder for table placeholder for optional JOIN ON criteria WHERE placeholder for where criteria placeholder for optional HAVING and ORDER BY clauses when several columns, joins and/or criteria are involved. But I include spaces at each point and always PREFIX list items with a comma for the second and subsequent items as opposed to POSFIXing which could leave a trailing comma to clean up. For smaller simpler situations I use a method not dissimilar to the one you have posted.

 

[Xsi]

Thank you so much for this!! I will test it very soon I hope this is a good solution!

 

[Xsi]

Hi @pmegan,

Thank you for very good answer.
about @datum it's something I need to keep. (I actually could replace with anything).

 

[pmegan]

No problem, glad I was able to help.

 

[Xsi]

I was looking on this tutorial atm.

I wonde if this is something that could fit my purposes.

http://www.dreamincode.net/forums/t...y-a-database-parameterizing-your-sql-queries/

Thank you in advance.

 

[Xsi]

@pmegan,

I wonder how do I remove elements from the stringbuilder?

thank you in advance.

 

[pmegan]

Parametized queries are great, but only if the structure of the query isn't going to change much. It sounds like your program is going to give the user a way of defining the result set and filters on the fly, which won't work with this kind of query. You would have to create a template for every combination of results and filters.

The StringBuilder doesn't really have elements like an array, it's really just a concatenation tool. I wouldn't try to remove parts of the string. Whenever I do stuff like this I create a function that builds the query, and call it when a button is clicked to run the query.

 

[softhemc]

In fact using a List(Of String) or ArrayList as you have done gives the most flexibility.

 

[Xsi]

@pmegan
ok I understand then I don't want to use SQL parameters. my query have to be very flexible.
Thank you

 

[pmegan]

It seems like this is getting more complicated than it needs to be. I'm not sure why you would need to remove sections of a query. I certainly wouldn't do that. If there's a procedure that creates the query dynamically, I'd just create a new query when the criteria changes rather than try to remove a piece of text.

Can you give a little more detail on how this is going to work? I'm assuming there will be a form where the user will pick desired result fields for the Select clause, and some text boxes or drop downs with the filter criteria for the Where clause. Is that correct?

I've done a couple of programs that are similar to that, so made a quick form with 4 checkboxes to allow users to add up to 4 columns
Name: chkField_1, tag: dbField1, text: Field 1
Name: chkField_2, tag: dbField2, text: Field 2
Name: chkField_3, tag: dbField3, text: Field 3
Name: chkField_4, tag: dbField4, text: Field 4

2 MaskedTextBox controls for the year and order number. These are hardcoded so I didn't use tags.
txtYear
txtOrderNum

And 4 textboxes for additional filters
Name: txtWhere_1, tag: dbfilter1
Name: txtWhere_2, tag: dbfilter2
Name: txtWhere_3, tag: dbfilter3
Name: txtWhere_4, tag: dbfilter4

In an actual program, the tags would be set to the associated column name from the database. This allows you to pass the controls into generic processing functions instead of using a giant list of if/then statements.

Private Function MakeQuery() As String

        ' function to create a SQL query from user supplied criteria
        ' checkboxes and textboxes on the from must have the associated 
        ' data table fieldname set as the tag
        ' result set column names will be taken from the checkbox text

        Dim sb As New System.Text.StringBuilder
        Dim strQuery As String = [String].Empty

        Try

            With sb

                .AppendLine("SELECT")
                .AppendLine("ordernr [Order number]")
                .AppendLine(",offertnr [Quotation number]")

                BuildSelectLine(sb, chkField_1)
                BuildSelectLine(sb, chkField_2)
                BuildSelectLine(sb, chkField_3)
                BuildSelectLine(sb, chkField_4)

                .AppendLine("FROM")
                .AppendLine("tt")
                .AppendLine("WHERE 0 = 0")

                ' if the year textbox has a value, add it to the query
                If Not txtYear.Text = "" Then
                    .AppendLine("AND DATEPART(YEAR,tt.date) = " & txtYear.Text)
                End If

                BuildWhereLine(sb, txtOrderNum)
                BuildWhereLine(sb, txtWhere_1)
                BuildWhereLine(sb, txtWhere_2)
                BuildWhereLine(sb, txtWhere_3)
                BuildWhereLine(sb, txtWhere_4)

            End With

            strQuery = sb.ToString '.Replace(vbCrLf, " ")

        Catch ex As Exception

        End Try

        Return strQuery

    End Function

    ''' <summary>
    ''' Adds and element to the SELECT query if checked
    ''' </summary>
    ''' <param name="sb">stringbuilder passed in by reference</param>
    ''' <param name="cb">the checkbox to process</param>
    ''' <remarks></remarks>
    Private Sub BuildSelectLine(ByRef sb As System.Text.StringBuilder, cb As CheckBox)
        If cb.Checked Then
            sb.AppendLine("," & cb.Tag & " [" & cb.Text & "]")
        End If
    End Sub

    ''' <summary>
    ''' Adds a condition to the WHERE clause if the textbox is not empty
    ''' </summary>
    ''' <param name="sb">stringbuilder passed in by reference</param>
    ''' <param name="txt">user entered filter criteria</param>
    ''' <remarks></remarks>
    Private Sub BuildWhereLine(ByRef sb As System.Text.StringBuilder, txt As TextBox)
        If txt.Text <> "" Then
            sb.AppendLine("AND " & txt.Tag & " = '" & txt.Text & "'")
        End If
    End Sub

    ''' <summary>
    ''' Adds a condition to the WHERE clause if the maskedtextbox is not empty
    ''' </summary>
    ''' <param name="sb">stringbuilder passed in by reference</param>
    ''' <param name="txt">user entered filter criteria</param>
    ''' <remarks></remarks>
    Private Sub BuildWhereLine(ByRef sb As System.Text.StringBuilder, txt As MaskedTextBox)
        If txt.Text <> "" Then
            sb.AppendLine("AND " & txt.Tag & " = '" & txt.Text & "'")
        End If
    End Sub