public static JObject GetSQLMergeStatementCore(HttpRequest req,
Logging logging)
{
string requestBody = new StreamReader(req.Body).ReadToEndAsync().Result;
dynamic data = JsonConvert.DeserializeObject(requestBody);
JObject Root = new JObject();
TaskMetaDataDatabase TMD = new TaskMetaDataDatabase();
using (SqlConnection _con = TMD.GetSqlConnection())
{
string _token = Shared._AzureAuthenticationCredentialProvider.GetAzureRestApiToken("https://database.windows.net/");
String g = Guid.NewGuid().ToString().Replace("-", "");
_con.AccessToken = _token;
JArray arrStage = (JArray)data["Stage"];
string _StagingTableSchema = data["StagingTableSchema"].ToString();
string _StagingTableName = "#Temp_" + data["StagingTableName"].ToString() + g.ToString();
string _CreateStatementStage = GenerateSQLStatementTemplates.GetCreateTable(arrStage, _StagingTableSchema, _StagingTableName, false);
TMD.ExecuteSql(_CreateStatementStage, _con);
JArray arrTarget = (JArray)data["Target"];
string _TargetTableSchema = data["TargetTableSchema"].ToString();
string _TargetTableName = "#Temp_" + data["TargetTableName"].ToString() + g.ToString();
string _CreateStatementTarget = GenerateSQLStatementTemplates.GetCreateTable(arrTarget, _TargetTableSchema, _TargetTableName, false);
TMD.ExecuteSql(_CreateStatementTarget, _con);
string _MergeStatement = TMD.GenerateMergeSQL(_StagingTableSchema, _StagingTableName, _TargetTableSchema, _TargetTableName, _con, true, logging);
string fullStagingTableName = string.Format("[{0}].[{1}]", _StagingTableSchema, _StagingTableName.Replace("#Temp_", "").Replace(g.ToString(), ""));
string fullTargetTableName = string.Format("[{0}].[{1}]", _TargetTableSchema, _TargetTableName.Replace("#Temp_", "").Replace(g.ToString(), ""));
_MergeStatement = _MergeStatement.Replace(_TargetTableName, fullTargetTableName);
_MergeStatement = _MergeStatement.Replace(_StagingTableName, fullStagingTableName);
//Add Select for ADF Lookup Activity
_MergeStatement += Environment.NewLine + "Select 1 ";
Root["MergeStatement"] = _MergeStatement;
logging.LogInformation("GetSQLMergeStatement Function complete.");
}
return(Root);
}
public static JObject GetSQLCreateStatementFromSchemaCore(HttpRequest req,
Logging logging)
{
string requestBody = new StreamReader(req.Body).ReadToEndAsync().Result;
JObject data = JsonConvert.DeserializeObject <JObject>(requestBody);
string _CreateStatement;
JArray arr;
if (data["Data"] != null)
{
//Need to swap logic for parquet vs sql etc
arr = (JArray)data["Data"]["value"];
}
else if (data["SchemaFileName"] != null)
{
string _storageAccountName = data["StorageAccountName"].ToString();
string _storageAccountContainer = data["StorageAccountContainer"].ToString();
string _relativePath = data["RelativePath"].ToString();
string _schemaFileName = data["SchemaFileName"].ToString();
_storageAccountName = _storageAccountName.Replace(".dfs.core.windows.net", "").Replace("https://", "").Replace(".blob.core.windows.net", "");
TokenCredential StorageToken = new TokenCredential(Shared._AzureAuthenticationCredentialProvider.GetAzureRestApiToken("https://" + _storageAccountName + ".blob.core.windows.net", Shared._ApplicationOptions.UseMSI));
arr = (JArray)JsonConvert.DeserializeObject(Shared.Azure.Storage.ReadFile(_storageAccountName, _storageAccountContainer, _relativePath, _schemaFileName, StorageToken));
}
else
{
throw new System.ArgumentException("Not Valid parameters to GetSQLCreateStatementFromSchema Function!");
}
bool _DropIfExist = data["DropIfExist"] == null ? false : (bool)data["DropIfExist"];
_CreateStatement = GenerateSQLStatementTemplates.GetCreateTable(arr, data["TableSchema"].ToString(), data["TableName"].ToString(), _DropIfExist);
_CreateStatement += Environment.NewLine + "Select 1";
JObject Root = new JObject
{
["CreateStatement"] = _CreateStatement
};
return(Root);
}
