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

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