Notice: This page requires JavaScript to function properly.
Please enable JavaScript in your browser settings or update your browser.Task - Connecting to a MySQL Database and Executing Commands | Introduction to Databases with .NET
Introduction to .NET with C#

Task - Connecting to a MySQL Database and Executing CommandsTask - Connecting to a MySQL Database and Executing Commands

This task will recap most of the basic concepts of this chapter but the solution for it is very similar to what we had been practicing in the videos. Your task is to:

  • Write the code for connecting to a server. You can connect to your local server if you have it hosted on your PC. If not, you can enter placeholder values in the connection string. The database name in the connection string should be school;
  • Make sure you are correctly closing the connection after use; Use either the Close() method or the using statement;
  • Enclose the relevant code into a try-catch block;
  • Execute the command for creating a new table called teachers, which will have four columns, namely id, name, age, and experience. Use the following command: CREATE TABLE teachers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30), age INT, experience FLOAT);
  • Execute the following three commands, which will insert 3 rows into the database:
  • Finally, read and display the data of the 3 rows. The column name is of type String, age is of type Int32, and experience is of type Float.
Link to the Task: GitHub
You can use the GetFloat method of the MySqlDataReader for reading the experience column. 

using System;
using MySql.Data.MySqlClient;

public class MYSQL_Practice_1
{
    // Modify the connection string here
    public static string connectionString = "server=127.0.0.1;port=7777;user=root;password=Th!sIsMyPassword;database=school;";

    public static void Main(string[] args)
    {
        PopulateDatabase();
    }

    public static void PopulateDatabase()
    {
        // Write code below this line
        using (MySqlConnection connection = new MySqlConnection(connectionString))
        {
            try
            {
                connection.Open();
                Console.WriteLine("Successfully Connected To The MySQL Server");

                string tableCmdText = "CREATE TABLE teachers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30), age INT, experience FLOAT);";
                new MySqlCommand(tableCmdText, connection).ExecuteNonQuery();

                new MySqlCommand("INSERT INTO teachers (name, age, experience) VALUES ('John Smith', 35, 5.5);", connection).ExecuteNonQuery();
                new MySqlCommand("INSERT INTO teachers (name, age, experience) VALUES ('Anna Johnson', 40, 8.2);", connection).ExecuteNonQuery();
                new MySqlCommand("INSERT INTO teachers (name, age, experience) VALUES ('Robert Davis', 32, 3.1);", connection).ExecuteNonQuery();


                var queryCmd = new MySqlCommand("SELECT * FROM teachers;", connection);

                using (MySqlDataReader reader = queryCmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        int id = reader.GetInt32("id");
                        string name = reader.GetString("name");
                        int age = reader.GetInt32("age");
                        float exp = reader.GetFloat("experience");

                        Console.WriteLine($">> ID: {id}, NAME: {name}, AGE: {age}, EXPERIENCE: {exp}");
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
        }
        // Write code above this line
    }
}

Все було зрозуміло?

Секція 3. Розділ 9
course content

Зміст курсу

Introduction to .NET with C#

1. Introduction

What is the .NET Platform exactly?Creating a Console ApplicationTaking input from the UserTask - Building a Simple CalculatorDealing with Exceptions caused by User InputTask - Improving the CalculatorReading FilesWriting FilesTask - File Based Calculator

2. Asynchronous Programming and Introduction to Web Services

What is Asynchronous Programming?Implementing an Asynchronous MethodTask - Asynchronously Reading FilesIntroduction to APIsMaking a GET RequestTask - Using an APIHandling Exceptions caused by HTTP RequestTask - Exception Handling for API Response

3. Introduction to Databases with .NET

What is a Database?Structure of a Relational DatabaseSetting Up A MySQL ServerConnecting to the MySQL ServerTask - Writing Connection StringsExecuting CommandsTask - Executing MySQL CommandsMySqlCommand MethodsTask - Connecting to a MySQL Database and Executing Commands

4. CRUD Operations with MySQL

Introduction to CRUD OperationsInstalling & Using MySQL WorkbenchCREATE - Part ITask - Using CREATECREATE - Part IITask - Using INSERTReadTask - Using SELECTUpdateTask - Using UPDATEDelete - Part IDelete - Part IITask - Using DELETE

5. Optimizing Database Operations

Stored ProceduresTask - Executing Stored ProceduresDataReader vs DataSetDataAdapter & DataSetTask - Reading Tables using MySqlDataAdapterWhat's Next?

Introduction to .NET with C#

Task - Connecting to a MySQL Database and Executing CommandsTask - Connecting to a MySQL Database and Executing Commands

This task will recap most of the basic concepts of this chapter but the solution for it is very similar to what we had been practicing in the videos. Your task is to:

  • Write the code for connecting to a server. You can connect to your local server if you have it hosted on your PC. If not, you can enter placeholder values in the connection string. The database name in the connection string should be school;
  • Make sure you are correctly closing the connection after use; Use either the Close() method or the using statement;
  • Enclose the relevant code into a try-catch block;
  • Execute the command for creating a new table called teachers, which will have four columns, namely id, name, age, and experience. Use the following command: CREATE TABLE teachers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30), age INT, experience FLOAT);
  • Execute the following three commands, which will insert 3 rows into the database:
  • Finally, read and display the data of the 3 rows. The column name is of type String, age is of type Int32, and experience is of type Float.
Link to the Task: GitHub
You can use the GetFloat method of the MySqlDataReader for reading the experience column. 

using System;
using MySql.Data.MySqlClient;

public class MYSQL_Practice_1
{
    // Modify the connection string here
    public static string connectionString = "server=127.0.0.1;port=7777;user=root;password=Th!sIsMyPassword;database=school;";

    public static void Main(string[] args)
    {
        PopulateDatabase();
    }

    public static void PopulateDatabase()
    {
        // Write code below this line
        using (MySqlConnection connection = new MySqlConnection(connectionString))
        {
            try
            {
                connection.Open();
                Console.WriteLine("Successfully Connected To The MySQL Server");

                string tableCmdText = "CREATE TABLE teachers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30), age INT, experience FLOAT);";
                new MySqlCommand(tableCmdText, connection).ExecuteNonQuery();

                new MySqlCommand("INSERT INTO teachers (name, age, experience) VALUES ('John Smith', 35, 5.5);", connection).ExecuteNonQuery();
                new MySqlCommand("INSERT INTO teachers (name, age, experience) VALUES ('Anna Johnson', 40, 8.2);", connection).ExecuteNonQuery();
                new MySqlCommand("INSERT INTO teachers (name, age, experience) VALUES ('Robert Davis', 32, 3.1);", connection).ExecuteNonQuery();


                var queryCmd = new MySqlCommand("SELECT * FROM teachers;", connection);

                using (MySqlDataReader reader = queryCmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        int id = reader.GetInt32("id");
                        string name = reader.GetString("name");
                        int age = reader.GetInt32("age");
                        float exp = reader.GetFloat("experience");

                        Console.WriteLine($">> ID: {id}, NAME: {name}, AGE: {age}, EXPERIENCE: {exp}");
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
        }
        // Write code above this line
    }
}

Все було зрозуміло?

Секція 3. Розділ 9
some-alt