Load data to dvg using DataAdapter with parameter is slow

  • Thread starter Thread starter Skypea
  • Start date Start date
S

Skypea

Guest
Hi! Im trying to load data from sql server 2005 to dgv. When I display top 100 data it show quickly, but when I add where in sql by 2 date, and use parameters.addwithvalue it loaded slowly. How to improve the performace?

this is my code:

Sub LoadToDgv()
Call openConn()

Dim LimitData As Integer
Dim sb As StringBuilder
sb = New StringBuilder
sb = getSql()

Dim sqlstring As String
sqlstring = sb.ToString

CMD = New SqlCommand(sqlstring, CONN)

If Limit.Text <> "" Then
LimitData = Val(Limit.Text)
CMD.Parameters.AddWithValue("@LimitData", LimitData)
End If


CMD.Parameters.AddWithValue("@dtfrom", DTFrom.Value)
CMD.Parameters.AddWithValue("@dtto", DTTo.Value)

DA = New SqlDataAdapter(CMD)
Dim dt As New DataTable
DA.Fill(dt)
DGVTransaksi.DataSource = dt
CONN.Close()
End Sub

Function getSql() As StringBuilder
Dim sb As StringBuilder
sb = New StringBuilder

sb.Append("select ")
If Limit.Text <> "" Then
sb.Append("top (@LimitData) ")
End If
sb.Append("trans.id, trans.entry_date, trans.destination, ")
sb.Append("trans.price, trans.module_id, trans.status, trans.status_date, ")
sb.Append("inbox.message from inbox inner join trans on ")
sb.Append("trans.id=inbox.trans_id ")
sb.Append("where (trans.status_date >= @dtfrom and trans.status_date <= @dtto) ")
sb.Append("order by trans.status_date desc ")

Return sb
End Function

Continue reading...
 
Back
Top