Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted January 25, 2016

Exploring SQL Server 2016 Always Encrypted – Part 3 – Differences between Deterministic or Randomize Encryption - Page 4

By Greg Larsen

Code to Populating table Sales.CustomerPII_Randomized

Here is the C Sharp code that reads from the Sales.CustomerPII table and then inserts the read records into the Sales.CustomerPII_Randomized table.

using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
 
// Populated Sales.CustomerPII_Randomized table with data
class AlwaysEncryptedInsert
{
    //Read connection
    SqlConnection conn;
    //Insert connnection
    SqlConnection conn2;
    public AlwaysEncryptedInsert()
    {
        // Instantiate the connections
        conn = new SqlConnection(
          "data source=WIN-B95MVF88UT5;initial catalog=AdventureWorks2016CTP3;integrated security = False; Column Encryption Setting=Enabled; User ID = Greg; Password = Test;");
        conn2 = new SqlConnection(
  "data source=WIN-B95MVF88UT5;initial catalog=AdventureWorks2016CTP3;integrated security = False; Column Encryption Setting=Enabled; User ID = Greg; Password = Test;");
    }
 
   static void Main()
    {
        AlwaysEncryptedInsert scd = new AlwaysEncryptedInsert();
 
        scd.ReadInsertdata();
    }
 
    public void ReadInsertdata()
    {
        try
        {
            // open connections
            conn.Open();
            conn2.Open();
           // issue select statement 
            SqlCommand cmd= new SqlCommand("select * from Sales.CustomerPII", conn);
            SqlDataReader reader = cmd.ExecuteReader();
            
 
            string CustomerID = "";
            string FirstName = "";
            string LastName = "";
            string SSN = "";
            string CreditCardNumber = "";
            string EmailAddress = "";
            string PhoneNumber = "";
            string TerritoryId = "";
 
            // iterate tbrough read buffer
            while (reader.Read())
            {
                //get column data
                CustomerID = reader["CustomerId"].ToString();
                FirstName = reader["FirstName"].ToString();
                LastName = reader["LastName"].ToString();
                SSN = reader["SSN"].ToString();
                CreditCardNumber = reader["CreditCardNumber"].ToString();
                EmailAddress = reader["EmailAddress"].ToString();
                PhoneNumber = reader["PhoneNumber"].ToString();
                TerritoryId = reader["TerritoryId"].ToString();
                
                // Issue insert into new table using randomized encryption
                SqlCommand com1 = new SqlCommand("insert into Sales.CustomerPII_Randomized values(@CustomerID, @FirstName, @LastName, @SSN, @CreditCardNumber, @EmailAddress, @PhoneNumber,@TerritoryId)", conn2);
                com1.Parameters.AddWithValue("@CustomerId", CustomerID);
                com1.Parameters.AddWithValue("@FirstName", FirstName);
                com1.Parameters.AddWithValue("@LastName", LastName);
                com1.Parameters.AddWithValue("@SSN", SSN);
                com1.Parameters.AddWithValue("@CreditCardNumber", CreditCardNumber);
                com1.Parameters.AddWithValue("@EmailAddress", EmailAddress);
                com1.Parameters.AddWithValue("@PhoneNumber", PhoneNumber);
                com1.Parameters.AddWithValue("@TerritoryId", TerritoryId);
                com1.ExecuteNonQuery();
            }
        }
        finally
        {
            // Close the connection
            if (conn != null)
            {
                conn.Close();
            }
            if (conn2 != null)
            {
                conn.Close();
            }
        }
 
    }
    
}
 
 
 
 

Code to Populate Deterministic and Randomized fields

Here is the code to populate the table named “Compare “.

using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
 
// Populate Tables to show differences between encryption types
class AlwaysEncryptedShowDiffernces
{
    //Insert connection
    SqlConnection conn;
    public AlwaysEncryptedShowDiffernces()
    {
        // Instantiate the connections
        conn = new SqlConnection(
          "data source=WIN-B95MVF88UT5;initial catalog=AdventureWorks2016CTP3;integrated security = False; Column Encryption Setting=Enabled; User ID = Greg; Password = Test;");
 
    }
 
    static void Main()
    {
        AlwaysEncryptedShowDiffernces scd = new AlwaysEncryptedShowDiffernces();
 
        scd.Joindata();
    }
 
    public void Joindata()
    {
        try
        {
            // open connections
            conn.Open();
 
 
            string String1D = "ABC";
            string String1R = "ABC";
            string String2D = "ABC";
            string String2R = "ABC";
            string String3D = "ABC";
            string String3R = "ABC";
            // Populate Compare Table
 
            SqlCommand cmd = new SqlCommand("INSERT INTO Compare (DeterministicString, RandomizedString) Values (@String1D, @String1R),(@String2D, @String2R),(@String3D, @String3R)", conn);
            cmd.Parameters.AddWithValue("@String1D", String1D);
            cmd.Parameters.AddWithValue("@String2D", String2D);
            cmd.Parameters.AddWithValue("@String3D", String3D);
            cmd.Parameters.AddWithValue("@String1R", String1R);
            cmd.Parameters.AddWithValue("@String2R", String2R);
            cmd.Parameters.AddWithValue("@String3R", String3R);
            SqlDataReader reader = cmd.ExecuteReader();
 
        }
        finally
        {
            // Close the connection
            if (conn != null)
            {
                conn.Close();
            }
 
        }
 
    }
 
}

 

Code to Search CustomerPII Table

This code will perform a WHERE constraint against an Always Encrypted column:

using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
 
// Search Sales.CustomerPII table with data
class AlwaysEncryptedSearch
{
    //Read connection
    SqlConnection conn;
 
    public AlwaysEncryptedSearch()
    {
        // Instantiate the connections
        conn = new SqlConnection(
          "data source=WIN-B95MVF88UT5;initial catalog=AdventureWorks2016CTP3;integrated security = False; Column Encryption Setting=Enabled; User ID = Greg; Password = Test;");
 
    }
 
    static void Main()
    {
        AlwaysEncryptedSearch scd = new AlwaysEncryptedSearch();
 
        scd.Searchdata();
    }
 
    public void Searchdata()
    {
        try
        {
            // open connections
            conn.Open();
 
            // issue select statement 
            SqlCommand cmd = new SqlCommand("SELECT CustomerID, CreditCardNumber FROM Sales.CustomerPII WHERE CreditCardNumber = @CCN", conn);
            SqlParameter CCN = new SqlParameter("@CCN", System.Data.SqlDbType.NVarChar, 25);
            CCN.Value = "11115373226233";
            cmd.Parameters.Add(CCN);
            SqlDataReader reader = cmd.ExecuteReader();
 
            string CustomerID = "";
 
            // iterate tbrough read buffer
            while (reader.Read())
            {
                //get CustomerID read
                CustomerID = reader["CustomerId"].ToString();
                //display CustomerID read
                MessageBox.Show("CustomerId = " + CustomerID);
            }
        }
        finally
        {
            // Close the connection
            if (conn != null)
            {
                conn.Close();
            }
 
        }
 
    }
 
}

Code to Populate JOIN_SSN table

The code is used to populate the JOIN_SSN table:

using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
 
// Populate Sales.JOIN_SSN table with data
class AlwaysEncryptedInsert
{
    //Insert connection
    SqlConnection conn;
    public AlwaysEncryptedInsert()
    {
        // Instantiate the connections
        conn = new SqlConnection(
          "data source=WIN-B95MVF88UT5;initial catalog=AdventureWorks2016CTP3;integrated security = False; Column Encryption Setting=Enabled; User ID = Greg; Password = Test;");
 
    }
 
    // call methods that demo Determinstic and Randomized Always Encrypted Columns
    static void Main()
    {
        AlwaysEncryptedInsert scd = new AlwaysEncryptedInsert();
 
        scd.Joindata();
    }
 
    public void Joindata()
    {
        try
        {
            // open connections
            conn.Open();
 
           
            string SSN1 = "319-51-1661";
            string SSN2 = "555-51-1661";
            string SSN3 = "918-75-6173";
           // issue Insert statement 
            SqlCommand cmd= new SqlCommand("INSERT INTO JOIN_SSN Values (@SSN1),(@SSN2),(@SSN3)", conn);
            cmd.Parameters.AddWithValue("@SSN1", SSN1);
            cmd.Parameters.AddWithValue("@SSN2", SSN2);
            cmd.Parameters.AddWithValue("@SSN3", SSN3);
            SqlDataReader reader = cmd.ExecuteReader();
 
        }
        finally
        {
            // Close the connection
            if (conn != null)
            {
                conn.Close();
            }
 
        }
 
    }
    
}

 

Code to Populate JOIN_SSN_Randomized table

The code to populate my JOIN_SSN_Randomized table:

using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
 
// Populate Sales.JOIN_SSN table with data
class AlwaysEncryptedInsert
{
    //Insert connection
    SqlConnection conn;
    public AlwaysEncryptedInsert()
    {
        // Instantiate the connections
        conn = new SqlConnection(
          "data source=WIN-B95MVF88UT5;initial catalog=AdventureWorks2016CTP3;integrated security = False; Column Encryption Setting=Enabled; User ID = Greg; Password = Test;");
 
    }
 
    // call methods that demo Determinstic and Randomized Always Encrypted Columns
    static void Main()
    {
        AlwaysEncryptedInsert scd = new AlwaysEncryptedInsert();
 
        scd.Joindata();
    }
 
    public void Joindata()
    {
        try
        {
            // open connections
            conn.Open();
 
           
            string SSN1 = "319-51-1661";
            string SSN2 = "555-51-1661";
            string SSN3 = "918-75-6173";
           // issue Insert statement 
            SqlCommand cmd= new SqlCommand("INSERT INTO JOIN_SSN_Randomized Values (@SSN1),(@SSN2),(@SSN3)", conn);
            cmd.Parameters.AddWithValue("@SSN1", SSN1);
            cmd.Parameters.AddWithValue("@SSN2", SSN2);
            cmd.Parameters.AddWithValue("@SSN3", SSN3);
            SqlDataReader reader = cmd.ExecuteReader();
 
        }
        finally
        {
            // Close the connection
            if (conn != null)
            {
                conn.Close();
            }
 
        }
 
    }
    
}

See all articles by Greg Larsen



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM