public static bool SaveOrUpdateAppInstance(AppInstance instance, string spHostWebDomain)
{
try
{
AppInstanceModel updateEntity = null;
StringBuilder commandText = new StringBuilder();
if (!String.IsNullOrWhiteSpace(instance.InstanceId))
{
// Assign the result to a DbxlInstanceEntity object.
updateEntity = GetAppInstanceEntity(instance.InstanceId, instance.O365Domain);
}
//commandText = "OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE QFSCredentials;";
if (updateEntity == null)
{
commandText.AppendFormat("OPEN SYMMETRIC KEY {0} DECRYPTION BY CERTIFICATE QFSCredentials; ", EncryptionKey);
commandText.Append("INSERT INTO QFSCredentials VALUES(@O365Domain, @InstanceId, @InstanceName, ");
commandText.AppendFormat("EncryptByKey(Key_GUID('{0}'), @Password, 1, HashBytes('SHA1', CONVERT( nvarchar, @Username))), @ServiceURL, ", EncryptionKey);
commandText.AppendFormat("EncryptByKey(Key_GUID('{0}'), @Username), @Domain)", EncryptionKey);
}
else
{
commandText.AppendFormat("OPEN SYMMETRIC KEY {0} DECRYPTION BY CERTIFICATE QFSCredentials; ", EncryptionKey);
commandText.Append("Update QFSCredentials Set InstanceName = @InstanceName, ");
commandText.AppendFormat("Password = EncryptByKey(Key_GUID('{0}'), @Password, 1, HashBytes('SHA1', CONVERT( nvarchar, @Username))), ServiceURL = @ServiceURL, ", EncryptionKey);
commandText.AppendFormat("Username = EncryptByKey(Key_GUID('{0}'), @Username), Domain = @Domain WHERE O365Domain = @O365Domain AND InstanceId = @InstanceId", EncryptionKey);
}
using (var sqlConnection = new SqlConnection(ConnectionString))
{
using (var sqlCommandIOrU = new SqlCommand(commandText.ToString(), sqlConnection))
{
sqlConnection.Open();
sqlCommandIOrU.Parameters.AddWithValue("@InstanceId", updateEntity == null ? Guid.NewGuid().ToString() : updateEntity.InstanceId);
sqlCommandIOrU.Parameters.AddWithValue("@InstanceName", instance.InstanceName);
sqlCommandIOrU.Parameters.AddWithValue("@ServiceURL", instance.ServiceURL);
sqlCommandIOrU.Parameters.Add("@Username", SqlDbType.NVarChar, 255).Value = !String.IsNullOrWhiteSpace(instance.Username) ? EncryptData(instance.Username) : updateEntity.Username;
// sqlCommandIOrU.Parameters.AddWithValue("@Username", !String.IsNullOrWhiteSpace(instance.Username) ? EncryptData(instance.Username) : updateEntity.Username);
sqlCommandIOrU.Parameters.Add("@Password", SqlDbType.NVarChar, 255).Value = !String.IsNullOrWhiteSpace(instance.Password) ? EncryptData(instance.Password) : updateEntity.Password;
//sqlCommandIOrU.Parameters.AddWithValue("@Password", !String.IsNullOrWhiteSpace(instance.Password) ? EncryptData(instance.Password) : updateEntity.Password);
sqlCommandIOrU.Parameters.AddWithValue("@Domain", instance.Domain == null ? "" : instance.Domain);
sqlCommandIOrU.Parameters.AddWithValue("@O365Domain", updateEntity == null ? spHostWebDomain : updateEntity.O365Domain);
var cmdResult = sqlCommandIOrU.ExecuteNonQuery();
}
}
return(true);
}
catch
{
return(false);
}
}
public AppInstance(AppInstanceModel entity)
{
InstanceId = entity.InstanceId;
InstanceName = entity.InstanceName;
ServiceURL = entity.ServiceURL;
Domain = entity.Domain;
O365Domain = entity.O365Domain;
EncryptedUsername = entity.Username;
EncryptedPassword = entity.Password;
}
private static AppInstanceModel GetAppInstanceEntity(string instanceId, string spHostWebDomain)
{
AppInstanceModel appInstance = new AppInstanceModel();
using (var sqlConnection = new SqlConnection(ConnectionString))
{
StringBuilder commandText = new StringBuilder();
commandText.AppendFormat("OPEN SYMMETRIC KEY {0} DECRYPTION BY CERTIFICATE QFSCredentials; ", EncryptionKey);
commandText.Append("SELECT InstanceId, InstanceName, ServiceURL, Domain, O365Domain, CONVERT(nvarchar(255), DecryptByKey(Username)) AS 'Username', ");
commandText.Append("CONVERT(nvarchar(255), DecryptByKey(Password, 1, HashBytes('SHA1', CONVERT(nvarchar, DecryptByKey(Username))))) As Password ");
commandText.Append("FROM QFSCredentials WHERE O365Domain = @O365Domain and InstanceId = @InstanceId");
//commandText.AppendFormat("OPEN SYMMETRIC KEY {0} DECRYPTION BY CERTIFICATE QFSCredentials; ", EncryptionKey);
//commandText.Append("SELECT InstanceId, InstanceName, ServiceURL, Domain, O365Domain, CONVERT(varchar(255), DecryptByKey(Username)) AS 'Username', ");
//commandText.Append("CONVERT(varchar, DecryptByKey(Password, 1, HashBytes('SHA1', CONVERT(varbinary, CONVERT(varchar(255), DecryptByKey(Username)))))) As Password ");
//commandText.Append("FROM QFSCredentials WHERE O365Domain = @O365Domain and InstanceId = @InstanceId");
using (var sqlCommand = new SqlCommand(commandText.ToString(), sqlConnection))
{
sqlCommand.Parameters.AddWithValue("@O365Domain", spHostWebDomain);
sqlCommand.Parameters.AddWithValue("@InstanceId", instanceId);
sqlConnection.Open();
using (SqlDataReader reader = sqlCommand.ExecuteReader())
{
if (reader.Read())
{
appInstance.InstanceId = Convert.ToString(reader["InstanceId"]);
appInstance.InstanceName = Convert.ToString(reader["InstanceName"]);
appInstance.ServiceURL = Convert.ToString(reader["ServiceURL"]);
appInstance.Domain = Convert.ToString(reader["Domain"]);
appInstance.O365Domain = Convert.ToString(reader["O365Domain"]);
appInstance.Username = Convert.ToString(reader["Username"]);
appInstance.Password = Convert.ToString(reader["Password"]);
}
}
}
}
return(appInstance);
}
public void SerializeTest()
{
AppInstanceModel model = new AppInstanceModel
{
Id = "AppInstanceModel.Id",
Company = "AppInstanceModel.Compan",
Name = "AppInstanceModel.Name",
Title = "AppInstanceModel.Title",
CopyRight = "AppInstanceModel.CopyRight"
};
var bytes = JsonSerializer <AppInstanceModel> .Instance.Serialize(model);
var str = Encoding.UTF8.GetString(bytes);
using (var stream = new StreamWriter("AppInstanceModel.config"))
{
stream.Write(str);
}
}
public void Save(AppInstanceModel model)
{
}