在Windows Phone应用程序中通过LINQ to SQL使用本地数据库

446 views 0 comments posted at about 7 years ago Raymond Tang Fahao

在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

Add comment

Comments (0)

No comments yet.
In this Page