C数据库连接类如何创建？代码示例？

C# 创建数据库连接类代码

在C#中，创建一个用于管理数据库连接的类可以帮助我们简化和集中化数据库操作，以下是一个示例代码，展示了如何创建一个通用的数据库连接类，该类可以用于连接不同类型的数据库（例如SQL Server、MySQL等）。

安装必要的NuGet包

你需要确保安装了必要的NuGet包，对于SQL Server，你可以使用System.Data.SqlClient；对于MySQL，你可以使用MySql.Data。

Install-Package System.Data.SqlClient
Install-Package MySql.Data

创建数据库连接类

下面是一个示例代码，展示了如何创建一个通用的数据库连接类：

using System;
using System.Data;
using System.Data.Common;
using System.Configuration;
public class DatabaseConnection
{
    private string connectionString;
    private DbProviderFactory factory;
    private DbConnection connection;
    public DatabaseConnection(string providerName, string connectionString)
    {
        this.connectionString = connectionString;
        this.factory = DbProviderFactories.GetFactory(providerName);
        this.connection = factory.CreateConnection();
    }
    public void Open()
    {
        if (connection.State != ConnectionState.Open)
        {
            connection.ConnectionString = connectionString;
            connection.Open();
            Console.WriteLine("Database connection opened successfully.");
        }
        else
        {
            Console.WriteLine("Database connection is already open.");
        }
    }
    public void Close()
    {
        if (connection.State == ConnectionState.Open)
        {
            connection.Close();
            Console.WriteLine("Database connection closed successfully.");
        }
        else
        {
            Console.WriteLine("Database connection is not open.");
        }
    }
    public DataTable ExecuteQuery(string query)
    {
        DataTable dataTable = new DataTable();
        using (DbCommand command = factory.CreateCommand())
        {
            command.Connection = connection;
            command.CommandText = query;
            using (DbDataAdapter adapter = factory.CreateDataAdapter())
            {
                adapter.SelectCommand = command;
                adapter.Fill(dataTable);
            }
        }
        return dataTable;
    }
    public int ExecuteNonQuery(string query)
    {
        int rowsAffected = 0;
        using (DbCommand command = factory.CreateCommand())
        {
            command.Connection = connection;
            command.CommandText = query;
            rowsAffected = command.ExecuteNonQuery();
        }
        return rowsAffected;
    }
}

使用示例

以下是如何使用上述DatabaseConnection类的示例：

class Program
{
    static void Main(string[] args)
    {
        string providerName = "System.Data.SqlClient"; // 或者 "MySql.Data.MySqlClient" 用于MySQL
        string connectionString = "your_connection_string_here";
        DatabaseConnection dbConn = new DatabaseConnection(providerName, connectionString);
        dbConn.Open();
        // 执行查询并获取结果
        DataTable table = dbConn.ExecuteQuery("SELECT  FROM YourTable");
        foreach (DataRow row in table.Rows)
        {
            Console.WriteLine(row["YourColumn"].ToString());
        }
        // 执行非查询命令（如插入、更新、删除）
        int rowsAffected = dbConn.ExecuteNonQuery("INSERT INTO YourTable (YourColumn) VALUES ('YourValue')");
        Console.WriteLine($"Rows affected: {rowsAffected}");
        dbConn.Close();
    }
}

相关问答FAQs

Q1: 如何配置连接字符串？

A1: 连接字符串通常包含服务器地址、数据库名称、用户名和密码等信息，以下是SQL Server和MySQL的示例连接字符串：

SQL Server:Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;

MySQL:Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;

Q2: 如何处理数据库连接异常？

A2: 你可以使用try-catch块来捕获和处理数据库连接异常。

try
{
    dbConn.Open();
}
catch (Exception ex)
{
    Console.WriteLine($"Database connection error: {ex.Message}");
}