[原创]Silverlight与Access数据库的互操作(CURD完全解析)

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文件夹中，便于操作管理。

 

建立数据模型

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 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;");

            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表中的数据总数

        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 EventHandlerCancelEventArgs>(dfEmployee_DeletingItem);

        }

 

        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 EventHandlerAsyncCompletedEventArgs>(webClient_InsertCompleted);

                webClient.InsertAsync(returnValues);

                //必须考虑数据集中既有添加又有更新的情况

                returnValues.Clear();

                updateValues.ForEach(x => returnValues.Add(x));

                webClient.UpdateCompleted += new EventHandlerAsyncCompletedEventArgs>(webClient_UpdateCompleted);

                webClient.UpdateAsync(returnValues);

            }

            else if (updateValues.Count

            {

                //删除数据

                EmployeesInfoWebServiceSoapClient webClient = new EmployeesInfoWebServiceSoapClient();

                webClient.DeleteCompleted += new EventHandlerAsyncCompletedEventArgs>(webClient_DeleteCompleted);

                webClient.DeleteAsync(deletedID);

            }

            else

            {

                //更新数据

                updateValues.ForEach(x => returnValues.Add(x));

                EmployeesInfoWebServiceSoapClient webClient = new EmployeesInfoWebServiceSoapClient();

                webClient.UpdateCompleted += new EventHandlerAsyncCompletedEventArgs>(webClient_UpdateCompleted);

                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;

        }

    }

}

 

最终效果图

 

作者：Kinglee
文章出处：Kinglee’s Blog (http://www.cnblogs.com/Kinglee/)