What is the best local database to choose for a simple project in Visual Studio?

Question

Hello. I have a project. This is the client of the site, which stores the list of the series you have watched. Request your list from the site takes time, especially if the list is large. The smallest request slowed down the work of the program for 5 (!) Seconds. Without the list, the application does not make sense, and therefore can not start working.

In this regard, I thought to solve the problems as follows. Request a list from the site when you first start the program and authorize to the local database. Next, display information from it. Updating the database would be carried out only by data from the server by querying the list at a certain interval or when making changes to the list by the user.

Example. The user first launched the program. The program requested a username and password and remembered the user. Then sent a request to the server and received a list. The first launch in any case will be long. Further, these data would be stored in a local database. The user would already display a list from the database. In order to keep local database data up-to-date, once, for example, in 5 minutes, the program would send a new request to the server and update the data. Or when the user initiates changes in the software (adding viewed series or other things). This would be done in a separate thread.

On subsequent launches of the program, it would start instantly, displaying the list stored locally to the user, while initiating an update request in a separate thread.

Database Requirements

Local The database with the list of serials should be stored on the user's computer in the program folder.
Portable. The database should not require the user to install additional libraries on the user's computer, in addition to the .Net Framework, which the program itself will require. Unless it will be portable libraries which will be in the folder of the program itself.
Not bulky. Since the database will be on the user's computer, I didn’t want the table of 10 fields with 1000 lines to weigh under a gigabyte.
LINQ support. Nativno or by connecting third-party libraries, I would like to perform all queries through LINQ, and not SQLConnect.

Most likely, there will be one database and 4 tables in it. No complicated things like stored procedures or triggers are required of it. Only storage. All actions with it will be done directly through the program.

Considered as a variant sqlite, but not sure that it has good support for LINQ. And I decided to consult first. If you know a good way to make friends sqlite with LINQ or there is a solution for better, then I will be grateful for the help.

Theoretically, LINQ can be attached to whatever base you like.
You describe entities in the form of classes and update methods.
And by the way, you can load data into a DataTable and perform any LINQ operations on it.
@ iluxa1810 I need the database to exist even after exiting the application and save all the data, and not only while it is running ..
@PrimusSingularis and there is a suitable guide / library, how to use it along with linq?
@ Mr.Modest exactly what Linq do you need IEnumerable or IQueryable?

Accepted Answer · 2017-01-22T15:20:50

You can use Access as a local database if you do not plan to store a huge amount of data. (Access supports no more than 2 gigabytes of data).

The presence of the Office itself is not required, you can put free Access Engine to work with Access .

Regarding LINQ , then you can work with tables in memory through LINQ queries to the DataTable , and then at the end of the work, transfer the data to Access .

Also, instead of working through DataTable you can describe entities as classes and use Dapper to write your ORM. Dapper has automapping, which allows you to reflect the data returned by the query automatically on the class fields.

If you don’t want to reinvent the wheel, you can search for a ready-made ORM. If I'm not mistaken, then NHibernate supports working with Access out of the box .

well ... the number of rows per table can reach somewhere around 5-6 thousand according to the idea ...
Unless of course you are going to store the files in the database themselves.
neither the program itself, nor the DB will work with files .. the maximum is 4-5 tables with 30 fields in each .. rows and numbers
If you describe a class, just like a table in a database, then when you call conn.Query <Table1> ("SELECT * FROM Table1"). ToList () will automatically return the filled list to you and itself zamapit you.
@ iluxa1810, Microsoft SQL Server Compact Edition is very easy to deploy, used in one of the projects.

Sergey Rufanov Sergey Rufanov 7,966 15 34 · Answer 2 · 2017-01-22T15:36:41

Use the usual serialization, in any format you like (XML, MS-NRBF, JSON, Protocol Buffers, or even in “handwritten on the knee in 5 minutes”) - out of the box it will fulfill all the conditions you have set up, including LINQ .

I am quite serious. I do not see much point at this stage of the project, and using these conditions to use a full-fledged database will have more minuses than advantages.

the number of lines of code can reach 5000, and the number of fields in tables 30 .. I am afraid of the very existence of a text file of similar dimensions .. besides, fanatical optimization is not included in the plans .. but further support of the project with priority.
@Mr.Modest with proper work with file streams, and 100k entries will not cause a noticeable drop in performance.
@rdorn and yet, experience with the database would be more useful for me)
You can simply use ready-made ones, since under .Net there are more than one hundred of them.

Answer 3 · 2017-01-22T16:43:36

Consider the use of MS SQL Server Compact, this is, in fact, a single-user embedded version of SQL Server, there is full support for the Entity Framework.

Some years it was not updated, but quite to itself a full-fledged working product.

You connect a couple of libraries to your application and you can even run it from a flash drive.

What is the best local database to choose for a simple project in Visual Studio?

3 answers 3

More articles: