How should I save the data in my program?

[G33kman]

Ok so I'm new to vb and I won't lie I'm stumbling left and right trying to figure this out.

I have a program that I am working on that we will enter information into every time a phone call comes in. I need to be able to collect a bunch of information (Customer information, Job type, comments about the job and some other minor information) I've been reading about different ways to save this information so that it can be reviewed and updated later if need be.

I looked at saving this information to a text file, xml document and a database (which seems extremely confusing) I would like to know how people typically go about saving this type of information.

Over time this "Lead Log" will grow as people call in so I know using a text file to save the information isn't the best option so I need a way to save this information so that it can be recalled later if needed.

Any ideas?

Ryan

[Shaggy Hiker]

From the sound of it, you might as well rule out everything other than a database, confusing or not. Text would be fairly easy, and XML could be extremely easy, but neither one is going to work well for this, especially since the size could end up getting pretty large. With either of those, the only truly effective way to deal with the data would be to load ALL of it each time you run the program. That's fine for a small number of records, but it becomes prohibitive as the size increases. Databases, on the other hand, are designed to deal with that. The two that I would consider would be Access and SQL Server Express. Access has some real limitations, especially if you will either be working over a LAN, or will have multiple users. The advantage to Access is that it is relatively easy to use outside of the program. SQL Server Express would be a total pain to use outside of the program unless you were careful to get the version with Management Studio. Finding a free download of SQL Server Express from MS is easy, but finding one that includes the Management Studio is not always what you get on the first try, and Management Studio should be considered essential.

From the programming perspective, there is little difference between the two databases. In both cases, MS has tried to add lots of tools and mechanisms to make it easier to connect to, and interact with, the database. I'm not thrilled with any of these tools. Entity Framework would be the one to look at, if you wanted to choose one, as it is getting better and better. However, in all of these cases, I feel that MS has been creating a black box. Once you learn the tool, you can do simple things easily. Getting beyond those simple things can be quite challenging to start out with, though, because you have to wade through a bunch of generated code that you probably won't understand at first. For this reason, I prefer the old school method of creating a connection, creating a Command object from that connection, then either filling a datatable (or dataset, which is a collection of datatables), using a datareader (faster than a datatable, but read-only and forward-only), or getting a scalar (a single value from a single field). You can get everything done using those techniques, and they are fairly simple, but I would expect that you will find FAR more discussion on Entity Framework, LINQ2SQL, Datasources, TableAdapters, and strongly-typed datasets. You don't NEED any of those things, they were all attempts to make working with a database easier, but the result is that there are so many ways to deal with a database by now that it can easily be confusing when you are starting out.

I would also build the tables in the database, whether that is Access, or Management Studio. You could create them via code, but that will be painful and has no advantage.

[G33kman]

From the sound of it, you might as well rule out everything other than a database, confusing or not. Text would be fairly easy, and XML could be extremely easy, but neither one is going to work well for this, especially since the size could end up getting pretty large. With either of those, the only truly effective way to deal with the data would be to load ALL of it each time you run the program. That's fine for a small number of records, but it becomes prohibitive as the size increases. Databases, on the other hand, are designed to deal with that. The two that I would consider would be Access and SQL Server Express. Access has some real limitations, especially if you will either be working over a LAN, or will have multiple users. The advantage to Access is that it is relatively easy to use outside of the program. SQL Server Express would be a total pain to use outside of the program unless you were careful to get the version with Management Studio. Finding a free download of SQL Server Express from MS is easy, but finding one that includes the Management Studio is not always what you get on the first try, and Management Studio should be considered essential.

From the programming perspective, there is little difference between the two databases. In both cases, MS has tried to add lots of tools and mechanisms to make it easier to connect to, and interact with, the database. I'm not thrilled with any of these tools. Entity Framework would be the one to look at, if you wanted to choose one, as it is getting better and better. However, in all of these cases, I feel that MS has been creating a black box. Once you learn the tool, you can do simple things easily. Getting beyond those simple things can be quite challenging to start out with, though, because you have to wade through a bunch of generated code that you probably won't understand at first. For this reason, I prefer the old school method of creating a connection, creating a Command object from that connection, then either filling a datatable (or dataset, which is a collection of datatables), using a datareader (faster than a datatable, but read-only and forward-only), or getting a scalar (a single value from a single field). You can get everything done using those techniques, and they are fairly simple, but I would expect that you will find FAR more discussion on Entity Framework, LINQ2SQL, Datasources, TableAdapters, and strongly-typed datasets. You don't NEED any of those things, they were all attempts to make working with a database easier, but the result is that there are so many ways to deal with a database by now that it can easily be confusing when you are starting out.

I would also build the tables in the database, whether that is Access, or Management Studio. You could create them via code, but that will be painful and has no advantage.

Ok so from the sounds of it my best best would be to use a database (kinda figured that would be what I would need to do) bah! This is starting to hurt the brain already lol.

So I can't create a database in vb (not even 100% sure how to make that work yet) that I can set up tables and enter in the headers (Customer ID, Customer Name, Customer Phone, etc...) and if the open button is clicked it will show a list of the most recent (however many) logs that were created otherwise if the new button is clicked and the form is filled out it will enter that information into the database?

The records will grow over time but 99.9% of the time only the last month or two will need to be accessed. I don't know if that makes things easier or not.

Ryan

[Shaggy Hiker]

You can create a database entirely in VB code....but it would be painful to do so.

Generally, what you described is not a database, but a dataset with multiple datatables (there is no need for a dataset unless you have multiple tables). Such a thing could be created in code fairly easily, and all of the actions you then described would also be fairly easy. However, the dataset would only exist in memory. Once you closed the program...it would be gone. Therefore, the other part is storing the information. Datasets are VERY easy to move to and from XML, because they have WriteXML and ReadXML methods that just take a file name and do the rest. Datatables have the same feature, but if you have more than one datatable, you might as well have them in a dataset and let the dataset read and write the XML.

The problem with that approach is that the XML file will be a fair amount larger than the data in your dataset because each item will be wrapped in tags. Therefore, if you think the total data will be fairly large...the XML file will be even larger. The fact that you will only need a portion of the records won't help much, either. That fact won't make the file any smaller. I have never tried directly searching an XML file, but I would expect that it is possible....using the exact same techniques that you'd have to use to search a real database, and totally removing the advantage that ReadXML and WriteXML gave you. So, the only way that XML would be easier is if you read in the WHOLE file every time the program started, and wrote out the whole file before closing (or at other times, as needed). Therefore, the data you care about wouldn't matter, as you'd have ALL the data every time. Over time, this would start to cause problems, and would eventually be more than your system could handle. At that point, the simplicity of ReadXML/WriteXML will have been totally wasted, as you'll have to throw it out and move to a real database.

With a database, you'd write a query that returned just the records you wanted (just those last months, perhaps), and load it into a datatable. The code for this is utterly trivial:

 Using cn as New SqlClient.SqlConnection(someConnectionString)
  Using cmd As SqlClient.SqlCommand = cn.CreateCommand
   Using da As New SqlClient.SqlDataAdapter
    da.SelectCommand = cmd
    cmd.CommandText = "SELECT some fields FROM some table WHERE something or other"
    Try
     dim dt As New Datatable
     da.Fill(dt)
    Catch ex As Exception
     'Do something with the exception, if thrown.
    End Try
   End Using
  End Using
 End Using

There are a few things about that that are not ideal, and it doesn't provide you with a connection string (Google for connection strings to find a page that gives you connection strings for all sorts of databases), and the SQL statement is pure fiction (though structured right for a simple query), but those are all the steps you need. I wouldn't be creating the datatable where I have it in that example, and you can do different things with the exception handling, but the fundamentals are there

[jmcilhinney]

You might benefit from following the Data Walkthroughs link in my signature.