Parameratized database example?

[Gopher2011]

Hi,

I am trying to create a Parameratized database that

1) lets me put a sql string together.
2) lets me add parameters
3) lets me call the db part multiple times with different queries.
4) put the data in a datagridview table.

I have found numerous examples but they keep breaking, and are not straight forward.
Do you guys know of any links to any simple working examples?

 

[jmcilhinney]

There's no such thing as a "parameterized database". I don't know whether you just mean executing queries with parameters or creating an actual data access layer. Either way, you should follow the Blog link in my signature. There's a post on each.

 

[Gopher2011]

Thanks - I will go and look! Appreciated.

 

[Gopher2011]

I will post my solution to help others.

This is the database part:-

Imports System.Data
Imports System.Data.SqlClient

Public Class Database
    Public sP_2 As String = "SELECT * FROM CustTest WHERE CustName LIKE @pFr AND Status = @pTo"

    Public DC As SqlConnection = New SqlConnection("Password=aa;Persist Security Info=True;User ID=aa;Initial Catalog= FinalSQL;Data Source=GFI")
    Public DS As New DataSet
    Public SC As New SqlCommand


    Public Sub PopulateTable_FrTo()
        DC.Open()
        SC = New SqlCommand(sP_2)
        SC.Parameters.Add(New SqlParameter("@pFr", SqlDbType.VarChar, 10)).Value = frm_Main.s_Fr
        SC.Parameters.Add(New SqlParameter("@pTo", SqlDbType.VarChar, 10)).Value = frm_Main.s_To

        Using SR As SqlDataReader = SC.ExecuteReader()
            Application.DoEvents()
            DS.Tables.Clear()
            DS.Tables.Add("Main")
            DS.Tables("Main").Load(SR)
            'Show on datagridview named 'dgv_Table'
            frm_Main.dgv_Table.DataSource = DS.Tables("Main")
        End Using
        DC.Close()
    End Sub
End Class

And the calling code in Main is

'Class declaration part
    Public s_Fr As String
    Public s_To As String
    Private GFI As Database

'In a sub load main etc
     GFI = New Database

     s_Fr = "John"
     s_To = "Pass"
 
     'Get data and show it on the the table
     Call GFI.PopulateTable_FrTo()

 

[jmcilhinney]

This:

SC.Parameters.Add(New SqlParameter("@pFr", SqlDbType.VarChar, 10)).Value = frm_Main.s_Fr

can be simplified to this:

SC.Parameters.AddWithValue("@pFr", frm_Main.s_Fr)

 

[Gopher2011]

Hey thanks again mate.

 

[Gopher2011]

Hi,

Interestingly this change you tell me of slows down my code. not sure if its relevant or not:-

It goes from (seconds)
0.890597
1.093715
0.999968
1.046842
0.828099

for each execution to:
1.156213
1.078091
1.124964
1.031217
1.609324

So its fractionally slower. Ok I only tested it 5 times but is it because of the code change or the butterfly in Beijing?

(I keep timers in my SQL code calls to help speed it up - im trying to get a 45 minute sql call down to maybe a few mnutes, hence my converting the old programmers stuff to paramerterized queries and ultimately your very much appreciated help)

 

[MattP]

(I keep timers in my SQL code calls to help speed it up - im trying to get a 45 minute sql call down to maybe a few mnutes, hence my converting the old programmers stuff to paramerterized queries and ultimately your very much appreciated help)

While parameterizing your query is definitely a good practice (always to this) if you've got a 45 minute call you're trying to get down to a couple of minutes you're looking in the wrong place.

The first thing I would personally look for is 'IN' clauses in the query and replace those with JOINs.

I've had several instances where I've been asked to look into queries that are performing badly (yay vendor code!) and seen the dreaded IN statement. Rewriting the statement to use JOINs rather than IN has taken the execution from ~10 minutes to completing before SSMS could tick 1 second.

 

[Gopher2011]

Hi Thanks for the reply - and sorry it took me time to reply, I have been out of the office.

This guy has no IN SQL but does have several left joins added together.
Is this likely to slow it down?