public async Task<IHttpActionResult> PostToAddGalleryWithPicture() { if (!Request.Content.IsMimeMultipartContent()) { return new System.Web.Http.Results.StatusCodeResult(HttpStatusCode.UnsupportedMediaType, Request); } string savepath = WebApiApplication.SERVERPATH + "myImages/gallery"; var result = new MultipartFormDataStreamProvider(savepath); Gallery_detail data = new Gallery_detail(); try { await Request.Content.ReadAsMultipartAsync(result); //READ JSON DATA PART JObject datareceive = JObject.Parse(result.FormData.GetValues(result.FormData.AllKeys[0])[0]); data.curri_id = datareceive["curri_id"].ToString(); data.name = datareceive["name"].ToString(); data.date_created = DateTime.Now.GetDateTimeFormats(new System.Globalization.CultureInfo("en-US"))[5]; data.aca_year = Convert.ToInt32(datareceive["aca_year"]); data.personnel_id = Convert.ToInt32(datareceive["personnel_id"]); JArray tlist = (JArray)datareceive["pictures"]; int fileind = 0; foreach (JObject item in tlist) { Picture p = new Picture { caption = item["caption"].ToString() }; MultipartFileData file = result.FileData[fileind++]; FileInfo fileInfo = new FileInfo(file.LocalFileName); string newfilename = string.Format("{0}.{1}", fileInfo.Name.Substring(9), file.Headers.ContentDisposition.FileName.Split('.').LastOrDefault().Split('\"').FirstOrDefault()); p.file_name = "myImages/gallery/" + newfilename; File.Move(string.Format("{0}/{1}", savepath, fileInfo.Name), string.Format("{0}/{1}", savepath, newfilename)); data.pictures.Add(p); } object resultfromdb = await datacontext.InsertNewGalleryWithSelect(data); if (resultfromdb.GetType().ToString() != "System.String") return Ok(resultfromdb); else return InternalServerError(new Exception(resultfromdb.ToString())); } catch (System.Exception e) { return InternalServerError(e); } }
public async Task<object> UpdateGalleryWithSelect(Gallery_detail gdata) { DBConnector d = new DBConnector(); if (!d.SQLConnect()) return WebApiApplication.CONNECTDBERRSTRING; List<Gallery_detail> result = new List<Gallery_detail>(); Gallery_detail dummyfordeletepictures = new Gallery_detail(); string temp1tablename = "#temp1"; string updatepicturecmd = ""; string ifexistscond = string.Format("if exists (select * from {0} where {1} = {2}) ", FieldName.TABLE_NAME, FieldName.GALLERY_ID, gdata.gallery_id); string insertintopicturecmd = string.Format("insert into {0} values ", Picture.FieldName.TABLE_NAME); string deletefrompicturecmd = string.Format("delete from {0} output deleted.{1} where {2} = {3} ", Picture.FieldName.TABLE_NAME, Picture.FieldName.FILE_NAME, Picture.FieldName.GALLERY_ID, gdata.gallery_id); string excludecond = "1=1 "; int len = insertintopicturecmd.Length; string createtabletemp1 = string.Format("create table {0} (" + "[row_num] INT IDENTITY(1, 1) NOT NULL," + "[{1}] {2} NULL," + "PRIMARY KEY ([row_num])) " + "ALTER TABLE {0} " + "ALTER COLUMN {1} {2} COLLATE DATABASE_DEFAULT " , temp1tablename, Picture.FieldName.FILE_NAME, DBFieldDataType.FILE_NAME_TYPE); string updategallerycmd = string.Format("update {0} set {1} = '{2}', {3} = '{4}' where {5} = {6} ", FieldName.TABLE_NAME, FieldName.PERSONNEL_ID, gdata.personnel_id, FieldName.NAME, gdata.name, FieldName.GALLERY_ID, gdata.gallery_id); foreach (Picture p in gdata.pictures) { if (p.gallery_id != 0) { updatepicturecmd += string.Format("update {0} set {1} = '{2}' where {3} = {4} and {5} = '{6}' ", Picture.FieldName.TABLE_NAME, Picture.FieldName.CAPTION, p.caption, Picture.FieldName.GALLERY_ID, gdata.gallery_id, Picture.FieldName.FILE_NAME, p.file_name); //Gen delete cond excludecond += string.Format("and {0} != '{1}' ", Picture.FieldName.FILE_NAME, p.file_name); } else { if (insertintopicturecmd.Length <= len) insertintopicturecmd += string.Format("({0},'{1}','{2}')", gdata.gallery_id, p.file_name, p.caption); else insertintopicturecmd += string.Format(",({0},'{1}','{2}')", gdata.gallery_id, p.file_name, p.caption); } } if (insertintopicturecmd.Length <= len) insertintopicturecmd = ""; deletefrompicturecmd += string.Format("and ({0}) ", excludecond); string insertintotemp1 = string.Format("INSERT INTO {0} " + "select * from " + "({1}) " + "as outputdelete ", temp1tablename, deletefrompicturecmd); curri_id = gdata.curri_id; aca_year = gdata.aca_year; string selectcmd = getSelectByCurriculumAcademicCommand(true); d.iCommand.CommandText = string.Format("{0} BEGIN {1} {2} {3} {4} {5} {6} END",ifexistscond, createtabletemp1,updategallerycmd,updatepicturecmd, insertintotemp1,insertintopicturecmd,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) { gallery_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.GALLERY_ID].Ordinal]); if (gallery_id != 0) { //If date is not null (means result is row with picture data) if (result.FirstOrDefault(g => g.gallery_id == gallery_id) == null) { string real_t_prename; if (item.ItemArray[data.Columns[User_type.FieldName.USER_TYPE_NAME].Ordinal].ToString() == "อาจารย์") real_t_prename = NameManager.GatherPreName(item.ItemArray[data.Columns[Teacher.FieldName.T_PRENAME].Ordinal].ToString()); else real_t_prename = item.ItemArray[data.Columns[Teacher.FieldName.T_PRENAME].Ordinal].ToString(); result.Add(new Gallery_detail { aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]), personnel_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.PERSONNEL_ID].Ordinal]), t_name = real_t_prename + item.ItemArray[data.Columns[Teacher.FieldName.T_NAME].Ordinal].ToString(), curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(), gallery_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.GALLERY_ID].Ordinal]), date_created = Convert.ToDateTime(item.ItemArray[data.Columns[FieldName.DATE_CREATED].Ordinal].ToString(), System.Globalization.CultureInfo.CurrentCulture).GetDateTimeFormats()[3], name = item.ItemArray[data.Columns[FieldName.NAME].Ordinal].ToString(), pictures = new List<Picture>() }); } result.First(g => g.gallery_id == gallery_id).pictures.Add(new Picture { gallery_id = Convert.ToInt32(item.ItemArray[data.Columns[Picture.FieldName.GALLERY_ID].Ordinal]), file_name = item.ItemArray[data.Columns[Picture.FieldName.FILE_NAME].Ordinal].ToString(), caption = item.ItemArray[data.Columns[Picture.FieldName.CAPTION].Ordinal].ToString() }); } else { dummyfordeletepictures.pictures.Add(new Picture { file_name = item.ItemArray[data.Columns[Picture.FieldName.FILE_NAME].Ordinal].ToString() }); } } data.Dispose(); } else { //Reserved for return error string res.Close(); return "ไม่พบข้อมูลอัลบั้มรูปภาพที่ต้องการแก้ไขในระบบ"; } 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(); } result.Add(dummyfordeletepictures); return result; }
public async Task<object> InsertNewGalleryWithSelect(Gallery_detail gdata) { DBConnector d = new DBConnector(); if (!d.SQLConnect()) return WebApiApplication.CONNECTDBERRSTRING; List<Gallery_detail> result = new List<Gallery_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.GALLERY_ID); string createtabletemp2 = string.Format("create table {0} (" + "[row_num] INT IDENTITY(1, 1) NOT NULL," + "[{1}] {3} NULL," + "[{2}] VARCHAR(MAX) NULL," + "PRIMARY KEY ([row_num])) " + "ALTER TABLE {0} " + "ALTER COLUMN {1} {3} COLLATE DATABASE_DEFAULT " + "ALTER TABLE {0} " + "ALTER COLUMN {2} VARCHAR(MAX) COLLATE DATABASE_DEFAULT " , temp2tablename, Picture.FieldName.FILE_NAME,Picture.FieldName.CAPTION, DBFieldDataType.FILE_NAME_TYPE); string insertintotemp1 = string.Format("INSERT INTO {0} " + "select * from " + "(insert into {1}({2},{3},{4},{5},{6}) output inserted.{7} values " + "('{8}','{9}','{10}','{11}',{12})) " + "as outputinsert ", temp1tablename, FieldName.TABLE_NAME, FieldName.PERSONNEL_ID, FieldName.NAME, FieldName.DATE_CREATED, FieldName.CURRI_ID, FieldName.ACA_YEAR, FieldName.GALLERY_ID, gdata.personnel_id, gdata.name, gdata.date_created, gdata.curri_id, gdata.aca_year); string insertintotemp2 = string.Format("INSERT INTO {0} VALUES (null,null)", temp2tablename); foreach (Picture p in gdata.pictures) { insertintotemp2 += string.Format(",('{0}','{1}')", p.file_name,p.caption); } string insertintopicture = string.Format(" INSERT INTO {0} " + "select {1},{2},{3} from {4},{5} where {2} is not null ", Picture.FieldName.TABLE_NAME, FieldName.GALLERY_ID, Picture.FieldName.FILE_NAME, Picture.FieldName.CAPTION, temp1tablename, temp2tablename); curri_id = gdata.curri_id; aca_year = gdata.aca_year; string selectcmd = getSelectByCurriculumAcademicCommand(false); d.iCommand.CommandText = string.Format("BEGIN {0} {1} {2} {3} {4} {5} END", createtabletemp1, createtabletemp2, insertintotemp1, insertintotemp2, insertintopicture, 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) { gallery_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.GALLERY_ID].Ordinal]); if (result.FirstOrDefault(g => g.gallery_id == gallery_id) == null) { string real_t_prename; if (item.ItemArray[data.Columns[User_type.FieldName.USER_TYPE_NAME].Ordinal].ToString() == "อาจารย์") real_t_prename = NameManager.GatherPreName(item.ItemArray[data.Columns[Teacher.FieldName.T_PRENAME].Ordinal].ToString()); else real_t_prename = item.ItemArray[data.Columns[Teacher.FieldName.T_PRENAME].Ordinal].ToString(); result.Add(new Gallery_detail { aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]), personnel_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.PERSONNEL_ID].Ordinal]), t_name = real_t_prename + item.ItemArray[data.Columns[Teacher.FieldName.T_NAME].Ordinal].ToString(), curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(), gallery_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.GALLERY_ID].Ordinal]), date_created = Convert.ToDateTime(item.ItemArray[data.Columns[FieldName.DATE_CREATED].Ordinal].ToString(), System.Globalization.CultureInfo.CurrentCulture).GetDateTimeFormats()[3], name = item.ItemArray[data.Columns[FieldName.NAME].Ordinal].ToString(), pictures = new List<Picture>() }); } result.First(g => g.gallery_id == gallery_id).pictures.Add(new Picture { gallery_id = Convert.ToInt32(item.ItemArray[data.Columns[Picture.FieldName.GALLERY_ID].Ordinal]), file_name = item.ItemArray[data.Columns[Picture.FieldName.FILE_NAME].Ordinal].ToString(), caption = item.ItemArray[data.Columns[Picture.FieldName.CAPTION].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; }