详解于ADO .NET 类操作数据("深入解析ADO .NET类操作数据技巧")

原创

ithorizon 6个月前 (10-19) 阅读数 24 #后端开发

深入解析ADO .NET类操作数据技巧

一、概述

ADO .NET 是一种用于在.NET环境中访问数据库的技术。它提供了一组充裕的类，让操作数据库变得更加易懂。本文将详细介绍怎样使用 ADO .NET 类操作数据，包括连接数据库、执行 SQL 命令、读取数据、插入、更新和删除数据等技巧。

二、连接数据库

要操作数据库，首先需要产生与数据库的连接。在 ADO .NET 中，使用 Connection 类来管理数据库连接。

2.1 使用SqlConnection连接SQL Server数据库


using System;
using System.Data.SqlClient;
class Program
{
    static void Main()
    {
        string connectionString = "server=localhost;database=MyDatabase;uid=sa;pwd=123456";
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            Console.WriteLine("数据库连接顺利！");
        }
    }
}

2.2 使用OleDbConnection连接其他数据库


using System;
using System.Data.OleDb;
class Program
{
    static void Main()
    {
        string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\path\\to\\database.mdb";
        using (OleDbConnection connection = new OleDbConnection(connectionString))
        {
            connection.Open();
            Console.WriteLine("数据库连接顺利！");
        }
    }
}

三、执行SQL命令

在 ADO .NET 中，使用 Command 类来执行 SQL 命令。以下是怎样使用 Command 类执行 SQL 命令的示例。

3.1 执行查询命令


using System;
using System.Data.SqlClient;
class Program
{
    static void Main()
    {
        string connectionString = "server=localhost;database=MyDatabase;uid=sa;pwd=123456";
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            string query = "SELECT * FROM Users";
            using (SqlCommand command = new SqlCommand(query, connection))
            {
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Console.WriteLine(reader["Name"] + ", " + reader["Age"]);
                }
            }
        }
    }
}

3.2 执行插入、更新和删除命令


using System;
using System.Data.SqlClient;
class Program
{
    static void Main()
    {
        string connectionString = "server=localhost;database=MyDatabase;uid=sa;pwd=123456";
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            string insertCommand = "INSERT INTO Users (Name, Age) VALUES ('张三', 25)";
            using (SqlCommand command = new SqlCommand(insertCommand, connection))
            {
                command.ExecuteNonQuery();
            }
            string updateCommand = "UPDATE Users SET Age = 26 WHERE Name = '张三'";
            using (SqlCommand command = new SqlCommand(updateCommand, connection))
            {
                command.ExecuteNonQuery();
            }
            string deleteCommand = "DELETE FROM Users WHERE Name = '张三'";
            using (SqlCommand command = new SqlCommand(deleteCommand, connection))
            {
                command.ExecuteNonQuery();
            }
        }
    }
}

四、事务处理

在 ADO .NET 中，事务处理用于确保多个操作作为一个整体执行，要么全部顺利，要么全部挫败。以下是怎样使用事务处理来执行多个操作的示例。

4.1 使用事务处理多个操作


using System;
using System.Data.SqlClient;
class Program
{
    static void Main()
    {
        string connectionString = "server=localhost;database=MyDatabase;uid=sa;pwd=123456";
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            SqlTransaction transaction = connection.BeginTransaction();
            try
            {
                string insertCommand = "INSERT INTO Users (Name, Age) VALUES ('李四', 30)";
                using (SqlCommand command = new SqlCommand(insertCommand, connection, transaction))
                {
                    command.ExecuteNonQuery();
                }
                string updateCommand = "UPDATE Users SET Age = 31 WHERE Name = '李四'";
                using (SqlCommand command = new SqlCommand(updateCommand, connection, transaction))
                {
                    command.ExecuteNonQuery();
                }
                transaction.Commit();
                Console.WriteLine("事务提交顺利！");
            }
            catch (Exception)
            {
                transaction.Rollback();
                Console.WriteLine("事务回滚！");
            }
        }
    }
}

五、数据适配器与数据集

在 ADO .NET 中，数据适配器（DataAdapter）和数据集（DataSet）用于填充内存中的数据结构，以便进行数据操作。

5.1 使用DataAdapter填充DataSet


using System;
using System.Data;
using System.Data.SqlClient;
class Program
{
    static void Main()
    {
        string connectionString = "server=localhost;database=MyDatabase;uid=sa;pwd=123456";
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            string query = "SELECT * FROM Users";
            using (SqlCommand command = new SqlCommand(query, connection))
            {
                using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                {
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet, "Users");
                    DataTable table = dataSet.Tables["Users"];
                    foreach (DataRow row in table.Rows)
                    {
                        Console.WriteLine(row["Name"] + ", " + row["Age"]);
                    }
                }
            }
        }
    }
}

5.2 使用DataAdapter更新数据库


using System;
using System.Data;
using System.Data.SqlClient;
class Program
{
    static void Main()
    {
        string connectionString = "server=localhost;database=MyDatabase;uid=sa;pwd=123456";
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            string query = "SELECT * FROM Users";
            using (SqlCommand command = new SqlCommand(query, connection))
            {
                using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                {
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet, "Users");
                    DataTable table = dataSet.Tables["Users"];
                    // 修改DataSet中的数据
                    table.Rows[0]["Age"] = 32;
                    // 更新数据库
                    SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
                    adapter.Update(dataSet, "Users");
                }
            }
        }
    }
}

六、异步操作

在 ADO .NET 中，可以使用异步操作来尽或许减少损耗应用程序的性能。以下是怎样使用异步操作执行 SQL 命令的示例。

6.1 使用异步操作执行查询命令


using System;
using System.Data.SqlClient;
using System.Threading.Tasks;
class Program
{
    static async Task Main()
    {
        string connectionString = "server=localhost;database=MyDatabase;uid=sa;pwd=123456";
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            await connection.OpenAsync();
            string query = "SELECT * FROM Users";
            using (SqlCommand command = new SqlCommand(query, connection))
            {
                SqlDataReader reader = await command.ExecuteReaderAsync();
                while (await reader.ReadAsync())
                {
                    Console.WriteLine(reader["Name"] + ", " + reader["Age"]);
                }
            }
        }
    }
}