3 Replies Latest reply: Jan 17, 2013 12:57 PM by 636190 RSS

    Fetch size / typed dataset

    985307
      Hi,

      I am working with .net 4.0 and odp.net. Using typed data sets ( binding source, table adapter etc.. ) I need a mechanism to pull some number of rows instead of the whole set. And when user "runs out of data" ( moves to some position near the end of the current data set ), fetch another piece of the data set. It is a bit of a problem when 1000000 records is loaded into the grid. I know that oracle maintains an open cursor, and uses session's fetch size setting to calculate the amount of rows it will send per trip. Is there any auto mechanism for it, or do i need to code it by hand. if I must code it, what would be the best approach?

      Thank you!
        • 1. Re: Fetch size / typed dataset
          636190
          Would you please add some detail here..." pull some number of rows instead of the whole set"... to do what with? Is the dataset already populated or are you talking about adding rows to it? The same confusion with this ... "fetch another piece of the data set."....

          Code it up yourself(somebody has to iterate those rows... why not you)
          I know of no "auto" mechanism.

          Also, I have found tableadapters very, very slow with a large number of records.
          Why not use multiple datasets and/or datatables? If I understand the issue correctly, you could have a thread populating the "next" datatable while the user was mucking around with the first. Just be sure to swap out your binding source values in the GUI thread. I have had issues if I did not.

          r,
          dennis
          • 2. Re: Fetch size / typed dataset
            985307
            Well, lets that there is a table containing 3 million records. It's really not a smart idea to populate those records into the data grid control at once. Instead, it should be populated let's say 500 by 500 records, as the user moves down the grid. Since, .NET typed data sets provide full auto mechanism to build buisness apps, i was hoping not to do much tech stuff, and focus on my buisness rules.

            "Code it up yourself(somebody has to iterate those rows... why not you)
            I know of no "auto" mechanism. "

            How would I approach this problem??
            • 3. Re: Fetch size / typed dataset
              636190
              Yes, I have used typed datasets for a long time they can be good things.
              Build multiple datasets, two maybe three. Just change your select statement for each one as needed
              Again you could have a thread that just focused on building them.

              I do not think it is to much tech stuff to change the DataSource and DataMember of a datagridview.
              datagridview1.DataSource = dataSet1;
              datagridview1.DataMember = dataSet1.Tables[0].TableName;

              You do need some way(trigger) to tell the app when the users needs a "new" set of records. The UI may "jump" a little during the swap of datasets.... but maybe not since the table structure will not be changing.

              The new .NET threading Task api's make threading straight forward.
              DataSet X1 = null;
              var context = TaskScheduler.FromCurrentSynchronizationContext();                 
              Task buildDS = Task.Factory.StartNew(() =>
              {
                 X1 = MaterializeDataSet(_connectionString, _sqlSelect, false, false, true);
              }
              ).ContinueWith(_ => Console.WriteLine("Success in building dataset..."), context);
              r,
              dennis
                    public static DataSet MaterializeDataSet(string _connectionString, string _sqlSelect, bool _returnProviderSpecificTypes, bool _includeSchema, bool _fillTable)
                      {
                          DataSet ds = null;
                          using (OracleConnection _oraconn = new OracleConnection(_connectionString))
                          {
                              try
                              {
                                  _oraconn.Open();
                                   using (OracleCommand cmd = new OracleCommand(_sqlSelect, _oraconn))
                                  {
                                      cmd.CommandType = CommandType.Text;
                                      using (OracleDataAdapter da = new OracleDataAdapter(cmd))
                                      {
                                          da.ReturnProviderSpecificTypes = _returnProviderSpecificTypes;
                                          if (_includeSchema == true)
                                          {
                                              ds = new DataSet("SCHEMASUPPLIED");
                                              da.FillSchema(ds, SchemaType.Source);
                                              if (_fillTable == true)
                                                  da.Fill(ds.Tables[0]);
                                          }
                                          else
                                          {
                                              ds = new DataSet("SCHEMANOTSUPPLIED");
                                              if (_fillTable == true)
                                                  da.Fill(ds);
                                          }
                                          ds.Tables[0].TableName = "Table";
                                      }//using da
                                  } //using cmd
                              }
                              catch (OracleException _oraEx)
                              {
                                  throw (_oraEx); // Actually rethrow
                              }
                              catch (System.Exception _sysEx)
                              {
                                  throw (_sysEx); // Actually rethrow
                              }
                              finally
                              {
                                  if (_oraconn.State == ConnectionState.Broken || _oraconn.State == ConnectionState.Open)
                                      _oraconn.Close();
                              }
                          }//using oraconn
                          if (ds != null)
                          {
                              if (ds.Tables != null && ds.Tables[0] != null)
                                  return ds;
                              else
                                  return null;
                          }
                          else
                              return null;
                      }