Notes

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

namespace ConsoleApp3
{
/*internal class revisioncurdoperationsoncourse
{
SqlConnection conn;
public revisioncurdoperationsoncourse() //constructor
{
conn = new SqlConnection("Data source=(localdb)MSSQLLocalDB;Initial Catalog=College;Integrated Security=SSPI");
}

static void Main()
{
revisioncurdoperationsoncourse scd = new revisioncurdoperationsoncourse();
Console.WriteLine();
Console.WriteLine("Course Before Insert");
Console.WriteLine("------------");
scd.ReadData();

scd.InsertData();
Console.WriteLine();
Console.WriteLine("Course After insert");
Console.WriteLine("------------");
scd.ReadData();

scd.UpdateData();
Console.WriteLine();
Console.WriteLine("Course After update");
Console.WriteLine("------------");
scd.ReadData();

scd.DeleteData();
Console.WriteLine();
Console.WriteLine("Course After Delete");
Console.WriteLine("------------");
scd.ReadData();

int numberOfRecordes = scd.GetNumberOfRecords();
Console.WriteLine();
Console.WriteLine("Number of Records : {0}", numberOfRecordes);

scd.UseOrderBy();
Console.WriteLine();
Console.WriteLine("Course order by");
Console.WriteLine("------------");
//scd.ReadData();


scd.UseGroupBy();
Console.WriteLine();
Console.WriteLine("Course group by");
Console.WriteLine("------------");
//scd.ReadData();


}
public void ReadData()
{
SqlDataReader rdr = null;

try
{
conn.Open();

SqlCommand cmd = new SqlCommand("select * from Department", conn);

rdr = cmd.ExecuteReader();

while (rdr.Read())
{
Console.WriteLine("{0},{1},{2},{3}", rdr[0], rdr[1], rdr[2], rdr[3]);
}
}

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

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

public void InsertData()
{
try
{
conn.Open();
string insertString = @"insert into Department
(id,Course,Teachers,Students)
values(6,'Maths',4,60)";
SqlCommand cmd = new SqlCommand(insertString, conn);
cmd.ExecuteNonQuery();
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}

public void UpdateData()
{
try
{
conn.Open();

string updateString = @"
update Department
set Course = 'Science'
where Course = 'Maths'";

SqlCommand cmd = new SqlCommand(updateString);
cmd.Connection = conn;

cmd.ExecuteNonQuery();
}

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

public void DeleteData()
{
try
{
conn.Open();

string deleteString = @"
delete from Department
where Course='Science'";

SqlCommand cmd = new SqlCommand();
cmd.CommandText = deleteString;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
}

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

public int GetNumberOfRecords()
{
int count = -1;
try
{
conn.Open();
SqlCommand cmd = new SqlCommand("select count(*) from Department", conn);

count = (int)cmd.ExecuteScalar();
}

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

public void UseOrderBy()
{
SqlDataReader rdr = null;

try
{
conn.Open();

SqlCommand cmd = new SqlCommand("select * from Department Order by Course Desc", conn);

rdr = cmd.ExecuteReader();

while (rdr.Read())
{
Console.WriteLine("{0},{1},{2},{3}", rdr[0], rdr[1], rdr[2], rdr[3]);
}
}

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

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

public void UseGroupBy()
{
SqlDataReader rdr = null;

try
{
conn.Open();

SqlCommand cmd = new SqlCommand("Select count(id) as p,Course from Department group by Course", conn);

rdr = cmd.ExecuteReader();

while (rdr.Read())
{
Console.WriteLine(rdr[0] + " " + rdr[1]);
}
}

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

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


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

namespace ConsoleApp3
{
/*internal class revisionspwithotparam
{

static void Main()
{

SqlConnection conn = new SqlConnection(
"Data Source=(localdb)MSSQLLocalDB;Initial Catalog=College;Integrated Security=SSPI");

SqlDataReader rdr = null;

try
{
conn.Open();

SqlCommand cmd = new SqlCommand("spwithoutparam", conn);

rdr = cmd.ExecuteReader();

while (rdr.Read())
{
Console.WriteLine(rdr[1]);
}
}

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

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


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

namespace ConsoleApp3
{
/*internal class revisionspwithparam
{
static void Main()
{
string connectionString =
"Data Source=(localdb)MSSQLLocalDB;Initial Catalog=College;" + "Integrated Security=true";

string queryString =
"spwithparam";

int paramValue1 = 1;


using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(queryString, connection);
command.Parameters.AddWithValue("@id", paramValue1);

command.CommandType = CommandType.StoredProcedure;

try
{
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("{0}",
reader[0]);
}
reader.Close();
}

catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
}*/
}


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

namespace ConsoleApp3
{
internal class revisionspwithoutputparam
{
static void Main()
{
string connectionString =
"Data Source=(localdb)MSSQLLocalDB;Initial Catalog=College;" + "Integrated Security=true";

SqlConnection connection = new SqlConnection(connectionString);



SqlCommand command = new SqlCommand("spoutputparmeter", connection);
command.CommandType = CommandType.StoredProcedure;


SqlParameter id = command.Parameters.Add("@id",SqlDbType.Int);
id.Value = 1;

SqlParameter location = command.Parameters.Add("@loac", SqlDbType.VarChar);
location.Direction = ParameterDirection.Output;

connection.Open();
command.ExecuteNonQuery();

string loc =(string)location.Value;
connection.Close();

Console.WriteLine(loc);

}
}
}


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

namespace ConsoleApp3
{
/*internal class revisionspwithreturn
{
static void Main()
{
string connectionString =
"Data Source=(localdb)MSSQLLocalDB;Initial Catalog=College;" + "Integrated Security=true";

SqlConnection connection = new SqlConnection(connectionString);



SqlCommand command = new SqlCommand("spreturn2", connection);
command.CommandType = CommandType.StoredProcedure;


SqlParameter returnpar = command.Parameters.Add("@total", SqlDbType.Int);
returnpar.Direction = ParameterDirection.ReturnValue;

connection.Open();
command.ExecuteNonQuery();

int tot=(int)returnpar.Value;
connection.Close();

Console.WriteLine(tot);

}
}*/
}