Multi user local Database application, what DB and how to develop?

Hi, I've read the stickies but I still have some questions!

I need to code an office, database based application.
This will be a product life-cicle management for my team.
We receive several computers and components that we need to test and then send back to the owner.
I want multiple users to login in this application and to be able to:
- manage the logistic: what's in, what's out, generate and print delivery letters and other shipping related documents
- create a DB of products with their category and brands
- create a contact list with the right people addresses for each brand and supplier
- generate performances charts and be able to print them , save them as picture or upload them.

I was a Visual Studio developer but my last version was VB 5 :) Lately I developed a lot of stuff with PHP + MySQL, so I know how to create and manage databases.
I have already purchased several books and started studying my stuff. My company has an MSDN subscription, software availability is not a problem.

I'd like to use the LINQ language to manage DB interactions in order for the application to be ready for eventual moves to different DB technologies.

Unanswered questions:
- I'm almost sold on SQL server. I've read that LINQ can manage MYSQL but that has limited features with it. I have to learn everything in SQL server, while I can manage effectively MySQL. Can I effectively use MySQL with LINQ?

- Should I choice SQL Server, how do I create the development environment? I've done the first experiments and I was able to interrogate the server and to do some inserts. But every time I restarted the debug, all the data inserted or modified in the last execution disappeared.
Which is the right setup for this kind of development?

- Should I choose SQL Server, how can I manage the development from different locations? office, home, etc? If I used MySQL I would run it on a online server and configure the server to connect only with requests from certain IP addresses.

- Finally, how do I manage concurrency, when 2 ore more users access the same tables?

Thanks! Ciao!

LINQ is not actually a language but rather a technology that is used in applications written in other languages, e.g. VB.NET and C# apps can both use LINQ. Also, LINQ is an overarching technology but there are many different flavours, each implemented with its own provider. The idea is that you can use the same syntax in your app regardless of the data source but for each different data source there must be a specific provider, with the provider doing the translation between the common LINQ syntax and the specific data source. Data source for which providers exist include .NET collections (LINQ to Objects), XML data (LINQ to XML), ADO.NET DataSets (LINQ to DataSet), SQL Server (LINQ to SQL), Entity Framework (LINQ to Entities) and others.

If you were looking to use MySQL then you would use the Entity Framework provider for MySQL, which (I believe) is part of the freely downloadable Connector/Net from MySQL, to generate an EF model and then use LINQ to Entities in your VB or C# code. If you were looking to use SQL Server, I would suggest using the Entity Framework there also, in preference to LINQ to SQL.

I'm not sure whether it is supported in all editions but I'm fairly sure that SQL Server does support direct HTTP access. I've never used it though and most people don't because it increases your potential attack surface. Usually you would create a web service to connect to the database, but that might feel like wasted work if the finished app won't actually use the same web service. That might be a good architectural choice for the finished app though, so maybe it's a win-win.

If you have experimented with SQL Server and VS and you seem to be losing the changes you make then I would guess that that's because you are using SQL Server Express with a local data file and you haven't changed the default configuration. You won't have that issue with a permanently attached database but it's easily fixed with local data files anyway. To learn how, follow the first link in my signature.

ADO.NET supports optimistic concurrency by default. Other data access technologies, e.g. Entity Framework, will still use ADO.NET under the hood. If a concurrency violation occurs then an exception is thrown and the save fails. It's up to you to use appropriate exception handling and then notify the user appropriately. You might hard-code a specific course of action or ask the user how to proceed. Regardless of exactly what you choose, in order to save you will have to retrieve the data from the database again so that you have the most recent data and go from there again. You can read about optimistic concurrency and how it is implemented in ADO.NET at the MSDN Library.

Quote:

---

Originally Posted by jmcilhinney

If you were looking to use MySQL then you would use the Entity Framework provider for MySQL, which (I believe) is part of the freely downloadable Connector/Net from MySQL, to generate an EF model and then use LINQ to Entities in your VB or C# code. If you were looking to use SQL Server, I would suggest using the Entity Framework there also, in preference to LINQ to SQL.

---

Hi, thanks for this great reply! I will look into the entity framework provider for MySQL, maybe it's my best bet. Concerning LINQ to SQL, it's just a matter of being a total dummy; I've found how to use Linq to SQL, it was relatively easy to create a data contexts and my first queries were responding finely.
Thanks!

There are many similarities between LINQ to SQL and the Entity Framework. The most obvious difference is that L2S supports SQL Server only while the EF can support any data source for which an EF provider exists. Microsoft created their own for SQL Server but other vendors, icluding Oracle and MySQL, have either created or are creating them for their products too. Also, despite Microsoft saying that support for L2S will remain, EF will continue to get more features over time while I don't think that L2S will be expanded any further.

Thanks for your replies! I'm already working with the Entity Framework, I was a bit confused because I could find no "Linq to Entities" item in my Visual Studio. I then understood that I had to use "ADO.NET Entity Framework Entity Data Model".
I'm finally connecting my VB.Net app to a remote MySQL server and I've start developing :)