MFC SQLite on Visual Studio with NuGet and Easy Instructions

2016.08.11 17:01

졸리운_곰 조회 수:329

SQLite on Visual Studio with NuGet and Easy Instructions

My most popular blog post ever was entitled SQLite on Visual Studio 2010 Setup Instructions which I wrote nearly 18 months ago. A lot has changed since then but the page is still as popular as ever. Here is the replacement for that antiquated post.

I’m now using Visual Studio 2012 at home. I’m still using Visual Studio 2010 at work. I have tried the latest x86/x64 SQLite NuGet package on both and it works very well. So forget about my post from 18 months ago and read on.

If you don’t have the NuGet Package Manager installed, go get it and get it installed.

For testing purposes, create a simple console application. If you’re like me, you’ll change the project platform target so that it will build for Any CPU. And if you’re using VS 2012, you’ll uncheck the “Prefer 32-bit” checkbox so that your console app will run as an x64 app in your environment. Unless, of course, if you are running in an x86 OS, you will want to stick with that. (As an aside, if you are still running in 32-bit mode, I recommend you upgrade.)

Now comes the fun part. Illustrated and very easy instructions. Don’t worry, we’ll get to some code later on.

First, right click on your project in the Solution Explorer and select Manage NuGet Packages…

sqlite_nuget_01

In the Package Manager dialog, click the “Online” tab on the left and then search for “sqlite”. You’ll see something like this:

sqlite_nuget_02

경축! 아무것도 안하여 에스천사게임즈가 새로운 모습으로 재오픈 하였습니다.
어린이용이며, 설치가 필요없는 브라우저 게임입니다.
https://s1004games.com

Select the (x86/x64) package for maximum flexibility. You will end up with two folders in your project with the appropriate interop assemblies in each. Those files are marked as “Content” and “Copy always” in your project. In this way, you no longer have to worry about which platform your application is running on. Here’s what it looks like:

sqlite_nuget_03

Save and build. You are now ready for some real code. Yes. It is that easy. So much easier than 18 months ago.

Now here’s the test code. This code shows you two approaches in Test1() and Test2() methods. Test1 uses the SQLiteFactory to create a standard System.Data.Common.DbConnection object so that you can plug in your standard ADO.NET code. Test2 uses the System.Data.SQLite namespace and its SQLiteConnection, SQLiteCommand, SQLiteParameter and SQLiteDataReader classes.

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144     
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
 
namespace SQLiteExperiment
{
  class Program
  {
    static void Main(string[] args)
    {
      var dbFile = @"D:\SQLData\SQLite\test.db";
      if (File.Exists(dbFile)) File.Delete(dbFile);
 
      var connString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", dbFile);
 
      //test using System.Data.Common and SQLiteFactory
      Test1(connString);
 
      if (File.Exists(dbFile))
        Console.WriteLine("Test1 succeeds");
      else
        Console.WriteLine("Test1 fails.");
 
      //prepare for test 2 using SQLite
      //Note: If Pooling=true in connection string the
      //      File.Delete will fail with IOException - The process cannot
      //      access the file because it is being used by another process.
      if (File.Exists(dbFile)) File.Delete(dbFile);
 
       
      Console.WriteLine(string.Empty);
 
      //test using System.Data.SQLite namespace including SQLiteConnection
      Test2(connString);
 
      if (File.Exists(dbFile))
        Console.WriteLine("Test2 succeeds");
      else
        Console.WriteLine("Test2 fails.");
 
      Console.ReadLine(); //prevent close while in debug
    }
 
    static void Test1(string connString)
    {
      Console.WriteLine("Begin Test1");
      using (var factory = new System.Data.SQLite.SQLiteFactory())
      using (System.Data.Common.DbConnection dbConn = factory.CreateConnection())
      {
        dbConn.ConnectionString = connString;
        dbConn.Open();
        using (System.Data.Common.DbCommand cmd = dbConn.CreateCommand())
        {
          //create table
          cmd.CommandText = @"CREATE TABLE IF NOT EXISTS T1 (ID integer primary key, T text);";
          cmd.ExecuteNonQuery();
 
          //parameterized insert
          cmd.CommandText = @"INSERT INTO T1 (ID,T) VALUES(@id,@t)";
           
          var p1 = cmd.CreateParameter();
          p1.ParameterName = "@id";
          p1.Value = 1;
 
          var p2 = cmd.CreateParameter();
          p2.ParameterName = "@t";
          p2.Value = "test1";
 
          cmd.Parameters.Add(p1);
          cmd.Parameters.Add(p2);
 
          cmd.ExecuteNonQuery();
 
          //read from the table
          cmd.CommandText = @"SELECT ID, T FROM T1";
          using (System.Data.Common.DbDataReader reader = cmd.ExecuteReader())
          {
            while (reader.Read())
            {
              long id = reader.GetInt64(0);
              string t = reader.GetString(1);
              Console.WriteLine("record read as id: {0} t: {1}", id, t);
            }
          }
          cmd.Dispose();
        }
        if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
        dbConn.Dispose();
        factory.Dispose();
      }
      Console.WriteLine("End Test1");
    }
 
    private static void Test2(string connString)
    {
      Console.WriteLine("Begin Test2");
 
      using (var dbConn = new System.Data.SQLite.SQLiteConnection(connString))
      {
        dbConn.Open();
        using (System.Data.SQLite.SQLiteCommand cmd = dbConn.CreateCommand())
        {
          //create table
          cmd.CommandText = @"CREATE TABLE IF NOT EXISTS T1 (ID integer primary key, T text);";
          cmd.ExecuteNonQuery();
 
          //parameterized insert - more flexibility on parameter creation
          cmd.CommandText = @"INSERT INTO T1 (ID,T) VALUES(@id,@t)";
 
          cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
            {
              ParameterName = "@id",
              Value = 1
            });
 
          cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
          {
            ParameterName = "@t",
            Value = "test2"
          });
 
          cmd.ExecuteNonQuery();
 
          //read from the table
          cmd.CommandText = @"SELECT ID, T FROM T1";
          using (System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader())
          {
            while (reader.Read())
            {
              long id = reader.GetInt64(0);
              string t = reader.GetString(1);
              Console.WriteLine("record read as id: {0} t: {1}", id, t);
            }
          }
        }
        if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
      }
      Console.WriteLine("End Test2");
    }
  }
}

This code is ultra simplistic but illustrates exactly how easy it is use to use SQLite after “installing” the NuGet package. I have not tried to use a visual designer with this package. I’ll leave that discussion to another day.

 

[출처] http://www.tsjensen.com/blog/post/2012/11/10/SQLite-on-Visual-Studio-with-NuGet-and-Easy-Instructions

 

본 웹사이트는 광고를 포함하고 있습니다.
광고 클릭에서 발생하는 수익금은 모두 웹사이트 서버의 유지 및 관리, 그리고 기술 콘텐츠 향상을 위해 쓰여집니다.
이 게시물을
목록
목록
쓰기
태그
대표 김성준 주소 : 경기 용인 분당수지 U타워 등록번호 : 142-07-27414
통신판매업 신고 : 제2012-용인수지-0185호 출판업 신고 : 수지구청 제 123호 개인정보보호최고책임자 : 김성준 sjkim70@stechstar.com
대표전화 : 010-4589-2193 [fax] 02-6280-1294 COPYRIGHT(C) stechstar.com ALL RIGHTS RESERVED