Link to home
Create AccountLog in
Avatar of Rias
RiasFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Replace dataadapter by sql datareader

Hello,
Is there any way I can replace the data adapter with sql reader to fill datatable.

 Dim SQLobj As New SqlCommand("L_Sy")
  SQLobj.Parameters.AddWithValue("@Title", Dy_Title)
            SQLobj.Parameters.AddWithValue("@Year_Ref1", Dy_Year_Ref1)
            SQLobj.Parameters.AddWithValue("@Year_Ref2", Dy_Year_Ref2)
            SQLobj.Parameters.AddWithValue("@FaxNumber", Dy_FaxNumber)
            SQLobj.Parameters.AddWithValue("@Attn", Dy_Attn)

    SQLobj.CommandType = CommandType.StoredProcedure
            SQLobj.Connection = sql.GetConnection
            clsFrmmain.Populate(SQLobj)



  Public Sub Populate(ByVal Command As SqlCommand)
        'Accept command as string and fill the datatable with the result of sql query
        ' Test_Str = "Test"
        Dim dt = New DataTable
        Try
            adapter = New SqlDataAdapter()
            adapter.SelectCommand = Command
            dt.BeginLoadData()
            adapter.Fill(dt)
            dt.EndLoadData()
            Command.Connection.Close()
            Command.Dispose()
            Data_Table = dt


        Catch MyException As SqlException
            MessageBox.Show("Stored procedure Error: MySQL code: " &
            MyException.Number & "  " &
            MyException.Message)
        End Try
    End Sub
Avatar of AndyAinscow
AndyAinscow
Flag of Switzerland image

Yes.  For each row in the reader add a new row to the data table and copy the contents of each field from the reader into the equivalent field in the new row in the table.

ps.  This is rather more code for you to write an could possibly run more slowly as well.
Avatar of Rias

ASKER

I read an article which prompts it will be faster:

var dataReader = cmd.ExecuteReader();
var dataTable = new DataTable();
dataTable.Load(dataReader);
Avatar of Rias

ASKER

Need to know how I can implement here.
You're right.  The Load method can use a datareader as the source (guess who hasn't ever done that).
In that case it is probably faster than using an adapter (a datareader is a lightweight class supporting limited functionality) but make certain the table and reader do match one another in terms of the columns of data.
ASKER CERTIFIED SOLUTION
Avatar of AndyAinscow
AndyAinscow
Flag of Switzerland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Rias

ASKER

Andy,
Any suggestion in my code