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

1、Good is good, but better carries it.精益求精，善益求善。Silverlight访问数据库之Access数据库银光中国Silverlight资源社区论坛Silverlight访问数据库之Access数据库时间:2009-12-1022:21来源:博客园作者:Kinglee点击:3540次Silverlight与SQLServer或SQLServerExpress的互操作，已成为我们常见的开发Siverlight应用程序的一种模式。可是在开发一些小型应用程序时，我们就需要使用一些小巧的轻量级的数据库，比如Access数据库。由于VisualStudio中并没有直

2、接提供Silverlight与Access互操作的系列方法。于是本文就将为大家介绍Silverlight与SQLServer或SQLServerExpress的互操作，已成为我们常见的开发Siverlight应用程序的一种模式。可是在开发一些小型应用程序时，我们就需要使用一些小巧的轻量级的数据库，比如Access数据库。由于VisualStudio中并没有直接提供Silverlight与Access互操作的系列方法。于是本文就将为大家介绍如何让Silverlight使用Access作为后台数据库。准备工作1）建立起测试项目细节详情请见强大的DataGrid组件2_数据交互之ADO.NETEnt

3、ityFrameworkSilverlight学习笔记10。2）创建测试用数据库如下图所示，创建一个名为Employees.mdb的Access数据库，建立数据表名称为Employee。将该数据库置于作为服务端的项目文件夹下的App_Data文件夹中，便于操作管理。建立数据模型EmployeeModel.cs文件（放置在服务端项目文件夹下）usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;namespacedatagridnaccessdbpublicclassEmployeeModelpublicintEmployeeI

4、Dget;set;publicstringEmployeeNameget;set;publicintEmployeeAgeget;set;建立服务端WebService右击服务端项目文件夹，选择Add-NewItem，按下图所示建立一个名为EmployeesInfoWebService.asmx的WebService，作为Silverlight与Access数据库互操作的桥梁。创建完毕后，双击EmployeesInfoWebService.asmx打开该文件。将里面的内容修改如下：usingSystem;usingSystem.Collections.Generic;usingSystem.L

5、inq;usingSystem.Web;usingSystem.Web.Services;usingSystem.Data.OleDb;/引入该命名空间为了操作Access数据库usingSystem.Data;namespacedatagridnaccessdb/SummarydescriptionforEmployeesInfoWebService/WebService(Namespace=/)WebServiceBinding(ConformsTo=WsiProfiles.BasicProfile1_1)System.ComponentModel.ToolboxItem(false)/T

6、oallowthisWebServicetobecalledfromscript,usingASP.NETAJAX,uncommentthefollowingline./System.Web.Script.Services.ScriptServicepublicclassEmployeesInfoWebService:System.Web.Services.WebServiceWebMethod/获取雇员信息publicListGetEmployeesInfo()ListreturnedValue=newList();OleDbCommandCmd=newOleDbCommand();SQLE

7、xcute(SELECT*FROMEmployee,Cmd);OleDbDataAdapterEmployeeAdapter=newOleDbDataAdapter();EmployeeAdapter.SelectCommand=Cmd;DataSetEmployeeDataSet=newDataSet();EmployeeAdapter.Fill(EmployeeDataSet);foreach(DataRowdrinEmployeeDataSet.Tables0.Rows)EmployeeModeltmp=newEmployeeModel();tmp.EmployeeID=Convert.

8、ToInt32(dr0);tmp.EmployeeName=Convert.ToString(dr1);tmp.EmployeeAge=Convert.ToInt32(dr2);returnedValue.Add(tmp);returnreturnedValue;WebMethod/添加雇员信息publicvoidInsert(Listemployee)employee.ForEach(x=stringCmdText=INSERTINTOEmployee(EmployeeName,EmployeeAge)VALUES(+x.EmployeeName+,+x.EmployeeAge.ToStri

9、ng()+);SQLExcute(CmdText););WebMethod/更新雇员信息publicvoidUpdate(Listemployee)employee.ForEach(x=stringCmdText=UPDATEEmployeeSETEmployeeName=+x.EmployeeName+,EmployeeAge=+x.EmployeeAge.ToString();CmdText+=WHEREEmployeeID=+x.EmployeeID.ToString();SQLExcute(CmdText););WebMethod/删除雇员信息publicvoidDelete(List

10、employee)employee.ForEach(x=stringCmdText=DELETEFROMEmployeeWHEREEmployeeID=+x.EmployeeID.ToString();SQLExcute(CmdText););/执行SQL命令文本，重载1privatevoidSQLExcute(stringSQLCmd)stringConnectionString=PROVIDER=Microsoft.Jet.OLEDB.4.0;DATASOURCE=+Server.MapPath(App_DataEmployees.mdb;);OleDbConnectionConn=new

11、OleDbConnection(ConnectionString);Conn.Open();OleDbCommandCmd=newOleDbCommand();Cmd.Connection=Conn;Cmd.CommandTimeout=15;Cmd.CommandType=CommandType.Text;Cmd.CommandText=SQLCmd;Cmd.ExecuteNonQuery();Conn.Close();/执行SQL命令文本，重载2privatevoidSQLExcute(stringSQLCmd,OleDbCommandCmd)stringConnectionString=

12、PROVIDER=Microsoft.Jet.OLEDB.4.0;DATASOURCE=+Server.MapPath(App_DataEmployees.mdb;);OleDbConnectionConn=newOleDbConnection(ConnectionString);Conn.Open();Cmd.Connection=Conn;Cmd.CommandTimeout=15;Cmd.CommandType=CommandType.Text;Cmd.CommandText=SQLCmd;Cmd.ExecuteNonQuery();之后，在Silverlight客户端应用程序文件夹下，

13、右击References文件夹，选择菜单选项AddServiceReference.。如下图所示，引入刚才我们创建的WebService（别忘了按Discover按钮进行查找）。创建Silverlight客户端应用程序MainPage.xaml文件MainPage.xaml.cs文件usingSystem;usingSystem.Collections.Generic;usingSystem.Collections.ObjectModel;usingSystem.Linq;usingSystem.Net;usingSystem.Windows;usingSystem.Windows.Contr

14、ols;usingSystem.Windows.Documents;usingSystem.Windows.Input;usingSystem.Windows.Media;usingSystem.Windows.Media.Animation;usingSystem.Windows.Shapes;usingSystem.Xml;usingSystem.Xml.Linq;usingSystem.Windows.Browser;usingSilverlightClient.EmployeesInfoServiceReference;namespaceSilverlightClientpublicp

15、artialclassMainPage:UserControlintoriginalNum;/记录初始时的Employee表中的数据总数ObservableCollectiondeletedID=newObservableCollection();/标记被删除的对象publicMainPage()InitializeComponent();this.Loaded+=newRoutedEventHandler(MainPage_Loaded);this.btnGetData.Click+=newRoutedEventHandler(btnGetData_Click);this.btnSaveAl

16、l.Click+=newRoutedEventHandler(btnSaveAll_Click);this.dfEmployee.DeletingItem+=newEventHandler(dfEmployee_DeletingItem);voiddfEmployee_DeletingItem(objectsender,System.ComponentModel.CancelEventArgse)deletedID.Add(dfEmployee.CurrentItemasEmployeeModel);/正在删除时，将被删除对象进行标记，以便传给服务端真正删除。voidbtnSaveAll_Cl

17、ick(objectsender,RoutedEventArgse)ListupdateValues=dfEmployee.ItemsSource.Cast().ToList();ObservableCollectionreturnValues=newObservableCollection();if(updateValues.CountoriginalNum)/添加数据for(inti=originalNum;i=updateValues.Count-1;i+)returnValues.Add(updateValues.ToArray()i);EmployeesInfoWebServiceS

18、oapClientwebClient=newEmployeesInfoWebServiceSoapClient();webClient.InsertCompleted+=newEventHandler(webClient_InsertCompleted);webClient.InsertAsync(returnValues);/必须考虑数据集中既有添加又有更新的情况returnValues.Clear();updateValues.ForEach(x=returnValues.Add(x);webClient.UpdateCompleted+=newEventHandler(webClient

19、_UpdateCompleted);webClient.UpdateAsync(returnValues);elseif(updateValues.CountoriginalNum)/删除数据EmployeesInfoWebServiceSoapClientwebClient=newEmployeesInfoWebServiceSoapClient();webClient.DeleteCompleted+=newEventHandler(webClient_DeleteCompleted);webClient.DeleteAsync(deletedID);else/更新数据updateValu

20、es.ForEach(x=returnValues.Add(x);EmployeesInfoWebServiceSoapClientwebClient=newEmployeesInfoWebServiceSoapClient();webClient.UpdateCompleted+=newEventHandler(webClient_UpdateCompleted);webClient.UpdateAsync(returnValues);voidwebClient_UpdateCompleted(objectsender,System.ComponentModel.AsyncCompletedEventArgse)tbResult.Text=更新成功！;GetEmployees();/更新originalNum防止数据的重复插入，感谢园友紫色永恒的及时指出！voidwebClient_DeleteCompleted(objectsender,System.Compo