How to add data into MySQL database using C#
Many small and large enterprises utilize MySQL, a quick and simple RDBMS created, promoted, and maintained by the Swedish business MySQL AB. MySQL is growing in popularity for many solid reasons.
- An open-source license is included with MySQL's release. So using it doesn't cost you anything.
- MySQL is a highly strong software on its own. It manages a significant portion of the features included in the priciest and most potent database products.
- MySQL makes use of the popular SQL data language in its standard version.
- MySQL is compatible with a wide range of operating systems and programming languages, such as PHP, PERL, C, C++, Java, etc.
- MySQL operates efficiently and rapidly, even with enormous data sets.
- PHP is the most popular programming language for web creation, and MySQL gets along well with it.
- Large databases—tables with at least 50 million rows—can be supported by MySQL. A table's 4GB default file size limit can be increased to a theoretical 8 million terabytes (TB) if your operating system supports it.
- MySQL is adaptable. Programmers can alter MySQL programming to suit their unique surroundings thanks to the open-source GPL license.
Linking C# to a Database:
To work with a database, you must first establish a connection. The parameters listed below are typically included in the connection to a database.
- Database name or Data Source: The name of the database to which a connection must be established before working with more than one database concurrently. Credentials are required to connect to the database; these are the login and password.
- Optional Parameters: You can offer more information about how .NET should connect to the database to handle the data by specifying optional parameters for each type of database.
Note: In this instance, we are using the command prompt to run these instructions. You can view the outcome using Microsoft SQL Server Management Studio.
Process of adding data into a MySQL database using C#
- Install MySQL Connector/NET: Use NuGet Package Manager to install the MySQL Connector/NET.
- Create a Connection: With the connection parameters—server name, port, database name, username, and password—create a MySqlConnection object.
- Open Connection: Use the MySqlConnection object's Open() method to create a connection to the MySQL database.
- Create an INSERT statement in SQL: To insert data, create an SQL INSERT statement using the table name and column names specified.
- Make an instance of MySqlCommand: Use the SQL INSERT statement and the MySqlConnection object to instantiate a MySqlCommand object.
- Include Parameters: To stop SQL injection, add parameters to the MySqlCommand object.
- Run a SQL Command: To run the SQL INSERT statement, use the MySqlCommand object's ExecuteNonQuery() method. The number of rows impacted is returned.
- Handle Exceptions: Try-catch blocks are used to handle any exceptions that may arise during the procedure.
- Close Connection: To terminate the database connection, use the MySqlConnection object's Close() function.
Code:
using MySql.Data.MySqlClient;
using System;
using System.Windows.Forms;
namespace YourNamespace
{
public partial class MainForm : Form
{
public MainForm()
{
InitializeComponent(); // Assuming this method initializes your form and its controls
}
private void SaveButton_Click(object sender, EventArgs e)
{
try
{
string connectionString = "Server=localhost;Port=3307;Database=YourDatabase;Uid=root;Pwd=root;";
string query = "INSERT INTO Students (StudentId, FullName, FatherName, Age, Semester) VALUES (@StudentId, @FullName, @FatherName, @Age, @Semester)";
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
using (MySqlCommand command = new MySqlCommand(query, connection))
{
// Add parameters to prevent SQL injection
command.Parameters.AddWithValue("@StudentId", studentIdTextBox.Text);
command.Parameters.AddWithValue("@FullName", fullNameTextBox.Text);
command.Parameters.AddWithValue("@FatherName", fatherNameTextBox.Text);
command.Parameters.AddWithValue("@Age", ageTextBox.Text);
command.Parameters.AddWithValue("@Semester", semesterTextBox.Text);
connection.Open();
int rowsAffected = command.ExecuteNonQuery();
if (rowsAffected > 0)
{
MessageBox.Show("Data saved successfully");
}
else
{
MessageBox.Show("Failed to save data");
}
}
}
}
catch (MySqlException ex)
{
MessageBox.Show("MySQL Error: " + ex.Message);
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex.Message);
}
}
}
}
Applications:
There are many uses for adding data to a MySQL database in different domains that can be achieved with C#. The following are some typical situations in which this feature is used:
- User Registration in Web Applications: User registration forms frequently call for the safe storage of user information in a MySQL database, including username, email address, and password.
- Content Management: Web content management systems (CMS) regularly add articles, blog entries, photos, and other content to MySQL databases using administrative interfaces.
- E-commerce: To manage inventory, sales, and customer accounts, online retailers upload product details, customer orders, and transaction details to MySQL databases.