use dynamic sql for datagrid

[pfechner]

apologies first if there is an answer to my query on this forum somewhere. i have searched but the results have either been too confusing for me to understand or have not applied to my situation.

I have an SQL select statement which is generated by items in a list box using this code:

mystring = Nothing 
myindex = Nothing 
lstcriteria.SelectedIndex = 0 
Do Until myindex = lstprecincts.Items.Count 
lstprecincts.SelectedIndex = myindex 
mystring = mystring & " " & lstcriteria.Text 
myindex = myindex + 1 
Loop

it is a basic sql string which looks like:

select x from a where b=c

i need to bind the results to a datagrid and understand that using datasets and datatables is the way to do this.
I can create these manually using the designer, but in this instance the number of columns etc in the result set can change.

so the question is - how do i programmically create a dataset, add a datatable and fill it using a string as the sql command?

please help.

Paul

 

[cjard]

Hmm.

OK.

I need you to do 3 things.

1) Read the PQ link in my signature
2) Read the DW2 link in my signature, section on Creating A Simple Data App
3) Youre building a dynamic sql for which system? Oracle? SQLS? Access?

You will dynamically create a parameterised query (1) that fills a typed dataset (2). I will help with the vendor specific elements (3)

 

[Stonkie]

        Dim grid As New DataGridView()
        Dim connection As New OleDbConnection("Provider=OleDB;")
        Dim command As New OleDbCommand("SELECT * FROM mytable", connection)
        Dim adapter As New OleDbDataAdapter(command)
        Dim table As DataTable = Nothing

        If grid.DataSource Is Nothing Then
            table = New DataTable()
            grid.DataSource = table
        Else
            table = DirectCast(grid.DataSource, DataTable)
            table.Clear()
        End If

        adapter.Fill(table) ' You might have to add a fillshema if it changes

I'm not so sure this work as I don't have a database to try it on right now, but I've used similar code for a search utility.

 

[cjard]

I'm not so sure this work as I don't have a database to try it on right now, but I've used similar code for a search utility.

I generally advise people who are on .NET 2, to use the new methods of data access in .NET 2 (getting the IDE to generate the code for you). Whether you do, or not is of course your choice, but consider if it's fair to carry on teaching newbies the old way when there is a better, faster way out there that promotes more sensible programming practices and more secure code..

 

[Stonkie]

Yeah, I'd recommend this too most of the time, but I was left under the impression that the columns he uses in his condition are dynamically chosen, not static. Using parameters in the command would do no good so he can't build these in the designer...

Obviously, using autogenerated code scales much better and proves more reusable.

 

[cjard]

Yeah, I'd recommend this too most of the time, but I was left under the impression that the columns he uses in his condition are dynamically chosen, not static.

In this situation you make multiple queries in the designer

Using parameters in the command would do no good so he can't build these in the designer...

In this situation you would write teh command so as to implement the choice logic:
SELECT * FROM table WHERE
(col1 = @col1 OR @col1 IS NULL) AND
(col2 = @col2 OR @col2 IS NULL)

Now, to "wildcard" any column simply make it null, it will not apply for query purposes (predicate always returns true; other predicates must be used)

 

[Stonkie]

This is all great in theory, but when you have multiple linked tables and many searchable fields with a datagridview displaying user chosen columns (in user chosen order), theory tends to be difficult to implement.

It is all a balance between extensibility, reusing code, efficiency, development time, etc.

I want to add that my answer may seem simpler on first read, but don't overlook cjard's, it is the best one in most case.

 

[cjard]

This is all great in theory, but when you have multiple linked tables and many searchable fields with a datagridview displaying user chosen columns (in user chosen order), theory tends to be difficult to implement.

Yeah, there comes a point where you have to tell the whiners in the business that they cant have a "search any field for any value with any number of results shown in any columns the user picks in any order" because what you end up creating is something that looks like the MS Access Query Grid and all your users need to know SQL

I do actually use dynamic sql in a few places, building an SQL like:

SELECT * FROM search_personForm_view WHERE 1=1 AND
...

Where ... is replaced by all the chosen name=value pairs. Of course, it's built in parameter compatible ways for speed and protection from rogue data, and the VIEW in the database dictates what is shown (it includes all columns for searching purposes, and also a number of additional columns as indicated by a leading space in the column name, which are rendered into the datagrid at runtime)

CREATE VIEW search_personForm_view AS
SELECT
  person_name,
  address_age
  person_name as " Full Name"
  address_age / 365 || ' years' as " Time at Address"
FROM
  person INNER JOIN address USING(address_id)

It is all a balance between extensibility, reusing code, efficiency, development time, etc.

yep.. What I described above is about as far as i'm prepared to go in the name of those 4 paragons when given the "search any field of any form" requirement

I want to add that my answer may seem simpler on first read, but don't overlook cjard's, it is the best one in most case.

Remember, managing other people's expectations is the most important part of any product delivery. Tell them you can deliver 80% of what they ask in 20% of the time, then deliver 85%, but say that the remaining was prohibitively expensive and didnt offer extra cost-to-efficiency savings..