ASP.NET Core 後台查詢頁面教學

�����}�o�`�����@�إ\��O�b��x�s�W���@��ơA�M��b�e�x��ܸ�ơA�����������i�γ̷s�����\��A�����оDZN�|�ЧA�p��إ߫�x���i���@�ɭ��A�æb�e�x��ܸ�ơC

���оǽd�ҷ|�إ� ASP.NET Core MVC �s�M�סA�s�W�@�ӫ�x���i�޲z�����A�z�L�d����ܸ�Ʈw�������i��ơC

�d�ߥ�����Ƨe�{�O²�檺�ʧ@�A�ӧڷ|�[�W�����d�߬۹�O���������ʧ@�A�ڷ|�ܽd�@�ӧڱ`�Ϊ�������k���U��ѦҡC

���d�Ҩϥ� ASP.NET Core MVC �����O .NET6�A�e�ݨϥ� Vue3 �ج[�A��ݸ�Ʈw�ϥ� SQL Server 2019�A�ϥ� Dapper �M��s�u�A�好���d�ҥi�H�U���C

�ؿ�

1 �إ߱M�� [2 �]�p�d�߭���](#step2) 3 ��Ʈw�y�k [4 �M�װ�¦�]�w](#step4) [4.1 ��J Vue3 �M�� [4.2 ��J jQuery BlockUI Plugin �M�� [4.3 ���� Json �^�ǹw�]�p�g�]�w](#step7) 5 �d�ߤ��i [5.1 View ��� Vue3 �y�k 5.2 Controller �y�k 5.3 Ū�� appsettings.json 5.4 �w�� Dapper 5.5 �إ� ViewModel [6 �W������d�ߥ\�� 6.1 �s�W VuePagination.js ���� 6.2 �ޥ� VuePagination.js ���� 6.3 ���U VuePagination.js ���� 6.4 �ϥΤ������� 6.5 View �d�ߥ\��վ� [6.6 grid ����W������ݩ� 6.7 Controller �d�ߥ\��վ� 6.8 ViewModel �վ� 6.9 �d�ҤU��

�إ߱M��

�}�� Visual Studio 2022�A�إ߷s�M�׬��uASP.NET Core Web ���ε{�� (Model-View-Controller)�v�C

img1

��J�M�צW�١B���|�C

img2

�[�c��ܡu.NET 6.0�v�����A���U�u�إߡv�N�|�إߦ��M�סC

img3

�]�p�d�߭���

�o�̧ڭ̷s�W�@�� Controller �M���B�z��x�����i���@�C �b Controllers ���k��s�W�@�ӡu����v�C

img4

��ܡuMVC ��� �V �ťաv, ���W���uAdmAnnoController�v�C

�b \Controllers\AdmAnnoController.cs �ɮת� Index() ���k���u�s�W�˵��v�A�i�H�s�W���� View �����C

img5

��ܡuRazor �˵��v�A�W�ٺ����w�]�uIndex�v�A�Ŀ�u�ϥΪ����t�m���v�A���u�s�W�v�C

img5-1

�e���]�p�ڭ̴N�q Bootstrap �ƻs�@�ǾA�X�d����ܪ��d�Ҩ� View �̭��C �ڥΨ쪺�˦��� Card, Form, Table, Button

�ڳ]�p�F�d�ߵe���A�����ϥΥH�U�y�k���N \Views\AdmAnno\Index.cshtml �즳���y�k�C

<div id="QueryPanel" class="card">
    <div class="card-header">
        ���i���@
    </div>
    <div class="card-body">
        <div class="row">
            <div class="col-auto">
                <label for="AnnoSubject" class="col-form-label">���i�D�D</label>
            </div>
            <div class="col-auto">
                <input type="text" id="AnnoSubject" class="form-control">
            </div>
            <div class="col-auto">
                <label for="AnnoStatus" class="col-form-label">���i���A</label>
            </div>
            <div class="col-auto">
                <select class="form-select" id="AnnoStatus">
                    <option value="1">���</option>
                    <option value="0">����</option>
                </select>
            </div>
        </div>
    </div>
    <div class="card-header">
        <button type="button" class="btn btn-primary">�d��</button>
    </div>
	<div class="card-body">
		<table class="table">
			<thead>
				<tr>
					<th>���i���</th>
					<th>���i�D�D</th>
					<th>���i���e</th>
					<th>���i���A</th>
				</tr>
			</thead>
			<tbody>
			</tbody>
		</table>
	</div>
</div>

�NProgram.cs�ɮ׭˼ƲĤG�檺pattern: "{controller=Home}/{action=Index}/{id?}");�אּpattern: "{controller=AdmAnno}/{action=Index}/{id?}");�C

�� F5 ���������N�|�ݨ�H�U���e���C

img6

����o�̥D�n�O���]�p�ڭ̪��e���A���U�ӴN�n�]�p��Ʈw�M��}�l�g�{���X�F�C

��Ʈw�y�k

�ڭ̷|�ϥ� SQL Server �ӷ��@��ƨӷ��A�ڤw�g�s�W�n ��Teach�� ����Ʈw�F�A���ۥH�U�y�k�s�W���i Table�C

CREATE TABLE [dbo].[Announcement] (
[Pkey] int IDENTITY(1, 1) NOT NULL,
[AnnoDate] date NOT NULL,
[AnnoSubject] nvarchar(50) NOT NULL,
[AnnoContent] nvarchar(1000) NOT NULL,
[AnnoStatus] smallint NOT NULL,
PRIMARY KEY CLUSTERED ([Pkey] ASC)
 ON [PRIMARY]
)

���F Table ����A�o�̧ڴN�����s�W 16 �����ո�ơA�]���ڭ̲Ĥ@�ӥ\��O�d�ߡA���F��Ƥ~��ݥX���G�C

insert into [dbo].[Announcement]([AnnoDate],[AnnoSubject],[AnnoContent],[AnnoStatus]) values ('2022-02-01 00:00:00',N'Subject1',N'Content1',1)
insert into [dbo].[Announcement]([AnnoDate],[AnnoSubject],[AnnoContent],[AnnoStatus]) values ('2022-02-02 00:00:00',N'Subject2',N'Content2',1)
insert into [dbo].[Announcement]([AnnoDate],[AnnoSubject],[AnnoContent],[AnnoStatus]) values ('2022-02-03 00:00:00',N'Subject3',N'Content3',1)
insert into [dbo].[Announcement]([AnnoDate],[AnnoSubject],[AnnoContent],[AnnoStatus]) values ('2022-02-04 00:00:00',N'Subject4',N'Content4',1)
insert into [dbo].[Announcement]([AnnoDate],[AnnoSubject],[AnnoContent],[AnnoStatus]) values ('2022-02-05 00:00:00',N'Subject5',N'Content5',1)
insert into [dbo].[Announcement]([AnnoDate],[AnnoSubject],[AnnoContent],[AnnoStatus]) values ('2022-02-06 00:00:00',N'Subject6',N'Content6',1)
insert into [dbo].[Announcement]([AnnoDate],[AnnoSubject],[AnnoContent],[AnnoStatus]) values ('2022-02-07 00:00:00',N'Subject7',N'Content7',1)
insert into [dbo].[Announcement]([AnnoDate],[AnnoSubject],[AnnoContent],[AnnoStatus]) values ('2022-02-08 00:00:00',N'Subject8',N'Content8',1)
insert into [dbo].[Announcement]([AnnoDate],[AnnoSubject],[AnnoContent],[AnnoStatus]) values ('2022-02-09 00:00:00',N'Subject9',N'Content9',1)
insert into [dbo].[Announcement]([AnnoDate],[AnnoSubject],[AnnoContent],[AnnoStatus]) values ('2022-02-10 00:00:00',N'Subject10',N'Content10',1)
insert into [dbo].[Announcement]([AnnoDate],[AnnoSubject],[AnnoContent],[AnnoStatus]) values ('2022-02-11 00:00:00',N'Subject11',N'Content11',1)
insert into [dbo].[Announcement]([AnnoDate],[AnnoSubject],[AnnoContent],[AnnoStatus]) values ('2022-02-12 00:00:00',N'Subject12',N'Content12',1)
insert into [dbo].[Announcement]([AnnoDate],[AnnoSubject],[AnnoContent],[AnnoStatus]) values ('2022-02-13 00:00:00',N'Subject13',N'Content13',1)
insert into [dbo].[Announcement]([AnnoDate],[AnnoSubject],[AnnoContent],[AnnoStatus]) values ('2022-02-14 00:00:00',N'Subject14',N'Content14',1)
insert into [dbo].[Announcement]([AnnoDate],[AnnoSubject],[AnnoContent],[AnnoStatus]) values ('2022-02-15 00:00:00',N'Subject15',N'Content15',1)
insert into [dbo].[Announcement]([AnnoDate],[AnnoSubject],[AnnoContent],[AnnoStatus]) values ('2022-02-16 00:00:00',N'Subject16',N'Content16',1)

�M�װ�¦�]�w

�o�̷|���w�� ASP.NET Core MVC �M�׼W�[�@�ǰ�¦�]�w�A�H��K����}�o�C

�[�J Vue3 �M��

Vue3 �O�e�ݱ�����쪺�ج[���O�w�A���} \Views\Shared_Layout.cshtml �ɮסA�b�U�� JavaScript �ޥμW�[ Vue3 ���O�w�y�k�A���Ǫ��n�D�n��b jQuery ����~��C

<script src="https://unpkg.com/vue@3"></script>

img7

���b Layout �[�W Vue3 �ޥΫ�A�ڭ̴N�i�H�b�Ҧ��������ϥ� Vue3 �y�k�F�A���ޥλy�k�ӷ��i�Ѧҩx����C

�[�J jQuery BlockUI Plugin �M��

jQuery BlockUI �O���e�ݦV��ݩI�s�ɡA�Ȯ���w�e�ݵe���A�H����G���I�������D�C �b���[�J Vue3 �M�󪺤U��A�[�J�ޥλy�k�C

<script src="https://malsup.github.io/jquery.blockUI.js"></script>

���� Json �^�ǹw�]�p�g�]�w

�b .NET Framework �ϥ� Json �^�ǮɡA�e�ݦ��쪺 Json ����j�p�g�]�w�P ViewModel �ۦP�A�Ӧb .NET Core �ɫh�w�]�}�Y���p�g (�m�p���R�W)�A�o�̧ڳ��|�վ㦨�P ViewModel �ۦP�C

�b Program.cs �[�J�H�U�y�k�G

// ���� Json �^�Ǥj�p�g�P ViewModel �ۦP
builder.Services.AddControllers().AddJsonOptions(options =>
    {
        options.JsonSerializerOptions.PropertyNamingPolicy = null;
    });
img7-1

�d�ߤ��i

�o�̷|�}�l�g�{���X�A�b�]�p�e���W�� 2 �Ӭd�����A1 �Ӭd�߫��s�A�����U�d�߶s��A�a�J�d�߱���A�q��Ʈw��Ū����Ƨe�{�C

View ���[ Vue3 �y�k

���ڭ̦b Index.cshtml �ϥ� Bootstrap �]�p�n�F�e���A���ۭn�[�W Vue3 ���d�߰ʰ_�ӡC �N�H�U���y�k�����л\�� \Views\AdmAnno\Index.cshtml �̭��C

<div id="app">
    <div id="QueryPanel" class="card">
        <div class="card-header">
            ���i���@
        </div>
        <div class="card-body">
            <div class="row">
                <div class="col-auto">
                    <label for="queryFormAnnoSubject" class="col-form-label">���i�D�D</label>
                </div>
                <div class="col-auto">
                    <input type="text" id="queryFormAnnoSubject" class="form-control" v-model="queryForm.AnnoSubject">
                </div>
                <div class="col-auto">
                    <label for="queryFormAnnoStatus" class="col-form-label">���i���A</label>
                </div>
                <div class="col-auto">
                    <select class="form-select" id="queryFormAnnoStatus" v-model="queryForm.AnnoStatus">
                        <option value="1">���</option>
                        <option value="0">����</option>
                    </select>
                </div>
            </div>
        </div>
        <div class="card-header">
            <button type="button" class="btn btn-primary" v-on:click="Query()">�d��</button>
        </div>
		<div class="card-body">
			<table class="table">
				<thead>
					<tr>
						<th>���i���</th>
						<th>���i�D�D</th>
						<th>���i���e</th>
						<th>���i���A</th>
					</tr>
				</thead>
				<tbody>
					<tr v-for="(item, index) in grid.datas">
						<td>{{item.AnnoDate}}</td>
						<td>{{item.AnnoSubject}}</td>
						<td>{{item.AnnoContent}}</td>
						<td>{{item.AnnoStatusName}}</td>
					</tr>
				</tbody>
			</table>
		</div>
    </div>
</div>
@section scripts {
<script>
    const app = Vue.createApp({
    data() {
        return {
            queryForm:{
                AnnoSubject: ''
                , AnnoStatus: '1'
            }
            , grid:{
                datas:[]
            }
        }
    }
    , methods: {
        Query() {
            var self = this;
 
            // �զX������
            var postData = {};
            postData['AnnoSubject'] = self.queryForm.AnnoSubject;
            postData['AnnoStatus'] = self.queryForm.AnnoStatus;
			
            $.blockUI();
            // �ϥ� jQuery Ajax �ǰe�ܫ��
            $.ajax({
                url:'@Url.Content("~/AdmAnno/Query")',
                method:'POST',
                dataType:'json',
                data: { inModel: postData },
                success: function (datas) {
					$.unblockUI();
                    if (datas.ErrMsg) {
                        alert(datas.ErrMsg);
                        return;
                    }
                    // �j�w�C��
                    self.grid.datas = datas.Grid;
                },
                error: function (err) {
                    $.unblockUI();
                    alert(err.status + " " + err.statusText + '\n' + err.responseText);
                }
            });
 
        }
      }
    });
	const vm = app.mount('#app');
</script>
}

Controller �y�k

�b View �d�߫�|�I�s ~/AdmAnno/Query�A�b \Controllers\AdmAnnoController.cs �[�J�H�U Action�C

/// <summary>
/// �d�ߤ��i
/// </summary>
/// <param name="inModel"></param>
/// <returns></returns>
public IActionResult Query(QueryIn inModel)
{
	QueryOut outModel = new QueryOut();
	outModel.Grid = new List<AnnoModel>();
 
	// ��Ʈw�s�u�r��
	string connStr = _configuration.GetConnectionString("SqlServer");
	using (var cn = new SqlConnection(connStr))
	{
		// �D�n�d�� SQL
		string sql = @"SELECT Pkey, CONVERT(varchar(12) , AnnoDate, 111 ) as AnnoDate, AnnoSubject, AnnoContent, AnnoStatus, Case AnnoStatus when '1' then '���' when '0' then '����' end As AnnoStatusName
						FROM Announcement 
						WHERE 1=1 ";
 
		if (!string.IsNullOrEmpty(inModel.AnnoSubject))
		{
			sql += " AND AnnoSubject LIKE @AnnoSubject ";
		}
		if (!string.IsNullOrEmpty(inModel.AnnoStatus))
		{
			sql += " AND AnnoStatus = @AnnoStatus ";
		}
		sql += " ORDER BY AnnoDate desc, AnnoStatus ";
		
		object param = new
		{
			AnnoSubject = "%" + inModel.AnnoSubject + "%",
			AnnoStatus = inModel.AnnoStatus
		};
		
		// �ϥ� Dapper �d��
		var list = cn.Query<AnnoModel>(sql, param);
		
		// ��X����
		foreach (var item in list)
		{
			outModel.Grid.Add(item);
		}
	}
	return Json(outModel);
}

Ū�� appsettings.json

�ڱN��Ʈw�s�u��b appsettings.json �̭��A���} appsettings.json ��A�[�J�H�U�s�u�r��C

"ConnectionStrings": {
	"SqlServer": "Data Source=127.0.0.1;Initial Catalog=Teach;Persist Security Info=false;User ID=test;Password=test;"
}
img8

�b .NET 6 �n���o appsettings.json ���]�w�ӷ��A�n�b Controller �W�[�غc�lŪ�� Configuration�C

private readonly IConfiguration _configuration;
 
public AdmAnnoController(IConfiguration configuration)
{
	_configuration = configuration;
}

�w�� Dapper

�ڸ�Ʈw���ʪ���ϥηL�� ORM �M�� Dapper�A�ݭn�w�� Dapper �~��ϥΡC �}�ҡu�M�� > �޲z NuGet �M��v�C

img9

�j�M�uDapper�v�A�w�˦��M��C

img10

�إ� ViewModel

ViewModel �O�Ψөw�q Controller �P View ���������w�q�A�ڭ̭��إߤF�s Controller�A�ҥH�o���ӫإߥ������� ViewModel�C �b�uModel ���k�� > �[�J > ���O�v�C

img11

�M��R�W�� ��AdmAnnoViewModel���C

img12

�M��b AdmAnnoViewModel ���O�̭��A�[�J�b Controller �Ψ쪺 ViewModel�C

public class QueryIn
{
	public string AnnoSubject { get; set; }
	public string AnnoStatus { get; set; }
}
 
public class QueryOut
{
	public List<AnnoModel> Grid { get; set; }
}
 
public class AnnoModel
{
	public string Pkey { get; set; }
	public string AnnoDate { get; set; }
	public string AnnoSubject { get; set; }
	public string AnnoContent { get; set; }
	public string AnnoStatus { get; set; }
	public string AnnoStatusName { get; set; }
}
img12-1

������o�̤���A�ڭ̴N�i�H����²�檺�d�ߥ\��F�A���U F5 ��A����u�d�ߡv�s�A�N�i�H��ܥX��Ʈw������ƤF�C

img13

�i�O�o�ɭԧڭ��ٯʤ@�Ӥ������\��A���U�ӧڭ̴N�~�򧹦��������оǡC

�W�[�����d�ߥ\��

�����W�������˦��ܦh�ءA�ӧڴ��ѧڳ̱`�Ϊ� Vue3 �������󵹦U��ѦҡA�ڭ̫e�ݬO�إߦb Vue3 �W�����A�ҥH�ڷ|�b Vue3 �s�W�@�Ӥ���������C

�o�q���оǷ|��������@�I�A�ڬO�v�B�y�k�оǡA�p�G�L�k�z�Ѫ��ܡA�i��U���ݤ@�U���㪺�d�ҨӤ��|����e�����@�I�C

�s�W VuePagination.js ����

��������O�@�Ӧh�ƭ������|�Ψ쪺�\��A��ij�i�H�s�W�@���ɮסA�N�����޿�g�b�̭��A�M��b Vue3 �N����ޤJ�C

�b /js �ؿ����s�W�@���ɮסA�ɮשR�W�� ��VuePagination.js���C

img14

�M��bVuePagination.js ���K�W�H�U�y�k�C

const VuePagination = {
    data() {
        return {
            PerPage:'�C��'
            , PageTiems:'��'
            , Page:'��'
            , Times:'��'
            , Total:'�@'
            , TotalPage:'��'
        }
    }
    , props: ['pagination']
    , template: `
        <div style="text-align:right">
            <span v-for="pageNo in pagination.pages">
                <a v-if="pagination.pageNo != pageNo" v-on:click="gotoPage(pageNo)" style="cursor:pointer">
                    {{ pageNo }}
                </a>
                <label v-else>
                    {{ "[" + pageNo + "]" }}
                </label>&nbsp;
            </span>
            <span class="pager-nav">
                �i{{PerPage}}&nbsp;<input type="text" maxlength="3" style="width:35px;text-align:center;font-size:12px;" name="pageSize" :value="pagination.pageSize" v-on:change="onchange"/>
                &nbsp;{{PageTiems}}�A
                {{Total}} {{pagination.totalPage}} {{TotalPage}} {{pagination.totalCount}} {{PageTiems}}�j
                <button type="button" class="btn btn-secondary btn-sm pager-btn" style="margin-bottom: 5px;margin-right:5px;" v-on:click="gotoPage()">Q</button>
            </span>
        </div>`
    , methods: {
        gotoPage(pageNo) {
            var self = this;
            console.log(pageNo);
            // �O�_���ǤJ���w����
            if (pageNo !== undefined) {
                if (pageNo === '<') {
                    self.pagination.pageNo = parseInt(self.pagination.pageNo) - 1;
                } else if (pageNo === '>') {
                    self.pagination.pageNo = parseInt(self.pagination.pageNo) + 1;
                } else if (pageNo === '<<') {
                    self.pagination.pageNo = (Math.floor((parseInt(self.pagination.pageNo) - 10) / 10) * 10 + 1);
                } else if (pageNo === '>>') {
                    self.pagination.pageNo = (Math.floor((parseInt(self.pagination.pageNo) + 10) / 10) * 10 + 1);
                } else {
                    self.pagination.pageNo = parseInt(pageNo);
                }
            } else {
                self.pagination.pageNo = 1;
            }
            // ���w���Ƭ�0�A�۰��ܧ�1
            if (parseInt(self.pagination.pageNo) === 0 || self.IsNumeric(self.pagination.pageNo) === false) {
                self.pagination.pageNo = 1;
            }
            // ���w���Ƥj���`���ơA�۰��ܧ��`����
            self.pagination.pageNo =
                parseInt(self.pagination.pageNo) > parseInt(self.pagination.totalPage)
                    ? self.pagination.totalPage : self.pagination.pageNo;
            // ���w���Ƭ�0�A�۰��ܧ�10
            if (parseInt(self.pagination.pageSize) === 0 || self.IsNumeric(self.pagination.pageSize) === false) {
                self.pagination.pageSize = 10;
            }
            // call on even
            this.$emit('requery', { pagination: self.pagination });
        }
        , onchange(e) {
            var self = this;
            var re = /[^0-9]/;
            if (re.test(e.target.value) === false) {
                self.pagination[e.target.name] = parseInt(e.target.value);
            }
        }
        , IsNumeric(n) {
            return (n - 0) === n && n.toString().length > 0;
        }
    }
};

�ޥ� VuePagination.js ����

�b \Views\Shared_Layout.cshtml �� JavaScript �W�[�ޥ� VuePagination.js �ɮסC

<script src="~/js/VuePagination.js"></script>

img15

���U VuePagination.js ����

Vue3 ����ݭn���U�b\Views\AdmAnno\Index.cshtml�ɮת� Vue.createApp({}); ���~��A�� Vue ������U����A�W�٬� ��vue-pagination���C

app.component('vue-pagination', VuePagination);

img16

�ϥΤ�������

���U�n����A�N�i�H�b�e���W��������m�A��W��������C

<vue-pagination v-bind:pagination="grid.pagination" v-on:requery="reQuery"></vue-pagination>

img20

�]�������|���s�d�ߡA�ҥH�o����|�I�s�d�߭��� reQuery() �\��A���歫�s�d�ߡC

View �d�ߥ\��վ�

�ڭ̭��w�g�g�n�F�򥻪��d�� Query()�A�o�̦]�������\��A�վ�@�U�A�üW�[�@�� reQuery() �������ɥi�H���s�I�s�C �վ�᪺��k�O�G

Query(reQuery) {
	var self = this;
 
	if (reQuery !== 'reQuery') {
		self.grid.pagination.pageNo = 1;
	}
 
	// �զX������
	var postData = {};
	postData['AnnoSubject'] = self.queryForm.AnnoSubject;
	postData['AnnoStatus'] = self.queryForm.AnnoStatus;
 
	// ���[����
	postData['pagination'] =  JSON.parse(JSON.stringify(self.grid.pagination));
 
	$.blockUI();
	// �ϥ� jQuery Ajax �ǰe�ܫ��
	$.ajax({
		url:'@Url.Content("~/AdmAnno/Query")',
		method:'POST',
		dataType:'json',
		data: { inModel: postData },
		success: function (datas) {
			$.unblockUI();
			if (datas.ErrMsg) {
				alert(datas.ErrMsg);
				return;
			}
			// �j�w�C��
			self.grid.datas = datas.Grid;
			self.grid.pagination = datas.pagination;
		},
		error: function (err) {
			$.unblockUI();
			alert(err.status + " " + err.statusText + '\n' + err.responseText);
		}
	});
 
}
// ���歫�d
, reQuery(emitData) {
	var self = this;
	if (emitData !== undefined) {
		self.grid.pagination = emitData.pagination;
	}
	self.Query('reQuery');
}

grid ����W�[�����ݩ�

�b Vue3 �즳�ŧi�� data �ݩ� grid �n�W�[�@�Ӥ����ݩʡG

, pagination: {
	pages: [], pageNo: '1', pageSize: '10', totalCount: ''
}
img20-1

Controller �d�ߥ\��վ�

�b Controller �쥻�]�g�n�F�򥻬d�ߥ\��A�o�̦]�W�[�����d�߫�A�]�n�վ�@�U�y�k�A�i�ΥH�U�y�k�������N�쥻���y�k�G

/// <summary>
/// �d�ߤ��i
/// </summary>
/// <param name="inModel"></param>
/// <returns></returns>
public IActionResult Query(QueryIn inModel)
{
	QueryOut outModel = new QueryOut();
	outModel.Grid = new List<AnnoModel>();
 
	// ��Ʈw�s�u�r��
	string connStr = _configuration.GetConnectionString("SqlServer");
	using (var cn = new SqlConnection(connStr))
	{
		// �D�n�d�� SQL
		string sql = @"SELECT Pkey, CONVERT(varchar(12) , AnnoDate, 111 ) as AnnoDate, AnnoSubject, AnnoContent, AnnoStatus, Case AnnoStatus when '1' then '���' when '0' then '����' end As AnnoStatusName
						FROM Announcement 
						WHERE 1=1 ";
 
		if (!string.IsNullOrEmpty(inModel.AnnoSubject))
		{
			sql += " AND AnnoSubject LIKE @AnnoSubject ";
		}
		if (!string.IsNullOrEmpty(inModel.AnnoStatus))
		{
			sql += " AND AnnoStatus = @AnnoStatus ";
		}
		sql += " ORDER BY AnnoDate desc, AnnoStatus ";
 
		object param = new
		{
			AnnoSubject = "%" + inModel.AnnoSubject + "%",
			AnnoStatus = inModel.AnnoStatus
		};
 
		// �����B�z
		int totalRowCount = 0;
		if (inModel.pagination.pageNo > 0)
		{
			string orderBy = "";
			// ���o�`����
			string totalRowSql = sql;
			if (totalRowSql.ToUpper().IndexOf("ORDER BY") > -1)
			{
				orderBy = totalRowSql.Substring(sql.ToUpper().LastIndexOf("ORDER BY"));
				totalRowSql = totalRowSql.Replace(orderBy, "");
			}
			totalRowSql = "SELECT COUNT(*) AS CNT FROM (" + totalRowSql + ") CNT_TABLE";
			var rowCnt = cn.Query(totalRowSql, param);
			foreach (var item in rowCnt)
			{
				totalRowCount = item.CNT;
			}
 
			// ���o���� SQL
			int startRow = ((inModel.pagination.pageNo - 1) * inModel.pagination.pageSize) + 1;
			int endRow = (startRow + inModel.pagination.pageSize) - 1;
			orderBy = sql.Substring(sql.ToString().ToUpper().LastIndexOf("ORDER BY"));
			sql = sql.Replace(orderBy, "");
			// �h�� Order by �O�W
			orderBy = orderBy.ToUpper().Replace("ORDER BY", "");
			StringBuilder newOrderBy = new StringBuilder();
			int index = 0;
			string[] orderBys = orderBy.Split(',');
			for (int i = 0; i < orderBys.Length; i++)
			{
				if (newOrderBy.Length > 0) { newOrderBy.Append(","); }
				string ob = orderBys[i];
				index = ob.IndexOf('.');
				if (index > -1)
				{
					newOrderBy.Append(ob.Substring(index + 1));
				}
				else
				{
					newOrderBy.Append(ob);
				}
			}
			newOrderBy.Insert(0, "ORDER BY ");
 
			sql = string.Concat(
				new object[] {
					"SELECT * FROM (SELECT *, ROW_NUMBER() OVER (", newOrderBy.ToString(), ") AS RCOUNT FROM (", sql, ") PAGE_SQL ) PAGE_SQL2 WHERE PAGE_SQL2.RCOUNT BETWEEN "
					, startRow, " AND ", endRow, " ", newOrderBy.ToString() });
		}
 
		// �ϥ� Dapper �d��
		var list = cn.Query<AnnoModel>(sql, param);
 
		// ��X����
		foreach (var item in list)
		{
			outModel.Grid.Add(item);
		}
 
		// �p�����
		outModel.pagination = this.PreparePage(inModel.pagination, totalRowCount);
	}
	return Json(outModel);
}
 
/// <summary>
/// �p�����
/// </summary>
/// <param name="model"></param>
/// <param name="TotalRowCount"></param>
/// <returns></returns>
public PaginationModel PreparePage(PaginationModel model, int TotalRowCount)
{
	List<string> pages = new List<string>();
	int pageStart = ((model.pageNo - 1) / 10) * 10;
	model.totalCount = TotalRowCount;
	model.totalPage =
			Convert.ToInt16(Math.Ceiling(
			 double.Parse(model.totalCount.ToString()) / double.Parse(model.pageSize.ToString())
			));
 
	if (model.pageNo > 10)
		pages.Add("<<");
	if (model.pageNo > 1)
		pages.Add("<");
	for (int i = 1; i <= 10; ++i)
	{
		if (pageStart + i > model.totalPage)
			break;
		pages.Add((pageStart + i).ToString());
	}
	if (model.pageNo < model.totalPage)
		pages.Add(">");
	if ((pageStart + 10) < model.totalPage)
		pages.Add(">>");
	model.pages = pages;
	return model;
}

�b SQL Server �������A�ڬO�����ק� SQL �y�k�A���d���`���ơA�A�d�߻ݭn���d���ơA���C�������ɡA���|���s�p��A�u�d�߻ݭn����ƽd��C

ViewModel �վ�

ViewModel �������N�O�W�[����������A�o�̧ڴN�����K�W�y�k�A�i�H�������N���e�� Model�C

public class QueryIn
{
	public string AnnoSubject { get; set; }
	public string AnnoStatus { get; set; }
 
	public PaginationModel pagination { get; set; }
}
 
public class QueryOut
{
	public List<AnnoModel> Grid { get; set; }
	public PaginationModel pagination { get; set; }
}
 
public class AnnoModel
{
	public string Pkey { get; set; }
	public string AnnoDate { get; set; }
	public string AnnoSubject { get; set; }
	public string AnnoContent { get; set; }
	public string AnnoStatus { get; set; }
	public string AnnoStatusName { get; set; }
}
 
/// <summary>
// ���� Model
/// </summary>
public class PaginationModel
{
	public List<string> pages { get; set; }
	public int pageNo { get; set; }
	public int pageSize { get; set; }
	public int totalPage { get; set; }
	public int totalCount { get; set; }
}

�������o�̫�A�N�i�H���դ������\��F�A�� F5 ����M�סA�d�߸�ƫ�N�|��ܤ��������G�C

img17

������ 2 ���C

img18

�o�O�ګܱ`�Ϊ��e�ݤ�������A���ɵ��A�C

PreviousASP.NET Core 客製化Model Validation 預設錯誤訊息NextASP.NET Core 網站生命週期

Last updated