System.Data.SQLite

作者:追风剑情 发布于:2018-4-26 14:40 分类:C#

1. 下载System.Data.SQLite.dll并添加到项目中

下载地址: http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki

22222.jpg3333.jpg

示例


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

namespace SqliteTest
{
    public class SqliteHelper
    {
        private SQLiteConnection dbConnection;
        private SQLiteCommand dbCommand;
        private SQLiteDataReader dataReader;

        /// <summary>
        /// 连接数据库
        /// </summary>
        /// <param name="connectionString">例如: "data source=mydb.db"</param>
        public bool Connect(string db_path)
        {
            try
            {
                //如果db文件不存在,会自动创建一个新的db文件
                string connectionString = string.Format("data source={0}", db_path);
                dbConnection = new SQLiteConnection(connectionString);
                dbConnection.Open();
                return true;
            }
            catch (Exception e)
            {
                Log(e.ToString());
            }
            return false;
        }

        //执行SQL: 查
        public SQLiteDataReader ExecuteQuery(string sql)
        {
            try
            {
                dbCommand = dbConnection.CreateCommand();
                dbCommand.CommandText = sql;
                dataReader = dbCommand.ExecuteReader();
            }
            catch (Exception e)
            {
                Log(e.Message);
            }

            return dataReader;
        }

        //执行SQL:增、删、改操作
        public int ExecuteNonQuery(string sql)
        {
            int result_code = -1;
            try
            {
                dbCommand = dbConnection.CreateCommand();
                dbCommand.CommandText = sql;
                result_code = dbCommand.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Log(e.Message);
            }

            return result_code;
        }

        public void Close()
        {
            if (dbCommand != null)
            {
                dbCommand.Cancel();
            }
            dbCommand = null;

            if (dataReader != null)
            {
                dataReader.Close();
            }
            dataReader = null;

            if (dbConnection != null)
            {
                dbConnection.Close();
            }
            dbConnection = null;
        }

        static void Log(string s)
        {
            Console.WriteLine("SqliteHelper: {0}", s);
        }
    }
}


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

namespace SqliteTest
{
    class Program
    {
        static void Main(string[] args)
        {
            SqliteHelper sqlite = new SqliteHelper();
            bool success = sqlite.Connect(@"D:\ssss\trunk\client\.svn\wc.db");
            Console.WriteLine("Connect: {0}", success);
            SQLiteDataReader reader = sqlite.ExecuteQuery(@"select rowid, local_relpath from NODES order by rowid ASC limit 0,4");
            Console.WriteLine("FieldCount: {0}", reader.FieldCount);
            Console.WriteLine("VisibleFieldCount: {0}", reader.VisibleFieldCount);
            Console.WriteLine("StepCount: {0}", reader.StepCount);
            Console.WriteLine("HasRows: {0}", reader.HasRows);

            /*StringBuilder sb = new StringBuilder();
            for (int i = 0; i < reader.FieldCount; i++)
            {
                //reader.GetDataTypeName(i);//获取列数据类型
                //reader.GetName(i);//获取列名称
                //sb.Append(reader.GetName(i) + ",");
            }
            Console.WriteLine(sb.ToString());*/

            while(reader.Read())
            {
                //按列名读取每一行数据
                Console.WriteLine("{0}, {1}",
                    reader.GetInt32(reader.GetOrdinal("rowid")),
                    reader.GetString(reader.GetOrdinal("local_relpath")));
            }
            Console.Read();
        }
    }
}


运行测试

1111.jpg


开发中可能会遇到的报错:

“System.BadImageFormatException”类型的第一次机会异常在 System.Data.SQLite.dll 中发生
SqliteHelper: System.BadImageFormatException: 试图加载格式不正确的程序。 (异常来自 HRESULT:0x8007000B)
   在 System.Data.SQLite.UnsafeNativeMethods.sqlite3_config_none(SQLiteConfigOpsEnum op)
   在 System.Data.SQLite.SQLite3.StaticIsInitialized() 位置 c:\dev\sqlite\dotnet\System.Data.SQLite\SQLite3.cs:行号 3931
   在 System.Data.SQLite.SQLiteLog.Initialize() 位置 c:\dev\sqlite\dotnet\System.Data.SQLite\SQLiteLog.cs:行号 141
   在 System.Data.SQLite.SQLiteConnection..ctor(String connectionString, Boolean parseViaFramework) 位置 c:\dev\sqlite\dotnet\System.Data.SQLite\SQLiteConnection.cs:行号 1722
   在 System.Data.SQLite.SQLiteConnection..ctor(String connectionString) 位置 c:\dev\sqlite\dotnet\System.Data.SQLite\SQLiteConnection.cs:行号 1659
   
原因:当前所使用的.NET Framework版本与System.Data.SQLite所使用的版本不一致
属性->应用程序->目标框架

1111.png


示例二:将数据库中的数据显示到控件中


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.IO;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SQLite;

namespace Test7
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
            Init();
        }

        private void Init()
        {
            string db_path = Environment.CurrentDirectory + "\\info.db";
            Console.WriteLine(db_path);

            SqliteHelper db = new SqliteHelper();
            bool success = db.Connect(db_path);
            if (!success)
            {
                MessageBox.Show("连接数据库失败!");
                return;
            }

            Console.WriteLine("Connect: {0}", success);

            /*
            string sql_create_table = "CREATE TABLE IF NOT EXISTS user_info(row_id INTEGER PRIMARY KEY AUTOINCREMENT, sex INT, name TEXT, reg_date CURRENT_TIMESTAMP)";
            int result_code = db.ExecuteNonQuery(sql_create_table);
            Console.WriteLine("result_code: {0}", result_code);

            string sql_insert = @"INSERT INTO user_info(sex, name, reg_date) VALUES(0, '明明', CURRENT_TIMESTAMP)";
            result_code = db.ExecuteNonQuery(sql_insert);
            Console.WriteLine("result_code: {0}", result_code);
             * */

            string sql_query = "SELECT * FROM 'user_info'";
            SQLiteDataReader reader = db.ExecuteQuery(sql_query);
            while(reader.Read())
            {
                //逐行读取数据库中记录
                int row_id = reader.GetInt32(reader.GetOrdinal("row_id"));
                int sex = reader.GetInt32(reader.GetOrdinal("sex"));
                string name = reader.GetString(reader.GetOrdinal("name"));
                string reg_date = reader.GetString(reader.GetOrdinal("reg_date"));
                //将数据插入DataGridView控件中
                int index = this.dataGridView1.Rows.Add();
                this.dataGridView1.Rows[index].Cells[0].Value = row_id;
                this.dataGridView1.Rows[index].Cells[1].Value = sex;
                this.dataGridView1.Rows[index].Cells[2].Value = name;
                this.dataGridView1.Rows[index].Cells[3].Value = reg_date;
            }
            //设置列标题居中显示
            this.dataGridView1.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
        }
    }
}


运行测试

1111.png

更多示例:

DataGridView显示数据库数据


标签: C#

Powered by emlog  蜀ICP备18021003号   sitemap

川公网安备 51019002001593号