Notes

create database Assignment2

CREATE TABLE ratings (
id INT NOT NULL,
rating INT NOT NULL,
user_id INT NOT NULL,
movie_id INT NOT NULL,

);

INSERT INTO ratings (id,rating, user_id, movie_id)
VALUES (1,4, 1, 1),
(2,3, 2, 1),
(3,5, 3, 2),
(4,2, 4, 3),
(5,4, 1, 5),
(6,3, 2, 4);


CREATE TABLE users (
id INT NOT NULL,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(255) NOT NULL
);

INSERT INTO users (id,first_name, last_name, email)
VALUES (1,'John', 'Doe', '[email protected]'),
(2,'Deepraj', 'Podder', '[email protected]'),
(3,'Bob', 'Smith', '[email protected]'),
(4,'Alice', 'Jones', '[email protected]');

CREATE TABLE movies (
id INT NOT NULL ,
name VARCHAR(255) NOT NULL,
description TEXT NOT NULL
);

INSERT INTO movies (id,name, description)
VALUES (1,'The Shawshank Redemption', 'Two imprisoned men bond over a number of years, finding solace and eventual redemption through acts of common decency.'),
(2,'The Godfather', 'The aging patriarch of an organized crime dynasty transfers control of his clandestine empire to his reluctant son.'),


(3,'The Dark Knight', 'When the menace known as the Joker wreaks havoc and chaos on the people of Gotham, Batman must accept one of the greatest psychological and physical tests of his ability to fight injustice.'),
(4,'Pulp Fiction', 'The lives of two mob hitmen, a boxer, a gangster and his wife, and a pair of diner bandits intertwine in four tales of violence and redemption.'),
(5,'Forrest Gump', 'The presidencies of Kennedy and Johnson, the events of Vietnam, Watergate and other historical events unfold through the perspective of an Alabama man with an IQ of 75.');

CREATE TABLE tags (
id INT NOT NULL,
tag VARCHAR(255) NOT NULL,
user_id INT NOT NULL,
movie_id INT NOT NULL,

);

INSERT INTO tags (id,tag, user_id, movie_id)
VALUES (1,'Action', 1, 3),
(2,'Drama', 2, 1),
(3,'Crime', 3, 2),
(4,'Comedy', 4, 4),
(5,'Romance', 5, 5),
(6,'Thriller', 1, 1),
(7,'Adventure', 2, 3),
(8,'Sci-Fi', 3, 3),
(9,'Horror', 4, 2),
(10,'Mystery', 5, 1);

_________________________ STORED PROCEDURES __________________________


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_ratings_by_movies
@search_movies nvarchar(50)
as
BEGIN
SELECT r.rating AS rating, m.name AS movie_name, m.description AS movie_description
FROM ratings r
JOIN movies m ON m.id = r.movie_id
WHERE m.name = @search_movies

END;

exec get_ratings_by_movies @search_movies='Forrest Gump'

----------------------CODING ADO.NET-------------------------------

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;


namespace Assignment2
{
public class Program
{
SqlConnection conn;

public Program()
{
//instantiate the connection
conn = new SqlConnection("Data Source=(localdb)\MSSQLLocalDB; Initial Catalog = Assignment2; Integrated Security = SSPI");

}

// call methods that demo SqlCommand Capabilities
static void Main()
{
Program pr = new Program();

Console.WriteLine();
Console.WriteLine("ExecuteReader");
Console.WriteLine("Datas before insert");
Console.WriteLine("--------------------------");

//use ExecuteReader method
pr.ReadData();

//use ExecuteNonQuery method for Insert
pr.Insertdata();
Console.WriteLine();
Console.WriteLine("ExecuteNonQuery");
Console.WriteLine("Datas after inserting new record");
Console.WriteLine("--------------------------");

pr.ReadData();

//use ExecuteNonQuery method for Update
pr.Updatedata();
Console.WriteLine();
Console.WriteLine("ExecuteNonQuery");
Console.WriteLine("Datas after updating a record");
Console.WriteLine("--------------------------");
pr.ReadData();

//use ExecuteNonQuery method for Delete
pr.Deletedata();
Console.WriteLine();
Console.WriteLine("ExecuteNonQuery");
Console.WriteLine("Datas after deleting a record");
Console.WriteLine("--------------------------");
pr.ReadData();


//use ExecuteScalar method
Console.WriteLine() ;
Console.WriteLine("Execute Scalar");
Console.WriteLine("---------------------------------");

int numberOfMovies = pr.GetNumberOfMovies();
Console.WriteLine();
Console.WriteLine("Number of Records: " + numberOfMovies);

Console.WriteLine() ;
Console.WriteLine("Calling the SP get_movie_tags:");
Console.WriteLine("---------------------------------");
pr.StoredProc1();

Console.WriteLine();
Console.WriteLine("Calling the SP get_ratings_by_movies:");
Console.WriteLine("---------------------------------");
pr.StoredProc2();

}

//use ExecuteReader method

public void ReadData()
{
SqlDataReader rdr = null;
try
{
//2.Open the Conncetion
conn.Open();
SqlCommand cmd = new SqlCommand("select * from users", conn);

//4.Use Connection

//get query results
rdr = cmd.ExecuteReader();

//print first column of each record
while (rdr.Read())
{
Console.WriteLine("{0}t{1}t{2}t{3}", rdr[0], rdr[1], rdr[2], rdr[3]);
}
}
finally
{
//close the reader
if (rdr != null)
{
rdr.Close();
}

//close the connection
if (conn != null)
{
conn.Close();
}
}
}

public void Insertdata()
{
try
{
conn.Open();
string insertString = @"insert into users(id,first_name,last_name,email) values(9,'Rohan', 'Sarkar','[email protected]')";
SqlCommand cmd = new SqlCommand(insertString, conn);
cmd.ExecuteNonQuery();
}

finally
{
//close the connection
if (conn != null)
{
conn.Close();
}
}
}

public void Updatedata()
{
try
{
conn.Open();
string updateString = @"UPDATE users SET last_name='Abraham' WHERE id='1'";
SqlCommand cmd = new SqlCommand(updateString);
cmd.Connection = conn;
cmd.ExecuteNonQuery();
}

finally
{
//close the connection
if (conn != null)
{
conn.Close();
}
}
}

public void Deletedata()
{
try
{
conn.Open();
string deleteString = @"DELETE from users WHERE id='9'";
SqlCommand cmd = new SqlCommand();
cmd.CommandText = deleteString;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
}

finally
{
//close the connection
if (conn != null)
{
conn.Close();
}
}
}

public int GetNumberOfMovies()
{
int count = 0;
try
{
conn.Open();
SqlCommand cmd = new SqlCommand("select count(*) from users", conn);
count = (int)cmd.ExecuteScalar();

}

finally
{
//close the connection
if (conn != null)
{
conn.Close();
}
}
return count;

}

public void StoredProc1()
{
SqlDataReader rdr = null;
//try
//{
//2.Open the Conncetion
conn.Open();
SqlCommand cmd = new SqlCommand("get_movie_tags", conn);


cmd.CommandType = System.Data.CommandType.StoredProcedure;

//4.Use Connection

//get query results

rdr = cmd.ExecuteReader();

//print first column of each record
while (rdr.Read())
{
for (int i = 0; i < rdr.FieldCount; i++)
{
Console.WriteLine(rdr[i] + " ");
}
Console.WriteLine();

}
conn.Close();

}

public void StoredProc2()
{
SqlDataReader rdr = null;

//{
//2.Open the Conncetion
conn.Open();
SqlCommand cmd = new SqlCommand("get_ratings_by_movies", conn);


cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@search_movies", "Forrest Gump");
//4.Use Connection

//get query results

rdr = cmd.ExecuteReader();

//print
while (rdr.Read())
{
for (int i = 0; i < rdr.FieldCount; i++)
{
Console.WriteLine(rdr[i] + " ");
}
Console.WriteLine();

}
conn.Close();
}
}
}