C# connecting to MySQL database
Petr Faltus development
MySQLclient.cs
GitHub repository: mysql-connection-source-codes
git clone https://github.com/petrfaltus/mysql-connection-source-codes.git
Directories to MySQLclient.cs in the repository: csharp
using System; using System.Data; using MySql.Data.MySqlClient; public class MySQLclient { private const string db_host = "localhost"; private const int db_port = 3306; private const string db_name = "testdb"; private const string db_username = "testuser"; private const string db_password = "T3stUs3r!"; private const string db_table = "people"; private const string db_update_column = "remark"; private const string db_update_column_variable = "@updatevar"; private const string db_column = "id"; private const string db_column_variable = "@var"; private const int db_column_value = 1; private const string db_factorial_variable = "@n"; private const int db_factorial_value = 4; private const string db_add_and_subtract_a_variable = "@a"; private const int db_add_and_subtract_a_value = 12; private const string db_add_and_subtract_b_variable = "@b"; private const int db_add_and_subtract_b_value = 5; private const string db_add_and_subtract_x_variable = "@x"; private const string db_add_and_subtract_y_variable = "@y"; private static string GetNow() { DateTime dateTimeNow = DateTime.Now; return dateTimeNow.ToString(); } public static void Main(string[] args) { // Build the connection string MySqlConnectionStringBuilder connBuilder = new MySqlConnectionStringBuilder(); connBuilder.Server = db_host; connBuilder.Port = db_port; connBuilder.UserID = db_username; connBuilder.Password = db_password; connBuilder.Database = db_name; try { using (MySqlConnection conn = new MySqlConnection(connBuilder.ConnectionString)) { // Connect the database conn.Open(); Console.WriteLine("Data source: {0}", conn.DataSource); Console.WriteLine("Server version: {0}", conn.ServerVersion); Console.WriteLine("Server thread: {0}", conn.ServerThread); Console.WriteLine("Database: {0}", conn.Database); Console.WriteLine("Connection timeout: {0}", conn.ConnectionTimeout); Console.WriteLine(); // UPDATE statement string new_comment = "C# " + GetNow(); string sql0 = String.Format("update {0} set {1}={2} where {3}!={4}", db_table, db_update_column, db_update_column_variable, db_column, db_column_variable); Console.WriteLine(sql0); using (var cmd = new MySqlCommand(sql0, conn)) { cmd.Parameters.AddWithValue(db_update_column_variable, new_comment); cmd.Parameters.AddWithValue(db_column_variable, db_column_value); int updatedRows = cmd.ExecuteNonQuery(); Console.WriteLine("Total updated rows: {0}", updatedRows); } Console.WriteLine(); // Full SELECT statement string sql1 = String.Format("select * from {0}", db_table); Console.WriteLine(sql1); using (var cmd = new MySqlCommand(sql1, conn)) using (MySqlDataReader reader = cmd.ExecuteReader()) { int columns = reader.FieldCount; Console.WriteLine("Total columns: {0}", columns); for (int ii = 0; ii < columns; ii++) { Console.WriteLine(" - {0} {1}", reader.GetName(ii), reader.GetDataTypeName(ii)); } int number = 0; while (reader.Read()) { number++; Console.Write(number); for (int ii = 0; ii < columns; ii++) { string type = reader.GetDataTypeName(ii); string value = "?"; if (!reader.IsDBNull(ii)) { if (type.EndsWith("CHAR")) { value = reader.GetString(ii); } else if (type.Equals("DATETIME")) { value = reader.GetDateTime(ii).ToString(); } else if (type.EndsWith("INT")) { value = reader.GetInt32(ii).ToString(); } } else { value = "(null)"; } Console.Write(" '{0}'", value); } Console.WriteLine(); } } Console.WriteLine(); // SELECT WHERE statement string sql2 = String.Format("select count(*) from {0} where {1}!={2}", db_table, db_column, db_column_variable); Console.WriteLine(sql2); using (var cmd = new MySqlCommand(sql2, conn)) { cmd.Parameters.AddWithValue(db_column_variable, db_column_value); Object result = cmd.ExecuteScalar(); Console.WriteLine("Result: {0}", result); } Console.WriteLine(); // SELECT function statement string sql3 = String.Format("select factorial({0})", db_factorial_variable); Console.WriteLine(sql3); using (var cmd = new MySqlCommand(sql3, conn)) { cmd.Parameters.AddWithValue(db_factorial_variable, db_factorial_value); Object result = cmd.ExecuteScalar(); Console.WriteLine("Result: {0}", result); } Console.WriteLine(); // CALL procedure statement string sql4 = String.Format("add_and_subtract"); Console.WriteLine(sql4); using (var cmd = new MySqlCommand(sql4, conn)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue(db_add_and_subtract_a_variable, db_add_and_subtract_a_value); cmd.Parameters.AddWithValue(db_add_and_subtract_b_variable, db_add_and_subtract_b_value); MySqlParameter par3 = new MySqlParameter(db_add_and_subtract_x_variable, MySqlDbType.Int32); par3.Direction = ParameterDirection.Output; cmd.Parameters.Add(par3); MySqlParameter par4 = new MySqlParameter(db_add_and_subtract_y_variable, MySqlDbType.Int32); par4.Direction = ParameterDirection.Output; cmd.Parameters.Add(par4); cmd.ExecuteNonQuery(); Console.WriteLine("Result x: {0}", cmd.Parameters[db_add_and_subtract_x_variable].Value); Console.WriteLine("Result y: {0}", cmd.Parameters[db_add_and_subtract_y_variable].Value); } } } catch (MySqlException mex) { Console.WriteLine("MySQL error {0}: {1}", mex.Number, mex.Message); } } }
Development tools
Developer ASCII table
Characters and HTML entities in the UTF-8 table
Predefined web CSS colors
CSS px to rem converter
🤝 Your IP address is 2600:1f28:365:80b0:e655:538a:27ae:d312