Silverlight 与 SQL Server 或 SQL Server Express 的互操作，已成为我们常见的开发 Siverlight 应用程序的一种模式。可是在开发一些小型应用程序时，我们就需要使用一些小巧的轻量级的数据库，比如 Access 数据库。由于 Visual Studio 中并没有直接提供 Sil
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 description for EmployeesInfoWebService
///
[ 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;" );
OleDbConnection Conn = new OleDbConnection (ConnectionString);
Conn.Open();
OleDbCommand Cmd = new OleDbCommand ();
Cmd.Connection = Conn ;
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;" );
OleDbConnection Conn = new OleDbConnection (ConnectionString);
Conn.Open();
Cmd.Connection = Conn ;
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 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
{
returnValues.Add(updateValues.ToArray()[i]);
}
EmployeesInfoWebServiceSoapClient webClient = new EmployeesInfoWebServiceSoapClient ();
webClient.InsertCompleted += new EventHandler AsyncCompletedEventArgs >(webClient_InsertCompleted);
webClient.InsertAsync(returnValues);
// 必须考虑数据集中既有添加又有更新的情况
returnValues.Clear();
updateValues.ForEach(x => returnValues.Add(x));
webClient.UpdateCompleted += new EventHandler AsyncCompletedEventArgs >(webClient_UpdateCompleted);
webClient.UpdateAsync(returnValues);
}
else if (updateValues.Count
{
// 删除数据
EmployeesInfoWebServiceSoapClient webClient = new EmployeesInfoWebServiceSoapClient ();
webClient.DeleteCompleted += new EventHandler AsyncCompletedEventArgs >(webClient_DeleteCompleted);
webClient.DeleteAsync(deletedID);
}
else
{
// 更新数据
updateValues.ForEach(x => returnValues.Add(x));
EmployeesInfoWebServiceSoapClient webClient = new EmployeesInfoWebServiceSoapClient ();
webClient.UpdateCompleted += new EventHandler 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
文章出处：Kinglee’s Blog (http://www.cnblogs.com/Kinglee/)
查看更多关于Silverlight与Access数据库的互操作(CURD完全解析)的详细内容...