2019/0612/WebSockets Notification + MVC Part2 PhotoSharing PartialView & ADO.NET & EntitySQL & VMd

VMD = ViewModel

#補充 WebSockets 推送訊息 Notification js 物件

接續 WebSockets

Notification js物件 前端 提示框

broadcast

沒有拜訪過網站，而如果網站有推撥處理要 允許權限

Ie沒有支援notification

時間戳章

Split 靠 : 切開 變成兩個集合

Tag 辨識碼

沒有辦法用在手機上

手機的推撥是基於os作業系統Operating System而不是瀏覽器

Angularwww.google.com

AngularJS是一款由Google維護的開源JavaScript函式庫

Enable JavaScript to use searchwww.google.com

### ftp://mvc@10.10.3.189/WebSocket2/WebSocket2/App_Start/WebApiConfig.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web.Http;

namespace WebSocket2
{
    public static class WebApiConfig
    {
        public static void Register(HttpConfiguration config)
        {
            // Web API 設定和服務

            // Web API 路由
            config.MapHttpAttributeRoutes();

            config.Routes.MapHttpRoute(
                name: "DefaultApi",
                routeTemplate: "api/{controller}/{id}",
                defaults: new { id = RouteParameter.Optional }
            );
        }
    }
}

###ftp://mvc@10.10.3.189/WebSocket2/WebSocket2/Controllers/NotificationController.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;
using System.Web;
using Microsoft.Web.WebSockets;


namespace WebSocket2.Controllers
{
    public class NotificationController : ApiController
    {
        public HttpResponseMessage Get()
        {
            HttpContext.Current.AcceptWebSocketRequest(new ChatWebSocketHandler());
            return Request.CreateResponse(HttpStatusCode.SwitchingProtocols);
        }

        class ChatWebSocketHandler : WebSocketHandler
        {
            
            private static WebSocketCollection _chatClients = new WebSocketCollection();
         

            public ChatWebSocketHandler()
            {

                //_message2 = subject;
            }

            public override void OnOpen()
            {
                _chatClients.Add(this);
            }

            public override void OnMessage(string message)
            {
                _chatClients.Broadcast(message);
            }
        }
    }
}

###ftp://mvc@10.10.3.189/WebSocket2/WebSocket2/Views/Home/Contact.cshtml

@{
    ViewBag.Title = "Contact";
}
<div class="jumbotron">
    <h1>訊息推送</h1>
    <p class="lead">ASP.NET is a free web framework for building great Web sites and Web applications using HTML, CSS and JavaScript.</p>
    <p><a href="https://asp.net" class="btn btn-primary btn-lg">Learn more &raquo;</a></p>
</div>

<div class="row">
    <div class="col-md-12">
        <fieldset>
            <legend>訊息推送</legend>
            主旨:<input id="subject" name="subject" type="text" /><br />
            內容:<input id="message" name="message" type="text" /><br />
            <input id="btnSend" type="button" value="推送" />


        </fieldset>

    </div>
</div>

@section scripts{
    <script>

        var url = "ws://10.10.3.189/api/Notification";
        var ws = new WebSocket(url);
        console.log(ws);

        ws.onopen = function () {
            //ws.send("已進入聊天!!");
            //ws.close();
            //alert('已連線');
        }

        ws.onerror = function () {
            alert('有錯誤');
        }

        ws.onmessage = function (e) {
            console.log(e.data);
            var res = e.data.split(":");
            console.log(res);
            var r = Math.random();
            var notify = new Notification(res[0],{
                body: res[1],
                icon: '/images/wda.gif',
                tag: r// 設定標籤
            });

            //notify.onclick = function () { // 綁定點擊事件
            //    //e.preventDefault(); // prevent the browser from focusing the Notification's tab
            //   // window.open('http://www.mcsdd.tw'); // 打開特定網頁
            //    alert('test');
            //}
            //console.log(notify);

        }
        $('#btnSend').click(function () {
            var msg = $('#subject').val() + ":" + $('#message').val();
            ws.send(msg);

        });


        //var notifyConfig = {
        //    body: '\\ ^o^ /', // 設定內容
        //    icon: '/images/favicon.ico' // 設定 icon
        //};

        if (Notification.permission === 'default' || Notification.permission === 'undefined') {
            Notification.requestPermission(function (permission) {
                if (permission === 'granted') { // 使用者同意授權
                    var notification = new Notification('Hi there!', notifyConfig); // 建立通知
                }
            });
        }

</script>
}

###ftp://mvc@10.10.3.189/WebSocket2/WebSocket2/Views/Home/PromptController

using System.Net;
using System.Net.Http;
using System.Web.Http;
using System.Web;
using Microsoft.Web.WebSockets;

namespace WebSocket2.Controllers
{
    public class PromptController : ApiController
    {
        public HttpResponseMessage Get(string username)
        {
            HttpContext.Current.AcceptWebSocketRequest(new ChatWebSocketHandler(username));
            return Request.CreateResponse(HttpStatusCode.SwitchingProtocols);
        }
        class ChatWebSocketHandler : WebSocketHandler
        {
            private string _username;
            private static WebSocketCollection _chatClients =
                new WebSocketCollection();

            public ChatWebSocketHandler(string username)
            {
                _username = username;
            }
            //public ChatWebSocketHandler()
            //{

            //}
            public override void OnOpen()
            {
                _chatClients.Add(this);
            }
            public override void OnMessage(string message)
            {
                _chatClients.Broadcast(message);
                //_chatClients.Broadcast(_username);
                //_chatClients.Broadcast(message);
            }
        }
    }
}

#05_PartialView 部分檢視

PartialView 部分檢視

通常用在大家都要用它

或是不同的model也可以用PartialView或viewModel

原本要解決多model時可以

Model => action => view

Viewmodel => action => view

Model => viewbag => view

PartialView用途很廣

##首先 在 controller 新增一個 action 來當作 PartialView

但命名規則 前面要加上 _ 只

要是很多狀況時會用到 就要套上 like layout 的命名規則

有_是被保護起來的狀態 無法單獨執行 like master 無法直接執行

照片放泛型 list 裡面

然後 tolist前 要先排序 orderby 依照descebding逆排 建立日期

Thenby 順排

Lambda 跟 linq 跟 sql 寫法不同

ADO.NET才能用SQL但還是有預存程序之類的在 SQLSERVER時用這個比較方便

##接著產生view

Create as a partial view 必須打勾

除了在controller新增檢視外

也可在 shared file 右鍵新增

沒用IEnumerable 的話就是直接帶model過來

但如果放在 IE 裡面 就能用 foreach 讀出來

只是顯示值用 displayfor

新增一個新的index 用於綁定 PartialView

##綁定的方式為

PartialView 是階層的

執行有幾張照片抓不到 filter ValueReporter擋掉了?註冊在整個網站上

必須要在PhotosController.cs _PhotoGallery()

上面[ValueReporter(IsCheck=false)]避免被執行

##反面列表 (之前抓LOG影響要 改成不驗證LOG

但不知道為啥 自己寫的 filter 不吃 false 所以改成自己寫成 條件式

public bool IsCheck { get; set; }

##PartialView利用價值

示範在 Home\Index.cshtml

在 PhotosController.cs

前端傳值 PartialView 只顯示前端傳值的筆數

排序後再做顯示做在後端

###Controllers/PhotosController

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using PhotoSharing.Models;
using PhotoSharing.DAL;
using System.Net;


namespace PhotoSharing.Controllers
{
  
    public class PhotosController : Controller
    {
        //2.2建立Data Context 來自 Photo Model 的 PhotoSharingContext 
        PhotoSharingContext context = new PhotoSharingContext();

        // GET: Photos
        //2.3-1 Index()回傳Photo
      
        public ActionResult Index()
        {
            ViewBag.Date = DateTime.Now;  //用ViewBag帶入今日日期
            return View(context.Photos.ToList());
        }

        //2.3-2 Display(int id) 透過id參數回傳Photo,若找不到回傳HttpNotFound()helper
       
        public ActionResult Display(int? id)
        {
            ViewData["Date"] = DateTime.Now; //用ViewData帶入今日日期
            if (id==null)
            {
                return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
            }

            Photo photo = context.Photos.Find(id);
            if(photo==null)
            {
                return HttpNotFound();
            }

            return View("Display", photo);
        }
        //2.3-7 建立GetImage(int id)回傳File(photo.PhotoFile, photo.ImageMimeType)
        //這不是View()Helper的ActionResult, 這是File()helper
        public FileContentResult GetImage(int id)
        {
            Photo photo = context.Photos.Find(id);
            return File(photo.PhotoFile, photo.ImageMimeType);
        }

        //2.3-3 GET:Create() 產生新增Photo作業,並回傳 new Photo()並產生CreatedDate屬性值= DateTime.Today
        public ActionResult Create()
        {
            Photo newPhoto = new Photo();
            newPhoto.CreatedDate = DateTime.Today;
            
            return View("Create",newPhoto);
        }
        //2.3-4 Post:Create(Photo photo, HttpPostedFileBase image),使用HTTP POST,執行新增Photo回存作業,回傳RedirectToAction()helper
        //      如果ModelState.IsValid==false,回傳Photo給View, 反之執行新增Photo回存作業
        //      photo.ImageMimeType = image.ContentType;
        //      photo.PhotoFile = new byte[image.ContentLength];
        //      image.InputStream.Read(photo.PhotoFile, 0, image.ContentLength);
        [HttpPost,ValidateAntiForgeryToken]
        public ActionResult Create(Photo photo,HttpPostedFileBase image)
        {
            photo.CreatedDate = DateTime.Today;
            if(ModelState.IsValid)
            {
                //有post照片才做照片上傳的處理
                if (image!=null)
                {
                    photo.ImageMimeType = image.ContentType;  //抓照片型態
                    photo.PhotoFile = new byte[image.ContentLength];  //取得上傳照片的大小再轉byte陣列
                    image.InputStream.Read(photo.PhotoFile, 0, image.ContentLength);
                }
                context.Photos.Add(photo);
                context.SaveChanges();
                return RedirectToAction("Index");

            }
            else
            {
                return View("Create", photo);
            }


            
        }
        //2.3-5 Get:Delete(int id)產生刪除Photo作業,並回傳Photo(),若找不到回傳HttpNotFound()helper
        public ActionResult Delete(int? id)
        {
            if (id == null)
            {
                return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
            }

            Photo photo = context.Photos.Find(id);
            if (photo == null)
            {
                return HttpNotFound();
            }

            return View("Delete", photo);
        }
        //2.3-6 Post:建立DeleteConfirmed(int id)使用[ActionName("Delete")]屬性,透過HTTP POST,執行刪除Photo回存作業.回傳RedirectToAction()helper
        [HttpPost,ValidateAntiForgeryToken]
        [ActionName("Delete")]
        public ActionResult DeleteConfirmed(int? id)
        {
            Photo photo = context.Photos.Find(id);
            context.Photos.Remove(photo);
            context.SaveChanges();
            return RedirectToAction("Index");
        }

        //3.13-3 在PhotoController中的_PhotoGallery Action上標註排除使用Filter
        [ValueReporter(IsCheck=false)]
        //3.6.在PhotoController.cs建立[ChildActionOnly] _PhotoGallery action,用於Partial View
        public ActionResult _PhotoGallery(int number=0)
        {
            List<Photo> photos;
            if (number == 0)
            {
                //Lambda
                photos = context.Photos.OrderByDescending(p => p.CreatedDate).ThenBy(p => p.PhotoID).ToList();

                //LINQ
                //photos = (from p in context.Photos
                //          orderby p.CreatedDate descending, p.PhotoID ascending
                //          select p).ToList();

                //SQL
                //Select * from photo
                //order by CreatedDate desc,p.PhotoID
            }
            else
            {
                photos = context.Photos.OrderByDescending(p => p.CreatedDate).ThenBy(p => p.PhotoID).Take(number).ToList();

                //LINQ
                //photos = (from p in context.Photos
                //          orderby p.CreatedDate descending, p.PhotoID ascending
                //          select p).Take(number).ToList();


                //SQL
                //Select top 2 * from photo
                //order by CreatedDate desc,p.PhotoID

            }

            return PartialView("_PhotoGallery", photos);


        }

    }
}


x

### Views/Shared/_PhotoGallery

@model IEnumerable<PhotoSharing.Models.Photo>

@foreach (var item in Model)
{
    <h2>『@item.Title』</h2>
    <img src="@Url.Action("GetImage","Photos",new { id=item.PhotoID})" style="width:80%" />
    <p>
        建立者:@Html.DisplayFor(model => item.UserName)
    </p>
    <p>
        建立日期:@Html.DisplayFor(model => item.CreatedDate)
    </p>
    @Html.ActionLink("顯示詳細資料", "Display", new { id = item.PhotoID },new { @class="btn btn-primary"})

    <hr />

}

### Views/Photos/Index.cshtml

@{
    ViewBag.Title = "討論區";
}
<div>
    <h2>討論區</h2>
    <hr />
    <p>@Html.ActionLink("新增留言", "Create")</p>

    @Html.Action("_PhotoGallery", "Photos")

</div>

### 老師步驟

//Partial View
//3.6.在PhotoController.cs建立[ChildActionOnly] _PhotoGallery action,用於Partial View

//3.7.建立_PhotoGallery Views
//    使用Scaffold template:Empty
//    Model Class:Photo (PhotoSharing.Models)
//    Data Context Class:PhotoSharingContext (PhotoSharing.Models)
//    "勾選" Create as a partial view核取方塊


//3.8 將_PhotoGallery.cshtml移至Shared目錄

//3.9 在_PhotoGallery.cshtml完成_PhotoGallery Partial Views
//    加上 @model IEnumerable<PhotoSharing.Models.Photo>
//    如此可使用@foreach (var item in Model){....}

//3.10 在Index View使用 _PhotoGallery Partial Views
//3.10-1  在PhotosController的Index Action==>將return View("Index", context.Photos.ToList())改成 return View()
//3.10-2  建立Index Views
//        使用Scaffold template:Empty(without model)
//        不要勾選Create as a partial view核取方塊
//        勾選Use a Layout Page核取方塊  

//3.11 完成Index View(Photo/Index),使用 _PhotoGallery Action不帶number參數
//     @Html.Action("_PhotoGallery", "Photos")

//3.12  修改View(Home/Index),使用 _PhotoGallery Action輸入number參數
//      @Html.Action("_PhotoGallery", "Photo", new { number = 2 }

//3.13 處理ValueReporter Filter會因為使用PartialView造成的重覆開啟資料連線問題
//3.13-1 在ValueReporter加上判斷是否要使用Filter的屬性IsCheck
//3.13-2 在OnActionExecuting及OnResultExecuted中加上判斷
//3.13-3 在PhotoController中的_PhotoGallery Action上標註排除使用Filter
//3.13-4 在App_Start\FilterConfig.cs中加入IsCheck=true

# 06_Entity SQL

都是基於ADO.net

現在都用EntityFramwork Model linq 方式給

但 Entity SQL 雖然是架構在 EntityFramwork 但

拉出來講根留言板進度無關

##首先先示範怎麼寫 ADO.NET

加了一個資料夾 DB_ADOnet -> Controllers

增加一個controller PhotoCommentsController.cs

ADO.NET 怎在 Controller寫? 只要把它當成 webform codebehind即可

首先先做ConnectionStrings

把兩個表join起來再回給 index

首先先寫 SQL

查出來是一個 inner join 後的 table

丟到 DataTable 物件

弄一個函數做 sqldataadapter 傳指令進去 傳查詢後的 table回來

接著新增檢視

創造出來的表 所以引入model的方式比較特別 不是原model的結構

要先using system.data

然後 @model system.data.datatable

因為沒有model所以title都要自己寫

Gridview 跟 datatable 一列就是一個 row

所以要用 datarow讀出來

@foreach(DataRow db in Model.)

引用方式用 @ db [“PhotoID”]即可

無任何留言 用 @ if 判別

到此完成

join正規化過後的資料表組合回來

Webform只要拖過來做連結即可

MVC 什麼都要自己刻

接著去做 Display 也是用 ADO.NET

Controller 查詢 然後return 查詢後的table

如果沒收到傳送值 顯示錯誤頁面

接著做回覆留言 其實就是 Create

HTTPPOST

Validate

簡單繫結

做一個 executeSql

做資料儲存

如果是預存程序

Cmd.CommanndText 預存程序名稱

是 void 必須要指定 command type

如果把 commandtype 寫在資料儲存 function 那就只能預存程序 所以可以寫在外面

接著做 Parameters.AddWithValue

有指定名稱 純量變數名稱

### DB_ADOnet/Controllers/PhotoCommentsController.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Net;

namespace PhotoSharing.DB_ADOnet.Controllers
{
    public class PhotoCommentsController : Controller
    {
        SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
        SqlCommand Cmd = new SqlCommand();

        private DataTable querySql(string sql)
        {
            SqlDataAdapter adp = new SqlDataAdapter(sql,Conn);
            DataSet ds = new DataSet();
            adp.Fill(ds);
            return ds.Tables[0];
        }

        private void executeSql(string sql)
        {
            Conn.Open();
            Cmd.Connection = Conn;
            Cmd.CommandText = sql;
            Cmd.ExecuteNonQuery();
            Conn.Close();
          
        }

        // GET: PhotoComments
        public ActionResult Index()
        {
            var sql = "select p.PhotoID, p.Title, p.Description, p.CreatedDate, c.Subject, c.Body, c.UserName " +
                "from photos as p inner join comments as c on p.PhotoID=c.PhotoID";

            DataTable dt = querySql(sql);

            return View(dt);
        }


        public ActionResult Display(int? id)
        {
            if(id==null)
            {
                return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
            }

            var sql = "select p.PhotoID, p.Title, p.Description, p.CreatedDate, c.Subject, c.Body, c.UserName " +
                "from photos as p inner join comments as c on p.PhotoID=c.PhotoID where p.Photoid="+id;

            DataTable dt = querySql(sql);

            return View(dt);
        }


        public ActionResult CreateComment(int? id)
        {
            if (id == null)
            {
                return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
            }
            ViewBag.PhotoID = id;

            return View();
        }

        [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult CreateComment(string username, string subject, string body, int photoid)
        {

            var sql = "Add_Comment";
            Cmd.CommandType = CommandType.StoredProcedure;
            Cmd.Parameters.AddWithValue("@username", username);
            Cmd.Parameters.AddWithValue("@subject", subject);
            Cmd.Parameters.AddWithValue("@body", body);
            Cmd.Parameters.AddWithValue("@photoid", photoid);

            executeSql(sql);

            return RedirectToAction("Index");
        }



    }
}

### Views/PhotoComments/Index.cshtml

@using System.Data

@model System.Data.DataTable
@{
    ViewBag.Title = "Index";
}

<h2>留言列表</h2>

@if (Model.Rows.Count == 0)
{
    <h2>無任何留言</h2>
}
else
{
<table class="table">
    <tr>
        <th>
            PhotoID
        </th>
        <th>
             Title
            </th>
        <th>
           Description
        </th>
        <th>
            CreatedDate
        </th>
        <th>
            Subject
        </th>
        <th>
            Body
        </th>
        <th>
            UserName
        </th>
    </tr>

    @foreach (DataRow dr in Model.Rows)
    {
    <tr>
        <td>
            @dr["PhotoID"]
        </td>
        <td>
            @dr["Title"]
        </td>
        <td>
            @dr["Description"]
        </td>
        <td>
            @dr["CreatedDate"]
        </td>
        <td>
            @dr["Subject"]
        </td>
        <td>
            @dr["Body"]
        </td>
        <td>
            @dr["UserName"]
        </td>
        <td>
            @Html.ActionLink("回覆留言", "CreateComment", new { id = dr["PhotoID"] }) |
            @Html.ActionLink("詳細資料", "Display", new { id = dr["PhotoID"] }) |
            
        </td>
    </tr>
    }

</table>
}

### Views/PhotoComments/Display.cshtml

@using System.Data

@model System.Data.DataTable
@{
    ViewBag.Title = "Display";
}

<h2>單筆留言</h2>

@if (Model.Rows.Count == 0)
{
    <h2>無任何留言</h2>
}
else
{
    <table class="table">
        <tr>
            <th>
                PhotoID
            </th>
            <th>
                Title
            </th>
            <th>
                Description
            </th>
            <th>
                CreatedDate
            </th>
            <th>
                Subject
            </th>
            <th>
                Body
            </th>
            <th>
                UserName
            </th>
        </tr>

        @foreach (DataRow dr in Model.Rows)
        {
            <tr>
                <td>
                    @dr["PhotoID"]
                </td>
                <td>
                    @dr["Title"]
                </td>
                <td>
                    @dr["Description"]
                </td>
                <td>
                    @dr["CreatedDate"]
                </td>
                <td>
                    @dr["Subject"]
                </td>
                <td>
                    @dr["Body"]
                </td>
                <td>
                    @dr["UserName"]
                </td>
                <td>
                    @Html.ActionLink("回覆留言", "CreateComment", new { id = dr["PhotoID"] })


                </td>
            </tr>
        }

    </table>



}
@Html.ActionLink("回留言列表", "Index")

#下午從 visual studio做一個預存程序 開始

新增查詢或是預存程序 來打 sql 碼

Create proc

參數@

###Add_Comment.sql

create proc Add_Comment
	@username nvarchar(100),
	@subject nvarchar(250),
	@body nvarchar(max),
	@photoid bigint
as
begin

	insert into Comments(username, [Subject], body, PhotoID)
	values(@username,@subject,@body,@photoid)
end

做好 Add_Comment

接著到 CreateComment.cshtml

寫回覆留言的View

要處理 ADO.NET 新增 並儲存到 資料庫的 操作 從前到後

到此 ADO.NET CRU 做完

###Views/PhotoComments/CreateComment.cshtml

@{
    ViewBag.Title = "CreateComment";
}

<h2>回覆留言</h2>
@using (Html.BeginForm())
{
    @Html.AntiForgeryToken();
    <p>
        <label for="subject">回覆主題:</label>
        <input id="subject" name="subject" type="text" />
    </p>

    <p>
        <label for="body">回覆內容:</label>
        <textarea id="body" name="body" rows="5" cols="30"></textarea>

    </p>
    <p>
        <label for="username">回覆人名稱:</label>
        <input id="username" name="username" type="text" />
    </p>

    <input id="photoid" name="photoid" type="hidden" value="@ViewBag.PhotoID" />

    <p>
        <input id="Submit1" type="submit" value="確定送出" class="btn btn-default" />
    </p>


}

接著講

##EntitySQL

File -> DB_EntitySQL -> Models 建立獨立的Model

Webconfig 就會多出 ConnectionString 連線資訊

<connectionStrings>
    <add name="ConnectionString" connectionString="Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\PhotoSharing.mdf;Integrated Security=True" providerName="System.Data.SqlClient" />
  <add name="PhotoSharingEntities" connectionString="metadata=res://*/DB_EntitySQL.Models.PhotoComments.csdl|res://*/DB_EntitySQL.Models.PhotoComments.ssdl|res://*/DB_EntitySQL.Models.PhotoComments.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=(LocalDB)\MSSQLLocalDB;attachdbfilename=|DataDirectory|\PhotoSharing.mdf;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" /></connectionStrings>
  <appSettings>

接著再建立一個 controllers 資料夾

並建立 EntityPhotoCommentsController.cs

並編輯

首先 using PhotoSharing.DB_EntitySQL.Models / System.Net

以前都用用 EntitiyFromwork 先新增一個 db物件

以前Model 只能一個 資料表對一個，

現在要試 join 表

要先using System.Data.Entity.Core.EntityClient

要改用 EntityConnection name 要對應到 webconfig ConnectionString

Createcommand() from不可以直接寫資料表名稱 必須連結構也寫出來補上去

連線不會自己開關 也是要像ADO.NET 一樣

因為沒有 DATASET ? 所以只能一筆筆慢慢讀

而且同個陣列必須要同一個型態所以都轉成 string

可以丟在 LIST 泛型內 也可以丟在 arraylist 擺放集合的物件

不能直接return 過去

要丟在 view bag 裡面過去

接著新增檢視index 不用帶 model

那不帶model 要怎辦呢 ?

把ViewBag 丟到 var 裡面

就可以拿來當 model 用

底下用原本的方式編輯即可

### ###DB_EntitySQL/Controllers/EntityPhotoCommentsController.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using PhotoSharing.DB_EntitySQL.Models;
using System.Net;
using System.Data.Entity.Core.EntityClient;
using System.Collections;
using System.Data;

namespace PhotoSharing.DB_EntitySQL.Controllers
{
    public class EntityPhotoCommentsController : Controller
    {
        /*PhotoSharingEntities db = new PhotoSharingEntities();*/  //建立DB Context
        EntityConnection Conn = new EntityConnection("name=PhotoSharingEntities");

        // GET: EntityPhotoComments
        public ActionResult Index()
        {
            var Cmd = Conn.CreateCommand();
            Cmd.CommandText = "select p.PhotoID, p.Title, p.Description, p.CreatedDate, c.Subject, c.Body, c.UserName " +
                "from PhotoSharingEntities.photos as p inner join PhotoSharingEntities.comments as c on p.PhotoID=c.PhotoID";

            Conn.Open();
            var rd = Cmd.ExecuteReader(CommandBehavior.SequentialAccess);

            ArrayList list = new ArrayList();

            while(rd.Read())
            {
                var data = new
                {
                    PhotoID = rd["PhotoID"].ToString(),
                    Title = rd["Title"].ToString(),
                    Description = rd["Description"].ToString(),
                    CreatedDate = rd["CreatedDate"].ToString(),
                    Subject = rd["Subject"].ToString(),
                    Body = rd["Body"].ToString(),
                    UserName = rd["UserName"].ToString()
                };
                list.Add(data);

            }


            Conn.Close();

            ViewBag.Data = list;
            return View();
        }

        public ActionResult Display(int? id)
        {
            if (id == null)
            {
                return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
            }


            var Cmd = Conn.CreateCommand();
            Cmd.CommandText = "select p.PhotoID, p.Title, p.Description, p.CreatedDate, c.Subject, c.Body, c.UserName " +
                "from PhotoSharingEntities.photos as p inner join PhotoSharingEntities.comments as c on p.PhotoID=c.PhotoID " +
                "where p.PhtotID=" + id;

            Conn.Open();
            var rd = Cmd.ExecuteReader(CommandBehavior.SequentialAccess);

            ArrayList list = new ArrayList();

            while (rd.Read())
            {
                var data = new
                {
                    PhotoID = rd["PhotoID"].ToString(),
                    Title = rd["Title"].ToString(),
                    Description = rd["Description"].ToString(),
                    CreatedDate = rd["CreatedDate"].ToString(),
                    Subject = rd["Subject"].ToString(),
                    Body = rd["Body"].ToString(),
                    UserName = rd["UserName"].ToString()
                };
                list.Add(data);

            }


            Conn.Close();

            ViewBag.Data = list;
            return View();
        }


        public ActionResult CreateComment(int? id)
        {
            if (id == null)
            {
                return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
            }
            ViewBag.PhotoID = id;

            return View();
        }

        [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult CreateComment(Comments comment)
        {
            var Cmd = Conn.CreateCommand();
            Cmd.CommandType = CommandType.StoredProcedure;
            Cmd.CommandText = "PhotoSharingEntities.Add_Comment";

    
            Cmd.Parameters.AddWithValue("username", comment.UserName);
            Cmd.Parameters.AddWithValue("subject", comment.Subject);
            Cmd.Parameters.AddWithValue("body", comment.Body);
            Cmd.Parameters.AddWithValue("photoid", comment.PhotoID);

            Conn.Open();
            Cmd.ExecuteNonQuery();
            Conn.Close();

            return RedirectToAction("Index");
        }
    }
}

##補充 模型瀏覽器 資料庫更新模型 (

##編輯 尋找取代 ( 可以快速把某個字符 取代成 別的 )

接著把 Display controller補完 只要拿剛剛的index來改即可

再繼續接著把 Display 檢視做完 可以從剛剛的index 改

接著處理 create controller 一些細節跟 ADO.NET不一樣

這邊就不用簡單繫結了直接傳Model近來

然後commandtext 也要把結構寫出來

然後 parameters 不用@

然後一樣打開後要關閉

Excutenonqu

這裡讀寫資料都只用到 sqlmand

不像ADO.NET 要用不同物件

處理完功能後

一樣新增檢視

一樣去改 ADO.NET 寫的 Create

至此結束

###在只有一個 model的狀況下 釉藥join 就可以利用到 entitysql

###Views/EntityPhotoComments/Display.cshtml

@{
    ViewBag.Title = "Display";
    var data = ViewBag.Data;
}

<h2>留言列表</h2>

@if (data.GetType().GetProperty("Count").GetValue(data) == 0)
{
    <h2>無任何留言</h2>
}
else
{
    <table class="table">
        <tr>
            <th>
                PhotoID
            </th>
            <th>
                Title
            </th>
            <th>
                Description
            </th>
            <th>
                CreatedDate
            </th>
            <th>
                Subject
            </th>
            <th>
                Body
            </th>
            <th>
                UserName
            </th>
        </tr>

        @foreach (var item in data)
        {
            var PhotoID = item.GetType().GetProperty("PhotoID").GetValue(item);
            <tr>
                <td>
                    @item.GetType().GetProperty("PhotoID").GetValue(item)

                </td>
                <td>
                    @item.GetType().GetProperty("Title").GetValue(item)

                </td>
                <td>
                    @item.GetType().GetProperty("Description").GetValue(item)

                </td>
                <td>
                    @item.GetType().GetProperty("CreatedDate").GetValue(item)

                </td>
                <td>
                    @item.GetType().GetProperty("Subject").GetValue(item)

                </td>
                <td>
                    @item.GetType().GetProperty("Body").GetValue(item)

                </td>
                <td>
                    @item.GetType().GetProperty("UserName").GetValue(item)

                </td>
                <td>
                    @Html.ActionLink("回覆留言", "CreateComment", new { id = PhotoID }) |
                    @Html.ActionLink("詳細資料", "Display", new { id = PhotoID }) |

                </td>
            </tr>
        }

    </table>
}
@Html.ActionLink("回留言列表", "Index")

### Views/EntityPhotoComments/CreateComment.cshtml

@{
    ViewBag.Title = "CreateComment";
}



<h2>回覆留言</h2>
@using (Html.BeginForm())
{
    @Html.AntiForgeryToken();
    <p>
        <label for="subject">回覆主題:</label>
        <input id="subject" name="subject" type="text" />
    </p>

    <p>
        <label for="body">回覆內容:</label>
        <textarea id="body" name="body" rows="5" cols="30"></textarea>

    </p>
    <p>
        <label for="username">回覆人名稱:</label>
        <input id="username" name="username" type="text" />
    </p>

    <input id="photoid" name="photoid" type="hidden" value="@ViewBag.PhotoID" />

    <p>
        <input id="Submit1" type="submit" value="確定送出" class="btn btn-default" />
    </p>


}

接著

#07_ViewModel

分類並做資料存取

一對多 資料做增刪修

一個以上的model 合成邏輯上的 一個 viewmodel

直接在裡面建立類別 Photocomments.cs

做 public List <Photos> photos {get;set;}

新增一個controller

VMPhotoCommentsController.cs

Using model

引用 db entities

然後把資料表查詢的結果整合成剛剛建立好的 viewmodel 物件

然後把一些關鍵欄位查詢好後放到viewbag 然後return viewmodel物件

接著新增檢視 範本 list 模型 留言 砍掉一些沒用的

如果model為空則 尚未有人留言

假設說 想要顯示單筆資料 那在viewmodel要如何做?

如果有些值 viewmodel 呼叫不到 這時剛剛產出的viewbag用途就來了

Display.cshtml 頁面 用 陣列抓值

到此 完整 但viewmodel的地方還沒講完明天繼續

### ViewModel/PhotoComment.cs

using PhotoSharing.DB_EntitySQL.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace PhotoSharing.ViewModel
{
    public class PhotoComments
    {
        public List<Photos> photos { get; set; }
        public List<Comments> comments { get; set; }
    }
}

###ViewModel/Controllers/VMPhotoCommentsController.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Net;
using PhotoSharing.DB_EntitySQL.Models;

namespace PhotoSharing.ViewModel.Controllers
{
    //step 1.4 建立VMPhotoCommentsController
    public class VMPhotoCommentsController : Controller
    {
        PhotoSharingEntities db = new PhotoSharingEntities();

        // GET: VMPhotoComments
        //step 2.1 建立Index Action
        public ActionResult Index(int id=1)
        {
            PhotoComments pc = new PhotoComments()
            {
                photos = db.Photos.ToList(),
                comments = db.Comments.Where(p => p.PhotoID == id).ToList()
            };

            ViewBag.PID = id;
            ViewBag.PTitle = db.Photos.Where(p => p.PhotoID == id).FirstOrDefault().Title;


            return View(pc);
        }

        //step 3.1 建立Display Action
        public ActionResult Display(int? id)
        {
            if (id == null)
            {
                return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
            }


            PhotoComments pc = new PhotoComments()
            {
                photos = db.Photos.Where(p => p.PhotoID == id).ToList(),
                comments = db.Comments.Where(p => p.PhotoID == id).ToList()
            };

            ViewBag.PID = id;
            //ViewBag.PTitle = db.Photos.Where(p => p.PhotoID == id).FirstOrDefault().Title;


            return View(pc);
        }
    }
}

###Views/VMPhotoComments/Index.cshtml

@*//step 2.2 建立Index View(Empty-Without Model)*@
@model PhotoSharing.ViewModel.PhotoComments

@{
    ViewBag.Title = "Index";
}

<h2>Index</h2>

<div class="row">
    <div class="col-xs-3">
        @*//step 2.3 建立討論主題列表*@
        <h4>討論主題列表</h4>
        @foreach (var item in Model.photos)
        {
            <h4>@Html.ActionLink(item.PhotoID + "." + item.Title, "Index", new { id = item.PhotoID })</h4>
        }
    </div>
    <div class="col-xs-9">
        @*//step 2.4 建立回覆列表*@
        <h2>@ViewBag.PTitle</h2>
        @if (Model.comments.Count == 0)
        {
            <h4>尚無任何回覆</h4>
            <p>
                @*//step 4.5 在Index View建立"回覆" ActionLink*@
                @Html.ActionLink("回覆留言", "CreateComment", new { id = ViewBag.PID })

            </p>
        }
        else
        {
            <p>
                @*//step 4.5 在Index View建立"回覆" ActionLink*@
                @Html.ActionLink("回覆留言", "CreateComment", new { id = ViewBag.PID }) |
                @*//step 3.5 在Index View建立"詳細資料" ActionLink*@
                @Html.ActionLink("詳細資料", "Display", new { id = ViewBag.PID }) |
            </p>


            <table class="table">
                <tr>

                    <th>
                        Subject
                    </th>
                    <th>
                        Body
                    </th>
                    <th>
                        UserName
                    </th>
                </tr>

                @foreach (var item in Model.comments)
                {
                    <tr>
                        <td>
                            @item.Subject

                        </td>
                        <td>
                            @item.Body

                        </td>
                        <td>
                            @item.UserName

                        </td>

                    </tr>

                }
            </table>
        }
    </div>
</div>


@*@Html.ActionLink("Edit", "Edit", new { id = item.CommentID }) |
    @Html.ActionLink("Details", "Details", new { id = item.CommentID }) |
    @Html.ActionLink("Delete", "Delete", new { id = item.CommentID })*@

### Views/VMPhotoComments/Display.cshtml

@*//step 3.2 建立Display View(Empty-Without Model)*@
@model PhotoSharing.ViewModel.PhotoComments
@{
    ViewBag.Title = "Display";
}

@*//step 3.3 建立討論主題顯示區*@
<div class="row well">
    <div class="col-xs-12">
        <h2>@Model.photos[0].Title</h2>
        <p>@Model.photos[0].Description</p>
        <p>建立人:@Model.photos[0].UserName</p>
        <p>建立日期:@Model.photos[0].CreatedDate</p>
    </div>
</div>
<hr />
@*//step 3.4 建立回覆列表顯示區*@
<div class="row">
    <div class="col-lg-offset-2 col-xs-8">
        @if (Model.comments.Count == 0)
        {
            <h4>尚無任何回覆</h4>

        }
        else
        {
            foreach (var item in Model.comments)
            {
                <div class="row table-bordered">
                    <div class="col-xs-12">
                        <h3>@item.Subject</h3>
                        <p>@item.Body</p>
                        <p class="text-right">建立人:@item.UserName</p>

                    </div>
                </div>
                <hr />
            }
        }
    </div>
</div>
@*//step 3.6 在Display View建立"回討論主題列表" ActionLink*@
@Html.ActionLink("回留言列表", "Index", new { id = ViewBag.PID })

###老師步驟

//ViewModel
//step 1 建立ViewModel
//step 1.1 建立ViewModel資料夾
//step 1.2 建立PhotoComments類別
//step 1.3 建立Controller資料夾
//step 1.4 建立VMPhotoCommentsController

//step 2 建立討論主題與回覆列表
//step 2.1 建立Index Action
//step 2.2 建立Index View(Empty-Without Model)
//step 2.3 建立討論主題列表
//step 2.4 建立回覆列表

//step 3 建立討論主題與回覆詳細資料
//step 3.1 建立Display Action
//step 3.2 建立Display View(Empty-Without Model)
//step 3.3 建立討論主題顯示區
//step 3.4 建立回覆列表顯示區
//step 3.5 在Index View建立"詳細資料" ActionLink
//step 3.6 在Display View建立"回討論主題列表" ActionLink