Silverlight访问数据库之Access数据库银光中国Silverlight资源社区论坛

1、Silverlight访问数据库之Access 数据库时间:2009-12-10 22:21来源:博客园 作者:Kinglee 点击: 3540次Silverlight 与 SQL Server 或 SQL Server Express 的互操作，已成为我们常见的开发 Siverlight 应用程序的一种模式。可是在开发一些小型应用程序时，我们就需要使用一些小巧的轻量级的数据库，比如 Access 数据库。由于 Visual Studio 中并没有直接提供 Silverlight 与 Access 互操作的系列方法。于是本文就将为大家介绍Silverlight与SQL Server或SQL S

2、erver Express的互操作，已成为我们常见的开发Siverlight应用程序的一种模式。可是在开发一些小型应用程序时，我们就需要使用一些小巧的轻量级的数据库，比如Access数据库。由于Visual Studio中并没有直接提供Silverlight与Access互操作的系列方法。于是本文就将为大家介绍如何让Silverlight使用Access作为后台数据库。准备工作1）建立起测试项目细节详情请见强大的DataGrid组件2_数据交互之ADO.NET Entity FrameworkSilverlight学习笔记10。2）创建测试用数据库如下图所示，创建一个名为Employees.m

3、db的Access数据库，建立数据表名称为Employee。将该数据库置于作为服务端的项目文件夹下的App_Data文件夹中，便于操作管理。建立数据模型EmployeeModel.cs文件（放置在服务端项目文件夹下）using System;using System.Collections.Generic;using System.Linq;namespace datagridnaccessdbpublic class EmployeeModelpublic int EmployeeID get; set; public string EmployeeName get; set; public

4、 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.We

5、b.Services;using System.Data.OleDb;/引入该命名空间为了操作Access数据库using System.Data;namespace datagridnaccessdb/ / Summary description for EmployeesInfoWebService/ WebService(Namespace = /)WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)System.ComponentModel.ToolboxItem(false)/ To allow this Web Se

6、rvice to be called from script, using ASP.NET AJAX, uncomment the following line. / System.Web.Script.Services.ScriptServicepublic class EmployeesInfoWebService : System.Web.Services.WebServiceWebMethod/获取雇员信息public List GetEmployeesInfo()List returnedValue = new List();OleDbCommand Cmd = new OleDbC

7、ommand();SQLExcute(SELECT * FROM Employee, Cmd);OleDbDataAdapter EmployeeAdapter = new OleDbDataAdapter();EmployeeAdapter.SelectCommand = Cmd;DataSet EmployeeDataSet = new DataSet();EmployeeAdapter.Fill(EmployeeDataSet);foreach (DataRow dr in EmployeeDataSet.Tables0.Rows)EmployeeModel tmp = new Empl

8、oyeeModel();tmp.EmployeeID = Convert.ToInt32(dr0);tmp.EmployeeName = Convert.ToString(dr1);tmp.EmployeeAge = Convert.ToInt32(dr2);returnedValue.Add(tmp);return returnedValue;WebMethod /添加雇员信息public void Insert(List employee)employee.ForEach( x = string CmdText = INSERT INTO Employee(EmployeeName,Emp

9、loyeeAge) VALUES(+x.EmployeeName+,+x.EmployeeAge.ToString()+);SQLExcute(CmdText););WebMethod /更新雇员信息public void Update(List employee)employee.ForEach(x =string CmdText = UPDATE Employee SET EmployeeName=+x.EmployeeName+,EmployeeAge=+x.EmployeeAge.ToString();CmdText += WHERE EmployeeID=+x.EmployeeID.

10、ToString();SQLExcute(CmdText););WebMethod /删除雇员信息public void Delete(List employee)employee.ForEach(x =string CmdText = DELETE FROM Employee WHERE EmployeeID=+x.EmployeeID.ToString();SQLExcute(CmdText););/执行SQL命令文本，重载1private void SQLExcute(string SQLCmd)string ConnectionString = PROVIDER=Microsoft.J

11、et.OLEDB.4.0;DATA SOURCE= + Server.MapPath(App_DataEmployees.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.ExecuteNonQu

12、ery();Conn.Close();/执行SQL命令文本，重载2private void SQLExcute(string SQLCmd,OleDbCommand Cmd)string ConnectionString = PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE= + Server.MapPath(App_DataEmployees.mdb;);OleDbConnection Conn = new OleDbConnection(ConnectionString);Conn.Open();Cmd.Connection = Conn;Cmd.C

13、ommandTimeout = 15;Cmd.CommandType = CommandType.Text;Cmd.CommandText = SQLCmd;Cmd.ExecuteNonQuery();之后，在Silverlight客户端应用程序文件夹下，右击References文件夹，选择菜单选项Add Service Reference.。如下图所示，引入刚才我们创建的Web Service（别忘了按Discover按钮进行查找）。创建Silverlight客户端应用程序MainPage.xaml文件文件using System;using System.Collections.Gener

14、ic;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 S

15、ystem.Xml.Linq;using System.Windows.Browser;using SilverlightClient.EmployeesInfoServiceReference;namespace SilverlightClientpublic partial class MainPage : UserControlint originalNum;/记录初始时的Employee表中的数据总数ObservableCollection deletedID = new ObservableCollection();/标记被删除的对象public MainPage()Initiali

16、zeComponent();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(dfEmployee_DeletingItem);void dfEmployee_Deleting

17、Item(object sender, System.ComponentModel.CancelEventArgs e)deletedID.Add(dfEmployee.CurrentItem as EmployeeModel);/正在删除时，将被删除对象进行标记，以便传给服务端真正删除。void btnSaveAll_Click(object sender, RoutedEventArgs e)List updateValues = dfEmployee.ItemsSource.Cast().ToList();ObservableCollection returnValues = new O

18、bservableCollection();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(webClie

19、nt_InsertCompleted);webClient.InsertAsync(returnValues);/必须考虑数据集中既有添加又有更新的情况returnValues.Clear();updateValues.ForEach(x = returnValues.Add(x);webClient.UpdateCompleted += new EventHandler(webClient_UpdateCompleted);webClient.UpdateAsync(returnValues);else if (updateValues.Count originalNum)/删除数据Empl

20、oyeesInfoWebServiceSoapClient webClient = new EmployeesInfoWebServiceSoapClient();webClient.DeleteCompleted += new EventHandler(webClient_DeleteCompleted);webClient.DeleteAsync(deletedID);else/更新数据updateValues.ForEach(x = returnValues.Add(x);EmployeesInfoWebServiceSoapClient webClient = new Employee

21、sInfoWebServiceSoapClient();webClient.UpdateCompleted += new EventHandler(webClient_UpdateCompleted);webClient.UpdateAsync(returnValues);void webClient_UpdateCompleted(object sender, System.ComponentModel.AsyncCompletedEventArgs e)tbResult.Text = 更新成功！;GetEmployees();/更新originalNum防止数据的重复插入，感谢园友紫色永恒的及时指出！void webClient_DeleteCompleted(object sender, System.ComponentMod