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