Notes

create database College

create table Department(dept_id int, dept_name nvarchar(20))
Insert into Department values (1, 'HR')
Insert into Department values (2, 'IT')
Insert into Department values (3, 'ServiceNow')
Insert into Department values (4, 'Training')
Insert into Department values (5, 'Management')

SElect * from Department

create table Course(course_id int, course_name nvarchar(20))
Insert into Course values(101, 'Physics')
Insert into Course values(102, 'Chemistry')
Insert into Course values(103, 'Maths')
Insert into Course values(104, 'Bio')
Insert into Course values(105, 'Physics')

SElect * from Course


create table Teacher(teacher_id int, name nvarchar(20), dept_id int)

Insert into Teacher values(201, 'Ram', 4)
Insert into Teacher values(202, 'Shyam', 3)
Insert into Teacher values(203, 'Sita', 5)
Insert into Teacher values(204, 'Riya', 1)
Insert into Teacher values(205, 'Raman', 2)

Select * from Teacher

create table Students(student_id int, name nvarchar(20), gender nvarchar(10), course_id int)

Insert into Students values(1001, 'Shreya', 'Female', 105)
Insert into Students values(1002, 'Rohan', 'Male', 103)
Insert into Students values(1003, 'Sneha', 'Female', 101)
Insert into Students values(1004, 'Rohit', 'Male', 104)
Insert into Students values(1005, 'Adam', 'Male', 102)

Select * from Students

create table Theory(id int, name nvarchar(15), student_id int, teacher_id int)

Insert into Theory values(601, 'MWE', 1003, 202)
Insert into Theory values(602, 'BEE', 1001, 204)
Insert into Theory values(603, 'EDC', 1004, 205)
Insert into Theory values(604, 'MME', 1002, 201)
Insert into Theory values(605, 'AEC', 1005, 203)

Select * from Theory

create table Labs(id_lab int, name nvarchar(15), student_id int, teacher_id int)

Insert into Labs values(901, 'bio lab', 1002, 203)
Insert into Labs values(902, 'chem lab', 1003, 201)
Insert into Labs values(903, 'phy lab', 1002, 205)
Insert into Labs values(904, 'comp lab', 1004, 202)
Insert into Labs values(905, 'geo lab', 1005, 204)

Select * from Labs

create table Infrastructures(name nvarchar(20), numbers int)

Insert into Infrastructures values('Funnel', 100)
Insert into Infrastructures values('Test tube', 120)
Insert into Infrastructures values('Compputers ', 200)
Insert into Infrastructures values('Measuring Rod', 50)

Select * from Infrastructures

CREATE PROCEDURE spstu
AS BEGIN
SELECT s.student_id, s.name, s.gender, s.course_id ,c.course_name
FROM Course c
full join Students s
ON s.course_id=c.course_id
END;
EXEC spstu;

CREATE PROCEDURE spteacher
@new int
AS BEGIN
SELECT * from Teacher t JOIN Department d ON t.dept_id=d.dept_id where d.dept_id = @new;
END
EXEC spteacher 4;

using Microsoft.Data.SqlClient;
using static Azure.Core.HttpHeader;
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 SQLCommand
{
SqlConnection conn;
string connectionString = "Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=College;Integrated Security=SSPI";
public SQLCommand()
{
conn = new SqlConnection(connectionString);
}
static void Main()
{
SQLCommand sQLCommand = new SQLCommand();
Console.WriteLine("======================Before Inserting=======================");
sQLCommand.Readdata();
Console.WriteLine("======================After Inserting=======================");
sQLCommand.InsertData();
sQLCommand.Readdata();
Console.WriteLine("======================After Updating======================");
sQLCommand.UpdateData();
sQLCommand.Readdata();

Console.WriteLine("======================After Deleting======================");
sQLCommand.DeleteData();
sQLCommand.Readdata();

Console.WriteLine("======================Get Count======================");
Console.WriteLine(sQLCommand.GetCount());


}
public void Readdata()
{
SqlDataReader rdr = null;
try
{
conn.Open();
SqlCommand cmd = new SqlCommand("Select * from Students", conn);
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Console.WriteLine(rdr[1]);
}

}
finally
{
if (rdr != null)
{
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
}
}
public void InsertData()
{
try
{

conn.Open();
string query = @"INSERT INTO Students(student_id,name, gender, course_id)
values(1006,'Kriti', 'Female', 103)";
SqlCommand cmd = new SqlCommand(query, conn);
cmd.ExecuteNonQuery();
}
finally
{
if (conn != null)
{
conn.Close();
}

}
}
public void UpdateData()
{
try
{

conn.Open();
string query = @"UPDATE Students SET name='Roma' WHERE name='Sneha'";
SqlCommand cmd = new SqlCommand(query, conn);
cmd.ExecuteNonQuery();
}
finally
{
if (conn != null)
{
conn.Close();
}

}
}
public void DeleteData()
{
try
{

conn.Open();
string query = @"DELETE FROM Students WHERE name='Rohit'";
SqlCommand cmd = new SqlCommand(query, conn);
cmd.ExecuteNonQuery();
}
finally
{
if (conn != null)
{
conn.Close();
}

}
}
public int GetCount()
{
int count = -1;
try
{

conn.Open();
string query = @"SELECT count(*) from Students";
SqlCommand cmd = new SqlCommand(query, conn);
count = Convert.ToInt32(cmd.ExecuteScalar());
}
finally
{
if (conn != null)
{
conn.Close();
}

}
return count;
}
}
}

############################################################################
using Microsoft.Data.SqlClient;
using static Azure.Core.HttpHeader;
using System.Data;
using System;
using System.Data;
using System.Data.SqlClient;

namespace ConsoleApp5
{
/*class SpCalling
{
static void Main(string[] args)
{
try
{
//Store the connection string in the ConnectionString variable
string ConnectionString = "Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=College;Integrated Security=SSPI";



//Create the SqlConnection object
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
//Create the SqlCommand object by passing the stored procedure name and connection object as parameters
SqlCommand cmd = new SqlCommand("spstu", connection)
{
//Specify the command type as Stored Procedure
CommandType = CommandType.StoredProcedure
};

//Open the Connection
connection.Open();

//Execute the command i.e. Executing the Stored Procedure using ExecuteReader method
//SqlDataReader requires an active and open connection
SqlDataReader sdr = cmd.ExecuteReader();

//Read the data from the SqlDataReader
//Read() method will returns true as long as data is there in the SqlDataReader
while (sdr.Read())
{
//Accessing the data using the string key as index
//Console.WriteLine(sdr["ID"] + ", " + sdr["Name"] + ", " + sdr["Gender"] + ", " + sdr["Salary"]);

//Accessing the data using the integer index position as key


Console.WriteLine(sdr[0] + ", " + sdr[1] + ", " + sdr[2] + ", " + sdr[3]);
}
}
}
catch (Exception ex)
{
Console.WriteLine($"Exception Occurred: {ex.Message}");
}
Console.ReadKey();
}
}*/
}


############################################################################

using Microsoft.Data.SqlClient;
using static Azure.Core.HttpHeader;
using System.Data;
using System;
using System.Data;
using System.Data.SqlClient;

namespace ConsoleApp5
{
class SpCalling
{
static void Main(string[] args)
{
try
{
//Store the connection string in the ConnectionString variable
string ConnectionString = "Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=College;Integrated Security=SSPI";



//Create the SqlConnection object
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
//Create the SqlCommand object by passing the stored procedure name and connection object as parameters
SqlCommand cmd = new SqlCommand("spteacher", connection)
{
//Specify the command type as Stored Procedure
CommandType = CommandType.StoredProcedure
};
//Create an instance of SqlParameter
SqlParameter param1 = new SqlParameter
{
ParameterName = "@new", //Parameter name defined in stored procedure
SqlDbType = SqlDbType.Int, //Data Type of Parameter
Value = 4, //Value passes to the paramtere
Direction = ParameterDirection.Input //Specify the parameter as input
};

//Open the Connection
connection.Open();

//Execute the command i.e. Executing the Stored Procedure using ExecuteReader method
//SqlDataReader requires an active and open connection
SqlDataReader sdr = cmd.ExecuteReader();

//Read the data from the SqlDataReader
//Read() method will returns true as long as data is there in the SqlDataReader
while (sdr.Read())
{
//Accessing the data using the string key as index
//Console.WriteLine(sdr["ID"] + ", " + sdr["Name"] + ", " + sdr["Gender"] + ", " + sdr["Salary"]);

//Accessing the data using the integer index position as key


Console.WriteLine(sdr[0] + ", " + sdr[1] + ", " + sdr[2] + ", " + sdr[3]);
}
}
}
catch (Exception ex)
{
Console.WriteLine($"Exception Occurred: {ex.Message}");
}
Console.ReadKey();
}
}
}

public void spCall3()
{
SqlDataReader rdr = null;

try
{
conn.Open();
SqlCommand cmd = new SqlCommand("spCourseOfDepartment", conn)
{
CommandType = CommandType.StoredProcedure
};
SqlParameter course = new SqlParameter
{
ParameterName = "@Course",
SqlDbType = SqlDbType.VarChar,
Size=50,
Value = "Communication Systems",
Direction = ParameterDirection.Input
};
SqlParameter depname = new SqlParameter
{
ParameterName = "@DepName",
SqlDbType = SqlDbType.VarChar,
Size= 50,
Direction = ParameterDirection.Output
};
cmd.Parameters.Add(course);
cmd.Parameters.Add(depname);
rdr = cmd.ExecuteReader();

Console.WriteLine(cmd.Parameters["@DepName"].Value);

}

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

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


public void spCall4()
{
SqlDataReader rdr = null;

try
{
conn.Open();
SqlCommand cmd = new SqlCommand("spStudentinCouse3", conn)
{
CommandType = CommandType.StoredProcedure
};
SqlParameter depname = new SqlParameter
{
ParameterName = "@StuID",
SqlDbType = SqlDbType.Int,
Value = 1001,
Direction = ParameterDirection.Input
};
SqlParameter returnval = new SqlParameter
{
ParameterName = "@Returnvaluee",
SqlDbType = SqlDbType.Int,

Direction = ParameterDirection.ReturnValue
};

cmd.Parameters.Add(depname);
cmd.Parameters.Add(returnval);
cmd.ExecuteNonQuery();
Console.WriteLine((int)cmd.Parameters["@Returnvaluee"].Value);
}

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

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


}
}