Save data from custom sql query bound to a DataGrid in C#

Question

So I have this problem - I want to allow users of my app to run select queries against a PostgreSql database and I want the data returned by those queries to be bound to a DataSet -> DataGrid. Problem is the "custom" part :). Of course when I try to edit data coming from a query with join clause, then the trouble starts. I'm getting an error saying that UpdateCommand couldn't be automatically generated because of lacking primary key column, which is expected (well, at least I was expecting it)... Solution I came up with is primitive, but good enough for me - I have a list of table names, and when one of those table names is found in custom query, then I want to generate my own UpdateCommand on the fly. So:

1. I'm having trouble writing regular expression that would get table names from query, so any help would be much appreciated :). Thing is tricky - you've got to remember that table name may also be found as a column name, so the regular expression should be able to filter out that cases.
2. Is it generally a good idea in my scenario? Maybe there's a simpler and/or more elegant solution to my problem?

Something like a pseudo code:

string[] names = new string[] { "table1", "table2" };
string customQuery = GetWhateverUserWroteInATextBox();

//what I want to do is to find name coming from names array in the customQuery

I'm calling it a custom query, because the user writes it, and I can't predict what the user input will be.

Answer 1

First Question:

As you said the regex is a little tricky ...

        string[] tableNames = new string[] { "table1", "table2" };
        List<string> fields = new List<string>();
        Regex rgx = new Regex(@"\w+(?=\s?,?)");
        MatchCollection matches = rgx.Matches(textBox1.Text);
        foreach (Match m in matches)
        {
            if (!tableNames.Contains(m.Value))
            {
                fields.Add(m.Value);
            }
        }

If U can convince your users that separate fields with , this works fine ;)

If my pattern does not match with your name styles you can make your own patterns and here is some instructions:

• MSDN: Regular Expression Language
• MSDN: Regex Class
• MSDN: How to: Search Strings Using Regular Expressions
• Stackoverflow Reference

Second Question:

1. The way that users enter their own query is not good, because:

   • For this kind of queries you have to inform users about table and field names. this means you let your guard down and accept some security risks.

   • It's not user friendly. Filling a text box with bunch of names and some hard rules is not simple for users. Note that for being a good developer you have to imagine all users are lazy and idiot.

   • You can not prevent users's access. If user can make their own query so they can simply make all queries for all of your records and tables. In that case maybe some information have to stay in shadows and you can't guaranty.

   • You can not convince users to fallow rules in making new queries and it will cause errors for you and users.

2. You can simply make your own interface.

   • A new interface is a new experience for users so they will loved that.