[原创]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;");
Conn.Open();
OleDbCommand Cmd = new OleDbCommand();
Cmd.Connection =
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;");
Conn.Open();
Cmd.Connection =
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 EventHandler
}
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 EventHandler
webClient.InsertAsync(returnValues);
//必须考虑数据集中既有添加又有更新的情况
returnValues.Clear();
updateValues.ForEach(x => returnValues.Add(x));
webClient.UpdateCompleted += new EventHandler
webClient.UpdateAsync(returnValues);
}
else if (updateValues.Count
{
//删除数据
EmployeesInfoWebServiceSoapClient webClient = new EmployeesInfoWebServiceSoapClient();
webClient.DeleteCompleted += new EventHandler
webClient.DeleteAsync(deletedID);
}
else
{
//更新数据
updateValues.ForEach(x => returnValues.Add(x));
EmployeesInfoWebServiceSoapClient webClient = new EmployeesInfoWebServiceSoapClient();
webClient.UpdateCompleted += new EventHandler
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/)

MySQLdiffersfromotherSQLdialectsinsyntaxforLIMIT,auto-increment,stringcomparison,subqueries,andperformanceanalysis.1)MySQLusesLIMIT,whileSQLServerusesTOPandOracleusesROWNUM.2)MySQL'sAUTO_INCREMENTcontrastswithPostgreSQL'sSERIALandOracle'ssequenceandt

MySQL partitioning improves performance and simplifies maintenance. 1) Divide large tables into small pieces by specific criteria (such as date ranges), 2) physically divide data into independent files, 3) MySQL can focus on related partitions when querying, 4) Query optimizer can skip unrelated partitions, 5) Choosing the right partition strategy and maintaining it regularly is key.

How to grant and revoke permissions in MySQL? 1. Use the GRANT statement to grant permissions, such as GRANTALLPRIVILEGESONdatabase_name.TO'username'@'host'; 2. Use the REVOKE statement to revoke permissions, such as REVOKEALLPRIVILEGESONdatabase_name.FROM'username'@'host' to ensure timely communication of permission changes.

InnoDB is suitable for applications that require transaction support and high concurrency, while MyISAM is suitable for applications that require more reads and less writes. 1.InnoDB supports transaction and bank-level locks, suitable for e-commerce and banking systems. 2.MyISAM provides fast read and indexing, suitable for blogging and content management systems.

There are four main JOIN types in MySQL: INNERJOIN, LEFTJOIN, RIGHTJOIN and FULLOUTERJOIN. 1.INNERJOIN returns all rows in the two tables that meet the JOIN conditions. 2.LEFTJOIN returns all rows in the left table, even if there are no matching rows in the right table. 3. RIGHTJOIN is contrary to LEFTJOIN and returns all rows in the right table. 4.FULLOUTERJOIN returns all rows in the two tables that meet or do not meet JOIN conditions.

MySQLoffersvariousstorageengines,eachsuitedfordifferentusecases:1)InnoDBisidealforapplicationsneedingACIDcomplianceandhighconcurrency,supportingtransactionsandforeignkeys.2)MyISAMisbestforread-heavyworkloads,lackingtransactionsupport.3)Memoryengineis

Common security vulnerabilities in MySQL include SQL injection, weak passwords, improper permission configuration, and unupdated software. 1. SQL injection can be prevented by using preprocessing statements. 2. Weak passwords can be avoided by forcibly using strong password strategies. 3. Improper permission configuration can be resolved through regular review and adjustment of user permissions. 4. Unupdated software can be patched by regularly checking and updating the MySQL version.

Identifying slow queries in MySQL can be achieved by enabling slow query logs and setting thresholds. 1. Enable slow query logs and set thresholds. 2. View and analyze slow query log files, and use tools such as mysqldumpslow or pt-query-digest for in-depth analysis. 3. Optimizing slow queries can be achieved through index optimization, query rewriting and avoiding the use of SELECT*.


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

mPDF
mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

Dreamweaver CS6
Visual web development tools

VSCode Windows 64-bit Download
A free and powerful IDE editor launched by Microsoft

Atom editor mac version download
The most popular open source editor

Safe Exam Browser
Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.
