public async Task<IHttpActionResult> PutForAddQuestionare(Questionare_set_detail_full data)
{
data.date = DateTime.Now.GetDateTimeFormats(new System.Globalization.CultureInfo("en-US"))[5];
object result = await datacontext.InsertNewQuestionareWithSelect(data);
if (result.GetType().ToString() != "System.String")
return Ok(result);
else
return InternalServerError(new Exception(result.ToString()));
}
public async Task<object> InsertNewQuestionareWithSelect(Questionare_set_detail_full qdata)
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
return WebApiApplication.CONNECTDBERRSTRING;
List<Questionare_set_detail> result = new List<Questionare_set_detail>();
string temp1tablename = "#temp1";
string temp2tablename = "#temp2";
string temp3tablename = "#temp3";
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.QUESTIONARE_SET_ID);
string createtabletemp2 = string.Format("create table {0} (" +
"[row_num] INT IDENTITY(1, 1) NOT NULL," +
"[{1}] VARCHAR(2000) NOT NULL," +
"PRIMARY KEY ([row_num])) " +
"ALTER TABLE {0} " +
"ALTER COLUMN {1} VARCHAR(2000) COLLATE DATABASE_DEFAULT "
, temp2tablename, Questionare_question_obj.FieldName.DETAIL);
string createtabletemp3 = string.Format("create table {0} (" +
"[row_num] INT IDENTITY(1, 1) NOT NULL," +
"[{1}] INT NULL," +
"PRIMARY KEY ([row_num])) "
, temp3tablename, Questionare_privilege.FieldName.PRIVILEGE_TYPE_ID);
string insertintotemp1 = string.Format("INSERT INTO {0} " +
"select * from " +
"(insert into {1} output inserted.{2} values " +
"('{3}','{4}',{5},'{6}','{7}')) " +
"as outputinsert ",
temp1tablename, FieldName.TABLE_NAME, FieldName.QUESTIONARE_SET_ID,
qdata.personnel_id, qdata.curri_id,qdata.aca_year, qdata.name,qdata.date);
string insertintotemp2 = string.Format("INSERT INTO {0} VALUES (null)", temp2tablename);
foreach (Questionare_question_obj q in qdata.my_questions)
{
insertintotemp2 += string.Format(",('{0}')", q.detail);
}
string insertintotemp3 = string.Format("INSERT INTO {0} VALUES (null)", temp3tablename);
foreach (User_type u in qdata.my_target)
{
insertintotemp3 += string.Format(",('{0}')", u.user_type_id);
}
string insertintoquestionareprivilege = string.Format(" INSERT INTO {0} " +
"select {1},{2} from {3},{4} where {2} is not null ",
Questionare_privilege.FieldName.TABLE_NAME, FieldName.QUESTIONARE_SET_ID, Questionare_privilege.FieldName.PRIVILEGE_TYPE_ID,
temp1tablename, temp3tablename);
string insertintoquestionarequestionobj = string.Format(" INSERT INTO {0} " +
"select {1},{2} from {3},{4} where {2} is not null ",
Questionare_question_obj.FieldName.TABLE_NAME, FieldName.QUESTIONARE_SET_ID, Questionare_question_obj.FieldName.DETAIL,
temp1tablename, temp2tablename);
curri_id = qdata.curri_id;
aca_year = qdata.aca_year;
string selectcmd = getSelectByCurriculumAcademicCommand();
d.iCommand.CommandText = string.Format("BEGIN {0} {1} {2} {3} {4} {5} {6} {7} {8} END", createtabletemp1, createtabletemp2,createtabletemp3,
insertintotemp1, insertintotemp2, insertintotemp3, insertintoquestionareprivilege,
insertintoquestionarequestionobj, selectcmd);
try
{
System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync();
if (res.HasRows)
{
DataTable data = new DataTable();
data.Load(res);
foreach (DataRow item in data.Rows)
{
int qid = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.QUESTIONARE_SET_ID].Ordinal]);
if (result.FirstOrDefault(t => t.questionare_set_id == qid) == null)
{
result.Add(new Questionare_set_detail
{
aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]),
name = item.ItemArray[data.Columns[FieldName.NAME].Ordinal].ToString(),
personnel_id = item.ItemArray[data.Columns[FieldName.PERSONNEL_ID].Ordinal].ToString() != "" ? Convert.ToInt32(item.ItemArray[data.Columns[FieldName.PERSONNEL_ID].Ordinal]) : 0,
questionare_set_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.QUESTIONARE_SET_ID].Ordinal]),
date = Convert.ToDateTime(item.ItemArray[data.Columns[FieldName.DATE].Ordinal].ToString(), System.Globalization.CultureInfo.CurrentCulture).GetDateTimeFormats()[3],
curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(),
t_name = item.ItemArray[data.Columns[User_list.FieldName.T_PRENAME].Ordinal].ToString() + item.ItemArray[data.Columns[Teacher.FieldName.T_NAME].Ordinal].ToString()
});
}
result.First(t => t.questionare_set_id == qid).target.Add(new User_type
{
user_type_id = Convert.ToInt32(item.ItemArray[data.Columns[Questionare_privilege.FieldName.PRIVILEGE_TYPE_ID].Ordinal]),
user_type = item.ItemArray[data.Columns[User_type.FieldName.USER_TYPE_NAME].Ordinal].ToString()
});
}
data.Dispose();
}
else
{
//Reserved for return error string
}
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;
}