locked
Update a datagridview bound to a csv file

    Question

  • I am currently developing an application which involves binding a datagridview to a csv file. I am able to load the csv file correctly into the datagridview but not able to update it. I've tried using the commandbuilder as well as update commands with parameters. Can someone please help me with it? Thanks
    Tuesday, March 15, 2011 3:01 PM

Answers

  • I would recommend using a structured database format, such as SQL Server CE. Text files have no unique indexes or keys so they are difficult to update without overwriting the complete file.

    http://msdn.microsoft.com/en-us/sqlserver/bb895908

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Tuesday, March 15, 2011 3:44 PM
  • You don't say how often the database is being updated but one option to reduce the load could be to write to a file each time the database is updated.  The file doesn't need to contain anything, the intention being simply to update the LastWritten time.  When each user application needs to update the grid it would check the file's LastWritten time and only start an update if the file had changed since the last update - that would avoid reloading identical data to that which the user already has.  Then when getting the updated data you could do a Select which only reads records which have been updated since the last update ( you do have a TimeUpdated field? )

    • Marked as answer by araman2 Thursday, May 12, 2011 2:50 PM
    Tuesday, March 15, 2011 3:34 PM
  • Hi Araman,

     

    Welcome to the MSDN Forum.

     

    According to your description and the community members’ replies, there is a scheduler in the application and many people and some other applications will update it at any time. It seems that you want to display the live database data.

     

    If I have misunderstood anything, please feel free to let me know.

     

    Based on my experience, to get the fresh data, we should query the database timely. For example, if person #1 adds the progress 5%, the person #2 doesn’t get it right now on his own application without querying the DB in time. So I have a suggestion about this issue:

    1.      Set a timer in the application and the interval equals 2 minute. As you say, there are 50-60 person online at the same time, so the DB do a “select” operation per two seconds on average. This is not too much load to a large database such as SQL server with small data. If the data is large, it should be paged.

    2.      To avoid the dirty data, the “update” operation should like this: set a = a + 5.

    I hope this will be helpful to resolve the issue, if anything is unclear, please let me know.

     

    Best regards,
    Mike Feng [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, March 25, 2011 7:47 AM

All replies

  • You will have to re-write the file out to disk. 

    A csv file is just a text file with the columns indicated  by commas:

    Book1.csv opened in Notepad reads:

    2,3,rer,44,

    dd,3er,shadfg,562,8346

    rgfdfgd,ert3453,4351235,retwert,414512

     

    Tuesday, March 15, 2011 3:17 PM
  • To throw more light on the problem... The application that is being developed here is a scheduler which maintains a list of jobs for a company.. the list is pulled from 4 tables from a database. At any given time 50-60 users will be using this application and the grid is refreshed every 45-60 seconds.. Instead of filling the datagridview using a select command for that duration which would put a lot of load on the database .. I am wondering if a csv file could be the solution. This csv file will be generated every time the application is started.. and all updates will be made to the csv file and I am also executing a couple of procedures to make the updates to the actual database at the same time. So instead of the database all the users will be retrieving data from the csv file which will be updated every time a user makes any change. Please let me know if i am not clear. Also let me know if there is any other way I can achieve this .. the bottom line is to reduce the load on the database. Thanks
    Tuesday, March 15, 2011 3:17 PM
  • A csv file is just a dumb spreadsheet. You will probably need something smarter, like a database (that could use an excel spreadsheet as a source if necessary), to properly refresh the data in a timely manner and not run into file locking problems.
    Tuesday, March 15, 2011 3:27 PM
  • You don't say how often the database is being updated but one option to reduce the load could be to write to a file each time the database is updated.  The file doesn't need to contain anything, the intention being simply to update the LastWritten time.  When each user application needs to update the grid it would check the file's LastWritten time and only start an update if the file had changed since the last update - that would avoid reloading identical data to that which the user already has.  Then when getting the updated data you could do a Select which only reads records which have been updated since the last update ( you do have a TimeUpdated field? )

    • Marked as answer by araman2 Thursday, May 12, 2011 2:50 PM
    Tuesday, March 15, 2011 3:34 PM
  • I would recommend using a structured database format, such as SQL Server CE. Text files have no unique indexes or keys so they are difficult to update without overwriting the complete file.

    http://msdn.microsoft.com/en-us/sqlserver/bb895908

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Tuesday, March 15, 2011 3:44 PM
  • No the tables do not have a timestamp field. Also the problem with it is that this application is not the only one which can update the database.. there is a ticketing application which also updates the database on a regular basis. i dont think ill be able to detect if any change is made through the other application.
    Tuesday, March 15, 2011 3:44 PM
  • i dont think ill be able to detect if any change is made through the other application.
    But wouldn't you need to update the csv file with changes made by these other applications?  If not then aren't any changes they make irrelevant to this app.
    Tuesday, March 15, 2011 4:19 PM
  • Yeah.. come to think of it now.. no.. Ill need something that would let me know of the changes made through the other application... Is a timestamp field the only solution?
    Tuesday, March 15, 2011 4:22 PM
  • Probably not - I'm not a SQL expert - but it's the only one that comes to mind at present.
    • Marked as answer by araman2 Thursday, May 12, 2011 2:50 PM
    • Unmarked as answer by araman2 Thursday, May 12, 2011 2:50 PM
    Tuesday, March 15, 2011 4:46 PM
  • Hi Araman,

     

    Welcome to the MSDN Forum.

     

    According to your description and the community members’ replies, there is a scheduler in the application and many people and some other applications will update it at any time. It seems that you want to display the live database data.

     

    If I have misunderstood anything, please feel free to let me know.

     

    Based on my experience, to get the fresh data, we should query the database timely. For example, if person #1 adds the progress 5%, the person #2 doesn’t get it right now on his own application without querying the DB in time. So I have a suggestion about this issue:

    1.      Set a timer in the application and the interval equals 2 minute. As you say, there are 50-60 person online at the same time, so the DB do a “select” operation per two seconds on average. This is not too much load to a large database such as SQL server with small data. If the data is large, it should be paged.

    2.      To avoid the dirty data, the “update” operation should like this: set a = a + 5.

    I hope this will be helpful to resolve the issue, if anything is unclear, please let me know.

     

    Best regards,
    Mike Feng [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, March 25, 2011 7:47 AM
  • Araman,

    In addition to Paul, don't use a CSV if you want to update it, you can use it for transport but that is than definitally the end.

    I've code to rewrite it in a sample but don't show it to you, at a certain moment you come in trouble because an whatever error (strange character or whatever).

    As second solution to the one from Paul you can use an XML file, but that is only in the case that you have to transport that file outside the environment of the computer.

    It is very easy to use by readXML and WriteXMl if you use the DataSet, but also Linq to XML is especially with VB a very good choice.

    http://msdn.microsoft.com/en-us/library/bb386924.aspx


    Success
    Cor
    Friday, March 25, 2011 8:33 AM
  • Hi Araman,

     

    Thanks for posting in the MSDN Forum.

     

    Any update? I have marked Paul's and my own replies as answer, if you think it provides no help, please unmark it.

     

    Thank you for your understanding and support.

     

    Best Regards,


    Mike Feng [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, March 29, 2011 5:44 AM