none
Superior Performance using Oledb datareader RRS feed

  • Question

  • Hello all.

    I have been querying data on an access database back end, using  the sql statements as the command text for the command object. I am using a windows forms application as the interface for the project. I am only reading the data into Data grid Views  for my application. When I execute the data reader I extract the column count, names and data types in a loop and create new columns in  a new data table. I then populate the table  with the load command, Loading the data reader.  Next I query the data table to  get a list for Controls or use the  data table as is as  a control source for the data grid views.

      I am finding the response to these queries is far faster than standard Microsoft Access forms applications using the visual query builder. The down side is that you need to keep the connection open until the queried data is consumed.  Is there a detrimental effect in keeping this data connection object open  while I populate Grid views or perform additional queries on the tables using LINQ.   Will it prevent access from some other user who may be updating or editing the back-end database.  In other words. The back=end databases are updated by an automated process. While this is in progress can I still read from the database with a data reader?  Any Information on this subject will be appreciated.

    Thank You


    John C

    Sunday, November 23, 2014 7:14 PM

Answers

  • Hello John,

    >> While this is in progress can I still read from the database with a data reader?

    If you are just to read data, I think you can, however, according to the isolation you used, there are some scenario you should pay attention to them:

    Dirty Read: One transaction reads changed data of anohter tranaction but that data is still not committed. You may take decission/action based on that data. A problem will arise when data is rolled-back later. If rollback happens then your decision/action will be wrong and it produces a bug in your application.

    Non Repeatable Read: A transaction reads the same data from same table multiple times. A problem will arise when for each read, data is different.

    Phantom Read: Suppose a transaction will read a table first and it finds 100 rows. A problem will arise when the same tranaction goes for another read and it finds 101 rows. The extra row is called a phantom row.

    For avoid any of them, using the Serializable isolated in code as:

    var option = new TransactionOptions();
    
                option.IsolationLevel = IsolationLevel.Serializable;
    
    
                using (var scope = new TransactionScope(TransactionScopeOption.Required, option))
    
                {
    
                    SqlConnection conn = new SqlConnection(@"…");
    
                    try
    
                    {
    
                        conn.Open();
    
                        SqlCommand cmd = new SqlCommand("…", conn);
    
                        SqlDataReader reader = cmd.ExecuteReader();
    
                        scope.Complete();
    
                    }
    
                    catch (Exception)
    
                    {
    
    
                        throw;
    
                    }
    
                    finally
    
                    {
    
                        conn.Close();
    
                        scope.Dispose();
    
                    }
    
                }
    

    If you want to know about isolated in .NET, you could check this link:

    http://msdn.microsoft.com/en-us/library/system.data.isolationlevel(v=vs.110).aspx

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by John C in Fla Monday, November 24, 2014 9:41 AM
    Monday, November 24, 2014 7:11 AM
    Moderator