Notes

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

namespace ConsoleApp4
{
internal class SQLPractice
{

SqlConnection conn;
string connectionString = "Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=Practice6;Integrated Security=SSPI";
public SQLPractice()
{
conn = new SqlConnection(connectionString);
}
static void Main()
{
SQLPractice sQLCommand = new SQLPractice();
// 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());
sQLCommand.ReaddataUsingSP();
sQLCommand.ReaddataUsingSPParams_GetProductById();
sQLCommand.ReaddataUsingSPParams_GetOrderDetailsByP_id();



}
public void ReaddataUsingSP()
{
SqlDataReader rdr = null;
try
{
conn.Open();
SqlCommand cmd = new SqlCommand("SP_GetAllProducts", conn);
cmd.CommandType = CommandType.StoredProcedure;
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Console.WriteLine(rdr[1]);
}

}
finally
{
if (rdr != null)
{
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
}
}
public void ReaddataUsingSPParams_GetProductById()
{
SqlDataReader rdr = null;
try
{
conn.Open();
SqlCommand cmd = new SqlCommand("SP_GetAllProductById", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param;
param = cmd.Parameters.Add("@id", SqlDbType.Int);
param.Value = Convert.ToInt32(Console.ReadLine());
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Console.WriteLine(rdr[1]);
}

}
finally
{
if (rdr != null)
{
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
}
}
public void ReaddataUsingSPParams_GetOrderDetailsByP_id()
{
SqlDataReader rdr = null;
try
{
conn.Open();
SqlCommand cmd = new SqlCommand("SP_GetProductNameByOrderId", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param;
param = cmd.Parameters.Add("@id", SqlDbType.Int);
param.Value = Convert.ToInt32(Console.ReadLine());
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Console.WriteLine(rdr[0]);
}

}
finally
{
if (rdr != null)
{
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
}
}
public void Readdata()
{
SqlDataReader rdr = null;
try
{
conn.Open();
SqlCommand cmd = new SqlCommand("Select * from Product", 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 Product(name,description,quantity,unitPrice)
values('Realme','Good With Performance',2,20000)";
SqlCommand cmd = new SqlCommand(query, conn);
cmd.ExecuteNonQuery();
}
finally
{
if (conn != null)
{
conn.Close();
}

}
}
public void UpdateData()
{
try
{

conn.Open();
string query = @"UPDATE Product SET unitPrice=25000 WHERE productID=105";
SqlCommand cmd = new SqlCommand(query, conn);
cmd.ExecuteNonQuery();
}
finally
{
if (conn != null)
{
conn.Close();
}

}
}
public void DeleteData()
{
try
{

conn.Open();
string query = @"DELETE FROM Product WHERE productID=105";
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 Product";
SqlCommand cmd = new SqlCommand(query, conn);
count = Convert.ToInt32(cmd.ExecuteScalar());
}
finally
{
if (conn != null)
{
conn.Close();
}

}
return count;
}
}
}