[原创]Silverlight与Access数据库的互操作(CURD完全解析)
Silverlight与SQL Server或SQL Server Express的互操作，已成为我们常见的开发Siverlight应用程序的一种模式。可是在开发一些小型应用程序时，我们就需要使用一些小巧的轻量级的数据库，比如Access数据库。由于Visual Studio中并没有直接提供Silverlight与Access互操作的系列方法。于是本文就将为大家介绍如何让Silverlight使用Access作为后台数据库。
Silverlight与SQL Server或SQL Server Express的互操作，已成为我们常见的开发Siverlight应用程序的一种模式。可是在开发一些小型应用程序时，我们就需要使用一些小巧的轻量级的数据库，比如Access数据库。由于Visual Studio中并没有直接提供Silverlight与Access互操作的系列方法。于是本文就将为大家介绍如何让Silverlight使用Access作为后台数据库。
准备工作
1）建立起测试项目
细节详情请见强大的DataGrid组件[2]_数据交互之ADO.NET Entity Framework——Silverlight学习笔记[10]。
2）创建测试用数据库
如下图所示，创建一个名为Employees.mdb的Access数据库，建立数据表名称为Employee。将该数据库置于作为服务端的项目文件夹下的App_Data文件夹中，便于操作管理。
建立数据模型
EmployeeModel.cs文件（放置在服务端项目文件夹下）
using System;
using System.Collections.Generic;
using System.Linq;
namespace datagridnaccessdb
{
public class EmployeeModel
{
public int EmployeeID { get; set; }
public string EmployeeName { get; set; }
public int EmployeeAge { get; set; }
}
}
建立服务端Web Service★
右击服务端项目文件夹，选择Add->New Item....，按下图所示建立一个名为EmployeesInfoWebService.asmx的Web Service，作为Silverlight与Access数据库互操作的桥梁。
创建完毕后，双击EmployeesInfoWebService.asmx打开该文件。将里面的内容修改如下：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data.OleDb;//引入该命名空间为了操作Access数据库
using System.Data;
namespace datagridnaccessdb
{
/// <summary>
/// Summary description for EmployeesInfoWebService
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.ComponentModel.ToolboxItem(false)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
// [System.Web.Script.Services.ScriptService]
public class EmployeesInfoWebService : System.Web.Services.WebService
{
[WebMethod]//获取雇员信息
public List<EmployeeModel> GetEmployeesInfo()
{
List<EmployeeModel> returnedValue = new List<EmployeeModel>();
OleDbCommand Cmd = new OleDbCommand();
SQLExcute("SELECT * FROM Employee", Cmd);
OleDbDataAdapter EmployeeAdapter = new OleDbDataAdapter();
EmployeeAdapter.SelectCommand = Cmd;
DataSet EmployeeDataSet = new DataSet();
EmployeeAdapter.Fill(EmployeeDataSet);
foreach (DataRow dr in EmployeeDataSet.Tables[0].Rows)
{
EmployeeModel tmp = new EmployeeModel();
tmp.EmployeeID = Convert.ToInt32(dr[0]);
tmp.EmployeeName = Convert.ToString(dr[1]);
tmp.EmployeeAge = Convert.ToInt32(dr[2]);
returnedValue.Add(tmp);
}
return returnedValue;
}
[WebMethod] //添加雇员信息
public void Insert(List<EmployeeModel> employee)
{
employee.ForEach( x =>
{
string CmdText = "INSERT INTO Employee(EmployeeName,EmployeeAge) VALUES('"+x.EmployeeName+"',"+x.EmployeeAge.ToString()+")";
SQLExcute(CmdText);
});
}
[WebMethod] //更新雇员信息
public void Update(List<EmployeeModel> employee)
{
employee.ForEach(x =>
{
string CmdText = "UPDATE Employee SET EmployeeName='"+x.EmployeeName+"',EmployeeAge="+x.EmployeeAge.ToString();
CmdText += " WHERE EmployeeID="+x.EmployeeID.ToString();
SQLExcute(CmdText);
});
}
[WebMethod] //删除雇员信息
public void Delete(List<EmployeeModel> employee)
{
employee.ForEach(x =>
{
string CmdText = "DELETE FROM Employee WHERE EmployeeID="+x.EmployeeID.ToString();
SQLExcute(CmdText);
});
}
//执行SQL命令文本，重载1
private void SQLExcute(string SQLCmd)
{
string ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" + Server.MapPath(@"App_Data\Employees.mdb;");
Conn.Open();
OleDbCommand Cmd = new OleDbCommand();
Cmd.Connection =
Cmd.CommandTimeout = 15;
Cmd.CommandType = CommandType.Text;
Cmd.CommandText = SQLCmd;
Cmd.ExecuteNonQuery();
Conn.Close();
}
//执行SQL命令文本，重载2
private void SQLExcute(string SQLCmd,OleDbCommand Cmd)
{
string ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" + Server.MapPath(@"App_Data\Employees.mdb;");
Conn.Open();
Cmd.Connection =
Cmd.CommandTimeout = 15;
Cmd.CommandType = CommandType.Text;
Cmd.CommandText = SQLCmd;
Cmd.ExecuteNonQuery();
}
}
}
之后，在Silverlight客户端应用程序文件夹下，右击References文件夹，选择菜单选项Add Service Reference...。如下图所示，引入刚才我们创建的Web Service（别忘了按Discover按钮进行查找）。
创建Silverlight客户端应用程序
MainPage.xaml文件
<UserControl
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:d="http://schemas.microsoft.com/expression/blend/2008" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
mc:Ignorable="d" xmlns:data="clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls.Data" xmlns:dataFormToolkit="clr-namespace:System.Windows.Controls;assembly=System.Windows.Controls.Data.DataForm.Toolkit" x:Class="SilverlightClient.MainPage"
d:DesignWidth="320" d:DesignHeight="240">
<Grid x:Name="LayoutRoot" Width="320" Height="240" Background="White">
<dataFormToolkit:DataForm x:Name="dfEmployee" Margin="8,8,8,42"/>
<Button x:Name="btnGetData" Height="30" Margin="143,0,100,8" VerticalAlignment="Bottom" Content="Get Data" Width="77"/>
<Button x:Name="btnSaveAll" Height="30" Margin="0,0,8,8" VerticalAlignment="Bottom" Content="Save All" HorizontalAlignment="Right" Width="77"/>
<TextBlock x:Name="tbResult" Height="30" HorizontalAlignment="Left" Margin="8,0,0,8" VerticalAlignment="Bottom" Width="122" TextWrapping="Wrap" FontSize="16"/>
</Grid>
</UserControl>
MainPage.xaml.cs文件
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Linq;
using System.Net;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Animation;
using System.Windows.Shapes;
using System.Xml;
using System.Xml.Linq;
using System.Windows.Browser;
using SilverlightClient.EmployeesInfoServiceReference;
namespace SilverlightClient
{
public partial class MainPage : UserControl
{
int originalNum;//记录初始时的Employee表中的数据总数
ObservableCollection<EmployeeModel> deletedID = new ObservableCollection<EmployeeModel>();//标记被删除的对象
public MainPage()
{
InitializeComponent();
this.Loaded += new RoutedEventHandler(MainPage_Loaded);
this.btnGetData.Click += new RoutedEventHandler(btnGetData_Click);
this.btnSaveAll.Click += new RoutedEventHandler(btnSaveAll_Click);
this.dfEmployee.DeletingItem += new EventHandler<System.ComponentModel.CancelEventArgs>(dfEmployee_DeletingItem);
}
void dfEmployee_DeletingItem(object sender, System.ComponentModel.CancelEventArgs e)
{
deletedID.Add(dfEmployee.CurrentItem as EmployeeModel);//正在删除时，将被删除对象进行标记，以便传给服务端真正删除。
}
void btnSaveAll_Click(object sender, RoutedEventArgs e)
{
List<EmployeeModel> updateValues = dfEmployee.ItemsSource.Cast<EmployeeModel>().ToList();
ObservableCollection<EmployeeModel> returnValues = new ObservableCollection<EmployeeModel>();
if (updateValues.Count > originalNum)
{
//添加数据
for (int i = originalNum; i <= updateValues.Count - 1; i++)
{
returnValues.Add(updateValues.ToArray()[i]);
}
EmployeesInfoWebServiceSoapClient webClient = new EmployeesInfoWebServiceSoapClient();
webClient.InsertCompleted += new EventHandler<System.ComponentModel.AsyncCompletedEventArgs>(webClient_InsertCompleted);
webClient.InsertAsync(returnValues);
//必须考虑数据集中既有添加又有更新的情况
returnValues.Clear();
updateValues.ForEach(x => returnValues.Add(x));
webClient.UpdateCompleted += new EventHandler<System.ComponentModel.AsyncCompletedEventArgs>(webClient_UpdateCompleted);
webClient.UpdateAsync(returnValues);
}
else if (updateValues.Count < originalNum)
{
//删除数据
EmployeesInfoWebServiceSoapClient webClient = new EmployeesInfoWebServiceSoapClient();
webClient.DeleteCompleted += new EventHandler<System.ComponentModel.AsyncCompletedEventArgs>(webClient_DeleteCompleted);
webClient.DeleteAsync(deletedID);
}
else
{
//更新数据
updateValues.ForEach(x => returnValues.Add(x));
EmployeesInfoWebServiceSoapClient webClient = new EmployeesInfoWebServiceSoapClient();
webClient.UpdateCompleted += new EventHandler<System.ComponentModel.AsyncCompletedEventArgs>(webClient_UpdateCompleted);
webClient.UpdateAsync(returnValues);
}
}
void webClient_UpdateCompleted(object sender, System.ComponentModel.AsyncCompletedEventArgs e)
{
tbResult.Text = "更新成功！";
GetEmployees();//更新originalNum防止数据的重复插入，感谢园友紫色永恒的及时指出！
}
void webClient_DeleteCompleted(object sender, System.ComponentModel.AsyncCompletedEventArgs e)
{
tbResult.Text = "删除成功！";
}
void webClient_InsertCompleted(object sender, System.ComponentModel.AsyncCompletedEventArgs e)
{
tbResult.Text = "添加成功！";
}
void btnGetData_Click(object sender, RoutedEventArgs e)
{
GetEmployees();
}
void MainPage_Loaded(object sender, RoutedEventArgs e)
{
GetEmployees();
}
void GetEmployees()
{
EmployeesInfoWebServiceSoapClient webClient = new EmployeesInfoWebServiceSoapClient();
webClient.GetEmployeesInfoCompleted +=
new EventHandler<GetEmployeesInfoCompletedEventArgs>(webClient_GetEmployeesInfoCompleted);
webClient.GetEmployeesInfoAsync();
}
void webClient_GetEmployeesInfoCompleted(object sender, GetEmployeesInfoCompletedEventArgs e)
{
originalNum = e.Result.Count;//记录原始数据个数
dfEmployee.ItemsSource = e.Result;
}
}
}
最终效果图
文章出处：Kinglee’s Blog (http://www.cnblogs.com/Kinglee/)
版权声明：本文的版权归作者与博客园共有。转载时须注明本文的详细链接，否则作者将保留追究其法律责任。
