Ícono de mensaje
Saltar al pie de página
  1. IronPDF
  2. Blog de IronPDF
  3. .NET Ayuda
  4. Conexión MySQL con C#
.NET AYUDA

C# Conexión MySQL (Cómo Funciona para Desarrolladores)

Curtis Chau
Curtis Chau
Actualizado:

Introduction to C# MySQL Integration

Connecting C# applications to MySQL databases enables developers to leverage the power of a relational database for storing, retrieving, and managing data efficiently. This guide provides a step-by-step process to integrate MySQL with C# applications and demonstrates how to generate PDFs from the data within your MySQL database using the IronPDF library.

Prerequisites

To follow along with this guide, you’ll need:

  • Visual Studio or any C# IDE
  • A MySQL Database (installed and running)
  • The IronPDF library (for PDF generation)

Setting Up MySQL Database

Installing and Configuring MySQL

  1. Download the latest version of MySQL from mysql.com.
  2. Run the installer and follow the setup instructions. Select "Developer Default" to include MySQL Server and MySQL Workbench.
  3. Configure the MySQL root user credentials during setup and ensure that the MySQL service is running.

Creating a Sample Database and Tables

  1. Open MySQL Workbench and connect to the server.
  2. Create a new database and a sample table using SQL commands:
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)
);
  1. Insert sample data:
INSERT INTO Employees (FirstName, LastName, Position, Salary) 
VALUES ('John', 'Doe', 'Software Developer', 80000),
       ('Jane', 'Smith', 'Data Analyst', 75000);

Setting Up MySQL User for Remote Access (Optional)

For remote access, create a MySQL user with necessary permissions:

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

Connecting C# to MySQL Database

Installing MySql.Data Library in C#

To connect C# applications to MySQL, we use the MySQL Connector/NET library (often referred to as Connector/NET). This is the official .NET driver for MySQL, which can be installed via NuGet.

  1. Open Visual Studio and create a new C# Console Application.
  2. Add the MySql.Data library via NuGet Package Manager:
    • Right-click the project > Manage NuGet Packages > Browse > search for MySql.Data and install it.

Writing the Connection Code

The following code example demonstrates how to establish a connection to MySQL:

using System;
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}");
        }
        finally
        {
            connection.Close(); // Ensure the connection is closed after use
        }
    }
}
using System;
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}");
        }
        finally
        {
            connection.Close(); // Ensure the connection is closed after use
        }
    }
}
Imports System
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}")
		Finally
			connection.Close() ' Ensure the connection is closed after use
		End Try
	End Sub
End Class
$vbLabelText   $csharpLabel

Explanation:

  • Connection String: Contains details such as server, database name, user ID, and password.
  • MySqlConnection: Used to establish the connection.
  • Open() Method: Attempts to open the connection.
  • Exception Handling: Catch exceptions to handle connection errors gracefully.

Using DNS SRV Records for Connection (Optional)

If your application is hosted in the cloud or requires connecting to a MySQL database via DNS SRV records, you can replace the server name with the corresponding DNS entry that resolves to the database's IP.

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;"
$vbLabelText   $csharpLabel

Connection Pooling

By default, MySQL Connector/NET supports connection pooling, which helps manage database connections more efficiently. Connection pooling reduces the overhead of opening and closing connections repeatedly by reusing existing connections from a pool.

If you want to customize the connection pooling behavior, you can adjust your connection string like this:

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;"
$vbLabelText   $csharpLabel

Handling Common Errors

Common issues include incorrect connection strings, firewall restrictions, or MySQL service not running. Ensure all configuration details are correct and that the MySQL service is active.

Performing CRUD Operations with C# and MySQL

Creating a C# Class for Database Operations

For code organization, create a DatabaseHelper class to handle all database operations. This class will contain methods for Insert, Read, Update, and Delete data (CRUD) operations.

using System;
using MySql.Data.MySqlClient;

public class DatabaseHelper
{
    private string connectionString = "Server=localhost;Database=SampleDB;User ID=root;Password=yourpassword;";

    // Method to insert a new employee record
    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);

            // Add parameters to prevent SQL injection
            cmd.Parameters.AddWithValue("@FirstName", firstName);
            cmd.Parameters.AddWithValue("@LastName", lastName);
            cmd.Parameters.AddWithValue("@Position", position);
            cmd.Parameters.AddWithValue("@Salary", salary);

            connection.Open();
            cmd.ExecuteNonQuery(); // Execute the insert command
        }
    }
}
using System;
using MySql.Data.MySqlClient;

public class DatabaseHelper
{
    private string connectionString = "Server=localhost;Database=SampleDB;User ID=root;Password=yourpassword;";

    // Method to insert a new employee record
    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);

            // Add parameters to prevent SQL injection
            cmd.Parameters.AddWithValue("@FirstName", firstName);
            cmd.Parameters.AddWithValue("@LastName", lastName);
            cmd.Parameters.AddWithValue("@Position", position);
            cmd.Parameters.AddWithValue("@Salary", salary);

            connection.Open();
            cmd.ExecuteNonQuery(); // Execute the insert command
        }
    }
}
Imports System
Imports MySql.Data.MySqlClient

Public Class DatabaseHelper
	Private connectionString As String = "Server=localhost;Database=SampleDB;User ID=root;Password=yourpassword;"

	' Method to insert a new employee record
	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)

			' Add parameters to prevent SQL injection
			cmd.Parameters.AddWithValue("@FirstName", firstName)
			cmd.Parameters.AddWithValue("@LastName", lastName)
			cmd.Parameters.AddWithValue("@Position", position)
			cmd.Parameters.AddWithValue("@Salary", salary)

			connection.Open()
			cmd.ExecuteNonQuery() ' Execute the insert command
		End Using
	End Sub
End Class
$vbLabelText   $csharpLabel

Explanation:

  • Parameterization: Using @Parameter reduces the risk of SQL injection.
  • connection.Open(): Opens the MySQL connection.
  • cmd.ExecuteNonQuery(): Executes the insert query.

Inserting Data into MySQL Database

To add new employee data, call the InsertEmployee method:

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)
$vbLabelText   $csharpLabel

Retrieving and Displaying Data

Retrieve data and display it in the console:

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
$vbLabelText   $csharpLabel

Explanation:

  • ExecuteReader(): Executes the select query and returns a MySqlDataReader object.
  • reader.Read(): Iterates through the result set, displaying each employee’s details.

Updating and Deleting Records

Here’s an example to update an employee’s salary:

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);

        // Parameterize the SQL command
        cmd.Parameters.AddWithValue("@Salary", newSalary);
        cmd.Parameters.AddWithValue("@EmployeeID", employeeId);

        connection.Open();
        cmd.ExecuteNonQuery(); // Execute the update command
        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);

        // Parameterize the SQL command
        cmd.Parameters.AddWithValue("@Salary", newSalary);
        cmd.Parameters.AddWithValue("@EmployeeID", employeeId);

        connection.Open();
        cmd.ExecuteNonQuery(); // Execute the update command
        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)

		' Parameterize the SQL command
		cmd.Parameters.AddWithValue("@Salary", newSalary)
		cmd.Parameters.AddWithValue("@EmployeeID", employeeId)

		connection.Open()
		cmd.ExecuteNonQuery() ' Execute the update command
		Console.WriteLine("Employee salary updated successfully!")
	End Using
End Sub
$vbLabelText   $csharpLabel

Update Command: Uses parameterized query to update the Salary column based on EmployeeID.

Generating PDFs from MySQL Data with IronPDF

Introduction to IronPDF

IronPDF is a robust library that allows developers to easily create, edit, and manipulate PDF documents within C# applications. It supports a wide range of PDF functionalities, making it a perfect tool for data-driven applications that require automated report generation, document manipulation, or HTML-to-PDF conversion. Whether you need to convert dynamic web pages into PDF files or generate custom PDFs from scratch, IronPDF simplifies the process with a few lines of code.

Key Features of IronPDF

  • HTML to PDF Conversion: One of the standout features of IronPDF is its ability to convert HTML content into fully formatted PDF documents. This feature is particularly useful for generating reports from dynamic web content or when working with data stored in a web format.
  • Editing PDFs: IronPDF allows for editing existing PDFs, including adding, removing, and modifying content, such as text, images, tables, and more. This is ideal for applications that need to process or update pre-existing documents.
  • PDF Merging and Splitting: With IronPDF, you can easily merge multiple PDFs into a single document or split a large PDF into smaller files. This feature is useful for organizing and managing large collections of documents.
  • Styling and Customization: When generating PDFs from HTML, you can use CSS to style the document and achieve a custom layout that matches your application’s design. IronPDF gives you full control over the appearance of your PDFs, ensuring they meet your specific requirements.

Setting Up IronPDF in Your C# Project

To use IronPDF, install it via NuGet Package Manager in Visual Studio:

Install-Package IronPdf

Converting MySQL Data to PDF Format

Here’s the full code example that shows how to create a PDF report of employee data:

using System;
using MySql.Data.MySqlClient;
using IronPdf;

public class Program
{
    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 System;
using MySql.Data.MySqlClient;
using IronPdf;

public class Program
{
    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 System
Imports MySql.Data.MySqlClient
Imports IronPdf

Public Class Program
	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
$vbLabelText   $csharpLabel

Breakdown of the Code

  1. Connection to MySQL Database:

    • The connectionString defines the MySQL server, database, user, and password.
    • You connect using MySqlConnection and handle CRUD operations with MySqlCommand.

  2. Insert Operation (InsertEmployee):

    • Uses MySqlCommand with parameterized queries (@FirstName, @LastName, etc.) to prevent SQL injection.
    • After opening the connection (connection.Open()), ExecuteNonQuery() runs the INSERT SQL statement.

  3. Read Operation (GetEmployees):

    • Executes a SELECT * query to fetch all employee records.
    • Uses a MySqlDataReader to iterate over the result set and display each record in the console.

  4. Update Operation (UpdateEmployeeSalary):

    • The method accepts an employeeId and a newSalary to update the employee's salary.
    • It uses a parameterized UPDATE SQL query.
  5. PDF Generation (GenerateEmployeeReportPDF):
    • Collects employee data into an HTML string with a simple table structure.
    • The HTML content is passed to IronPDF’s RenderHtmlAsPdf method to generate a PDF report.
    • The resulting PDF is saved as EmployeeReport.pdf.

Conclusion

In this article, we walked through the essential steps for integrating MySQL with a C# application. From setting up the database and performing CRUD operations to generating PDFs with IronPDF, we covered a wide range of foundational topics that are crucial for building data-driven applications. Here's a recap of the major concepts:

  • MySQL and C# Integration: We demonstrated how to connect to a MySQL database using the MySql.Data library, manage database connections, and perform CRUD operations using parameterized queries. This ensures that data can be efficiently stored, updated, and retrieved in a secure and organized manner.
  • Performing CRUD Operations: With the example methods for inserting, updating, and reading employee data, you can extend this logic to manage other types of records in a real-world database. The use of parameterized queries also helps mitigate SQL injection attacks, ensuring the security of your application.
  • IronPDF for PDF Generation: IronPDF makes it simple to generate professional-looking PDFs from dynamic HTML content. By converting data retrieved from MySQL into an HTML table, we can create customized reports and save them as PDFs, which can be useful for generating invoices, reports, summaries, and more. IronPDF's straightforward API makes it an excellent tool for any C# developer needing to handle PDF generation within their applications.

By combining C# and MySQL, developers can build robust applications that store and manage data while offering advanced functionalities like PDF reporting. These capabilities are useful across industries, from finance to healthcare, where accurate data management and reporting are critical.

For developers looking to incorporate PDF generation into their C# applications, IronPDF that allows you to test out the full suite of features. Whether you need to generate simple documents or sophisticated reports, IronPDF can be an invaluable tool for automating PDF creation within your workflow.

Preguntas Frecuentes

¿Cuáles son los requisitos previos para integrar MySQL con aplicaciones C#?

Para integrar MySQL con aplicaciones C#, necesitas un IDE como Visual Studio, una base de datos MySQL en ejecución y IronPDF para generar PDF a partir del contenido de la base de datos.

¿Cómo puedo convertir datos de MySQL en un PDF usando C#?

Puedes convertir datos de MySQL en un PDF transformando primero los datos en una cadena HTML y luego usando el método RenderHtmlAsPdf de IronPDF para generar un documento PDF.

¿Cómo instalo y configuro MySQL para usar con C#?

Instala MySQL descargándolo de mysql.com, ejecuta el instalador y sigue las instrucciones de configuración. Elige 'Developer Default' para que se incluya MySQL Server y Workbench y configura las credenciales del usuario root.

¿Qué biblioteca se recomienda para la conectividad entre C# y MySQL?

Se recomienda la biblioteca MySQL Connector/NET para establecer la conectividad entre aplicaciones C# y una base de datos MySQL. Permite el uso de cadenas de conexión para facilitar la comunicación.

¿Cómo puedo asegurar mis consultas SQL al usar C# con MySQL?

Para asegurar las consultas SQL, usa consultas parametrizadas que ayudan a prevenir ataques de inyección SQL asegurando una validación adecuada de entrada.

¿Qué es el pool de conexiones en el contexto de MySQL y C#?

El pool de conexiones se refiere a la práctica de reutilizar conexiones de base de datos de un pool, lo cual mejora la eficiencia al reducir la sobrecarga asociada con abrir y cerrar conexiones repetidamente.

¿Cómo creo una base de datos y tabla de ejemplo en MySQL para la integración con C#?

Abre MySQL Workbench, conéctate a tu servidor y usa comandos SQL como CREATE DATABASE SampleDB; y CREATE TABLE Employees (...); para configurar una base de datos y tabla de ejemplo.

¿Qué características debo buscar en una biblioteca de PDF para aplicaciones C#?

Una biblioteca robusta de PDF para C# debe ofrecer características como conversión de HTML a PDF, edición de PDF, fusión y división, y la capacidad de aplicar estilos personalizados usando CSS, como las que proporciona IronPDF.

¿Cómo realizo operaciones CRUD en una base de datos MySQL usando C#?

Implementa operaciones CRUD creando una clase de ayuda en C# que use comandos SQL parametrizados dentro de métodos para Insertar, Leer, Actualizar y Borrar datos en la base de datos MySQL.

¿Cómo puedo actualizar los detalles de un empleado en una base de datos MySQL usando C#?

Actualiza los detalles de un empleado escribiendo un método en C# que use un comando UPDATE SQL parametrizado, permitiendo modificaciones como actualizaciones de salario basadas en EmployeeID.

Curtis Chau
Curtis Chau
  Chatea con el equipo de ingeniería ahora
Escritor Técnico

Curtis Chau tiene una licenciatura en Ciencias de la Computación (Carleton University) y se especializa en el desarrollo front-end con experiencia en Node.js, TypeScript, JavaScript y React. Apasionado por crear interfaces de usuario intuitivas y estéticamente agradables, disfruta trabajando con frameworks modernos y creando manuales bien ...

Leer más

Artículos Relacionados

Prueba de 30 días → Producto completo. Sin límites. Sin tarjeta. Comenzar prueba gratuita