MS SQL INDEX LIST 取得現有 MS SQL 的資料表的index清單

'取得現有 MS SQL 的資料表的index清單

SELECT TBL.NAME AS TABLE_NAME,
        I.NAME AS INDEX_NAME,
        I.INDEX_ID,
        (CASE IC.IS_INCLUDED_COLUMN
            WHEN 1 THEN IC.INDEX_COLUMN_ID
            ELSE IC.KEY_ORDINAL
         END)
           AS [ID],
        CLMNS.NAME AS COLUMN_NAME,
        CLMNS.COLUMN_ID AS COLUMN_POSITION,
        IC.IS_DESCENDING_KEY AS DESCEND , I.IS_UNIQUE
   FROM          SYS.TABLES AS TBL
              INNER JOIN
                 SYS.INDEXES AS I
              ON (I.OBJECT_ID = TBL.OBJECT_ID)
           INNER JOIN
              SYS.INDEX_COLUMNS AS IC
           ON (IC.COLUMN_ID > 0
               AND (   IC.KEY_ORDINAL > 0
                    OR IC.PARTITION_ORDINAL = 0
                    OR IC.IS_INCLUDED_COLUMN != 0))
              AND (IC.INDEX_ID = CAST (I.INDEX_ID AS INT)
                   AND IC.OBJECT_ID = I.OBJECT_ID)
        INNER JOIN
           SYS.COLUMNS AS CLMNS
        ON CLMNS.OBJECT_ID = IC.OBJECT_ID AND CLMNS.COLUMN_ID = IC.COLUMN_ID
  WHERE 1 = 1
 --(I.NAME='???')
 --AND (TBL.NAME=???'  )
 ORDER BY TBL.NAME , INDEX_ID ASC , [ID]

crystal report 浮水印(watermark) 簽章(STAMP)

crystal report 浮水印(watermark) 簽章(STAMP)

準備兩個當浮水印圖檔
A.confidential.wmf 背景透通
B.confidential.jpg 沒有透通

'步驟
1.在頁首a放入這兩張圖,頁首a的區段專家"放置在隨後區段"勾選起來
2.將頁首a高度拉大,圖檔移至頁首a的最下方(這樣浮水印就會出現在頁面靠中間的位置))


如果報表中沒有線條這兩種圖檔呈現出的結果是一樣的
但是如果報表中有線條

3.在細目中插入藍色線條
4.在細目中插入文字物件,但是這個文字物件不放入任何文字,且將它拉的和藍線一樣長
   並將文字物件的"框線"-"線條樣式"-"上"設定成單線,將"色彩"設定為綠色

設計畫面 左圖為A 右圖為B

預覽畫面

此時預覽結果發現 兩張圖都被綠線壓過 , A圖以透通的方式顯示藍線,
B圖藍線完全被蓋掉


結論
以浮水印的需求 (浮水印應該是在最下層)
A/B圖檔都可以但需使用非一般的線條,要用文字物件的框線也就是綠線


以簽章(STAMP)的需求,例如收據章 (圖案應該是在最上層)
若有壓到"方塊"或"線條",則只有A圖檔可以

收據章範例

crystal report 動態產生欄位 -FieldObject

下載 sample pdf

Imports CrystalDecisions.Shared
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.ReportAppServer


'crystal report  動態產生欄位 自動產生你要的rpt
        '1.準備一個空的報表樣本() : standard.rpt  資料庫欄位必須是空的
        '2.準備一個在應用程式中使用資料的 DataSet : DataSet.xsd (這裡採用Push Model)
        '3 要使用的TABLE  NAME :MYTABLE   MYTABLE SCHEMA需定義在 DataSet.xsd裡喔
        '  MYTABLE 共有五個COLUMN : COLUMN1 COLUMN2 ....等會要跑LOOP自動加入報表裡


        Dim tablename As String = "MYTABLE"
        Dim crReport As ReportDocument = New CrystalDecisions.CrystalReports.Engine.ReportDocument
        '載入報表樣本
        Dim Path As String = Request.ServerVariables("APPL_PHYSICAL_PATH")
        crReport.Load(Path & "rpt\standard.rpt")


        '利用ClientDoc來變更REPORT內容
        Dim rasClientDocument As ClientDoc.ISCDReportClientDocument
        rasClientDocument = crReport.ReportClientDocument



        'A設定資料表來源位置
        Dim crLogonInfo As New DataDefModel.PropertyBagClass()
        crLogonInfo.Add("File Path ", "c:\mydir\dataset.xsd")
        Dim connProps As New DataDefModel.PropertyBagClass()
        connProps.Add("Database DLL", "crdb_adoplus.dll")
        connProps.Add("QE_DatabaseType", "ADO.NET (XML)")
        connProps.Add("QE_LogonProperties", crLogonInfo)



        '指定連線方式
        Dim connInfo As New DataDefModel.ConnectionInfoClass
        connInfo.Attributes = connProps
        connInfo.Kind = DataDefModel.CrConnectionInfoKindEnum.crConnectionInfoKindCRQE



        '加入table (必須存在A指定的dataset.xsd中)
        Dim table As DataDefModel.Table = New DataDefModel.TableClass
        table.Alias = tablename
        table.Name = tablename
        table.ConnectionInfo = connInfo
        rasClientDocument.DatabaseController.AddTable(table)


        '在報表的細目(detail)加入資料欄位
        '找到細目區段
        Dim DetailSection As ReportDefModel.Section = rasClientDocument.ReportDefinition.DetailArea.Sections(0)
        Dim fieldIndex As Integer
        Dim tableIndex As Integer
        Dim dField As DataDefModel.Field
        Dim dFieldObject As ReportDefModel.FieldObject
        Dim dTable As DataDefModel.Table


        '欄位的字型顏色定義 非必需   standard.rpt中已有預設的字型(由檔案->選項->字型可以查看)
        Dim dFontColor As ReportDefModel.FontColor
        Dim dFont As ReportDefModel.Font
        dFont = New ReportDefModel.FontClass()
        dFont.Size = 11
        dFont.Name = "標楷體"
        dFontColor = New ReportDefModel.FontColorClass()
        dFontColor.Font = dFont


        Dim begpx As Integer = 200 '第一個資料欄位在細目中的位置
        For j As Integer = 1 To 5
            tableIndex = rasClientDocument.Database.Tables.FindByAlias(tablename)
            Dim temp As DataDefModel.ISCRTable = rasClientDocument.Database.Tables(tableIndex)
            dTable = CType(temp, DataDefModel.Table)
            fieldIndex = dTable.DataFields.Find(dTable.DataFields(j - 1).Name, DataDefModel.CrFieldDisplayNameTypeEnum.crFieldDisplayNameName, DataDefModel.CeLocale.ceLocaleUserDefault)
            dField = CType(dTable.DataFields(fieldIndex), DataDefModel.Field)
            dFieldObject = New ReportDefModel.FieldObjectClass()
            dFieldObject.Kind = ReportDefModel.CrReportObjectKindEnum.crReportObjectKindField
            dFieldObject.FieldValueType = dField.Type
            dFieldObject.DataSource = dField.FormulaForm
            dFieldObject.FontColor = dFontColor
            dFieldObject.Height = 211
            dFieldObject.Top = 85
            dFieldObject.Left = begpx
            dFieldObject.Width = 300
            begpx += j * 350
            rasClientDocument.ReportDefController.ReportObjectController.Add(dFieldObject, DetailSection, 0)
        Next


        '產生新報表 做為其他用途
        crReport.SaveAs(Path & "myreport.rpt")


        '當然也可以不用產生新報表 可以直接 ASSIGN DATA SOURCE 產生REPORT的結果
        crReport.SetDataSource(mydata)
        crReport.ExportToHttpResponse(ExportFormatType.PortableDocFormat, Response, True, "Report")

MS SQL TABLE LIST 取得現有 MS SQL 的資料表清單/欄位清單

'取得現有 MS SQL 的資料表清單 (含擴充屬性的描述)

SELECT o.name AS table_name,  ep.value  AS 說明 

  FROM    sys.objects O

       LEFT JOIN

          sys.extended_properties AS ep

       ON o.object_id = ep.major_id

 WHERE     1 = 1

       AND ISNULL (minor_id, 0) = 0

       AND O.name NOT LIKE 'sys%'

       AND O.type = 'U'

ORDER BY O.name

'取得現有 MS SQL 的資料表欄位清單

SELECT b.colorder,

       a.name AS table_name,

       b.name AS FNAME,

       (SELECT convert (VARCHAR, value)

          FROM ::

                  fn_listextendedproperty (NULL,

                                           'user',

                                           'dbo',

                                           'table',

                                           a.name,

                                           'column',

                                           b.name

               ))

          AS DESCRIPT,

       b.xtype,

       (SELECT    TOP 1

               c.name

          FROM systypes c

         WHERE c.xtype = b.xtype)

          AS ctype,

       b.length,

       b.isnullable,

       ''

  FROM sysobjects a, syscolumns b

 WHERE a.id = b.id AND A.type = 'U'

ORDER BY a.name, b.colorder

regex

'移除HTML TAG
Regex.Replace(String, "<.*?>", "")
'移除 (.)
Regex.Replace(String.Split("-")(1), "\((.+)\)", "") 
'連續3個以上4
Regex.IsMatch((String, "4{3,}")

'身分證字號格式
Regex.IsMatch(idno, "^([a-zA-Z]+\d+|\d+[a-zA-Z]+)[a-zA-Z0-9]*$")


Regex("\(([\u4e00-\u9fa5]+)\)")
Regex.IsMatch(String, "NAME|DESC|RMK|ORIGIN|CONTENT", RegexOptions.IgnoreCase)

ASP.NET GRIDVIEW

<asp:TemplateField HeaderText="head"  HeaderStyle-Wrap="False">
                            <ItemTemplate>
                             <asp:LinkButton id="field2" runat="server" cssclass="pathLink" Text='<%# Eval("field2") %>'  CommandName="commA" CausesValidation="False" CommandArgument='<%# Eval("field1", "field1={0}") + "&" + Eval("field2", "field2={0}")  %>' ></asp:LinkButton>
                            </ItemTemplate>
</asp:TemplateField>




 <asp:TemplateField HeaderText="標題" HeaderStyle-Wrap="false">
        <ItemTemplate>
           <%#Eval("field1").insert(3, ".")%>  <%#LEFT(Eval("field1"),3) %>
          <asp:Label ID="Hyphen" runat="server" Text="-" Visible='<%# Eval("field2") = "01" %>' >
        </ItemTemplate>
    </asp:TemplateField>