Windows Phone 7.1本地数据库操作 SQLCE 增/删/改/查

Windows Phone从7.1开始支持本地数据库,是SQL CE,但是不支持直接使用SQL语句操作,需要用到Linq.因为网上例子比较少,而且都使用MVVM模式,对于我这个学C#没几天的人来说,太复杂了。折腾了一天,总算是搞定,上范例截图:

稍微解说一下,首先需要添加引用System.Data.Linq,再针对你的表定义一个类.我的表名为train,有三个字段,分别为id,train_no,favorite,定义如下:


 [Table]
    public class train : INotifyPropertyChanged, INotifyPropertyChanging
    {
        //类名与表名相同
        //给表定义一个类,包括所有的字段
        private int id;
        //字段名
        [Column(IsPrimaryKey = true, CanBeNull = false, IsDbGenerated = true, DbType = "INT NOT NULL Identity", AutoSync = AutoSync.OnInsert)]
        //主键,不为空,自动生成,int型不为空,仅在插入时返回值
        public int ID
        {
            get
            {
                return id;
            }
            set
            {
                if (id != value)
                {
                    //修改发送通知
                    NotifyPropertyChanging("ID");
                    id = value;
                    NotifyPropertyChanged("ID");
                }

            }
        }

        private string train_no;
        [Column]
        public string Train_no
        {
            get
            {
                return train_no;
            }
            set
            {
                NotifyPropertyChanging("Train_no");
                train_no = value;
                NotifyPropertyChanged("Train_no");
            }
        }

        private int favorite;
        [Column (DbType = "INT NOT NULL")]
        public int Favorite
        {
            get
            {
                return favorite;
            }
            set
            {
                NotifyPropertyChanging("Favorite");
                favorite = value;
                NotifyPropertyChanged("Favorite");
            }
        }


        public event PropertyChangedEventHandler PropertyChanged;

        private void NotifyPropertyChanged(string propertyName)
        {
            if (PropertyChanged != null)
            {
                PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
            }
        }


        //通知修改
        public event PropertyChangingEventHandler PropertyChanging;

        private void NotifyPropertyChanging(string propertyName)
        {
            if (PropertyChanging != null)
            {
                PropertyChanging(this, new PropertyChangingEventArgs(propertyName));
            }
        }

       
        public override string ToString()
        {
            return this.ID.ToString() + "," + this.Train_no + " ," + this.Favorite ;
        }
    }

再定义一个继承自 DataContext的DBContext类用于操作数据库:


    public class DBContext : DataContext
    {
        private const string DBPath = "Data Source=isostore:/db.sdf";
        //定义数据库路径
        public Table Train;
        //定义train表
        public DBContext(): base(DBPath)
        {
            //直接调用父类的构造方法
        }
        
    }

下面就是程序中的增删改查了:


namespace LocalDB
{
    public partial class MainPage : PhoneApplicationPage
    {
        // Constructor
        DBContext db;
        public ObservableCollection Items { get; private set; }
        public MainPage()
        {
            InitializeComponent();
        }

        private void LayoutRoot_Loaded(object sender, RoutedEventArgs e)
        {
            //页面载入时初始化
            db = new DBContext();
            if (!db.DatabaseExists())
                db.CreateDatabase();
            //数据库不存在就创建数据库


        }
        private void button1_Click(object sender, RoutedEventArgs e)
        {
            //增
            train t = new train();
            t.Train_no = "aaasf";
            t.Favorite = 1;
            db.Train.InsertOnSubmit(t);
            db.SubmitChanges();
            //提交修改
        }

        private void button2_Click(object sender, RoutedEventArgs e)
        {
            //查
            var rows = from z in db.Train where z.ID>0&&z.ID<20 select z;
            //相当于select * from train,限定条件参考删、改
            textBlock1.Text = "";
            foreach (train t in rows)
            {
                textBlock1.Text += t.Train_no + "\n";
            }
        }


        private void button3_Click(object sender, RoutedEventArgs e)
        {
            //改
            var rows = from z in db.Train where z.Train_no == "aaasf" select z;
            //相当于select * from train where train_no="aaasf";
            //选出对象,再修改,没有update方法
            foreach (train mt in rows)
            {
                mt.Favorite = 1;
                mt.Train_no = "fsa";

            }
            db.SubmitChanges();


        }

        private void button4_Click(object sender, RoutedEventArgs e)
        {
            //删
            var rows = from z in db.Train where z.Train_no == "fsa" select z;
            //相当于select * from train where train_no="fsa"
            //先选出对象,再删除没有直接的delect方法
            foreach (train mt in rows)
            {
                db.Train.DeleteOnSubmit(mt);
            }
            db.SubmitChanges();
        }
    }
}

最后附上打包的源代码:
[download id="29"]

© 2011, 冰冻鱼. 请尊重作者劳动成果,复制转载保留本站链接! 应用开发笔记