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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed


Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted January 25, 2016

WEBINAR:
On-Demand

The California Consumer Privacy Act: What You Need to Know


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