Results 1 to 17 of 17

Thread: How to store data in VB.net?

  1. #1

    Thread Starter
    New Member
    Join Date
    Apr 2018
    Posts
    4

    How to store data in VB.net?

    Hi guys.
    I am new to .net environment and trying to make a new application.

    I want to store data in that application. For example, every time a user make a selection, that needs to be stored somewhere.

    So what are the ways to do that and what is the maximum data sizes that can be stored.

    Thanks.

  2. #2
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: How to store data in VB.net?

    There are many options, but there are probably only about three worth considering.

    1) My.Settings: You can add settings in Project | Properties on the Settings tab. These can hold a variety of things, and are essentially variables that persist from one run of the program to the next. Be aware that they are stored as plain text, so if there is anything that needs to be secure, it has to at least be encrypted. You likely wouldn't want to go higher than a few dozen items using this route, but it is about the easiest possible option.

    2) A datatable: A datatable is like a database table in memory. You can create them easily enough, then add columns to it for whatever fields you want. You then add rows that hold the data. What is convenient about a datatable is that you can easily store it to an XML file, because it has WriteXML() and ReadXML() methods. An XML file is just plain text, and can be opened with any text editor, such as NotePad, so once again, if it has to be secure, then it has to be encrypted. What the limit on this is I can't say. I seem to remember somebody said they were using that method for over 100,000 records in a file. The reading and writing might slow a bit as you get to that size, but that's the only real issue. Datatables also have many means to sort and filter data, and can be displayed VERY easily using a DataGridView.

    3) A database: This is the biggest and most complex option. This would be for storing all kinds of data, and is the data store for most of the worlds data. It's a subject all its own, though, and there are many different databases and many different ways to work with them. It's ideal for complex data, multiple tables, and complicated searching. The size depends on the database, but billions of records aren't out of the question. I would say that working with a database is one of the most common skills in coding simply because of the number and versatility of databases available and already in existence. It certainly is the hardest to learn, but considering how fundamental it is, there are also lots of resources to help learn it.
    My usual boring signature: Nothing

  3. #3
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: How to store data in VB.net?

    Rather than asking us what are the maximum data sizes, how about you indicate how much data you're likely to want to store? You say that you want to store user selections. What is a "user selection"? You could be far more clear about what you actually want to do.

  4. #4

    Thread Starter
    New Member
    Join Date
    Apr 2018
    Posts
    4

    Re: How to store data in VB.net?

    Hi guys,
    My data size may vary from a 10,000 rows to a million.
    By user selection I mean, for example, I give a user 5 options to select from, he has to select one option and after he has made that selection, the count of that selection should increase. So...

    A 0 - B 0 - C 0 - D 0 - E 0
    after user chose option 'B', it should be like:
    A 0 - B 1 - C 0 - D 0 - E 0
    and so on...

    I want to connect my application to a database so that it is more flexible for me to do the job. I am able to connect and update the database but not sure how to retrive the same. Can you help me with the same?

  5. #5
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: How to store data in VB.net?

    Sure, but it depends largely on the database. If you could get to millions of rows, then that kind of rules out pretty much everything OTHER than a database, and rules out some databases, too, such as Access.

    From what you have described, one alternative would be to have a table with the options as fields, and one row be a user selection. That would work as long as there will only ever be options A-E, and I do mean EVER! If there is a chance that an F might be added at some point in the future, then what I described is pretty bad. In that case, you'd want at least two tables, one with the selection action, and the other being the selection. So, Table 1 might have the user identification, which might be name, time the selection was made, and so on, then the second table might have one or more records for the selections made at that selection time. So, they might have A:0, B:0, C:0, D:0, E:0 all tied to the first selection, while the second selection would also have five rows, that would be the same except that there would be B:1.

    So, that second table might have a selection ID (a foreign key relationship to the first table), a item ID (the primary key of the table), the selection Type (A-E, currently, but could be more things later), and a bit field for the selection.
    My usual boring signature: Nothing

  6. #6

    Thread Starter
    New Member
    Join Date
    Apr 2018
    Posts
    4

    Re: How to store data in VB.net?

    The thing you described is pretty complex for me as I am a absolute beginner to .net platform. So I have decided I will try just to make the structure of application and fetch the data needed from excel for now. I will include the database thing in future or other versions.

    Meanwhile, can you help me with the code of how to open an excel and refer to a particular cell?

    Thanks a lot for your support!!

  7. #7
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: How to store data in VB.net?

    Actually, you'd be better off with the database. To use Excel would be either exactly the same code as you'd use for a database, or it would be FAR more difficult. Take your pick. There isn't a way to get to Excel that is easier than the methods used to get to either Access or SQL Server (including the free SQL Server Express), but there IS a way that is much harder.

    For any of those, you'd be using ADO.NET, and that's many different subjects itself. For example, I'd prefer to use what may be called raw sql:
    Code:
    Dim dt As New Datatable
    
    Using cn As New Connection(SomeConnectionString)
     Using cmd As Command = cn.CreateCommand
      Using da As New DataAdapter(cmd)
       Try
         cmd.CommandText = "SELECT SOMETHING HERE"
         da.Fill(dt)
    
        Catch ex as Exception
          'Do something with any issues.
       End Try
      End Using
     End Using
    End Using
    There are a variety of concepts in there, so it can look daunting. The Using construct is convenient, as it will clean up the connection even if something goes wrong. You won't see it in much older examples, though. I think the Using construct either wasn't added, or at least wasn't popular before some point like 2005. Another point is that you can never trust a datasource, so catching any exceptions it might throw is a good idea, even if you don't want to do anything other than showing a messagebox to say that something went wrong.

    However, the basic idea works for any datasource, which includes Excel, some text files, and databases: You create a connection to the datasource (the Connection object, though there will be different types for different providers), you then create a dataadapter, you come up with a select statement to get the data you want, and finally you fill a datatable with data.

    There are variations of this, such as things that just get one value from one column, and a variation that gets each row, but in a read-only, forwards-only, fashion. Those are faster than the dataadapter, but you are trading limitations for speed. It's all plenty fast, though, for most things.

    That general design is so generic that it has been abstracted away in various ways. For example, there is Entity Framework, where you start with the data you want, and it can even build the database for you. There is also a datasource wizard that creates TableAdapters rather than DataAdapters. The TableAdapter gives you a strongly typed Dataset, which means that if the column takes a String, you had better give it a string. If it takes an integer, you have to give it an integer, and so on. Yet another alternative would be LINQ2SQL, though that has fallen out of favor, and doesn't seem to provide any advantage over the other options. I know little about that one, too, so it may be an aspect of one of the others.

    In any case, you can create a connection to an Excel spreadsheet with the right connection string, in which case you may be able to use any of the options. The alternative is to use the Excel interop, in which case you open a workbook, open workseets in the workbook, then use various methods to navigate around and read data. You have to reference the interop, though, and there is a different interop for each version of Excel. That means that if you upgrade Excel...your program stops working. You can get around this using late binding, but you really don't want to build a program using late binding. You build it with a reference to a specific Excel, and only switch to late binding once you have the design well tested.

    I don't find the interops to be all that well documented, so my experience with programming against the Excel interop was kind of negative in that it just didn't seem all that helpful. If things went wrong, it could be harder to figure out what went wrong, and why. Then when you add late binding, where you get NO help, it just makes things more difficult, but without late binding, you are tied to just that version of Excel that you developed against. That's a pain, too.
    My usual boring signature: Nothing

  8. #8
    You don't want to know.
    Join Date
    Aug 2010
    Posts
    4,578

    Re: How to store data in VB.net?

    Yeah, I had this typed out and decided I didn't want to be negative, but it bears saying:

    Working with Excel is just about one of the MOST complicated things you can do. As a newbie, I expect you'll be very frustrated and unsuccessful with it. To use Excel from .NET you need to understand:
    • The fundamentals of .NET.
    • The fundamentals of COM.
    • How to read both VB .NET and C++ examples.
    • The fundamentals of .NET/COM interop.
    • The Excel object model.


    The best tool for interacting with Excel is the VBA editor within Excel. Experience with that kind of sort of maps to .NET, but you have to understand a lot of little gotchas and be willing to deal with lots of cognitive tasks like "remember to dispose of objects in exactly the reverse order of creation, giving time for the GC to collect in between." Or "Some COM collections index starting at 1, but .NET doesn't reflect that so you just have to remember not to access element 0."

    You'd be much better off learning to interact with a database, even if it's a full-featured one like MSSQL first. I think it's a little ambitious to shoot for a million-record project if you're very new. Make that a goal for 2 years from now, trust me.
    This answer is wrong. You should be using TableAdapter and Dictionaries instead.

  9. #9
    PowerPoster i00's Avatar
    Join Date
    Mar 2002
    Location
    1/2 way accross the galaxy.. and then some
    Posts
    2,388

    Re: How to store data in VB.net?

    Quote Originally Posted by Sitten Spynne View Post
    Yeah, I had this typed out and decided I didn't want to be negative, but it bears saying:

    Working with Excel is just about one of the MOST complicated things you can do. As a newbie, I expect you'll be very frustrated and unsuccessful with it. To use Excel from .NET you need to understand:
    • The fundamentals of .NET.
    • The fundamentals of COM.
    • How to read both VB .NET and C++ examples.
    • The fundamentals of .NET/COM interop.
    • The Excel object model.


    The best tool for interacting with Excel is the VBA editor within Excel. Experience with that kind of sort of maps to .NET, but you have to understand a lot of little gotchas and be willing to deal with lots of cognitive tasks like "remember to dispose of objects in exactly the reverse order of creation, giving time for the GC to collect in between." Or "Some COM collections index starting at 1, but .NET doesn't reflect that so you just have to remember not to access element 0."

    You'd be much better off learning to interact with a database, even if it's a full-featured one like MSSQL first. I think it's a little ambitious to shoot for a million-record project if you're very new. Make that a goal for 2 years from now, trust me.
    Nope .. I would suggest NOT using the excel interops ...
    Excel is rubbish, do yourself a favor and use EPPlus instead.

    It has the following advantages over the MS interop rubbish:
    • Doesn't require Excel
    • Less complex to use
    • Much, much faster (over 100x in my tests)
    • Doesn't require handles to run so can be used without restriction in services etc
    • Doesn't prod Excel about (Interop libs manipulate Excel - hence the speed issues, also in interop: sometimes, even when it is set to hidden in code it will flick up on the users screen if they are using Excel and do this on-screen, this allows the user to "inject data" accidentally and if the user closes Excel an exception will be thrown in your program)
    • Supports more than 1 million rows and zz columns


    The only disadvantage I can think of is that
    • It only supports xlsx files


    This reduces the list (in the quote) to just:
    • The fundamentals of .NET.


    Kris

  10. #10
    PowerPoster i00's Avatar
    Join Date
    Mar 2002
    Location
    1/2 way accross the galaxy.. and then some
    Posts
    2,388

    Re: How to store data in VB.net?

    Small subset of what I would suggest (based on what I find I regularly use):

    • File, project settings, or registry for settings.
    • Some kind of serialization for complex data types.
    • Database for lots of data (SQL for multi user (server/client), SQLite (for single user)).
    • Excel / word for Exporting
    • Excel, csv for importing


    Kris

  11. #11
    PowerPoster i00's Avatar
    Join Date
    Mar 2002
    Location
    1/2 way accross the galaxy.. and then some
    Posts
    2,388

    Re: How to store data in VB.net?

    Quote Originally Posted by Shaggy Hiker View Post
    Sure, but it depends largely on the database. If you could get to millions of rows, then that kind of rules out pretty much everything OTHER than a database, and rules out some databases, too, such as Access.
    Why does this rule out access :
    https://support.office.com/en-us/art...8-98c1025bb47c

    Kris

  12. #12
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: How to store data in VB.net?

    Quote Originally Posted by i00 View Post
    Why does this rule out access :
    https://support.office.com/en-us/art...8-98c1025bb47c

    Kris
    The fact that you can do something is not an indication that you should.

  13. #13
    PowerPoster i00's Avatar
    Join Date
    Mar 2002
    Location
    1/2 way accross the galaxy.. and then some
    Posts
    2,388

    Re: How to store data in VB.net?

    Quote Originally Posted by Shaggy Hiker View Post
    In any case, you can create a connection to an Excel spreadsheet with the right connection string, in which case you may be able to use any of the options. The alternative is to use the Excel interop, in which case you open a workbook, open workseets in the workbook, then use various methods to navigate around and read data.
    Would take the connection string option too over interop as doesn't require Excel, is much faster and would more in-line to extending it to use a DB in future (better for learning about data access too that can be transfered to your future knowledge database related programming).... it does not support formatting tho ... read my other post for more stuff on this #9

    Quote Originally Posted by Shaggy Hiker View Post
    Yet another alternative would be LINQ2SQL, though that has fallen out of favor, and doesn't seem to provide any advantage over the other options. I know little about that one, too, so it may be an aspect of one of the others.
    I don't really understand why it has "fallen out of favor".. I personally think that it is much better than EF, as I find that I can extend it much more too! ...

    I am actually planning to release a library to extend Linq2SQL that supports HEAPS more features (a lot more than EF).

    Sample of what you can do:
    VB.Net Code:
    1. 'Generate an operation to delete all the people since version 20 that are over 50
    2. Dim Operation = DBML.BulkOperations.
    3.                      AddDelete(DBML.GetTrackedChanges(Of Database.Person) _
    4.                                                      (20, Nothing,
    5.                                                       ChangeTrackingTypes.Inserts).
    6.                                     Where(Function(x) x.Age.GetValueOrDefault > 50))
    7. 'Preview the sql to the debugger
    8. Debug.Print(DBML.PreviewSQL(DBMLBase.DatabaseCommon.MergeSQLPramsModes.InPlace))
    9. 'Submit it
    10. DBML.SubmitChangesException()
    SQL Code:
    1. DELETE FROM [dbo].[Person]
    2. FROM [dbo].[Person] AS [j0] INNER JOIN (
    3.     SELECT [t1].[ID]
    4.     FROM (SELECT [_b4f7221e0e3a4ffb819298fe4cb7a202].[ID] AS Value FROM (SELECT [ID] FROM CHANGETABLE(CHANGES [Person], 20) AS DataChanges WHERE SYS_CHANGE_OPERATION = 'I') AS [_b4f7221e0e3a4ffb819298fe4cb7a202]) AS [t0]
    5.     INNER JOIN [dbo].[Person] AS [t1] ON [t0].[Value] = [t1].[ID]
    6.     WHERE (COALESCE((2018 - DATEPART(Year, [t1].[DOB])) - (
    7.         (CASE
    8.             WHEN (DATEPART(Month, [t1].[DOB]) >= 4) AND (DATEPART(Day, [t1].[DOB]) > 13) THEN 1
    9.             ELSE 0
    10.          END)),0)) > 50
    11. ) AS [j1] ON ([j0].[ID] = [j1].[ID])

    Kris

  14. #14
    PowerPoster i00's Avatar
    Join Date
    Mar 2002
    Location
    1/2 way accross the galaxy.. and then some
    Posts
    2,388

    Re: How to store data in VB.net?

    Quote Originally Posted by jmcilhinney View Post
    The fact that you can do something is not an indication that you should.
    Completely agree... would never advise access (hence why I did not suggest it in #10 )... just saying that that is not technically a limitation

    Kris

  15. #15
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: How to store data in VB.net?

    Dates already correct. No amendment required.
    Probably the biggest reason is that it's SQLServer only. You could write your own DataContext to connect to other platforms but it's a huge undertaking so you probably wouldn't. And there were some third party libs that you could grab but they took a long time to arrive on the scene and largely missed the boat.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  16. #16
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: How to store data in VB.net?

    Quote Originally Posted by i00 View Post

    I don't really understand why it has "fallen out of favor".. I personally think that it is much better than EF, as I find that I can extend it much more too! ...
    Yeah, I don't understand it, either. I don't use either LINQ2SQL or EF, but there's more chatter around the latter, and I hear none about the former. Don't know why that is, and it could be sampling bias, but it is what it is.

    Two million records may or may not be a limit for Access. There's a limit to the size of an Access database, or maybe that was just mdb databases, but it doesn't really matter from my perspective. If you are talking about millions of records, then I don't think Access is a good back end for that. For one thing, Access has some strange instabilities that can cause data corruption, and Access has some limitations that restrict how effectively it works once you get beyond a single user. I think it can be a good back end for field systems, but I would want something more robust if I'm expecting millions of records.
    My usual boring signature: Nothing

  17. #17

    Thread Starter
    New Member
    Join Date
    Apr 2018
    Posts
    4

    Re: How to store data in VB.net?

    Thanks a lot guys!!
    I will definitely try what all of you have suggested.Will take some time as its too complex for me. Will keep you posted of the outcomes.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width