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

[Helixpoint]

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

 

[Alvin Bruney]

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.

 

[John Saunders]

How much faster would a DataReader be? Doesn't Fill use a DataReader to fill
the DataSet?

 

[Alvin Bruney]

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]

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...