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 February 1, 2016

WEBINAR:
On-Demand

How to Help Your Business Become an AI Early Adopter


Exploring SQL Server 2016 Always Encrypted – Part 4 – Encrypting Existing Data - Page 4

By Greg Larsen

Code to Encrypt Data in MyConfidentialdata table

This C Sharp code moves unencrypted data from MyConfidentialData table to MyConfidentialDataTemp 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=SERVER1;initial catalog=Demo_Encrypt_Existing;integrated security = False; Column Encryption Setting=Enabled; User ID = AppAdmin; Password = AppAdm1n;");
        conn2 = new SqlConnection(
  "data source=SERVER1;initial catalog=Demo_Encrypt_Existing;integrated security = False; Column Encryption Setting=Enabled; User ID = AppAdmin; Password = AppAdm1n;");
    }
 
    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 ConfidentialID, FirstName, LastName, SSN, DriverLicenseNumber from MyConfidentialData", conn);
            SqlDataReader reader = cmd.ExecuteReader();
 
 
              // iterate tbrough read buffer
            while (reader.Read())
            {
 
 
                // Issue insert into new  temporarhy table table  
                SqlCommand com1 = new SqlCommand("insert into MyConfidentialDataTemp values(@ConfidentialID, @FirstName, @LastName, @SSN, @DriverLicenseNumber)", conn2);
                com1.Parameters.AddWithValue("@ConfidentialID", reader["ConfidentialId"]);
                com1.Parameters.AddWithValue("@FirstName", reader["FirstName"]);
                com1.Parameters.AddWithValue("@LastName", reader["LastName"]);
                com1.Parameters.AddWithValue("@SSN", reader["SSN"]);
                com1.Parameters.AddWithValue("@DriverLicenseNumber", reader["DriverLicenseNumber"]);
                com1.ExecuteNonQuery();
            }
        }
        finally
        {
            // Close the connection
            if (conn != null)
            {
                conn.Close();
            }
            if (conn2 != 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