SQL 2008 資料加密

SQL 2008 資料加密
以下範例建立一個具有儲存加密，讀取解密的資料表

參考網址：
http://dotnetframework.blogspot.tw/2011/01/blog-post.html
http://www.dotblogs.com.tw/dc690216/archive/2009/09/10/10559.aspx


1.建立非對稱金鑰。指定name=mykey, pwd=p@ssw0rd

CREATE ASYMMETRIC KEY mykey
    WITH ALGORITHM = RSA_2048
    ENCRYPTION BY PASSWORD = 'p@ssw0rd';
GO

2.查詢第1步驟所建立的key是否成功。

select * from sys.asymmetric_keys

3.建立測試資料表

4.建立新增、更新、刪除資料表的預存程序。

USE [EIP_new]
GO
 
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE PROCEDURE [dbo].[sp_BPM_Modify_t_BPM_Seal_type_E]
 
 @action_type    NCHAR(1) = '' ,
 @id    INT = 0,
 @username        NVARCHAR(50) = '',
 @uid   NVARCHAR(10) = '',
 @pwd   NVARCHAR(30) = '',
 @sort   INT = 0,
 @cdt   DateTime
AS
BEGIN
 
 SET NOCOUNT ON;
 
    --傳入資料檢核
 IF (@action_type NOT IN ('A','D','U'))
 BEGIN
  DECLARE @DBID INT;
  SET @DBID = DB_ID();
  DECLARE @DBNAME NVARCHAR(128);
  SET @DBNAME = DB_NAME();
  RAISERROR
  (N'資料庫 ID:%d, 資料庫名稱: %s。 錯誤訊息：傳入的Action_Mode，必須是 A, U, D 其中一種',
  10, -- Severity.
  1, -- State.
  @DBID, -- First substitution argument.
  @DBNAME); -- Second substitution argument.
 END
 
 --宣告加密用變數
 DECLARE
  @encryp_uid NVARCHAR(max),
  @encryp_pwd NVARCHAR(max),
  @encryp_sort NVARCHAR(max)
 
 --SET @encryp_uid = ENCRYPTBYASYMKEY(ASYMKEY_ID('mykey'),@uid)
 SET @encryp_uid = ENCRYPTBYASYMKEY(ASYMKEY_ID('mykey'),CONVERT(nvarchar(max), @uid))
 --SET @encryp_pwd = ENCRYPTBYASYMKEY(ASYMKEY_ID('mykey'),@pwd)
 SET @encryp_pwd = ENCRYPTBYASYMKEY(ASYMKEY_ID('mykey'),CONVERT(nvarchar(max), @pwd))
 SET @encryp_sort = ENCRYPTBYASYMKEY(ASYMKEY_ID('mykey'),CONVERT(NVARCHAR(10),@sort))
 
 SET @encryp_uid = ENCRYPTBYASYMKEY(ASYMKEY_ID('mykey'),CONVERT(nvarchar(max), @uid))
 
 
 --插入
 IF (@action_type = 'A')
 BEGIN
  INSERT INTO t_BPM_Seal_type_E
        (
  username ,
        [uid] ,
        pwd ,
        sort ,
        cdt
        )
        VALUES
        (          
     @username,
  @encryp_uid,
        @encryp_pwd,
        @encryp_sort,
  @cdt
        )
 END
 
 --更新
 IF (@action_type = 'U')
 BEGIN
  UPDATE t_BPM_Seal_type_E
  SET username=@username,
   [uid]=@encryp_uid,
   pwd=@encryp_pwd,
   cdt=@cdt
  WHERE id=@id
 END
 
 --刪除
 IF (@action_type = 'D')
 BEGIN
  DELETE t_BPM_Seal_type_E
  WHERE id=@id
 END
 
END
GO

5.建立檢視資料表，用來讀取被加密的資料表

CREATE VIEW [dbo].[v_BPM_Seal_type_E]
AS
SELECT     id, username, CONVERT(NVARCHAR(50), DECRYPTBYASYMKEY(ASYMKEY_ID('mykey'), uid, N'p@ssw0rd')) AS uid, CONVERT(NVARCHAR(50),
                      DECRYPTBYASYMKEY(ASYMKEY_ID('mykey'), pwd, N'p@ssw0rd')) AS pwd, CONVERT(NVARCHAR(50),
                      DECRYPTBYASYMKEY(ASYMKEY_ID('mykey'), sort, N'p@ssw0rd')) AS sort, cdt
FROM         dbo.t_BPM_Seal_type_E

6.最後查詢被加密的資料表。
上面是未解密原資料表，下面是已解密的檢視(使用第5步驟建立的檢視表)

7.直接查詢，不透過檢視表(View)

Select id,
ENCRYPTBYASYMKEY(ASYMKEY_ID('mykey'),
CONVERT(nvarchar(max), username)) as '加密'
From dbo.t_BPM_Seal_type_E

Select  id,
CONVERT(nvarchar(10), DECRYPTBYASYMKEY(ASYMKEY_ID('mykey'), uid, N'p@ssw0rd')) as '解密'
From dbo.t_BPM_Seal_type_E

ASP.NET 將網頁輸出PDF

ASP.NET PDF
將網頁物件輸出至PDF，以下使用Table為例。

1.Download the itextsharp.dll from Internet.
2.Reference of  itextsharp.dll

3.use following references of itextsharp.dll

using iTextSharp.text;
using iTextSharp.text.pdf;
using iTextSharp.text.html.simpleparser;

4.Now the whole code of tsPDF.aspx.cs page will be as follows:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data.SqlClient;
using System.IO;
using System.Web;
using iTextSharp.text;
using iTextSharp.text.pdf;
using iTextSharp.text.html.simpleparser;  
 
public partial class Test_tsPDF : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        
    }
 
    public override void VerifyRenderingInServerForm(Control control)
    {
        //required to avoid the runtime error "  
        //Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server."  
    }  
 
    private void ExportGridToPDF()
    {
        try
        {
            Response.ContentType = "application/pdf";
            Response.AddHeader("content-disposition", "attachment;filename=Vithal_Wadje.pdf");
            Response.Cache.SetCacheability(HttpCacheability.NoCache);
            StringWriter sw = new StringWriter();
            HtmlTextWriter hw = new HtmlTextWriter(sw);
            //GridView1.RenderControl(hw);
            Table1.RenderControl(hw);
            StringReader sr = new StringReader(sw.ToString());
            Document pdfDoc = new Document(PageSize.A4, 10f, 10f, 10f, 0f);
            HTMLWorker htmlparser = new HTMLWorker(pdfDoc);
            PdfWriter.GetInstance(pdfDoc, Response.OutputStream);
            pdfDoc.Open();
            htmlparser.Parse(sr);
            pdfDoc.Close();
            Response.Write(pdfDoc);
            Response.End();
            GridView1.AllowPaging = true;
            GridView1.DataBind();
        }
        catch (Exception)
        {
            
            throw;
        }
        
    }
 
    protected void Button1_Click(object sender, EventArgs e)
    {
        ExportGridToPDF();  
    }
}

5.Now the whole code of tsPDF.aspx page will be as follows:

<body>
    <form id="form1" runat="server">
    <div>
        <asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" />
        <asp:GridView ID="GridView1" runat="server"></asp:GridView>
        <asp:Table ID="Table1" runat="server">
            <asp:TableRow>
                <asp:TableCell>Cell 1</asp:TableCell>
                <asp:TableCell>Cell 2</asp:TableCell>
                <asp:TableCell>Cell 3</asp:TableCell>
                <asp:TableCell>Cell 4</asp:TableCell>
                <asp:TableCell>Cell 5</asp:TableCell>
                <asp:TableCell>Cell 6</asp:TableCell>
            </asp:TableRow>
        </asp:Table>
    </div>
    </form>
</body>

Reference website:

http://www.c-sharpcorner.com/UploadFile/0c1bb2/export-gridview-to-pdf/

http://www.cc.ntu.edu.tw/chinese/epaper/0015/20101220_1509.htm

ASP.NET GridView to Excel(GridView輸出到Excel)

ASP.NET GridView to Excel(GridView輸出到Excel)

C#:

protected void Page_Load(object sender, EventArgs e)
{
        DataTable dt = new DataTable();
        GridView1.DataSource = dt;
        GridView1.DataBind();
 
        #region HMTL Response
        Response.Clear();
        Response.Write("<meta http-equiv=Content-Type content=text/html;charset=utf-8>");
        Response.AddHeader("content-disposition", "attachment;filename=" + strExportFilename + ".xls");
        Response.Cache.SetCacheability(HttpCacheability.NoCache);
        Response.ContentType = "application/vnd.xls";
        //Response.Charset = "big5";  //utf-8
        Response.Charset = "utf-8";  //utf-8
        //Response.ContentEncoding = System.Text.Encoding.GetEncoding(950); //65001
        Response.ContentEncoding = System.Text.Encoding.GetEncoding(65001); //65001
 
        System.IO.StringWriter stringWrite = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
        GridView1.RenderControl(htmlWrite); //將物件GridView1輸出
        //Table1.RenderControl(htmlWrite); //將物件Table1輸出
        Response.Write(stringWrite.ToString().Replace("<div>", "").Replace("</div>", ""));
        Response.End();
        #endregion
}

ASP.NET 型別 'GridView' 的控制項 'GridView1' 必須置於有 runat=server 的表單標記之中

ASP.NET 型別 'GridView' 的控制項 'GridView1' 必須置於有 runat=server 的表單標記之中

在.cs中加入下段即可

public override void VerifyRenderingInServerForm(Control control)
{
    //處理'GridView' 的控制項 'GridView' 必須置於有 runat=server 的表單標記之中
}

Ref
http://social.msdn.microsoft.com/Forums/zh-TW/fc860a06-bb14-40fd-bced-9d86af5d9653/gridviewexcel?forum=236

ASP.NET 取得GridView Cells下所有Label物件

ASP.NET 取得GridView Cells下所有Label物件

C#:

 protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            try
            {
                if (e.Row.RowType != DataControlRowType.DataRow) // 非資料行, 離開
                    return;
 
                //單筆, 取得指定名稱Label物件, 若Text="n/a", 將Text改為空白
                Label lblGvptz_zoom = e.Row.FindControl("lblGvptz_zoom") as Label;
                if (lblGvptz_zoom.Text.Equals("n/a"))
                    lblGvptz_zoom.Text = "";
 
                //多筆迴圈, 取得Row下所以Cells中的Label物件, 若Text="n/a", 將Text改為空白
                foreach (TableCell cl in e.Row.Cells)
                {
                    foreach (object ctrl in cl.Controls)
                    {
                        if (ctrl is System.Web.UI.WebControls.Label) //判斷ctrl是否為Label物件
                        {
                            Label lblctrl = (Label)ctrl;
                            if (lblctrl.Text.Equals("n/a"))
                                lblctrl.Text = "";
                        }
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

T-SQL Cannot create a row of size 9440 which is greater than the allowable maximum row size of 8060

T-SQL Cannot create a row of size 9440 which is greater than the allowable maximum row size of 8060

StoredProcedure寫了一段PIVOT時出現的錯誤訊息
錯誤原因是其中有一個欄位長度超過8060，解決方法將原來nvarchar(max)改為==>nvarchar(4000)即可

下段StoredProcedure其中text1使用了nvarchar(max)

將text1改為nvarchar(4000)即可

T-SQL Stored Procedure使用條件式 IN

T-SQL Stored Procedure使用條件式 IN

How to pass string parameter with `IN` operator in stored procedure

(1)T-SQL (sp_ATS_ComparisonPIVOT) 主要預存程序
傳入參數：@model = N'A1234,B1234'

CREATE PROCEDURE [dbo].[sp_ATS_ComparisonPIVOT]
 
 @model       nvarchar(max)   
AS
 declare @ColumnGroup   nvarchar(max),
   @PivotSQL    nvarchar(max)
BEGIN
 
 SET NOCOUNT ON;
 
 select  @ColumnGroup = COALESCE(@ColumnGroup + ',' ,'' ) + QUOTENAME(model)
 FROM t_ATS_spec
 --請看下一行範例
 where model in (Select data From dbo.fn_slip_str(@model,','))
 GROUP BY QUOTENAME(model)
 select @ColumnGroup
 
 SELECT @PivotSQL = N'
 SELECT *
 FROM  (SELECT technicalspecification,name,model,text1 FROM t_ATS_spec) AS SourceTable
 PIVOT
 (max(text1)
 FOR
 model  IN (' + @ColumnGroup + N')
 ) AS PivotTable'
 exec(@PivotSQL)
 
END

(2)T-SQL(Function fn_slip_str)搭配字串分割程序使用

Create Function fn_slip_str( @InStr nvarchar(2000) , @s_char nvarchar(1) )
Returns @tb Table ( sno int , data nvarchar(100) )
As
Begin
    /*依據傳入字元進行字串分割，回傳Table*/
    /*
    Declare @InStr nvarchar(2000) , @s_char nvarchar(1);
    Set @InStr = '字串一,字串2,字串3,字串4';
    Set @s_char = ',';*/
 
    Set @InStr = @s_char + @InStr + @s_char;
    Declare @p1 Int , @p2 Int , @data nvarchar(100) , @sno int;
    Set @p1 = -1 ; Set @p2 = -1 ; Set @data = '' ; Set @sno = 0;
    While ( 0 Not In (@P1,@P2) ) Begin
  Set @p1 = CharIndex(@s_char,@InStr,@p1+1);
  Set @p2 = CharIndex(@s_char,@InStr,@p1+1);
  If ( 0 In (@p1,@p2) )
      Break;
  Set @data = SubString(@InStr,@p1+1,@p2-@p1-1);
  if ( @data <> '' ) Begin
      Set @sno = @sno +1;
      Insert Into @tb ( sno , data ) Values ( @sno , @data )
  End
    End
    Return
End

(3)執行結果：

Select * From dbo.fn_slip_str('字串一,字串2,字串3,字串4',',');

Ref WebSite
http://www.dotblogs.com.tw/rachen/archive/2008/05/23/4110.aspx

T-SQL字串分割

T-SQL字串分割

T-SQL code(backup):

Create Function fn_slip_str( @InStr nvarchar(2000) , @s_char nvarchar(1) )
Returns @tb Table ( sno int , data nvarchar(100) )
As
Begin
    /*依據傳入字元進行字串分割，回傳Table*/
    /*
    Declare @InStr nvarchar(2000) , @s_char nvarchar(1);
    Set @InStr = '字串一,字串2,字串3,字串4';
    Set @s_char = ',';*/
 
    Set @InStr = @s_char + @InStr + @s_char;
    Declare @p1 Int , @p2 Int , @data nvarchar(100) , @sno int;
    Set @p1 = -1 ; Set @p2 = -1 ; Set @data = '' ; Set @sno = 0;
    While ( 0 Not In (@P1,@P2) ) Begin
  Set @p1 = CharIndex(@s_char,@InStr,@p1+1);
  Set @p2 = CharIndex(@s_char,@InStr,@p1+1);
  If ( 0 In (@p1,@p2) )
      Break;
  Set @data = SubString(@InStr,@p1+1,@p2-@p1-1);
  if ( @data <> '' ) Begin
      Set @sno = @sno +1;
      Insert Into @tb ( sno , data ) Values ( @sno , @data )
  End
    End
    Return
End

執行結果：

Select * From dbo.fn_slip_str('字串一,字串2,字串3,字串4',',');

下段程式原始來源請參考下列網址(如有侵權請告知):
http://www.dotblogs.com.tw/rachen/archive/2008/05/23/4110.aspx