Generating LINQ to SQL Code for SQLite in .NET (C #)

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

Foreword

All our customers had code requirements:

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

Based on the above, for the databases, of course, LINQ to SQL classes related to relational objects were used.

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



')

What to do? Where to run?

Of course, in Google, from which we smoothly run over in stackoverflow! There we have over9000 options:

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

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

Decision

There is nothing left but to look for the “left” code generators for the table. A little googling and weighing all the pros and cons, I stopped at 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 links! This is exactly what was needed, and I began to develop. The first exception: dblinq2007 is written and generates a schema for Framework 2.0, and we use 4.0. We download the source code, open it in the studio, select the 4th version of the framework in the properties of the projects and rebuild dblinq2007!

Code generation

To generate, you must use the executable file dblinq2007, which is located in the compiled binaries directory and is called DbMetal.exe. Let's smoke its docks a bit, and, having looked through the help, we see that it is enough to specify the provider, the connection string and the file name, and DbMetal will generate a DBML file for SQLite:



Generating DBML model:



After generating DBML, we need to generate a CS file (should be named the same as a 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, in which the work with SQLite is implemented.



Now that all the files have been 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, it’s enough to select all the labels in the Server Browser, and drag them into the DBML constructor:



Victory!



In principle, everything. After the done manipulations, we will be able to write LINQ requests 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<Order> OrderList = (from o in dbContext.Order select o).ToList(); //  List<Order> OrderList = dbContext.Order.Take(10).ToList(); //      string OrderStatus = OrderList[0].OrderStatus.Name; //    List<OrderUnit> OrderUnits = OrderList[0].OrderUnit.ToList(); //   List<MenuImages> 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(); } }