Exploring the integration of .NET with MySQL is a common requirement for developers using these technologies. .NET provides a high-level data access API .NET Data Provider for MySQL, making it easy to connect and interact with MySQL databases. Here, we'll demonstrate a practical example of using MySQL with .NET, focusing on connection, querying, and data manipulation.
Before we dive in, ensure you have the latest MySql.Data library installed. You can install it via NuGet package manager in your project. Now, let's connect to a MySQL database from a .NET application.
Establishing the Connection
Firstly, install the MySql.Data NuGet package. Then, you can establish a connection using the MySqlConnection class, as shown:
```csharp string connStr = "Server=myServerAddress;Database=myDatabase;Uid=myUsername;Pwd=myPassword;"; using (MySqlConnection conn = new MySqlConnection(connStr)) { conn.Open(); Console.WriteLine("Connected to MySQL!"); } ```
Inserting Data
Now that we're connected, let's insert some data into a MySQL table. We'll use the MySqlCommand class for running SQL queries:
```csharp string query = "INSERT INTO employees(name, age, salary) VALUES('John Doe', 30, 60000)"; using (MySqlCommand cmd = new MySqlCommand(query, conn)) { cmd.ExecuteNonQuery(); } ```
Retrieving Data
To retrieve data, we can use a MySqlDataReader, which provides a forward-only stream of rows from a MySQL result set:
```csharp string query = "SELECT * FROM employees"; using (MySqlCommand cmd = new MySqlCommand(query, conn)) { using (MySqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { Console.WriteLine("{0} - {1} years old; Salary: {2}", reader.GetString(0), reader.GetInt32(1), reader.GetDecimal(2)); } } } ```
Working with Stored Procedures
MySQL supports stored procedures, which are powerful tools for encapsulating and reusing code. Let's create a stored procedure and invoke it from .NET.
Creating a Stored Procedure
First, create a stored procedure in your MySQL database:
```sql DELIMITER // CREATE PROCEDURE GetEmployeeSalary(IN _empName VARCHAR(100)) BEGIN SELECT salary FROM employees WHERE name = _empName; END// DELIMITER ; ```
Calling the Stored Procedure
Now, invoke this stored procedure from your .NET application:
```csharp string query = "GetEmployeeSalary @empName"; using (MySqlCommand cmd = new MySqlCommand(query, conn)) { cmd.Parameters.AddWithValue("_empName", "John Doe"); object result = cmd.ExecuteScalar(); Console.WriteLine("Salary: " + result); } ```
In conclusion, working with MySQL from a .NET application is straightforward and efficient, thanks to the robust .NET Data Provider for MySQL. This hands-on example has demonstrated connecting to a MySQL database, inserting and retrieving data, and invoking stored procedures. Now, you can explore more advanced features and optimize your .NET-MySQL workflow.