推荐文章

public sealed partial class MainPage : Page
    {
        public MainPage()
        {
            this.InitializeComponent();

            using (SQLiteConnection connection = CreateDbConnection("pb2.sdf"))
            {
                //创建表
                CreateTable(connection, "user");
                //插入数据
                InsertRow(connection, "PB2.CN", "user");
                //更新数据
                UpdateRow(connection, "www.PB2.CN", "PB2.CN", "user");
                //删除数据
                DeleteRow(connection, "www.PB2.CN", "user");
            }

        }
        /// <summary>
        /// 创建链接
        /// </summary>
        /// <param name="_dbName"></param>
        /// <returns></returns>
        private SQLiteConnection CreateDbConnection(string _dbName)
        {
            //创建连接
            SQLiteConnection connection = new SQLiteConnection(_dbName);
            if (null == connection)
            {
                throw new Exception("create db connection failed");
            }
            return connection;
        }
        /// <summary>
        /// 新建表
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="_tableName"></param>
        private void CreateTable(SQLiteConnection connection,string _tableName)
        {
            //创建表
            string sql = string.Format("create table if not exists {0} (id integer primary key autoincrement,name text)", _tableName);
            using (ISQLiteStatement sqliteStatement = connection.Prepare(sql))
            {
                //执行语句
                sqliteStatement.Step();
            }
        }
        /// <summary>
        /// 插入数据
        /// </summary>
        /// <param name="connection">链接</param>
        /// <param name="name">字段值</param>
        /// <param name="_tableName">表名</param>
        private void InsertRow(SQLiteConnection connection, string name, string _tableName)
        {
            //插入数据
            string sql = string.Format("insert into {0} (name) values (?)", _tableName);
            using (ISQLiteStatement sqliteStatement = connection.Prepare(sql))
            {
                //绑定参数
                sqliteStatement.Bind(1, name);
                //执行语句
                sqliteStatement.Step();
            }
        }
        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="connection">链接</param>
        /// <param name="newName">新值</param>
        /// <param name="oldName">旧值</param>
        /// <param name="_tableName">表名</param>
        private void UpdateRow(SQLiteConnection connection, string newName, string oldName,  string _tableName)
        {
            string sql = string.Format("update {0} set name = ? where name = ?", _tableName);
            using (ISQLiteStatement sqliteStatement = connection.Prepare(sql))
            {
                //绑定参数
                sqliteStatement.Bind(1, newName);
                sqliteStatement.Bind(2, oldName);
                //执行语句
                sqliteStatement.Step();
            }
        }
        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="connection">链接</param>
        /// <param name="name">值</param>
        /// <param name="_tableName">表名</param>
        private void DeleteRow(SQLiteConnection connection, string name , string _tableName)
        {
            string sql = string.Format("delete from {0} where name = ?", _tableName);
            using (ISQLiteStatement sqliteStatement = connection.Prepare(sql))
            {
                //绑定参数
                sqliteStatement.Bind(1, name);
                //执行语句
                sqliteStatement.Step();
            }
        }
    }