Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 494
  • Last Modified:

Open DataTable vb.net

I've got this function that returns a DataTable for using as the datasource of my DataGridview or comboboxes.
The strange thing is, it seems like my function request the data twice from the database resulting in waiting 2 times longer.
I time it and it took eg 10 seconds on the line with comment '111111 and again 10 seconds on the line with comment '222222

Any idea how I can prevent this?

Imports  System.Data.SqlClient

Public Class Form1

  Friend m_db As SqlConnection

  Friend Function OpenDataTable(ByVal sQry As String) As DataTable
    Dim commandMain As SqlCommand
    Dim readerMain As SqlDataReader
    Dim dataTable As New DataTable
    commandMain = New SqlCommand(sQry, m_db)
    commandMain.CommandTimeout = 0
    readerMain = commandMain.ExecuteReader '1111111
    dataTable.Load(readerMain) '222222
    readerMain.Close()
    OpenDataTable = dataTable
    readerMain = Nothing
    commandMain = Nothing
  End Function

  Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    DataGridView1.DataSource = OpenDataTable("Select * from Assets;")
  End Sub
End Class


[code]
Imports System.Data.SqlClient

Public Class Form1

  Friend m_db As SqlConnection

  Friend Function OpenDataTable(ByVal sQry As String) As DataTable
    Dim commandMain As SqlCommand
    Dim readerMain As SqlDataReader
    Dim dataTable As New DataTable
    commandMain = New SqlCommand(sQry, m_db)
    commandMain.CommandTimeout = 0
    readerMain = commandMain.ExecuteReader '1111111
    dataTable.Load(readerMain) '222222
    readerMain.Close()
    OpenDataTable = dataTable
    readerMain = Nothing
    commandMain = Nothing
  End Function

  Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    DataGridView1.DataSource = OpenDataTable("Select * from Assets;")
  End Sub
End Class

Open in new window

0
koossa
Asked:
koossa
  • 5
  • 4
3 Solutions
 
jpaulinoCommented:
Try this way, where connString is your connection string and query your query:

            Try

                Using da As New SqlDataAdapter(query, connString)
                    Using ds As New DataSet
                        da.Fill(ds)
                        Me.DataGridView.DataSource = ds.Tables(0).DefaultView

                        End With

                    End Using
                End Using

            Catch ex As Exception
                Throw New ArgumentException(ex.Message)
            End Try

Open in new window

0
 
YZlatCommented:
use the following functions:

 Function GetConnection(ByVal strConn As String) As SqlConnection
        Dim conn As SqlConnection
        Try
            conn = New SqlConnection(strConn)
            conn.Open()
        Catch ex As SqlException
            Console.Write("SQL ERROR: " & ex.Message)
        Catch ex As Exception
            Console.Write("ERROR: " & ex.Message)
        End Try
        GetConnection = conn
    End Function
    Function GetDataTable(ByVal strConn As String, ByVal query As String) As DataTable
        Dim conn As SqlConnection
        Dim dset As New DataSet
        Try
            conn = GetConnection(strConn)
            Dim da As SqlDataAdapter = New SqlDataAdapter(query, conn)
            ''make sure command does not timeout
            da.SelectCommand.CommandTimeout = 0
            ''fill dataset
            da.Fill(dset)
        Catch ex As SqlException
            Console.Write("SQL ERROR: " & ex.Message)
        Catch ex As Exception
            Console.Write("ERROR: " & ex.Message)
        Finally
            If conn.State = ConnectionState.Open Then
                conn.Close()
            End If
        End Try
        GetDataTable = dset.Tables(0)
    End Function

Open in new window


call it so:

 Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    DataGridView1.DataSource = GetDataTable("your connection string here","Select * from Assets;")
  End Sub

Open in new window

0
 
koossaAuthor Commented:
The   readerMain = commandMain.ExecuteReader took 4.52 seconds and the dataTable.Load(readerMain) took 18.84 seconds to execute
Total of 23.36 seconds to execute

The DataAdapter.Fill(DataSet) took on the same query 25.61 seconds
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
jpaulinoCommented:
Maybe you can improve your query ... you should never use "Select * " because you cannot benefit from indexing.

Specify all fields you need (only the ones you need) and check if you have indexes for that table.
0
 
koossaAuthor Commented:
Yes, I know, I don't actually use "select *", I just used it for this example.
0
 
jpaulinoCommented:
Can you show your query? Do you use Stored Procedures?
0
 
koossaAuthor Commented:
No, I don't use Stored Procedures.

Here is one of my queries, the query is generated depending on what the user select from filters and enter to search.
SELECT top 100 Division.Name as DivName, Locations.Name AS LocName, Case When [Assets].[Select]=0 or [Assets].[Select] is null then 0 Else 1 End as [Select], [Asset Barcode] AS [Asset Barcode], Assets.Name, Assets.Description, Assets.Category, Assets.[Division ID], [Status History].Status, [Status History].[Status Date], [Status History].[Location Barcode], Assets.[Purchase Date], Assets.[Purchase Price], Assets.[Expiry Date], Assets.[Expiry value], [Status History].[History ID], Assets.[Notes],[Custom1],[Custom2],[Custom3],[Custom4],[Custom5],Case When Photo_Thumb Is Null Then 0 Else 1 End as PhotoExists FROM Assets LEFT OUTER JOIN Division ON Assets.[Division ID] = Division.ID LEFT JOIN (Locations RIGHT JOIN [Status History] ON Locations.[Location Barcode] = [Status History].[Location Barcode]) ON Assets.[Asset Barcode] = [Status History].[Asset ID] Where ( (Division.[Name] like '%test%' and  ISNULL([Assets].[Select], 0)>=0 and ISNULL([Locations].[Select], 0)>=0 And  (([Status History].[Status Date]
)=(Select Max([Status Date]) from [Status History] Where [Asset ID] = [Asset Barcode]))) or ([Asset Barcode] like '%test%' and  ISNULL([Assets].[Select], 0)>=0 and ISNULL([Locations].[Select], 0)>=0 And  (([Status History].[Status Date])=(Select Max([Status Date]) from [Status History] Where [Asset ID] = [Asset Barcode]))) or (Assets.[Name] like '%test%' and  ISNULL([Assets].[Select], 0)>=0 and ISNULL([Locations].[Select], 0)>=0 And  (([Status History].[Status Date])=(Select Max([Status Date]) from [Status History] Where [Asset ID] = [Asset Barcode]))) or (Assets.[Description] like '%test%' and  ISNULL([Assets].[Select], 0)>=0 and ISNULL([Locations].[Select], 0)>=0 And  (([Status History].[Status Date])=(Select Max([Status Date]) from [Status History] Where [Asset ID] = [Asset Barcode]))) or (Assets.[Notes] like '%test%' and  ISNULL([Assets].[Select], 0)>=0 and ISNULL([Locations].[Select], 0)>=0 And  (([Status History].[Status Date])=(Select Max([Status Date]) from [Status History] Where [Asset ID] = [Asset Ba
rcode]))) or (Assets.[Category] like '%test%' and  ISNULL([Assets].[Select], 0)>=0 and ISNULL([Locations].[Select], 0)>=0 And  (([Status History].[Status Date])=(Select Max([Status Date]) from [Status History] Where [Asset ID] = [Asset Barcode]))) or ([Status] like '%test%' and  ISNULL([Assets].[Select], 0)>=0 and ISNULL([Locations].[Select], 0)>=0 And  (([Status History].[Status Date])=(Select Max([Status Date]) from [Status History] Where [Asset ID] = [Asset Barcode]))) or ([Locations].[Name] like '%test%' and  ISNULL([Assets].[Select], 0)>=0 and ISNULL([Locations].[Select], 0)>=0 And  (([Status History].[Status Date])=(Select Max([Status Date]) from [Status History] Where [Asset ID] = [Asset Barcode])))) or ( (Division.[Name] like '%test%' and  ISNULL([Assets].[Select], 0)>=0 and ISNULL([Locations].[Select], 0)>=0 And  (([Status History].[History ID]) Is Null)) or ([Asset Barcode] like '%test%' and  ISNULL([Assets].[Select], 0)>=0 and ISNULL([Locations].[Select], 0)>=0 And  (([Status History].[History ID]) 
Is Null)) or (Assets.[Name] like '%test%' and  ISNULL([Assets].[Select], 0)>=0 and ISNULL([Locations].[Select], 0)>=0 And  (([Status History].[History ID]) Is Null)) or (Assets.[Description] like '%test%' and  ISNULL([Assets].[Select], 0)>=0 and ISNULL([Locations].[Select], 0)>=0 And  (([Status History].[History ID]) Is Null)) or (Assets.[Notes] like '%test%' and  ISNULL([Assets].[Select], 0)>=0 and ISNULL([Locations].[Select], 0)>=0 And  (([Status History].[History ID]) Is Null)) or (Assets.[Category] like '%test%' and  ISNULL([Assets].[Select], 0)>=0 and ISNULL([Locations].[Select], 0)>=0 And  (([Status History].[History ID]) Is Null)) or ([Status] like '%test%' and  ISNULL([Assets].[Select], 0)>=0 and ISNULL([Locations].[Select], 0)>=0 And  (([Status History].[History ID]) Is Null)) or ([Locations].[Name] like '%test%' and  ISNULL([Assets].[Select], 0)>=0 and ISNULL([Locations].[Select], 0)>=0 And  (([Status History].[History ID]) Is Null)));

Open in new window

0
 
jpaulinoCommented:
You should use SP and indexes to speed up the queries ... you could have nice improvements.

You also should use parameters instead of string concatenation (if you use it).
0
 
koossaAuthor Commented:
Thank you!
My tables are thoroughly indexed.
Will Stored procedures be worth the effort on speed improvement, I don't want to work a day on it and only win a couple of milliseconds?
0
 
jpaulinoCommented:
Normally it's quicker with SP, but you can try to do in one or two and check the results.

Have a read on this: http://stackoverflow.com/questions/59880/are-stored-procedures-more-efficient-in-general-than-inline-statements-on-mode

It also very easy to do SP and you will not lost a work day for that :)
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now