sql aliases and datatables

[sillysod]

Hello fellas,

A little background for you in my app against some fields the user has a browse button, this button brings up a listview with the availiable field values.

The user is able to change what fields are shown in this list, for example the customer browser might just show
Customer Code
Customer Name
But the user needs to see the post code too in this list and also be able to search against this field. The way this is implemented is the listview gets its column names / rows from a datatable which is populated view a SQL string stored in the my.settings of my app. This approach allows the user to modify the browse window by use of aliases in that SQL String

so field CUS_CODE can become Code or Cus Code or whatever the user wants in the browser.

My problem is when i come to build the WHERE clause which i will append to the end of this SQL string to filter the results given
(a second tab on the browser is loaded with a text box for every field in result set, which i then use to build a WHERE clause)
Because i use aliases in the SQL statement to make it easier for user, i now need to get back to the original field name from the alias, at the moment i get an error because column name "Code" doesnt exist in the customers table.

Hopefully you see what i mean, thanks in advance

 

[techsmith]

The problem seems to be that you need a list/dictionary based class that can give you 2 methods getcolumnname(alias) and getalias(columnname). I'm not sure if this exists, from my limited understanding of the IDictionary interface it's always designed to do getvalue(key) and not getkey(value).

You can create a new class with 2 dictionaries (hashtable or ListDictionary depending on size of list) kept in synch i.e. Add(Alias, ColumnName) adds a row to 2 dictionaries where dic1 uses alias as the key and dic2 uses columnname as the key.

Similarly Remove, RemoveAt etc. affect both dictionaries to keep them in synch

Hope this helps

 

[sillysod]

Cheers,

I did originally consider using a "translation" table within the SQL Database, this would allow user to enter a more descriptive field name.

The reason I decided against this was i think it reduces the flexibility which i can achieve.

At the moment the user can enter any SQL string they desire (the only restriction being that column 0 must hold the value which the application requires ), the listview can have any number of fields, with any column names they desire. For example the user could define a SQL string which summarises transactional data eg
Customer Code
Customer Name
YTD Turnover

Previously I used a sort of static form for each type of browse button. So customer browse would bring up a list of customers (which could show anything the user required) and this was supplemented on another tab with a filter window which was hard coded with several "key" customer fields which i feel the user might want to search against.

Currently im working on a more dynamic query window which looks at the columns of the resultant datatable and adds the ability to filter based on any of these columns
allowing for searches such as
YTD Turnover > 10,000

I would really like to crack this as i think it would be a very nice feature of the app, hopefully someone can suggest a way around this.

One idea i toyed with last night was creating a view in the SQL backend for each browser type, and allowing the user to modify this query, which would allow me to run add a WHERE clause using the alias's, however vb threw an exception and wouldnt recognise that the view existed?

 

[techsmith]

How about a collection of aliases (probably need a class to hold the browser/user/columnname/alias data with the appropriate getColumnName and getAlias functions).

When you need to apply a filter you use the getColumnName() function. When you fill the grid source you use the getAlias() function to set the columnname of the grid source.

I would also add a collection of users' views of the data i.e. the filtered aliased lists that they have saved.

Serialise the collections and your away.

 

[sillysod]

hmmm, but how would this get around being able to filter on fields which are functions ? such as the YTD Turnover field in above example, which would involve a query linked to the invoices table and summarising the data?

If theres a way around that, it sounds like a plan. I dont pretend to know much about aliases and serialising, but im sure i could find out about that somewhere on net

 

[sillysod]

Thanks Techsmith, you have introduced me to a pretty funky area of VB that i didnt know about.

I now have some new ideas about how i can tidy this whole "browser" up and standardise it in my code while still allowing the same flexibility.

I shall commence rewriting the whole thing this evening and report back, hopefully with some form of example