search
HomeDatabaseMysql Tutorial教你如何来进行编写通用的数据访问_MySQL

引言

  在负责咨询工作的过去 6 年中,我曾多次听说关于数据访问和操作方面的问题,它时刻困扰着用户:“如何编写应用程序,以便只需对其进行很少的改动或不进行改动即可使用数据库服务器 x、y 和 z?”由于知道数据访问层仍然是现代应用程序的最关键部分,并且通常是经验不足的开发人员的头号敌人,因此我的第一反应始终是:根本办不到!

  面对着人们惶惶不安的面孔以及“使用 Microsoft 在 ADO 中提供的通用数据访问方法如何?”这样的问题,我决定针对此问题提供更详细的说明以及建议的解决方案。

  问题在于,如果应用程序是较小的原型,或者如果并发用户较少并且数据访问逻辑比较简单,那么即使您选择下面这些最简单的方法,也不会遇到任何问题:使用 RAD 工具(如 Data Environment in Microsoft® Visual Basic® 6.0),或某些“一揽子”解决方案(如 ActiveX® Data Control 和其他第三方组件),这些解决方案通常会隐藏应用程序与特定数据源之间进行的复杂交互。然而,当用户数量增加使得必须解决并发操作问题时,由于频繁使用动态记录集、服务器端光标以及不必要的锁定策略,导致出现许多性能问题。为达到用户目标而必须对系统所做的设计和代码更改将花费您大量的时间,因为您从开始时就没有考虑过这一问题。

  使用通用数据访问方法

  在将 ADO 可靠地并入 MDAC(Microsoft Data Access Components 2.1 版)后,Microsoft 掀起了通用数据访问的使用高潮。其主导思想是向开发人员展示,通过使用简单的对象模型(“连接”、“命令”和“记录集”),可以编写出能够与各种不同的数据源(无论是关系数据源还是非关系数据源)连接的应用程序。文档(以及当时的大多数文章和示例)中通常未曾提及的是,即使使用相同的数据访问技术,各种数据源的可编程性和特征也千差万别。

  其结果是,在需要从多个数据源获取数据的应用程序中,最简单的方法是使用所有数据源所提供的功能的“共同点”,但因此会失去使用数据源特定选项的好处,即为访问和操作各种 RDBMS 中的信息提供最佳方法。

  我对该方法始终存在的怀疑是,经过与我的客户进行更详细的分析后,我们通常一致认为与应用程序中处理显示和业务逻辑的其他部分相比,与数据源进行交互的只是应用程序很小的一部分。通过进行精心的模块化设计,可以将 RDBMS 特定代码隔离在一些容易互换的模块中,从而避免对数据访问使用“通用”方法。然而,我们可以使用非常特定的数据访问代码(根据数据源的不同,使用存储过程、命令批处理和其他特性),而不触及其他大多数应用程序代码。这总是提醒大家:正确的设计是编写可移植的有效代码的关键。

    ADO.NET 将一些重要的变化引入到数据访问编码领域,如专用 .NET 数据提供程序这样的概念。使用特定的提供程序,可以绕过为数众多但有时没必要的一系列软件接口和服务(它们是 OLE DB 和 ODBC 层在数据访问代码与数据库服务器之间插入的内容),从而以最佳方式连接到数据源。但每个数据源仍然存在不同的特征和特性(具有不同的 SQL Dialect),且编写高效的应用程序仍然必须使用这些特定特征而不是“共同点”。从可移植性观点看来,托管和非托管的数据访问技术仍然非常类似。

  除“利用数据源的唯一特征”外,编写良好数据访问层所必需的其他规则对每个数据源通常都是相同的:

  • 在可能的情况下使用连接池机制。

  • 节约使用数据库服务器的有限资源。

  • 注意网络的往返。

  • 在适当的情况下,增强执行计划的重复使用率并避免重复编译。

  • 使用适当的锁定模型管理并发性。

  从我使用模块化设计方法的个人经验来看,整个应用程序中专用于处理特定数据源的代码量不会超过总量的 10%。显而易见,这比仅仅更改配置文件中的连接字符串更复杂,但我认为,这样做会获得性能收益,因此这是一个可接受的折衷办法。

  使用基本接口

  此处的目标是使用抽象,并将特定于特殊数据源的代码封装在类层中,从而使应用程序的其他部分独立于后端数据库服务器或免受其影响。

  .NET Framework 的面向对象这一特性将在该过程中为我们提供帮助,使我们能够选择要使用的抽象级别。选项之一是使用每个 .NET 数据提供程序都必须实现的基本接口(IDbConnection、IDbCommand、IDataReader 等)。另一个选项是创建一组类(数据访问层),用于管理应用程序的所有数据访问逻辑(例如,使用 CRUD 范例)。为检查这两种可能性,我们首先从基于 Northwind 数据库的订单输入应用程序示例入手,然后插入和检索不同数据源中的信息。

  数据提供程序基本接口标识应用程序与数据源进行交互通常所需的典型行为:

  • 定义连接字符串。

  • 打开和关闭与数据源的物理连接。

  • 定义命令和相关参数。

  • 执行可以创建的不同种类的命令。

  • 返回一组数据。

  • 返回标量值。

  • 对数据执行操作但不返回任何内容。

  • 对返回的数据集提供只向前型访问和只读型访问。

  • 定义使数据集与数据源(数据适配器)的内容保持同步所需的一组操作。

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Explain the InnoDB Buffer Pool and its importance for performance.Explain the InnoDB Buffer Pool and its importance for performance.Apr 19, 2025 am 12:24 AM

InnoDBBufferPool reduces disk I/O by caching data and indexing pages, improving database performance. Its working principle includes: 1. Data reading: Read data from BufferPool; 2. Data writing: After modifying the data, write to BufferPool and refresh it to disk regularly; 3. Cache management: Use the LRU algorithm to manage cache pages; 4. Reading mechanism: Load adjacent data pages in advance. By sizing the BufferPool and using multiple instances, database performance can be optimized.

MySQL vs. Other Programming Languages: A ComparisonMySQL vs. Other Programming Languages: A ComparisonApr 19, 2025 am 12:22 AM

Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages ​​such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages ​​have advantages in their respective fields such as data analytics, enterprise applications, and system programming.

Learning MySQL: A Step-by-Step Guide for New UsersLearning MySQL: A Step-by-Step Guide for New UsersApr 19, 2025 am 12:19 AM

MySQL is worth learning because it is a powerful open source database management system suitable for data storage, management and analysis. 1) MySQL is a relational database that uses SQL to operate data and is suitable for structured data management. 2) The SQL language is the key to interacting with MySQL and supports CRUD operations. 3) The working principle of MySQL includes client/server architecture, storage engine and query optimizer. 4) Basic usage includes creating databases and tables, and advanced usage involves joining tables using JOIN. 5) Common errors include syntax errors and permission issues, and debugging skills include checking syntax and using EXPLAIN commands. 6) Performance optimization involves the use of indexes, optimization of SQL statements and regular maintenance of databases.

MySQL: Essential Skills for Beginners to MasterMySQL: Essential Skills for Beginners to MasterApr 18, 2025 am 12:24 AM

MySQL is suitable for beginners to learn database skills. 1. Install MySQL server and client tools. 2. Understand basic SQL queries, such as SELECT. 3. Master data operations: create tables, insert, update, and delete data. 4. Learn advanced skills: subquery and window functions. 5. Debugging and optimization: Check syntax, use indexes, avoid SELECT*, and use LIMIT.

MySQL: Structured Data and Relational DatabasesMySQL: Structured Data and Relational DatabasesApr 18, 2025 am 12:22 AM

MySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.

MySQL: Key Features and Capabilities ExplainedMySQL: Key Features and Capabilities ExplainedApr 18, 2025 am 12:17 AM

MySQL is an open source relational database management system that is widely used in Web development. Its key features include: 1. Supports multiple storage engines, such as InnoDB and MyISAM, suitable for different scenarios; 2. Provides master-slave replication functions to facilitate load balancing and data backup; 3. Improve query efficiency through query optimization and index use.

The Purpose of SQL: Interacting with MySQL DatabasesThe Purpose of SQL: Interacting with MySQL DatabasesApr 18, 2025 am 12:12 AM

SQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.

MySQL for Beginners: Getting Started with Database ManagementMySQL for Beginners: Getting Started with Database ManagementApr 18, 2025 am 12:10 AM

The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

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

Hot Tools

Atom editor mac version download

Atom editor mac version download

The most popular open source editor

SublimeText3 Linux new version

SublimeText3 Linux new version

SublimeText3 Linux latest version

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

SublimeText3 English version

SublimeText3 English version

Recommended: Win version, supports code prompts!

SAP NetWeaver Server Adapter for Eclipse

SAP NetWeaver Server Adapter for Eclipse

Integrate Eclipse with SAP NetWeaver application server.