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(); }