public void TestEncryptDecryptWithCSP(string connectionString)
{
string providerName = @"Microsoft Enhanced RSA and AES Cryptographic Provider";
string keyIdentifier = DataTestUtility.GetUniqueNameForSqlServer("CSP");
try
{
CertificateUtilityWin.RSAPersistKeyInCsp(providerName, keyIdentifier);
string cspPath = String.Concat(providerName, @"/", keyIdentifier);
SQLSetupStrategyCspExt sqlSetupStrategyCsp = new SQLSetupStrategyCspExt(cspPath);
string tableName = sqlSetupStrategyCsp.CspProviderTable.Name;
try
{
using (SqlConnection sqlConn = new SqlConnection(connectionString))
{
sqlConn.Open();
Table.DeleteData(tableName, sqlConn);
// insert 1 row data
Customer customer = new Customer(45, "Microsoft", "Corporation");
DatabaseHelper.InsertCustomerData(sqlConn, tableName, customer);
// Test INPUT parameter on an encrypted parameter
using (SqlCommand sqlCommand = new SqlCommand(@"SELECT CustomerId, FirstName, LastName FROM [@tableName] WHERE FirstName = @firstName",
sqlConn, null, SqlCommandColumnEncryptionSetting.Enabled))
{
sqlCommand.Parameters.AddWithValue(@"tableName", tableName);
SqlParameter customerFirstParam = sqlCommand.Parameters.AddWithValue(@"firstName", @"Microsoft");
customerFirstParam.Direction = System.Data.ParameterDirection.Input;
using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
{
ValidateResultSet(sqlDataReader);
}
}
}
}
catch (Exception e)
{
Console.WriteLine(@"Exception: {0}", e.Message);
}
finally
{
// clean up database resources
sqlSetupStrategyCsp.Dispose();
}
}
finally
{
CertificateUtilityWin.RSADeleteKeyInCsp(providerName, keyIdentifier);
}
}
