locked
Params RRS feed

  • Question

  • I have recently been looking at the use of params for moving data in and out of my tables.  It is new to me and tricky as hell, but I am told it is a much more secure, and better way of working with a database.

    I would like to know what the consensus is about using this process, since I can tell that params are not a recent innovation.



    gwboolean

    Monday, November 9, 2020 6:17 PM

All replies

  • Hello,

    Can you tell us what you mean by params and the definition of tables otherwise we must guess at what you mean e.g. a table could be a DataTable or a table in a database and params might be a parameter array.

    And if a parameter array there are other ways to accomplish what a parameter array does e.g. pass an array of one type, a list of one type where type might be a string array, list of string or a concrete class instance. 


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    My GitHub code samples
    GitHub page

    Monday, November 9, 2020 6:28 PM
  • Sure.  Here is one of the sets of routines I am using.

        Private Sub AddParams()
            'Add Parameters
            FileMaster.AddParam("@fileid", lblFileID.Text)
            FileMaster.AddParam("@filename", txtName.Text)
            FileMaster.AddParam("@description", txtDescription.Text)
            FileMaster.AddParam("@directory", lblDirectory.Text)
            FileMaster.AddParam("@user1", chkUser1.Checked)
            FileMaster.AddParam("@user2", chkUser2.Checked)
            FileMaster.AddParam("@user3", chkUser3.Checked)
            FileMaster.AddParam("@user4", chkUser4.Checked)
            FileMaster.AddParam("@user5", chkUser5.Checked)
            FileMaster.AddParam("@user6", chkUser6.Checked)
            FileMaster.AddParam("@user7", chkUser7.Checked)
            FileMaster.AddParam("@user8", chkUser8.Checked)
            FileMaster.AddParam("@user9", chkUser9.Checked)
            FileMaster.AddParam("@user0", chkUser0.Checked)
            FileMaster.AddParam("@active", rdoActive.Checked)
            FileMaster.AddParam("@inactive", rdoInactive.Checked)
            FileMaster.AddParam("@obsolete", rdoObsolete.Checked)
            FileMaster.AddParam("@select1", rdoUser1.Checked)
            FileMaster.AddParam("@select2", rdoUser2.Checked)
            'Report & Abort on errors
            If NoErrors(True) = False Then Exit Sub
        End Sub

    This routine creates all of the params I am using for the table FileMaster.

    This is the routine that sets up the params.

            Public Sub AddParam(Name As String, Value As Object)
                Dim NewParam As New OleDbParameter(Name, Value)
                Params.Add(NewParam)
            End Sub

    So, in this case I might use these params int the process of selecting a record and displaying it and this is the process I have followed and works quite well.

            If Not String.IsNullOrEmpty(RecordID) Then
                lblFileID.Text = RecordID
                AddParams()
                GetRecord()
                RefreshForm()
                SetState("View")
            Else
                AddControls()
                SetState("Edit")
            End If

        Public Sub GetRecord()
            'Command
            FileMaster.FileMasterQuery("SELECT FileID, FileName, FileDesc, FileDir, " &
                                       "FileUser1, FileUser2, FileUser3, FileUser4, FileUser5, " &
                                       "FileUser6, FileUser7, FileUser8, FileUser9, FileUser0, " &
                                       "FileActive, FileInactive, FileObsolete, FileSelect1, FileSelect2 " &
                                       "FROM FileMaster " &
                                       "WHERE FileID=@fileid")
    
            'Report & Abort on errors or no recourds found
            If NoErrors(True) = False OrElse FileMaster.RecordCount < 1 Then Exit Sub
        End Sub
            Public Sub FileMasterQuery(myQuery As String)
                RecordCount = 0
                Exception = ""
                Try
    #Region "Open Connection/Load Table"
                    MasterBaseConnection.Open() 'Open connection
                    ListCommand = New OleDbCommand(myQuery, MasterBaseConnection) 'Database Command
                    Params.ForEach(Sub(p) ListCommand.Parameters.Add(p)) 'Load params into command using one line lamda.
                    Params.Clear() 'Clear params list
                    ListTable = New DataTable
                    ListAdapter = New OleDbDataAdapter(ListCommand)
                    RecordCount = ListAdapter.Fill(ListTable)
    #End Region
    
                Catch ex As Exception
                    Exception = ex.Message
                End Try
                If MasterBaseConnection.State = ConnectionState.Open Then MasterBaseConnection.Close()
            End Sub

    This process works as I want it to and not where I am running into a problem.  But I wanted you to see how I am attempting to approach this for my usage.




    gwboolean

    Monday, November 9, 2020 7:18 PM
  • I forgot the refreshform()

        Public Sub RefreshForm()
            'Return record
            Dim r As DataRow = FileMaster.ListTable.Rows(CurrentRecord)
            'Populate controls
            lblFileID.Text = r("fileid").ToString
            txtName.Text = r("filename").ToString
            txtDescription.Text = r("description").ToString
            lblDirectory.Text = r("directory").ToString
            If r("user1") IsNot Nothing Then chkUser1.Checked = CBool(r("user1"))
            If r("user2") IsNot Nothing Then chkUser2.Checked = CBool(r("user2"))
            If r("user3") IsNot Nothing Then chkUser3.Checked = CBool(r("user3"))
            If r("user4") IsNot Nothing Then chkUser4.Checked = CBool(r("user4"))
            If r("user5") IsNot Nothing Then chkUser5.Checked = CBool(r("user5"))
            If r("user6") IsNot Nothing Then chkUser6.Checked = CBool(r("user6"))
            If r("user7") IsNot Nothing Then chkUser7.Checked = CBool(r("user7"))
            If r("user8") IsNot Nothing Then chkUser8.Checked = CBool(r("user8"))
            If r("user9") IsNot Nothing Then chkUser9.Checked = CBool(r("user9"))
            If r("user0") IsNot Nothing Then chkUser0.Checked = CBool(r("user0"))
            If r("active") IsNot Nothing Then rdoActive.Checked = CBool(r("active"))
            If r("inactive") IsNot Nothing Then rdoInactive.Checked = CBool(r("inactive"))
            If r("obsolete") IsNot Nothing Then rdoObsolete.Checked = CBool(r("obsolete"))
            If r("select1") IsNot Nothing Then rdoUser1.Checked = CBool(r("select1"))
            If r("select2") IsNot Nothing Then rdoUser2.Checked = CBool(r("select2"))
        End Sub



    gwboolean

    Monday, November 9, 2020 7:20 PM
  • If you are talking about the add parameters, generally speaking developer tend to use this pattern. This code was taken from a Microsoft certified contractor (originally in C#) 

    Public Shared Function PrepareInsertEmployeesIntoW2SqlCommand(ByVal conn As SqlConnection) As SqlCommand
    	Dim oCmd As New SqlCommand()
    	oCmd.Connection = conn
    	oCmd.CommandType = CommandType.Text
    	oCmd.CommandText = String.Format("INSERT INTO tblW2 (AuditNumber, [Year], SSN, LastName, FirstName, MiddleInitial, CreatedBy) VALUES (@AuditNumber, @Year, @SSN, @LastName, @FirstName, @MiddleInitial, @CreatedBy)")
    	oCmd.Parameters.Add("@AuditNumber", SqlDbType.Int)
    	oCmd.Parameters.Add("@Year", SqlDbType.Int)
    	oCmd.Parameters.Add("@SSN", SqlDbType.VarChar, 9)
    	oCmd.Parameters.Add("@LastName", SqlDbType.VarChar, 50)
    	oCmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 50)
    	oCmd.Parameters.Add("@MiddleInitial", SqlDbType.VarChar, 1)
    	oCmd.Parameters.Add("@CreatedBy", SqlDbType.VarChar, 20)
    	oCmd.Prepare()
    
    	Return oCmd
    End Function
    

    Then set the values below.

    Public Function LoadEmployeesIntoW2(ByVal dt As DataTable, ByVal pAuditId As Integer, ByVal pYear As Integer) As Integer()
    	Dim importedRows(1) As Integer
    	importedRows(0) = 0 'for new rows count
    	importedRows(1) = 0 'for updated rows count
    	Dim dtupdate As New DataTable()
    	dtupdate = dt.Clone()
    
    	Try
    		Using cn = New SqlConnection() With {.ConnectionString = Me.ConnectionString}
    
    			cn.Open()
    
    			Using cmd As SqlCommand = SqlCommandHelper.PrepareInsertEmployeesIntoW2SqlCommand(cn)
    				For Each dr As DataRow In dt.Rows
    					cmd.Parameters("@AuditNumber").Value = pAuditId
    					cmd.Parameters("@Year").Value = pYear
    					cmd.Parameters("@SSN").Value = TAPSNG_DAL.TableHelpers.ColumnFetchHelpers.GetColumnValueAsString(dr, "SSN")
    					cmd.Parameters("@LastName").Value = TAPSNG_DAL.TableHelpers.ColumnFetchHelpers.GetColumnValueAsString(dr, "LastName").Trim()
    					cmd.Parameters("@FirstName").Value = TAPSNG_DAL.TableHelpers.ColumnFetchHelpers.GetColumnValueAsString(dr, "FirstName").Trim()
    					cmd.Parameters("@MiddleInitial").Value = TAPSNG_DAL.TableHelpers.ColumnFetchHelpers.GetColumnValueAsString(dr, "MiddleInitial").ToUpper().Trim()
    					cmd.Parameters("@CreatedBy").Value = _userName
    
    					Try
    						Dim rowsAffected As Integer = cmd.ExecuteNonQuery()
    						importedRows(0) += rowsAffected
    					Catch sqlex As SqlException
    						If sqlex.Message.IndexOf("duplicate") > 0 Then
    							dtupdate.ImportRow(dr)
    						End If
    					End Try
    				Next dr
    			End Using
    		End Using
    	Catch dbex As Exception
    		Trace.TraceError(dbex.ToString())
    		Throw
    	End Try
    
    	Return importedRows
    End Function
    

    There is only one read benefit (and drawback), the benefit is the parameters are done in another class cleaning up the code using the parameters while the drawback is all code is not together.

    This is one of the many reasons I recommend looking at Entity Framework Core as EF Core creates parameters for you along with opening a connection (you don't create the connection) and handling the operation.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    My GitHub code samples
    GitHub page

    Monday, November 9, 2020 8:19 PM
  • Entity framework core.  Is this something that will cost me?  Keep in mind that I am just an old man with a hobby to keep the mind from turning to jello.

    gwboolean


    • Edited by gwboolean Monday, November 9, 2020 11:09 PM
    Monday, November 9, 2020 11:08 PM
  • Entity framework core.  Is this something that will cost me?  Keep in mind that I am just an old man with a hobby to keep the mind from turning to jello.

    gwboolean


    Entity Framework 6 (I just remembered EF Core is more work than you want but not EF 6)is free. 

    Take a look at some code samples I've done, see if you would be interested or not.

    https://github.com/karenpayneoregon/ef-track-added-modified-vb

    If you like then it will surely never let you brain turn to jello :-)


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    My GitHub code samples
    GitHub page

    Tuesday, November 10, 2020 12:45 AM
  • I will do that.  I am finding these params very interesting and have been playing with them a lot lately.  However, the interest is dwindling as I keep running into little ticky problems every time I tweak this stuff.

    Now, as far as the params that I currently am using, which is about all I can comprehend at the moment.  I have been running into a problem that I just cannot get past.  

    I am currently running into an error in the the RefreshForm() routine at the line

    txtDescription.Text = r("description").ToString

    The error is a System.ArgumentException stating that the Column description does not belong to table.

    Since I am not using all of the columns of the table I know that one must be very careful about lining up every param with every column, in terms of order and number params/columns  

    So what this error is telling me is that I have lost/misspelled a param or one of my table columns.  I hope my understanding is correct, because I have gone through every param and column in every query and routine numerous times and have been able to uncover where I made that mistake.


    gwboolean

    Tuesday, November 10, 2020 2:05 AM
  • Hi gwboolean,

    Thanks for your feedback.

    >>So what this error is telling me is that I have lost/misspelled a param or one of my table columns.  I hope my understanding is correct

    Yes, you can check the 'FileMaster.ListTable' table and see if the table contains 'description' column.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 11, 2020 5:36 AM
  • What the table has is a column that is called FileDir and the param is @dir.

    Assuming that my interpretation of the problem is correct, then I am unable to find where I went wrong at all with either the param or the column name.

    I know that I am not even a very good proof reader, but I have been through the code for the Query, Params() GetRecord() and RefreshForm() over and over and am unable to see where the mistake is.

    Which leaves me believing that my issue might be something else, but I am at a loss, at this point.


    gwboolean

    Wednesday, November 11, 2020 10:02 PM
  • Hi gwboolean,

    >>What the table has is a column that is called FileDir and the param is @dir.

    It seems that the table doesn't contain 'description' column, so you get that exception, 

    Could you provide more information about your 'FileMaster.ListTable'? 

    Besides, you can also provide some related code about 'FileMaster', and it will help us make a test.

    We are waiting for your update.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, November 12, 2020 2:21 AM
  • Karen,

    I was just looking through the code used in your InsertEmployee() Function.

    The params part I understand, but your Function blew my mind.

    Could you explain to me how that function works?  I can almost understand it, but I have never even thought that way.

    This is how I read that.  Your input value is the connection, and the return is the opened connection, command, and Inserting the control values into the param/column?

    Public Shared Function PrepareInsertEmployeesIntoW2SqlCommand(ByVal conn As SqlConnection) As SqlCommand
    	Dim oCmd As New SqlCommand()
    	oCmd.Connection = conn
    	oCmd.CommandType = CommandType.Text
    	oCmd.CommandText = String.Format("INSERT INTO tblW2 (AuditNumber, [Year], SSN, LastName, FirstName, MiddleInitial, CreatedBy) VALUES (@AuditNumber, @Year, @SSN, @LastName, @FirstName, @MiddleInitial, @CreatedBy)")
    	oCmd.Parameters.Add("@AuditNumber", SqlDbType.Int)
    	oCmd.Parameters.Add("@Year", SqlDbType.Int)
    	oCmd.Parameters.Add("@SSN", SqlDbType.VarChar, 9)
    	oCmd.Parameters.Add("@LastName", SqlDbType.VarChar, 50)
    	oCmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 50)
    	oCmd.Parameters.Add("@MiddleInitial", SqlDbType.VarChar, 1)
    	oCmd.Parameters.Add("@CreatedBy", SqlDbType.VarChar, 20)
    	oCmd.Prepare()
    
    	Return oCmd
    End Function


    gwboolean

    Friday, November 13, 2020 1:08 AM
  • Hi gwboolean,

    I have merged two pieces of Karen's code, and hope it can help you better understand the code.

        Public Function LoadEmployeesIntoW2(ByVal dt As DataTable, ByVal pAuditId As Integer, ByVal pYear As Integer) As Integer()
            Dim importedRows(1) As Integer
            importedRows(0) = 0 'for new rows count
            importedRows(1) = 0 'for updated rows count
            Dim dtupdate As New DataTable()
            dtupdate = dt.Clone()
    
            Try
                Using cn = New SqlConnection() With {.ConnectionString = "your connection string"}
                    cn.Open()
                    Using cmd As SqlCommand = New SqlCommand()
                        cmd.Connection = cn
                        cmd.CommandType = CommandType.Text
                        cmd.CommandText = String.Format("INSERT INTO tblW2 (AuditNumber, [Year], SSN, LastName, FirstName, MiddleInitial, CreatedBy) VALUES (@AuditNumber, @Year, @SSN, @LastName, @FirstName, @MiddleInitial, @CreatedBy)")
    
                        cmd.Parameters.Add("@AuditNumber", SqlDbType.Int)
                        cmd.Parameters.Add("@Year", SqlDbType.Int)
                        cmd.Parameters.Add("@SSN", SqlDbType.VarChar, 9)
                        cmd.Parameters.Add("@LastName", SqlDbType.VarChar, 50)
                        cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 50)
                        cmd.Parameters.Add("@MiddleInitial", SqlDbType.VarChar, 1)
                        cmd.Parameters.Add("@CreatedBy", SqlDbType.VarChar, 20)
                        cmd.Prepare()
    
                        For Each dr As DataRow In dt.Rows
                            cmd.Parameters("@AuditNumber").Value = pAuditId
                            cmd.Parameters("@Year").Value = pYear
                            cmd.Parameters("@SSN").Value = TAPSNG_DAL.TableHelpers.ColumnFetchHelpers.GetColumnValueAsString(dr, "SSN")
                            cmd.Parameters("@LastName").Value = TAPSNG_DAL.TableHelpers.ColumnFetchHelpers.GetColumnValueAsString(dr, "LastName").Trim()
                            cmd.Parameters("@FirstName").Value = TAPSNG_DAL.TableHelpers.ColumnFetchHelpers.GetColumnValueAsString(dr, "FirstName").Trim()
                            cmd.Parameters("@MiddleInitial").Value = TAPSNG_DAL.TableHelpers.ColumnFetchHelpers.GetColumnValueAsString(dr, "MiddleInitial").ToUpper().Trim()
                            cmd.Parameters("@CreatedBy").Value = _userName
    
                            Try
                                Dim rowsAffected As Integer = cmd.ExecuteNonQuery()
                                importedRows(0) += rowsAffected
                            Catch sqlex As SqlException
                                If sqlex.Message.IndexOf("duplicate") > 0 Then
                                    dtupdate.ImportRow(dr)
                                End If
                            End Try
                        Next dr
                    End Using
                End Using
            Catch dbex As Exception
                Trace.TraceError(dbex.ToString())
                Throw
            End Try
    
            Return importedRows
        End Function

    If you need further assistance, please let me know.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, November 13, 2020 7:50 AM