C# sqlite使用

下载SQLiteStudio https://sqlitestudio.pl/

先创建数据库

新建个文件后缀改为.db(改成其他的也可以如: .sj)

 

打开SQLiteStudio,添加数据库

 

 

 点击连接

 

 

创建表格

 

 

 写入表名  添加字段

 

 添加 int 型主键 ID(注:选择主键后会自增)

 

 创建表格

 

 

 c# 使用数据库

NuGet 添加 System.Data.SQLite

 

 

// 数据库连接字符串
        public static String DBConnStr = $"Data Source={Environment.CurrentDirectory}/data.db;Version=3;";

        // 数据库连接   默认初始化连接
        public SQLiteConnection myConnection = new SQLiteConnection(DBConnStr);

        // 中转仓库  
        public SQLiteDataAdapter DataAdapter = new SQLiteDataAdapter();

        // 存储查询结果
        public DataTable DataTable = new DataTable();

        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="strsql"></param>
        /// <returns></returns>
        public int ExecuteSql(string strsql)
        {
            try
            {
                if (myConnection.State != ConnectionState.Open)
                {
                    // 打开数据库连接
                    myConnection.Open();
                }

                SQLiteCommand myCommand = new SQLiteCommand(strsql, myConnection);
                
                return myCommand.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                MessageBox.Show("执行错误:"+ex.Message);

                return -1;
                //throw ex;
            }
        }

        /// <summary>
        /// 执行查询语句,获取查询结果
        /// </summary>
        /// <param name="strsql"></param>
        public void GetDataTableSql(string strsql)
        {
            try
            {
                if (myConnection.State != ConnectionState.Open)
                {
                    // 打开数据库连接
                    myConnection.Open();
                }

                SQLiteCommand myCommand = new SQLiteCommand(strsql, myConnection);

                DataAdapter.SelectCommand = myCommand;
                if (DataTable != null)
                {
                    DataTable.Dispose();
                    DataTable = new DataTable();
                    
                    //获取执行结果
                    DataAdapter.Fill(DataTable);
                    
                }
                
                return;
            }
            catch (Exception ex)
            {
                MessageBox.Show("查询错误" + ex.Message);
                //throw ex;
            }
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        public void DBClose()
        {
            if (myConnection.State == ConnectionState.Open)
            {
                myConnection.Close();
                myConnection.Dispose();
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            ExecuteSql(textBox1.Text);
        }

        private void button2_Click(object sender, EventArgs e)
        {
            GetDataTableSql(textBox2.Text);
            dataGridView1.DataSource = DataTable;
        }

执行效果