Notes

create table Orders
(
orderID int,
customerID int,
dateOrdered datetime,
dateRequired datetime,
[status] nvarchar(50)
)

insert into Orders(orderID,customerID,dateOrdered,dateRequired,[status])
values (1,3,31-02-2000,23-08-2001,'Good'),
(2,4,21-04-2001,14-02-2002,'Average'),
(3,1,25-03-2003,27-04-2004,'Good'),
(4,2,11-04-2002,22-01-2003,'Bad'),
(5,5,15-11-2004,12-10-2005,'Average')


create table Products
(
productID int,
[name] nvarchar(40),
[description] nvarchar(40),
quantity int,
unitPrice int
)

insert into Products(productID,[name],[description],quantity,unitPrice)
values (1,'Ram','Red',10,5),
(2,'Hari','Blue',20,8),
(3,'Gopal','Pink',30,3),
(4,'John','Orange',50,4),
(5,'Sam','Green',40,6)


create table OrderDetails
(
orderID int,
productID int,
quantity int,
LineNumber int
)

insert into OrderDetails(orderID,productID,quantity,LineNumber)
values (1,2,20,5),
(2,4,10,7),
(3,1,30,9),
(4,3,40,3),
(5,5,50,8)


create procedure spOrderProduct
as
begin
select *
from Orders left outer join OrderDetails
on Orders.orderID=OrderDetails.orderID
end

execute spOrderProduct


create procedure spOrderProduct1
@description nvarchar(50),
@productID int
as
Begin
select * from Products
where [description]=@description
and productID=@productID
End



execute spOrderProduct1 'Red',1



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

namespace ConsoleApp3
{
/*internal class examplecrudoperations
{



SqlConnection conn;
public examplecrudoperations() //constructor
{
conn = new SqlConnection("Data source=(localdb)MSSQLLocalDB;Initial Catalog=feb24;Integrated Security=SSPI");
}

static void Main()
{
examplecrudoperations scd = new examplecrudoperations();
Console.WriteLine();
Console.WriteLine("Name Before Insert");
Console.WriteLine("------------");

scd.ReadData();


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

scd.ReadData();

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

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




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


public void ReadData()
{
SqlDataReader rdr = null;

try
{
conn.Open();

SqlCommand cmd = new SqlCommand("select * from Products", 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 insertString = @"insert into Products
(productID,[name],[description],quantity,unitPrice)
values(6,'Harry','Orange',60,4)";
SqlCommand cmd = new SqlCommand(insertString, conn);
cmd.ExecuteNonQuery();
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}

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

string updateString = @"
update Products
set Name = 'Shyam'
where Name = 'Harry'";

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 Products
where Name='Shyam'";

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 Products", conn);

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

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


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 examplecallspwithparameter
{
static void Main()
{
string connectionString =
"Data Source=(localdb)MSSQLLocalDB;Initial Catalog=feb24;" + "Integrated Security=true";

string queryString =
"spOrderProduct1";

string paramValue1 ="Blue";
int paramValue2 = 2;

using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(queryString, connection);
command.Parameters.AddWithValue("@description", paramValue1);command.Parameters.AddWithValue("@productID", paramValue2);
command.CommandType = CommandType.StoredProcedure;

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

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


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

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

static void Main()
{
exampleexecutescalar scd = new exampleexecutescalar();

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

}

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

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

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