How to shift from straight query to parametrized one at module level

[ADQUSIT]

Hi. I've been using straight query, which had sql injection problem, so i shift over to parametrized one, which i learn in the same forum here.

Now i want to use the queries at module level, but i'm totally unfamiliar for this that how to use Parametrized queries at module level. I'm using the following code.

This is my module code, for insert query:

#Region "Insertion"

    Public Sub Insertion(ByVal tblName As String, ByVal columns As String, ByVal Parameters As String)
        Try
            cmdsql1.CommandText = "insert into " & tblName & " ( " & columns & ")  values  ( " & Parameters & ")"
            cmdsql1.Connection = Conn()
            cmdsql1.ExecuteNonQuery()

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub
#End Region

and this is my form button code:

Try
            Insertion("ProductBasicInfo", "ProdId, ProdName, Description, Manufacturer", " " & txtProdID.Text.Trim & ", '" & txtProdName.Text.Trim & "', '" & txtProdDesc.Text.Trim & "', '" & txtProdManuf.Text.Trim & "'")
            MessageBox.Show("Record Inserted Successfully")
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

My question is that how do i shift again from this query to parametrized once. Normally I'm doing all my work so far with parametrized queries. But this is my first attempt to working at module level, so i don't know that how to apply parametrized at module level. Please guide me.

[jmcilhinney]

I think that that's a rather dodgy way to implement a DAL but, if that's the way you want to go, I'd do it something like this:

Public Sub Insert(tableName As String, valuesByColumn As IDictionary(Of String, Object))
    Dim escapeChars = {"[", "]"}
 
    'Ensure that the table name contains no escape characters to guard against SQL injection.
    If escapeChars.Any(Function(escapeChar) tableName.Contains(escapeChar)) Then
        Throw New InvalidOperationException("Table name contains invalid characters that suggest possible SQL injection attack.")
    End If
 
    'Ensure that the column names contain no escape characters to guard against SQL injection.
    If valuesByColumn.Keys.Any(Function(columnName) escapeChars.Any(Function(s) columnName.Contains(s))) Then
        Throw New InvalidOperationException("Column name contains invalid characters that suggest possible SQL injection attack.")
    End If
 
    Dim sqlTemplate = "INSERT INTO [{0}] ([{1}]) VALUES ({2})"
    Dim columnList = String.Join("], [", valuesByColumn.Keys)
    Dim parameterList = String.Join(", ", valuesByColumn.Keys.Select(Function(columnName) "@" & columnName))
    Dim command = New SqlCommand(String.Format(sqlTemplate, tableName, columnList, parameterList))
 
    For Each columnName In valuesByColumn.Keys
        'Use DBNull.Value if the parameter value is Nothing.
        command.Parameters.AddWithValue("@" & columnName,
                                        If(valuesByColumn(columnName),
                                           DBNull.Value))
    Next
 
    '...
End Sub

[ADQUSIT]

Hooo. This is far complex. Its all gone over my head.

that's a rather dodgy way to implement a DAL

Would you please guide me that what is better alternative way, and what is DAL?

[kebo]

jm's code is a bit complicated especially for those trying to learn...
db command parameters work something like this...

cmd.CommandText = "SELECT * FROM <tableName> WHERE Name=@name and City=@city"
md.Parameters.Add("@name", OleDbType.VarChar).value = myCity
cmd.Parameters.Add("@city", OleDbType.VarChar).value =  myState

you will of course have to adapt it to your code.
hope this helps. Database programming is not my strong suit.

[ADQUSIT]

Hi Kebo.

cmd.CommandText = "SELECT * FROM <tableName> WHERE Name=@name and City=@city"

In this line of code, you have particularized 2 fields, received to the function. But in my case, everytime the function receives random number of fields, so how to cope with this?

[.paul.]

Hi Kebo.

cmd.CommandText = "SELECT * FROM <tableName> WHERE Name=@name and City=@city"

In this line of code, you have particularized 2 fields, received to the function. But in my case, everytime the function receives random number of fields, so how to cope with this?

the database has a fixed number of fields. you could try something like this:

Imports System.Data.OleDb

Public Class Form1

    Dim conn As OleDbConnection

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        conn = New OleDbConnection("connection string")
    End Sub

    Private Function getRecords(ByVal tableName As String, ByVal field1 As String, ByVal field2 As String, ByVal field3 As String) As DataTable
        Dim cmd As New OleDbCommand(String.Format("SELECT * FROM {0} WHERE Field1 LIKE @field1 AND Field2  LIKE @field2 AND Field3  LIKE @field3", tableName), conn)
        cmd.Parameters.Add("@field1", OleDbType.VarChar).Value = If(field1 <> "", field1, "*")
        cmd.Parameters.Add("@field2", OleDbType.VarChar).Value = If(field2 <> "", field2, "*")
        cmd.Parameters.Add("@field3", OleDbType.VarChar).Value = If(field3 <> "", field3, "*")

        Dim da As New OleDbDataAdapter(cmd)
        Dim dt As New DataTable
        da.Fill(dt)

        Return dt
    End Function

End Class

[ADQUSIT]

@.PAUL

I"m little confuse here:

Private Function getRecords(ByVal tableName As String, ByVal field1 As String, ByVal field2 As String, ByVal field3 As String) As DataTable
        Dim cmd As New OleDbCommand(String.Format("SELECT * FROM {0} WHERE Field1 LIKE @field1 AND Field2  LIKE @field2 AND Field3  LIKE @field3", tableName), conn)

Please guide me for this.

Secondly, how many field parameters i can pass to this function?

[.paul.]

what is your tablename + all of your field names + datatypes in that table?
i'll show you how you can query that table by any combination of fields...

[ADQUSIT]

Table name = ProductBasicInfo

ProdId = int, ProdName = varchar, Description = varchar, Manufacturer = varchar

[.paul.]

Imports System.Data.OleDb

Public Class Form1

    Dim conn As OleDbConnection

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        conn = New OleDbConnection("connection string")
    End Sub

    Private Function getRecords(ByVal ProdId As Integer, ByVal ProdName As String, ByVal Description As String, ByVal Manufacturer As String) As DataTable
        Dim cmd As New OleDbCommand(String.Format("SELECT * FROM ProductBasicInfo WHERE {0} ProdName LIKE @ProdName AND Description  LIKE @Description AND Manufacturer  LIKE @Manufacturer", If(ProdId <> -1, "ProdId = @ProdId AND ", "")), conn)
        If ProdId <> -1 Then
            cmd.Parameters.Add("@ProdId", OleDbType.Integer).Value = ProdId
        End If
        cmd.Parameters.Add("@ProdName", OleDbType.VarChar).Value = If(ProdName <> "", ProdName, "*")
        cmd.Parameters.Add("@Description", OleDbType.VarChar).Value = If(Description <> "", Description, "*")
        cmd.Parameters.Add("@Manufacturer", OleDbType.VarChar).Value = If(Manufacturer <> "", Manufacturer, "*")

        Dim da As New OleDbDataAdapter(cmd)
        Dim dt As New DataTable
        da.Fill(dt)

        Return dt
    End Function

    'you can use any combination of these (-1 specifies no ProdId, "" specifies ANY in the other fields):
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        'get records by ProdId
        Dim dt As DataTable = getRecords(1, "", "", "")
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        'get records by ProdName
        Dim dt As DataTable = getRecords(-1, "a name", "", "")
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        'get records by Description
        Dim dt As DataTable = getRecords(-1, "", "a description", "")
    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        'get records by Manufacturer
        Dim dt As DataTable = getRecords(-1, "", "", "a manufacturer")
    End Sub

End Class

[ADQUSIT]

I will try it and will feed back. Thank you sir.

[ADQUSIT]

Hello Paul.

You have define the fields of single table in this particular function. But lets say if i have several tables, so how this single function will fulfill my need. Even if i make the same function with different set of parameters (function overloading), how i will separately define the fields in function for n number of tables? Won't this be a cumbersome task to do?

Isn't there any way, by which I don't need to define the fields for each table (like I'm doing in this way), like i do in straight query method?

Please guide me if I'm wrong?