public void Save(Doctor aDoctor)
{
string sqlQuery = "INSERT INTO tbl_doctors VALUES('" + aDoctor.Name + "','" + aDoctor.Degree + "','" + aDoctor.Specification + "', '" + aDoctor.DoctorCenterId + "')";
aSqlCommand = new SqlCommand(sqlQuery, aConnectionManager.GetConnection());
aSqlCommand.ExecuteNonQuery();
aConnectionManager.CloseConnection();
}
public List <Appointment> GetAppointmentsByComplaint(int cid)
{
using (var connection = SqlConnectionManager.GetConnection())
using (var command = new NpgsqlCommand()) {
var appointments = new List <Appointment>();
command.Connection = connection;
command.CommandText = "select * from getAppointmentsByComplaint(@cid)";
command.Parameters.AddWithValue("@cid", cid);
var reader = command.ExecuteReader();
var dt = new DateTime(1970, 01, 01);
while (reader.Read())
{
var a = new Appointment()
{
Id = int.Parse(reader["id"].ToString()),
Complaint = new Complaint()
{
Id = int.Parse(reader["complaint_id"].ToString())
},
Date = DateTime.Parse(reader["date"].ToString()),
Status = bool.Parse(reader["status"].ToString())
};
var ft = TimeSpan.Parse(reader["from_time"].ToString());
var tt = TimeSpan.Parse(reader["to_time"].ToString());
a.FromTime = dt + ft;
a.ToTime = dt + tt;
appointments.Add(a);
}
return(appointments);
}
}
public void ExecuteNonQuery(SqlCommand cmd)
{
var conn = SqlConnectionManager.GetConnection(_connectionString);
cmd.Connection = conn;
cmd.ExecuteNonQuery();
}
public LoginResult CaretakerLogin(string username, string password)
{
using (var connection = SqlConnectionManager.GetConnection())
using (var command = new NpgsqlCommand())
{
command.Connection = connection;
command.CommandText = "select caretakerLogin(@uname, @pwd)";
command.Parameters.AddWithValue("@uname", username);
command.Parameters.AddWithValue("@pwd", password);
var reader = command.ExecuteReader();
var result = LoginResult.InvalidRollNumber;
if (reader.Read())
{
int x = (int)reader.GetValue(0);
if (x == 1)
{
result = LoginResult.Successful;
}
else if (x == 0)
{
result = result = LoginResult.InvalidPassword;
}
}
return(result);
}
}
internal List <Center> GetTheCenter(string thanaId)
{
List <Center> aCenterList = new List <Center>();
string sqlQury = "SELECT * FROM tbl_centers WHERE thana_id = '" + thanaId + "'";
aSqlCommand = new SqlCommand(sqlQury, aConnectionManager.GetConnection());
aReader = aSqlCommand.ExecuteReader();
while (aReader.Read())
{
Center aCenter = new Center();
aCenter.CenterId = Convert.ToInt32(aReader["id"]);
aCenter.CenterName = aReader["name"].ToString();
aCenterList.Add(aCenter);
}
return(aCenterList);
}
private static SqlTableInformation CreateSqlTableInformation(string connectionString, string tableName)
{
var connection = SqlConnectionManager.GetConnection(connectionString);
var columns = GetColumnsInfo(connection, tableName);
// Checking if the necessary table exists
if (columns.Count == 0)
{
return(null);
}
var tableInformation = new SqlTableInformation(tableName);
foreach (var column in columns.Values)
{
tableInformation.AddColumnInformation(
new SqlColumnInformation(
column.Name,
column.IsPrimaryKey,
column.IsIdentity,
column.IsComputed,
column.IsNullable,
ConvertSqlTypeToSystemType(column.Type),
ConvertSqlTypeToSqlDbType(column.Type)
));
}
return(tableInformation);
}
public List <Class> GetClassesByTeacherAndCourse(int tid, int cid)
{
using (var classConnection = SqlConnectionManager.GetConnection())
using (var classCommand = new NpgsqlCommand())
{
classCommand.Connection = classConnection;
classCommand.CommandText = "select * from getClassesByTeacherAndCourse(@tid, @cid)";
classCommand.Parameters.AddWithValue("@tid", tid);
classCommand.Parameters.AddWithValue("@cid", cid);
var classReader = classCommand.ExecuteReader();
var classes = new List <Class>();
while (classReader.Read())
{
var c = new Class()
{
Id = int.Parse(classReader["id"].ToString()),
Branch = classReader["branch"].ToString(),
Year = int.Parse(classReader["year"].ToString()),
SectionFrom = int.Parse(classReader["sec_from"].ToString()),
SectionTo = int.Parse(classReader["sec_to"].ToString())
};
classes.Add(c);
}
return(classes);
}
}
public Teacher GetTeacherByCode(string code)
{
using (var connection = SqlConnectionManager.GetConnection())
using (var command = new NpgsqlCommand())
{
command.Connection = connection;
command.CommandText = "select * from profileteacherinfo(@code)";
command.Parameters.AddWithValue("@code", code);
var reader = command.ExecuteReader();
if (reader.Read())
{
return(new Teacher()
{
Id = int.Parse(reader["tid"].ToString()),
Name = reader["tname"].ToString(),
Code = reader["tcode"].ToString(),
Contact = reader["tcontact"].ToString(),
Designation = reader["tdes"].ToString(),
Email = reader["tmail"].ToString()
});
}
return(null);
}
}
void paytab()
{
int month = DateTime.Now.Month;
int year = DateTime.Now.Year;
var connection = SqlConnectionManager.GetConnection();
string query = "SELECT [BasicPayPD],[EAccno] FROM [dbo].[tblPayroll] WHERE [EId]='" + txtEid.Text + "' AND [Month]='" + month + "' AND [Year]='" + year + "'";
var cmd = SqlConnectionManager.GetCommand(query, connection);
cmd.Connection.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
btnUpdate.Text = "Update";
while (reader.Read())
{
txtAn.Text = reader["EAccno"].ToString();
txtBp.Text = reader["BasicPayPD"].ToString();
}
}
else
{
btnUpdate.Text = "Add";
// btnClear_Click(new object(), new EventArgs());
}
}
}
public Caretaker GetCaretakerInfo(string username)
{
using (var connection = SqlConnectionManager.GetConnection())
using (var command = new NpgsqlCommand()) {
command.Connection = connection;
command.CommandText = "select * from getCaretakerInfo(@uname)";
command.Parameters.AddWithValue("@uname", username);
var reader = command.ExecuteReader();
if (reader.Read())
{
return(new Caretaker()
{
Id = int.Parse(reader["id"].ToString()),
Name = reader["name"].ToString(),
Username = reader["username"].ToString(),
Email = reader["email"].ToString(),
Contact = reader["contact"].ToString(),
Hostel = new Hostel()
{
Id = int.Parse(reader["hostel_id"].ToString()),
Name = reader["hostel_name"].ToString()
}
});
}
return(null);
}
}
void emptab()
{
var connection = SqlConnectionManager.GetConnection();
string query = "SELECT * FROM [dbo].[tblEmployee] WHERE [EId]='" + txtEid.Text + "'";
var cmd = SqlConnectionManager.GetCommand(query, connection);
cmd.Connection.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
btnUpdate.Text = "Update";
while (reader.Read())
{
txtName.Text = reader.GetString(1);
txtDepartment.Text = reader.GetString(2);
txtDesignation.Text = reader.GetString(3);
DateTime dt1 = Convert.ToDateTime(reader["EJoiningDate"].ToString());
txtJoindate.Text = String.Format("{0:yyyy-MM-dd}", dt1);
txtGender.Text = reader.GetString(6);
DateTime dt2 = Convert.ToDateTime(reader["EDob"].ToString());
txtDob.Text = String.Format("{0:yyyy-MM-dd}", dt2);
txtAddress.Text = reader.GetString(8);
txtPhoneno.Text = reader.GetString(9);
txtEmail.Text = reader.GetString(10);
}
}
else
{
btnUpdate.Text = "Add";
// btnClear_Click(new object(), new EventArgs());
}
}
}
public Student GetStudentInfo(string rollno)
{
using (var connection = SqlConnectionManager.GetConnection())
using (var command = new NpgsqlCommand()
{
Connection = connection
}) {
command.CommandText = "select * from getStudentInfo(@rollno)";
command.Parameters.AddWithValue("@rollno", rollno);
var reader = command.ExecuteReader();
if (reader.Read())
{
return(new Student()
{
Id = int.Parse(reader["id"].ToString()),
Name = reader["name"].ToString(),
Rollno = reader["rollno"].ToString(),
PersonalContact = reader["personal_contact"].ToString(),
ParentContact = reader["parent_contact"].ToString(),
Gender = ((BitArray)reader["gender"]).Get(0) ? 'M' : 'F',
Email = reader["email"].ToString(),
Hostel = new Hostel()
{
Id = int.Parse(reader["hostel_id"].ToString()),
Name = reader["hostel_name"].ToString(),
RoomNumber = reader["hostel_room_no"].ToString(),
Type = ((BitArray)reader["gender"]).Get(0) ? "Boys" : "Girls"
}
});
}
return(null);
}
}
public List <Evaluation> GetClassEvaluationDetail(int classId, int examId)
{
using (var studentsConnection = SqlConnectionManager.GetConnection())
using (var studentsCommand = new NpgsqlCommand())
{
studentsCommand.Connection = studentsConnection;
studentsCommand.CommandText = "select * from getStudentsByClass(@cid)";
studentsCommand.Parameters.AddWithValue("@cid", classId);
var studentsReader = studentsCommand.ExecuteReader();
var evaluations = new List <Evaluation>();
while (studentsReader.Read())
{
var e = new Evaluation()
{
Student = new Student()
{
Id = int.Parse(studentsReader["id"].ToString()),
Rollno = studentsReader["rollno"].ToString(),
Name = studentsReader["name"].ToString()
}
};
using (var evalConnection = SqlConnectionManager.GetConnection())
using (var evalCommand = new NpgsqlCommand())
{
evalCommand.Connection = evalConnection;
evalCommand.CommandText = "select * from getStudentEvaluationDetail(@sid, @eid)";
evalCommand.Parameters.AddWithValue("@sid", e.Student.Id);
evalCommand.Parameters.AddWithValue("@eid", examId);
var evalReader = evalCommand.ExecuteReader();
if (evalReader.Read())
{
e.Id = int.Parse(evalReader["id"].ToString());
e.Examination = new Examination()
{
Id = int.Parse(evalReader["exam_id"].ToString()),
TotalMarks = int.Parse(evalReader["total_marks"].ToString())
};
e.Teacher = new Teacher()
{
Id = int.Parse(evalReader["teacher_id"].ToString())
};
e.MarksObtained = int.Parse(evalReader["marks_obtained"].ToString());
e.DateTime = DateTime.Parse(evalReader["datetime"].ToString());
e.AnswerSheet = evalReader["ans_sheet"].ToString();
e.Status = true;
}
else
{
e.Status = false;
}
}
evaluations.Add(e);
}
return(evaluations);
}
}
internal List <District> GetAllDistrict()
{
List <District> aDistrictList = new List <District>();
string sqlQuery = "SELECT * FROM tbl_districts ORDER BY id ASC";
aSqlCommand = new SqlCommand(sqlQuery, aConnectionManager.GetConnection());
aReader = aSqlCommand.ExecuteReader();
while (aReader.Read())
{
District aDistrict = new District();
aDistrict.DistrictId = Convert.ToInt32(aReader["id"]);
aDistrict.DistrictName = aReader["name"].ToString();
aDistrictList.Add(aDistrict);
}
aConnectionManager.CloseConnection();
return(aDistrictList);
}
protected void btnTimein_Click(object sender, EventArgs e)
{
var connection = SqlConnectionManager.GetConnection();
string eid = ((string)Session["id"]);
string intime = DateTime.Now.ToString("HH:mm");
string adate = DateTime.Today.ToString("MM/dd/yyyy");
string month = DateTime.Now.Month.ToString();
string year = DateTime.Now.Year.ToString();
string query = "SELECT * FROM [dbo].[tblAttendance] WHERE EId='" + eid + "' AND Adate='" + adate + "'";
var cmd = SqlConnectionManager.GetCommand(query, connection);
cmd.Connection.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
string it = "10:00";
while (reader.Read())
{
it = reader["Intime"].ToString();
}
if (it == "10:00")
{
lblError.Visible = false;
string query2 = "UPDATE tblAttendance SET Intime='" + intime + "', Outtime='19:30', Status='present' WHERE EId='" + eid + "' AND Adate='" + adate + "'";
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.InsertCommand = new SqlCommand(query2, connection);
adapter.InsertCommand.ExecuteNonQuery();
Response.Write("<script>alert('In-Time Registered Successfully.');</script>");
}
else
{
lblError.Visible = true;
lblError.Text = "In-Time already registered!";
}
}
else
{
string query2 = "SELECT * FROM tblEmployee";
var cmd2 = SqlConnectionManager.GetCommand(query2, connection);
using (SqlDataReader reader2 = cmd2.ExecuteReader())
{
if (reader2.HasRows)
{
SqlDataAdapter sa = new SqlDataAdapter();
while (reader2.Read())
{
int empid = reader2.GetInt32(0);
string insertQuery = "INSERT INTO tblAttendance (EId, Intime, Outtime, Adate, Status, Month, Year) VALUES ('" + empid + "', '10:00', '19:30', '" + adate + "', 'absent', '" + month + "', '" + year + "')";
sa.InsertCommand = new SqlCommand(insertQuery, connection);
sa.InsertCommand.ExecuteNonQuery();
}
}
}
Response.Write("<script>alert('Unable to register In-time, try again.');window.location = 'eAttendance.aspx';</script>");
}
}
}
internal string SaveMedicineToDb(Medicine aMedicine)
{
string sqlQuery = "INSERT INTO tbl_medicines VALUES('" + aMedicine.MedicineName + "', '" +
aMedicine.MedicinePower + "')";
aSqlCommand = new SqlCommand(sqlQuery, aConnectionManager.GetConnection());
int effectedRows = aSqlCommand.ExecuteNonQuery();
aConnectionManager.CloseConnection();
if (effectedRows > 0)
{
return("Medicine Add SuccesFully");
}
else
{
return("fail");
}
}
internal string AddNewDisease(Disease aDisease)
{
string sqlQuery = "INSERT INTO tbl_diseases VALUES('" + aDisease.DiseaseName + "', '" +
aDisease.DiseaseDescription + "', '" + aDisease.TreatementProcedure + "', '" +
aDisease.PreparedDrugs + "')";
aSqlCommand = new SqlCommand(sqlQuery, aConnectionManager.GetConnection());
int effectedRows = aSqlCommand.ExecuteNonQuery();
aConnectionManager.CloseConnection();
if (effectedRows > 0)
{
return("New diseases added succcesfully");
}
else
{
return("Falied");
}
}
void emptabAdd()
{
var connection = SqlConnectionManager.GetConnection();
string query = (@"INSERT INTO tblEmployee (EId, EName, EDepartment, EDesignation, EJoiningDate, ESex, EDob, EAddress, EPhoneNo, EEmail) VALUES ('" + txtEid.Text.Trim() + "', '" + txtName.Text.Trim() + "', '" + txtDepartment.Text.Trim() + "', '" + txtDesignation.Text.Trim() + "', '" + Convert.ToDateTime(txtJoindate.Text).ToString("MM/dd/yyyy") + "', '" + txtGender.Text.Trim() + "', '" + Convert.ToDateTime(txtDob.Text).ToString("MM/dd/yyyy") + "', '" + txtAddress.Text.Trim() + "', '" + txtPhoneno.Text.Trim() + "', '" + txtEmail.Text.Trim() + "')");
connection.Open();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.InsertCommand = new SqlCommand(query, connection);
adapter.InsertCommand.ExecuteNonQuery();
}
void paytabAdd()
{
var connection = SqlConnectionManager.GetConnection();
string query = (@"INSERT INTO tblPayroll (EId, EAccno, BasicPayPD, Month, Year) VALUES ('" + txtEid.Text.Trim() + "', '" + txtAn.Text.Trim() + "', '" + txtBp.Text.Trim() + "', '" + DateTime.Now.Month + "', '" + DateTime.Now.Year + "')");
connection.Open();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.InsertCommand = new SqlCommand(query, connection);
adapter.InsertCommand.ExecuteNonQuery();
}
public List <TeacherCourse> GetTeacherCourses(int tid)
{
using (var connection = SqlConnectionManager.GetConnection())
using (var command = new NpgsqlCommand())
{
var teacherCourses = new List <TeacherCourse>();
command.Connection = connection;
command.CommandText = "select * from courseslist(@tid)";
command.Parameters.AddWithValue("@tid", tid);
var reader = command.ExecuteReader();
while (reader.Read())
{
var teacherCourse = new TeacherCourse()
{
Teacher = new Teacher()
{
Id = tid
},
Course = new Course()
{
Id = int.Parse(reader["cid"].ToString()),
CourseName = reader["course_name"].ToString(),
CourseCode = reader["course_code"].ToString()
},
Classes = new List <Class>()
};
using (var classConnection = SqlConnectionManager.GetConnection())
using (var classCommand = new NpgsqlCommand())
{
classCommand.Connection = classConnection;
classCommand.CommandText = "select * from getClassesByTeacherAndCourse(@tid, @cid)";
classCommand.Parameters.AddWithValue("@tid", tid);
classCommand.Parameters.AddWithValue("@cid", teacherCourse.Course.Id);
var classReader = classCommand.ExecuteReader();
while (classReader.Read())
{
teacherCourse.Classes.Add(new Class()
{
Id = int.Parse(classReader["id"].ToString()),
Branch = classReader["branch"].ToString(),
Year = int.Parse(classReader["year"].ToString()),
SectionFrom = int.Parse(classReader["sec_from"].ToString()),
SectionTo = int.Parse(classReader["sec_to"].ToString())
});
}
}
teacherCourses.Add(teacherCourse);
}
return(teacherCourses);
}
}
private DataContext CreateDataContext()
{
IDbConnection connection = SqlConnectionManager.GetConnection(ConnectionString);
DataContext dataContext = (DataContext)Activator.CreateInstance(DataContextClass, connection);
if (_sqlLoggingContext.Enabled)
{
dataContext.Log = new SqlLoggerTextWriter(_sqlLoggingContext);
}
return(dataContext);
}
void emptabUpdate()
{
var connection = SqlConnectionManager.GetConnection();
string query = "UPDATE tblEmployee SET EName='" + txtName.Text.Trim() + "', EDepartment='" + txtDepartment.Text.Trim() + "', EDesignation='" + txtDesignation.Text.Trim() + "', EJoiningDate='" + Convert.ToDateTime(txtJoindate.Text).ToString("MM/dd/yyyy") + "', ESex='" + txtGender.Text.Trim() + "', EDob='" + Convert.ToDateTime(txtDob.Text).ToString("MM/dd/yyyy") + "', EAddress='" + txtAddress.Text.Trim() + "', EPhoneNo='" + txtPhoneno.Text.Trim() + "', EEmail='" + txtEmail.Text.Trim() + "' WHERE EId='" + txtEid.Text.Trim() + "'";
//var cmd = SqlConnectionManager.GetCommand(query, connection);
//cmd.Connection.Open();
connection.Open();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.UpdateCommand = new SqlCommand(query, connection);
adapter.UpdateCommand.ExecuteNonQuery();
}
void paytabUpdate()
{
int month = DateTime.Now.Month;
int year = DateTime.Now.Year;
var connection = SqlConnectionManager.GetConnection();
string query = "UPDATE tblPayroll SET EAccno='" + txtAn.Text.Trim() + "', BasicPayPD='" + txtBp.Text.Trim() + "' WHERE EId='" + txtEid.Text.Trim() + "' AND Month='" + month + "' AND Year='" + year + "'";
connection.Open();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.UpdateCommand = new SqlCommand(query, connection);
adapter.UpdateCommand.ExecuteNonQuery();
}
internal int SaveTreatement(Treatement aTreatement)
{
string sqlQuery = "INSERT INTO tbl_service_taken VALUES('" + aTreatement.PatientId + "', '" +
aTreatement.Observation + "', '" + aTreatement.Date + "', '" + aTreatement.DoctorId + "', " + aTreatement.CenterId + ")";
aSqlCommand = new SqlCommand(sqlQuery, aConnectionManager.GetConnection());
int effectedRows = aSqlCommand.ExecuteNonQuery();
if (effectedRows > 0)
{
aSqlCommand.CommandText = "SELECT MAX(id) AS Id FROM tbl_service_taken";
aReader = aSqlCommand.ExecuteReader();
aReader.Read();
int serviceId = Convert.ToInt32(aReader["Id"]);
aConnectionManager.CloseConnection();
return(serviceId);
}
else
{
aConnectionManager.CloseConnection();
return(0);
}
}
void atttabAdd()
{
string adate = DateTime.Today.ToString("MM/dd/yyyy");
string month = DateTime.Now.Month.ToString();
string year = DateTime.Now.Year.ToString();
var connection = SqlConnectionManager.GetConnection();
string query = (@"INSERT INTO tblAttendance (EId, Intime, Outtime, Adate, Status, Month, Year) VALUES ('" + txtEid.Text.Trim() + "', '10:00', '19:30', '" + adate + "', 'present', '" + month + "', '" + year + "')");
connection.Open();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.InsertCommand = new SqlCommand(query, connection);
adapter.InsertCommand.ExecuteNonQuery();
}
public List <EvaluationClass> GetEvaluationClassesByTeacherAndExam(int tid, int eid)
{
using (var classConnection = SqlConnectionManager.GetConnection())
using (var classCommand = new NpgsqlCommand())
{
classCommand.Connection = classConnection;
classCommand.CommandText = "select * from getClassesByTeacherAndExam(@tid, @eid)";
classCommand.Parameters.AddWithValue("@tid", tid);
classCommand.Parameters.AddWithValue("@eid", eid);
var classReader = classCommand.ExecuteReader();
var classes = new List <EvaluationClass>();
while (classReader.Read())
{
var e = new EvaluationClass();
var c = new Class()
{
Id = int.Parse(classReader["id"].ToString()),
Branch = classReader["branch"].ToString(),
Year = int.Parse(classReader["year"].ToString()),
SectionFrom = int.Parse(classReader["sec_from"].ToString()),
SectionTo = int.Parse(classReader["sec_to"].ToString())
};
e.TeacherClass = c;
using (var statusConnection = SqlConnectionManager.GetConnection())
using (var statusCommand = new NpgsqlCommand())
{
statusCommand.Connection = statusConnection;
statusCommand.CommandText = "select * from getClassEvaluationStatus(@cid, @eid)";
statusCommand.Parameters.AddWithValue("@cid", c.Id);
statusCommand.Parameters.AddWithValue("@eid", eid);
var statusReader = statusCommand.ExecuteReader();
if (statusReader.Read())
{
e.EvaluationStatus = statusReader.GetBoolean(0);
}
else
{
e.EvaluationStatus = false;
}
}
classes.Add(e);
}
return(classes);
}
}
public List <Examination> GetExaminationListByTeacher(int id, int len)
{
using (var connection = SqlConnectionManager.GetConnection())
using (var command = new NpgsqlCommand())
{
var examinations = new List <Examination>();
command.Connection = connection;
command.CommandText = "select * from teacherExaminationList(@id, @len)";
command.Parameters.AddWithValue("@id", id);
command.Parameters.AddWithValue("@len", len);
var reader = command.ExecuteReader();
while (reader.Read())
{
var e = new Examination()
{
Id = int.Parse(reader["id"].ToString()),
Course = new Course()
{
Id = int.Parse(reader["c_id"].ToString()),
CourseName = reader["cname"].ToString(),
CourseCode = reader["ccode"].ToString()
},
ExaminationType = reader["etype"].ToString(),
DateTime = DateTime.Parse(reader["dt"].ToString()),
TotalMarks = int.Parse(reader["tmarks"].ToString()),
ReferenceAnswerSheet = reader["rans"].ToString(),
EvaluationStatus = false
};
using (var statusConnection = SqlConnectionManager.GetConnection())
using (var statusCommand = new NpgsqlCommand()) {
statusCommand.Connection = statusConnection;
statusCommand.CommandText = "select * from getExamEvaluationStatus(@tid, @eid)";
statusCommand.Parameters.AddWithValue("@tid", id);
statusCommand.Parameters.AddWithValue("@eid", e.Id);
var statusReader = statusCommand.ExecuteReader();
if (statusReader.Read())
{
e.EvaluationStatus = statusReader.GetBoolean(0);
}
}
examinations.Add(e);
}
return(examinations);
}
}
public void ExecuteNonQuery(string commandText)
{
if (string.IsNullOrEmpty(commandText))
{
return;
}
Log.LogInformation("SqlDataProvider", commandText);
var conn = SqlConnectionManager.GetConnection(_connectionString);
using (var cmd = new SqlCommand(commandText, conn))
{
cmd.ExecuteNonQuery();
}
}
protected void btnTimeout_Click(object sender, EventArgs e)
{
var connection = SqlConnectionManager.GetConnection();
string eid = ((string)Session["id"]);
string outtime = DateTime.Now.ToString("HH:mm");
string adate = DateTime.Today.ToString("MM/dd/yyyy");
string query = "SELECT * FROM [dbo].[tblAttendance] WHERE EId='" + eid + "' AND Adate='" + adate + "'";
var cmd = SqlConnectionManager.GetCommand(query, connection);
cmd.Connection.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
string it = "10:00";
string ot = "19:30";
while (reader.Read())
{
it = reader["Intime"].ToString();
ot = reader["Outtime"].ToString();
}
if (it != "10:00")
{
if (ot == "19:30")
{
lblError.Visible = false;
string query2 = "UPDATE tblAttendance SET Outtime='" + outtime + "' WHERE EId='" + eid + "' AND Adate='" + adate + "'";
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.UpdateCommand = new SqlCommand(query2, connection);
adapter.UpdateCommand.ExecuteNonQuery();
Response.Write("<script>alert('Out-Time Registered Successfully.');</script>");
}
else
{
lblError.Visible = true;
lblError.Text = "Out-Time already registered!";
}
}
else
{
lblError.Visible = true;
lblError.Text = "In-Time not registered, register In-Time first!";
}
}
}
}
public List <string> GetDefaultComplaintTitles(int id)
{
using (var connection = SqlConnectionManager.GetConnection())
using (var command = new NpgsqlCommand()) {
command.Connection = connection;
command.CommandText = "select * from getDefaultComplaintTitles(@id)";
command.Parameters.AddWithValue("@id", id);
var reader = command.ExecuteReader();
var titles = new List <string>();
while (reader.Read())
{
titles.Add(reader["complaint_title"].ToString());
}
return(titles);
}
}