1、SQLite介绍

SQLite，是一款轻型的数据库，是遵守的ACID关系型数据库管理系统，它包含在一个相对小的C库中。它的设计目标嵌入式是的，而且已经在很多中使用了它，它占用资源非常的低，在嵌入式设备中，可能只需要几百K的内存就够了。它能够支持Windows/Linux/Unix等等主流的操作系统，同时能够跟很多程序语言相结合，比如 Tcl、C#、PHP、Java等。

2、C#下调用SQLite数据库

在NuGet程序包内，搜索System.Data.Sqlite,安装Sqlite类库

创建SQLiteHelper类 如下：

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Data;
  4 using System.Data.SQLite;
  5 using System.IO;
  6 using System.Linq;
  7 using System.Text;
  8 using System.Threading.Tasks;
  9 
 10 namespace DemoSQLite.com.DemoSQLite.Control
 11 {
 12     public class SQLiteHelper
 13     {
 14         ///创建数据库文件
 15         public static void CreateDBFile(string fileName)
 16         {
 17             string path = System.Environment.CurrentDirectory + @"/Data/";
 18             if (!Directory.Exists(path))
 19             {
 20                 Directory.CreateDirectory(path);
 21             }
 22             string databaseFileName = path + fileName;
 23             if (!File.Exists(databaseFileName))
 24             {
 25                 SQLiteConnection.CreateFile(databaseFileName);
 26             }
 27         }
 28 
 29         //生成连接字符串
 30         private static string CreateConnectionString()
 31         {
 32             SQLiteConnectionStringBuilder connectionString = new SQLiteConnectionStringBuilder();
 33             connectionString.DataSource = @"data/StudentDB.db";
 34 
 35             string conStr = connectionString.ToString();
 36             return conStr;
 37         }
 38 
 39         /// <summary>
 40         /// 对插入到数据库中的空值进行处理
 41         /// </summary>
 42         /// <param name="value"></param>
 43         /// <returns></returns>
 44         public static object ToDbValue(object value)
 45         {
 46             if (value == null)
 47             {
 48                 return DBNull.Value;
 49             }
 50             else
 51             {
 52                 return value;
 53             }
 54         }
 55 
 56         /// <summary>
 57         /// 对从数据库中读取的空值进行处理
 58         /// </summary>
 59         /// <param name="value"></param>
 60         /// <returns></returns>
 61         public static object FromDbValue(object value)
 62         {
 63             if (value == DBNull.Value)
 64             {
 65                 return null;
 66             }
 67             else
 68             {
 69                 return value;
 70             }
 71         }
 72 
 73         /// <summary>
 74         /// 执行非查询的数据库操作
 75         /// </summary>
 76         /// <param name="sqlString">要执行的sql语句</param>
 77         /// <param name="parameters">参数列表</param>
 78         /// <returns>返回受影响的条数</returns>
 79         public static int ExecuteNonQuery(string sqlString, params SQLiteParameter[] parameters)
 80         {
 81             string connectionString = CreateConnectionString();
 82             using (SQLiteConnection conn = new SQLiteConnection(connectionString))
 83             {
 84                 conn.Open();
 85                 using (SQLiteCommand cmd = conn.CreateCommand())
 86                 {
 87                     cmd.CommandText = sqlString;
 88                     foreach (SQLiteParameter parameter in parameters)
 89                     {
 90                         cmd.Parameters.Add(parameter);
 91                     }
 92                     return cmd.ExecuteNonQuery();
 93                 }
 94             }
 95         }
 96 
 97         /// <summary>
 98         /// 执行查询并返回查询结果第一行第一列
 99         /// </summary>
100         /// <param name="sqlString">SQL语句</param>
101         /// <param name="sqlparams">参数列表</param>
102         /// <returns></returns>
103         public static object ExecuteScalar(string sqlString, params SQLiteParameter[] parameters)
104         {
105             string connectionString = CreateConnectionString();
106             using (SQLiteConnection conn = new SQLiteConnection(connectionString))
107             {
108                 conn.Open();
109                 using (SQLiteCommand cmd = conn.CreateCommand())
110                 {
111                     cmd.CommandText = sqlString;
112                     foreach (SQLiteParameter parameter in parameters)
113                     {
114                         cmd.Parameters.Add(parameter);
115                     }
116                     return cmd.ExecuteScalar();
117                 }
118             }
119         }
120 
121         /// <summary>
122         /// 查询多条数据
123         /// </summary>
124         /// <param name="sqlString">SQL语句</param>
125         /// <param name="parameters">参数列表</param>
126         /// <returns>返回查询的数据表</returns>
127         public static DataTable GetDataTable(string sqlString, params SQLiteParameter[] parameters)
128         {
129             string connectionString = CreateConnectionString();
130             using (SQLiteConnection conn = new SQLiteConnection(connectionString))
131             {
132                 conn.Open();
133                 using (SQLiteCommand cmd = conn.CreateCommand())
134                 {
135                     cmd.CommandText = sqlString;
136                     foreach (SQLiteParameter parameter in parameters)
137                     {
138                         cmd.Parameters.Add(parameter);
139                     }
140                     DataSet ds = new DataSet();
141                     SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
142                     adapter.Fill(ds);
143                     conn.Close();
144                     return ds.Tables[0];
145                 }
146             }
147         }
148     }
149 }

调用方式:

/// <summary>
        /// 查询所有年级
        /// </summary>
        /// <returns></returns>
        public List<GradeEty> GetAllGrade()
        {
            List<GradeEty> gradeEties = new List<GradeEty>();

            SQLiteHelper sQLiteHelper = new SQLiteHelper();
            string sqlQuery = "SELECT * FROM 'Grade';";
            SQLiteParameter[] parameters = new SQLiteParameter[]
             {
                    //new SQLiteParameter("@IMG_ID", DbType.Int32,4),
                    //new SQLiteParameter("@USER_ID", DbType.String),
             };
            //parameters[0].Value = model.IMG_ID;
            //parameters[1].Value = model.USER_ID;

            DataTable dt = SQLiteHelper.GetDataTable(sqlQuery, parameters);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                string Id = dt.Rows[i]["ID"].ToString();
                string GradeName = dt.Rows[i]["GradeName"].ToString();
            

                GradeEty clientEty = new GradeEty(Id, GradeName);
                gradeEties.Add(clientEty);
            }
            return gradeEties;
        }