LinqToSqlite, January 1, linqtosqlite

LinqToSqlite, January 1, linqtosqlite

Speak ahead

The main reason for writing these words is that the comrades who use LINQ are aware of the beauty of the text. (as for the beauty of the text, you know, I will not describe it. If you are not familiar with LINQ, there are many articles in the garden). Microsoft's old brother only provides their own SQLServer support-LinqToSql. However, many projects, especially small projects, deploying SQLServer is too troublesome (just download). Sqlite and Access are the choices of many people, but they do not want to write the "Red pass" (the string color in) as a result, Google's SQL statements are designed to serve as an excellent target. We found that Dblinq is commonly used by our partners, but the configuration is a little frustrating for many new users (at least the one who generates objects with the DbMetal.exe command every time ......, I am a little lazy anyway. I don't need to pay for it. (our pockets are a little tight ......).

Enter the subject

This article mainly introduces the use of Linq To DB, which is relatively simple compared with the existing solutions. Let's get started!

1. Open VS, create a project (I created Winform), and save it. The name is random;

2. Install Nuget's Linq to SQLite package, and open the Nuget Package Manager Console (for example, choose "Tools"> "library Package Manager"> "library Package Manager;

What is Nuget?

Without it, we need to reference some open-source libraries at least to do the following:

A. Find the correct Resource (library files and their dependencies );

B. Download the corresponding resources (are there any mistakes or have you ever cheated ?);

C. Add a reference;

For a long time, everyone has been repeating this kind of competition. Finally, a group of people can't stand it. So Nuget was born, and Microsoft certainly won't let it go, so VS was integrated;

3. Enter the command at the PM> prompt: (ensure that the network is smooth. The following command must be downloaded online)
Install-Package linq2db. SQLite

　　

4. After the installation is complete, the dependency items are automatically added: System. Data. SQLite, linq2db, and ling2db. t4models (create the T4 template of Linq to SQLite)
Added several items in solution Resource Manager (does it help you do what you originally wanted to do ?, The world has suddenly become better ),

5. Open the copyme.sqlite.tt.txt file in the linqtodb.templatefolder of the solution Resource Manager,

The red box in the figure generates the following class files in sequence: namespace, database directory, Database Name (change your own namespace, database name directory, Database Name, it is used by my test project, otherwise it cannot continue normally ).

Note: If the database has a password and is created using "SQLite2009 Pro", select the third RSA (Compatible with ADO.. NET Provider). Otherwise, the system will always prompt "File is encrypted or is not a database"

6. Remove and save the. TXT file. A prompt is displayed, indicating whether to execute the file. Click Yes. A class file will be generated in the same directory of this file (used in later programs)

7. Now you can use Linq To Sqlite;

　　

1  var db = new DatabaseHelper1DB(@"Data Source=F:\Yun\VolcanoCloudTest\DatabaseHelper2.db3;Version=3;Password=1");2             var temp =3                 from c in db.Users4                 select c;5             foreach (var c in temp)6                 listBox1.Items.Add(c.Name);

 

But unfortunately, an exception came out, that is, the connection string is not defined. The string type parameter of the DatabaseHelper1DB constructor <public DatabaseHelper1DB (string configuration)> in the original generation class is the Name in the connection string configuration file, not the connection string itself. I don't want to configure the connection string, because most of the time (at least I do) the connection string is dynamically generated by the Code. There are always some methods. Let's take a look at the base class LinqToDB. Data. DataConnection of DatabaseHelper1DB. It has a constructor like this:

1   public DataConnection(IDataProvider dataProvider, string connectionString);

The second parameter is exactly the connection string we want. The problem is how to pass in the first parameter, and the code of the derived class is automatically generated. What should we do? Method 2

A. Modify the generated class file:

1 public DatabaseHelper1DB (string configuration) 2: base (configuration) // The original 3 {4 InitDataContext (); 5} 6 7 public DatabaseHelper1DB (string configuration) 8: base (new LinqToDB. dataProvider. SQLite. SQLiteDataProvider (), configuration) // The modified 9 {10 InitDataContext (); 11}

But there is a problem. Every time the Code is generated, I have to modify it once, so it is not worry-free. (For details, refer to the second method)

B. modify the code generation template. Find the "LinqToDB. ttinclude" file and modify it as follows:

If (GenerateConstructors) {if (DefaultConfiguration = null) DataContextObject. members. add (new Method (null, DataContextObject. name, new string [0], new [] {"InitDataContext ();"}); else DataContextObject. members. add (new Method (null, DataContextObject. name, new string [0], new [] {"InitDataContext ();"}) {AfterSignature = {": base (\" "+ DefaultConfiguration + "\") "}); DataContextObject. members. add (new Method (null, DataContextObject. name, new [] {"string configuration"}, new [] {"InitDataContext ();"}) {AfterSignature = {": base (new LinqToDB. dataProvider. SQLite. SQLiteDataProvider (), configuration) "}); // The above": base (new LinqToDB. dataProvider. SQLite. SQLiteDataProvider (), configuration) is the modified DataContextObject. members. add (new MemberGroup {IsCompact = true, Members = {new Method ("void", "InitDataContext") {AccessModifier = AccessModifier. partial }}});
}

Run again. The exception is gone. (If you feel this is troublesome, or you are used to setting the connection string in the configuration, you only need to add the connection string in the configuration file and input the Name of the configuration item during use );

 

Finished? Slow! Don't be so happy. query is okay, update, delete? DatabaseHelper1DB does not provide the corresponding method. Does this stuff only support queries? So I threw out google and started to get dizzy. I read an article about linq to db from a foreigner, to put it bluntly, the linq to db technology is a kind of linq query solution. My days, query ?!, Is it true that it is not supported. After such a long time, you can only query the data. How can you afford it? Self-implemented? It should not be supported. Foreigners are still meticulous in their work. Provide a library, not just half. So I continued to search and read some blogs in English (That's a pain point, I am not good at English, you know ). Finally, I found out that the extension method is used to update and delete the data. The extension method is in another namespace (LinqToDB, it is not the namespace LinToSqlite of the DatabaseHelper1DB class.

1 using LinqToDB;

Insert:

1             User uNew = new User();2             uNew.Name = "test";3             uNew.Password = "11";4             db.Insert(uNew);

Update:

1  using (var db = new DatabaseHelper1DB(@"Data Source=F:\Yun\VolcanoCloudTest\DatabaseHelper2.db3;Version=3;Password=1"))2             {3                 db.Users4                   .Where(u => u.ID == 1)5                   .Set(p => p.Name,"test")6                   .Update();7             }

Delete:

1             using (var db = new DatabaseHelper1DB(@"Data Source=F:\Yun\VolcanoCloudTest\DatabaseHelper2.db3;Version=3;Password=1"))2             {3                 db.Users4                   .Where(u => u.ID == 1)5                   .Delete();6             }

　　

In addition, linqtodb also supports access (as many comrades are looking for linq to access, and some users are still charged, which may be a boon). The usage of more than 10 databases is similar, this article has been very long (I like short articles for easy reading), so I will not repeat them here. If you want to use it, let's do it by yourself.

Last

This is my first article in the garden. I hope you can correct me more. If you can help me, I am very happy. January 6, 2015 in the library.