详解Silverlight与Access互操作的具体实现

Silverlight与Access互操作是一个很基础的问题，主要涉及到数据库的操作。Access属于轻量级的数据库，应用起来还是比较方便的。创新互联推荐《
走向银光 —— 一步一步学Silverlight》

成都创新互联自2013年起，先为宝兴等服务建站，宝兴等地企业，进行企业商务咨询服务。为宝兴企业网站制作PC+手机+微官网三网同步一站式服务解决您的所有建站问题。

在开发一些小型应用程序时，我们就需要使用一些小巧的轻量级的数据库，比如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;"); 
  
  
  
              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<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 = "更新成功！"; 
  
  
  
          } 
  
  
  
          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; 
  
  
  
          } 
  
  
  
      } 
  
  
  
  }

最终效果图

链接：http://www.cnblogs.com/Kinglee/archive/2009/09/05/1561021.html

上一篇：wincc报错code13

下一篇：内网服务器是什么？外网服务器指什么意思