public void executeStatements(SQLStatements sqlStatements)
{
foreach (var s in sqlStatements.Statements)
{
SqlConnection conn = new SqlConnection(SQLConnectionStr);
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(SQLConnectionStr);
//use AAD auth when userid is not passed in connection string
if (string.IsNullOrEmpty(builder.UserID))
{
conn.AccessToken = (new AzureServiceTokenProvider()).GetAccessTokenAsync("https://database.windows.net/", Tenant).Result;
}
conn.Open();
using (var command = new SqlCommand(s.Statement, conn))
{
try
{
command.ExecuteNonQuery();
s.Created = true;
}
catch (SqlException ex)
{
s.Created = false;
s.Detail = ex.Message;
}
}
conn.Close();
}
}
public void executeStatements(SQLStatements sqlStatements)
{
SqlConnection conn = new SqlConnection(SQLConnectionStr);
conn.Open();
foreach (var s in sqlStatements.Statements)
{
using (var command = new SqlCommand(s.Statement, conn))
{
try
{
command.ExecuteNonQuery();
s.Created = true;
}
catch (SqlException ex)
{
s.Created = false;
s.Detail = ex.Message;
}
}
}
conn.Close();
}
public IActionResult GetEmploymentInfo()
{
var tenantID = CookieHandler.GetCurrentUserID(Request.Cookies["AuthToken"]);
var details = SQLStatements.GetEmploymentDetails(tenantID);
return(Json(new { success = true, data = details }));
}
public void TriggerCreate(string triggerName, string triggerFunctionName, bool overwriteIfExists = false)
{
var triggerExists = TriggerExists(triggerName);
if (!triggerExists || overwriteIfExists)
{
if (triggerExists)
{
TriggerDrop(triggerName);
}
Execute().ExecuteNonQuery(SQLStatements.TableTriggerCreate(triggerName, triggerFunctionName, this));
}
}
public IActionResult GetPersonalInfo()
{
var tenantID = CookieHandler.GetCurrentUserID(Request.Cookies["AuthToken"]);
var personalInfo = SQLStatements.GetPersonalInformation(tenantID);
if (personalInfo != null)
{
return(Json(new { success = true, data = personalInfo }));
}
else
{
return(Json(new { success = false, error = "No personal info found." }));
}
}
public static async Task <IActionResult> manifestToSQL(
[HttpTrigger(AuthorizationLevel.Function, "get", "post", Route = null)] HttpRequest req,
ILogger log, ExecutionContext context)
{
log.LogInformation("C# HTTP trigger function processed a request.");
//get data from
string tenantId = req.Headers["TenantId"];
string storageAccount = req.Headers["StorageAccount"];
string rootFolder = req.Headers["RootFolder"];
string localFolder = req.Headers["ManifestLocation"];
string manifestName = req.Headers["ManifestName"];
string DDLType = req.Headers["DDLType"];
string schema = req.Headers["Schema"];
string dataSourceName = req.Headers["DataSourceName"];
string fileFormat = req.Headers["FileFormat"];
string connectionString = req.Headers["SQLEndpoint"];
AdlsContext adlsContext = new AdlsContext()
{
StorageAccount = storageAccount,
FileSytemName = rootFolder,
MSIAuth = true,
TenantId = tenantId
};
// Read Manifest metadata
log.Log(LogLevel.Information, "Reading Manifest metadata");
List <SQLMetadata> metadataList = new List <SQLMetadata>();
await ManifestHandler.manifestToSQLMetadata(adlsContext, manifestName, localFolder, metadataList);
// convert metadata to DDL
log.Log(LogLevel.Information, "Converting metadata to DDL");
var statementsList = await ManifestHandler.SQLMetadataToDDL(metadataList, DDLType, schema, fileFormat, dataSourceName);
// Execute DDL
log.Log(LogLevel.Information, "Executing DDL");
SQLHandler sQLHandler = new SQLHandler(connectionString, tenantId);
var statements = new SQLStatements {
Statements = statementsList
};
sQLHandler.executeStatements(statements);
return(new OkObjectResult(JsonConvert.SerializeObject(statements)));
}
public string RefreshIndicesAndGetTablename()
{
string cmd_index = SQLStatements.Instance().GetIndiciesByName(_dbReg.Version, IndexName.Trim());
DataFilled = false;
txtIndexName.Text = IndexName.Trim();
string TableName = string.Empty;
try
{
lvFields.Items.Clear();
var con = new FbConnection(ConnectionStrings.Instance().MakeConnectionString(_dbReg));
con.Open();
string PkColumn = string.Empty;
string IndexName = string.Empty;
string IndexColumnName = string.Empty;
int Unique = 0;
FbCommand fcmd = new FbCommand(cmd_index, con);
var dread = fcmd.ExecuteReader();
if (dread.HasRows)
{
while (dread.Read())
{
TableName = dread.GetValue(0).ToString().Trim();
IndexColumnName = dread.GetValue(2).ToString().Trim();
Unique = StaticFunctionsClass.ToIntDef(dread.GetValue(3).ToString().Trim(), 0);
cbUnique.Checked = Unique > 0;
string[] lv = new string[1];
lv[0] = IndexColumnName;
ListViewItem lvi = new ListViewItem(lv);
lvFields.Items.Add(lvi);
}
DataFilled = true;
}
con.Close();
}
catch (Exception ex)
{
_localNotify?.AddToERROR(StaticFunctionsClass.DateTimeNowStr() + "->ConstraintForm->RefreshIndices()->" + ex.Message);
}
return(TableName);
}
static void Main(string[] args)
{
//get data from
string tenantId = "979fd422-22c4-4a36-bea6-1cf87b6502dd";
string storageAccount = "ftanalyticsd365fo.dfs.core.windows.net";
string rootFolder = "/dynamics365-financeandoperations/analytics.sandbox.operations.dynamics.com/";
string localFolder = "Tables/Finance/Ledger/Main";
string manifestName = "Main";
var connectionString = "Server=ftsasynapseworkspace-ondemand.sql.azuresynapse.net;Database=AnalyticsAXDB";
string dataSourceName = "sqlOnDemandDS";
AdlsContext adlsContext = new AdlsContext()
{
StorageAccount = storageAccount,
FileSytemName = rootFolder,
MSIAuth = true,
TenantId = tenantId
};
// Read Manifest metadata
Console.WriteLine("Reading Manifest metadata");
List <SQLMetadata> metadataList = new List <SQLMetadata>();
ManifestHandler.manifestToSQLMetadata(adlsContext, manifestName, localFolder, metadataList);
// convert metadata to DDL
Console.WriteLine("Converting metadata to DDL");
var statementsList = ManifestHandler.SQLMetadataToDDL(metadataList, "SynapseView", dataSourceName);
// Execute DDL
Console.WriteLine("Executing DDL");
SQLHandler sQLHandler = new SQLHandler(connectionString, tenantId);
var statements = new SQLStatements {
Statements = statementsList.Result
};
sQLHandler.executeStatements(statements);
Console.WriteLine(JsonConvert.SerializeObject(statements));
}
public IActionResult UpdatePersonalInfo(string firstName, string lastName, string emailAddress, string phoneNumber)
{
var tenantID = CookieHandler.GetCurrentUserID(Request.Cookies["AuthToken"]);
if (firstName != null && lastName != null && emailAddress != null && phoneNumber != null)
{
PersonalInformation info = Methods.Methods.UserInputPersonalInformation(tenantID, firstName, lastName, phoneNumber, emailAddress);
if (SQLStatements.UpdatePersonalInformation(info))
{
return(Json(new { success = true }));
}
return(Json(new { success = false, error = "DB Error" }));
}
else
{
return(Json(new { success = false, error = "Invalid URL input" }));
}
}
public async static Task <SQLStatements> CDMToSQL(AdlsContext adlsContext, string storageAccount, string rootFolder, string localFolder, string manifestName, string SAS, string pass, bool createDS)
{
SQLStatements statements = new SQLStatements();
List <SQLStatement> statementsList = new List <SQLStatement>();
var SQLHandler = new SQLHandler(System.Environment.GetEnvironmentVariable("SQL-On-Demand"));
var adlsURI = "https://" + storageAccount;
var sqlOnDemand = SQLHandler.createCredentialsOrDS(createDS, adlsURI, rootFolder, SAS, pass, dataSourceName);
await ManifestHandler.manifestToSQL(adlsContext, manifestName, localFolder, statementsList, createDS);
statements.Statements = statementsList;
SQLHandler.executeStatements(statements);
return(statements);
}
public IActionResult UpdateEmploymentInfo(string employer, string jobTitle)
{
var tenantID = CookieHandler.GetCurrentUserID(Request.Cookies["AuthToken"]);
EmploymentDetails details = new EmploymentDetails();
details.TenantID = tenantID;
details.Employer = employer;
details.JobTitle = jobTitle;
bool success = false;
success = SQLStatements.UpdateEmploymentDetails(details);
if (success)
{
return(Json(new { success = true, }));
}
else
{
return(Json(new { success = false, error = "An unknown error occured." }));
}
}
public void executeStatements(SQLStatements sqlStatements)
{
foreach (var s in sqlStatements.Statements)
{
SqlConnection conn = new SqlConnection(SQLConnectionStr);
conn.AccessToken = (new AzureServiceTokenProvider()).GetAccessTokenAsync("https://database.windows.net/", Tenant).Result;
conn.Open();
using (var command = new SqlCommand(s.Statement, conn))
{
try
{
command.ExecuteNonQuery();
s.Created = true;
}
catch (SqlException ex)
{
s.Created = false;
s.Detail = ex.Message;
}
}
conn.Close();
}
}
static void Main(string[] args)
{
//get data from config
string tenantId = ConfigurationManager.AppSettings.Get("TenantId"); //"979fd422-22c4-4a36-bea6-1cf87b6502dd";
string storageAccount = ConfigurationManager.AppSettings.Get("StorageAccount"); //"ftfinanced365fo.dfs.core.windows.net";
string accessKey = ConfigurationManager.AppSettings.Get("AccessKey");
string rootFolder = ConfigurationManager.AppSettings.Get("RootFolder"); //"/dynamics365-financeandoperations/finance.sandbox.operations.dynamics.com/";
string manifestFilePath = ConfigurationManager.AppSettings.Get("ManifestFilePath");
var targetDbConnectionString = ConfigurationManager.AppSettings.Get("TargetDbConnectionString"); //"Server=ftsasynapseworkspace-ondemand.sql.azuresynapse.net;Database=Finance_AXDB";
string dataSourceName = ConfigurationManager.AppSettings.Get("DataSourceName"); //"finance" ;
string DDLType = ConfigurationManager.AppSettings.Get("DDLType"); //"SynapseExternalTable";
string schema = ConfigurationManager.AppSettings.Get("Schema"); //"ChangeFeed";
string fileFormat = ConfigurationManager.AppSettings.Get("FileFormat"); //"CSV";
string convertToDateTimeStr = ConfigurationManager.AppSettings.Get("CovertDateTime"); //"CSV";
string TableNames = ConfigurationManager.AppSettings.Get("TableNames"); //"CSV";
NameValueCollection sAll = ConfigurationManager.AppSettings;
foreach (string s in sAll.AllKeys)
{
Console.WriteLine("Key: " + s + " Value: " + sAll.Get(s));
}
if (String.IsNullOrEmpty(manifestFilePath))
{
Console.WriteLine("Enter Manifest file relative path:(/Tables/Tables.manifest.cdm.json or /Tables/model.json)");
manifestFilePath = Console.ReadLine();
}
string manifestName = Path.GetFileName(manifestFilePath);
string localFolder = manifestFilePath.Replace(manifestName, "");
bool MSIAuth;
if (String.IsNullOrEmpty(accessKey))
{
MSIAuth = true;
}
else
{
MSIAuth = false;
}
AdlsContext adlsContext = new AdlsContext()
{
StorageAccount = storageAccount,
FileSytemName = rootFolder,
MSIAuth = MSIAuth,
TenantId = tenantId,
SharedKey = accessKey
};
// Read Manifest metadata
Console.WriteLine($"Reading Manifest metadata https://{storageAccount}{rootFolder}{manifestFilePath}");
bool convertDateTime = false;
if (convertToDateTimeStr.ToLower() == "true")
{
convertDateTime = true;
}
List <SQLMetadata> metadataList = new List <SQLMetadata>();
ManifestHandler.manifestToSQLMetadata(adlsContext, manifestName, localFolder, metadataList, convertDateTime);
// convert metadata to DDL
Console.WriteLine("Converting metadata to DDL");
var statementsList = ManifestHandler.SQLMetadataToDDL(metadataList, DDLType, schema, fileFormat, dataSourceName, TableNames);
// Execute DDL
Console.WriteLine("Executing DDL");
SQLHandler sQLHandler = new SQLHandler(targetDbConnectionString, tenantId);
var statements = new SQLStatements {
Statements = statementsList.Result
};
try
{
sQLHandler.executeStatements(statements);
foreach (var statement in statements.Statements)
{
Console.WriteLine(statement.Statement);
Console.WriteLine("Status:" + statement.Created);
Console.WriteLine("Detail:" + statement.Detail);
}
}
catch (Exception e)
{
Console.WriteLine("ERROR executing SQL");
Console.WriteLine(e.Message);
}
Console.WriteLine("Press any key to exit");
Console.ReadLine();
}
public void TriggerDrop(string triggerName)
{
Execute().ExecuteNonQuery(SQLStatements.TableTriggerDrop(triggerName, this));
}
public bool TriggerExists(string triggerName)
{
return(Execute().ExecuteScalar <bool>(SQLStatements.TableTriggerExists(triggerName, this)));
}
static void Main(string[] args)
{
//get data from config
string tenantId = ConfigurationManager.AppSettings.Get("TenantId"); //"979fd422-22c4-4a36-bea6-1cf87b6502dd";
string storageAccount = ConfigurationManager.AppSettings.Get("StorageAccount"); //"ftfinanced365fo.dfs.core.windows.net";
string rootFolder = ConfigurationManager.AppSettings.Get("RootFolder"); //"/dynamics365-financeandoperations/finance.sandbox.operations.dynamics.com/";
string localFolder = ConfigurationManager.AppSettings.Get("LocalFolder"); //"ChangeFeed";
string manifestName = ConfigurationManager.AppSettings.Get("ManifestName"); //"ChangeFeed";
var targetDbConnectionString = ConfigurationManager.AppSettings.Get("TargetDbConnectionString"); //"Server=ftsasynapseworkspace-ondemand.sql.azuresynapse.net;Database=Finance_AXDB";
string dataSourceName = ConfigurationManager.AppSettings.Get("DataSourceName"); //"finance" ;
string DDLType = ConfigurationManager.AppSettings.Get("DDLType"); //"SynapseExternalTable";
string schema = ConfigurationManager.AppSettings.Get("Schema"); //"ChangeFeed";
string fileFormat = ConfigurationManager.AppSettings.Get("FileFormat"); //"CSV";
NameValueCollection sAll = ConfigurationManager.AppSettings;
foreach (string s in sAll.AllKeys)
{
Console.WriteLine("Key: " + s + " Value: " + sAll.Get(s));
}
AdlsContext adlsContext = new AdlsContext()
{
StorageAccount = storageAccount,
FileSytemName = rootFolder,
MSIAuth = true,
TenantId = tenantId
};
// Read Manifest metadata
Console.WriteLine($"Reading Manifest metadata https://{storageAccount}{rootFolder}{localFolder}/{manifestName}.manifest.json");
List <SQLMetadata> metadataList = new List <SQLMetadata>();
ManifestHandler.manifestToSQLMetadata(adlsContext, manifestName, localFolder, metadataList);
// convert metadata to DDL
Console.WriteLine("Converting metadata to DDL");
var statementsList = ManifestHandler.SQLMetadataToDDL(metadataList, DDLType, schema, fileFormat, dataSourceName);
// Execute DDL
Console.WriteLine("Executing DDL");
SQLHandler sQLHandler = new SQLHandler(targetDbConnectionString, tenantId);
var statements = new SQLStatements {
Statements = statementsList.Result
};
try
{
sQLHandler.executeStatements(statements);
foreach (var statement in statements.Statements)
{
Console.WriteLine(statement.Statement);
Console.WriteLine("Status:" + statement.Created);
Console.WriteLine("Detail:" + statement.Detail);
}
}
catch
{
Console.WriteLine("ERROR executing SQL");
}
Console.WriteLine("Press any key to exit");
Console.ReadLine();
}
