Silverlight與Access互操作是一個(gè)很基礎(chǔ)的問題，主要涉及到數(shù)據(jù)庫的操作。Access屬于輕量級(jí)的數(shù)據(jù)庫，應(yīng)用起來還是比較方便的。51CTO編輯推薦《走向銀光 —— 一步一步學(xué)Silverlight》

在開發(fā)一些小型應(yīng)用程序時(shí)，我們就需要使用一些小巧的輕量級(jí)的數(shù)據(jù)庫，比如Access數(shù)據(jù)庫。由于Visual Studio中并沒有直接提供Silverlight與Access互操作的系列方法。于是本文就將為大家介紹如何讓Silverlight使用Access作為后臺(tái)數(shù)據(jù)庫。

準(zhǔn)備工作

1）建立起測試項(xiàng)目

細(xì)節(jié)詳情請(qǐng)見強(qiáng)大的DataGrid組件[2]_數(shù)據(jù)交互之ADO.NET Entity Framework——Silverlight學(xué)習(xí)筆記[10]。

2）創(chuàng)建測試用數(shù)據(jù)庫

如下圖所示，創(chuàng)建一個(gè)名為Employees.mdb的Access數(shù)據(jù)庫，建立數(shù)據(jù)表名稱為Employee。將該數(shù)據(jù)庫置于作為服務(wù)端的項(xiàng)目文件夾下的App_Data文件夾中，便于操作管理。

 

建立數(shù)據(jù)模型

EmployeeModel.cs文件（放置在服務(wù)端項(xiàng)目文件夾下）

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; }  
    }  
} 

建立服務(wù)端Web Service★

右擊服務(wù)端項(xiàng)目文件夾，選擇Add->New Item....，按下圖所示建立一個(gè)名為EmployeesInfoWebService.asmx的Web Service，作為Silverlight與Access數(shù)據(jù)庫互操作的橋梁。

創(chuàng)建完畢后，雙擊EmployeesInfoWebService.asmx打開該文件。將里面的內(nèi)容修改如下：

using System;  
using System.Collections.Generic;  
using System.Linq;  
using System.Web;  
using System.Web.Services;  
using System.Data.OleDb;//引入該命名空間為了操作Access數(shù)據(jù)庫  
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);  
            });  
        }  
       //執(zhí)行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();  
        }  
        //執(zhí)行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客戶端應(yīng)用程序文件夾下，右擊References文件夾，選擇菜單選項(xiàng)Add Service Reference...。如下圖所示，引入剛才我們創(chuàng)建的Web Service（別忘了按Discover按鈕進(jìn)行查找）。

創(chuàng)建Silverlight客戶端應(yīng)用程序

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;//記錄初始時(shí)的Employee表中的數(shù)據(jù)總數(shù)  
        ObservableCollection<EmployeeModel> deletedID = new ObservableCollection<EmployeeModel>();//標(biāo)記被刪除的對(duì)象  
       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);//正在刪除時(shí)，將被刪除對(duì)象進(jìn)行標(biāo)記，以便傳給服務(wù)端真正刪除。  
        }  
        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)  
            {  
                //添加數(shù)據(jù)  
                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);  
                //必須考慮數(shù)據(jù)集中既有添加又有更新的情況  
                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)  
            {  
                //刪除數(shù)據(jù)  
                EmployeesInfoWebServiceSoapClient webClient = new EmployeesInfoWebServiceSoapClient();  
                webClient.DeleteCompleted += new EventHandler<System.ComponentModel.AsyncCompletedEventArgs>(webClient_DeleteCompleted);  
                webClient.DeleteAsync(deletedID);  
            }  
            else  
            {  
               //更新數(shù)據(jù)  
                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;//記錄原始數(shù)據(jù)個(gè)數(shù)  
            dfEmployee.ItemsSource = e.Result;  
        }  
    }  
} 

最終效果圖

 

原文標(biāo)題：Silverlight與Access數(shù)據(jù)庫的互操作(CURD完全解析)

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

【編輯推薦】

1. Office 2010將使用Silverlight改善用戶體驗(yàn)
2. 微軟.NET平臺(tái)主管談Silverlight企業(yè)級(jí)開發(fā)
3. Flash與Silverlight多領(lǐng)域?qū)崪y對(duì)比
4. 微軟宣稱Silverlight裝機(jī)量超過三億
5. 圖解Silverlight 3的7個(gè)新功能