Notes

------------------------------------Database------------------------------------------------create database Orders

Create table Orders
( OrderId int ,
CustomerId int ,
DateOrdered nvarchar(20) ,
DateRequired nvarchar(20),
Status nvarchar(20)
)

Insert into Orders(OrderId,CustomerId,DateOrdered,DateRequired,Status)
Values( 1 , 101 , '2023/02/01' ,'2023/02/12','Delivered'),
( 2 , 102 , '2023/01/01' ,'2023/01/12','Not Delivered'),
( 3 , 103 , '2022/12/01' ,'2022/12/12','Delivered'),
( 4 , 104 , '2023/01/31' ,'2023/02/15','Delivered'),
( 5 , 105 , '2023/02/11' ,'2023/02/21','Not Delivered')

Create table OrderDetails
( OrderId int ,
ProductId int ,
Quantity int ,
LineNumber int
)

Insert into OrderDetails(OrderId,ProductId,Quantity,LineNumber)
Values(1,201,20,301),
(2,202,10,302),
(3,203,30,303),
(4,204,10,304),
(5,205,40,305)

Create table Products
( ProductId int ,
Name nvarchar(20),
Description nvarchar(20),
Quantity int ,
UnitPrice int
)

Insert into Products(ProductId,Name,Description,Quantity,UnitPrice)
Values(201 , 'Raj Singh' ,'TV' , 20 , 1000),
(202 , 'Ritu Saha' ,'AC' , 10 , 3000),
(203 , 'Varun Das' ,'Micro-Oven' , 30 , 2000),
(204 , 'Mohit Dhar' ,'Clothes' , 10 , 900),
(205 , 'Puja Sarkar' ,'Books' , 40 , 200)

Create Procedure spGetOrderDetails
as
begin
Select o.OrderId , o.CustomerId,d.ProductId,d.Quantity,d.LineNumber,o.DateOrdered,o.DateRequired
from Orders o Full join OrderDetails d
on o.OrderId= d.OrderId
end

spGetOrderDetails

Create Procedure spGetProductDescription
@ProductId int ,
@Description nvarchar(20) Output
as
begin
Select @Description = Description from Products where ProductId = @ProductId
end

Declare @d nvarchar(20)
Execute spGetProductDescription 203 , @d out
Print 'Product Description is '+@d


-------------------------------------------ADODOTNET----------------------------------------
==>CURD OPERATION
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 Orders
{
SqlConnection conn;
string connectionString = "Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=Orders;Integrated Security=SSPI";
public Orders()
{
conn = new SqlConnection(connectionString);
}
static void Main()
{
Orders sQLCommand = new Orders();
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 Orders", conn);
count =(int)cmd.ExecuteScalar();
}
finally
{
if (conn != null)
conn.Close();
}
return count;
}
public void Deletedata()
{
try
{
conn.Open();
string deleteString = @"delete from Orders where OrderId = 6";
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 Orders
set Status = 'Will be Delivered' Where Status = 'Not Delivered'";
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 Orders (OrderId,CustomerId,DateOrdered,DateRequired,Status)
Values( 6 , 106 , '2023/02/01' ,'2023/02/12','Delivered')";
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 Orders", conn);
rdr = cmd.ExecuteReader();
while (rdr.Read())
{

Console.WriteLine(rdr["OrderId"]+" " + rdr["CustomerId"]+" "+rdr["DateOrdered"]+" "
+ rdr["DateRequired"]+" " + rdr["Status"]);

}

}
finally
{
if (rdr != null)
{
rdr.Close();
}
if (conn != null)
{
conn.Close();
}
}
}
}
}
==> Sp without parameters
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 ConsoleApp6
{
class OrderDetails
{
static void Main(string[] args)
{
try
{

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

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

CommandType = CommandType.StoredProcedure
};

connection.Open();

SqlDataReader sdr = cmd.ExecuteReader();

while (sdr.Read())
{
Console.WriteLine(sdr["OrderId"] + " " + sdr["CustomerId"] + " "+sdr["ProductId"] + " "
+ sdr["Quantity"]+" " + sdr["LineNumber"]+" " + sdr["DateOrdered"]+" " + sdr["DateRequired"]);

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

}
}

==>Sp with parameters
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 Description
{
static void Main(string[] args)
{
try
{

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

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

CommandText = "spGet_Product_Description",
Connection = connection,
CommandType = CommandType.StoredProcedure
};
SqlParameter param1 = new SqlParameter
{
ParameterName = "@Description",
SqlDbType = SqlDbType.NVarChar,
Value ="TV",
Direction = ParameterDirection.Input
};

cmd.Parameters.Add(param1);



SqlParameter outParameter = new SqlParameter
{
ParameterName = "@ProductId" ,
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();
}

}
}