连接类 连接类有固定的使用模式,这是常用的乐观模式: using (var conn = new SqlConnection(connstr)){conn.Open();//执行各种数据库操作} 悲观的、防御性的编程方式,这里在using语句结尾显示关闭了连接,并捕捉整个using语句的数据库异常: try{using (v
连接类
连接类有固定的使用模式,这是常用的乐观模式:
using (var conn = new SqlConnection(connstr)) { conn.Open(); //执行各种数据库操作 }悲观的、防御性的编程方式,这里在using语句结尾显示关闭了连接,并捕捉整个using语句的数据库异常:
try { using (var conn = new SqlConnection(connstr)) { conn.Open(); //执行各种数据库操作 conn.Close(); } } catch (SqlException e) { throw; } 事务当使用多条语句修改数据库是,应将所有这些语句看作一个单元,要么完全成功修改数据库(提交),香港空间,要么完全失败任何一条修改语句都不会被执行(回滚)。要想使用事务,先要引用System.Transactions装配体。事务的代码如下所示:
using (var scope = new TransactionScope()) { using (var conn = new SqlConnection(connstr)) { conn.Open(); // 执行各种数据库操作 } scope.Complete(); }在这里,事务范围使用了默认构造函数,它意味着TransactionScopeOption.Required、IsolationLevel.Serializable、TimeOut=1分钟。
事务通过使用scope.Complete显式标记为完成。缺少这个调用,事务将被回滚。
事务范围内可以有多个连接,都被看作一个整体。
事务的隔离级别:
ReadCommitted 读取已经提交的数据
ReadUncommitted 读取还没有提交的数据
RepeatableRead
Serializable
命令打开数据库连接之后,需要定义要执行的命令。对Sql Server可用的命令类型有两种:文本、和存储过程。
定义文本类型命令的方法如下。因为文本类型的命令是默认值,虚拟主机,所以不需要显式指定命令类型:
var sql = "SELECT ContactName FROM Customers"; var cmd = new SqlCommand(sql, conn);定义存储过程类型的命令:
var cmd = new SqlCommand("CustOrderHist", conn) { CommandType = CommandType.StoredProcedure }; cmd.Parameters.AddWithValue("@CustomerID", "QUICK"); 执行命令定义了命令之后,需要执行它。根据返回结果不同分成三种执行方法。
ExecuteNonQuery方法一般用于UPDATE,INSERT,或DELETE语句。它执行命令,并返回受影响的行数。
打开连接后,添加如下代码:
var cmd = new SqlCommand("CustOrderHist", conn) { CommandType = CommandType.StoredProcedure }; cmd.Parameters.AddWithValue("@CustomerID", "QUICK"); var rows = cmd.ExecuteNonQuery();ExecuteReader方法执行命令,并返回一个数据阅读器对象。在打开连接语句之后,添加如下代码:
var sql = "SELECT ContactName,CompanyName FROM Customers"; var cmd = new SqlCommand(sql, conn); var reader = cmd.ExecuteReader(); while (reader.Read()) { Console.WriteLine("Contact: {0,-20} Company: {1}", reader[0], reader[1]); }ExecuteScalar执行返回一个标量值的命令。在打开连接语句之后,添加如下代码:
var sql = "SELECT COUNT(*) FROM Customers"; var cmd = new SqlCommand(sql, conn); var custs = (int)cmd.ExecuteScalar(); Console.WriteLine(custs);ExecuteScalar方法返回一个object对象,需要将其转化为实际的类型。
调用存储过程 调用不返回值的存储过程这里列出两个例子,使用了Northwind数据库的Region表。
更新记录存储过程定义如下:
CREATE PROCEDURE RegionUpdate (@RegionID INTEGER, @RegionDescription NCHAR(50)) AS SET NOCOUNT OFF UPDATE Region SET RegionDescription = @RegionDescription WHERE RegionID = @RegionID GO如前所述,定义一个命令,并添加他的参数和参数值:
var cmd = new SqlCommand("RegionUpdate", conn) { CommandType = CommandType.StoredProcedure }; cmd.Parameters.AddWithValue("@RegionID", 23); cmd.Parameters.AddWithValue("@RegionDescription", "Something"); cmd.ExecuteNonQuery(); 删除记录存储过程定义如下:
CREATE PROCEDURE RegionDelete (@RegionID INTEGER) AS SET NOCOUNT OFF DELETE FROM Region WHERE RegionID = @RegionID GO在命令定义部分,使用SqlParameter构造函数来构造命令参数:
var cmd = new SqlCommand("RegionDelete", conn) { CommandType = CommandType.StoredProcedure }; cmd.Parameters.Add(new SqlParameter("@RegionID", SqlDbType.Int, 0, "RegionID")); cmd.UpdatedRowSource = UpdateRowSource.None;构造完命令并添加参数定义之后,通过参数名字检索出参数并设置它的值,然后执行命令:
cmd.Parameters["@RegionID"].Value = 999; cmd.ExecuteNonQuery();也可以通过参数位置检索参数。
调用返回输出参数的存储过程下面的例子演示插入一个记录到数据库并且返回该记录的主键给调用者。
CREATE PROCEDURE RegionInsert(@RegionDescription NCHAR(50), @RegionID INTEGER OUTPUT)AS SET NOCOUNT OFF SELECT @RegionID = MAX(RegionID)+ 1 FROM Region INSERT INTO Region(RegionID, RegionDescription) VALUES(@RegionID, @RegionDescription) GO插入过程创造一个新的Region记录。因为主键由数据库自己生成,这值被作为一个输出参数从过程(@RegionID)返回。下面代码显示我们将如何调用RegionInsert存储过程:
var cmd = new SqlCommand("RegionInsert", conn) { CommandType = CommandType.StoredProcedure }; cmd.Parameters.Add(new SqlParameter("@RegionDescription", SqlDbType.NChar, 50, "RegionDescription")); cmd.Parameters.Add(new SqlParameter("@RegionID", SqlDbType.Int, 0, ParameterDirection.Output, false, 0, 0, "RegionID", DataRowVersion.Default, null)); cmd.UpdatedRowSource = UpdateRowSource.OutputParameters;第二参数,@RegionID,香港服务器,包括它的参数方向,在这个例子中是Output。代码的最后一行UpdateRowSource枚举被用来指明数据将从这个存储过程通过输出参数返回。

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.

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.

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 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 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 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.

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.

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


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

MantisBT
Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

Dreamweaver Mac version
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

PhpStorm Mac version
The latest (2018.2.1) professional PHP integrated development tool

WebStorm Mac version
Useful JavaScript development tools