This site uses cookies to deliver our services. By using this site, you acknowledge that you have read and understand our Cookie and Privacy policy. Your use of Kontext website is subject to this policy. Allow Cookies and Dismiss

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

509 views 0 comments last modified about 7 years ago Raymond Tang Fahao

In this page

在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="kontext.tech" 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

Related pages

Surface RT/Pro 版本Windows 8 QQ下载与安装

2515 views   0 comments last modified about 5 years ago

Surface的用户可能会遇到一个难题,在应用商城里边无法搜索到QQ应用程序。但是让我疑惑的是,我的Windows 8.1 系统之前是成功安装了QQ。

后来在腾讯QQ下载官网发现,其实是有QQ应用安装的链接:

按照如下方法安装Surface QQ.

Have fun!

image

View detail

my first begining with Windows 7

101 views   0 comments last modified about 9 years ago

Maybe it is a little late now due to busy work,anyhow,it is better to do lately than never to do.

View detail

[教程]在Windows Phone 程序中使用LINQ to XML以及执行数据绑定

260 views   0 comments last modified about 7 years ago


LINQ是Language Integrated Query的简写,中文名称是语言集成查询,它是一组技术的组合,包括LINQ to DataSet(typed 以及untyped),LINQ to SQL(适用于SQL Server数据库),LINQ to XML(包括内存中以及XML文件中的数据),LINQ to Object,LINQ to Entities(结合Entity Framework使用)等。这些技术建立在将查询功能直接集成到 C# 语言(以及 Visual Basic 和可能的任何其他 .NET 语言)的基础上。借助于 LINQ,查询现在已是高级语言构造,就如同类、方法、事件等等。具体参考MSDN文档介绍View detail

SVN 中文子目录权限失效的解决方案

241 views   0 comments last modified about 9 years ago

svn对于非英文文件名和目录名使用utf-8格式编码处理,要对中文目录进行正确控制,
对于authz文件应该使用无BOM的utf-8格式,如何将默认的文件转为utf-8?可以使用UltraEdit编辑器存储的时候把格式选择“UTF-8 无BOM”;

View detail

LINQ to SQL in Windows Phone 7 Error: An overflow occurred while converting to datetime.

454 views   0 comments last modified about 7 years ago

Resolve the issue in Linq to SQL in Windows Phone 7 App:
An overflow occurred while converting to datetime.

View detail

Add comment

Please login first to add comments.  Log in New user?  Register

Comments (0)

No comments yet.