public void TestCreateDataPayload() { List <Property> _prop = new List <Property>(); DataResponse newRecord = new DataResponse() { Id = "HelloData", Name = "Hello.png", User = "", Created_on = DateTime.Now.ToString(), Edited_on = DateTime.Now.ToString(), Extension = ".png", MimeType = "application/image", Size = 111, Type = "IMAGE", Url = "http://localhost/uploads/data/Hello.png", FilePath = "", Properties = _prop }; DataResponse createdRecord = DataPayload.Create(newRecord); Assert.Equal(newRecord, createdRecord); }
public async Task <IActionResult> PostSqlAsync() { DataResponse newRecord = new DataResponse(); string reqBody = ""; string reqSql = ""; string dirFullpath = $"{DirectoryHelper.GetDataDirectoryPath()}"; string newFile = "DataHub_" + DateTime.Now.Ticks.ToString() + ".csv"; string _filePath = Path.Combine(dirFullpath, newFile); long fileSize = 0L; List <string> resultRows = new List <string>(); StringBuilder csvBuilder = new StringBuilder(); List <Property> _props = new List <Property>(); int numberOfColumns = 0; // using (var reader = new StreamReader(Request.Body)) { reqBody = reader.ReadToEnd().ToString(); } JObject jsonBody = JObject.Parse(reqBody); reqSql = jsonBody["sql"].ToString(); #region ODBC string zmodId = ZSSettingPayload.GetUserNameOrEmail(HttpContext); var settings = ZSSettingPayload.GetDataHubInfo(zmodId); string cnn = $"Driver={settings.Item5};ConnectionType=Direct;HOST={settings.Item1};PORT={settings.Item4};AuthenticationType=Plain;UID={settings.Item2};PWD={settings.Item3};SSL={settings.Item6}"; Console.WriteLine($"DATAHUB >>>>>>>>>>>>>>>>>>>>> { cnn }"); //using (OdbcConnection connection = new OdbcConnection("Driver=Dremio Connector;ConnectionType=Direct;HOST=dremio-demo.westeurope.cloudapp.azure.com;PORT=31010;AuthenticationType=Plain;UID=demo;PWD=iug2019#riga")) using (OdbcConnection connection = new OdbcConnection(cnn)) { try { connection.Open(); Console.WriteLine("DATAHUB CONNECTION ESTABLISHED..."); OdbcCommand DbCommand = connection.CreateCommand(); Console.WriteLine(reqSql); DbCommand.CommandText = reqSql; List <string> columns = new List <string>(); OdbcDataReader reader = DbCommand.ExecuteReader(); numberOfColumns = reader.FieldCount; //add header column name for (int i = 0; i < reader.FieldCount; i++) { columns.Add(reader.GetName(i)); csvBuilder.Append(reader.GetName(i)); if (i < reader.FieldCount - 1) { csvBuilder.Append(","); } } resultRows.Add(csvBuilder.ToString()); csvBuilder.Clear(); // //add rows while (reader.Read()) { csvBuilder.Clear(); for (int i = 0; i < reader.FieldCount; i++) { if (!reader.IsDBNull(i)) { csvBuilder.Append(reader[i]); } else { csvBuilder.Append("No Data"); } if (i < reader.FieldCount - 1) { csvBuilder.Append(","); } } resultRows.Add(csvBuilder.ToString()); } using (StreamWriter writer = new StreamWriter(_filePath)) { foreach (var line in resultRows) { writer.WriteLine(line); } writer.Flush(); fileSize = writer.BaseStream.Length; } string _url = DirectoryHelper.GetDataUrl(newFile); await Task.FromResult(0); // string type = "CSV"; //get properties row and column count int[] csvProps = CsvHelper.GetCsvRowColumnCount(dirFullpath + @"/" + newFile); _props.Add(new Property { key = "Number of rows", value = resultRows.Count().ToString() }); _props.Add(new Property { key = "Number of columns", value = numberOfColumns.ToString() }); newRecord = new DataResponse() { Created_on = DateTime.Now.ToString(), Edited_on = DateTime.Now.ToString(), Extension = "CSV", FilePath = _filePath, Id = newFile, MimeType = "text/csv", Name = newFile.Replace(".CSV", ""), Properties = _props, Size = fileSize, Type = type, Url = _url, DateCreated = DateTime.Now }; DataPayload.Create(newRecord); } catch (Exception e) { // return error message Console.WriteLine("DataHub ERROR:>>>>" + e.Message); //cleanup the file created } } #endregion // return(Json(newRecord)); }
public static bool ScanDataDirectory() { bool result = false; string fileName, _url, _fullName, fileContent, fileExt = ""; Console.WriteLine("Dir Loc=" + DirectoryHelper.fileUploadDirectoryPath); var zmodDir = new ZmodDirectory(DirectoryHelper.fileUploadDirectoryPath); //seed data - subdir, csv, img and json #region DATA - SUBDIR foreach (var subdir in Directory.GetDirectories(DirectoryHelper.GetDataDirectoryPath())) { string folderName = Path.GetFileName(subdir); string _createdOn = Directory.GetCreationTime(subdir).ToString(); List <Property> _props = new List <Property>(); _props.Add(new Property { key = "Subdirectories", value = DirectoryHelper.CountDirectories(subdir).ToString() }); _props.Add(new Property { key = "Files", value = DirectoryHelper.CountFiles(subdir).ToString() }); DataResponse newRecord = new DataResponse() { Created_on = Directory.GetCreationTime(subdir).ToString(), Edited_on = Directory.GetLastWriteTime(subdir).ToString(), Extension = "", Type = "FOLDER", FilePath = subdir, Id = folderName, MimeType = "", Name = folderName, Properties = _props, DateCreated = Directory.GetCreationTime(subdir) }; // DataPayload.Create(newRecord); } #endregion #region DATA - CSV foreach (var item in zmodDir.CsvFiles) { List <Property> _props = new List <Property>(); fileName = item.Value.info.Name; fileExt = "csv"; _fullName = item.Value.info.FullName; _fullName = _fullName.Substring(_fullName.IndexOf("Data")).Remove(0, 5); _url = DirectoryHelper.GetDataUrl(_fullName); //get properties row and column count int[] csvProps = CsvHelper.GetCsvRowColumnCount(item.Value.info.FullName); _props.Add(new Property { key = "Number of Rows", value = csvProps[0].ToString() }); _props.Add(new Property { key = "Number of Columns", value = csvProps[1].ToString() }); // DataResponse newRecord = new DataResponse() { Created_on = item.Value.info.CreationTime.ToString(), Edited_on = item.Value.info.LastWriteTime.ToString(), Extension = fileExt, FilePath = item.Value.info.FullName, Id = fileName.Replace($".{fileExt}", ""), MimeType = "text/csv", Name = fileName, Properties = _props, Size = item.Value.info.Length, Type = "CSV", Url = _url, User = "", DateCreated = item.Value.info.CreationTime }; // DataPayload.Create(newRecord); } #endregion #region DATA - IMAGES foreach (var item in zmodDir.ImageFiles) { List <Property> _props = new List <Property>(); fileName = item.Value.info.Name; fileExt = item.Value.info.Extension.Remove(0, 1); _fullName = item.Value.info.FullName; _fullName = _fullName.Substring(_fullName.IndexOf("Data")).Remove(0, 5); _url = DirectoryHelper.GetDataUrl(_fullName).Replace("\\", "/"); //get properties try { // using (var image = new Bitmap(System.Drawing.Image.FromFile(item.Value.info.FullName))) using (var image = new Bitmap(item.Value.info.FullName)) { _props.Add(new Property { key = "Width", value = image.Width.ToString() + " px" }); _props.Add(new Property { key = "Height", value = image.Height.ToString() + " px" }); image.Dispose(); } // DataResponse newRecord = new DataResponse() { Created_on = item.Value.info.CreationTime.ToString(), Edited_on = item.Value.info.LastWriteTime.ToString(), Extension = fileExt, FilePath = item.Value.info.FullName, Id = fileName.Replace($".{fileExt}", ""), MimeType = $"image/{fileExt}", Name = fileName, Properties = _props, Size = item.Value.info.Length, Type = "IMAGE", Url = _url, User = "", DateCreated = item.Value.info.CreationTime }; // DataPayload.Create(newRecord); } catch (Exception ex) { Console.WriteLine(ex.StackTrace); } } #endregion #region DATA - JSON foreach (var item in zmodDir.JsonFiles) { List <Property> _props = new List <Property>(); fileName = item.Value.info.Name; fileExt = "json"; _fullName = item.Value.info.FullName; _fullName = _fullName.Substring(_fullName.IndexOf("Data")).Remove(0, 5); _url = DirectoryHelper.GetDataUrl(_fullName); fileContent = ""; //read json file from filestream if (!string.IsNullOrEmpty(fileName)) { using (StreamReader reader = new StreamReader(item.Value.info.FullName)) { fileContent = reader.ReadToEnd(); } } //parse try { if (!string.IsNullOrEmpty(fileContent)) { JsonTextReader reader = new JsonTextReader(new StringReader(fileContent)); int objCtr = 0; while (reader.Read()) { if (reader.TokenType == JsonToken.EndObject) { objCtr++; } } _props.Add(new Property { key = "Number of Objects", value = objCtr.ToString() }); // DataResponse newRecord = new DataResponse() { Created_on = item.Value.info.CreationTime.ToString(), Edited_on = item.Value.info.LastWriteTime.ToString(), Extension = fileExt, FilePath = item.Value.info.FullName, Id = fileName.Replace($".{fileExt}", ""), MimeType = "application/json", Name = fileName, Properties = _props, Size = item.Value.info.Length, Type = "JSON", Url = _url, User = "", DateCreated = item.Value.info.CreationTime }; // DataPayload.Create(newRecord); } } catch (Exception ex) { //TODO: logger string err = ex.StackTrace; } } #endregion #region DATA - MP4 foreach (var item in zmodDir.VideoFiles) { List <Property> _props = new List <Property>(); fileName = item.Value.info.Name; fileExt = item.Value.info.Extension.Remove(0, 1); _fullName = item.Value.info.FullName; _fullName = _fullName.Substring(_fullName.IndexOf("Data")).Remove(0, 5); _url = DirectoryHelper.GetDataUrl(_fullName); //get properties try { DataResponse newRecord = new DataResponse() { Created_on = item.Value.info.CreationTime.ToString(), Edited_on = item.Value.info.LastWriteTime.ToString(), Extension = fileExt, FilePath = item.Value.info.FullName, Id = fileName.Replace($".{fileExt}", ""), MimeType = $"video/{fileExt}", Name = fileName, Properties = _props, Size = item.Value.info.Length, Type = "VIDEO", Url = _url, User = "", DateCreated = item.Value.info.CreationTime }; // DataPayload.Create(newRecord); } catch (Exception ex) { //TODO: logger string err = ex.StackTrace; } } #endregion #region DATA - TEXT foreach (var item in zmodDir.TextFiles) { List <Property> _props = new List <Property>(); fileName = item.Value.info.Name; fileExt = item.Value.info.Extension.Remove(0, 1); _fullName = item.Value.info.FullName; _fullName = _fullName.Substring(_fullName.IndexOf("Data")).Remove(0, 5); _url = DirectoryHelper.GetDataUrl(_fullName); //get properties try { DataResponse newRecord = new DataResponse() { Created_on = item.Value.info.CreationTime.ToString(), Edited_on = item.Value.info.LastWriteTime.ToString(), Extension = fileExt, FilePath = item.Value.info.FullName, Id = fileName.Replace($".{fileExt}", ""), MimeType = $"text/plain", Name = fileName, Properties = _props, Size = item.Value.info.Length, Type = "TEXT", Url = _url, User = "", DateCreated = item.Value.info.CreationTime }; // DataPayload.Create(newRecord); } catch (Exception ex) { Debug.WriteLine(ex.StackTrace); } } #endregion return(result); }