Cursos /
Introduction to .NET with C#
Task - 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 theusing
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, namelyid
,name
,age
, andexperience
. 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 typeString
,age
is of typeInt32
, andexperience
is of typeFloat
.
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
}
}
¿Todo estuvo claro?
Sección 3. Capítulo 9
Contenido del Curso
Introduction to .NET with C#
2. Asynchronous Programming and Introduction to Web Services
3. Introduction to Databases with .NET
Introduction to .NET with C#
Task - 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 theusing
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, namelyid
,name
,age
, andexperience
. 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 typeString
,age
is of typeInt32
, andexperience
is of typeFloat
.
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
}
}
¿Todo estuvo claro?
Sección 3. Capítulo 9