Пример #1
0
        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));
            }
        }
Пример #2
0
        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);
        }
Пример #3
0
        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);
        }