.NET 幫助

C# MySQL 連線（開發人員的運作方式）

發佈 2024年12月16日

C# MySQL 整合介紹

將 C# 應用程式連接至 MySQL 資料庫，使開發人員能夠利用關聯式資料庫的強大功能，高效地儲存、檢索和管理資料。本指南提供了逐步整合的過程MySQL與 C# 應用程式結合，展示如何使用您 MySQL 資料庫中的數據生成 PDF。IronPDF 庫.

先決條件

要了解此指南，您需要：

Visual Studio 或任何 C# IDE
MySQL 資料庫(已安裝並運行)
IronPDF 程式庫(用於 PDF 生成)

設置 MySQL 資料庫

安裝和配置 MySQL

從下載最新版本的 MySQLmysql.com.
運行安裝程式並按照設置指示操作。選擇「開發者預設」以包含 MySQL Server 和 MySQL Workbench。
在設定過程中配置 MySQL root 使用者憑證，並確保 MySQL 服務正在運行。

建立範例資料庫和資料表

打開 MySQL Workbench 並連接到伺服器。
使用 SQL 命令建立一個新的資料庫和一個範例資料表：

CREATE DATABASE SampleDB;
USE SampleDB;
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY AUTO_INCREMENT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Position VARCHAR(50),
    Salary DECIMAL(10, 2)
);

CREATE DATABASE SampleDB;
USE SampleDB;
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY AUTO_INCREMENT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Position VARCHAR(50),
    Salary DECIMAL(10, 2)
);

CREATE DATABASE SampleDB
Dim SampleDB As USE
CREATE TABLE Employees(EmployeeID INT PRIMARY KEY AUTO_INCREMENT, FirstName VARCHAR(50), LastName VARCHAR(50), Position VARCHAR(50), Salary [DECIMAL](10, 2))

VB C#

插入範例數據：

INSERT INTO Employees (FirstName, LastName, Position, Salary) 
VALUES ('John', 'Doe', 'Software Developer', 80000),
       ('Jane', 'Smith', 'Data Analyst', 75000);

INSERT INTO Employees (FirstName, LastName, Position, Salary) 
VALUES ('John', 'Doe', 'Software Developer', 80000),
       ('Jane', 'Smith', 'Data Analyst', 75000);

INSERT INTO Employees(FirstName, LastName, Position, Salary) VALUES( 'John', 'Doe', 'Software Developer', 80000), ('Jane', 'Smith', 'Data Analyst', 75000);

VB C#

為遠端訪問設置 MySQL 用戶（可選）

要進行遠端存取，請建立一個具有必要許可權的 MySQL 使用者：

CREATE USER 'remoteUser'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON SampleDB.* TO 'remoteUser'@'%';
FLUSH PRIVILEGES;

CREATE USER 'remoteUser'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON SampleDB.* TO 'remoteUser'@'%';
FLUSH PRIVILEGES;

'INSTANT VB TODO TASK: The following line uses invalid syntax:
'CREATE USER 'remoteUser"@"c%' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES @ON SampleDB.* @TO 'remoteUser"@"c%'; FLUSH PRIVILEGES;

VB C#

執行程式，現在它應該看起來像這樣：

C# MySQL 連接（開發人員如何運作）：圖1

將 C# 連接到 MySQL 資料庫

在 C# 中安裝 MySql.Data 庫

為了將 C# 應用程式連接到 MySQL，我們使用 MySQL Connector/NET 函式庫。(通常稱為 Connector/NET). 這是 MySQL 的官方 .NET 驅動程式，可以通過 NuGet 安裝。

開啟 Visual Studio 並建立新的 C# 主控台應用程式。
1. 透過 NuGet 套件管理器新增 MySql.Data 程式庫：
  - 右鍵點擊專案 > 管理 NuGet 套件 > 瀏覽 > 搜尋 MySql.Data 並安裝。

編寫連接代碼

以下代碼範例演示了如何建立與 MySQL 的連接：

using MySql.Data.MySqlClient;
public class Program
{
    // Connection string containing the server, database, user credentials, etc.
    private string connectionString = "Server=localhost;Database=SampleDB;User ID=root;Password=yourpassword;";
    private void Initialize()
    {
        // Create a MySQL connection object
        MySqlConnection connection = new MySqlConnection(connectionString);
        try
        {
            connection.Open();
            Console.WriteLine("Connected to MySQL Database!");
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error: {ex.Message}");
        }
    }
}

using MySql.Data.MySqlClient;
public class Program
{
    // Connection string containing the server, database, user credentials, etc.
    private string connectionString = "Server=localhost;Database=SampleDB;User ID=root;Password=yourpassword;";
    private void Initialize()
    {
        // Create a MySQL connection object
        MySqlConnection connection = new MySqlConnection(connectionString);
        try
        {
            connection.Open();
            Console.WriteLine("Connected to MySQL Database!");
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error: {ex.Message}");
        }
    }
}

Imports MySql.Data.MySqlClient
Public Class Program
	' Connection string containing the server, database, user credentials, etc.
	Private connectionString As String = "Server=localhost;Database=SampleDB;User ID=root;Password=yourpassword;"
	Private Sub Initialize()
		' Create a MySQL connection object
		Dim connection As New MySqlConnection(connectionString)
		Try
			connection.Open()
			Console.WriteLine("Connected to MySQL Database!")
		Catch ex As Exception
			Console.WriteLine($"Error: {ex.Message}")
		End Try
	End Sub
End Class

VB C#

C# MySQL 連線（開發人員如何運作）：圖 3 - 主控台輸出：連線成功

解釋

private string server：代表主機(例如，localhost適用於本地伺服器或遠端連線的 IP 地址).
private string database：表示資料庫名稱(在這種情況下，SampleDB).
private string password：用於驗證 MySQL 使用者的密碼。
private MySqlConnection connection: 一個用來開啟和管理連線的 MySQL connection 物件。
在上面的代碼中，使用 MySqlConnection 來建立與 MySQL 資料庫的連接。

使用 DNS SRV 記錄進行連接（可選）

如果您的應用程式託管在雲端或需要透過 DNS SRV 記錄連接到 MySQL 資料庫，您可以將伺服器名稱替換為解析到資料庫 IP 的相應 DNS 項目。

string connectionString = "Server=mysql.example.com;Database=SampleDB;User ID=root;Password=yourpassword;";

string connectionString = "Server=mysql.example.com;Database=SampleDB;User ID=root;Password=yourpassword;";

Dim connectionString As String = "Server=mysql.example.com;Database=SampleDB;User ID=root;Password=yourpassword;"

VB C#

連線池

預設情況下，MySQL Connector/NET 支援連接池，這有助於更有效地管理資料庫連接。連接池透過重用池中的現有連接，減少反復開啟和關閉連接的開銷。

如果您想自訂連線池行為，您可以像這樣調整您的連線字串：

string connectionString = "Server=localhost;Database=SampleDB;User ID=root;Password=yourpassword;Pooling=true;";

string connectionString = "Server=localhost;Database=SampleDB;User ID=root;Password=yourpassword;Pooling=true;";

Dim connectionString As String = "Server=localhost;Database=SampleDB;User ID=root;Password=yourpassword;Pooling=true;"

VB C#

處理常見錯誤

常見問題包括不正確的連接字串、防火牆限制或 MySQL 服務未運行。確保所有配置詳細資訊正確且 MySQL 服務處於啟用狀態。

使用 C# 和 MySQL 執行 CRUD 操作

創建一個 C# 類以進行資料庫操作

為了更好的程式碼組織，創建一個 DatabaseHelper 類別來處理所有資料庫操作。此類別將包含插入、讀取、更新和刪除資料的方法(增刪改查)運作。

public class DatabaseHelper
{
    private string connectionString = "Server=localhost;Database=SampleDB;User ID=root;Password=yourpassword;";
    public void InsertEmployee(string firstName, string lastName, string position, decimal salary)
    {
        using (var connection = new MySqlConnection(connectionString))
        {
            string query = "INSERT INTO Employees (FirstName, LastName, Position, Salary) VALUES (@FirstName, @LastName, @Position, @Salary)";
            MySqlCommand cmd = new MySqlCommand(query, connection);
            cmd.Parameters.AddWithValue("@FirstName", firstName);
            cmd.Parameters.AddWithValue("@LastName", lastName);
            cmd.Parameters.AddWithValue("@Position", position);
            cmd.Parameters.AddWithValue("@Salary", salary);
            connection.Open();
            cmd.ExecuteNonQuery();
        }
    }
}

public class DatabaseHelper
{
    private string connectionString = "Server=localhost;Database=SampleDB;User ID=root;Password=yourpassword;";
    public void InsertEmployee(string firstName, string lastName, string position, decimal salary)
    {
        using (var connection = new MySqlConnection(connectionString))
        {
            string query = "INSERT INTO Employees (FirstName, LastName, Position, Salary) VALUES (@FirstName, @LastName, @Position, @Salary)";
            MySqlCommand cmd = new MySqlCommand(query, connection);
            cmd.Parameters.AddWithValue("@FirstName", firstName);
            cmd.Parameters.AddWithValue("@LastName", lastName);
            cmd.Parameters.AddWithValue("@Position", position);
            cmd.Parameters.AddWithValue("@Salary", salary);
            connection.Open();
            cmd.ExecuteNonQuery();
        }
    }
}

Public Class DatabaseHelper
	Private connectionString As String = "Server=localhost;Database=SampleDB;User ID=root;Password=yourpassword;"
	Public Sub InsertEmployee(ByVal firstName As String, ByVal lastName As String, ByVal position As String, ByVal salary As Decimal)
		Using connection = New MySqlConnection(connectionString)
			Dim query As String = "INSERT INTO Employees (FirstName, LastName, Position, Salary) VALUES (@FirstName, @LastName, @Position, @Salary)"
			Dim cmd As New MySqlCommand(query, connection)
			cmd.Parameters.AddWithValue("@FirstName", firstName)
			cmd.Parameters.AddWithValue("@LastName", lastName)
			cmd.Parameters.AddWithValue("@Position", position)
			cmd.Parameters.AddWithValue("@Salary", salary)
			connection.Open()
			cmd.ExecuteNonQuery()
		End Using
	End Sub
End Class

VB C#

說明

參數化：使用 @Parameter 減少 SQL 注入的風險。
connection.Open()：開啟 MySQL 連線。
cmd.ExecuteNonQuery()：執行插入查詢。

將資料插入 MySQL 資料庫

要新增員工數據，請調用 InsertEmployee 方法：

DatabaseHelper dbHelper = new DatabaseHelper();
dbHelper.InsertEmployee("Alice", "Brown", "Project Manager", 90000);

DatabaseHelper dbHelper = new DatabaseHelper();
dbHelper.InsertEmployee("Alice", "Brown", "Project Manager", 90000);

Dim dbHelper As New DatabaseHelper()
dbHelper.InsertEmployee("Alice", "Brown", "Project Manager", 90000)

VB C#

檢索和顯示數據

擷取資料並在控制台中顯示：

public void GetEmployees()
{
    using (var connection = new MySqlConnection(connectionString))
    {
        string query = "SELECT * FROM Employees";
        MySqlCommand cmd = new MySqlCommand(query, connection);
        connection.Open();
        using (MySqlDataReader reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine($"{reader["FirstName"]} {reader["LastName"]}, Position: {reader["Position"]}, Salary: {reader["Salary"]}");
            }
        }
    }
}

public void GetEmployees()
{
    using (var connection = new MySqlConnection(connectionString))
    {
        string query = "SELECT * FROM Employees";
        MySqlCommand cmd = new MySqlCommand(query, connection);
        connection.Open();
        using (MySqlDataReader reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine($"{reader["FirstName"]} {reader["LastName"]}, Position: {reader["Position"]}, Salary: {reader["Salary"]}");
            }
        }
    }
}

Public Sub GetEmployees()
	Using connection = New MySqlConnection(connectionString)
		Dim query As String = "SELECT * FROM Employees"
		Dim cmd As New MySqlCommand(query, connection)
		connection.Open()
		Using reader As MySqlDataReader = cmd.ExecuteReader()
			Do While reader.Read()
				Console.WriteLine($"{reader("FirstName")} {reader("LastName")}, Position: {reader("Position")}, Salary: {reader("Salary")}")
			Loop
		End Using
	End Using
End Sub

VB C#

說明

ExecuteReader()：執行選擇查詢並返回 MySqlDataReader 物件。
reader.Read()：遍歷結果集，顯示每位員工的詳細信息。

更新和刪除記錄

以下是更新員工薪資的範例：

public void UpdateEmployeeSalary(int employeeId, decimal newSalary)
{
    using (var connection = new MySqlConnection(connectionString))
    {
        string query = "UPDATE Employees SET Salary = @Salary WHERE EmployeeID = @EmployeeID";
        MySqlCommand cmd = new MySqlCommand(query, connection);
        cmd.Parameters.AddWithValue("@Salary", newSalary);
        cmd.Parameters.AddWithValue("@EmployeeID", employeeId);
        connection.Open();
        cmd.ExecuteNonQuery();
        Console.WriteLine("Employee salary updated successfully!");
    }
}

public void UpdateEmployeeSalary(int employeeId, decimal newSalary)
{
    using (var connection = new MySqlConnection(connectionString))
    {
        string query = "UPDATE Employees SET Salary = @Salary WHERE EmployeeID = @EmployeeID";
        MySqlCommand cmd = new MySqlCommand(query, connection);
        cmd.Parameters.AddWithValue("@Salary", newSalary);
        cmd.Parameters.AddWithValue("@EmployeeID", employeeId);
        connection.Open();
        cmd.ExecuteNonQuery();
        Console.WriteLine("Employee salary updated successfully!");
    }
}

Public Sub UpdateEmployeeSalary(ByVal employeeId As Integer, ByVal newSalary As Decimal)
	Using connection = New MySqlConnection(connectionString)
		Dim query As String = "UPDATE Employees SET Salary = @Salary WHERE EmployeeID = @EmployeeID"
		Dim cmd As New MySqlCommand(query, connection)
		cmd.Parameters.AddWithValue("@Salary", newSalary)
		cmd.Parameters.AddWithValue("@EmployeeID", employeeId)
		connection.Open()
		cmd.ExecuteNonQuery()
		Console.WriteLine("Employee salary updated successfully!")
	End Using
End Sub

VB C#

更新命令：使用參數化查詢根據 EmployeeID 更新 Salary 欄位

使用 IronPDF 從 MySQL 數據生成 PDF

IronPDF 介紹

IronPDF是一個強大的程式庫，允許開發人員在 C# 應用程式中輕鬆創建、編輯和操作 PDF 文件。它支持各種 PDF 功能，是資料驅動應用程式的完美工具，適合需要自動化報告生成、文件操作或 HTML 到 PDF 轉換的需求。無論您是需要將動態網頁轉換為 PDF 檔案，還是從頭開始生成自訂 PDF，IronPDF 只需幾行代碼即可簡化這一過程。

IronPDF 的主要功能

HTML 轉換為 PDF：IronPDF 的一個突出功能是其能夠转换 HTML內容轉換為完全格式化的 PDF 文件。此功能特別適合從動態網頁內容生成報告，或處理以網頁格式存儲的數據時使用。
編輯PDF：IronPDF允許編輯現有PDF，包括添加、刪除和修改內容，如文本、圖像、表格等。這非常適合需要處理或更新現有文件的應用程式。
PDF 合併與拆分：使用 IronPDF，您可以輕鬆地合併多個 PDF合併到單一文件中或分割將大型 PDF 拆分為較小的文件。此功能對於組織和管理大量文檔集合非常有用。
樣式和自訂：從 HTML 生成 PDF 時，您可以使用 CSS 來設計文件，並實現與您的應用程式設計相匹配的自訂佈局。 IronPDF 為您提供對 PDF 外觀的完全控制，確保它們滿足您的特定需求。

在您的C#專案中設定IronPDF

使用IronPDF，通過 Visual Studio 中的 NuGet 套件管理器安裝：

Install-Package IronPdf

Install-Package IronPdf

'INSTANT VB TODO TASK: The following line uses invalid syntax:
'Install-Package IronPdf

VB C#

將 MySQL 數據轉換為 PDF 格式

以下是顯示如何建立員工資料 PDF 報告的完整代碼示例：

using IronPdf;
using System;
using MySql.Data.MySqlClient;
public class Program
{
    // MySQL connection string
    private static string connectionString = "Server=localhost;Database=SampleDB;User ID=root;Password=yourpassword;";
    public static void Main(string[] args)
    {
        // Perform CRUD operations
        DatabaseHelper dbHelper = new DatabaseHelper();
        // Insert a new employee
        dbHelper.InsertEmployee("Alice", "Brown", "Project Manager", 90000);
        // Display employees
        dbHelper.GetEmployees();
        // Update an employee's salary
        dbHelper.UpdateEmployeeSalary(1, 95000);
        // Generate a PDF report
        dbHelper.GenerateEmployeeReportPDF();
        Console.WriteLine("Operations completed.");
    }  
}
public class DatabaseHelper
{
    private string connectionString = "Server=localhost;Database=SampleDB;User ID=root;Password=yourpassword;";
    // Insert employee into database
    public void InsertEmployee(string firstName, string lastName, string position, decimal salary)
    {
        using (var connection = new MySqlConnection(connectionString))
        {
            string query = "INSERT INTO Employees (FirstName, LastName, Position, Salary) VALUES (@FirstName, @LastName, @Position, @Salary)";
            MySqlCommand cmd = new MySqlCommand(query, connection);
            cmd.Parameters.AddWithValue("@FirstName", firstName);
            cmd.Parameters.AddWithValue("@LastName", lastName);
            cmd.Parameters.AddWithValue("@Position", position);
            cmd.Parameters.AddWithValue("@Salary", salary);
            connection.Open();
            cmd.ExecuteNonQuery();
            Console.WriteLine($"Employee {firstName} {lastName} inserted successfully!");
        }
    }
    // Get employees from the database and display them
    public void GetEmployees()
    {
        using (var connection = new MySqlConnection(connectionString))
        {
            string query = "SELECT * FROM Employees";
            MySqlCommand cmd = new MySqlCommand(query, connection);
            connection.Open();
            using (MySqlDataReader reader = cmd.ExecuteReader())
            {
                Console.WriteLine("\nEmployee List:");
                while (reader.Read())
                {
                    Console.WriteLine($"{reader["EmployeeID"]} - {reader["FirstName"]} {reader["LastName"]}, Position: {reader["Position"]}, Salary: {reader["Salary"]}");
                }
            }
        }
    }
    // Update the salary of an employee
    public void UpdateEmployeeSalary(int employeeId, decimal newSalary)
    {
        using (var connection = new MySqlConnection(connectionString))
        {
            string query = "UPDATE Employees SET Salary = @Salary WHERE EmployeeID = @EmployeeID";
            MySqlCommand cmd = new MySqlCommand(query, connection);
            cmd.Parameters.AddWithValue("@Salary", newSalary);
            cmd.Parameters.AddWithValue("@EmployeeID", employeeId);
            connection.Open();
            cmd.ExecuteNonQuery();
            Console.WriteLine($"Employee ID {employeeId}'s salary updated to {newSalary}.");
        }
    }
    // Generate a PDF report of all employees
    public void GenerateEmployeeReportPDF()
    {
        string htmlContent = "<h1>Employee Report</h1><table border='1'><tr><th>EmployeeID</th><th>First Name</th><th>Last Name</th><th>Position</th><th>Salary</th></tr>";
        using (var connection = new MySqlConnection(connectionString))
        {
            string query = "SELECT * FROM Employees";
            MySqlCommand cmd = new MySqlCommand(query, connection);
            connection.Open();
            using (MySqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    htmlContent += $"<tr><td>{reader["EmployeeID"]}</td><td>{reader["FirstName"]}</td><td>{reader["LastName"]}</td><td>{reader["Position"]}</td><td>{reader["Salary"]}</td></tr>";
                }
            }
        }
        htmlContent += "</table>";
        // Use IronPDF to convert HTML to PDF
        ChromePdfRenderer renderer = new ChromePdfRenderer();
        PdfDocument pdf = renderer.RenderHtmlAsPdf(htmlContent);
        pdf.SaveAs("EmployeeReport.pdf");
        Console.WriteLine("PDF Report generated successfully!");
    }
}

using IronPdf;
using System;
using MySql.Data.MySqlClient;
public class Program
{
    // MySQL connection string
    private static string connectionString = "Server=localhost;Database=SampleDB;User ID=root;Password=yourpassword;";
    public static void Main(string[] args)
    {
        // Perform CRUD operations
        DatabaseHelper dbHelper = new DatabaseHelper();
        // Insert a new employee
        dbHelper.InsertEmployee("Alice", "Brown", "Project Manager", 90000);
        // Display employees
        dbHelper.GetEmployees();
        // Update an employee's salary
        dbHelper.UpdateEmployeeSalary(1, 95000);
        // Generate a PDF report
        dbHelper.GenerateEmployeeReportPDF();
        Console.WriteLine("Operations completed.");
    }  
}
public class DatabaseHelper
{
    private string connectionString = "Server=localhost;Database=SampleDB;User ID=root;Password=yourpassword;";
    // Insert employee into database
    public void InsertEmployee(string firstName, string lastName, string position, decimal salary)
    {
        using (var connection = new MySqlConnection(connectionString))
        {
            string query = "INSERT INTO Employees (FirstName, LastName, Position, Salary) VALUES (@FirstName, @LastName, @Position, @Salary)";
            MySqlCommand cmd = new MySqlCommand(query, connection);
            cmd.Parameters.AddWithValue("@FirstName", firstName);
            cmd.Parameters.AddWithValue("@LastName", lastName);
            cmd.Parameters.AddWithValue("@Position", position);
            cmd.Parameters.AddWithValue("@Salary", salary);
            connection.Open();
            cmd.ExecuteNonQuery();
            Console.WriteLine($"Employee {firstName} {lastName} inserted successfully!");
        }
    }
    // Get employees from the database and display them
    public void GetEmployees()
    {
        using (var connection = new MySqlConnection(connectionString))
        {
            string query = "SELECT * FROM Employees";
            MySqlCommand cmd = new MySqlCommand(query, connection);
            connection.Open();
            using (MySqlDataReader reader = cmd.ExecuteReader())
            {
                Console.WriteLine("\nEmployee List:");
                while (reader.Read())
                {
                    Console.WriteLine($"{reader["EmployeeID"]} - {reader["FirstName"]} {reader["LastName"]}, Position: {reader["Position"]}, Salary: {reader["Salary"]}");
                }
            }
        }
    }
    // Update the salary of an employee
    public void UpdateEmployeeSalary(int employeeId, decimal newSalary)
    {
        using (var connection = new MySqlConnection(connectionString))
        {
            string query = "UPDATE Employees SET Salary = @Salary WHERE EmployeeID = @EmployeeID";
            MySqlCommand cmd = new MySqlCommand(query, connection);
            cmd.Parameters.AddWithValue("@Salary", newSalary);
            cmd.Parameters.AddWithValue("@EmployeeID", employeeId);
            connection.Open();
            cmd.ExecuteNonQuery();
            Console.WriteLine($"Employee ID {employeeId}'s salary updated to {newSalary}.");
        }
    }
    // Generate a PDF report of all employees
    public void GenerateEmployeeReportPDF()
    {
        string htmlContent = "<h1>Employee Report</h1><table border='1'><tr><th>EmployeeID</th><th>First Name</th><th>Last Name</th><th>Position</th><th>Salary</th></tr>";
        using (var connection = new MySqlConnection(connectionString))
        {
            string query = "SELECT * FROM Employees";
            MySqlCommand cmd = new MySqlCommand(query, connection);
            connection.Open();
            using (MySqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    htmlContent += $"<tr><td>{reader["EmployeeID"]}</td><td>{reader["FirstName"]}</td><td>{reader["LastName"]}</td><td>{reader["Position"]}</td><td>{reader["Salary"]}</td></tr>";
                }
            }
        }
        htmlContent += "</table>";
        // Use IronPDF to convert HTML to PDF
        ChromePdfRenderer renderer = new ChromePdfRenderer();
        PdfDocument pdf = renderer.RenderHtmlAsPdf(htmlContent);
        pdf.SaveAs("EmployeeReport.pdf");
        Console.WriteLine("PDF Report generated successfully!");
    }
}

Imports Microsoft.VisualBasic
Imports IronPdf
Imports System
Imports MySql.Data.MySqlClient
Public Class Program
	' MySQL connection string
	Private Shared connectionString As String = "Server=localhost;Database=SampleDB;User ID=root;Password=yourpassword;"
	Public Shared Sub Main(ByVal args() As String)
		' Perform CRUD operations
		Dim dbHelper As New DatabaseHelper()
		' Insert a new employee
		dbHelper.InsertEmployee("Alice", "Brown", "Project Manager", 90000)
		' Display employees
		dbHelper.GetEmployees()
		' Update an employee's salary
		dbHelper.UpdateEmployeeSalary(1, 95000)
		' Generate a PDF report
		dbHelper.GenerateEmployeeReportPDF()
		Console.WriteLine("Operations completed.")
	End Sub
End Class
Public Class DatabaseHelper
	Private connectionString As String = "Server=localhost;Database=SampleDB;User ID=root;Password=yourpassword;"
	' Insert employee into database
	Public Sub InsertEmployee(ByVal firstName As String, ByVal lastName As String, ByVal position As String, ByVal salary As Decimal)
		Using connection = New MySqlConnection(connectionString)
			Dim query As String = "INSERT INTO Employees (FirstName, LastName, Position, Salary) VALUES (@FirstName, @LastName, @Position, @Salary)"
			Dim cmd As New MySqlCommand(query, connection)
			cmd.Parameters.AddWithValue("@FirstName", firstName)
			cmd.Parameters.AddWithValue("@LastName", lastName)
			cmd.Parameters.AddWithValue("@Position", position)
			cmd.Parameters.AddWithValue("@Salary", salary)
			connection.Open()
			cmd.ExecuteNonQuery()
			Console.WriteLine($"Employee {firstName} {lastName} inserted successfully!")
		End Using
	End Sub
	' Get employees from the database and display them
	Public Sub GetEmployees()
		Using connection = New MySqlConnection(connectionString)
			Dim query As String = "SELECT * FROM Employees"
			Dim cmd As New MySqlCommand(query, connection)
			connection.Open()
			Using reader As MySqlDataReader = cmd.ExecuteReader()
				Console.WriteLine(vbLf & "Employee List:")
				Do While reader.Read()
					Console.WriteLine($"{reader("EmployeeID")} - {reader("FirstName")} {reader("LastName")}, Position: {reader("Position")}, Salary: {reader("Salary")}")
				Loop
			End Using
		End Using
	End Sub
	' Update the salary of an employee
	Public Sub UpdateEmployeeSalary(ByVal employeeId As Integer, ByVal newSalary As Decimal)
		Using connection = New MySqlConnection(connectionString)
			Dim query As String = "UPDATE Employees SET Salary = @Salary WHERE EmployeeID = @EmployeeID"
			Dim cmd As New MySqlCommand(query, connection)
			cmd.Parameters.AddWithValue("@Salary", newSalary)
			cmd.Parameters.AddWithValue("@EmployeeID", employeeId)
			connection.Open()
			cmd.ExecuteNonQuery()
			Console.WriteLine($"Employee ID {employeeId}'s salary updated to {newSalary}.")
		End Using
	End Sub
	' Generate a PDF report of all employees
	Public Sub GenerateEmployeeReportPDF()
		Dim htmlContent As String = "<h1>Employee Report</h1><table border='1'><tr><th>EmployeeID</th><th>First Name</th><th>Last Name</th><th>Position</th><th>Salary</th></tr>"
		Using connection = New MySqlConnection(connectionString)
			Dim query As String = "SELECT * FROM Employees"
			Dim cmd As New MySqlCommand(query, connection)
			connection.Open()
			Using reader As MySqlDataReader = cmd.ExecuteReader()
				Do While reader.Read()
					htmlContent &= $"<tr><td>{reader("EmployeeID")}</td><td>{reader("FirstName")}</td><td>{reader("LastName")}</td><td>{reader("Position")}</td><td>{reader("Salary")}</td></tr>"
				Loop
			End Using
		End Using
		htmlContent &= "</table>"
		' Use IronPDF to convert HTML to PDF
		Dim renderer As New ChromePdfRenderer()
		Dim pdf As PdfDocument = renderer.RenderHtmlAsPdf(htmlContent)
		pdf.SaveAs("EmployeeReport.pdf")
		Console.WriteLine("PDF Report generated successfully!")
	End Sub
End Class

VB C#

控制台輸出：

C# MySQL 連接（開發人員如何運作）：圖 4

PDF 輸出

C# MySQL連接（對開發人員的運作方式）：圖5

代碼分解

連接到 MySQL 資料庫：
- connectionString 定義了 MySQL 伺服器、資料庫、使用者和密碼。
- 您使用 MySqlConnection 進行連接，並使用 MySqlCommand 處理 CRUD 操作。
插入操作(插入員工)
- 使用 MySqlCommand 與參數化查詢(@FirstName, @LastName, 等。)以防止 SQL 注入。
- 開啟連線後(connection.Open())，ExecuteNonQuery()執行 INSERT SQL 語句。
讀取操作(獲取員工)
- 執行 SELECT * 查詢以獲取所有員工記錄。
- 使用 MySqlDataReader 來遍歷結果集，並在控制台顯示每個記錄。
更新操作(更新員工薪水)
- 該方法接受一個 employeeId 和一個 newSalary 來更新員工的薪資。
- 它使用參數化的UPDATE SQL查詢。
PDF 生成(GenerateEmployeeReportPDF)
- 將員工資料收集成具有簡單表格結構的 HTML 字串。
- HTML 內容被傳遞到 IronPDF 的 HtmlToPdf.StaticRenderHtmlAsPdf 方法以生成 PDF 報告。
- 生成的 PDF 被保存為 EmployeeReport.pdf。

結論

在本文中，我們介紹了將 MySQL 與 C# 應用程式整合的基本步驟。從設置資料庫和執行 CRUD 操作到使用 IronPDF 生成 PDF，我們涵蓋了建立資料驅動應用程式所需的廣泛基礎主題。以下是主要概念的回顧：

MySQL 和 C# 整合：我們展示了如何使用 MySql.Data 庫連接到 MySQL 資料庫、管理資料庫連接以及使用參數化查詢執行 CRUD 操作。這確保了數據可以以安全且有序的方式高效地存儲、更新和檢索。
執行 CRUD 操作：通過插入、更新和讀取員工資料的範例方法，您可以延伸此邏輯以管理真實世界資料庫中的其他類型記錄。使用參數化查詢也有助於減輕 SQL 注入攻擊，確保應用程序的安全性。
IronPDF for PDF Generation：IronPDF 讓從動態 HTML 內容生成專業外觀的 PDF 變得簡單。透過將從 MySQL 擷取的資料轉換為 HTML 表格，我們可以建立自訂報告並將其儲存為 PDF，這對於生成發票、報告、摘要等非常有用。 IronPDF 的簡單 API 使其成為任何需要在應用程式中處理 PDF 生成的 C# 開發人員的優秀工具。
透過結合 C# 和 MySQL，開發人員可以構建強大的應用程式，這些應用程式不僅能儲存和管理數據，還能提供如 PDF 報告等高級功能。這些功能在各個行業中都很有用，從金融到醫療保健，精確的數據管理和報告都是至關重要的。
對於希望將 PDF 生成整合到其 C# 應用程式中的開發人員，IronPDF允許您測試完整功能套件。無論您需要生成簡單的文件還是複雜的報告，IronPDF 都可以成為在您的工作流程中自動化 PDF 創建的寶貴工具。

< 上一頁
Parseint C＃（對開發人員的工作原理）

下一個 >
C# 命名元组（開發者如何使用）