Getting Started With dotConnect for MySQL

using Devart.Data.MySql;

class Program
{
  static void Main()
  {
    // Define your connection string with SSL options
    MySqlConnection connection = new MySqlConnection(
      "Server=127.0.0.1;" +
      "Protocol=SSL;" +
      "UserId=TestUser;" +
      "Password=TestPassword;" +
      "Database=sakila;" +
      "LicenseKey=**********"
    );

    // Set SSL options
    connection.SslOptions.CACert = "file://D:\\Project\\CA-cert.pem";
    connection.SslOptions.Cert = "file://D:\\Project\\SSL-client-cert.pem";
    connection.SslOptions.Key = "file://D:\\Project\\SSL-client-key.pem";

    try
    {
      connection.Open();
      Console.WriteLine("SSL connection to the database opened successfully!");

      // Perform database operations here
      // For example, execute a simple query
      string query = "SELECT DATABASE();";
      using (MySqlCommand command = new MySqlCommand(query, connection))
      {
        object result = command.ExecuteScalar();
        Console.WriteLine($"Connected to database: {result}");
      }
    }
    catch (Exception ex)
    {
      Console.WriteLine($"Error: {ex.Message}");
    }
  }
}

using Devart.Data.MySql;

class Program
{
  static void Main()
  {
    string connectionString =
      "Server=127.0.0.1;" +
      "Port=3306;" +
      "UserId=TestUser;" +
      "Password=TestPassword;" +
      "Database=sakila;" +
      "LicenseKey=**********";

    using (MySqlConnection connection = new MySqlConnection(connectionString))
    {
      try
      {
        connection.Open();
        Console.WriteLine("Connection successful!");

        // Define a query to select data from the actor table
        string query = "SELECT actor_id, first_name, last_name FROM actor";

        // Create a command object
        using (MySqlCommand command = new MySqlCommand(query, connection))
        {
          // Execute the query and get a data reader
          using (MySqlDataReader reader = command.ExecuteReader())
          {
            // Loop through the result set
            while (reader.Read())
            {
              // Access the data using column names or indices
              int actorId = reader.GetInt32("actor_id");
              string firstName = reader.GetString("first_name");
              string lastName = reader.GetString("last_name");

              // Print the data to the console
              Console.WriteLine($"Actor ID: {actorId}, Name: {firstName} {lastName}");
            }
          }
        }
      }
      catch (Exception ex)
      {
        Console.WriteLine($"Error: {ex.Message}");
      }
    }
  }
}

using Microsoft.EntityFrameworkCore;
using Devart.Data.MySql.EFCore;

public class Actor
{
  public int ActorId { get; set; }
  public string FirstName { get; set; }
  public string LastName { get; set; }
}

public class SakilaContext : DbContext
{
  public DbSet<Actor> Actors { get; set; }

  protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
  {
    optionsBuilder.UseMySql(
      "Server=127.0.0.1;Port=3306;UserId=TestUser;Password=TestPassword;Database=sakila;LicenseKey=**********",
      MySqlServerVersion.LatestSupportedServerVersion
    );
  }
}

class Program
{
  static void Main()
  {
    using (var context = new SakilaContext())
    {
      try
      {
        // Ensure the database is created
        context.Database.EnsureCreated();
        Console.WriteLine("Connected to the database successfully!");

        // Query data from the Actors table
        var actors = context.Actors;
        foreach (var actor in actors)
        {
          Console.WriteLine($"Actor ID: {actor.ActorId}, Name: {actor.FirstName} {actor.LastName}");
        }
      }
      catch (Exception ex)
      {
        Console.WriteLine($"Error: {ex.Message}");
      }
    }
  }
}

using Devart.Data.MySql;

class Program
{
  static void Main()
  {
    string connectionString =
      "Server=127.0.0.1;" +
      "Port=3306;" +
      "UserId=TestUser;" +
      "Password=TestPassword;" +
      "Database=sakila;" +
      "LicenseKey=**********";

    using (MySqlConnection connection = new MySqlConnection(connectionString))
    {
      try
      {
        connection.Open();
        Console.WriteLine("Connection successful!");

        // Define a query to select data from the actor table
        string query = "SELECT actor_id, first_name, last_name FROM actor";

        // Create a command object
        using (MySqlCommand command = new MySqlCommand(query, connection))
        {
          // Create a data adapter
          using (MySqlDataAdapter adapter = new MySqlDataAdapter(command))
          {
            // Create a DataTable to hold the data
            DataTable dataTable = new DataTable();

            // Fill the DataTable with data from the database
            adapter.Fill(dataTable);

            // Loop through the DataTable and print the data
            foreach (DataRow row in dataTable.Rows)
            {
              int actorId = (int)row["actor_id"];
              string firstName = row["first_name"].ToString();
              string lastName = row["last_name"].ToString();

              Console.WriteLine($"Actor ID: {actorId}, Name: {firstName} {lastName}");
            }
          }
        }
      }
      catch (Exception ex)
      {
        Console.WriteLine($"Error: {ex.Message}");
      }
    }
  }
}

using Devart.Data.MySql;

class Program
{
  static void Main()
  {
    string connectionString =
      "Server=127.0.0.1;" +
      "Port=3306;" +
      "UserId=TestUser;" +
      "Password=TestPassword;" +
      "Database=sakila;" +
      "LicenseKey=**********";

    using (MySqlConnection connection = new MySqlConnection(connectionString))
    {
      try
      {
        connection.Open();
        Console.WriteLine("Connection successful!");

        // Start a transaction
        using (MySqlTransaction transaction = connection.BeginTransaction())
        {
          try
          {
            // Create a command to insert a new actor
            string insertQuery = "INSERT INTO actor (first_name, last_name) VALUES ('John', 'Doe')";
            using (MySqlCommand insertCommand = new MySqlCommand(insertQuery, connection, transaction))
            {
              insertCommand.ExecuteNonQuery();
              Console.WriteLine("Insert command executed.");
            }

            // Create a command to update an existing actor
            string updateQuery = "UPDATE actor SET last_name = 'Smith' WHERE first_name = 'John' AND last_name = 'Doe'";
            using (MySqlCommand updateCommand = new MySqlCommand(updateQuery, connection, transaction))
            {
              updateCommand.ExecuteNonQuery();
              Console.WriteLine("Update command executed.");
            }

            // Commit the transaction
            transaction.Commit();
            Console.WriteLine("Transaction committed successfully.");
          }
          catch (Exception ex)
          {
            // Roll back the transaction if any command fails
            transaction.Rollback();
            Console.WriteLine($"Transaction rolled back due to an error: {ex.Message}");
          }
        }
      }
      catch (Exception ex)
      {
        Console.WriteLine($"Error: {ex.Message}");
      }
    }
  }
}

using Devart.Data.MySql;

class Program
{
  static void Main()
  {
    string connectionString =
      "Server=127.0.0.1;" +
      "Port=3306;" +
      "UserId=TestUser;" +
      "Password=TestPassword;" +
      "Database=sakila;" +
      "LicenseKey=**********";

    using (MySqlConnection connection = new MySqlConnection(connectionString))
    {
      try
      {
        connection.Open();
        Console.WriteLine("Connection successful!");

        // Attempt to execute a query that may cause an exception
        string query = "SELECT * FROM non_existent_table";
        using (MySqlCommand command = new MySqlCommand(query, connection))
        {
          using (MySqlDataReader reader = command.ExecuteReader())
          {
            while (reader.Read())
            {
              // Process data (this won't be reached if the table doesn't exist)
              Console.WriteLine(reader["column_name"]);
            }
          }
        }
      }
      catch (MySqlException ex)
      {
        // Handle MySqlException and display error details
        Console.WriteLine($"MySQL Error: {ex.Message}");
        Console.WriteLine($"Error Code: {ex.Code}");
        Console.WriteLine($"Error Source: {ex.Source}");
        Console.WriteLine($"Stack Trace: {ex.StackTrace}");
      }
      catch (Exception ex)
      {
        // Handle any other exceptions
        Console.WriteLine($"General Error: {ex.Message}");
      }
    }
  }
}