Automated Insert SQL Query Parameterised?

[Barrabutus]

Hi guys

I wrote this little function to allow me to pass an ArrayList of Strings and then based on the table passed to pre-build a SQLCommand like so

INSERT INTO `table` (`column`,`column`) VALUES (`value1`,`value2`);

The function for this is here:

   Public Function _SQLInsertBuilder(ByVal values As ArrayList, ByVal Table As String) As String

        Dim commandString As String
        Dim columns As ArrayList = GetTables(Table)
        commandString = "INSERT INTO `" & Table & "` ("
        For Each column As String In columns
            commandString += "`" & column & "`,"
        Next
        'Strip the last comma
        commandString = commandString.Substring(0, commandString.Count - 1)
        commandString += ") VALUES ("
        For Each value As String In values
            commandString += "`" & value & "`,"
            ' We have to parameterize the values : TODO SAFETY/SECURITY
            frmDebug.lstMessages.Items.Add("Current Value is " & value)
        Next
        'Strip the last comma
        commandString = commandString.Substring(0, commandString.Count - 1)
        commandString += ")"
        Return commandString
    End Function

Now i know that much like mysql_real_escape_string() VB.NET has a Parameter ability to make querys safer.

But what i want to know is if a query that can build itself like this is a GOOD idea or a TERRIBLE one.

If its good how could i parameterise the values during the build of the string.

Many Thanks Barra.

[jmcilhinney]

It's a terrible idea to use any mechanism that inserts variables into SQL code as string literals. You should absolutely use parameters. For an example of how you might do that, follow the Blog link in my signature and check out my Database Independent DAL post.

Also, I don't really understand how a method can be named "GetTables" and then return an ArrayList of column names as Strings. Surely a method that gets column names should be named "GetColumnNames".

[Barrabutus]

Thanks for the reply Jmc

Thats a good point getTables should be renamed to getColumnNames lol

I do understand that using a method like i posted would be a bad idea but would it still be a bad idea if it was able to parameterise the values before performing the insert?

Regards Barra

EDIT :

Ive just read your DAL Post , I,ll need to reread it a few times to see how it works properly lol

But this function looks to me very similar to what the above achieves just in a parameterised environment. Or am i wrong?

Public Function GetCommand( _ByVal commandText As String, _ByVal parameters As IDictionary(Of String, Object)) As DbCommand    
Dim command As DbCommand = Me.GetCommand(commandText)     
For Each parameterName As String In parameters.Keys        
command.Parameters.Add(Me.GetParameter(parameterName,  parameters(parameterName)))    
Next parameterName    
 Return command
End Function

[jmcilhinney]

You are not wrong.

[per-i]

Hi,

could you please give me some advice on the following? I have read your blog jmc

In our program everything is loaded dynamicly. So the users opens a form for let's say a certain product. The program then creates the form and a table name and id value is passed to this form. What happens then is :

1. A stored procedure is executed which gets info about the wanted table (paramters that is passed is @table_name). This procedure returns some info like the primarykey column and all the columns that are in the table.

2. Based on the returned column info the form is created dynamicly. It will add text boxes, checkboxes etc based on the column type.

3. A stored procedure is executed whicht gets the actual data (parameters that are passed are @table_name, @prim_key, @id)

4. The controls in the form are given the correct value based on the data from step 3

This all works fin. No speed issues because it is multi threaded.

For deleting there also is a stored procedure in which we pass the same parameters again (@table_name, @prim_key, @id). So we have got just a few stored procedures for 90% of the things a users wants to do).

But now for the insert and update. This is done in a way described by Barrabutus. So we create a sql string dynamicly and just pass it to a command. At this moment we loop through the controls on the form and we can see if they need updating to the database. Since i know the tablename (and prim_key and row id) i can generate an update sql string.

I know that isn't a good way to do it, but i don't know how we could do it differently. Is there a way to use the parameters? Since the amount of columns that need updating can differ. and the table that needs updating is variable also in our form.

regards,

per-i

[jmcilhinney]

Isn't that exactly what the code of mine that you posted previously does? You've just got to put all the parameter names and values into a Dictionary and build the SQL code. That's easy enough to do simply by looping through your column names.

[per-i]

Hi jmc,

sorry if a am asking something very stupid... But i see what your code should do and i understand it. But how do i use it in my situation then?

There are 3 fixed parameters for the update :

1. @table_name
2. @prim_key
3. @Row_id

And then there are multiple none fixed parameters i need to pass (each a pair of a column name and it's value)

So that could be

1. Products
2. Product_ID
3. 10

and then for example
Product_Name - "Apple"
Product_Price - 2,50

I don't understand how to pass this correctly to the sql server then. I understand that it is easy to create all the parameters in the command object. But what to use as command text? Create a string for that dynamicly?

regards,

per-i

[jmcilhinney]

You can't pass identifiers as parameters, so the table name and the column names have to go into the literal String. Lets' say that you have a table name and an array of column names. You could create an UPDATE statement like this:

Private Function CreateUpdateStatement(tableName As String, primaryKeyName As String, columnNames As String()) As String
    Dim criteria As New List(Of String)
 
    For Each columnName In columnNames
        criteria.Add(String.Format("[{0}] = @{0}", columnName))
    Next
 
    Return String.Format("UPDATE {0} SET {1} WHERE [{2}] = @{2}",
                         tableName,
                         String.Join(", ", criteria),
                         primaryKeyName)
End Function

You can then pass that String to a method like the one of mine you posted earlier, along with a Dictionary of parameter names and values, to create the command with text and parameters.

[per-i]

Hi Jim,

thanks! I now understand what you do. I didn't understood that you wanted me to passed the parameter name in the command string also (which is so obvious, damm i feel stupid now ).

I will test it this afternoon. But this approach should work great, also because we already keep track of the changed columns in a dictionary already (as soon as the user changes a textbox for example).

Regards,

per-i