LINQ to SQL code generation for SQLite in .NET (C #)

I rarely write C #, and basically all of our applications and services connect to a data source using an MSSQL server or database services. And now it is time to write an application using not a server, but a local database. A little google, I suddenly chose SQLite.

Foreword

All of our customers had code requirements:

• established variable naming;
• code grouping (constructors, variables, methods, events ...);
• tabs and formatting (punishable by death);
• for self-written SQL queries, the developer was removed from the team.

Based on the foregoing, LINQ to SQL classes associated with relational objects were of course used for databases.

Naturally, in my desktop application, I immediately downloaded and installed the SQLite provider , created a database, all the tablets, links, created the LINQ to SQL class, dragged all objects from the browser into it, and ...

What to do? Where to run?

Of course, google, from which we smoothly run into stackoverflow! There we have over9000 options:

• yes, let's write everything from the beginning (SQLiteConnection, SQLiteCommand, SQLiteDataReader, SQLiteDataRecord, etc.):
• let's write queries manually;
• let's change the whole project when changing or adding one field to the table;
• relations between tables for weaklings, we will use a DataTable and work with it on column indices ...

No no and one more time no. A data class must work with data, which is responsible for connecting to the database, fetching, inserting, updating and deleting records (DataContext), and the developer must use objects named in the same way as relational objects and their properties!

Decision

There is nothing left to do but to look for “left” code generators for the table. Googling a little and having weighed all the pros and cons, I settled on dblinq2007 .

As it turned out later, it can generate not just a source file, but a studio LINQ to SQL file with our database schema and connections! This is exactly what was needed, and I started to develop. The first exception: dblinq2007 is written and generates a scheme for Framework 2.0, and we use 4.0. We download the sources, open in the studio, in the project properties, select the 4th version of the framework and rebuild dblinq2007!

Code generation

To generate it, you need to use the dblinq2007 executable file, which lies in the compiled binaries directory and is called DbMetal.exe. Let's smoke its docks a bit, and, looking at the help, we can just specify the provider, connection string and file name, and DbMetal will generate a DBML file for us for SQLite:



Generate a DBML model:



After generating the DBML, we need to generate a CS file (should be called like this same as the DBML file), in which the structure of our database with all the links will be described. The main generated class Main is inherited from the DataContext class , which implements work with SQLite.



Now that all the files are generated, copy them to the directory of our project and add the DBML file to the project. To see that the studio is now working with SQLite, just select all the plates in the Server Browser and drag them into the DBML constructor:



Victory!



In principle, that's all. After the done manipulations, we will be able to write LINQ queries to our database and objects with all connections will be returned to us.

public class Test
{
        private void Example()
        {
            //берем из конфига строку подключения и подключаемся к БД
            SQLiteConnection Connection = new SQLiteConnection(Properties.Settings.Default.connectionString);
            Connection.Open();
            //тот самый DatabaseContext, через который мы работаем с БД
            Main dbContext = new Main(Connection, new SqliteVendor());
            //получаем данные
            List OrderList = (from o in dbContext.Order
                            select o).ToList();
            //или так
            List OrderList = dbContext.Order.Take(10).ToList();
            //получаем статус заказа из связанной таблицы
            string OrderStatus = OrderList[0].OrderStatus.Name;
            //получаем все позиции заказа
            List OrderUnits = OrderList[0].OrderUnit.ToList();
            //получаем изображения позиции
            List UnitImages = OrderList[0].OrderUnit[0].Menu.MenuImages.ToList();
            //вставляем данные
            Order ord = new Order()
            {
                OrderNumber = 1,
                ToTime = DateTime.Now
            };
            dbContext.Order.InsertOnSubmit(ord);
            dbContext.SubmitChanges();
            //Удаляем данные
            dbContext.Order.DeleteOnSubmit(OrderList[0]);
            dbContext.SubmitChanges();
            //закрываем подключение
            Connection.Close();
        }
}