public static List <Models.Device> GetDevices()
{
List <Models.Device> devices = new List <Models.Device>();
using (SqlConnection dbConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString))
{
if (dbConnection.State == System.Data.ConnectionState.Open)
{
dbConnection.Close();
}
dbConnection.Open();
using (SqlCommand cmd = new SqlCommand("spGetDevices", dbConnection))
{
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Models.Device data = new Models.Device
{
ID = int.Parse(reader["ID"].ToString()),
Code = reader["Code"].ToString(),
Name = reader["Name"].ToString(),
TypeID = int.Parse(reader["DeviceID"].ToString()),
TypeName = reader["DeviceQualification"].ToString()
};
devices.Add(data);
}
}
}
return(devices);
}
public static Models.Device FindDevice(int id)
{
Models.Device device = new Models.Device();
using (SqlConnection dbConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["dbConnection"].ToString()))
{
if (dbConnection.State == System.Data.ConnectionState.Open)
{
dbConnection.Close();
}
dbConnection.Open();
using (SqlCommand cmd = new SqlCommand("spFindDevice", dbConnection))
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@id", id);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
device.ID = int.Parse(reader["ID"].ToString());
device.Name = reader["Name"].ToString();
device.TypeID = int.Parse(reader["TypeID"].ToString());
device.Code = reader["Code"].ToString();
device.TypeName = reader["DeviceQualification"].ToString();
}
}
}
return(device);
}
private static List <Models.Device> GetEmployeeDevices(int id)
{
List <Models.Device> devices = new List <Models.Device>();
using (SqlConnection dbConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString))
{
if (dbConnection.State == ConnectionState.Open)
{
dbConnection.Close();
}
dbConnection.Open();
using (SqlCommand cmd = new SqlCommand("spGetDevicesByEmployeeID", dbConnection))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@employeeID", id);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Models.Device data = new Models.Device
{
ID = int.Parse(reader["ID"].ToString()),
Name = reader["Name"].ToString(),
TypeID = int.Parse(reader["TypeID"].ToString()),
TypeName = reader["TypeName"].ToString(),
Code = reader["Code"].ToString()
};
devices.Add(data);
}
}
}
return(devices);
}
public static List <Employee> GetEmployees()
{
List <Employee> Employees = new List <Employee>();
using (var dbConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["dbConnection"].ConnectionString))
{
if (dbConnection.State == ConnectionState.Open)
{
dbConnection.Close();
}
dbConnection.Open();
using (var cmd = new SqlCommand("spGetEmployees", dbConnection))
{
cmd.CommandType = CommandType.StoredProcedure;
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
var data = new Employee
{
ID = int.Parse(reader["ID"].ToString()),
Code = reader["Code"].ToString(),
First_Name = reader["First_Name"].ToString(),
Last_Name = reader["Last_Name"].ToString(),
Contact_No = reader["Contact_No"].ToString(),
Address = reader["Address"].ToString(),
Date_Hired = reader["Date_Hired"].ToString()
};
Employees.Add(data);
}
reader.Close();
}
foreach (Employee emp in Employees)
{
emp.Devices = new List <Models.Device>();
using (SqlCommand cmd1 = new SqlCommand("spGetDevicesByEmployeeID", dbConnection))
{
cmd1.CommandType = CommandType.StoredProcedure;
cmd1.Parameters.AddWithValue("@employeeID", emp.ID);
SqlDataReader reader1 = cmd1.ExecuteReader();
while (reader1.Read())
{
Models.Device device = new Models.Device
{
ID = int.Parse(reader1["ID"].ToString()),
Code = reader1["Code"].ToString(),
Name = reader1["Name"].ToString(),
TypeID = int.Parse(reader1["TypeID"].ToString()),
TypeName = reader1["TypeName"].ToString()
};
emp.Devices.Add(device);
}
reader1.Close();
}
}
}
return(Employees);
}