none
select query using which way ? RRS feed

  • Question

  • waht is the best way to return the datareader into gridview ?
    i created the code as below . Please have a look and give advice. thanks 

    i previous use dataTable to pass the value from select function as below:,
     however i found out that it will be faster to use  the sqldatareader
    but how do i put that into dataview ? is it necessary?

    Customer cust = new Customer();
    cust.SelectAll();
    dataview = cust.Table.DefaultView;
    dvCustomer.DataSource = dataview;


    //i changed the above to this 
    Customer cust = new Customer();
    dvCustomer.DataSource = cust.Dr;


    in my customer.cs 

    public void SelectAll()
    {
    base.Query = "SELECT * FROM Customers";
    base.SelectAll();
    this.dr = base.SelectAll();   // return the value to datareader
    }


    dataaccess.cs


     public SqlDataReader SelectAll()  
            {  
                SqlDataReader dr = null;  
                this.table = new DataTable(this.tableName);  
                try  
                {  
                    Connect();  
                   
                    SqlCommand cmd = new SqlCommand(this.query, cn);  
                    dr = cmd.ExecuteReader();  
                    dr.Read();  
                }  
                catch (SqlException ex)  
                {  
                    log.Error(ex.Message);  
                    throw;  
                }  
                finally  
                {  
                    if (dr != null) dr.Close();  
                    Disconnect();  
                }  
                return dr;  
            } 


    alain
    Friday, November 21, 2008 9:10 AM

Answers

  • Hi,

    DataReader is lightweight ,while DataSet has more flexibility for DataSet can be filled from the table you querried ,it need more memory to store these data.

    We do not decide to choose which one only by speed .Because DataReader is forward only , is a stream of data that is returned from a database query , and do not need to store those data. When we want to retrieve a large amount of rows like 100,000, we can use DataReader to retrieve them without consumming so many memory like DataSet does.
    DataSets are ideal if data needs to be edited, sorted, filtered, or searched.

    For more information about contrasting the  DataReader and DataSet,please see:http://msdn.microsoft.com/en-us/magazine/cc188717.aspx

    In your post ,you contrasted the time consumed by the two method , and Datareader takes long time.
    I do not  think dt.Load is a proper method to use, when we retrieve data , we just use:
    while(dr.read())
    {}
    do not load data to table ,it will take more time to load data.
    and if the table contains a huge amount of rows , I think Data reader will take less time to retrieve all the rows,because it 's lightweight , do not need many memory , do not have to do other work.

    Best regards,
    Harry



    • Proposed as answer by Harry Zhu Thursday, November 27, 2008 7:01 AM
    • Marked as answer by kkkJoe Thursday, November 27, 2008 1:06 PM
    Thursday, November 27, 2008 7:01 AM

All replies

  • I wrote following code to test both methods;

     

    1  const string connectionString = "Data Source=TRDSRVR014;Initial Catalog=FBS_2.0_Products_Test;User Id=sa;Password=";  
    2  
    3         DateTime start = DateTime.UtcNow;  
    4  
    5         //using (SqlConnection sqlConnection = new SqlConnection(connectionString))  
    6         //{  
    7         //    using (SqlCommand sqlCommand = new SqlCommand("select * from GTPMSI_AS_RULES", sqlConnection))  
    8         //    {  
    9         //        sqlConnection.Open();  
    10         //        SqlDataReader reader = sqlCommand.ExecuteReader();  
    11         //        GridView1.DataSource = reader;  
    12         //        GridView1.DataBind();  
    13         //    }  
    14         //}  
    15  
    16         using (SqlConnection sqlConnection = new SqlConnection(connectionString))  
    17         {  
    18             using (SqlDataAdapter sqlAdapter = new SqlDataAdapter("select * from GTPMSI_AS_RULES", sqlConnection))  
    19             {  
    20                 DataTable resultTable = new DataTable();  
    21                 sqlAdapter.Fill(resultTable);  
    22                 GridView1.DataSource = resultTable;  
    23                 GridView1.DataBind();  
    24             }  
    25         }  
    26  
    27         DateTime finish = DateTime.UtcNow;  
    28         TimeSpan result = finish - start; 

    The first sample took 2 seconds, 354 milliseconds and the second method took 3 seconds, 421 milliseconds.

    Friday, November 21, 2008 12:08 PM
  • i just do not understand why i cant get the Gridview1.databind()
    Please advice.

    'System.Windows.Forms.DataGridView' does not contain a definition for 'DataBind' and no extension method 'DataBind' accepting a first argument of type 'System.Windows.Forms.DataGridView' could be found (are you missing a using directive or an assembly reference?)       
    alain
    Friday, November 21, 2008 1:13 PM
  • DataBind is only present in the DataGridViews ASP.NET counterpart. In Windows, you don't need to do this after you assign a DataSource.
    Friday, November 21, 2008 8:40 PM
  • HI what if i assign the sqlreader into a datatable?  is this a bad practise?

     SqlCommand cmd = new SqlCommand(this.query, cn);
    dr = cmd.ExecuteReader();
    DataTable dt = new DataTable();
    dt.Load(dr);

    Also, if sqldatareader is fast enough.. why are they introducing out the sqldataadapter + dataset..
    i used to retrieve my rows of records and bind into the dataset..
    Please advice me the comparison between using the way of sqldatareader is better or bind to the dataset ?
    Thanks

    alain
    Sunday, November 23, 2008 9:36 AM
  • i compared these 2 below;
    but surprisingly the sqldatareader take longer time compare to the normal select query.

     public DataTable SelectAll()  
            {  
                DateTime start = DateTime.UtcNow;  
     
                SqlDataReader dr = null;  
                DataTable dt = new DataTable();  
                this.table = new DataTable(this.tableName);  
                try 
                {  
                    Connect();  
                    SqlCommand cmd = new SqlCommand(this.query, cn);  
                    dr = cmd.ExecuteReader();  
                   
                    dt.Load(dr);  
                }  
                catch (SqlException ex)  
                {  
                    log.Error(ex.Message);  
                    throw;  
                }  
                finally 
                {  
                    if (dr != null) dr.Close();  
                    Disconnect();  
                }  
     
                DateTime end = DateTime.UtcNow;  
                TimeSpan result = end - start;  
                System.Diagnostics.Debug.Write(result);  
     
                return dt;  
            } 


    normal  select  -  this is faster ..  why?

     public void SelectAll()  
            {  
                DateTime start = DateTime.UtcNow;  
                this.table = new DataTable(this.tableName);  
                try 
                {  
                    Connect();  
                    SqlDataAdapter ad = new SqlDataAdapter(this.query, cn);  
                    ad.Fill(this.table);  
                }  
                catch (SqlException ex)  
                {  
                    log.Error(ex.Message);  
                    throw;  
                }  
                finally { Disconnect(); }  
                DateTime end = DateTime.UtcNow;  
                TimeSpan result = end- start;  
                System.Diagnostics.Debug.Write("time taken: " + result);  
            } 

    alain
    Sunday, November 23, 2008 10:30 AM
  • anyone can advice here? thanks
    alain
    Monday, November 24, 2008 12:01 PM
  • Hi,

    DataReader is lightweight ,while DataSet has more flexibility for DataSet can be filled from the table you querried ,it need more memory to store these data.

    We do not decide to choose which one only by speed .Because DataReader is forward only , is a stream of data that is returned from a database query , and do not need to store those data. When we want to retrieve a large amount of rows like 100,000, we can use DataReader to retrieve them without consumming so many memory like DataSet does.
    DataSets are ideal if data needs to be edited, sorted, filtered, or searched.

    For more information about contrasting the  DataReader and DataSet,please see:http://msdn.microsoft.com/en-us/magazine/cc188717.aspx

    In your post ,you contrasted the time consumed by the two method , and Datareader takes long time.
    I do not  think dt.Load is a proper method to use, when we retrieve data , we just use:
    while(dr.read())
    {}
    do not load data to table ,it will take more time to load data.
    and if the table contains a huge amount of rows , I think Data reader will take less time to retrieve all the rows,because it 's lightweight , do not need many memory , do not have to do other work.

    Best regards,
    Harry



    • Proposed as answer by Harry Zhu Thursday, November 27, 2008 7:01 AM
    • Marked as answer by kkkJoe Thursday, November 27, 2008 1:06 PM
    Thursday, November 27, 2008 7:01 AM
  • THanks for the reply..  
    Also, i love that link very much.. it helps me understand more ..
    thanks Harry.. 
    alain
    Thursday, November 27, 2008 1:09 PM