public async Task<IHttpActionResult> Put(JObject data)
{
Lab_list_detail detail = new Lab_list_detail
{
aca_year = Convert.ToInt32(data["aca_year"]),
curri_id = data["curri_id"].ToString(),
name = data["name"].ToString(),
room = data["room"].ToString(),
lab_num = Convert.ToInt32(data["lab_num"]),
};
JArray officer_data = (JArray)data["officer"];
foreach (JObject item in officer_data)
{
detail.officer.Add(new Personnel_with_t_name
{
user_id = Convert.ToInt32(item["user_id"])
});
}
object result = await datacontext.UpdateLabListWithSelect(detail);
if (result.GetType().ToString() != "System.String")
return Ok(result);
else
return BadRequest(result.ToString());
}
public async Task<object> InsertNewLabListWithSelect(Lab_list_detail ldata)
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
return WebApiApplication.CONNECTDBERRSTRING;
List<Lab_list_detail> result = new List<Lab_list_detail>();
string temp1tablename = "#temp1";
string temp2tablename = "#temp2";
string createtabletemp1 = string.Format("create table {0} (" +
"[row_num] INT IDENTITY(1, 1) NOT NULL," +
"[{1}] INT NOT NULL," +
"PRIMARY KEY ([row_num])) ", temp1tablename, FieldName.LAB_NUM);
string createtabletemp2 = string.Format("create table {0} (" +
"[row_num] INT IDENTITY(1, 1) NOT NULL," +
"[{1}] INT NULL," +
"PRIMARY KEY ([row_num])) "
, temp2tablename, Lab_officer.FieldName.OFFICER);
string insertintotemp1 = string.Format("INSERT INTO {0} " +
"select * from " +
"(insert into {1} output inserted.{2} values " +
"('{3}','{4}','{5}',{6})) as outputinsert ",
temp1tablename, FieldName.TABLE_NAME, FieldName.LAB_NUM,
ldata.name, ldata.room, ldata.curri_id, ldata.aca_year);
string insertintotemp2 = string.Format("INSERT INTO {0} VALUES (null)", temp2tablename);
foreach (Personnel_with_t_name p in ldata.officer)
{
insertintotemp2 += string.Format(",({0})", p.user_id);
}
string insertintolabofficer = string.Format(" INSERT INTO {0} " +
"select {1},{2} from {3},{4} where {2} is not null ",
Lab_officer.FieldName.TABLE_NAME, FieldName.LAB_NUM, Lab_officer.FieldName.OFFICER,
temp1tablename, temp2tablename);
curri_id = ldata.curri_id;
aca_year = ldata.aca_year;
string selectcmd = getSelectByCurriculumAcademicCommand();
d.iCommand.CommandText = string.Format("BEGIN {0} {1} {2} {3} {4} {5} END", createtabletemp1, createtabletemp2,
insertintotemp1, insertintotemp2, insertintolabofficer, selectcmd);
try
{
System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync();
do
{
if (res.HasRows)
{
DataTable data = new DataTable();
data.Load(res);
foreach (DataRow item in data.Rows)
{
if (data.Columns.Contains(FieldName.ROOM))
{
result.Add(new Lab_list_detail
{
lab_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.LAB_NUM].Ordinal]),
aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]),
curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(),
name = item.ItemArray[data.Columns[FieldName.NAME].Ordinal].ToString(),
room = item.ItemArray[data.Columns[FieldName.ROOM].Ordinal].ToString()
});
}
else
{
int labid = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.LAB_NUM].Ordinal]);
result.First(t => t.lab_num == labid).officer.Add(new Personnel_with_t_name
{
user_id = Convert.ToInt32(item.ItemArray[data.Columns[Lab_officer.FieldName.OFFICER].Ordinal]),
t_name = item.ItemArray[data.Columns[User_list.FieldName.T_PRENAME].Ordinal].ToString() + item.ItemArray[data.Columns[User_list.FieldName.T_NAME].Ordinal].ToString()
});
}
}
data.Dispose();
}
else if (!res.IsClosed)
{
if (!res.NextResult())
break;
}
} while (!res.IsClosed);
res.Close();
}
catch (Exception ex)
{
//Handle error from sql execution
return ex.Message;
}
finally
{
//Whether it success or not it must close connection in order to end block
d.SQLDisconnect();
}
return result;
}
public async Task<object> UpdateLabListWithSelect(Lab_list_detail ldata)
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
return WebApiApplication.CONNECTDBERRSTRING;
List<Lab_list_detail> result = new List<Lab_list_detail>();
string updatelablistcmd = string.Format("update {0} set {1} = '{2}', {3} = '{4}' " +
"where {5} = {6} ", FieldName.TABLE_NAME, FieldName.NAME, ldata.name,
FieldName.ROOM, ldata.room,FieldName.LAB_NUM,ldata.lab_num);
string deletefromlabofficer = string.Format("DELETE FROM {0} where {1} = {2} ",
Lab_officer.FieldName.TABLE_NAME, Lab_officer.FieldName.LAB_NUM, ldata.lab_num);
string insertintolabofficer = string.Format("INSERT INTO {0} values ",
Lab_officer.FieldName.TABLE_NAME);
foreach (Personnel_with_t_name p in ldata.officer)
{
insertintolabofficer += string.Format("({0},{1})", ldata.lab_num, p.user_id);
if (p != ldata.officer.Last())
insertintolabofficer += ",";
}
curri_id = ldata.curri_id;
aca_year = ldata.aca_year;
string selectcmd = getSelectByCurriculumAcademicCommand();
string updatecondition = string.Format("if exists (select * from {0} where {1} = {2}) ", FieldName.TABLE_NAME, FieldName.LAB_NUM, ldata.lab_num);
d.iCommand.CommandText = string.Format("{4} BEGIN {0} {1} {2} {3} END", updatelablistcmd,
deletefromlabofficer, insertintolabofficer, selectcmd,updatecondition);
try
{
System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync();
do
{
if (res.HasRows)
{
DataTable data = new DataTable();
data.Load(res);
foreach (DataRow item in data.Rows)
{
if (data.Columns.Contains(FieldName.ROOM))
{
result.Add(new Lab_list_detail
{
lab_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.LAB_NUM].Ordinal]),
aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]),
curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(),
name = item.ItemArray[data.Columns[FieldName.NAME].Ordinal].ToString(),
room = item.ItemArray[data.Columns[FieldName.ROOM].Ordinal].ToString()
});
}
else
{
int labid = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.LAB_NUM].Ordinal]);
result.First(t => t.lab_num == labid).officer.Add(new Personnel_with_t_name
{
user_id = Convert.ToInt32(item.ItemArray[data.Columns[Lab_officer.FieldName.OFFICER].Ordinal]),
t_name = item.ItemArray[data.Columns[User_list.FieldName.T_PRENAME].Ordinal].ToString() + item.ItemArray[data.Columns[User_list.FieldName.T_NAME].Ordinal].ToString()
});
}
}
data.Dispose();
}
else if (!res.IsClosed)
{
if (!res.NextResult())
break;
}
} while (!res.IsClosed);
res.Close();
}
catch (Exception ex)
{
//Handle error from sql execution
return ex.Message;
}
finally
{
//Whether it success or not it must close connection in order to end block
d.SQLDisconnect();
}
return result;
}
