How to convert to parametrized query fashion

[ADQUSIT]

Hi. I've been using straight SQL queries for insert, update and delete in my last project and in my last couples of threads, i was highly advised to use the parametrized queries instead of straight one. This is what i was using in my program, which i have quit now and trying to fully adopt the parametrized fashion of querying.

What i was doing is that i had a module in which i had all function, which were receiving query data, processing upon it and sending the result back.
This is my module code:

These are all objects:

Imports System.Data.SqlClient
Imports System.Data
Imports System.IO

Module modCon

    Dim strCn As String = "Data Source = (local); Initial Catalog = AbidMIS; User ID = sa; Password = 123456;"
    Public cnSql As New SqlConnection(strCn)
    Public daSql As New SqlDataAdapter
    Public dsSql As New DataSet
    Public cmdSql1, cmdSql2 As New SqlCommand

Public Function Connect()

        Try
            If cnSql.State = ConnectionState.Closed Then
                cnSql.Open()
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

        Return cnSql
    End Function

After this i had Separate regions for Insert, Delete and Update:

#Region "Insertion"
    ''' <summary>
    ''' Simple Insert Function
    ''' </summary>
    ''' <param name="tblName"></param>
    ''' <param name="columns"></param>
    ''' <param name="parameters"></param>
    ''' <remarks></remarks>
    ''' 
    Public Sub Insert(ByVal tblName As String, ByVal columns As String, ByVal parameters As String)
        Try

            cmdSql1.CommandText = "insert into " & tblName & "(" & columns & ") values (" & parameters & ")"
            cmdSql1.Connection = Connect()
            cmdSql1.ExecuteNonQuery()

        Catch ex As Exception

            MessageBox.Show(ex.Message)
        End Try
    End Sub

#End Region

From the form submit button, the following query was executing sending the data into three parts, tblName, Columns and Parameters as string. This is the query:

Insert("ProductBasicInfo", "ProdID, ProdName, Description, Manufacturer, Store", txtProdID.Text.Trim & ", '" & txtProdName.Text.Trim & "','" & txtProdDesc.Text.Trim & "','" & txtProdManuf.Text.Trim & "', " & txtStore.Text.Trim)
            MessageBox.Show("Record inserted successfully")

.

Same is the case was with select and update queries and function at module etc.

Now what i want to start my new project with parametrized one, but how to do the above mentioned all in parametrized fashion. Pleassseee Guide me that how to leave the sql injection way back and join the parametrized one.

[cicatrix]

Here's an example (I took it directly from one of my projects):

Usage:

' you substitute actual values with parameter names in your sql command text (paramid):
Dim sqltext As String = "Select Field1 From MyTable Where FieldA=@paramid"
' You add a parameter named 'paramid' to the command and provide its value
' If you used plain text command its text wold have been:
' Select Field1 From MyTable Where FieldA=27"
Dim params As New Dictionary(Of String, Object) From {{"paramid", 27}}
Dim result As DataTable()
 
Call QueryDB(sqltext, result, params)

Public Shared Function QueryDB(sqlquery As String, ByRef dtresult As DataTable, Optional queryparams As Dictionary(Of String, Object) = Nothing) As OpResults
    Dim conn As New SqlConnection(m_connstr)
    Dim cmd As New SqlCommand(sqlquery, conn)
    If queryparams IsNot Nothing Then
                ' You add the parameters for your sql command here:
                ' The command text must have placeholders like @paramid containing the names of parameters to add.
                ' In this example we add a parameter named 'paramid' and its value = 27 (see above)
        For Each param As KeyValuePair(Of String, Object) In queryparams
            Dim value As Object = param.Value
            cmd.Parameters.AddWithValue(param.Key, value)
            ' foreach
        Next
    End If
 
    dtresult = New DataTable()
    Dim da As New SqlDataAdapter(cmd)
 
    Try
        conn.Open()
                da.Fill(dtresult)
            conn.Close()
    Catch ex As Exception
        Dim mb As System.Reflection.MethodBase = System.Reflection.MethodBase.GetCurrentMethod()
        Dim methodname As String = String.Format("{0}.{1}", mb.DeclaringType.Name, mb.Name)
        logger.Log(String.Format("{0} error: {1}", methodname, ex.Message))
        conn.Close()
        Return OpResults.DB_ERROR
    End Try
    Return OpResults._SUCCESS
End Function

[Evil_Giraffe]

Now what i want to start my new project with parametrized one, but how to do the above mentioned all in parametrized fashion.

You cannot parameterise everything that you've injected above. You cannot parameterise the table name or the columns, only the values. However, you shouldn't need to parameterise those first two, since you hard code them where you call Insert from, presumably? In which case, instead of passing a string containing the table name, a string containing the columns and a string containing the values, simply pass a query object with the table and columns baked into the string, and the values passed as query parameters.

Edit: That is to say, instead of your call to Insert, you might have a call like this:

Dim query As New SqlCommand("INSERT INTO ProductBasicInfo (ProdID, ProdName, Description, Manufacturer, Store) VALUES (@prodId, @prodName, @description, @manufacturer, @store)")
query.Parameters.AddWithValue("@prodId", txtProdID.Text.Trim)
query.Parameters.AddWithValue("@prodName", txtProdName.Text.Trim)
query.Parameters.AddWithValue("@description", txtProdDesc.Text.Trim)
query.Parameters.AddWithValue("@manufacturer", txtProdManuf.Text.Trim)
query.Parameters.AddWithValue("@store", txtStore.Text.Trim)
Execute(query)

The Execute method can take care of wiring up the connection object to the command, and so on. Note that it's no longer an Insert method, it can run any query that doesn't return results.

[ADQUSIT]

@Evil:

simply pass a query object with the table and columns baked into the string, and the values passed as query parameters.

Like how i do this?

[Evil_Giraffe]

Sorry, my edit crossed with your reply!

[ADQUSIT]

1). Hello, there is a code, which i never used and seen, as i am new to parametrized way of coding. I got the idea little bit, but not fully that what is happening with this code actually.

2). Also, give me idea that if i don't use functions ( like i was using in my above-mentioned first post where i was sending values from form to module for all insert, update and delete queries ) and use straight parametrized queries on each form separate separate, so will it be a good idea or not? I mean that i just make my module and will have common objects there with public access specifier and will get them all over my forms and nothing else will be there in module, all queries and things will be working on their related forms. Is it right way?


3). This is my code example which i am trying to use:....

Try

            If dgvProd.Rows.Count <= 0 Then
                MessageBox.Show("At Least One record must be available in Data Viewer", "SellingMS", MessageBoxButtons.OK)
                Exit Sub
                cmbProdName.Focus()

            Else

                Dim insProd As String = "insert into ProdInfo (Prod_Name, Prod_Pri, Prod_Desc, Prod_Made) values (@Prod_Name, @Prod_Pri, @Prod_Desc, @Prod_Made)"
                Dim cmdSql As New SqlCommand(insProd, cnSql)
                cmdSql.CommandType = CommandType.Text
                daSql.InsertCommand = cmdSql

                cnSql.Open()
                Dim i As Integer
                For i = 0 To (dt.Rows.Count - 1)


                    cmdSql.Parameters.Clear()
                    ' ------------ WORKING CODE -------------------
                    With cmdSql

                        .Parameters.Add("@Prod_Name", SqlDbType.VarChar).Value = dt.Rows(i)("Product Name")
                        .Parameters.Add("@Prod_Pri", SqlDbType.Int).Value = dt.Rows(i)("Product Price")
                        .Parameters.Add("@Prod_Desc", SqlDbType.VarChar).Value = dt.Rows(i)("Product Description")
                        .Parameters.Add("@Prod_Made", SqlDbType.VarChar).Value = dt.Rows(i)("Product Made")

                    End With
                    ' ------------ WORKING CODE -------------------

                    cmdSql.ExecuteNonQuery()

                Next
                'MsgBox("hey")
                MessageBox.Show(i & "  " & "Records have been inserted", "SellingMS", MessageBoxButtons.OK)
                Call Clear(Me)
                Call ClearCombo(Me)
                dt.Rows.Clear()

            End If

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            cnSql.Close()
        End Try
    End Sub

[ADQUSIT]

However, you shouldn't need to parameterise those first two, since you hard code them where you call Insert from, presumably?

Evil, I didn't pick what you want me to learn from your words, Please tell me that what is hard code and how i did it??

[Evil_Giraffe]

Evil, I didn't pick what you want me to learn from your words, Please tell me that what is hard code and how i did it??

Okay, this is how you call your database functions:

Insert("ProductBasicInfo", "ProdID, ProdName, Description, Manufacturer, Store", txtProdID.Text.Trim & ", '" & txtProdName.Text.Trim & "','" & txtProdDesc.Text.Trim & "','" & txtProdManuf.Text.Trim & "', " & txtStore.Text.Trim)

This line of code specifies:

• That you are doing an Insert - because you are calling the Insert function.
• That you are inserting into the ProductBasicInfo table - from the first argument.
• That you are supply values to the columns ProdID, ProdName, etc... - from the second argument.
• That the values to put in those columns come from txtProdID, txtProdName, ... - from the third argument.

All those things are specified by code OUTSIDE your database functions. What your database functions deal with is taking those specified things and updating the database with them.

Likewise, the code example I posted specifies all those same things:

    Dim query As New SqlCommand("INSERT INTO ProductBasicInfo (ProdID, ProdName, Description, Manufacturer, Store) VALUES (@prodId, @prodName, @description, @manufacturer, @store)")
    query.Parameters.AddWithValue("@prodId", txtProdID.Text.Trim)
    query.Parameters.AddWithValue("@prodName", txtProdName.Text.Trim)
    query.Parameters.AddWithValue("@description", txtProdDesc.Text.Trim)
    query.Parameters.AddWithValue("@manufacturer", txtProdManuf.Text.Trim)
    query.Parameters.AddWithValue("@store", txtStore.Text.Trim)
    Execute(query)

• That you are doing an Insert - as part of the SQL query.
• That you are inserting into the ProductBasicInfo table - as part of the SQL query.
• That you are supply values to the columns ProdID, ProdName, etc... - as part of the SQL query.
• That the values to put in those columns come from txtProdID, txtProdName, ... - by the parameters attached to the query.

Again, the Execute method deals with the nuts and bolts of updating the database with that information.

My point was that although you cannot parameterise the query with the operation, table name or column names, only values, that doesn't stop you because you specify the query outside the Execute function, at the same place you were previously specifying the operation, table name and column names.

[ADQUSIT]

Hello E_G, what you say about my post# 6. Is it right way to do the parametrized way of coding.?

[Evil_Giraffe]

Looks about right to me. I don't usually need to loop over a set of values to insert, so I don't find myself executing the same query multiple times with different parameters, but I assume that part works? What you may find better is to add the parameters outside the loop without values, and just assign different values to the parameters inside the loop, instead of creating parameters each time around.

[ADQUSIT]

E_G, using the loop in my way was actually needing in my app. I had to submit multiple records with single submit button, so therefore i needed to use the loop.

I made a new practice of your code, here is mine:

Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.Click
        Try

            Dim cmdSql As New SqlCommand("insert into Login (Name, password) values (@name, @password) ", cnSql)
            cmdSql.CommandType = CommandType.Text
            daSql.InsertCommand = cmdSql

            cnSql.Open()

            cmdSql.Parameters.AddWithValue("@name", txtUname.Text.Trim)
            cmdSql.Parameters.AddWithValue("@password", txtUpwd.Text.Trim)
            cmdSql.ExecuteNonQuery()

            MessageBox.Show("Record Inserted")
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error")
        Finally
            cnSql.Close()
        End Try
    End Sub

I don't know how to use the execute function, therefore, i just used the executeNonQuery.

[ADQUSIT]

In my post # 6, i was using:

With cmdSql

.Parameters.Add("@Prod_Name", SqlDbType.VarChar).Value = dt.Rows(i)("Product Name")
.Parameters.Add("@Prod_Pri", SqlDbType.Int).Value = dt.Rows(i)("Product Price")
.Parameters.Add("@Prod_Desc", SqlDbType.VarChar).Value = dt.Rows(i)("Product Description")
.Parameters.Add("@Prod_Made", SqlDbType.VarChar).Value = dt.Rows(i)("Product Made")

End With

Here i was using parameters.add (showing type as well), but in my new code, I'm using parameters.AddWithValue (without datatype mentioning. ) What is the difference between both.

Does AddWithValue add all values in string format?

[si_the_geek]

AddWithValue detects the data type that you pass to it... so if you pass it a String (such as txtUname.Text.Trim), it will set the data type of the parameter to VarChar. If you pass an Integer (such as i ), it will set the data type of the parameter to Int.

[ADQUSIT]

AddWithValue detects the data type that you pass to it... so if you pass it a String (such as txtUname.Text.Trim), it will set the data type of the parameter to VarChar. If you pass an Integer (such as i ), it will set the data type of the parameter to Int.

Meaning thereby that ADdWithValue will automatically detect what it receives form user input. Right?
Well, What way is more better to use? .Add or .AddWithValue, which is safe to use? and does it matter with performance?

[si_the_geek]

Meaning thereby that ADdWithValue will automatically detect what it receives form user input. Right?

Close, but not quite... it detects what your code passes to it.

If you had a textbox for the user to enter a number, and you pass textbox.text, it will be treated as a String. If you pass CInt(textbox.text), it will be treated as an Integer

Well, What way is more better to use? .Add or .AddWithValue, which is safe to use? and does it matter with performance?

Performance doesn't matter unless you are adding thousands/millions of parameters (most likely inside a loop), in which case you have bigger problems to worry about (but .Add would be slightly better).

In terms of being safe, with .AddWithValue you need to make sure you do conversion if needed (using something like CInt etc), otherwise you will get the wrong data type (which may work for a while, and get bugs/errors later). If you use .Add then you can't forget to specify the data type.

[ADQUSIT]

Oh.. Thats very informative. Please guide me that in post # 11 i used the cmdSql.executeNonQuery while i was advised in post # 3 to use Execute. Please guide me that where is to use the Execute function. I found only ExecuteNonQuery or ExecuteScalar etc.

[Evil_Giraffe]

"Execute" was supposed to be a function you wrote yourself to replace the "Insert" function. It's not an existing method on the SqlCommand class. The idea was that since you have a module that is handling all the connection details already for the Insert function, by putting an Execute function there that reused all that infrastructure it would save you having to deal with the connection details every time you ran a query. Then you could simply create the SqlCommand, attach the parameters and pass it to the Execute method. That method would then get a connection and call ExecuteNonQuery on the command.

Sorry for any confusion.

[ADQUSIT]

E_Gir, so do you refer the Execute function to this??:

Public Function Connect()

    Try
            If cnSql.State = ConnectionState.Closed Then
                cnSql.Open()
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

        Return cnSql
    End Function

I'm afraid that I think I'm not getting you..