Question what is the best way to work with tables?

[kwanbis]

I returned to my university to finish my thesis. Sadly, so much time has passed that I find that they no longer use C to program the thesis (you have to develop a system), but that they now use VB.NET for which I never had a single class. I tried to convince them to let me do it in C, but they won?t allow me so now I have to develop a system using a language I barely understand.

I decided to make a Point of Sale software. Basically, a cashier needs to select from a list of products the products that the customer is purchasing and after all the products have been input on the system, the cashier presses a button and an invoice is printed and the transaction recorded.

I thought of solving this with two datagridviews, one linked to the stock table that would list all the products from the stock, so that when the cashier double clicks on one of the products, the product would be added to the second datagridview. The second datagridview should be empty each time a new customer starts the purchase process, and with each double click on the stock datagridview that product would be loaded to the purchase datagridview.

Once the cashier has finished processing the purchase, he would press the invoice button which would launch a couple of actions: 1) the system would iterate through the purchase datagridview and print the relevant information on the invoice, and 2) all that information would be saved to a tabled called effective_purchases or something like that for record keeping.

I?ve seen some work done by other students but I?m not 100% sure that they are doing the right thing. Basically, they use a SqlDataAdapter to run a query against the stock table, then they use a the .Fill function to fill the DataTable. Then they create a List that holds objects of the type product that are created by iterating through the datatable and that list is assigned to the datasource of the datagridview.

Basically, something like this:

        Dim adaptadorSQL As New SqlDataAdapter("select * from stock", _conexion)
        Dim tablaDatos As New DataTable
        adaptadorSQL.Fill(tablaDatos)
        Dim ProductsList As New List(Of Product)
        For Each row As DataRow In TablaDatos.Rows
            Dim Registro As New Product
            Registro.ID = Integer.Parse(row("ID").ToString)
            Registro.Name = row("Name").ToString
            Registro.Value = Decimal.Parse(fila("Value").ToString)
            ProductsList.Add(Registro)
        Next
        dgvProducts.DataSource = ProductsList

So I have many many doubts. Considering I rather not use datasets, is this the simplest way to link a database table to a datagridview? If not, how should I do it? And once I have the products datagridview linked, how do I make the purchases datagridview? Remember that at first it has to be empty. So, do I create an empty table (temporal) and once the purchase button is press do I copy that temporal table to the definitive purchases table? How?

By the way, I rather NOT use a dataset, as my professors told me it is very complicated and that by using simple queries I should be able to do it.

THANKS A LOT.

 

[jmcilhinney]

Firstly, your professors are wrong. There's nothing especially complicated about DataSets. In fact, it might make your life easier. You can use a data adapter to populate a DataTable, which may or may not be in a DataSet, and then simply bind that to your grid. The grid will create the columns for you and the data will be displayed. The user can then make changes (add, edit, delete) and you can then save all the changes to the database with a single call to the Update method of the same data adapter. If you were to use a typed DataSet then there's even less code to write and you also get more help from Intellisense.

If you specifically want to avoid using a DataSet - or, more accurately, a DataTable - then you would have to use a data reader. You can create a command object - the same command object that would otherwise be used by a data adapter to populate a DataTable - and then call ExecuteReader to get a data reader that can be used to loop through the result set of the query. The real issue then comes when you want to save changes. You can either populate a DataTable with all your changes and then save them in one go or you save each one separately. I would suggest that you take a look at these code examples for a start, to get a feel for the ways you can use ADO.NET in different scenarios:

Retrieving and Saving Data in Databases

 

[Herman]

You could also use Linq-to-SQL or Entity Framework, which would be my first choice above datasets.

http://www.vbdotnetforums.com/linq/61075-converting-noob-sql-linq.html