Looking for a quicker way to fill a datagrid???

Discussion in 'ASP .Net' started by Helixpoint, Jul 30, 2003.

  1. Helixpoint

    Helixpoint Guest

    Here is my code:

    Dim availMach As New ASPNETProduct.machinedb()

    Dim dstMachList As DataSet

    dstMachList = availMach.GetAvailMach(theSearchVal, theSearchBy,
    theMachCode1, theSearchView, SelSort, SelSortDir)

    availMachList.DataSource = dstMachList





    Public Function GetAvailMach(ByVal theSearchVal As String, ByVal theSearchBy
    As String, ByVal MACH_CODE1 As String, ByVal theSearchView As String, ByVal
    SelSort As String, ByVal SelSortDir As String) As DataSet

    ' Create Instance of Connection and Command Object

    Dim myConnection As New
    SqlConnection(ConfigurationSettings.AppSettings("connectionString"))

    Dim myCommand As New SqlDataAdapter("SelectOpenInventory", myConnection)

    ' Mark the Command as a SPROC

    myCommand.SelectCommand.CommandType = CommandType.StoredProcedure

    ' Add Parameters to SPROC

    Dim parametertheSearchVal As New SqlParameter("@theSearchVal",
    SqlDbType.NVarChar, 100)

    parametertheSearchVal.Value = theSearchVal

    myCommand.SelectCommand.Parameters.Add(parametertheSearchVal)

    Dim parametertheSearchBy As New SqlParameter("@theSearchBy",
    SqlDbType.NVarChar, 100)

    parametertheSearchBy.Value = theSearchBy

    myCommand.SelectCommand.Parameters.Add(parametertheSearchBy)



    Dim parameterMACH_CODE1 As New SqlParameter("@MACH_CODE",
    SqlDbType.NVarChar, 50)

    parameterMACH_CODE1.Value = MACH_CODE1

    myCommand.SelectCommand.Parameters.Add(parameterMACH_CODE1)



    Dim parametertheSearchView As New SqlParameter("@theSearchView",
    SqlDbType.NVarChar, 50)

    parametertheSearchView.Value = theSearchView

    myCommand.SelectCommand.Parameters.Add(parametertheSearchView)

    Dim parameterSelSort As New SqlParameter("@SelSort", SqlDbType.NVarChar, 50)

    parameterSelSort.Value = SelSort

    myCommand.SelectCommand.Parameters.Add(parameterSelSort)

    Dim parameterSelSortDir As New SqlParameter("@SelSortDir",
    SqlDbType.NVarChar, 10)

    parameterSelSortDir.Value = SelSortDir

    myCommand.SelectCommand.Parameters.Add(parameterSelSortDir)





    ' Create and Fill the DataSet

    Dim myDataSet As New DataSet

    myCommand.Fill(myDataSet)

    ' Return the datareader

    Return myDataSet

    End Function


    --

    dave
    Helixpoint, Jul 30, 2003
    #1
    1. Advertising

  2. Helixpoint

    Alvin Bruney Guest

    if fill is not fast enough for you, use a datareader to loop thru. remember
    the reader is a fire hose cursor and you have to explicitly close it when
    done or else it is considered blocked.

    "Helixpoint" <> wrote in message
    news:...
    > Here is my code:
    >
    > Dim availMach As New ASPNETProduct.machinedb()
    >
    > Dim dstMachList As DataSet
    >
    > dstMachList = availMach.GetAvailMach(theSearchVal, theSearchBy,
    > theMachCode1, theSearchView, SelSort, SelSortDir)
    >
    > availMachList.DataSource = dstMachList
    >
    >
    >
    >
    >
    > Public Function GetAvailMach(ByVal theSearchVal As String, ByVal

    theSearchBy
    > As String, ByVal MACH_CODE1 As String, ByVal theSearchView As String,

    ByVal
    > SelSort As String, ByVal SelSortDir As String) As DataSet
    >
    > ' Create Instance of Connection and Command Object
    >
    > Dim myConnection As New
    > SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
    >
    > Dim myCommand As New SqlDataAdapter("SelectOpenInventory", myConnection)
    >
    > ' Mark the Command as a SPROC
    >
    > myCommand.SelectCommand.CommandType = CommandType.StoredProcedure
    >
    > ' Add Parameters to SPROC
    >
    > Dim parametertheSearchVal As New SqlParameter("@theSearchVal",
    > SqlDbType.NVarChar, 100)
    >
    > parametertheSearchVal.Value = theSearchVal
    >
    > myCommand.SelectCommand.Parameters.Add(parametertheSearchVal)
    >
    > Dim parametertheSearchBy As New SqlParameter("@theSearchBy",
    > SqlDbType.NVarChar, 100)
    >
    > parametertheSearchBy.Value = theSearchBy
    >
    > myCommand.SelectCommand.Parameters.Add(parametertheSearchBy)
    >
    >
    >
    > Dim parameterMACH_CODE1 As New SqlParameter("@MACH_CODE",
    > SqlDbType.NVarChar, 50)
    >
    > parameterMACH_CODE1.Value = MACH_CODE1
    >
    > myCommand.SelectCommand.Parameters.Add(parameterMACH_CODE1)
    >
    >
    >
    > Dim parametertheSearchView As New SqlParameter("@theSearchView",
    > SqlDbType.NVarChar, 50)
    >
    > parametertheSearchView.Value = theSearchView
    >
    > myCommand.SelectCommand.Parameters.Add(parametertheSearchView)
    >
    > Dim parameterSelSort As New SqlParameter("@SelSort", SqlDbType.NVarChar,

    50)
    >
    > parameterSelSort.Value = SelSort
    >
    > myCommand.SelectCommand.Parameters.Add(parameterSelSort)
    >
    > Dim parameterSelSortDir As New SqlParameter("@SelSortDir",
    > SqlDbType.NVarChar, 10)
    >
    > parameterSelSortDir.Value = SelSortDir
    >
    > myCommand.SelectCommand.Parameters.Add(parameterSelSortDir)
    >
    >
    >
    >
    >
    > ' Create and Fill the DataSet
    >
    > Dim myDataSet As New DataSet
    >
    > myCommand.Fill(myDataSet)
    >
    > ' Return the datareader
    >
    > Return myDataSet
    >
    > End Function
    >
    >
    > --
    >
    > dave
    >
    >
    Alvin Bruney, Jul 31, 2003
    #2
    1. Advertising

  3. How much faster would a DataReader be? Doesn't Fill use a DataReader to fill
    the DataSet?
    --
    John Saunders
    Internet Engineer


    "Alvin Bruney" <vapordan_spam_me_not@hotmail_no_spamhotmail.com> wrote in
    message news:%...
    > if fill is not fast enough for you, use a datareader to loop thru.

    remember
    > the reader is a fire hose cursor and you have to explicitly close it when
    > done or else it is considered blocked.
    >
    > "Helixpoint" <> wrote in message
    > news:...
    > > Here is my code:
    > >
    > > Dim availMach As New ASPNETProduct.machinedb()
    > >
    > > Dim dstMachList As DataSet
    > >
    > > dstMachList = availMach.GetAvailMach(theSearchVal, theSearchBy,
    > > theMachCode1, theSearchView, SelSort, SelSortDir)
    > >
    > > availMachList.DataSource = dstMachList
    > >
    > >
    > >
    > >
    > >
    > > Public Function GetAvailMach(ByVal theSearchVal As String, ByVal

    > theSearchBy
    > > As String, ByVal MACH_CODE1 As String, ByVal theSearchView As String,

    > ByVal
    > > SelSort As String, ByVal SelSortDir As String) As DataSet
    > >
    > > ' Create Instance of Connection and Command Object
    > >
    > > Dim myConnection As New
    > > SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
    > >
    > > Dim myCommand As New SqlDataAdapter("SelectOpenInventory", myConnection)
    > >
    > > ' Mark the Command as a SPROC
    > >
    > > myCommand.SelectCommand.CommandType = CommandType.StoredProcedure
    > >
    > > ' Add Parameters to SPROC
    > >
    > > Dim parametertheSearchVal As New SqlParameter("@theSearchVal",
    > > SqlDbType.NVarChar, 100)
    > >
    > > parametertheSearchVal.Value = theSearchVal
    > >
    > > myCommand.SelectCommand.Parameters.Add(parametertheSearchVal)
    > >
    > > Dim parametertheSearchBy As New SqlParameter("@theSearchBy",
    > > SqlDbType.NVarChar, 100)
    > >
    > > parametertheSearchBy.Value = theSearchBy
    > >
    > > myCommand.SelectCommand.Parameters.Add(parametertheSearchBy)
    > >
    > >
    > >
    > > Dim parameterMACH_CODE1 As New SqlParameter("@MACH_CODE",
    > > SqlDbType.NVarChar, 50)
    > >
    > > parameterMACH_CODE1.Value = MACH_CODE1
    > >
    > > myCommand.SelectCommand.Parameters.Add(parameterMACH_CODE1)
    > >
    > >
    > >
    > > Dim parametertheSearchView As New SqlParameter("@theSearchView",
    > > SqlDbType.NVarChar, 50)
    > >
    > > parametertheSearchView.Value = theSearchView
    > >
    > > myCommand.SelectCommand.Parameters.Add(parametertheSearchView)
    > >
    > > Dim parameterSelSort As New SqlParameter("@SelSort", SqlDbType.NVarChar,

    > 50)
    > >
    > > parameterSelSort.Value = SelSort
    > >
    > > myCommand.SelectCommand.Parameters.Add(parameterSelSort)
    > >
    > > Dim parameterSelSortDir As New SqlParameter("@SelSortDir",
    > > SqlDbType.NVarChar, 10)
    > >
    > > parameterSelSortDir.Value = SelSortDir
    > >
    > > myCommand.SelectCommand.Parameters.Add(parameterSelSortDir)
    > >
    > >
    > >
    > >
    > >
    > > ' Create and Fill the DataSet
    > >
    > > Dim myDataSet As New DataSet
    > >
    > > myCommand.Fill(myDataSet)
    > >
    > > ' Return the datareader
    > >
    > > Return myDataSet
    > >
    > > End Function
    > >
    > >
    > > --
    > >
    > > dave
    > >
    > >

    >
    >
    John Saunders, Jul 31, 2003
    #3
  4. Helixpoint

    Alvin Bruney Guest

    underneath it does but there is a lot of extra overhead because the results
    have to be packaged and stuffed into a dataset. the performance difference
    is night and day.

    "John Saunders" <> wrote in message
    news:...
    > How much faster would a DataReader be? Doesn't Fill use a DataReader to

    fill
    > the DataSet?
    > --
    > John Saunders
    > Internet Engineer
    >
    >
    > "Alvin Bruney" <vapordan_spam_me_not@hotmail_no_spamhotmail.com> wrote in
    > message news:%...
    > > if fill is not fast enough for you, use a datareader to loop thru.

    > remember
    > > the reader is a fire hose cursor and you have to explicitly close it

    when
    > > done or else it is considered blocked.
    > >
    > > "Helixpoint" <> wrote in message
    > > news:...
    > > > Here is my code:
    > > >
    > > > Dim availMach As New ASPNETProduct.machinedb()
    > > >
    > > > Dim dstMachList As DataSet
    > > >
    > > > dstMachList = availMach.GetAvailMach(theSearchVal, theSearchBy,
    > > > theMachCode1, theSearchView, SelSort, SelSortDir)
    > > >
    > > > availMachList.DataSource = dstMachList
    > > >
    > > >
    > > >
    > > >
    > > >
    > > > Public Function GetAvailMach(ByVal theSearchVal As String, ByVal

    > > theSearchBy
    > > > As String, ByVal MACH_CODE1 As String, ByVal theSearchView As String,

    > > ByVal
    > > > SelSort As String, ByVal SelSortDir As String) As DataSet
    > > >
    > > > ' Create Instance of Connection and Command Object
    > > >
    > > > Dim myConnection As New
    > > > SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
    > > >
    > > > Dim myCommand As New SqlDataAdapter("SelectOpenInventory",

    myConnection)
    > > >
    > > > ' Mark the Command as a SPROC
    > > >
    > > > myCommand.SelectCommand.CommandType = CommandType.StoredProcedure
    > > >
    > > > ' Add Parameters to SPROC
    > > >
    > > > Dim parametertheSearchVal As New SqlParameter("@theSearchVal",
    > > > SqlDbType.NVarChar, 100)
    > > >
    > > > parametertheSearchVal.Value = theSearchVal
    > > >
    > > > myCommand.SelectCommand.Parameters.Add(parametertheSearchVal)
    > > >
    > > > Dim parametertheSearchBy As New SqlParameter("@theSearchBy",
    > > > SqlDbType.NVarChar, 100)
    > > >
    > > > parametertheSearchBy.Value = theSearchBy
    > > >
    > > > myCommand.SelectCommand.Parameters.Add(parametertheSearchBy)
    > > >
    > > >
    > > >
    > > > Dim parameterMACH_CODE1 As New SqlParameter("@MACH_CODE",
    > > > SqlDbType.NVarChar, 50)
    > > >
    > > > parameterMACH_CODE1.Value = MACH_CODE1
    > > >
    > > > myCommand.SelectCommand.Parameters.Add(parameterMACH_CODE1)
    > > >
    > > >
    > > >
    > > > Dim parametertheSearchView As New SqlParameter("@theSearchView",
    > > > SqlDbType.NVarChar, 50)
    > > >
    > > > parametertheSearchView.Value = theSearchView
    > > >
    > > > myCommand.SelectCommand.Parameters.Add(parametertheSearchView)
    > > >
    > > > Dim parameterSelSort As New SqlParameter("@SelSort",

    SqlDbType.NVarChar,
    > > 50)
    > > >
    > > > parameterSelSort.Value = SelSort
    > > >
    > > > myCommand.SelectCommand.Parameters.Add(parameterSelSort)
    > > >
    > > > Dim parameterSelSortDir As New SqlParameter("@SelSortDir",
    > > > SqlDbType.NVarChar, 10)
    > > >
    > > > parameterSelSortDir.Value = SelSortDir
    > > >
    > > > myCommand.SelectCommand.Parameters.Add(parameterSelSortDir)
    > > >
    > > >
    > > >
    > > >
    > > >
    > > > ' Create and Fill the DataSet
    > > >
    > > > Dim myDataSet As New DataSet
    > > >
    > > > myCommand.Fill(myDataSet)
    > > >
    > > > ' Return the datareader
    > > >
    > > > Return myDataSet
    > > >
    > > > End Function
    > > >
    > > >
    > > > --
    > > >
    > > > dave
    > > >
    > > >

    > >
    > >

    >
    >
    Alvin Bruney, Aug 1, 2003
    #4
  5. Interesting. I'll have to do some performance tests on that some day. I had
    thought that, absent DataRelations, they'd just loop through the DataReader,
    calling:

    DataSet ds = something();
    DataTable dt = ds.Tables[0];
    DataReader reader = somethingElse();

    while (reader.Read())
    {
    DataRow dr = dt.NewRow();
    dr.ItemArray = reader.GetValues();
    dt.Rows.Add(dr);
    }

    Oh, well, one more mystery...
    --
    John Saunders
    Internet Engineer


    "Alvin Bruney" <vapordan_spam_me_not@hotmail_no_spamhotmail.com> wrote in
    message news:ueZbU5%...
    > underneath it does but there is a lot of extra overhead because the

    results
    > have to be packaged and stuffed into a dataset. the performance difference
    > is night and day.
    >
    > "John Saunders" <> wrote in message
    > news:...
    > > How much faster would a DataReader be? Doesn't Fill use a DataReader to

    > fill
    > > the DataSet?
    > > --
    > > John Saunders
    > > Internet Engineer
    > >
    > >
    > > "Alvin Bruney" <vapordan_spam_me_not@hotmail_no_spamhotmail.com> wrote

    in
    > > message news:%...
    > > > if fill is not fast enough for you, use a datareader to loop thru.

    > > remember
    > > > the reader is a fire hose cursor and you have to explicitly close it

    > when
    > > > done or else it is considered blocked.
    > > >
    > > > "Helixpoint" <> wrote in message
    > > > news:...
    > > > > Here is my code:
    > > > >
    > > > > Dim availMach As New ASPNETProduct.machinedb()
    > > > >
    > > > > Dim dstMachList As DataSet
    > > > >
    > > > > dstMachList = availMach.GetAvailMach(theSearchVal, theSearchBy,
    > > > > theMachCode1, theSearchView, SelSort, SelSortDir)
    > > > >
    > > > > availMachList.DataSource = dstMachList
    > > > >
    > > > >
    > > > >
    > > > >
    > > > >
    > > > > Public Function GetAvailMach(ByVal theSearchVal As String, ByVal
    > > > theSearchBy
    > > > > As String, ByVal MACH_CODE1 As String, ByVal theSearchView As

    String,
    > > > ByVal
    > > > > SelSort As String, ByVal SelSortDir As String) As DataSet
    > > > >
    > > > > ' Create Instance of Connection and Command Object
    > > > >
    > > > > Dim myConnection As New
    > > > > SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
    > > > >
    > > > > Dim myCommand As New SqlDataAdapter("SelectOpenInventory",

    > myConnection)
    > > > >
    > > > > ' Mark the Command as a SPROC
    > > > >
    > > > > myCommand.SelectCommand.CommandType = CommandType.StoredProcedure
    > > > >
    > > > > ' Add Parameters to SPROC
    > > > >
    > > > > Dim parametertheSearchVal As New SqlParameter("@theSearchVal",
    > > > > SqlDbType.NVarChar, 100)
    > > > >
    > > > > parametertheSearchVal.Value = theSearchVal
    > > > >
    > > > > myCommand.SelectCommand.Parameters.Add(parametertheSearchVal)
    > > > >
    > > > > Dim parametertheSearchBy As New SqlParameter("@theSearchBy",
    > > > > SqlDbType.NVarChar, 100)
    > > > >
    > > > > parametertheSearchBy.Value = theSearchBy
    > > > >
    > > > > myCommand.SelectCommand.Parameters.Add(parametertheSearchBy)
    > > > >
    > > > >
    > > > >
    > > > > Dim parameterMACH_CODE1 As New SqlParameter("@MACH_CODE",
    > > > > SqlDbType.NVarChar, 50)
    > > > >
    > > > > parameterMACH_CODE1.Value = MACH_CODE1
    > > > >
    > > > > myCommand.SelectCommand.Parameters.Add(parameterMACH_CODE1)
    > > > >
    > > > >
    > > > >
    > > > > Dim parametertheSearchView As New SqlParameter("@theSearchView",
    > > > > SqlDbType.NVarChar, 50)
    > > > >
    > > > > parametertheSearchView.Value = theSearchView
    > > > >
    > > > > myCommand.SelectCommand.Parameters.Add(parametertheSearchView)
    > > > >
    > > > > Dim parameterSelSort As New SqlParameter("@SelSort",

    > SqlDbType.NVarChar,
    > > > 50)
    > > > >
    > > > > parameterSelSort.Value = SelSort
    > > > >
    > > > > myCommand.SelectCommand.Parameters.Add(parameterSelSort)
    > > > >
    > > > > Dim parameterSelSortDir As New SqlParameter("@SelSortDir",
    > > > > SqlDbType.NVarChar, 10)
    > > > >
    > > > > parameterSelSortDir.Value = SelSortDir
    > > > >
    > > > > myCommand.SelectCommand.Parameters.Add(parameterSelSortDir)
    > > > >
    > > > >
    > > > >
    > > > >
    > > > >
    > > > > ' Create and Fill the DataSet
    > > > >
    > > > > Dim myDataSet As New DataSet
    > > > >
    > > > > myCommand.Fill(myDataSet)
    > > > >
    > > > > ' Return the datareader
    > > > >
    > > > > Return myDataSet
    > > > >
    > > > > End Function
    > > > >
    > > > >
    > > > > --
    > > > >
    > > > > dave
    > > > >
    > > > >
    > > >
    > > >

    > >
    > >

    >
    >
    John Saunders, Aug 1, 2003
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Carlo Filippini
    Replies:
    3
    Views:
    397
  2. Mark
    Replies:
    3
    Views:
    2,039
    John Saunders
    Nov 12, 2004
  3. Tarren
    Replies:
    2
    Views:
    10,258
    Brock Allen
    Sep 29, 2005
  4. has

    Quicker way to copy()?

    has, Jun 9, 2004, in forum: Python
    Replies:
    0
    Views:
    275
  5. Quicker way for DOM?

    , May 31, 2007, in forum: Javascript
    Replies:
    7
    Views:
    106
Loading...

Share This Page