Notes

==> Creating Tables and Inserting data

create database Movies

create table ratings(
id int ,
rating int ,
user_id int ,
movie_id int
)

insert into ratings(id,rating,user_id,movie_id)
values(1,4 , 101,201),
(2,3.8 , 102,202),
(3,5 , 103,203),
(4,3.5 , 104,204),
(5,4.5 , 105,205)

create table users(
id int,
first_name nvarchar(20) ,
last_name nvarchar(20),
email nvarchar(20)
)

insert into users(id,first_name,last_name,email)
values(101 , 'Ritu' ,'Saha','[email protected]'),
(102 , 'Madhu' ,'Singh','[email protected]'),
(103 , 'Rohan' ,'Das','[email protected]'),
(104 , 'Tiyasha' ,'Dhar','[email protected]'),
(105 , 'Soham' ,'Sah','[email protected]')

create table movies(
id int,
name nvarchar(20),
description nvarchar(20),
)

insert into movies(id , name, description)
values(201 , 'Andhadhun','Thriller'),
(202 , 'Veronica','Horror'),
(203 , 'Lakshya','War'),
(204 , 'The Body','Mystery'),
(205 , 'Inception','Sci-fi')

create table tags(
id int ,
tag nvarchar(20),
user_id int ,
movie_id int
)

insert into tags(id,tag,user_id,movie_id)
values(1,'Above Average',101,201),
(2,'Average',102,202),
(3,'Good',103,203),
(4,'Average',104,204),
(5,'Good',105,205)

==>SP without Parameter Joining 2 tables

Create procedure sprating_movies
as
begin
Select r.id,r.rating,r.user_id,r.movie_id , m.name, m.description
from ratings r full join movies m on r.movie_id = m.id
end

sprating_movies

==> SP with Parameter


Create procedure sp_movieid
@tag nvarchar(20),
@movie_id int Output
as
begin
Select @tag = tag
from tags
where movie_id=@movie_id
end

Declare @id int
Execute sp_movieid 'Above Average',@id output
Print @id


----------ADO DOTNET--------
===> All the Crud Operation and used ExecuteReader , ExecuteNonQuery and ExecuteScalar

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

namespace ConsoleApp4
{
internal class Movies
{
SqlConnection conn;
string connectionString = "Data Source=(localdb)MSSQLLocalDB;Initial Catalog=Movies;Integrated Security=SSPI";
public Movies()
{
conn = new SqlConnection(connectionString);
}
static void Main()
{
Movies sQLCommand = new Movies();
Console.WriteLine("Before Inserting");
sQLCommand.Readdata();

sQLCommand.Insertdata();
Console.WriteLine();
Console.WriteLine("After Inserting");
sQLCommand.Readdata();

sQLCommand.UpdateData();
Console.WriteLine();
Console.WriteLine("After Updating");
sQLCommand.Readdata();

sQLCommand.Deletedata();
Console.WriteLine();
Console.WriteLine("After Deleting");
sQLCommand.Readdata();

int noofrecords = sQLCommand.Getnoofrecords();
Console.WriteLine();
Console.WriteLine("No of Records:{0}", noofrecords);
}
public int Getnoofrecords()
{
int count = -1;
try
{
conn.Open();
SqlCommand cmd = new SqlCommand("select count(*) from movies", conn);
count = (int)cmd.ExecuteScalar();
}
finally
{
if (conn != null)
conn.Close();
}
return count;
}
public void Deletedata()
{
try
{
conn.Open();
string deleteString = @"delete from movies where id=206";
SqlCommand cmd = new SqlCommand();
cmd.CommandText = deleteString;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
}
finally
{
if (conn != null)
conn.Close();
}
}
public void UpdateData()
{
try
{
conn.Open();
string updateString = @" update movies
set description ='Sci-fi' where description ='Sci'";
SqlCommand cmd = new SqlCommand(updateString);
cmd.Connection = conn;
cmd.ExecuteNonQuery();
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
public void Insertdata()
{
try
{
conn.Open();
string insertString = @"Insert into movies(id , name, description)
values(206 , 'The Rim of the World ','Sci')";
SqlCommand cmd = new SqlCommand(insertString, conn);
cmd.ExecuteNonQuery();
}
finally
{
if (conn != null)
conn.Close();
}
}
public void Readdata()
{
SqlDataReader rdr = null;
try
{
conn.Open();
SqlCommand cmd = new SqlCommand("Select * from movies", conn);
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Console.WriteLine(rdr[0]);
Console.WriteLine(rdr[1]);
Console.WriteLine(rdr[2]);


}

}
finally
{
if (rdr != null)
{
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
}
}
}
}

==> Code for SP without Parameter
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Runtime.Intrinsics.Arm;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApp4
{
class rmovies
{
static void Main(string[] args)
{
try
{

string ConnectionString = "Data Source=(localdb)MSSQLLocalDB;Initial Catalog=Movies;Integrated Security=SSPI";

using (SqlConnection connection = new SqlConnection(ConnectionString))
{
SqlCommand cmd = new SqlCommand("sprating_movies", connection)
{

CommandType = CommandType.StoredProcedure
};

connection.Open();

SqlDataReader sdr = cmd.ExecuteReader();

while (sdr.Read())
{

Console.WriteLine(sdr["id"] + ", " + sdr["rating"]+", " + sdr["user_id"]+", " + sdr["movie_id"]+
", " + sdr["name"]+ ", " + sdr["description"]);

}
}
}
catch (Exception ex)
{
Console.WriteLine($"Exception Occurred: {ex.Message}");
}
Console.ReadKey();
}

}
}


==>Code for SP with Parameter

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

namespace ConsoleApp4
{
class movieid
{
static void Main(string[] args)
{
try
{

string ConnectionString = "Data Source=(localdb)MSSQLLocalDB;Initial Catalog=Movies;Integrated Security=SSPI";

using (SqlConnection connection = new SqlConnection(ConnectionString))
{
SqlCommand cmd = new SqlCommand()
{

CommandText = "sp_movieid",
Connection = connection,
CommandType = CommandType.StoredProcedure
};
SqlParameter param1 = new SqlParameter
{
ParameterName = "@tag",
SqlDbType = SqlDbType.NVarChar,
Value = "Good" ,
Direction = ParameterDirection.Input
};

cmd.Parameters.Add(param1);



SqlParameter outParameter = new SqlParameter
{
ParameterName = "@movie_id" ,
SqlDbType = SqlDbType.Int,
Direction = ParameterDirection.Output
};
cmd.Parameters.Add(outParameter);
connection.Open();

cmd.ExecuteNonQuery();


Console.WriteLine(outParameter.Value.ToString());


}
}
catch (Exception ex)
{
Console.WriteLine($"Exception Occurred: {ex.Message}");
}
Console.ReadKey();
}

}
}