*引言
笔者近段时间做关于WP开发方面的毕业设计,内容涉及到WP和数据库操作的问题。考虑到WP内置的数据库是基于隔离存储的,所以本身的性能鸡肋不说,而且效率低下。有没有一种方法能够实现WP与我们常用的强大的主流数据库交互呢?答案是肯定的。做过有关WCF开发的朋友应该清楚,WCF是可以实现跨机器、跨语言、跨平台、跨终端通信的通信架构。基于这一点,我们就可以利用它作为数据传递的中介,WP客户端可以把数据传到WCF,再由WCF实现对具体业务逻辑的操作,包括对数据库的操作。当然也可以根据需要,读取数据库内的数据传回到WP客户端。这样我就可以使WP客户端间接地对数据库进行访问了。
好吧,废话少说,切入正题。
1数据库设计
1.1建立一个数据库,命名为userinfo
数据库采用SQL Server,本人用的是SQL2008,SQL 2005理论上也可以。
use master
go
create database userinfo
on
(
name='userinfo_data',
filename='D:\database\userinfo.mdf',
size=10mb,
maxsize=50MB,
filegrowth=10%
)
log on
(
name='userinfo_log',
filename='D:\database\userinfo.ldf',
size=5mb,
maxsize=20MB,
filegrowth=10%
)
1.2往数据库建一个名为users的表,并向其中插入数据
create table users
(
UserID varchar(20) not null primary key,
UserName varchar(20) null,
Phone varchar(20) null,
QQ varchar(20) null,
Condition varchar(20) null
)
insert into users values('P000000001','陈才学','13977547364','265489211','良好')
insert into users values('P000000002','江依妍','13587543647','755229211','良好')
insert into users values('P000000003','王康','13375573644','4654871','良好')
insert into users values('P000000004','楚天','13927547364','66544211','发烧')
insert into users values('P000000006','王大干','13752636436','76629211','良好')
insert into users values('P000000007','李玉梅','13696773678','365489261','良好')
insert into users values('P000000008','唐一菲','13977585248','76546611','良好')
insert into users values('P000000009','马瑟','13977555525','865489211','感冒')
insert into users values('P000000010','郑树森','13977563584','35489211','良好')
insert into users values('P000000011','叶文乐','13977578232','968489211','良好')
insert into users values('P000000012','王天羽','13977541285','658589211','良好')
2配置环境系统
选择win7,VS2010 SP1开发环境,安装Windows Phone 7 SDK。装完这些后,打开VS 2010,新建一个WCF服务应用程序项目(文件---新建---项目---WCF服务应用程序),命名为DatabaseService,解决方案命名为Tester,右键单击解决方案“Tester”---添加---新建项目,windows phone应用程序,命名为WPClient,确定后,具体文件表如下图所示:
2.1 IService1.cs文件配置,声明接口
双击IService1.cs,添加如下代码
[ServiceContract]
public interface IService1
{
//声明增加数据接口
[OperationContract]
string Insertdata(string UserID, string UserName, string Phone, string QQ, string Condition);
//声明删除数据接口
[OperationContract]
string Deletedata(string UserID);
//声明更改数据接口
[OperationContract]
string Updatedata(string UserID, string UserName, string Phone, string QQ, string Condition);
//声明查询数据接口
[OperationContract]
string Selectdata(string UserID);
}
2.2 Service1.svc.cs文件配置,编写上述IService接口的子类Service1的实现方法
双击Service1.svc.cs,引用System.Data、System.Data.SqlClient 程序集,并添加如下代码:
public class Service1 : IService1
{
string resoult;
SqlConnection strCon = new SqlConnection(@"server=WIN-20130306POM\SQL2008;uid=sa;pwd=1990cfm;database=userinfo");
//创建数据库链接,其中“WIN-20130306POM\SQL2008”表示你的数据库服务器,uid表示数据库账号,pwd表示数据库密码,database表示你要连接的数据库名称
//增加数据接口实现代码
public string Insertdata(string UserID, string UserName, string Phone, string QQ, string Condition)
{
try
{
strCon.Open();
string _strSql = "select * from users where UserID=@UserID";
SqlCommand cmd1 = new SqlCommand(_strSql, strCon);
SqlParameter parm = new SqlParameter("@UserID", UserID);
cmd1.Parameters.Add(parm);
SqlDataReader reader = cmd1.ExecuteReader();//定义reader来判断返回结果是否为空
if (reader.Read())
{
//以上这七行代码是先判断UserID是否存在再进行后续的操作,为什么要这样做呢?因为插入新的UserID,此UserID可能是数据库对应的表中已经存在了的,为了避免程序出现bug,所以要预先判断。
if (!reader.IsDBNull(0))
{
reader.Close();
resoult = "数据库已存在相同的用户ID,请更改!";
}
}
else if (!reader.Read())
{
reader.Close();
string strSql = "insert into users(UserID,UserName,Phone,QQ,Condition) values(@UserID,@UserName,@Phone,@QQ,@Condition)";
SqlCommand cmd = new SqlCommand(strSql, strCon);
SqlParameter par1 = new SqlParameter("@UserID", UserID);
cmd.Parameters.Add(par1);
SqlParameter par2 = new SqlParameter("@UserName", UserName);
cmd.Parameters.Add(par2);
SqlParameter par3 = new SqlParameter("@Phone", Phone);
cmd.Parameters.Add(par3);
SqlParameter par4 = new SqlParameter("@QQ", QQ);
cmd.Parameters.Add(par4);
SqlParameter par5 = new SqlParameter("@Condition", Condition);
cmd.Parameters.Add(par5);
cmd.ExecuteNonQuery();
cmd.Dispose();
resoult = "数据已经被成功录入数据库!";
}
return resoult;
}
catch (Exception ex)
{
throw ex;
}
finally
{
strCon.Close();
}
}
//删除数据接口实现方法
public string Deletedata(string UserID)
{
try
{
strCon.Open();
string _strSql = "select * from users where UserID=@UserID";
SqlCommand cmd1 = new SqlCommand(_strSql, strCon);
SqlParameter parm = new SqlParameter("@UserID", UserID);
cmd1.Parameters.Add(parm);
SqlDataReader reader = cmd1.ExecuteReader();
if (reader.Read())
{
//还是先查后操作,防bug。你们懂的!
reader.Close();
string strSql = "delete from users where UserID=@UserID";
SqlCommand cmd = new SqlCommand(strSql, strCon);
SqlParameter parn = new SqlParameter("@UserID", UserID);
cmd.Parameters.Add(parn);
cmd.ExecuteNonQuery();
cmd.Dispose();
resoult = "数据已经成功删除!";
}
else if (!reader.Read())
{
reader.Close();
resoult = "用户ID不存在,请确认后再更改!";
}
return resoult;
}
catch (Exception ex)
{
throw ex;
}
finally
{
strCon.Close();
}
}
//更新数据接口实现代码
public string Updatedata(string UserID, string UserName, string Phone, string QQ, string Condition)
{
try
{
strCon.Open();
string _strSql = "select * from users where UserID=@UserID";
SqlCommand cmd1 = new SqlCommand(_strSql, strCon);
SqlParameter parm = new SqlParameter("@UserID", UserID);
cmd1.Parameters.Add(parm);
SqlDataReader reader = cmd1.ExecuteReader();
if (reader.Read())
{
//还是先查后操作,防bug。你们懂的!
reader.Close();
string strSql = "update users set UserName=@UserName,Phone=@Phone,QQ=@QQ,Condition=@Condition where UserID=@UserID";
SqlCommand cmd = new SqlCommand(strSql, strCon);
SqlParameter par1 = new SqlParameter("@UserID", UserID);
cmd.Parameters.Add(par1);
SqlParameter par2 = new SqlParameter("@UserName", UserName);
cmd.Parameters.Add(par2);
SqlParameter par3 = new SqlParameter("@Phone", Phone);
cmd.Parameters.Add(par3);
SqlParameter par4 = new SqlParameter("@QQ", QQ);
cmd.Parameters.Add(par4);
SqlParameter par5 = new SqlParameter("@Condition", Condition);
cmd.Parameters.Add(par5);
cmd.ExecuteNonQuery();
cmd.Dispose();
resoult = "数据已经被成功更新!";
}
else if (!reader.Read())
{
reader.Close();
resoult = "数据库表中不存在此用户ID,请更改!";
}
return resoult;
}
catch (Exception ex)
{
throw ex;
}
finally
{
strCon.Close();
}
}
//查询数据接口实现代码
public string Selectdata(string UserID)
{
try
{
strCon.Open();
string _strSql = "select * from users where UserID=@UserID";
SqlCommand cmd1 = new SqlCommand(_strSql, strCon);
SqlParameter parm = new SqlParameter("@UserID", UserID);
cmd1.Parameters.Add(parm);
SqlDataReader reader = cmd1.ExecuteReader();
if (reader.Read())
{
//还是先查后操作,防bug。你们懂的!
resoult = "查询结果=>"+"用户ID:" + reader.GetString(0) + ";" + "姓名:" + reader.GetString(1) + ";" + "电话号码:" + reader.GetString(2) + ";" + "QQ:" + reader.GetString(3) +";"+"健康状况:"+reader.GetString(4)+ "。";
reader.Close();
}
else if (!reader.Read())
{
reader.Close();
resoult = "您所查询的用户ID不存在!请重新输入!";
}
return resoult;
}
catch (Exception ex)
{
throw ex;
}
finally
{
strCon.Close();
}
}
}
3在WPClient项目中添加服务引用。
这一步是最重要的,需要特别留意,具体方法是,右键单击WPClient项目---添加服务引用---发现,命名空间写WPService,点确定完成。具体如下图所示。
4Windows Phone 客户端设计
4.1mainpage.xaml导航页配置
在WPClient项目添加一下几个Insertdata.xaml、Deletedata.xaml、Updatedata.xaml、Selectdata.xaml。(具体方法是右键单击WPCient项目---添加---新建项),效果如下图所示:
在mainpage.xaml中加入以下代码。
<StackPanel x:Name="TitlePanel" Grid.Row="0" Margin="12,17,0,28">
<TextBlock x:Name="ApplicationTitle" Text="我的应用程序" Style="{StaticResource PhoneTextNormalStyle}"/>
<TextBlock x:Name="PageTitle" Text="SQL数据操作" Margin="9,-7,0,0" Style="{StaticResource PhoneTextTitle1Style}"/>
</StackPanel>
<!--ContentPanel - 在此处放置其他内容-->
<Grid x:Name="ContentPanel" Grid.Row="1" Margin="12,0,12,0">
<Button Content="增加数据" Click="button1_Click" Height="72" HorizontalAlignment="Left" Margin="123,35,0,0" Name="button1" VerticalAlignment="Top" Width="160" />
<Button Content="删除数据" Click="button2_Click" Height="72" HorizontalAlignment="Left" Margin="123,140,0,0" Name="button2" VerticalAlignment="Top" Width="160" />
<Button Content="更改数据" Click="button3_Click" Height="72" HorizontalAlignment="Left" Margin="123,254,0,0" Name="button3" VerticalAlignment="Top" Width="160" />
<Button Content="查询数据" Click="button4_Click" Height="72" HorizontalAlignment="Left" Margin="123,363,0,0" Name="button4" VerticalAlignment="Top" Width="160" />
</Grid>
</Grid>
切换到mainpage.xmal.cs输入一下代码
public MainPage()
{
InitializeComponent();
}
private void button1_Click(object sender, RoutedEventArgs e)
{
this.NavigationService.Navigate(new Uri("/Insertdata.xaml", UriKind.Relative));
}
private void button2_Click(object sender, RoutedEventArgs e)
{
this.NavigationService.Navigate(new Uri("/Deletedata.xaml", UriKind.Relative));
}
private void button3_Click(object sender, RoutedEventArgs e)
{
this.NavigationService.Navigate(new Uri("/Updatedata.xaml", UriKind.Relative));
}
private void button4_Click(object sender, RoutedEventArgs e)
{
this.NavigationService.Navigate(new Uri("/Selectdata.xaml", UriKind.Relative));
}
}
4.2Insertdata.xaml模块设计,此模块实现数据的添加
双击打开Insertdata.xaml,添加以下代码:
<StackPanel x:Name="TitlePanel" Grid.Row="0" Margin="12,17,0,28">
<TextBlock x:Name="ApplicationTitle" Text="我的应用程序" Style="{StaticResource PhoneTextNormalStyle}"/>
<TextBlock x:Name="PageTitle" Text="插入数据" Margin="9,-7,0,0" Style="{StaticResource PhoneTextTitle1Style}"/>
</StackPanel>
<!--ContentPanel - 在此处放置其他内容-->
<Grid x:Name="ContentPanel" Grid.Row="1" Margin="12,0,12,0">
<TextBlock Height="30" HorizontalAlignment="Left" Margin="9,21,0,0" Name="textBlock1" Text="用户ID:" VerticalAlignment="Top" />
<TextBox Height="72" HorizontalAlignment="Left" Margin="80,0,0,0" Name="textBox1" Text=" " VerticalAlignment="Top" Width="370" />
<TextBlock Height="30" HorizontalAlignment="Left" Margin="12,99,0,0" Name="textBlock2" Text="姓名:" VerticalAlignment="Top" />
<TextBlock Height="30" HorizontalAlignment="Left" Margin="6,185,0,0" Name="textBlock3" Text="电话:" VerticalAlignment="Top" />
<TextBox Height="72" HorizontalAlignment="Left" Margin="78,78,0,0" Name="textBox2" Text=" " VerticalAlignment="Top" Width="372" />
<TextBox Height="72" HorizontalAlignment="Left" Margin="78,156,0,0" Name="textBox3" Text=" " VerticalAlignment="Top" Width="372" />
<TextBlock Height="30" HorizontalAlignment="Left" Margin="9,262,0,0" Name="textBlock4" Text="QQ:" VerticalAlignment="Top" />
<TextBox Height="72" HorizontalAlignment="Left" Margin="78,234,0,0" Name="textBox4" Text="" VerticalAlignment="Top" Width="372" />
<TextBlock Height="30" HorizontalAlignment="Left" Margin="12,336,0,0" Name="textBlock5" Text="健康状况:" VerticalAlignment="Top" />
<TextBox Height="72" HorizontalAlignment="Left" Margin="99,312,0,0" Name="textBox5" Text=" " VerticalAlignment="Top" Width="351" />
<Button Content="添加" Height="72" HorizontalAlignment="Left" Margin="12,390,0,0" Name="insertbnt" Click="insertbnt_Click" VerticalAlignment="Top" Width="160" />
<TextBlock FontSize="32" Height="119" HorizontalAlignment="Left" Margin="6,468,0,0" Name="insertinfo" Text=" " VerticalAlignment="Top" Width="444" />
</Grid>
双击打开Insertdata.xaml.cs,添加以下代码:
public Insertdata()
{
InitializeComponent();
}
private void insertbnt_Click(object sender, RoutedEventArgs e)
{
if (textBox1.Text != null && textBox2.Text != null && textBox3.Text != null && textBox4.Text != null && textBox5.Text != null)
{
WPService.Service1Client insert = new WPService.Service1Client();//创建WCF服务实例
string UserID = this.textBox1.Text.Trim();
string UserName = this.textBox2.Text.Trim();
string Phone = this.textBox3.Text.Trim();
string QQ = this.textBox4.Text.Trim();
string Condition = this.textBox5.Text.Trim();
insert.InsertdataCompleted+=new EventHandler<WPService.InsertdataCompletedEventArgs>(insert_InsertdataCompleted);
//监控处理过程
insert.InsertdataAsync(UserID,UserName,Phone,QQ,Condition);
//数据传送
}
else
{
MessageBox.Show("所有选项均不能为空!");
}
}
void insert_InsertdataCompleted(object sender,WPService.InsertdataCompletedEventArgs e)
{
insertinfo.Text = e.Result.ToString();//接收返回数据并显示
}
}
4.3Deletedata.xaml模块设计,这一模块是实现删除的数据
双击Deletedata.xaml,添加以下代码
<StackPanel x:Name="TitlePanel" Grid.Row="0" Margin="12,17,0,28">
<TextBlock x:Name="ApplicationTitle" Text="我的应用程序" Style="{StaticResource PhoneTextNormalStyle}"/>
<TextBlock x:Name="PageTitle" Text="删除数据" Margin="9,-7,0,0" Style="{StaticResource PhoneTextTitle1Style}"/>
</StackPanel>
<!--ContentPanel - 在此处放置其他内容-->
<Grid x:Name="ContentPanel" Grid.Row="1" Margin="12,0,12,0">
<TextBlock Height="30" HorizontalAlignment="Left" Margin="10,10,0,0" Name="textBlock1" Text="请输入要删除的用户ID:" VerticalAlignment="Top" />
<TextBox Height="72" HorizontalAlignment="Left" Margin="4,46,0,0" Name="textBox1" Text="" VerticalAlignment="Top" Width="446" />
<Button Content="删除" Height="72" HorizontalAlignment="Left" Margin="4,124,0,0" Name="deletebnt" Click="deletebnt_Click" VerticalAlignment="Top" Width="160" />
<TextBlock Height="271" FontSize="32" HorizontalAlignment="Left" Margin="12,219,0,0" Name="deleteinfo" Text=" " VerticalAlignment="Top" Width="438" />
</Grid>
双击Deletedata.xaml.cs,添加以下代码
public Deletedata()
{
InitializeComponent();
}
private void deletebnt_Click(object sender, RoutedEventArgs e)
{
if (textBox1.Text != null)
{
WPService.Service1Client delete = new WPService.Service1Client();//创建实例
string UserID = textBox1.Text.Trim();
delete.DeletedataCompleted+=new EventHandler<WPService.DeletedataCompletedEventArgs>(delete_DeletedataCompleted);
delete.DeletedataAsync(UserID);
}
else
{
MessageBox.Show("请输入用户ID!");
}
}
void delete_DeletedataCompleted(object sender, WPService.DeletedataCompletedEventArgs e)
{
deleteinfo.Text = e.Result.ToString();//接收并显示数据
}
}
4.4Updatedata.xaml模块设计,此模块实现数据的更新
双击Updatedata.xaml,添加如下代码
<StackPanel x:Name="TitlePanel" Grid.Row="0" Margin="12,17,0,28">
<TextBlock x:Name="ApplicationTitle" Text="我的应用程序" Style="{StaticResource PhoneTextNormalStyle}"/>
<TextBlock x:Name="PageTitle" Text="更新数据" Margin="9,-7,0,0" Style="{StaticResource PhoneTextTitle1Style}"/>
</StackPanel>
<!--ContentPanel - 在此处放置其他内容-->
<Grid x:Name="ContentPanel" Grid.Row="1" Margin="12,0,12,0">
<TextBlock Height="30" HorizontalAlignment="Left" Margin="10,10,0,0" Name="textBlock1" Text="请输入用户ID:" VerticalAlignment="Top" />
<TextBox Height="72" HorizontalAlignment="Left" Margin="10,50,0,0" Name="textBox1" Text=" " VerticalAlignment="Top" Width="440" />
<TextBlock Height="30" HorizontalAlignment="Left" Margin="12,156,0,0" Name="textBlock2" Text="姓名:" VerticalAlignment="Top" />
<TextBlock Height="30" HorizontalAlignment="Left" Margin="6,239,0,0" Name="textBlock3" Text="手机:" VerticalAlignment="Top" />
<TextBlock Height="30" HorizontalAlignment="Left" Margin="6,320,0,0" Name="textBlock4" Text="QQ:" VerticalAlignment="Top" />
<TextBlock Height="30" HorizontalAlignment="Left" Margin="12,403,0,0" Name="textBlock5" Text="健康状况:" VerticalAlignment="Top" />
<Button Content="更改" Height="72" HorizontalAlignment="Left" Margin="0,453,0,0" Name="updatebnt" Click="updatebnt_Click" VerticalAlignment="Top" Width="160" />
<TextBlock FontSize="32" Height="70" HorizontalAlignment="Left" Margin="12,531,0,0" Name="updateinfo" Text="" VerticalAlignment="Top" Width="420" />
<TextBox Height="72" HorizontalAlignment="Left" Margin="61,128,0,0" Name="textBox2" Text=" " VerticalAlignment="Top" Width="389" />
<TextBox Height="72" HorizontalAlignment="Left" Margin="72,206,0,0" Name="textBox3" Text=" " VerticalAlignment="Top" Width="378" />
<TextBox Height="72" HorizontalAlignment="Left" Margin="72,295,0,0" Name="textBox4" Text=" " VerticalAlignment="Top" Width="378" />
<TextBox Height="72" HorizontalAlignment="Left" Margin="118,375,0,0" Name="textBox5" Text=" " VerticalAlignment="Top" Width="332" />
</Grid>
双击Updatedata.xaml.cs,添加如下代码
public Updatedata()
{
InitializeComponent();
}
private void updatebnt_Click(object sender, RoutedEventArgs e)
{
if (textBox1.Text != null)
{
WPService.Service1Client update = new WPService.Service1Client();
string UserID = this.textBox1.Text.Trim();
string UserName = this.textBox2.Text.Trim();
string Phone = this.textBox3.Text.Trim();
string QQ = this.textBox4.Text.Trim();
string Condition = this.textBox5.Text.Trim();
update.UpdatedataCompleted+=new EventHandler<WPService.UpdatedataCompletedEventArgs>(update_UpdatedataCompleted);
update.UpdatedataAsync(UserID, UserName, Phone, QQ, Condition);
}
else
{
MessageBox.Show("用户ID不能为空!");
}
}
void update_UpdatedataCompleted(object semder, WPService.UpdatedataCompletedEventArgs e)
{
updateinfo.Text = e.Result.ToString();
}
}
4.5Selectdata.xaml模块设计,此模块实现数据的查询
双击Selectdata.xaml,并添加如下代码
<StackPanel x:Name="TitlePanel" Grid.Row="0" Margin="12,17,0,28">
<TextBlock x:Name="ApplicationTitle" Text="我的应用程序" Style="{StaticResource PhoneTextNormalStyle}"/>
<TextBlock x:Name="PageTitle" Text="删除数据" Margin="9,-7,0,0" Style="{StaticResource PhoneTextTitle1Style}"/>
</StackPanel>
<!--ContentPanel - 在此处放置其他内容-->
<Grid x:Name="ContentPanel" Grid.Row="1" Margin="12,0,12,0">
<TextBlock Height="30" HorizontalAlignment="Left" Margin="10,10,0,0" Name="textBlock1" Text="请输入要删除的用户ID:" VerticalAlignment="Top" />
<TextBox Height="72" HorizontalAlignment="Left" Margin="4,46,0,0" Name="textBox1" Text="" VerticalAlignment="Top" Width="446" />
<Button Content="删除" Height="72" HorizontalAlignment="Left" Margin="4,124,0,0" Name="deletebnt" Click="deletebnt_Click" VerticalAlignment="Top" Width="160" />
<TextBlock Height="271" FontSize="32" HorizontalAlignment="Left" Margin="12,219,0,0" Name="deleteinfo" Text=" " VerticalAlignment="Top" Width="438" />
</Grid>
双击Selectdata.xaml.cs,并添加如下代码
public Deletedata()
{
InitializeComponent();
}
private void deletebnt_Click(object sender, RoutedEventArgs e)
{
if (textBox1.Text != null)
{
WPService.Service1Client delete = new WPService.Service1Client();
string UserID = textBox1.Text.Trim();
delete.DeletedataCompleted+=new EventHandler<WPService.DeletedataCompletedEventArgs>(delete_DeletedataCompleted);
delete.DeletedataAsync(UserID);
}
else
{
MessageBox.Show("请输入用户ID!");
}
}
void delete_DeletedataCompleted(object sender, WPService.DeletedataCompletedEventArgs e)
{
deleteinfo.Text = e.Result.ToString();
}
}
5程序运行效果
5.1主界面
5.2 增加数据模块
5.3删除数据模块
5.4更新数据模块
5.5查询数据模块
关于本文的代码已经 共享到CSDN网站的代码资源里面的。各位朋友可以下载!
这是本人第一次写技术贴,难免有所纰漏和不足,请各位不吝赐教,提出改进意见,谢谢!