SQLite C#实现操作

debug command

C:\1.db

 

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

namespace CSharpConnectionSQLLite
{
    class Dao
    {
        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbDirectory"></param>
        /// <returns></returns>
        public SQLiteConnection GetConnection(string dbDirectory)
        {
            if (!File.Exists(dbDirectory))
            {
                SQLiteConnection.CreateFile(dbDirectory);
            }
            SQLiteConnectionStringBuilder connStr = new SQLiteConnectionStringBuilder();
            connStr.DataSource = dbDirectory;
            connStr.Pooling = true;
            connStr.FailIfMissing = true;
            SQLiteConnection conn = new SQLiteConnection(connStr.ToString());
            return conn;

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="conn"></param>
        public void CloseConnection(SQLiteConnection conn)
        {
            if(conn!= null)
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="createTableSQL"></param>
        /// <param name="conn"></param>
        public void CreateTable(string createTableSQL,SQLiteConnection conn)
        {
            SQLiteCommand cmd = new SQLiteCommand();
            cmd.CommandText = createTableSQL;
            cmd.Connection = conn;
            cmd.ExecuteNonQuery();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="insertSQL"></param>
        /// <param name="conn"></param>
        public void InsertIntoValue(string insertSQL , SQLiteConnection conn)
        {
            SQLiteCommand cmd = new SQLiteCommand();
            cmd.CommandText = insertSQL;
            cmd.Connection = conn;
            cmd.ExecuteNonQuery();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="querySQL"></param>
        /// <param name="conn"></param>
        /// <returns></returns>
        public SQLiteDataReader GetTableValue(string querySQL, SQLiteConnection conn)
        {
            SQLiteCommand cmd = new SQLiteCommand();
            cmd.CommandText = querySQL;
            cmd.Connection = conn;
            SQLiteDataReader reader = cmd.ExecuteReader();
            return reader;
        }
    }
}

 

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

namespace CSharpConnectionSQLLite
{
    class Program
    {
        private static SQLiteConnection conn = null;
        private static string sql = "CREATE TABLE test(username varchar(20),password varchar(20))";
        private static string insertintosql = "INSERT INTO test VALUES('myuser','mypassword')";
        private static string querySql = "SELECT * FROM test";

        static void Main(string[] args)
        {
            Dao dao = new Dao();
            try
            {
                conn = dao.GetConnection(args[0]);
                conn.Open();
                conn.CreateCommand();
                Console.WriteLine("Success");
                //dao.CreateTable(sql, conn);
                dao.InsertIntoValue(insertintosql,conn);
                SQLiteDataReader reader = dao.GetTableValue(querySql, conn);
                while(reader.Read())
                {
                    Console.Write(reader.GetString(0));
                    Console.Write("\t");
                    Console.WriteLine(reader.GetString(1));

                }
            }
            catch(Exception)
            {
                Console.WriteLine("Failed");
            }
            finally
            {
                dao.CloseConnection(conn);
            }
            Console.ReadKey();
        }
    }
}