How to do aggregation, place holders

[papyr]

I see col operations but how can we get other operations like.

• How to export the results of the data to another CSV
• Sum of Col A
• Avg of Col B
• How to import Text files Tab Delimited or CSV
• Place holders, how to replace all Johns* with Jason

[korzh]

Hello,

1. What did you mean by "to another CSV"? Most (if not all) our demo projects include "exporting to CSV" functionality. You can see how it works there.

2. About SUM or AVG. Here is the article on our documentation website which describes how to create queries with aggregate columns.

3. EasyQuery does not have any data importing function. It's just a library which help you to provide user-friendly UI for querying or filtering.

4. What did you mean by "placeholders"? Could you give an example?

5. About the error on our demo site (I believe you are talking about this page: http://demo.easyquerybuilder.com/asp-net-mvc/)
   We can't reproduce it. Could you, please, describe what steps we need to perform to get that error?

[papyr]

1. I like the export side, but I am also seeking the import side so that we can use the tool/sample to do our data processing on the source data. Is there any chance to import a CSV file so that the CSV or excel Cols show up on the left tab in your asp demo, checkout filehelpers.net free

2. on 4. when importing / moving data from one data-source and mapping to another for e.g. Users col1 SourceTable1.[COL1] from one system to another Users Col7 MergedTable2.[COL2] cell/row placeholders are required when the mapping is not able to find the record.

We create a new row with an empty cell and come back and manually reconcile that later. I am guessing you will say it does not / will not support that :)

[papyr]

I think part of the problem is that your nugets are old, can you update to core 2.1 and for mVC with latest nugets Core 2.1 and ASP 5.3.5 .net 4.62 Boot Strap 4.1, I cannot update and run.

do I need to install bower? this could also be an issue

I have VS 2017 update .8 (which ships with .net core 2.1 and 4.72), this soln does not build with that. Same thing for all the demos

System.Data.SqlClient.SqlException: 'CREATE FILE encountered operating system error 5 while attempting to open or create the physical file 'C:\Users\JasonEqDemoDb01.mdf'.
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.'
I checked folder permissions to read rewrite all, but still not go.

[korzh]

Hello,

1. In which one from EasyQuery samples are you interested in first of all?

2. We are going to publish version 4.4 soon - it will include the updated versions of the samples.
   However, our Nuget packages are already fully compatible with NET Core 2.1 - so it's just a matter of migration process for an ASP.NET Core project from 2.0 to 2.1. It's fully described here

3. Try to set another destination in your Local SQL database connection string. It's better to set it to some folder inside your web-app (like App_Data in our examples).

[papyr]

Purchase question, my mgr says jquery builder is free and you have separate price for ASP MVC and ASP core, should they not be the same project.

[korzh]

Hello,

EasyQuery.JS is free but it just provides an UI for query building. You will need a sever-side part to handle the requests from the widgets. We have two editions of such backend: one for ASP.NET 4 (for old .NET Framework 4.x) and another one - for ASP.NET Core.
The former is a new, rewritten version of EasyQuery library. That's why we sell it as a separate product.
Usually people needs only one edition: for ASP.NET 4 or for ASP.NET Core.

[papyr]

Hi Korzh, thanks for the response, good news is I have convinced my mgr to buy, we are very small anyways, and using the free jquery builder. but to answer your question,

• our project is now running in MVC 5 version and we are migrating to core, so till we finish we need both.
• The other question is doing the aggregation/count and showing the result record . So, after filter -> count -> sum -> show that data an create a new col (for the total, and create an insert statement last).
• I think your widget should offer a form CRUD to save all the queries/filter in a db table called filters table.

[korzh]

Hello,

1. You can buy ASP.NET MVC 5 license now and then when you are ready to migrate get ASP.NET Core with a 50% discount. I need to note, that ASP.NET and ASP.NET Core editions have some differences so, if possible, consider migrating to ASP.NET Core before the applying of EasyQuery to your project. In such case you will need only one license.

2. Aggregation fields are supported. Here is an article which describes how to add them to your query:
   https://korzh.com/easyquery/docs/tutorials/aggregate-data

As for the total - you will need to add it yourself. We don't have such option now.

3. We have such CRUD in our EasyReportDemo project (included to these samples).
   We also a separate service called [Easy.Report][http://easy.report] which you can test to see how EasyReportDemo will work (they are not the same of course but have many common parts).

Such CRUD functionality can't be implemented as a simple widget since it requires a lot of changes in different parts of the project.

[papyr]

Hi, I am getting errors on initializing default db, how to change the default path... its trying to write c\user... (i only have access to F:\dev)

• I will check if we can go to ASP core and get back.
• On the CRUD, I am only asking for a simple Submit button to capture the SQL command text, not the result and send the Cmd to the server, as a string or parameterized sting or something simple. That we can later run.
• I see exportToCSV, but I need an ExportToDB(string NewTableName) so we can create a temp table
• Model tool: We need to create the loaded files automatically, but it looks like the Editor is a manual process. Is there a way to put the tool in ASP site demo or trigger the tool to pickup and add all tables to the XML editor.

[korzh]

Hello,

1. The demo uses SQL Server LocalDB connection by default. The connection string is taken from the configuration file (appsettings.json) - you can change it in any way you want. I think, it tries to write to c:\users... just because that is the way LocalDB works. Unfortunately, we don't know how to setup it to write to some other folder. If you have a fully functional SQL Server installed - you can change the connection string to user it instead.

2. A simple "submit" button which sends some SQL statement for execution can be easily done in any ASP.NET (Core) app. You don't need EasyQuery for that. It can be implemented as a simple form with one Text Area field and one "Submit" button.

3. It's possible to do but we don't have an example for this task, unfortunately.

4. Yes, Data Model Editor is a standalone WinForms application. It's not possible to use it in ASP.NET app. However, creating a model with DME - is not the only way to do it. You can also fill your data model from Entity Framework's context or even by code. For more information please take a look at the following articles:

• https://korzh.com/easyquery/docs/fundamentals/data-model?highlights=2588,2604
• https://korzh.com/easyquery/docs/how-to/build-data-model-by-code

[papyr]

1. Usually, it should pick up the path from App_Data folder for localDb, but some where the env. variable is overridden. https://stackoverflow.com/questions/48823648/how-to-change-database-file-location-with-entitiyframework-codefirst-auto-migrat -- look at this for default location
   Here is the location
2. Yes sure, I can do the form submit, but the problem I dont want to mix my code with your controls, i.e. the whole text area which was originally part of the widget is now in a form. I was asking if we could get the values using, EzQuery.GetValidatedSql() to get [ SourceFileName, SQL String, DateTimeWhenExported]
3. I was told we def. need both MVC and Core till we transition,
4. I saw the link for loading on the fly, which is great news since thats what we are going. However, since we are loading directly from CSV, we need to load from a dynamic CSV Model is that possible?

---

//build runtime class based on metadata
FD.DelimitedClassBuilder cb = new FD.DelimitedClassBuilder("ImportFile", "|");
cb.IgnoreFirstLines = 1;
foreach (DT.DataRow drMetadata in dtMetadata.Rows)
{
    cb.AddField(drMetadata["EntityColumnName"].ToString(), typeof(string));
    cb.LastField.FieldQuoted = true;
    cb.LastField.QuoteMode = FH.QuoteMode.AlwaysQuoted;
    cb.LastField.QuoteMultiline = FH.MultilineMode.AllowForBoth;
}
//create async filehelper engine for row by row processing
FH.FileHelperAsyncEngine fhe = new FH.FileHelperAsyncEngine(cb.CreateRecordClass());

using (fhe.BeginReadStream(file))
{
    foreach(object record in fhe)
    {
        //convert object to list, doesn't work
        //SG.List<object> blaat = (record as SG.IEnumerable<object>).Cast<object>().ToList();

        //serialize record class to json
        string json = JS.JsonConvert.SerializeObject(record, JS.Formatting.Indented);
        //convert message to key-value dictionary
        SG.IDictionary<string, string> values = JS.JsonConvert.DeserializeObject<SG.IDictionary<string, string>>(json);

        values.Add("SourceSystem", messagesource);
        values.Add("SourceType", messagetype);

        string json2 = JS.JsonConvert.SerializeObject(values, JS.Formatting.Indented);

        SY.Console.WriteLine(json2);
    }
}
fhe.Close();

---

5. Since you do aggregation - I am not sure, can you please tell what is the difference between that and total or sum for a col. in easy query

[korzh]

1. So, according to that documentation you can use AttachDBFilename to set your DB file storage to some other place instead of the default one.

2. You see, the SQL statement shown in your demos is placed there for demonstration purposes only. Usually, people don't show the SQL to end users and of course do not allow to execute SQL modified on the client-side.
   For our demo project you can simply use JS code to get the statement from the DOM element where SQL statement is rendered and send to the server using AJAX.
   However, we don't have a ready-to-use solution for this task and we don't plan to implement it in the nearest future (for the reasons I mentioned above).

3. OK. So you can get ASP.NET MVC edition now and then you will get 50% discount for Core edition.

4. Yes, it's possible but you will need to write some custom code for this operation. We don't have something for importing model from a CSV which is ready right now.

5. You can define a total SUM for a column. It's described in that article I mentioned above.
   I though you need to get a final row with total sums for all columns you have in the result set.
   For this task you will need to write something in addition.

[papyr]

1. I fixed 1.
2. Fixed 2 is not a big issue.
3. I will ask about #3 for lic and which they prefer on Mon/Tuesday
4. for the automation of the reading the CSV files we will be and loading & the models automatically might be the big obstacle, the issue of getting the lib/component is a little lost, since I wanted to speed up my work. will the model editor code be included in the purchase? if its not we wont buy. We definitely need to auto load the files every 15 mins. the tool will be used to create the query, which will be run by a scheduler every 15 mins. (Maybe I can write some on my personal time, but I would need a personal license 😄 in return )
5. 5 I can see this, its very close to what i need, but wondering how we get to the one unique row with col totals.

Lastly, where is the link to the datamodel editor, and does it have any API

[korzh]

Hello,

I have missed your last question somehow. Here is the direct link to Data Model Editor:
https://korzh.com/download/dme_setup.exe

[papyr]

Thanks for that, my boss decided another control. Perhaps in the future!