在开发一些小型应用程序时,我们就需要使用一些小巧的轻量级的数据库,比如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;
- }
- }
- }
最终效果图
原文标题:Silverlight与Access数据库的互操作(CURD完全解析)
链接:http://www.cnblogs.com/Kinglee/archive/2009/09/05/1561021.html
【编辑推荐】