Notes

CREATE PROCEDURE get_movie_tags
as
BEGIN
SELECT m.name AS movie_name, u.first_name AS user_first_name, u.last_name AS user_last_name, t.tag AS movie_tag
FROM movies m
JOIN tags t ON m.id = t.movie_id
JOIN users u ON t.user_id = u.id;
END;
Exec get_movie_tags

CREATE PROCEDURE get_movies_by_tag(IN search_tag VARCHAR(255), IN search_limit INT)
BEGIN
SELECT m.name AS movie_name, m.description AS movie_description
FROM movies m
JOIN tags t ON m.id = t.movie_id
WHERE t.tag = search_tag
LIMIT search_limit;
END;

using System;
using System.Data.SqlClient;

class Program
{
static void Main(string[] args)
{
string connectionString = "Data Source=your-server;Initial Catalog=your-database;Integrated Security=True";

CreateUser(connectionString);
RetrieveUser(connectionString);
UpdateUser(connectionString);
DeleteUser(connectionString);
}

static void CreateUser(string connectionString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();

string insertSql = "INSERT INTO users (first_name, last_name, email) VALUES (@first_name, @last_name, @email);";
using (SqlCommand command = new SqlCommand(insertSql, connection))
{
command.Parameters.AddWithValue("@first_name", "John");
command.Parameters.AddWithValue("@last_name", "Doe");
command.Parameters.AddWithValue("@email", "[email protected]");

int rowsAffected = command.ExecuteNonQuery();

Console.WriteLine($"{rowsAffected} rows affected.");
}
}

PrintUsers(connectionString);
}

static void RetrieveUser(string connectionString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();

string selectSql = "SELECT * FROM users WHERE id = @id;";
using (SqlCommand command = new SqlCommand(selectSql, connection))
{
command.Parameters.AddWithValue("@id", 1);

using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
int id = (int)reader["id"];
string firstName = (string)reader["first_name"];
string lastName = (string)reader["last_name"];
string email = (string)reader["email"];

Console.WriteLine($"ID: {id}, Name: {firstName} {lastName}, Email: {email}");
}
}
}
}

PrintUsers(connectionString);
}

static void UpdateUser(string connectionString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();

string updateSql = "UPDATE users SET first_name = @first_name, last_name = @last_name WHERE id = @id;";
using (SqlCommand command = new SqlCommand(updateSql, connection))
{
command.Parameters.AddWithValue("@first_name", "Jane");
command.Parameters.AddWithValue("@last_name", "Doe");
command.Parameters.AddWithValue("@id", 1);

int rowsAffected = command.ExecuteNonQuery();

Console.WriteLine($"{rowsAffected} rows affected.");
}
}

PrintUsers(connectionString);
}

static void DeleteUser(string connectionString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();

string deleteSql = "DELETE FROM users WHERE id = @id;";
using (SqlCommand command = new SqlCommand(deleteSql, connection))
{
command.Parameters.AddWithValue("@id", 1);

int rowsAffected = command.ExecuteNonQuery();

Console.WriteLine($"{rowsAffected} rows affected.");
}
}

PrintUsers(connectionString);
}

static void PrintUsers(string connectionString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();

string selectSql = "SELECT * FROM users;";
using (SqlCommand command = new SqlCommand(selectSql, connection))
using (SqlDataReader reader = command.ExecuteReader())
{
Console.WriteLine("Users table:");
Console.WriteLine("------------");
while (reader.Read())
{
int id = (int)reader["id"];
string firstName = (string)reader["first_name"];
string lastName = (string)reader["last_name"];
string email = (string)reader["email"];

Console.WriteLine($"ID: {id}, Name: {firstName} {lastName}, Email: {email}");