public ActionResult Post(DataSourceModel dataSource)
{
logger.Info($"POST: {Request.Path} called");
logger.Info("Storing Datasource: Id={0}, DeviceId={1}, Type={2}, Description={3}",
dataSource.Id,
dataSource.DeviceId,
//dataSource.ChannelId,
dataSource.Description);
if (!ModelState.IsValid)
{
return(BadRequest(ModelState));
}
try
{
datasourceRepo.Add(dataSource);
}
catch (DbUpdateException ex)
{
logger.Error(ex);
return(StatusCode(StatusCodes.Status500InternalServerError, ex.InnerException.InnerException.Message));
}
logger.Info("Datasource added: Id={0}, DeviceId={1}, Type={2}, Description={3}",
dataSource.Id,
dataSource.DeviceId,
// dataSource.ChannelId,
dataSource.Description);
return(Created(Request.Path, dataSource));
}
public List <DataSourceModel> ListDataSources()
{
List <DataSourceModel> dataSources = new List <DataSourceModel>();
List <int> dataSourceIds = new List <int>();
using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.DatabaseConnectionString))
{
conn.Open();
using (SqlCommand command = new SqlCommand("SELECT DS_ID FROM DATA_SOURCES", conn))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
dataSourceIds.Add((int)reader["DS_ID"]);
}
}
}
}
foreach (int ds_id in dataSourceIds)
{
try
{
DataSourceModel dataSource = GetDataSource(ds_id);
dataSources.Add(dataSource);
}
catch (Exception ex)
{
Trace.WriteLine(String.Format("Error loading data source {0} - {1}", ds_id, ex.Message));
}
}
return(dataSources);
}
public void SaveDataSource(DataSourceModel DataSource, UserModel User)
{
// Is this a new data source?
using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.DatabaseConnectionString))
{
conn.Open();
if (DataSource.DataSourceId == 0)
{
String query = "INSERT INTO DATA_SOURCES";
query += " (DS_NAME, DS_CREATED_DATE, DS_CREATED_BY, DS_DELETED)";
query += " VALUES";
query += " (@DS_NAME, GETDATE(), @DS_CREATED_BY, 0)";
using (SqlCommand command = new SqlCommand(query, conn))
{
command.Parameters.AddWithValue("DS_NAME", DataSource.Name);
command.Parameters.AddWithValue("DS_CREATED_BY", User.Email);
command.ExecuteNonQuery();
}
}
else
{
throw new NotImplementedException();
String query = "UPDATE DATA_SOURCES";
using (SqlCommand command = new SqlCommand(query, conn))
{
command.Parameters.AddWithValue("DS_NAME", DataSource.Name);
command.Parameters.AddWithValue("DS_CREATED_BY", User.Email);
command.ExecuteNonQuery();
}
}
}
}
public DataSourceModel GetDataSource(int DataSourceId)
{
DataSourceModel dataSource = new DataSourceModel();
SqlCommand command;
SqlDataReader reader;
if (_conn.State != ConnectionState.Open)
{
_conn.Open();
}
command = new SqlCommand("select * from data_sources_tables where ds_id = @DataSourceId", _conn);
command.Parameters.AddWithValue("DataSourceId", DataSourceId);
reader = command.ExecuteReader();
dataSource.Tables = new List <TableModel>();
while (reader.Read())
{
TableModel table = ParseTableFromReader(reader);
dataSource.Tables.Add(table);
}
reader.Close();
command = new SqlCommand("select * from data_sources where ds_id = @DataSourceId", _conn);
command.Parameters.AddWithValue("DataSourceId", DataSourceId);
reader = command.ExecuteReader();
reader.Read();
dataSource.Name = (string)reader["DS_NAME"];
reader.Close();
_conn.Close();
return(dataSource);
}
/// <summary>
/// Initializes a new instance of the <see cref="frmConnection" /> class.
/// </summary>
/// <param name="model">The model.</param>
public frmConnection(DataSourceModel model) : this()
{
this.Model = model;
var builder = model.DataSource?.GetConnectionStringBuilder();
if (builder != null)
{
builder.TryGetValue("Provider", out object value);
cbProviders.EditValue = Model.DataSource.ProviderName;
builder.TryGetValue("User Id", out value);
txtUserId.Text = value as string;
builder.TryGetValue("Password", out value);
txtPassword.Text = value as string;
builder.TryGetValue("Data Source", out value);
btnDataSource.Text = value as string;
builder.TryGetValue("Extended Properties", out value);
if (string.IsNullOrEmpty(value as string) == false)
{
txtExt.Text = string.Format("'{0}'", value);
}
else
{
txtExt.Text = value as string;
}
}
Cursor = Cursors.Default;
}
public SequenceToSequenceDataTableAdaptor(ILinearAlgebraProvider lap,
ILearningContext learningContext, IDataTable dataTable, INode input,
DataSourceModel dataSource) : base(lap, learningContext, dataTable)
{
_Initialise(dataTable);
_input = input;
_inputSize = dataSource.InputSize;
_outputSize = dataSource.OutputSize;
}
/// <summary>
/// Add a list of elements as a DataSource
/// </summary>
/// <param name="context"></param>
/// <param name="key"></param>
/// <param name="elements"></param>
/// <returns></returns>
public static ContextModel AddCollection(this ContextModel context, string key, params ContextModel[] elements)
{
var element = new DataSourceModel()
{
Items = elements.ToList()
};
context.AddItem(key, element);
return(context);
}
//
//====================================================================================================
//
public override int AddContent(string contentName, string sqlTableName, string dataSourceName)
{
var tmpList = new List <string> {
};
DataSourceModel dataSource = DataSourceModel.createByUniqueName(cp, dataSourceName, ref tmpList);
return(ContentMetadataModel.verifyContent_returnId(cp.core, new Models.Domain.ContentMetadataModel {
dataSourceName = dataSource.name,
tableName = sqlTableName,
name = contentName
}, "Adding content [" + contentName + "], sqlTableName [" + sqlTableName + "]"));
}
/// <summary>
/// Initializes a new instance of the <see cref="frmSchemaEditor"/> class.
/// </summary>
/// <param name="model">The model.</param>
public frmSchemaEditor(DataSourceModel model, string queryName, bool isNew) : this()
{
this.IsNew = isNew;
this.model = model;
if (isNew == false)
{
tableSchema = model.TableSchemas.Where(w => w.TableName == queryName).SingleOrDefault();
txtQuery.Text = tableSchema.Query.Replace("\r\n", "\n").Replace("\n", "\r\n");
txtQueryName.EditValue = queryName;
txtQueryName.Enabled = false;
}
}
private async void FileButton_Click(object sender, RoutedEventArgs e)
{
var picker = new Windows.Storage.Pickers.FileOpenPicker();
picker.ViewMode = Windows.Storage.Pickers.PickerViewMode.List;
picker.SuggestedStartLocation = Windows.Storage.Pickers.PickerLocationId.DocumentsLibrary;
picker.FileTypeFilter.Add(".csv");
Windows.Storage.StorageFile file = await picker.PickSingleFileAsync();
if (file != null)
{
StorageFolder folder = ApplicationData.Current.TemporaryFolder;
var copiedFile = await file.CopyAsync(folder, file.Name, NameCollisionOption.ReplaceExisting);
var r = await DataSourceModel.ReadFileForDrawItem(copiedFile);
if (r != null)
{
if (r.ItemList.Count > 200 || r.ItemList.Count == 0)
{
ContentDialog dialog = new ContentDialog();
dialog.Title = Strings.Resources.DataSetting_Dialog_Error;
dialog.Content = Strings.Resources.DataSetting_Dialog_Exceed;
dialog.PrimaryButtonText = Strings.Resources.DataSetting_Dialog_Ok;
await dialog.ShowAsync();
}
else
{
this.FilePathText.Text = copiedFile.Name;
ApplyDataSource();
return;
}
}
else
{
ContentDialog dialog = new ContentDialog();
dialog.Title = Strings.Resources.DataSetting_Dialog_Error;
dialog.Content = Strings.Resources.DataSetting_Dialog_FileError;
dialog.PrimaryButtonText = Strings.Resources.DataSetting_Dialog_Ok;
await dialog.ShowAsync();
}
SetDataModel();
}
}
public void ParseClassAndProperty()
{
//// Arrange
var datasource = "Mynamespace.MyClass.MyProp";
//// Act
var result = DataSourceModel.Parse(datasource);
//// Assert
Assert.That(result, Is.Not.Null);
Assert.That(result.ClassName, Is.EqualTo("Mynamespace.MyClass"));
Assert.That(result.AssemblyName, Is.Null);
Assert.That(result.MemberName, Is.EqualTo("MyProp"));
Assert.That(result.MemberType, Is.EqualTo(MemberTypes.Property));
}
public void ParseClassMethodAndAssembly()
{
//// Arrange
var datasource = "Mynamespace.MyClass.MyMethod(), MyName.MyAssembly";
//// Act
var result = DataSourceModel.Parse(datasource);
//// Assert
Assert.That(result, Is.Not.Null);
Assert.That(result.ClassName, Is.EqualTo("Mynamespace.MyClass"));
Assert.That(result.AssemblyName, Is.EqualTo("MyName.MyAssembly"));
Assert.That(result.MemberName, Is.EqualTo("MyMethod"));
Assert.That(result.MemberType, Is.EqualTo(MemberTypes.Method));
}
/// <summary>
/// Builds the SQL.
/// </summary>
/// <param name="source">The source.</param>
/// <param name="fields">The fields.</param>
/// <param name="strings">The strings.</param>
/// <returns>System.String.</returns>
public virtual string BuildSql(DataSourceModel source, string[] fields, string[] inValues, string filter)
{
if (inValues == null)
{
return(BuildSql(source.Key, fields, source.SelectedTable, string.Empty, source.MaxRows, filter));
}
if (source.KeyType == typeof(string) || source.KeyType == typeof(DateTime) || source.KeyType == null)
{
return(BuildSql(source.Key, fields, source.SelectedTable, string.Empty, source.MaxRows, source.Key, inValues, filter));
}
var ints = Array.ConvertAll(inValues, int.Parse);
return(BuildSql(source.Key, fields, source.SelectedTable, string.Empty, source.MaxRows, source.Key, ints, filter));
}
/// <summary>
/// Logs the query.
/// </summary>
/// <param name="model">The model.</param>
/// <param name="select">The select.</param>
/// <param name="side">The side.</param>
private void LogQuery(DataSourceModel model, string select, string side)
{
try
{
if (Directory.Exists(@".\logs") == false)
{
Directory.CreateDirectory(@".\logs");
}
File.WriteAllText(string.Format(@".\logs\{0}Query_{1}_{2}.sql", side, model.SelectedTable, model.Key), select);
}
catch (Exception)
{
return;
}
}
/// <summary>
/// Creating the data source
/// </summary>
/// <param name="org">The Organization code for the service owner</param>
/// <param name="service">The service code for the current service</param>
/// <param name="edition">The edition code for the current service</param>
/// <param name="description">Description of the URL.</param>
/// <param name="url">The rest url field.</param>
/// <returns>Returns a DataSourceModel</returns>
public DataSourceModel Create(string org, string service, string edition, string description, string url)
{
var file = GetFilePath(org, service, edition);
var dataSourceModel = GetDataSourceRoot(file);
var newItem = new DataSourceModel
{
Id = GetNextId(dataSourceModel).ToString(),
Description = description,
Url = url,
Opprettet = DateTime.Now
};
dataSourceModel.UrlResources.DataSourceUrls.Add(newItem.Id, newItem);
Save(file, dataSourceModel);
return(newItem);
}
/// <summary>
/// Creates an adaptive data source from a serialised model
/// </summary>
/// <param name="dataTable">Data that will be sent to the preliminary graph</param>
/// <param name="dataSource">The serialised preliminary graph</param>
/// <param name="learningContext">Learning context to use while training the preliminary graph</param>
/// <returns></returns>
public IDataSource CreateDataSource(IDataTable dataTable, DataSourceModel dataSource, ILearningContext learningContext = null)
{
var input = this.CreateFrom(dataSource.Graph);
var columns = dataTable.Columns;
if (columns.Count == 2)
{
var column1 = columns[0].Type;
var column2 = columns[1].Type;
if (column1 == ColumnType.Matrix && column2 == ColumnType.Matrix)
{
return(new SequenceToSequenceDataTableAdaptor(_lap, learningContext, dataTable, input, dataSource));
}
}
throw new ArgumentException($"{nameof(dataTable)} does not contain a recognised data format");
}
public bool AddPlan(PlanModel plan, DataSourceModel ds)
{
string urlPath = "import/plans";
plan.DataSource = ds.Uuid;
string json = JsonConvert.SerializeObject(plan);
ApiResponse resp = CallApi(urlPath, "POST", json);
if (resp.Success)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// Update the file.
/// </summary>
/// <param name="org">The Organization code for the service owner</param>
/// <param name="service">The service code for the current service</param>
/// <param name="edition">The edition code for the current service</param>
/// <param name="model">Reference to the DataSourceModel</param>
public void Update(string org, string service, string edition, DataSourceModel model)
{
if (string.IsNullOrWhiteSpace(model?.Id))
{
throw new ArgumentException("Id missin'", nameof(model));
}
var file = GetFilePath(org, service, edition);
var root = GetDataSourceRoot(file);
var current = root?.UrlResources?.DataSourceUrls.Single(x => x.Value?.Id == model.Id);
if (current?.Value == null || string.IsNullOrEmpty(current?.Key))
{
throw new Exception("Finner ikke " + model.Id);
}
root.UrlResources.DataSourceUrls[current?.Key] = model;
Save(file, root);
}
private async void ApplyDataSource()
{
bool canProcess = DrawData.Instance.DrawHistory.Count == 0;
if (!canProcess)
{
ContentDialog dialog = new ContentDialog();
dialog.Title = Strings.Resources.DataSetting_Dialog_Warning;
dialog.Content = Strings.Resources.DataSetting_Dialog_Reload;
dialog.PrimaryButtonText = Strings.Resources.DataSetting_Dialog_Contiune;
dialog.SecondaryButtonText = Strings.Resources.DataSetting_Dialog_No;
canProcess = await dialog.ShowAsync() == ContentDialogResult.Primary;
}
if (canProcess)
{
if (this.NumberSource.IsChecked.HasValue && this.NumberSource.IsChecked.Value)
{
DataSourceModel model = new DataSourceModel()
{
Start = (int)RangeSelectorControl.RangeMin,
End = (int)RangeSelectorControl.RangeMax,
Prefix = this.PrefixLabel.Text
};
SettingData.Instance.DrawDataSource = model;
}
else
{
DataSourceModel model = new DataSourceModel()
{
FilePath = this.FilePathText.Text,
};
SettingData.Instance.DrawDataSource = model;
}
}
else
{
this.SetDataModel();
}
}
/// <summary>
/// Initializes a new instance of the <see cref="frmValidator"/> class.
/// </summary>
/// <param name="model">The model.</param>
/// <param name="data">The data.</param>
public frmValidator(CompareModel model, DataSourceModel data) : this()
{
this.model = model;
this.dataSource = data;
gridControl1.DataSource = data.SelectedSchema().Fields;
gridView1.BestFitWidth(false, true);
RefreshSummary();
this.Text = data.DataSource.GetConnectionStringBuilder()["Data Source"]?.ToString();
gridControl3.DataSource = eventItems.ToList();
QueryBuilder query = model.Source.DataSource.GetQueryBuilder();
if (string.IsNullOrEmpty(data.SelectedSchema().Query))
{
data.SelectedSchema().Query = query.BuildSql(model.Source,
data.SelectedSchema().Fields.Select(s => s.Name).ToArray(), null, null);
}
memoEdit1.Text = data.SelectedSchema().Query.Replace("\r\n", "\n").Replace("\n", "\r\n");
memoEdit1.ReadOnly = true;
}
public bool AddCustomer(CustomerModel cust, DataSourceModel ds)
{
if (cust == null)
{
return(false);
}
string urlPath = "import/customers";
cust.Data_Source_Uuid = ds.Uuid;
string json = JsonConvert.SerializeObject(cust);
ApiResponse resp = CallApi(urlPath, "POST", json);
if (resp.Success)
{
return(true);
}
else
{
return(false);
}
}
public DataSourceModel GetDataSource(int DataSourceId)
{
DataSourceModel dataSource = new DataSourceModel();
using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.DatabaseConnectionString))
{
conn.Open();
using (SqlCommand command = new SqlCommand("select * from data_sources_tables where ds_id = @DataSourceId", conn))
{
command.Parameters.AddWithValue("DataSourceId", DataSourceId);
using (SqlDataReader reader = command.ExecuteReader())
{
dataSource.Tables = new List <TableModel>();
while (reader.Read())
{
TableModel table = ParseTableFromReader(reader);
dataSource.Tables.Add(table);
}
}
}
using (SqlCommand command = new SqlCommand("select * from data_sources where ds_id = @DataSourceId", conn))
{
command.Parameters.AddWithValue("DataSourceId", DataSourceId);
using (SqlDataReader reader = command.ExecuteReader())
{
reader.Read();
dataSource.Name = (string)reader["DS_NAME"];
dataSource.DataSourceId = (int)reader["DS_ID"];
dataSource.DataSourceIdGuid = (Guid)reader["DS_ID_GUID"];
}
}
}
return(dataSource);
}
//
//===========================================================================
//
public static string get(CoreController core, AdminDataModel adminData, IndexConfigClass indexConfig, PermissionController.UserContentPermissions userContentPermissions, string sql, DataSourceModel dataSource, Dictionary <string, bool> FieldUsedInColumns, Dictionary <string, bool> IsLookupFieldValid)
{
try {
bool allowDelete = (adminData.adminContent.allowDelete) && (userContentPermissions.allowDelete) && (indexConfig.allowDelete);
var DataTable_HdrRow = new StringBuilder("<tr>");
//
// Row Number Column
DataTable_HdrRow.Append("<td width=20 align=center valign=bottom class=\"small ccAdminListCaption\">Row</td>");
//
// Edit Column
DataTable_HdrRow.Append("<td width=20 align=center valign=bottom class=\"small ccAdminListCaption\">Edit</td>");
//
// Delete Select Box Columns
if (!allowDelete)
{
DataTable_HdrRow.Append("<td width=20 align=center valign=bottom class=\"small ccAdminListCaption\"><input TYPE=CheckBox disabled=\"disabled\"></td>");
}
else
{
DataTable_HdrRow.Append("<td width=20 align=center valign=bottom class=\"small ccAdminListCaption\"><input TYPE=CheckBox OnClick=\"CheckInputs('DelCheck',this.checked);\"></td>");
}
//
// -- create header
int ColumnWidthTotal = 0;
foreach (var column in indexConfig.columns)
{
if (column.Width < 1)
{
column.Width = 1;
}
ColumnWidthTotal += column.Width;
}
foreach (var column in indexConfig.columns)
{
//
// ----- print column headers - anchored so they sort columns
//
int ColumnWidth = encodeInteger((100 * column.Width) / (double)ColumnWidthTotal);
//
// if this is a current sort ,add the reverse flag
//
StringBuilder buttonHref = new StringBuilder();
buttonHref.Append("/" + core.appConfig.adminRoute + "?" + rnAdminForm + "=" + AdminFormIndex + "&SetSortField=" + column.Name + "&RT=0&" + RequestNameTitleExtension + "=" + GenericController.encodeRequestVariable(adminData.titleExtension) + "&cid=" + adminData.adminContent.id + "&ad=" + adminData.ignore_legacyMenuDepth);
if (!indexConfig.sorts.ContainsKey(column.Name))
{
buttonHref.Append("&SetSortDirection=1");
}
else
{
switch (indexConfig.sorts[column.Name].direction)
{
case 1: {
buttonHref.Append("&SetSortDirection=2");
break;
}
case 2: {
buttonHref.Append("&SetSortDirection=0");
break;
}
default: {
// nothing
break;
}
}
}
//
// -- column header includes WherePairCount
if (!adminData.wherePair.Count.Equals(0))
{
int ptr = 0;
foreach (var kvp in adminData.wherePair)
{
if (!string.IsNullOrWhiteSpace(kvp.Key))
{
buttonHref.Append("&wl" + ptr + "=" + GenericController.encodeRequestVariable(kvp.Value));
buttonHref.Append("&wr" + ptr + "=" + GenericController.encodeRequestVariable(kvp.Value));
ptr++;
}
}
}
string buttonFace = adminData.adminContent.fields[column.Name.ToLowerInvariant()].caption;
buttonFace = GenericController.strReplace(buttonFace, " ", " ");
string SortTitle = "Sort A-Z";
//
if (indexConfig.sorts.ContainsKey(column.Name))
{
string sortSuffix = ((indexConfig.sorts.Count < 2) ? "" : indexConfig.sorts[column.Name].order.ToString());
switch (indexConfig.sorts[column.Name].direction)
{
case 1: {
buttonFace = iconArrowDown + sortSuffix + " " + buttonFace;
SortTitle = "Sort Z-A";
break;
}
case 2: {
buttonFace = iconArrowUp + sortSuffix + " " + buttonFace;
SortTitle = "Remove Sort";
break;
}
default: {
// nothing
break;
}
}
}
if (indexConfig.allowColumnSort)
{
buttonFace = HtmlController.a(buttonFace, new CPBase.BaseModels.HtmlAttributesA()
{
title = SortTitle,
href = buttonHref.ToString(),
@class = "ccAdminListCaption"
});
}
adminData.buttonObjectCount += 1;
DataTable_HdrRow.Append("<td width=\"" + ColumnWidth + "%\" valign=bottom align=left class=\"small ccAdminListCaption\">");
DataTable_HdrRow.Append(buttonFace);
DataTable_HdrRow.Append("</td>");
}
DataTable_HdrRow.Append("</tr>");
//
// -- generic admin url for edit and add links
string adminEditPresetArgQsList = "";
string adminUrlBase = "\\" + core.appConfig.adminRoute + "?" + rnAdminAction + "=" + Constants.AdminActionNop + "&cid=" + adminData.adminContent.id + "&" + RequestNameTitleExtension + "=" + GenericController.encodeRequestVariable(adminData.titleExtension) + "&ad=" + adminData.ignore_legacyMenuDepth + "&" + rnAdminSourceForm + "=" + adminData.adminForm + "&" + rnAdminForm + "=" + AdminFormEdit;
if (!adminData.wherePair.Count.Equals(0))
{
int WhereCount = 0;
foreach (var kvp in adminData.wherePair)
{
adminEditPresetArgQsList += "&" + encodeRequestVariable(kvp.Key) + "=" + GenericController.encodeRequestVariable(kvp.Value);
WhereCount++;
}
adminUrlBase += adminEditPresetArgQsList;
}
//
// -- output data rows
var dataTableRows = new StringBuilder();
string rowColor = "";
int rowNumber = 0;
int rowNumberLast = 0;
using (var csData = new CsModel(core)) {
if (csData.openSql(sql, dataSource.name, indexConfig.recordsPerPage, indexConfig.pageNumber))
{
rowNumber = indexConfig.recordTop;
rowNumberLast = indexConfig.recordTop + indexConfig.recordsPerPage;
//
// --- Print out the records
while ((csData.ok()) && (rowNumber < rowNumberLast))
{
int recordId = csData.getInteger("ID");
if (rowColor == "class=\"ccAdminListRowOdd\"")
{
rowColor = "class=\"ccAdminListRowEven\"";
}
else
{
rowColor = "class=\"ccAdminListRowOdd\"";
}
//
// -- new row
dataTableRows.Append(Environment.NewLine + "<tr>");
//
// --- row number column
dataTableRows.Append("<td align=right " + rowColor + ">" + (rowNumber + 1).ToString() + "</td>");
//
// --- edit column
dataTableRows.Append("<td align=center " + rowColor + ">" + getRecordEditAnchorTag(adminUrlBase + "&id=" + recordId) + "</td>");
//
// --- Delete Checkbox Columns
if (allowDelete)
{
dataTableRows.Append("<td align=center " + rowColor + "><input TYPE=CheckBox NAME=row" + rowNumber + " VALUE=1 ID=\"DelCheck\"><input type=hidden name=rowid" + rowNumber + " VALUE=" + recordId + "></span></td>");
}
else
{
dataTableRows.Append("<td align=center " + rowColor + "><input TYPE=CheckBox disabled=\"disabled\" NAME=row" + rowNumber + " VALUE=1><input type=hidden name=rowid" + rowNumber + " VALUE=" + recordId + "></span></td>");
}
//
// --- field columns
foreach (var column in indexConfig.columns)
{
string columnNameLc = column.Name.ToLowerInvariant();
if (FieldUsedInColumns.ContainsKey(columnNameLc))
{
if (FieldUsedInColumns[columnNameLc])
{
dataTableRows.Append((Environment.NewLine + "<td valign=\"middle\" " + rowColor + " align=\"left\">" + SpanClassAdminNormal));
dataTableRows.Append(getGridCell(core, adminData, column.Name, csData, IsLookupFieldValid[columnNameLc], GenericController.toLCase(adminData.adminContent.tableName) == "ccemail"));
dataTableRows.Append((" </span></td>"));
}
}
}
dataTableRows.Append(("\n </tr>"));
csData.goNext();
rowNumber = rowNumber + 1;
}
dataTableRows.Append("<input type=hidden name=rowcnt value=" + rowNumber + ">");
//
// --- print out the stuff at the bottom
//
int RecordTop_NextPage = indexConfig.recordTop;
if (csData.ok())
{
RecordTop_NextPage = rowNumber;
}
int RecordTop_PreviousPage = indexConfig.recordTop - indexConfig.recordsPerPage;
if (RecordTop_PreviousPage < 0)
{
RecordTop_PreviousPage = 0;
}
}
}
//
// Header at bottom
//
if (rowColor == "class=\"ccAdminListRowOdd\"")
{
rowColor = "class=\"ccAdminListRowEven\"";
}
else
{
rowColor = "class=\"ccAdminListRowOdd\"";
}
string blankRow = "<tr><td colspan=" + (indexConfig.columns.Count + 3) + " " + rowColor + " style=\"text-align:left ! important;\">{msg}</td></tr>";
if (rowNumber == 0)
{
//
// -- No records found
dataTableRows.Append(blankRow.Replace("{msg}", "----- no records were found -----"));
}
else
{
if (rowNumber < rowNumberLast)
{
//
// --End of list
dataTableRows.Append(blankRow.Replace("{msg}", "----- end of list -----"));
}
}
if (indexConfig.allowAddRow)
{
//
// optional AddRow
foreach (var addTag in getRecordAddAnchorTag(core, adminData.adminContent.name, adminEditPresetArgQsList, false, userContentPermissions.allowAdd))
{
dataTableRows.Append(blankRow.Replace("{msg}", addTag));
}
}
//
// Add another header to the data rows
//
dataTableRows.Append(DataTable_HdrRow.ToString());
//
var DataTable_FindRow = new StringBuilder();
if (indexConfig.allowFind)
{
//
// ----- DataTable_FindRow
//
DataTable_FindRow.Append("<tr><td colspan=" + (3 + indexConfig.columns.Count) + " style=\"background-color:black;height:1;\"></td></tr>");
DataTable_FindRow.Append("<tr>");
DataTable_FindRow.Append("<td valign=\"middle\" colspan=3 width=\"60\" class=\"ccPanel\" align=center style=\"vertical-align:middle;padding:8px;text-align:center ! important;\">");
DataTable_FindRow.Append(AdminUIController.getButtonPrimary(ButtonFind, "", false, "FindButton") + "</td>");
int ColumnPointer = 0;
var listOfMatches = new List <FindWordMatchEnum> {
FindWordMatchEnum.matchincludes, FindWordMatchEnum.MatchEquals, FindWordMatchEnum.MatchTrue, FindWordMatchEnum.MatchFalse
};
foreach (var column in indexConfig.columns)
{
string FieldName = GenericController.toLCase(column.Name);
string FindWordValue = "";
if (indexConfig.findWords.ContainsKey(FieldName))
{
var findWord = indexConfig.findWords[FieldName];
if (listOfMatches.Any(s => findWord.MatchOption.Equals(s)))
{
FindWordValue = findWord.Value;
}
}
DataTable_FindRow.Append(Environment.NewLine + "<td valign=\"middle\" align=\"center\" class=\"ccPanel3DReverse\" style=\"padding:8px;\">"
+ "<input type=hidden name=\"FindName" + ColumnPointer + "\" value=\"" + FieldName + "\">"
+ "<input class=\"form-control findInput\" onkeypress=\"KeyCheck(event);\" type=text id=\"F" + ColumnPointer + "\" name=\"FindValue" + ColumnPointer + "\" value=\"" + FindWordValue + "\" style=\"padding-right:.2rem;padding-left:.2rem;\">"
+ "</td>");
ColumnPointer += 1;
}
DataTable_FindRow.Append("</tr>");
}
//
// Assemble DataTable
//
string grid = ""
+ "<table ID=\"DataTable\" border=\"0\" cellpadding=\"0\" cellspacing=\"0\" width=\"100%\" style=\"Background-Color:white;\">"
+ DataTable_HdrRow + dataTableRows + DataTable_FindRow + "</table>";
return(grid);
} catch (Exception ex) {
LogController.logError(core, ex);
return(HtmlController.div("There was an error creating the record list."));
}
}
/// <summary>
/// Add automatic keys for the foreach item
/// </summary>
/// <param name="item"></param>
/// <param name="forEach"></param>
/// <param name="i"></param>
/// <param name="datasource"></param>
private static void AddAutoContextAddItemsPrefix(this ContextModel item, ForEach forEach, int i, DataSourceModel datasource)
{
if (forEach == null)
{
return;
}
if (!string.IsNullOrWhiteSpace(forEach.AutoContextAddItemsPrefix))
{
// We add automatic keys :
// Is first item
item.AddItem("#" + forEach.AutoContextAddItemsPrefix + "_ForEach_IsFirstItem#", new BooleanModel(i == 0));
item.AddItem("#" + forEach.AutoContextAddItemsPrefix + "_ForEach_IsNotFirstItem#", new BooleanModel(i > 0));
// Is last item
item.AddItem("#" + forEach.AutoContextAddItemsPrefix + "_ForEach_IsLastItem#", new BooleanModel(i == datasource.Items.Count - 1));
// Index of the element (Based on 0, and based on 1)
item.AddItem("#" + forEach.AutoContextAddItemsPrefix + "_ForEach_IndexBaseZero#", new StringModel(i.ToString()));
item.AddItem("#" + forEach.AutoContextAddItemsPrefix + "_ForEach_IndexBaseOne#", new StringModel((i + 1).ToString()));
item.AddItem("#" + forEach.AutoContextAddItemsPrefix + "_ForEach_IsOdd#", new BooleanModel(i % 2 == 1));
item.AddItem("#" + forEach.AutoContextAddItemsPrefix + "_ForEach_IsEven#", new BooleanModel(i % 2 == 0));
}
}
public void Add(DataSourceModel dataSource)
{
db.DataSource.Add(mapper.Map <SqlDataSource>(dataSource));
db.SaveChanges();
}
//
//====================================================================================================
/// <summary>
/// Run manual query
/// </summary>
/// <param name="cp"></param>
/// <returns></returns>
public static string get(CPClass cp)
{
string returnHtml = "";
CoreController core = cp.core;
try {
StringBuilderLegacyController Stream = new StringBuilderLegacyController();
Stream.add(AdminUIController.getHeaderTitleDescription("Run Manual Query", "This tool runs an SQL statement on a selected datasource. If there is a result set, the set is printed in a table."));
//
// Get the members SQL Queue
//
string SQLFilename = core.userProperty.getText("SQLArchive");
if (string.IsNullOrEmpty(SQLFilename))
{
SQLFilename = "SQLArchive" + core.session.user.id.ToString("000000000") + ".txt";
core.userProperty.setProperty("SQLArchive", SQLFilename);
}
string SQLArchive = core.cdnFiles.readFileText(SQLFilename);
//
// Read in arguments if available
//
int Timeout = core.docProperties.getInteger("Timeout");
if (Timeout == 0)
{
Timeout = 30;
}
//
int pageSize = core.docProperties.getInteger("PageSize");
if (pageSize == 0)
{
pageSize = 10;
}
//
int pageNumber = core.docProperties.getInteger("PageNumber");
if (pageNumber == 0)
{
pageNumber = 1;
}
//
string SQL = core.docProperties.getText("SQL");
if (string.IsNullOrEmpty(SQL))
{
SQL = core.docProperties.getText("SQLList");
}
DataSourceModel datasource = DataSourceModel.create(core.cpParent, core.docProperties.getInteger("dataSourceid"));
//
if ((core.docProperties.getText("button")) == ButtonRun)
{
//
// Add this SQL to the members SQL list
//
if (!string.IsNullOrEmpty(SQL))
{
string SQLArchiveOld = SQLArchive.Replace(SQL + Environment.NewLine, "");
SQLArchive = SQL.Replace(Environment.NewLine, " ") + Environment.NewLine;
int LineCounter = 0;
while ((LineCounter < 10) && (!string.IsNullOrEmpty(SQLArchiveOld)))
{
string line = getLine(ref SQLArchiveOld).Trim();
if (!string.IsNullOrWhiteSpace(line))
{
SQLArchive += line + Environment.NewLine;
}
}
core.cdnFiles.saveFile(SQLFilename, SQLArchive);
}
//
// Run the SQL
//
string errBefore = ErrorController.getDocExceptionHtmlList(core);
if (!string.IsNullOrWhiteSpace(errBefore))
{
// -- error in interface, should be fixed before attempting query
Stream.add("<br>" + core.dateTimeNowMockable + " SQL NOT executed. The following errors were detected before execution");
Stream.add(errBefore);
}
else
{
Stream.add("<p>" + core.dateTimeNowMockable + " Executing sql [" + SQL + "] on DataSource [" + datasource.name + "]");
DataTable dt = null;
try {
dt = core.db.executeQuery(SQL, DbController.getStartRecord(pageSize, pageNumber), pageSize);
} catch (Exception ex) {
//
// ----- error
Stream.add("<br>" + core.dateTimeNowMockable + " SQL execution returned the following error");
Stream.add("<br>" + ex.Message);
}
string errSql = ErrorController.getDocExceptionHtmlList(core);
if (!string.IsNullOrWhiteSpace(errSql))
{
Stream.add("<br>" + core.dateTimeNowMockable + " SQL execution returned the following error");
Stream.add("<br>" + errSql);
core.doc.errorList.Clear();
}
else
{
Stream.add("<br>" + core.dateTimeNowMockable + " SQL executed successfully");
if (dt == null)
{
Stream.add("<br>" + core.dateTimeNowMockable + " SQL returned invalid data.");
}
else if (dt.Rows == null)
{
Stream.add("<br>" + core.dateTimeNowMockable + " SQL returned invalid data rows.");
}
else if (dt.Rows.Count == 0)
{
Stream.add("<br>" + core.dateTimeNowMockable + " The SQL returned no data.");
}
else
{
//
// ----- print results
//
Stream.add("<br>" + core.dateTimeNowMockable + " The following results were returned");
Stream.add("<br></p>");
//
// --- Create the Fields for the new table
//
int FieldCount = dt.Columns.Count;
Stream.add("<table class=\"table table-bordered table-hover table-sm table-striped\">");
Stream.add("<thead class=\"thead - inverse\"><tr>");
foreach (DataColumn dc in dt.Columns)
{
Stream.add("<th>" + dc.ColumnName + "</th>");
}
Stream.add("</tr></thead>");
//
string[,] resultArray = core.db.convertDataTabletoArray(dt);
//
int RowMax = resultArray.GetUpperBound(1);
int ColumnMax = resultArray.GetUpperBound(0);
string RowStart = "<tr>";
string RowEnd = "</tr>";
string ColumnStart = "<td>";
string ColumnEnd = "</td>";
int RowPointer = 0;
for (RowPointer = 0; RowPointer <= RowMax; RowPointer++)
{
Stream.add(RowStart);
int ColumnPointer = 0;
for (ColumnPointer = 0; ColumnPointer <= ColumnMax; ColumnPointer++)
{
string CellData = resultArray[ColumnPointer, RowPointer];
if (isNull(CellData))
{
Stream.add(ColumnStart + "[null]" + ColumnEnd);
}
else if (string.IsNullOrEmpty(CellData))
{
Stream.add(ColumnStart + "[empty]" + ColumnEnd);
}
else
{
Stream.add(ColumnStart + HtmlController.encodeHtml(GenericController.encodeText(CellData)) + ColumnEnd);
}
}
Stream.add(RowEnd);
}
Stream.add("</table>");
}
}
}
Stream.add("<p>" + core.dateTimeNowMockable + " Done</p>");
}
//
// Display form
{
//
// -- sql form
int SQLRows = core.docProperties.getInteger("SQLRows");
if (SQLRows == 0)
{
SQLRows = core.userProperty.getInteger("ManualQueryInputRows", 5);
}
else
{
core.userProperty.setProperty("ManualQueryInputRows", SQLRows.ToString());
}
Stream.add(AdminUIEditorController.getHtmlCodeEditor(core, "SQL", SQL, false, "SQL", false));
Stream.add(" <INPUT TYPE=\"Text\" TabIndex=-1 NAME=\"SQLRows\" SIZE=\"3\" VALUE=\"" + SQLRows + "\" ID=\"\" onchange=\"SQL.rows=SQLRows.value; return true\"> Rows");
}
//
// -- data source
bool isEmptyList = false;
Stream.add(AdminUIController.getToolFormInputRow(core, "Data Source", AdminUIEditorController.getLookupContentEditor(core, "DataSourceID", datasource.id, ContentMetadataModel.getContentId(core, "data sources"), ref isEmptyList, false, "", "", false, "")));
{
//
// -- sql list
string js = "var e = document.getElementById('SQLList');SQL.value=e.options[e.selectedIndex].text;";
List <string> lookupList = SQLArchive.Split(new string[] { Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries).ToList();
string inputSelect = AdminUIEditorController.getLookupListEditor(core, "SQLList", 0, lookupList, false, "SQLList", "", false);
inputSelect = inputSelect.Replace("<select ", "<select onChange=\"" + js + "\" ");
Stream.add(AdminUIController.getToolFormInputRow(core, "Previous Queries", inputSelect));
}
//
// -- page size
if (isNull(pageSize))
{
pageSize = 100;
}
Stream.add(AdminUIController.getToolFormInputRow(core, "Page Size", AdminUIEditorController.getTextEditor(core, "PageSize", pageSize.ToString())));
//
// -- page number
if (isNull(pageNumber))
{
pageNumber = 1;
}
Stream.add(AdminUIController.getToolFormInputRow(core, "Page Number", AdminUIEditorController.getTextEditor(core, "PageNumber", pageNumber.ToString())));
//
// -- timeout
if (isNull(Timeout))
{
Timeout = 30;
}
Stream.add(AdminUIController.getToolFormInputRow(core, "Timeout (sec)", AdminUIEditorController.getTextEditor(core, "Timeout", Timeout.ToString())));
//
// -- assemble form
returnHtml = AdminUIController.getToolForm(core, Stream.text, ButtonCancel + "," + ButtonRun);
} catch (Exception ex) {
LogController.logError(core, ex);
throw;
}
return(returnHtml);
}
public string AddDataSource(DataSourceModel dataSource)
{
throw new NotImplementedException();
}
//
//=============================================================================
// Print the manual query form
//=============================================================================
//
public static string get(CoreController core)
{
string result = "";
try {
bool StatusOK = false;
int FieldCount = 0;
object Retries = null;
int RowMax = 0;
int RowPointer = 0;
int ColumnMax = 0;
int ColumnPointer = 0;
string ColumnStart = null;
string ColumnEnd = null;
string RowStart = null;
string RowEnd = null;
string[,] arrayOfSchema = null;
string CellData = null;
string TableName = "";
StringBuilderLegacyController Stream = new StringBuilderLegacyController();
string ButtonList = null;
DataTable RSSchema = null;
var tmpList = new List <string> {
};
DataSourceModel datasource = DataSourceModel.create(core.cpParent, core.docProperties.getInteger("DataSourceID"), ref tmpList);
//
ButtonList = ButtonCancel + "," + ButtonRun;
//
Stream.add(AdminUIController.getHeaderTitleDescription("Query Database Schema", "This tool examines the database schema for all tables available."));
//
StatusOK = true;
if ((core.docProperties.getText("button")) != ButtonRun)
{
//
// First pass, initialize
//
Retries = 0;
}
else
{
//
// Read in arguments
//
TableName = core.docProperties.getText("TableName");
//
// Run the SQL
Stream.add(SpanClassAdminSmall + "<br><br>");
Stream.add(core.dateTimeNowMockable + " Opening Table Schema on DataSource [" + datasource.name + "]<br>");
//
RSSchema = core.db.getTableSchemaData(TableName);
Stream.add(core.dateTimeNowMockable + " GetSchema executed successfully<br>");
if (!DbController.isDataTableOk(RSSchema))
{
//
// ----- no result
//
Stream.add(core.dateTimeNowMockable + " A schema was returned, but it contains no records.<br>");
}
else
{
//
// ----- print results
//
Stream.add(core.dateTimeNowMockable + " The following results were returned<br>");
//
// --- Create the Fields for the new table
//
FieldCount = RSSchema.Columns.Count;
Stream.add("<table border=\"1\" cellpadding=\"1\" cellspacing=\"1\" width=\"100%\">");
Stream.add("<tr>");
foreach (DataColumn RecordField in RSSchema.Columns)
{
Stream.add("<TD><B>" + SpanClassAdminSmall + RecordField.ColumnName + "</b></SPAN></td>");
}
Stream.add("</tr>");
//
arrayOfSchema = core.db.convertDataTabletoArray(RSSchema);
//
RowMax = arrayOfSchema.GetUpperBound(1);
ColumnMax = arrayOfSchema.GetUpperBound(0);
RowStart = "<tr>";
RowEnd = "</tr>";
ColumnStart = "<td class=\"ccadminsmall\">";
ColumnEnd = "</td>";
for (RowPointer = 0; RowPointer <= RowMax; RowPointer++)
{
Stream.add(RowStart);
for (ColumnPointer = 0; ColumnPointer <= ColumnMax; ColumnPointer++)
{
CellData = arrayOfSchema[ColumnPointer, RowPointer];
if (isNull(CellData))
{
Stream.add(ColumnStart + "[null]" + ColumnEnd);
}
else if ((CellData == null))
{
Stream.add(ColumnStart + "[empty]" + ColumnEnd);
}
else if (string.IsNullOrEmpty(CellData))
{
Stream.add(ColumnStart + "[empty]" + ColumnEnd);
}
else
{
Stream.add(ColumnStart + CellData + ColumnEnd);
}
}
Stream.add(RowEnd);
}
Stream.add("</TABLE>");
RSSchema.Dispose();
RSSchema = null;
}
//
// Index Schema
//
// RSSchema = DataSourceConnectionObjs(DataSourcePointer).Conn.OpenSchema(SchemaEnum.adSchemaColumns, Array(Empty, Empty, TableName, Empty))
Stream.add(SpanClassAdminSmall + "<br><br>");
Stream.add(core.dateTimeNowMockable + " Opening Index Schema<br>");
//
RSSchema = core.db.getIndexSchemaData(TableName);
if (!DbController.isDataTableOk(RSSchema))
{
//
// ----- no result
//
Stream.add(core.dateTimeNowMockable + " A schema was returned, but it contains no records.<br>");
}
else
{
//
// ----- print results
//
Stream.add(core.dateTimeNowMockable + " The following results were returned<br>");
//
// --- Create the Fields for the new table
//
FieldCount = RSSchema.Columns.Count;
Stream.add("<table border=\"1\" cellpadding=\"1\" cellspacing=\"1\" width=\"100%\">");
Stream.add("<tr>");
foreach (DataColumn RecordField in RSSchema.Columns)
{
Stream.add("<TD><B>" + SpanClassAdminSmall + RecordField.ColumnName + "</b></SPAN></td>");
}
Stream.add("</tr>");
//
arrayOfSchema = core.db.convertDataTabletoArray(RSSchema);
//
RowMax = arrayOfSchema.GetUpperBound(1);
ColumnMax = arrayOfSchema.GetUpperBound(0);
RowStart = "<tr>";
RowEnd = "</tr>";
ColumnStart = "<td class=\"ccadminsmall\">";
ColumnEnd = "</td>";
for (RowPointer = 0; RowPointer <= RowMax; RowPointer++)
{
Stream.add(RowStart);
for (ColumnPointer = 0; ColumnPointer <= ColumnMax; ColumnPointer++)
{
CellData = arrayOfSchema[ColumnPointer, RowPointer];
if (isNull(CellData))
{
Stream.add(ColumnStart + "[null]" + ColumnEnd);
}
else if ((CellData == null))
{
Stream.add(ColumnStart + "[empty]" + ColumnEnd);
}
else if (string.IsNullOrEmpty(CellData))
{
Stream.add(ColumnStart + "[empty]" + ColumnEnd);
}
else
{
Stream.add(ColumnStart + CellData + ColumnEnd);
}
}
Stream.add(RowEnd);
}
Stream.add("</TABLE>");
RSSchema.Dispose();
RSSchema = null;
}
//
// Column Schema
//
Stream.add(SpanClassAdminSmall + "<br><br>");
Stream.add(core.dateTimeNowMockable + " Opening Column Schema<br>");
//
RSSchema = core.db.getColumnSchemaData(TableName);
Stream.add(core.dateTimeNowMockable + " GetSchema executed successfully<br>");
if (DbController.isDataTableOk(RSSchema))
{
//
// ----- no result
//
Stream.add(core.dateTimeNowMockable + " A schema was returned, but it contains no records.<br>");
}
else
{
//
// ----- print results
//
Stream.add(core.dateTimeNowMockable + " The following results were returned<br>");
//
// --- Create the Fields for the new table
//
FieldCount = RSSchema.Columns.Count;
Stream.add("<table border=\"1\" cellpadding=\"1\" cellspacing=\"1\" width=\"100%\">");
Stream.add("<tr>");
foreach (DataColumn RecordField in RSSchema.Columns)
{
Stream.add("<TD><B>" + SpanClassAdminSmall + RecordField.ColumnName + "</b></SPAN></td>");
}
Stream.add("</tr>");
//
arrayOfSchema = core.db.convertDataTabletoArray(RSSchema);
//
RowMax = arrayOfSchema.GetUpperBound(1);
ColumnMax = arrayOfSchema.GetUpperBound(0);
RowStart = "<tr>";
RowEnd = "</tr>";
ColumnStart = "<td class=\"ccadminsmall\">";
ColumnEnd = "</td>";
for (RowPointer = 0; RowPointer <= RowMax; RowPointer++)
{
Stream.add(RowStart);
for (ColumnPointer = 0; ColumnPointer <= ColumnMax; ColumnPointer++)
{
CellData = arrayOfSchema[ColumnPointer, RowPointer];
if (isNull(CellData))
{
Stream.add(ColumnStart + "[null]" + ColumnEnd);
}
else if ((CellData == null))
{
Stream.add(ColumnStart + "[empty]" + ColumnEnd);
}
else if (string.IsNullOrEmpty(CellData))
{
Stream.add(ColumnStart + "[empty]" + ColumnEnd);
}
else
{
Stream.add(ColumnStart + CellData + ColumnEnd);
}
}
Stream.add(RowEnd);
}
Stream.add("</TABLE>");
RSSchema.Dispose();
RSSchema = null;
}
if (!StatusOK)
{
Stream.add("There was a problem executing this query that may have prevented the results from printing.");
}
Stream.add(core.dateTimeNowMockable + " Done</SPAN>");
}
//
// Display form
//
Stream.add(SpanClassAdminNormal);
//
Stream.add("<br>");
Stream.add("Table Name<br>");
Stream.add(HtmlController.inputText_Legacy(core, "Tablename", TableName));
//
Stream.add("<br><br>");
Stream.add("Data Source<br>");
Stream.add(core.html.selectFromContent("DataSourceID", datasource.id, "Data Sources", "", "Default"));
//
Stream.add("</SPAN>");
//
result = AdminUIController.getToolForm(core, Stream.text, ButtonList);
} catch (Exception ex) {
LogController.logError(core, ex);
}
return(result);
}
public override string BuildSql(DataSourceModel source, string[] fields, string[] strings, string filter)
{
return(base.BuildSql(source, fields, strings, filter));
}
public void Add(DataSourceModel dataSource)
{
var item = mapper.Map <CdbDataSource>(dataSource);
ItemResponse <CdbDataSource> response = container.CreateItemAsync(item, new PartitionKey(dataSource.DeviceId)).Result;
}
