在Windows Phone中,使用本地化存储有三种选择: IsolatedStorageSettings, IsolatedStorageFile 以及LINQ to SQL。如下图所示:

image

本文将演示如何在Windows Phone中使用LINQ to SQL操作本地数据库(SQL Server Compact Database)。

数据库连接字符串

数据库连接字符串请参考Local Database Connection Strings for Windows Phone。数据库连接字符串的例子:"Data Source = 'isostore:/mydb.sdf'; File Mode = read only;Culture Identifier = zh-CN; Max Database Size = 256; Max Buffer Size = 1024;Password=‘123’;Case Sensitive = true");”。

关于数据库连接字符串应注意以下几点:

  1. 数据库存储可包含在程序安装文件夹中或者独立存储中,分别对应"isostore:/”与"appdata:/”;如果使用appdata那么数据的模式应指明为read only。
  2. 数据库加密可以使用Password参数,数据库创建后不能加密。
  3. 可以通过Culture Identifier参数指定特定的Culture,运用于已经存在的数据库此参数将被忽略。
  4. 可以通过Case Sensitive指定是否区分大小写,运用于已经存在的数据库时,此参数将被忽略。

创建实体类Article代表数据库中的表Article

其包含三个字段,自增长的ArticleID,不为空的ArticleTitle以及添加的日期AddDate;代码如下:

namespace LinqToSqlSample
{
    [Table]
    public class Article : INotifyPropertyChanged, INotifyPropertyChanging
    {

        private int _articleID;
        /// <summary>
        /// Index of artcile ID, primary key
        /// </summary>
        [Column(AutoSync = AutoSync.OnInsert, DbType = "INT NOT NULL Identity", CanBeNull = false, IsDbGenerated = true, IsPrimaryKey = true)]
        public int ArticleID
        {
            get { return _articleID; }
            set
            {
                if (_articleID != value)
                {
                    NotifyPropertyChanging("ArticleID");
                    _articleID = value;
                    NotifyPropertyChanged("ArticleID");
                }

            }
        }
        private string _articleTitle;
        /// <summary>
        /// Article title
        /// </summary>
        [Column(CanBeNull = false)]
        public string ArticleTitle
        {
            get { return _articleTitle; }
            set
            {
                if (_articleTitle != value)
                {
                    NotifyPropertyChanging("ArticleTitle");
                    _articleTitle = value;
                    NotifyPropertyChanged("ArticleTitle");
                }
            }
        }
        private DateTime _addDate;
        /// <summary>
        /// Article add date
        /// </summary>
       [Column(DbType = “datetime")]
        public DateTime AddDate
        {
            get { return _addDate; }
            set
            {
                if (_addDate.CompareTo(_addDate) != 0)
                {
                    NotifyPropertyChanging("AddDate");
                    _addDate = value;
                    NotifyPropertyChanged("AddDate");
                }
            }
        }

        /// <summary>
        /// version column for update performance
        /// </summary>
        [Column(IsVersion = true)]
        private Binary _version;

        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));
            }
        }


    }
}

创建DataContext类ArticleDataContext

其代码如下:

namespace LinqToSqlSample
{
    public class ArticleDataContext : DataContext
    {
        /// <summary>
        /// Default connection string
        /// </summary>
        public static string ConnString = "Data Source=isostore:/ArticlesDB.sdf";

        #region constructor
        public ArticleDataContext(string connString)
            : base(connString)
        {
        }
        public ArticleDataContext()
            : this(ConnString)
        {
        }
        #endregion

        /// <summary>
        /// Represent article data table items
        /// </summary>
        public Table<Article> Articles;
    }
}

创建MainPageViewModel类

创建View Model MainPageViewModel,用于数据绑定,其代码如下:

namespace LinqToSqlSample
{
    public class MainPageViewModel : INotifyPropertyChanged
    {
        private ObservableCollection<Article> _articles;
        private readonly ArticleDataContext _context;

        public MainPageViewModel()
        {
            _context = new ArticleDataContext();
            InitializeData();
        }

        private void InitializeData()
        {
            var articleInDb = from Article article in _context.Articles
                              select article;
            this.Articles = new ObservableCollection<Article>(articleInDb);
        }

        public ObservableCollection<Article> Articles
        {
            get { return _articles; }
            set
            {
                if (_articles != value)
                {
                    _articles = value;
                    NotifyPropertyChanged("Articles");
                }
            }
        }

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

修改MainPage.xaml

在资源中添加ModelView,同时添加ListView展示数据并执行相应数据绑定,最终代码如下:

<phone:PhoneApplicationPage
    x:Class="LinqToSqlSample.MainPage"
    xmlns="
http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:phone="clr-namespace:Microsoft.Phone.Controls;assembly=Microsoft.Phone"
    xmlns:shell="clr-namespace:Microsoft.Phone.Shell;assembly=Microsoft.Phone"
    xmlns:d="
http://schemas.microsoft.com/expression/blend/2008"
    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:view="clr-namespace:LinqToSqlSample"
    mc:Ignorable="d" d:DesignWidth="480" d:DesignHeight="768"
    FontFamily="{StaticResource PhoneFontFamilyNormal}"
    FontSize="{StaticResource PhoneFontSizeNormal}"
    Foreground="{StaticResource PhoneForegroundBrush}"
    SupportedOrientations="Portrait" Orientation="Portrait"
    shell:SystemTray.IsVisible="True">
    <phone:PhoneApplicationPage.Resources>
        <view:MainPageViewModel x:Key="viewModel"/>
    </phone:PhoneApplicationPage.Resources>
    <!--LayoutRoot is the root grid where all page content is placed-->
    <Grid x:Name="LayoutRoot" Background="Transparent">
        <Grid.RowDefinitions>
            <RowDefinition Height="Auto"/>
            <RowDefinition Height="*"/>
        </Grid.RowDefinitions>

        <!--TitlePanel contains the name of the application and page title-->
        <StackPanel x:Name="TitlePanel" Grid.Row="0" Margin="12,17,0,28">
            <TextBlock x:Name="ApplicationTitle" Text="KOSMISCH.NET" Style="{StaticResource PhoneTextNormalStyle}"/>
            <TextBlock x:Name="PageTitle" Text="LINQ to SQL" Margin="9,-7,0,0" Style="{StaticResource PhoneTextTitle1Style}"/>
        </StackPanel>

        <!--ContentPanel - place additional content here-->
        <Grid x:Name="ContentPanel" Grid.Row="1" Margin="12,0,12,0">
            <StackPanel Orientation="Vertical" DataContext="{StaticResource viewModel}">
                <ListBox ItemsSource="{Binding Path=Articles}">
                    <ListBox.ItemTemplate>
                        <DataTemplate>
                            <Grid>
                                <Grid.ColumnDefinitions>
                                    <ColumnDefinition Width="50"/>
                                    <ColumnDefinition Width="240"/>
                                    <ColumnDefinition Width="60"/>
                                </Grid.ColumnDefinitions>
                                <TextBlock x:Name="ID" Text="{Binding Path=ArticleID, Mode=OneWay}" Grid.Column="0" HorizontalAlignment="Center" VerticalAlignment="Center" />
                                <TextBox x:Name="Title" Text="{Binding Path=ArticleTitle, Mode=OneWay}" Grid.Column="1" TextAlignment="Center" InputScope="Number"/>
                                <TextBox x:Name="AddDate" Text="{Binding Path=AddDate, Mode=TwoWay}" Grid.Column="2" HorizontalAlignment="Center" VerticalAlignment="Center" />
                            </Grid>
                        </DataTemplate>
                    </ListBox.ItemTemplate>
                </ListBox>
               
            </StackPanel>
        </Grid>
    </Grid>

    <!--Sample code showing usage of ApplicationBar-->
    <!--<phone:PhoneApplicationPage.ApplicationBar>
        <shell:ApplicationBar IsVisible="True" IsMenuEnabled="True">
            <shell:ApplicationBarIconButton IconUri="/Images/appbar_button1.png" Text="Button 1"/>
            <shell:ApplicationBarIconButton IconUri="/Images/appbar_button2.png" Text="Button 2"/>
            <shell:ApplicationBar.MenuItems>
                <shell:ApplicationBarMenuItem Text="MenuItem 1"/>
                <shell:ApplicationBarMenuItem Text="MenuItem 2"/>
            </shell:ApplicationBar.MenuItems>
        </shell:ApplicationBar>
    </phone:PhoneApplicationPage.ApplicationBar>-->

</phone:PhoneApplicationPage>

初始化数据库

在App.xaml.cs中初始化数据库,添加代码如下:

private static void InitializeDataBase()
       {
           using (var db = new ArticleDataContext())
           {
               if (db.DatabaseExists() == false)
               {
                   //Create the database
                   db.CreateDatabase();
                   db.Articles.InsertOnSubmit(new Article
                   {
                       AddDate = DateTime.Parse("2011-10-29"),
                       ArticleTitle = "Test Article 1"
                   });
                   db.Articles.InsertOnSubmit(new Article
                   {
                       AddDate = DateTime.Parse("2011-10-29"),
                       ArticleTitle = "Test Article 2"
                   });
                   db.Articles.InsertOnSubmit(new Article
                   {
                       AddDate = DateTime.Parse("2011-10-29"),
                       ArticleTitle = "Test Article 3"
                   });
                   db.Articles.InsertOnSubmit(new Article
                   {
                       AddDate = DateTime.Parse("2011-10-29"),
                       ArticleTitle = "Test Article 4"
                   });
                   db.SubmitChanges();
               }
           }
       }

并且在App类构造函数中引用此方法。

运行程序

运行结果如下:

LINQ to SQL in Windows Phone 1

About author
Disclaimer
The opinions and comments expressed herein are my own personal opinions and do not represent my employer's view in any way.
Comments
No comments.
Add comment
Title
Title is required.
Name
Name is required.
Email
Please input your personal email with valid format.
Comments
Please input comment content.
Captcha Refresh
Input captcha:

Subscription

Statistics

Locations of visitors to this page