好用字體 FiraCode

https://github.com/tonsky/FiraCode

解壓後安裝 dist 底下的 ttf 即可

在 vs 中使用

tool -> options -> environment -> Fonts and Colors

jquery datatable server side paging for postgresql

參考自這篇修改

https://github.com/peterschlosser/DataTablesCoreMVCDapper

因為常用 jquery datatable 但是一直沒時間整合後端分頁,所幸老外都寫好了

這篇需要注意到分頁頁碼有 bug 需要將 DataTablesLogicDapper.cs 修改成以下這樣才可以正常運作

// Copyright (c) Peter Schlosser. All rights reserved.  Licensed under the MIT license. See LICENSE.txt in the project root for license information.
using DataTablesCoreMVCDapper.Data;
using System.Threading.Tasks;

namespace DataTablesCoreMVCDapper.Models
{
    /// <summary>
    /// The DataTablesLogicDapper links DataTables AJAX requests from MVC Controllers to non-generic
    /// response handlers fulfilled by Dapper ORM.  The non-generic methods define the root SQL query
    /// and pass requests through the DataTablesRequestAsync generic method for request fulfillment.
    /// </summary>
    public class DataTablesLogicDapper
    {
        /// <summary>
        /// Handles generic DataTablesRequest for TSource data using Dapper ORM
        /// </summary>
        /// <param name="request">DataTables Ajax Request</param>
        /// <param name="baseSQLQuery">The base SQL query for TSource</param>
        /// <returns>DataTablesResponse for TSource data</returns>
        public static async Task<DataTablesResponse<TSource>> DataTablesRequestAsync<TSource>(DataTablesRequest request, string baseSQLQuery)
        {
            //這邊要改 MSSQL or PG
            // prepare the data and count SQL queries
            //var totalQuery = new DataTablesContextPG(baseSQLQuery, request);

            var totalQuery = new DataTablesContext(baseSQLQuery, request);
            var filterQuery = totalQuery.Where();

            //
            int total = await totalQuery.CountAsync();

            //
            int recordsFilter = await filterQuery.CountAsync();

            var dataQuery = filterQuery.OrderBy().SkipTake();

            // run the queries and return the response
            return new DataTablesResponse<TSource>()
            {
                Draw = request.Draw,
                //RecordsTotal = await totalQuery.CountAsync(),

                //資料總數
                RecordsTotal = total,

                //分頁數量
                RecordsFiltered = recordsFilter,

                //
                Data = await dataQuery.DataAsync<TSource>(),
                Error = request.Error,
            };
        }

        /// <summary>
        /// Handles the DataTablesRequest for the Customer Database
        /// </summary>
        /// <param name="request">DataTables Ajax Request</param>
        /// <returns>DataTablesResponse for Customer table</returns>
        /// <remarks>
        /// When intergration with projects without Data.Repository classes, the 
        /// base SQL query may be hard-coded here when making the call to the generic
        /// DataTablesRequestAsync() method.
        /// </remarks>
        public static async Task<DataTablesResponse<Customer>> DataTablesCustomerRequestAsync(DataTablesRequest request)
        {
            return await DataTablesRequestAsync<Customer>(request, CustomerRepository.BaseQuery());

            //return await DataTablesRequestAsync<Customer>(request, CustomerRepositoryPG.BaseQuery());
        }
    }
}

 

接著若要使用 postgresql 則需要安裝 npgsql 並且修改連線字串

將此專案內新增類別 DataTablesContextPG

其中 DataTablesContextPG  需要修改 MSSQL 內的 + 號語法為 PG 的 || 語法,並且把 count 的子查詢加上 alias name

public class DataTablesContextPG
    {
        public DataTablesContextPG(string query, DataTablesRequest request)
        {
            Query = query;
            Request = request;
        }

        public static string SearchToken { get; } = "SearchValue";
        public string Query { get; protected internal set; }
        public DataTablesRequest Request { get; protected set; }

        /// <summary>
        /// Dapper ORM SqlConnection Factory
        /// </summary>
        /// <returns>A new instance of DbConnection with the database for use by Dapper ORM.</returns>
        public static DbConnection GetOpenConnection()
        {
            var connection = new NpgsqlConnection(ConfigurationExtensions.GetConnectionString(Startup.Configuration, "DefaultConnection"));
            connection.Open();

            return connection;
        }

        /// <summary>
        /// Runs the specified data query and returns an IEnumerable of one or more TSource records.
        /// </summary>
        public static async Task<IEnumerable<TSource>> QueryAsync<TSource>(string query, object param)
        {
            using (var db = GetOpenConnection())
            {
                return await db.QueryAsync<TSource>(query, param);
            }
        }

        /// <summary>
        /// Runs the specified T query and return the single T result.
        /// </summary>
        public static async Task<T> QuerySingleAsync<T>(string query, object param = null)
        {
            using (var db = GetOpenConnection())
            {
                return await db.QuerySingleAsync<T>(query, param);
            }
        }
    }

    /// <summary>
    /// Extensions to format and execute generic SQL queries from DataTablesContext.Request settings
    /// </summary>
    /// <remarks>
    /// These extensions link the DataTablesContext with data returned by Dapper ORM.  The SQL query
    /// string formatters prepare the SQL query (DataTablesContext.Query) for sorting, filtering and
    /// paging using criteria defined by the DataTables AJAX request (DataTablesContext.Request.)
    /// The SQL query executors pass one or more SQL queries through mehtods in DataTablesContext
    /// down to Dapper ORM returning data needed to fulfill the DataTables AJAX request.
    /// </remarks>
    public static class DataTablesContextExtensionsPG
    {
        #region SQL Query Formatters

        // methods to format fragments of a SQL query in the form:
        // SELECT * FROM (BASE-QUERY) WHERE-CLAUSE ORDER-BY OFFSET-FETCH
        // where:
        //  BASE-QUERY is the SQL query to populate a list of data-model-class objects.
        //      the base query is a complete SQL query without ORDER-BY or GROUP-BY declaritives,
        //      and may include one or more JOIN to populate lists of data-model-class objects,
        //      example: SELECT * FROM [table]
        //  WHERE-CLAUSE is the SQL query fragment used to filter results by search string.
        //      example: WHERE [column0] LIKE '%@SearchValue%' OR [column1] LIKE '%@SearchValue%' OR ...
        //  ORDER-BY orders results by one or more columns. example: ORDER BY [column1] DESC
        //  OFFSET-FETCH limits result set returns for a given page. example: OFFSET 20 ROWS
        //      FETCH NEXT 10 ROWS ONLY
        // fragments are used to build three different queries returning:
        //  count of all displayable rows in table
        //  count of filtered (using search item) rows in table
        //  list of (optionally) filtered and ordered data rows for one page in DataTables UI table.

        public static DataTablesContextPG OrderBy(this DataTablesContextPG context)
        {
            try
            {
                context.Query = string.Concat(context.Query, context.Request.Order.Count == 0
                    // when no orderby specified, provide generic ORDER BY
                    // because ORDER BY is required when using OFFSET-FETCH.
                    ? @" ORDER BY 1"
                    : @" ORDER BY " + string.Join(",", context.Request.Order
                        .Select(o => $@"{context.Request.Columns[o.Column].Name}"
                            + (o.Descending ? " DESC" : "")
                        )
                    ));
            }
            catch (Exception ex)
            {
                context.Request.Error += string.Format("{0}: {1}\n{2}",
                    ex.GetType().Name,
                    ex.Message.TrimEnd('.'),
                    ex.StackTrace);
            }
            return context;
        }

        public static DataTablesContextPG SkipTake(this DataTablesContextPG context)
        {
            try
            {
                // OFFSET-FETCH works beautifully to give us a specific page of rows
                // from the full result set, but requires the use of ORDER BY.
                var offset = Math.Max(0, context.Request.Start);
                var fetch = Math.Max(0, context.Request.Length);

                //context.Query = string.Concat(context.Query, $@" OFFSET {offset} ROWS" +
                //    (fetch > 0 ? $" FETCH NEXT {fetch} ROWS ONLY" : string.Empty));

                context.Query = string.Concat(context.Query,
                    $@" LIMIT {fetch} " +
                    $@" OFFSET {offset} ");

            }
            catch (Exception ex)
            {
                context.Request.Error += string.Format("{0}: {1}\n{2}",
                    ex.GetType().Name,
                    ex.Message.TrimEnd('.'),
                    ex.StackTrace);
            }
            return context;
        }

        public static DataTablesContextPG Where(this DataTablesContextPG context)
        {
            try
            {
                // Note: the use of percent wilcard around search value.
                context.Query = string.Concat(context.Query, (string.IsNullOrWhiteSpace(context.Request.Search?.Value))
                    ? " WHERE 1=1"
                    : " WHERE " + string.Join(" OR ", context.Request.Columns
                        .Where(c => c.Searchable)
                        .Select(c => $@"{c.Name} LIKE '%'||@{DataTablesContextPG.SearchToken}||'%'")));
            }
            catch (Exception ex)
            {
                context.Request.Error += string.Format("{0}: {1}\n{2}",
                    ex.GetType().Name,
                    ex.Message.TrimEnd('.'),
                    ex.StackTrace);
            }
            return context;
        }

        #endregion

        #region SQL Query Executors
        // methods to execute generic SQL queries using Dapper

        public static DynamicParameters GetParam(this DataTablesContextPG context)
        {
            var param = new DynamicParameters();
            param.Add(DataTablesContextPG.SearchToken, context.Request.Search?.Value);
            return param;
        }

        /// <summary>
        /// Runs the context COUNT query and return the integer result.
        /// </summary>
        public static async Task<int> CountAsync(this DataTablesContextPG context)
        {
            var query = $@"SELECT COUNT(*) FROM ( {context.Query} ) as cnt ;";
            try
            {
                return await DataTablesContextPG.QuerySingleAsync<int>(query, context.GetParam());
            }
            catch (Exception ex)
            {
                context.Request.Error += string.Format("{0}: {1}\nSqlQuery: {2} using: {3}\n{4}",
                    ex.GetType().Name,
                    ex.Message.TrimEnd('.'),
                    query,
                    Newtonsoft.Json.JsonConvert.SerializeObject(context.GetParam()),
                    ex.StackTrace);
                return 0;
            }
        }

        /// <summary>
        /// Runs the context data query and returns an IEnumerable of one or more TSource records.
        /// </summary>
        public static async Task<IEnumerable<TSource>> DataAsync<TSource>(this DataTablesContextPG context)
        {
            try
            {
                return await DataTablesContextPG.QueryAsync<TSource>(context.Query, context.GetParam());
            }
            catch (Exception ex)
            {
                context.Request.Error += string.Format("{0}: {1}\nSqlQuery: {2} using: {3}\n{4}",
                    ex.GetType().Name,
                    ex.Message.TrimEnd('.'),
                    context.Query,
                    Newtonsoft.Json.JsonConvert.SerializeObject(context.GetParam()),
                    ex.StackTrace);
                return new List<TSource>();
            }
        }

        #endregion
    }

 

最後就是把 DataTablesLogicDapper 類別的 function DataTablesRequestAsync

var totalQuery = new DataTablesContextPG(baseSQLQuery, request);

修改為

var totalQuery = new DataTablesContext(baseSQLQuery, request);

如此一來就搞定了

 

 

 

 

 

 

 

 

 

line notify 開發設定

參考自

http://studyhost.blogspot.com/2016/12/linebot6-botline-notify.html

http://white5168.blogspot.com/2016/12/line-notify-1.html#.XN80T44zbIU

 

設定時網站可以用 localhost 就好

建立一個新的 line 群組

記得要把 line notify 邀請到群組

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>Document</title>
    <script>
        function oAuth2() {
            var URL = 'https://notify-bot.line.me/oauth/authorize?';
            URL += 'response_type=code';
            URL += '&client_id=開發頁面給的';
            URL += '&redirect_uri=http://localhost:5500';
            URL += '&scope=notify';
            URL += '&state=abcde';
            window.location.href = URL;
        }
    </script>
</head>
<body>
    <button onclick="oAuth2();">連結到LineNotify按鈕</button>
</body>
</html>

 

 

彙整不錯的 .net 網站 & 前端網站

https://www.connectionstrings.com/

https://dapper-tutorial.net/dapper

https://redstapler.co/

dotnettutorials

https://openingsource.org/zh-tw/

https://cssfx.dev/

http://brobin.github.io/hacker-bootstrap/

https://hackerthemes.com/bootstrap-cheatsheet/

https://bestofjs.org/

http://www.dotnetawesome.com/

https://www.pexels.com/search/flowers/

https://books.goalkicker.com/?fbclid=IwAR2NMvwZmIZlg0xkLanQqL3rtemnwlDT3T0SMHG7p_IbEwlpebM-HlQ9-po

使用 docfx 產生類別圖及文件說明

產生類別圖

預設若沒安裝類別圖設計則不會有這個功能可以參考這篇步驟安裝

勾選該選項後即可使用三條斜槓進行註解即可產生自己想要說明

下載編譯好的 docfx

https://dotnet.github.io/docfx/

丟到 d:\docfx

cmd 切換目錄到任意資料夾執行以下命令即會生出預設的樣板

D:\docfx\docfx init -q
D:\docfx\docfx docfx_project\docfx.json
D:\docfx\docfx serve docfx_project/_site

確認都 ok 以後使用以下命令,看自己專案位置在哪邊就指到哪

D:\docfx\docfx metadata D:\TestV1\TestV1\TestV1.csproj

成功後會在目前的資料夾 (linux 的 pwd) 生出 _api 資料夾,將內容全部複製到 docfx_project\api 底下即可

接著在執行以下命令一次開啟 http://localhost:8080 就可以完整的看到產生的文件

D:\docfx\docfx docfx_project\docfx.json
D:\docfx\docfx serve docfx_project/_site

 

將網站轉換為 pdf 文件 (效果不好,忘了以前怎麼處理的)

接著安裝 wkhtmltopdf

cd C:\Program Files\wkhtmltopdf\bin
wkhtmltopdf http://localhost:8080/api/TestV1.html D:\d.pdf

 

MADlib安裝過程

#download
#https://askubuntu.com/questions/831292/how-do-i-install-postgresql-9-6-on-any-ubuntu-version

#upgrading
#https://danielepestilli.com/en/blog/posts/3-upgrading-from-postgresql-9-5-to-9-6-on-ubuntu-16-04

#pl/python
#https://stackoverflow.com/questions/43984705/is-there-a-way-to-install-pl-python-after-the-database-has-been-compiled-without

#madlib
#https://cwiki.apache.org/confluence/display/MADLIB/Installation+Guide

#stop postgres
sudo /etc/init.d/postgresql stop

#install 9.6
sudo add-apt-repository "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -sc)-pgdg main"
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql-9.6

#step1
sudo pg_dropcluster --stop 9.6 main
sudo pg_upgradecluster -v 9.6 9.5 main

#step2
sudo pg_dropcluster --stop 9.5 main
sudo apt-get purge postgresql-9.5*
sudo apt-get autoremove

#step3
/etc/init.d/postgresql start

#install pl/python(2.7)
sudo apt-get install postgresql-plpython-9.6

#check pl/python install (option)
SELECT * FROM pg_available_extensions WHERE name like '%plpython%';

#run madlib it's work now!!
/usr/local/madlib/bin/madpack -s madlib -p postgres -c postgres/postgres@localhost:5432/test install

SQL Server Linked Postgres 筆記

參考網站
https://www.mssqltips.com/sqlservertip/3662/sql-server-and-postgresql-linked-server-configuration–part-2/

檢查pg版本
select version();
“PostgreSQL 9.5.10, compiled by Visual C++ build 1800, 64-bit”

下載對應的版本jdbc driver 並安裝
https://www.postgresql.org/ftp/odbc/versions/msi/
本例為 psqlodbc_09_05_0100.zip 對應到 9.5.10

開啟 控制台 -> 系統管理工具 -> ODBC 資料來源 (64 位元) -> 系統資料來源名稱 -> 新增 -> PostgreSQL Unicode(x64)
Data Source : PostgreSQL
Database : test
Server : 127.0.0.1
User Name : postgres
Port : 5432
Password : postgres

開啟 sql server 管理工具
伺服器物件 -> 新增 -> 連結的伺服器
連結的伺服器:隨便寫等等用(PG95)
其他資料來源:Microsoft OLE DB Provider for ODBC Drivers
產品名稱:PostgreSQL(跟ODBC的 Data Source名稱要設定一樣他會去抓這個)
資料來源:PostgreSQL(跟ODBC的 Data Source名稱要設定一樣他會去抓這個)

伺服器選項(其他預設就可以)
RPC:true
RPC輸出:true

搞定後就可以下查詢參數
select 欄位
from [LinkServer名稱].[資料庫].[schema名稱].[資料表名稱]

select msg
from PG95.test.[public].qq

從sql server 插入現有資料到postgres LinkServer
INSERT INTO PG95.test.[public].qq(msg)
SELECT Email
FROM Members

解決 asp.net web api ttfb 很慢

參考自

https://www.itread01.com/article/1530756582.html

https://stackoverflow.com/questions/41458420/long-ttfb-on-simple-get-query-in-web-api

https://stackoverflow.com/questions/36326942/how-to-reduce-wait-time-ttfb-for-mvc-application

https://www.cnblogs.com/CreateMyself/p/5408400.html

https://stackoverflow.com/questions/9594229/accessing-session-using-asp-net-web-api

https://blog.darkthread.net/blog/mvc-slow-magic/

因為燙手的案子是硬用 asp.net + asp.net web api 掛在一起,導致很多莫名其妙的事發生,起初還以為是 sql 查詢的問題,但實際執行起來並沒有到想像中的那麼慢。

於是參考了一些文章並且把 session 進行調整,速度馬上有明顯提升。只是不曉得原本開發的那票人是否有用 session 在 code 裡就是,有的話就尷尬了。

解法就是在 Global.asax 這支程式內加上以下 code 性能會有飛快的提升

public override void Init()
    {
        PostAuthenticateRequest += MvcApplication_PostAuthenticateRequest;
        base.Init();
    }

    void MvcApplication_PostAuthenticateRequest(object sender, EventArgs e)
    {
        //HttpContext.Current.SetSessionStateBehavior(
        //  SessionStateBehavior.Required);

        System.Web.HttpContext.Current.SetSessionStateBehavior(SessionStateBehavior.ReadOnly);
    }

asp.net web api recaptcha v3

花了一陣子又搞不太起來,結果反而錯誤是出在 asp.net web api 一直無法接收到參數身上。

關於這個雷我已經踩很多次了,但每次趕時間就又忘了紀錄,當 web api 接收 1 個參數時要加上 [FromBody],前端的jquery 呼叫 ajax 時參數必須寫成 data: JSON.stringify(token), 而 contentType: “application/json” 這樣才可以正確的讓 web api 接收到參數。

參考連結

https://chengjhe.blogspot.com/2018/11/how-to-use-recaptcha-v3.html

https://dotblogs.com.tw/mantou1201/2013/05/29/105059

後端 code

[HttpPost]
public IHttpActionResult VerifyBot([FromBody]string token)
{
	using (var client = new HttpClient())
	{
		client.DefaultRequestHeaders.Accept.Clear();
		client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
		string url = string.Format(
			"https://www.google.com/recaptcha/api/siteverify?secret={0}&response={1}",
			"你的後端金鑰",
			token);
		HttpResponseMessage response = client.GetAsync(url).Result;

		string result = response.Content.ReadAsStringAsync().Result;

		if (response.IsSuccessStatusCode)
		{
			var reCAPTCHA = JsonConvert.DeserializeObject<ReCAPTCHA>(result);

			if (reCAPTCHA.score > 0.5)
			{
				return Ok(reCAPTCHA);
			}
		}

		return Ok("Verify Bot Fail");
	}
	
}

新增類別

public class ReCAPTCHA
    {
        public bool success { get; set; }
        public double score { get; set; }
        public string action { get; set; }
        public DateTime challenge_ts { get; set; }
        public string hostname { get; set; }
    }

前端 code

grecaptcha.ready(function () {

	//對 google發送取得 token
	grecaptcha.execute('前端金鑰', {
		action: '/api/VerifyBot'
	}).then((token) => {
		//跟自己的 server 後端組合 token
		$.ajax({
			type: 'POST',
			url: '/api/VerifyBot',
			data: JSON.stringify(token),
			contentType: "application/json",
			success: (res) => {
				console.log('res:');
				console.log(res);
			}
		});
	});
});