SQLite基础篇之建库,建表,增,删,改,查(C#)

 准备工作:

1,下载SqlLite管理工具sqliteadmin(管理工具很多，推荐这个)

2,SqlLite for .Net(C#开发用)

3,引用命名空间：using System.Data.SQLite;

以上包需要可以提供

建库建表代码:

          //创建一个数据库文件
            string datasource = "E:/test.db";
          System.Data.SQLite.SQLiteConnection.CreateFile(datasource);
          //连接数据库
            System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection();
          System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
          connstr.DataSource = datasource;
          connstr.Password = "admin";//设置密码，SQLite ADO.NET实现了数据库密码保护
            conn.ConnectionString = connstr.ToString();
          conn.Open();
          //创建表
            System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
          string sql = "CREATE TABLE test(username varchar(20),password varchar(20))";
          cmd.CommandText = sql;
          cmd.Connection = conn;
          cmd.ExecuteNonQuery();

insert代码:

//打开连接

        public bool openConn()
        {
            bool init = false;
            try
            {
                conn = new System.Data.SQLite.SQLiteConnection("Data Source=" + datasource);
                System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
                connstr.DataSource = datasource;
                conn.ConnectionString = connstr.ToString();
                conn.Open();
                init = true;
            }
            catch (Exception ex)
            {
                return false;
            }
            return init;
        }

//执行Sql

       public void ExcuteSql(string sql)
        {
            if (openConn())
            {
                System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
                cmd.CommandText = sql;
                cmd.Connection = conn;
                cmd.ExecuteNonQuery();
                conn.Close();
                conn.Dispose();
            }

        }

查询：

            string datasource = "D:/people.s3db";
            System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("Data Source=" + datasource);
            System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
            connstr.DataSource = datasource;
            //connstr.Password = "admin";有密码可用
            conn.ConnectionString = connstr.ToString();
            conn.Open();
            System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
            string sql = "SELECT * FROM person";
            cmd.CommandText = sql;
            cmd.Connection = conn;
            System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader();
            StringBuilder sb = new StringBuilder();
            string name = "";
            List<string> l = new List<string>();
            while (reader.Read())
            {
                name = reader.GetString(1);
                l.Add(name);
            }