Pharma Management System
_______________________________________
Database Queries----PharmaDB
_____________________________
create database PharmaDB;
use PharmaDB;
create table Suppliers(
Id varchar(100),
Name varchar(100),
Address varchar(100)
);
insert into Suppliers values('AMH','AM HealthCare Private Limited','hyderabad');
insert into Suppliers values('CMB','CM Biological','vijayawada');
insert into Suppliers values('ESB','ES Biotech','warangal');
create table PharmaceuticalItemType(
Id varchar(100),
Name varchar(100)
);
insert into PharmaceuticalItemType values('1','Tablet');
insert into PharmaceuticalItemType values('2','Syrup');
create table PharmaceuticalItems(
Id int identity(1,1) not null,
Name varchar(100),
Cost float,
QuantityInStock int,
TypeId varchar(100),
SupplierId varchar(100)
);
select * from PharmaceuticalItems;
select * from PharmaceuticalItemType;
select * from Suppliers;
***********************************************************************************
***
app.config
_______________
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.7.2" />
</startup>
<connectionStrings>
<add name="PMSConnection" connectionString="data source=Database
Server;initial catalog=PharmaDB;integrated security=true;"/>
</connectionStrings>
</configuration>
***********************************************************************************
****
PharmaItemType.cs
________________________
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Pharma
{
public class PharmaItemType
{
public string Id { get; set; }
public string Name { get; set; }
}
}
***********************************************************************************
********
PharmaItem.cs
___________________
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Pharma
{
public class PharmaItem
{
public string Id { get; set; }
public string Name { get; set; }
public double Cost { get; set; }
public int QuantityInStock { get; set; }
}
}
***********************************************************************************
***
Suppliers.cs
___________________________
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Pharma
{
public class Suppliers
{
public string Id { get; set; }
public string Name { get; set; }
public string Address { get; set; }
}
}
***********************************************************************************
**
PharmaDAL.cs
______________________________
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Configuration;
namespace Pharma
{
public class PharmaDAL
{
string connection =
ConfigurationManager.ConnectionStrings["PMSConnection"].ConnectionString;
public PharmaDAL() { }
public bool InsertPharmaItem(string name, double cost, int quantityInStock,
int typeId, string supplierId)
try
{
using (SqlConnection con = new SqlConnection(connection))
{
con.Open();
string querystring = "insert into PharmaceuticalItems
values('" + name + "','" + cost + "','" + quantityInStock + "','" + typeId + "','"
+ supplierId + "')";
using (SqlCommand cmd = new SqlCommand(querystring, con))
{
int result = cmd.ExecuteNonQuery();
return result > 0?true:false;
}
}
catch (Exception e)
{
Console.WriteLine("Error : " + e.Message);
return false;
}
public List<PharmaItem> GetPharmaItemDetails()
{
List<PharmaItem> l1 = new List<PharmaItem>();
using (SqlConnection con = new SqlConnection(connection))
{
con.Open();
string querystring = "Select * from PharmaceuticalItems order
by Id";
using (SqlCommand cmd = new SqlCommand(querystring, con))
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
PharmaItem p1 = new PharmaItem
{
Id = reader[0].ToString(),
Name = reader[1].ToString(),
Cost = (double)reader[2],
QuantityInStock = (int)reader[3]
};
l1.Add(p1);
}
}
}
}
return l1;
}
public List<PharmaItemType> GetPharmaTypes()
{
List<PharmaItemType> l1 = new List<PharmaItemType>();
using (SqlConnection con = new SqlConnection(connection))
{
con.Open();
string querystring = "Select * from PharmaceuticalItemType order by
Id";
using (SqlCommand cmd = new SqlCommand(querystring, con))
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
PharmaItemType p1 = new PharmaItemType
{
Id = reader[0].ToString(),
Name = reader[1].ToString()
};
l1.Add(p1);
}
}
}
}
return l1;
public List<Suppliers> GetSuppliers()
{
List<Suppliers> l1 = new List<Suppliers>();
using (SqlConnection con = new SqlConnection(connection))
{
con.Open();
string querystring = "Select * from Suppliers ORDER BY Name";
using (SqlCommand cmd = new SqlCommand(querystring, con))
{
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Suppliers p1 = new Suppliers
{
Id = reader[0].ToString(),
Name = reader[1].ToString(),
Address = reader[2].ToString()
};
l1.Add(p1);
}
}
}
}
return l1;
public List<PharmaItem> GetPharmaItemDetailsAfterDiscount(double
cost,double discount)
{
List<PharmaItem> l1 = new List<PharmaItem>();
using (SqlConnection con = new SqlConnection(connection))
{
con.Open();
string querystring = "Select * from PharmaceuticalItems where Cost
> @cost";
using (SqlCommand cmd = new SqlCommand(querystring, con))
{
cmd.Parameters.AddWithValue("@cost", cost);
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
PharmaItem p1 = new PharmaItem();
p1.Id = reader[0].ToString();
p1.Name = reader[1].ToString();
double d = double.Parse(reader[2].ToString());
p1.Cost = d - ( d * (discount / 100));
p1.QuantityInStock = (int)reader[3];
l1.Add(p1);
}
}
}
}
return l1;
*******************************************************************************
program.cs
_____________________
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Pharma
{
public class Program
{
static void Main(string[] args)
{
while (true)
try
Console.WriteLine("\nMenu\n1.Add Pharmaceutical Items\n2.View
Pharmaceutical Items\n3.View Items on Discount\n4.Exit\n");
Console.Write("Enter Your Choice : ");
int choice = Convert.ToInt32(Console.ReadLine());
Console.WriteLine();
switch (choice)
case 1:
AddNewPharmaItem();
break;
case 2:
ViewPharmaItems();
break;
case 3:
{
Console.Write("Enter Cost : ");
double cost = double.Parse(Console.ReadLine());
Console.Write("Enter Discount : ");
double discount = double.Parse(Console.ReadLine());
ViewProductsAfterDiscount(cost, discount);
break;
case 4:
Console.WriteLine("Exiting from the Application");
Environment.Exit(0);
break;
default:
Console.WriteLine("Invalid Choice.Please Enter a
Valid Choice");
break;
catch (Exception e)
Console.WriteLine(e.Message);
public static void ViewPharmaItems()
{
try
PharmaDAL pharmaDalObject = new PharmaDAL();
var pharmaData = pharmaDalObject.GetPharmaItemDetails();
if (pharmaData == null || pharmaData.Count == 0)
Console.WriteLine("No Records Found");
else if (pharmaData.Count > 0)
Console.WriteLine("{0,-10}{1,-20}{2,-10}{3,-30}", "Id", "Name",
"Cost", "Quality In Stock");
foreach (var item in pharmaData)
Console.WriteLine("{0,-10}{1,-20}{2,-10}{3,-30}", item.Id,
item.Name, item.Cost, item.QuantityInStock);
catch (Exception ex)
Console.WriteLine(ex.Message);
public static void AddNewPharmaItem()
try
PharmaDAL pharmaDalObject = new PharmaDAL();
try
{
Console.Write("Pharma Item Name : ");
string name = Console.ReadLine().TrimStart();
double cost = 0.0;
do
Console.Write("Price : ");
cost = double.Parse(Console.ReadLine().TrimStart());
if (cost <= 0)
Console.WriteLine("Price should be Greater than Zero");
} while (cost <= 0);
int stockCount = 0;
do
Console.Write("Quantity In Stock : ");
stockCount = int.Parse(Console.ReadLine().TrimStart());
if (stockCount <= 0)
Console.WriteLine("Quantity In Stock should be Greater
then Zero");
} while (stockCount <= 0);
var pharmaItemTypes = pharmaDalObject.GetPharmaTypes();
Console.WriteLine("\nPharma Item Types\n");
Console.WriteLine("{0,-5}{1,-10}", "Id", "Name");
foreach (var item in pharmaItemTypes)
{
Console.WriteLine("{0,-5}{1,-10}", item.Id, item.Name);
Console.Write("Choose a Type Id : ");
int typeId = int.Parse(Console.ReadLine());
Console.WriteLine("\nPharma Item Suppliers\n");
var supplierDetails = pharmaDalObject.GetSuppliers();
Console.WriteLine("{0,-5}{1,-10}", "Id", "Name");
foreach (var item in supplierDetails)
Console.WriteLine("{0,-5}{1,-10}", item.Id, item.Name);
Console.Write("Choose a Supplier Id : ");
string supplierId = Console.ReadLine().TrimStart();
bool status = pharmaDalObject.InsertPharmaItem(name, cost,
stockCount, typeId, supplierId);
if (status == true)
Console.WriteLine("\nPharmaceutical Item Inserted
Successfully");
else
Console.WriteLine("\n{0} is already added,Try adding
different item");
catch (Exception ex)
Console.WriteLine(ex.Message);
}
}
catch (Exception ex)
Console.WriteLine(" " + ex.Message);
public static void ViewProductsAfterDiscount(double cost, double discount)
try
PharmaDAL pharmaDalObject = new PharmaDAL();
var pharmaData =
pharmaDalObject.GetPharmaItemDetailsAfterDiscount(cost, discount);
if (pharmaData == null || pharmaData.Count == 0)
Console.WriteLine("\nNo Records found");
else if (pharmaData.Count > 0)
Console.WriteLine("\n{0,-10}{1,-20}{2,-25}{3,-20}", "Id",
"Name", "Price After Discount", "Quantity In Stock");
foreach (var item in pharmaData)
Console.WriteLine("\n{0,-10}{1,-20}{2,-25}{3,-20}",
item.Id, item.Name, item.Cost, item.QuantityInStock);
catch (Exception ex)
Console.WriteLine(" " + ex.Message);
}
}
}
*******************************************_____THE
END_____*********************************
