C#操作SQLite

1、SQLite介绍

 

 

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

2、C#下调用SQLite数据库

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

 

 

创建SQLiteHelper类 如下：

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DemoSQLite.com.DemoSQLite.Control
{
    public class SQLiteHelper
    {
        ///创建数据库文件
        public static void CreateDBFile(string fileName)
        {
            string path = System.Environment.CurrentDirectory + @"/Data/";
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }
            string databaseFileName = path + fileName;
            if (!File.Exists(databaseFileName))
            {
                SQLiteConnection.CreateFile(databaseFileName);
            }
        }

        //生成连接字符串
        private static string CreateConnectionString()
        {
            SQLiteConnectionStringBuilder connectionString = new SQLiteConnectionStringBuilder();
            connectionString.DataSource = @"data/StudentDB.db";

            string conStr = connectionString.ToString();
            return conStr;
        }

        /// <summary>
        /// 对插入到数据库中的空值进行处理
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        public static object ToDbValue(object value)
        {
            if (value == null)
            {
                return DBNull.Value;
            }
            else
            {
                return value;
            }
        }

        /// <summary>
        /// 对从数据库中读取的空值进行处理
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        public static object FromDbValue(object value)
        {
            if (value == DBNull.Value)
            {
                return null;
            }
            else
            {
                return value;
            }
        }

        /// <summary>
        /// 执行非查询的数据库操作
        /// </summary>
        /// <param name="sqlString">要执行的sql语句</param>
        /// <param name="parameters">参数列表</param>
        /// <returns>返回受影响的条数</returns>
        public static int ExecuteNonQuery(string sqlString, params SQLiteParameter[] parameters)
        {
            string connectionString = CreateConnectionString();
            using (SQLiteConnection conn = new SQLiteConnection(connectionString))
            {
                conn.Open();
                using (SQLiteCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sqlString;
                    foreach (SQLiteParameter parameter in parameters)
                    {
                        cmd.Parameters.Add(parameter);
                    }
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 执行查询并返回查询结果第一行第一列
        /// </summary>
        /// <param name="sqlString">SQL语句</param>
        /// <param name="sqlparams">参数列表</param>
        /// <returns></returns>
        public static object ExecuteScalar(string sqlString, params SQLiteParameter[] parameters)
        {
            string connectionString = CreateConnectionString();
            using (SQLiteConnection conn = new SQLiteConnection(connectionString))
            {
                conn.Open();
                using (SQLiteCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sqlString;
                    foreach (SQLiteParameter parameter in parameters)
                    {
                        cmd.Parameters.Add(parameter);
                    }
                    return cmd.ExecuteScalar();
                }
            }
        }

        /// <summary>
        /// 查询多条数据
        /// </summary>
        /// <param name="sqlString">SQL语句</param>
        /// <param name="parameters">参数列表</param>
        /// <returns>返回查询的数据表</returns>
        public static DataTable GetDataTable(string sqlString, params SQLiteParameter[] parameters)
        {
            string connectionString = CreateConnectionString();
            using (SQLiteConnection conn = new SQLiteConnection(connectionString))
            {
                conn.Open();
                using (SQLiteCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sqlString;
                    foreach (SQLiteParameter parameter in parameters)
                    {
                        cmd.Parameters.Add(parameter);
                    }
                    DataSet ds = new DataSet();
                    SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
                    adapter.Fill(ds);
                    conn.Close();
                    return ds.Tables[0];
                }
            }
        }
    }
}

 

调用方式:

/// <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;
        }