I am trying to make an UPDATE STATEMENT generator using C# Winforms. I would like to build a statement generator which will print statements like this:
UPDATE Customers
SET ContactName='Alfred Schmidt', City='Hamburg'
WHERE CustomerName='Alfreds Futterkiste';
But my problem is, how can I let the user paste in multiple values for each SET? My end user is likely to build a list using a spreadsheet and will want to paste in the values into a multi-line text box. I could create 2 multi-line text boxes, but what if i have more SET columns i need to add?
3
Users should not be doing anything with SQL.
Your application should taking values that the Users enter, validating them, assembling them, as required, into the SQL statement preferably using Parameters instead of building raw SQL and then executing that SQL.
I would suggest that a multi-line textbox is a poor choice of User Interface for this.
Even if you set up one line per [database table] field, as in …
---------
Customer Name: | Fred |
Contact Name: | Wilma |
City: | Bedrock |
---------
… what would happen if they [accidentally] deleted one of those rows?
Data would be “associated” with the previous field, which would probably cause chaos.
This is the sort of thing that Grid Controls were created for.
You can control what each cell represents/ contains and, from other metadata, you can work out what type of data each item is, how to validate it and how to store it (i.e. what Type of Parameter to create).
Alternatively, if you want a “bulk load” facility from a spreadsheet, then create one. You then have to think about validation a bit differently, though.
What if they supply a spreadsheet with twenty rows, three of which contain errant data that your application rejects? Do you reject all twenty and force the user to reprocess the whole lot? It might actually be easier if you can write the error [messages] back into the spreadsheet for them to work through.
Alternatively, you might take the given spreadsheet and return them a new one containing only the rejected rows; less for them to work through but more complex to generate. YMMV.
I would encourage the user to export the spreadsheet instead. Each column header becomes the field to be set, and each row becomes one of the values. You write code to import the spreadsheet.
Place simple text fields, check if user entered anything, then check/avoid SQL injection attempts, then replace ‘Alfred Schmidt’ with value1, etc.