public static string GetAppointmentDataTable()
{
string strResponse = "";
DataTable dtAppointment = new DataTable();
dtAppointment.Columns.Add("DT_RowId");
dtAppointment.Columns.Add("APPOINTMENT_ID");
dtAppointment.Columns.Add("PATIENT_ID");
dtAppointment.Columns.Add("PATIENT_NAME");
dtAppointment.Columns.Add("DOCTOR_ID");
dtAppointment.Columns.Add("DOCTOR_NAME");
dtAppointment.Columns.Add("APPOINTMENT_DATE");
dtAppointment.Columns.Add("APPOINTMENT_TIME");
dtAppointment.Columns.Add("PROCEDURE_ID");
string query = "select * from appointment_view";
try
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["dbConnect"].ConnectionString);
con.Open();
SqlDataAdapter adap = new SqlDataAdapter(query, con);
DataTable dt = new DataTable();
adap.Fill(dt);
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dtAppointment.NewRow();
dr["DT_RowId"] = "row_" + Convert.ToInt64(dt.Rows[i]["APPOINTMENT_ID"].ToString());
dr["APPOINTMENT_ID"] = Convert.ToInt64(dt.Rows[i]["APPOINTMENT_ID"].ToString());
dr["PATIENT_ID"] = Convert.ToInt64(dt.Rows[i]["PATIENT_ID"].ToString());
dr["PATIENT_NAME"] = dt.Rows[i]["PATIENT_NAME"].ToString().ToUpper();
dr["DOCTOR_ID"] = Convert.ToInt64(dt.Rows[i]["DOCTOR_ID"].ToString());
dr["DOCTOR_NAME"] = dt.Rows[i]["DOCTOR_NAME"].ToString().ToUpper();
string appointmentDate = String.Format("{0:dd-MM-yyyy}", Convert.ToDateTime(dt.Rows[i]["APPOINTMENT_DATE"].ToString()));
dr["APPOINTMENT_DATE"] = appointmentDate;
dr["APPOINTMENT_TIME"] = dt.Rows[i]["APPOINTMENT_TIME"].ToString().ToUpper();
dr["PROCEDURE_ID"] = getProcedureID(Convert.ToInt64(dt.Rows[i]["APPOINTMENT_ID"].ToString()));
dtAppointment.Rows.Add(dr);
}
JavaScriptSerializer serializer = new JavaScriptSerializer();
List <Dictionary <string, object> > rows = new List <Dictionary <string, object> >();
Dictionary <string, object> row;
foreach (DataRow dr in dtAppointment.Rows)
{
row = new Dictionary <string, object>();
foreach (DataColumn col in dtAppointment.Columns)
{
row.Add(col.ColumnName, dr[col]);
}
rows.Add(row);
}
adap.Dispose();
con.Dispose();
strResponse = serializer.Serialize(rows);
}
catch (SqlException ex)
{
strResponse = ex.Message;
}
return(strResponse);
}
public void Dispose()
{
con.Dispose();
}