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文件夹中，便于操作管理。
![[原创]Silverlight与Access数据库的互操作(CURD完全解析)](/inc/test.jsp?url=http%3A%2F%2Fimages.cnblogs.com%2Fcnblogs_com%2Fkinglee%2Fmyimg%2Fsilverlightaccessdb1.PNG&refer=http%3A%2F%2Fwww.cnblogs.com%2FKinglee%2Farchive%2F2009%2F09%2F05%2F1561021.html)
建立数据模型
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数据库互操作的桥梁。
![[原创]Silverlight与Access数据库的互操作(CURD完全解析)](/inc/test.jsp?url=http%3A%2F%2Fimages.cnblogs.com%2Fcnblogs_com%2Fkinglee%2Fmyimg%2Fsilverlightaccessdb2.PNG&refer=http%3A%2F%2Fwww.cnblogs.com%2FKinglee%2Farchive%2F2009%2F09%2F05%2F1561021.html)
创建完毕后，双击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 ListEmployeeModel> GetEmployeesInfo()
{
ListEmployeeModel> returnedValue = new ListEmployeeModel>();
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(ListEmployeeModel> employee)
{
employee.ForEach( x =>
{
string CmdText = "INSERT INTO Employee(EmployeeName,EmployeeAge) VALUES('"+x.EmployeeName+"',"+x.EmployeeAge.ToString()+")";
SQLExcute(CmdText);
});
}
[WebMethod] //更新雇员信息
public void Update(ListEmployeeModel> 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(ListEmployeeModel> 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与Access数据库的互操作(CURD完全解析)](/inc/test.jsp?url=http%3A%2F%2Fimages.cnblogs.com%2Fcnblogs_com%2Fkinglee%2Fmyimg%2Fsilverlightaccessdb3.PNG&refer=http%3A%2F%2Fwww.cnblogs.com%2FKinglee%2Farchive%2F2009%2F09%2F05%2F1561021.html)
创建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表中的数据总数
ObservableCollectionEmployeeModel> deletedID = new ObservableCollectionEmployeeModel>();//标记被删除的对象
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
}
void dfEmployee_DeletingItem(object sender, System.ComponentModel.CancelEventArgs e)
{
deletedID.Add(dfEmployee.CurrentItem as EmployeeModel);//正在删除时，将被删除对象进行标记，以便传给服务端真正删除。
}
void btnSaveAll_Click(object sender, RoutedEventArgs e)
{
ListEmployeeModel> updateValues = dfEmployee.ItemsSource.CastEmployeeModel>().ToList();
ObservableCollectionEmployeeModel> returnValues = new ObservableCollectionEmployeeModel>();
if (updateValues.Count > originalNum)
{
//添加数据
for (int i = originalNum; i
{
returnValues.Add(updateValues.ToArray()[i]);
}
EmployeesInfoWebServiceSoapClient webClient = new EmployeesInfoWebServiceSoapClient();
webClient.InsertCompleted += new EventHandler
webClient.InsertAsync(returnValues);
//必须考虑数据集中既有添加又有更新的情况
returnValues.Clear();
updateValues.ForEach(x => returnValues.Add(x));
webClient.UpdateCompleted += new EventHandler
webClient.UpdateAsync(returnValues);
}
else if (updateValues.Count
{
//删除数据
EmployeesInfoWebServiceSoapClient webClient = new EmployeesInfoWebServiceSoapClient();
webClient.DeleteCompleted += new EventHandler
webClient.DeleteAsync(deletedID);
}
else
{
//更新数据
updateValues.ForEach(x => returnValues.Add(x));
EmployeesInfoWebServiceSoapClient webClient = new EmployeesInfoWebServiceSoapClient();
webClient.UpdateCompleted += new EventHandler
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 EventHandlerGetEmployeesInfoCompletedEventArgs>(webClient_GetEmployeesInfoCompleted);
webClient.GetEmployeesInfoAsync();
}
void webClient_GetEmployeesInfoCompleted(object sender, GetEmployeesInfoCompletedEventArgs e)
{
originalNum = e.Result.Count;//记录原始数据个数
dfEmployee.ItemsSource = e.Result;
}
}
}
最终效果图
![[原创]Silverlight与Access数据库的互操作(CURD完全解析)](/inc/test.jsp?url=http%3A%2F%2Fimages.cnblogs.com%2Fcnblogs_com%2Fkinglee%2Fmyimg%2Fsilverlightaccessdb4.PNG&refer=http%3A%2F%2Fwww.cnblogs.com%2FKinglee%2Farchive%2F2009%2F09%2F05%2F1561021.html)
作者：Kinglee
文章出处：Kinglee’s Blog (http://www.cnblogs.com/Kinglee/)
Datenbank drei Paradigmen
So löschen Sie eine Datenbank
So stellen Sie eine Verbindung zur Datenbank in VB her
MySQL-Datenbank wiederherstellen
So stellen Sie eine Verbindung zum Zugriff auf die Datenbank in VB her
So lösen Sie das Problem eines ungültigen Datenbankobjektnamens
So verbinden Sie VB mit dem Zugriff auf die Datenbank
So verbinden Sie ASP mit dem Zugriff auf die Datenbank
