Hao's learning log

   將資料表索引進行重建(REBUILD & REORGANIZE)   DECLARE @dbName NVARCHAR(128) SET @dbName = DB_NAME() DECLARE @schemaName NVARCHAR(128) DECLARE @tableName NVARCHAR(128) DECLARE @indexName NVARCHAR(128) DECLARE @indexId INT DECLARE @fragPercent FLOAT DECLARE @sql NVARCHAR(MAX) -- 迴圈檢查每個索引的碎片程度 DECLARE indexCursor CURSOR FOR SELECT s.name AS schemaName, t.name AS tableName, i.name AS indexName, i.index_id AS indexId, ips.avg_fragmentation_in_percent AS fragPercent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips INNER JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id INNER JOIN sys.tables t ON i.object_id = t.object_id INNER JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE ips.avg_fragmentation_in_percent > 10 -- 篩選需要維護的索引 AND i.type IN (1, 2) -- 只處理 Clustered 和 Non-Clustered 索引 AND i.is_disabled = 0 -- 忽略已停用的索引 ORDER BY tableName OPEN indexCursor FETCH NEXT FROM indexCursor INTO @schemaName, @tableName...

  ZebraDesigner 擴充字體步驟 安裝 Zebra Setup Utilities 安裝過程皆下一步即可 下載印表機字體 字體下載完後，編輯器上面就能選到新的字體， 且使用列印至檔案所產出的.prn就不會亂碼。 參考網址: https://blog.csdn.net/lixiaojie15523694746/article/details/103991751 By Hao★

  查詢所有Procedure   --MS SQL使用 SELECT name AS ProcedureName, create_date AS 建立時間, modify_date AS 異動時間 FROM sys.procedures WHERE is_ms_shipped = 0 -- 只顯示使用者自訂的預存程序 ORDER BY ProcedureName; --通用版本(尚未測試) SELECT ROUTINE_TYPE, ROUTINE_SCHEMA AS SchemaName, ROUTINE_NAME AS ProcedureName, CREATED AS 建立時間, LAST_ALTERED AS 最後異動時間 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' -- 只顯示預存程序 ORDER BY ROUTINE_NAME; 參考網址:  By Hao★

  Procedure最後執行時間   SELECT OBJECT_NAME(object_id) AS ProcedureName, last_execution_time AS 最後執行時間 FROM sys.dm_exec_procedure_stats WHERE OBJECT_NAME(object_id) <> '' ORDER BY ProcedureName; 參考網址:  By Hao★

查詢資料表使用紀錄   SELECT OBJECT_NAME(OBJECT_ID) AS TableName, last_user_update AS 最後更新時間, last_user_seek AS 最後搜尋時間, last_user_scan AS 最後掃描時間 FROM sys.dm_db_index_usage_stats WHERE database_id = DB_ID()           -- 當前數據庫 AND OBJECT_ID = OBJECT_ID('TableName') -- 指定資料表名稱 ORDER BY TableName; 參考網址:  By Hao★

UI介面多國語系切換功能   Imports System.ComponentModel Imports System.Globalization Imports System.Resources 'UI介面語系轉換 Public Class C_UILanguage Private systemResourceManager As ResourceManager Private errorResourceManager As ResourceManager Dim CRM As System.ComponentModel.ComponentResourceManager Public Sub New() systemResourceManager = New ResourceManager("專案名稱.Resources", GetType(C_UILanguage).Assembly) errorResourceManager = New ResourceManager("專案名稱.Resources.ErrorMessages", GetType(C_UILanguage).Assembly) End Sub Public Sub _Main(ByVal _Form As Windows.Forms.Form) '檢查引數 If _Form Is Nothing Then _Form.Text = "Form" Throw New ArgumentNullException(_Form.Text) End If CRM = Nothing '切換介面顯示的語言 Try CRM = New ComponentResourceManager(_Form.GetType) ' 設置表單的標題 CRM.ApplyResources(_Form, "$this...

匯出 DataGridView 資料到 Excel   Imports System.Runtime.InteropServices Private Sub btnExport_Click(sender As Object, e As EventArgs) Handles btnExport.Click ' 檢查DataGridView是否有數據 If dgv1.Rows.Count = 0 Then MessageBox.Show("沒有資料可以匯出", "訊息", MessageBoxButtons.OK, MessageBoxIcon.Hand) Return End If ' 檢查是否安裝Excel If Not IsExcelInstalled() Then MessageBox.Show("這台電腦沒有安裝 Office Excel，無法繼續執行！", "訊息", MessageBoxButtons.OK, MessageBoxIcon.Hand) Return End If Try ' 開始匯出資料到 Excel ExportDataGridViewToExcel(dgv1) Catch ex As Exception MessageBox.Show(ex.ToString, "匯出 Excel 發生錯誤", MessageBoxButtons.OK, MessageBoxIcon.Error) End Try End Sub ' 檢查是否安裝Excel Private Function IsExcelInstalled() As Boolean Try Dim excelApp As New Excel.Application() If excelApp IsNot Nothing Then excelApp.Quit() Return True ...

PIVOT使用   --【簡易版】 SELECT * FROM ( SELECT l.MasterLangId, l.LangType, l.ShowText FROM dbo.Lang l ) t PIVOT ( -- 設定彙總欄位及方式 MAX(ShowText) -- 設定轉置欄位，並指定轉置欄位中需彙總的條件值作為新欄位 FOR LangType IN ([zh-TW], [zh-CN], [en-US]) ) p; --【動態生成欄位】 -- 使用 FOR XML PATH 動態生成轉置列清單 DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX); SELECT @cols = STUFF(( SELECT ',' + QUOTENAME(LangType) FROM ( SELECT DISTINCT LangType FROM dbo.Lang ) AS Sub ORDER BY CASE WHEN LangType = 'zh-TW' THEN 1 WHEN LangType = 'zh-CN' THEN 2 ELSE 3 END, LangType FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)'), 1, 1, ''); -- 動態 PIVOT 查詢 SET @query = ' SELECT * FROM ( SELECT l.MasterLangId, l.LangType, l.ShowText FROM dbo.Lang l ) t PIVOT ( MAX(ShowText) FOR LangType IN (' + @cols + ') ) p; '; -- 執行動態 SQL EXEC sp_executesql @query; 參考網址:  https://dotb...

sp_send_dbmail使用   EXEC msdb.dbo.sp_send_dbmail @profile_name='MES_MAIL', @recipients='eMail Address', @blind_copy_recipients='eMail Address', @subject = '主旨', @body = '內容', @body_format = 'HTML'; SELECT * FROM msdb.dbo.sysmail_allitems --查詢寄送的郵件記錄 參考網址:  https://vito-note.blogspot.com/2013/06/database-mail.html https://ithelp.ithome.com.tw/articles/10198042?sc=pt https://learn.microsoft.com/zh-tw/sql/relational-databases/system-stored-procedures/sp-send-dbmail-transact-sql?view=sql-server-ver16 https://blog.csdn.net/Laaguid/article/details/89671396 By Hao★

查詢StoredProcedure中的Key Word   SELECT name AS StoredProcedureName, OBJECT_DEFINITION(OBJECT_ID(name)) AS ProcedureDefinition FROM sys.procedures WHERE upper(OBJECT_DEFINITION(OBJECT_ID(name))) LIKE '%KEY_WORD%' ORDER BY name; 參考網址:  By Hao★

取得Guid   using System; Console.WriteLine(Guid.NewGuid()); 參考網址:  https://learn.microsoft.com/zh-tw/dotnet/api/system.guid.newguid?view=net-7.0 https://blog.csdn.net/WuLex/article/details/109571681 By Hao★

  Stream.CopyTo 方法使用   //創建新的stream MemoryStream destination = new MemoryStream(); using (FileStream source = new FileStream("C:\temp\Machine.jpg", FileMode.Open)) { //將來源source,複製到目的地destination. source.CopyTo(destination); } 參考網址:  https://learn.microsoft.com/zh-tw/dotnet/api/system.io.stream.copyto?view=net-7.0 錯誤案例: //創建新的stream MemoryStream destination = new MemoryStream(); //來源端stream FileStream source = new FileStream("C:\temp\Machine.jpg", FileMode.Open, FileAccess.Read); destination.CopyTo(source); //錯誤訊息System.NotSupportedException: 'Stream does not support writing.' //原因:因為source是唯讀的，且來源端與目的地位置應交換 參考網址:  https://learn.microsoft.com/zh-tw/dotnet/api/system.notsupportedexception?view=net-7.0 By Hao★

  至 官網 下載版本13.4並執行安裝程式 將安裝目錄改成 D:\PostgreSQL\13 取消 Stack Builder 資料安裝預設目錄D:\PostgreSQL\13\data 設定資料庫管理員帳號 postgres 的密碼 預設Port為5432    資料庫安裝中 安裝完成  使用GUI工具PgAdmin連線到資料庫 開始選單 -> PostgreSQL 13 -> 點選 pgAdmin4 首次執行需設定一組pgAdmin的新密碼 (非資料庫管理員帳號 postgres 的密碼) 點選 Servers 輸入資料庫管理員帳號 postgres 的密碼，點選OK 連線成功 參考網址: PostgreSQL - 安裝教學 By Hao★

使用Gmail發送信件   using System; using System.Collections.Generic; public void SendMail(string Subject, string Body, IEnumerable ToUser) { string account = "XXX@gmail.com";//登入Mail Server的帳號 string password = "*******";//登入Mail Server的密碼 string from = "XXX@gmail.com";//實際發送信件的Mail地址 string host = "smtp.gmail.com";//發Mail的Server位置 string port = "587";//發Mail的Server位置使用的Port bool enableSsl = true;//是否啟用SSL憑證 System.Net.Mail.MailMessage MyMail = new System.Net.Mail.MailMessage(); MyMail.From = new System.Net.Mail.MailAddress(from); foreach (var i in ToUser) { MyMail.To.Add(i.ToString());//設定收件者Email } MyMail.Subject = Subject; MyMail.Body = Body; //設定信件內容 MyMail.IsBodyHtml = true; //是否使用html格式 System.Net.Mail.SmtpClient MySMTP = new System.Net.Mail.SmtpClient(host, Int32.Parse(port)); MySMTP.Credentials = new System.Net.NetworkCredential(account, password); MySMTP...

將DataTable轉換成Html格式表格   using System.Data; using System.Text; protected string ExportDatatableToHtml(DataTable dt) { StringBuilder strHTMLBuilder = new StringBuilder(); strHTMLBuilder.Append("<table border='2px' cellpadding='5'>"); strHTMLBuilder.Append("<tr>"); foreach (DataColumn myColumn in dt.Columns) { strHTMLBuilder.Append("<td>"); strHTMLBuilder.Append(myColumn.ColumnName); strHTMLBuilder.Append("</td>"); } strHTMLBuilder.Append("</tr>"); foreach (DataRow myRow in dt.Rows) { strHTMLBuilder.Append("<tr>"); foreach (DataColumn myColumn in dt.Columns) { strHTMLBuilder.Append("<td>"); strHTMLBuilder.Append(myRow[myColumn.ColumnName].ToString()); strHTMLBuilder.Append("</td>"); } strHTMLBuilder.Append...

將DataTable行列轉置   using System.Data; public DataTable RevertRowToColumn(DataTable dtSource) { DataTable newDataTale = new DataTable(); //原本DataTable列數 int dtRowsCount = dtSource.Rows.Count; //原本DataTable欄數 int dtColumnCount = dtSource.Columns.Count; //將原本DataTable的第一個欄位放入轉置後的第一欄 newDataTale.Columns.Add(dtSource.Columns[0].ToString(), typeof(string)); //將原本DataTable的第一列轉置為轉置後的欄位 for (int i = 0; i < dtRowsCount; i++) { newDataTale.Columns.Add(dtSource.Rows[i][0].ToString(), typeof(string)); } //將原本DataTable的欄位列依序轉置為轉置後的每一列 //起始為1，因為原本第一列已經放置為欄位名稱，要從第二列開始跑起 for (int s = 1; s < dtColumnCount; s++) { DataRow dr = newDataTale.NewRow(); dr[0] = dtSource.Columns[s].ToString(); for (int i = 0; i < dtRowsCount; i++) { dr[i + 1] = dtSource.Rows[i][s].ToString(); } newDataTale.Rows.Add(dr); } newDataTale.TableName = "NEW_" + dtSource.TableNa...

用 FileZilla Server v0.9.60 架設 FTP 站 1.執行FileZilla_Server-0_9_60.exe安裝檔 參考 https://briian.com/4618/3/

使用情境: 因新/舊版本的Windows Server差距太大，沒辦法使用介面來操作排程匯出/匯入，所以使用 Command Line 下指令的方式來匯出/匯入 Windows 排程。 CMD 1.輸出排程 schtasks /Query /S SERVER /XML /TN 排程名稱 > 排程輸出存放路徑\排程名稱.xml 2.輸入排程 schtasks /Create /XML 排程輸出存放路徑\排程名稱.xml /TN 排程名稱 /RU 使用者帳號 /RP 使用者密碼 PowerShell 1.輸出排程 Export-ScheduledTask -TaskName 排程名稱 -TaskPath 排程路徑 | out-file 要存放路徑\檔案名稱.xml 2.輸入排程 Register-ScheduledTask -xml (Get-Content 'XML檔的位置' | Out-String) -TaskName 排程名稱 -TaskPath 排程路徑 -User 網域\使用者 –Force 參考網址:  By Hao★

1.選擇一整列 DataGridView  →  屬性 → SelectionMode → FullRowSelect  2.只能單選 DataGridView  →  屬性 → MultiSelect → False  3.禁止排序功能 For i As Integer = 0 To DataGridView1.ColumnCount-1      DataGridView1.Columns(i).SortMode = DataGridViewColumnSortMode.NotSortable Next 參考網址:  By Hao★

   參考網址: msgbox(彈出式訊息方塊)的用法 By Hao★

  1.段行:_(底線)   Dim FileNotExistMsg As String = "File Not Exist, Please Check !!" Dim FileNotExistMsg As String = _ "File Not Exist, Please Check !!" 2.換行:vbCrLf   Dim statusFileCheck = "FileStatus: " + vbCrLf + FileNotExistMsg 3.註解:'(單引號)   '註解 參考網址:  By Hao★

1.獲取系統版本號   Version version = System.Reflection.Assembly.GetExecutingAssembly().GetName().Version; 2.格式化   string ver = string.Format("{0}.{1}.{2}.{3}", version.Major, version.Minor, version.Build, version.Revision.ToString("0000")); 2.不做另外處理,直接使用   string ver = System.Reflection.Assembly.GetExecutingAssembly().GetName().Version.ToString() 參考網址:  By Hao★

下載指定目錄下的文件   protected void DownloadFile( string filename, string filepath) { Response.Clear(); Response.AddHeader( "Content-Disposition" , "attachment; filename=" + HttpUtility .UrlEncode(filename, System.Text.Encoding .Default)); Response.ContentType = "application/octet-stream" ; Response.TransmitFile(filepath); Response.End(); } 參考網址:  By Hao★

查詢包含特定副檔名或是檔案中含有某關鍵字   using System.Text.RegularExpressions; using System.IO; private void FindFile(string dir) { DirectoryInfo Dir = new DirectoryInfo(dir); try { string filepath = ""; foreach (FileInfo file in Dir.GetFiles("*.xls"))//查詢附檔名為xls的文件 { Regex regex = new Regex(@"friend");//查詢檔案名稱中有關鍵字friend的文件 Match match = regex.Match(file.ToString()); if (match.Success) { filepath = Dir + file.ToString()); } } } catch (Exception ex) { Console.WriteLine(ex.Message); } } 參考網址:  By Hao★

隐藏列   GridPanel_1.ColumnModel.SetHidden(1, true); 參考網址:  By Hao★

1.判斷GridPanel中是否選中了某列   if(!GridPanel1.hasSelection()) { Ext.Msg.alert("提示", "請選擇資料!"); return; } 2.獲取選中行的數據   var record = GridPanel1.selModel.getSelected(); var ID = record.data.ID; ID為GridPanel1的Store的欄位名稱。 3.獲取GridPanel中所有的數據   var data = GridPanel1.getStore().getRange(); 可以將得到的所有數據轉換成Josn傳给後台： var Datas = []; var data = GridPanel1.getStore().getRange(); Ext.each(data, function(record) { Datas.push(record.data); }); var jsonData = Ext.encode(Datas); return jsonData; 參考網址:  Ext.Net 使用总结之GridPanel中的选中行   By Hao★

前台   <ext:CompositeField ID="CompositeField2" runat="server" FieldLabel="時間"> <Items> <ext:DateField ID="date_start" Vtype="daterange" runat="server" Flex="1"> <Listeners> <Render Handler="this.endDateField = '#{date_end}'" /> </Listeners> </ext:DateField> <ext:Label ID="Label2" runat="server" Text="~"> </ext:Label> <ext:DateField ID="date_end" Vtype="daterange" runat="server" Flex="1"> <Listeners> <Render Handler="this.startDateField = '#{date_start}'" /> </Listeners> </ext:DateField> </Items> </ext:CompositeField> 參考網址: Ext.Net 使用总结之查询条件中的起始日期   By Hao★

  查詢20分鐘前的資料 SELECT * FROM TableName AS OF TIMESTAMP SYSDATE - 20/1440; 查詢指定時間前的資料 SELECT * FROM TableName AS OF TIMESTAMP TO_TIMESTAMP('2020-01-01 12:10:10','YYYY-MM-DD HH24:MI:SS'); 查到後就可以把該紀錄插入表中 INSERT INTO TableName SELECT * FROM TableName AS OF TIMESTAMP SYSDATE - 20/1440 WHERE ID = '123' COMMIT; ps.1440代表一天有1440分鐘 By Hao★

前台   <script type="text/javascript"> var prepare = function(grid, toolbar, rowIndex, record) { var EditButton = toolbar.items.get(0); var DetailButton = toolbar.items.get(1); var SendButton = toolbar.items.get(2); var CheckButton = toolbar.items.get(3); var RejectButton = toolbar.items.get(4); var InactiveButton = toolbar.items.get(5); var PDFButton = toolbar.items.get(6); if (record.data.STATUS == "S" || record.data.STATUS == null || record.data.STATUS == "") { CheckButton.setDisabled(true); RejectButton.setDisabled(true); } if (record.data.CHECK_FLAG == "C") { EditButton.setDisabled(true); SendButton.setDisabled(true); CheckButton.setDisabled(true); RejectButton.setDisabled(true); } if (record.data.INACTIVE_FLAG == "I") { ...

前台   <ext:FileUploadField ID="FileUploadField" runat="server" FieldLabel="上傳附件" LabelAlign="Right" ButtonText="瀏覽" Icon="Attach" /> <ext:Button ID="Upload" runat="server" Text="上傳" Icon="EjectGreen"> <DirectEvents> <Click OnEvent="btnUploadFile"> </Click> </DirectEvents> </ext:Button> 後台C#   protected void btnUploadFile(object sender, DirectEventArgs e) { if (this.FileUploadField.HasFile) { string FileName = Path.GetFileName(this.FileUploadField.PostedFile.FileName); string newName = DateTime.Now.ToString(@"yyyyMMddHHmmss") + "_" + FileName; string savePath = Page.Server.MapPath("`/UploadFile/" + newName); if(!Directory.Exists(Path.GetDirectoryName(savePath))) { Directory.CreateDirectory(Path.GetDirectoryName(savePath));...

後台C#   protected void btnCancel_Click(object sender, DirectEventArgs e) { X.Msg.Confirm("提示", "删除將無法恢復是否確認刪除?", new MessageBoxButtonsConfig { Yes = new MessageBoxButtonConfig {      Handler = "Ext.net.DirectMethods.Yes()", Text = "確認" }, No = new MessageBoxButtonConfig {      Handler = "Ext.net.DirectMethods.No()", Text = "取消" } }).Show(); } [DirectMethod] public void Yes() {      RowSelectionModel sm = this.gpGridPanelID.SelectionModel.Primary as RowSelectionModel; if(sm.SelectedRows.Count != 0) { string tempRowData = ""; foreach(SelectedRow row in sm.SelectedRows) {      tempRowData = tempRowData + "," + row.RecordID ; } } } 前台   <ext:Store ID="StoreID" runat="server"> <Reader> <ext:JsonReader IDProperty=...

  Ext.grid.GridPanel提供的工具函數, 它會根據輸入的日期格式返回一個renderer函數, 之后Ext.grid.ColumnModel會利用這個renderer函數格式化表格中的日期or其他 <Renderer Fn="Ext.util.Format.dateRenderer('Y/m/d')" /> <Renderer Fn="Ext.util.Format.dateRenderer('Y/m/d H:i:s')" /> <Renderer Fn="Ext.util.Format.numberRenderer('0.000')" /> 參考網址:  Ext.util.Format腳本娃娃   forums.ext.net By Hao★

  using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Web; using Spire.Xls; namespace ToPDF { class Program { static void Main(string[] args) { string tempPath = HttpRuntime.AppDomainAppPath.ToString() + "\\Report\\"; Workbook workbook = new Workbook(); workbook.LoadFromFile(tempPath + "Sample.xlsx", ExcelVersion.Version2010); for(int i = 0; i < workbook.Worksheets.Count; i++) { workbook.Worksheets[i].PageSetup.Orientation = PageOrientationType.Landscape; workbook.Worksheets[i].PageSetup.IsFitToPage = true; } workbook.SaveToFile(tempPath + "result.pdf", Spire.Xls.FileFormat.PDF); } } } 參考網址 By Hao★

ASP中用Button傳遞兩個職給後台的方法： 1、在CommandArgument中用逗號分隔要傳送到後台的兩個參數： <asp:GridView ID="GridViewUserScraps" ItemStyle-VerticalAlign="Top"  AutoGenerateColumns="False" Width="100%" runat="server"  OnRowCommand="GridViews_RowCommand" > <Columns> <asp:TemplateField SortExpression="SendDate"> <ItemTemplate> <asp:Button ID="btnPost" CssClass="submitButton" Text="Comment"  runat="server" CommandName="Comment"  CommandArgument='<%#Eval("ID")+","+ Eval("UserID")%>' /> </ItemTemplate> </asp:TemplateField> </Columns> </asp:GridView> p.s. 也可以用這種 CommandArgument='<%# DataBinder.Eval(Container.DataItem,"ID") + "," +  DataBinder.Eval(Container.DataItem,"UserID")%>'  2、C#後台接收到button傳遞的參數後的處理方法： protected void GridView...

在10.12及以下版本，都有內建telnet命令，但在10.13以後，都已经取消了。 Last login: Sun Aug 11 11:44:10 on ttys000 HaoKi$ telnet -bash: telnet: command not found 打開终端機 安裝Homebrew -> /usr/bin/ruby -e “$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)” 再輸入密碼 指令在這裡-> https://brew.sh/index_zh-tw.html HaoKi$ /usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)" ==> This script will install: /usr/local/bin/brew /usr/local/share/doc/homebrew /usr/local/share/man/man1/brew.1 /usr/local/share/zsh/site-functions/_brew /usr/local/etc/bash_completion.d/brew /usr/local/Homebrew ==> The following existing directories will be made group writable: /usr/local/share/man/man3 /usr/local/share/man/man5 /usr/local/share/man/man7 ==> The following existing directories will have their owner set to HaoKi: /usr/local/share/man/man3 /usr/local/share/man/man5 /usr/local/share/man/man7 ==> The following existing directories will have ...