Heim > Datenbank > MySQL-Tutorial > [原创]Silverlight与Access数据库的互操作(CURD完全解析)

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

WBOY
Freigeben: 2016-06-07 15:38:45
Original
1190 Leute haben es durchsucht

Silverlight 与 SQL Server 或 SQL Server Express 的互操作,已成为我们常见的开发 Siverlight 应用程序的一种模式。可是在开发一些小型应用程序时,我们就需要使用一些小巧的轻量级的数据库,比如 Access 数据库。由于 Visual Studio 中并没有直接提供 Sil

SilverlightSQL ServerSQL Server Express的互操作,已成为我们常见的开发Siverlight应用程序的一种模式。可是在开发一些小型应用程序时,我们就需要使用一些小巧的轻量级的数据库,比如Access数据库。由于Visual Studio中并没有直接提供SilverlightAccess互操作的系列方法。于是本文就将为大家介绍如何让Silverlight使用Access作为后台数据库。

 

准备工作

1)建立起测试项目

细节详情请见强大的DataGrid组件[2]_数据交互之ADO.NET Entity Framework——Silverlight学习笔记[10]

2)创建测试用数据库

如下图所示,创建一个名为Employees.mdbAccess数据库,建立数据表名称为Employee。将该数据库置于作为服务端的项目文件夹下的App_Data文件夹中,便于操作管理。

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

建立数据模型

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.asmxWeb Service,作为SilverlightAccess数据库互操作的桥梁。

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

创建完毕后,双击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与Access数据库的互操作(CURD完全解析) 

创建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;

        }

    }

}

 

最终效果图

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

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

Verwandte Etiketten:
Quelle:php.cn
Erklärung dieser Website
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Beliebte Tutorials
Mehr>
Neueste Downloads
Mehr>
Web-Effekte
Quellcode der Website
Website-Materialien
Frontend-Vorlage