none
Local database suggestiones RRS feed

  • Question

  • Hi,

    I am writing a program in VB.NET (VS2005 Pro) that essentially simulates an neurology experiment. This program's data shoould consiste of 8 independent tables, each having 13 columns and up to 100,000 rows in each table. To make it simpler, all data will be in string format.
    Other requirments:
    1. The tables needs to be sortable like a dictioanry where 1 column will act as a key.
    2. The user needs to be able to see the data in a tabular format.
    3. The data must be held on the user's local machine, not a server and not remotley.
    4. Needs to easily be readable by Excel, doesn't matter how. It can convert to an XML file and then somehow be automatically converted to xls.

    I am thinking of using a databse becasue I would like to eventually create a login screen to link previous experiment values to a user, but that is not essential. I used dynamic multi-dimensiaonal arrays but find it difficult to work with, and extremley difficult to sort.

    I was looking at the idea of using an XML database which will probbaly solve any issues of excel compatability as well as provide support for displaying the information on a website sometime in the future. I can't figure out how this all works.

    Can anyone recommend a way to display a large amounts of generated data on a local computer, and almo make it save-able. I also like Oracle's 10g Express thing, but don't know if it would work for my specific problem becasue the cusotmer support people at Oracle don't speak English very well.

    Thank You,

    Maksim
    UCLA, Neurology
    Saturday, June 9, 2007 2:43 AM

Answers

  • Within your program, you're going to use DataTable and DataView objects, which basically let you do everything you would do with database tables (insert/delete/change rows, filter, sort, apply constraints, etc.) without concern for where the data actually lives.

    In your UI, you'll use the BindingSource to bind DataView objects to DataGridView controls, which should give you everything you need in order to present tabular data to users.  (I can't emphasize this enough:  if you're going to write a data-bound application, you must understand the BindingSource. )

    To make the data persistent, you have a lot of options.  I'd use SQL Server Express, because it's free, does what you need, and it's what I know.  It'll have no problem dealing with tables with the amount of data you're talking about.  And getting data out of SQLExpress and into Excel is straightforward enough.  If you use SQLExpress, you'll use DataAdapter objects to handle the interaction between the database table's select/update/delete commands and the contents of the DataTable in memory.

    You can also just serialize the DataTable to XML; the DataTable has ReadXml and WriteXml methods that handle all of this pretty transparently.

    You can create DataTables programmatically.  You can also create them in the DataSet designer, which is a pretty useful UI for managing a set of DataTables.

    There's a lot to learn about here, but it will all do what you need.  It takes some time to get an understanding of each piece's role in the data access puzzle, but once you see how they fit together it should all make sense.

    Hope this helps.




    Sunday, June 10, 2007 9:30 PM

All replies

  • The ADO.NET DataSet comes with both read and write XML methods you can use to generate XML and you can store XML in SQL Server and create indexes for the tables as needed.  The easy way to convert your data to Excel use Office 2007 and you will find new code to convert table data to Excel.  The last part is not required because you can find code doing the same for Office 2003.  Hope this helps.

     

     

     

    Saturday, June 9, 2007 1:05 PM
  • The purpose of the software is to make it completley standalone with no remote databases or connections.  It also must be installable on a client's computer without any difficult installation of SQL Express or anything like that. 

     

    Is there an explanation of how to write Native XML files that will act as my data storage unit?  And I also can't find any explanaton of how to create a usable DataSet and how to fill it without connecting to another database.

    Saturday, June 9, 2007 4:47 PM
  • What about using an .xls file as the datastore directly or failing that, an access .mdb file?
    Saturday, June 9, 2007 4:54 PM
  • Excel XP and above comes with save as XML option so you can just put your data in Excel put the Excel in a DataSet do all the manipulations you want and call the ReadXML method of the DataSet.  I know in a Web application you can use the Repeater control to display such data, but I think you are talking about WinForm application so I would say try the DataGridview.  A WinForm expert may give you a better option.  You could use Access as the previous post stated but Access comes with permissions issues.  I don't think you will find XML as data storage because it is just a markup language like HTML, however it is accepted into the Relational Model because the creators have agreed to do the algebra needed to process the data.  So the answer is any commercial RDBMS will store it native.

     

     

    Saturday, June 9, 2007 6:03 PM
  • Thank You,

     

    Lets assume the clients don't have Office installed, but are running XP.  What would you recommend to store all the tabular data, that would be easy to pull create/edit/delete individual rows?  I essentially want to reference each cell like I would in a multi-dimensional dynamic array except I want it to save in a readable format.  I like XML becasue it would probably be easier to display on a web-site in tabular format if the client doen'st have Excel installed.

     

    Saturday, June 9, 2007 7:23 PM
  • XP from Hardware vendors comes with Microsoft Works which comes with a spreadsheet that can read and write to Excel files, I have also seen developers recommend Aspose Cells which can be used free.  I think you are required to carry adds for the product in your application I am not sure how that works.  I think Excel makes it very easy to develop and maintain.

     

    ( I essentially want to reference each cell like I would in a multi-dimensional dynamic array except I want it to save in a readable format.)

     

    Multi dimensional arrays in the FCL(framework class library) 1.1 and 2.0 are not sort able if that changed in 3.5 I don't know I have not check but jagged arrays which is similar are sort able.  When you content needs to be sort able nothing is easier than a database Order By, I think you need to write down your needs and check for availability in the current framework and I only see Excel making it easy for you.  Hope this helps


    http://www.aspose.com/

    Saturday, June 9, 2007 11:56 PM
  • That actually helped a lot.

     

    So my application will send a 18-character long string through a serial port to an electrical stimulator.  The string contains a complete command of all the parameters a stimulator need (ex, C1-001222123454645, C1=channel 1, 001=amplitude...,=width,...=delay).  The user will make rules for the parameters that include how often they would like that particulr rule to update.  A quick example is that they would like to update the width by increasing it by +2 every 10 seconds, but every 30 seconds they want the width to be exactly 30. Then they can have a rule to specify that the amplitude will increase by 3 every 12 seconds.  So I find the most common update rate ,   in this is 2, and create a table of the parameters.  Then every 2 seconds the application sends the generated sequence to the stimulator.  To create this table I use a layer-type approach where I first populate the rule that has the least update rate and then move from there.

     

    I need to easily create/delete rows, and easily edit values in cells in a non-sequencial order.  I display the generated table as a detailed ListView, I can theoretically do all the generating in Excel but I tried that once and it only loaded Excel on my computer but not on any other ones.  Now that I have Office 2007, I am afraid I'll amke it work for 2007 but not for anything earlier.  If I have a pre-built stand alone system then I don't have to worry about this. 

     

    I only need the sorting to be done on the rules, whcih i made my own algorithm for but would like to use something more robust for possible expansion later on.  Do you know of a sure-way to communicate with all Excel versions? 

    Sunday, June 10, 2007 4:51 AM
  • You are adding more complication because serial port communication through System.IO was introduced in FCL(framework class library) 2.0, I have not used it however I have seen developers running into issues with it.  Now the data part is relatively easy because I think serial port communication is complicated.  Try the link below for the serial port class.

     

    http://msdn2.microsoft.com/en-us/library/system.io.ports.serialport.aspx

    Sunday, June 10, 2007 12:36 PM
  • I haven't run into any problems with serial port in .NET, only through VBA.  I have Excel 2007, but I haven't tried communication through it yet.  I am still hoping to find an easily managed data source.
    Sunday, June 10, 2007 5:10 PM
  • You need to sort the data nothing is better than order by so save your Excel as XML and persist the data in Access because you can package your software with the Jet engine for distribution. 

     

    http://office.microsoft.com/en-us/access/HP010950951033.aspx

    Sunday, June 10, 2007 6:26 PM
  • Yeah, that is what I think I'll do.  I see that I can make a Data connection to an Excel sheet. I put it in my Resource file and linked it from there, but it made the connection ad c:/UCLA/.../Resource so I don't think it'll work correctly if I deploy it.
    Sunday, June 10, 2007 8:11 PM
  • Within your program, you're going to use DataTable and DataView objects, which basically let you do everything you would do with database tables (insert/delete/change rows, filter, sort, apply constraints, etc.) without concern for where the data actually lives.

    In your UI, you'll use the BindingSource to bind DataView objects to DataGridView controls, which should give you everything you need in order to present tabular data to users.  (I can't emphasize this enough:  if you're going to write a data-bound application, you must understand the BindingSource. )

    To make the data persistent, you have a lot of options.  I'd use SQL Server Express, because it's free, does what you need, and it's what I know.  It'll have no problem dealing with tables with the amount of data you're talking about.  And getting data out of SQLExpress and into Excel is straightforward enough.  If you use SQLExpress, you'll use DataAdapter objects to handle the interaction between the database table's select/update/delete commands and the contents of the DataTable in memory.

    You can also just serialize the DataTable to XML; the DataTable has ReadXml and WriteXml methods that handle all of this pretty transparently.

    You can create DataTables programmatically.  You can also create them in the DataSet designer, which is a pretty useful UI for managing a set of DataTables.

    There's a lot to learn about here, but it will all do what you need.  It takes some time to get an understanding of each piece's role in the data access puzzle, but once you see how they fit together it should all make sense.

    Hope this helps.




    Sunday, June 10, 2007 9:30 PM
  • Thanks a lot Robert, that helped tremendously.

     

    In my solution I went to Add New Item > SQL Database and got the following error: my default settings don't allow remote connection. 

     

    If it were to work, how would deploying the enitre application look like?  Would I need them to install the SQL Express?

    Monday, June 11, 2007 12:21 AM
  • Ahhh ... I'll have to check out the ADO.NET DataSet.  We're trying to port our iPhone password manager app to Windows Phone 7, but were stuck since there was no local database support (yet).  More so stuck because we didn't want to work with XML files, but if the DataSet class can do what I think you're suggesting it can do then we might have a reasonable alternative than having to purchase and license a commercial solution.  Hopefully the performance is not horrendous. Thanks Caddre,

     

    Kevin

    ---

    LOCKBOX, the easiest to use and most secure file transfer service


    President & Founder (www.impactalabs.com)
    Wednesday, March 16, 2011 7:42 PM