Add SearchBuilder to server side asp.net. Examples?

ErnieCox

Here is an example controller action in asp.net. How would this be modified to filter results based on searchbuilder?
Client Side:

"ajax": {
            "url": "Home/LoadData", // url to controller action
            "type": "POST",
            "datatype": "json"
        }

Server Side

public IActionResult LoadData()
        {
            try
            {
                var draw = Request.Form["draw"].FirstOrDefault();
                // Skiping number of Rows count  
                var start = Request.Form["start"].FirstOrDefault();
                // Paging Length 10,20  
                var length = Request.Form["length"].FirstOrDefault();
                // Sort Column Name  
                var sortColumn = Request.Form["columns[" + Request.Form["order[0][column]"].FirstOrDefault() + "][name]"].FirstOrDefault();
                // Sort Column Direction ( asc ,desc)  
                var sortColumnDirection = Request.Form["order[0][dir]"].FirstOrDefault();
                // Search Value from (Search box)  
                var searchValue = Request.Form["search[value]"].FirstOrDefault();
                //Paging Size (10,20,50,100)  
                int pageSize = length != null ? Convert.ToInt32(length) : 0;
                int skip = start != null ? Convert.ToInt32(start) : 0;
                int recordsTotal = 0;
                // Getting all Customer data  
                var cData = (from tempc in DataRepository.GetCompanies() select tempc);
                //Sorting  
                if (!(string.IsNullOrEmpty(sortColumn) && string.IsNullOrEmpty(sortColumnDirection)))
                {
                    if(sortColumnDirection.ToLower() == "asc")
                        cData = cData.OrderBy(a => a.GetType().GetProperty(sortColumn).GetValue(a, null));
                    else if (sortColumnDirection.ToLower()=="desc")
                        cData = cData.OrderByDescending(a => a.GetType().GetProperty(sortColumn).GetValue(a, null));

                    //  cData = cData.OrderBy(s => sortColumn + " " + sortColumnDirection).ToList();
                }
                //Search  
                if (!string.IsNullOrEmpty(searchValue))
                {
                    cData = cData.Where(m => m.ID.Equals(searchValue)
                                                || m.Name.Contains(searchValue)
                                                 || m.Town.Contains(searchValue)
                                                || m.Address.Contains(searchValue)
                                                || m.EmployeesNumber.Equals(searchValue)
                                                || m.DateCreated.Equals(searchValue)
                                                || m.DateUpdated.Equals(searchValue));
                }
                //total number of rows count   
                recordsTotal = cData.Count();
                var data = cData.Skip(skip).Take(pageSize).ToList();
                //Paging   
                var jsonData = new { draw = draw, recordsFiltered = recordsTotal, recordsTotal = recordsTotal, data = data };
                //Returning Json Data              
                var options = new JsonSerializerOptions
                {
                    ReadCommentHandling = JsonCommentHandling.Skip,
                    AllowTrailingCommas = true,
                    WriteIndented = true
                };
                return Json(jsonData, options);
            }
            catch (Exception ex)
            {
                throw;
            }
        }

Edited by Colin - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

colin

The best bet is to use the Editor server-side libraries as discussed in this blog post,

Colin

ErnieCox

I am using the Editor Libraries. I do not understand how your code sample shown below can be integrated in the same iActionResult function above, which the the Ajax post action is directed to. It appears you are referring to the use of two separate functions in your examples, yet your example ajax urls only refer to one.

var response = new Editor(db, "users")
.Model<StaffModel>()
.Field(new Field("first_name"))
.Field(new Field("last_name"))
.Field(new Field("position"))
.Field(new Field("office"))
.Field(new Field("extn"))
.Field(new Field("start_date"))
.Field(new Field("salary"))
.Process(Request)
.Data()

return Json(response)

ErnieCox

Do you have a working searchbuilder server side .NET example project you can direct me to?

ErnieCox

After making the changes specified here https://editor.datatables.net/manual/net/core

I still keep getting "The specified invariant name 'System.Data.SqlClient' wasn't found in the list of registered .NET Data Providers."

ErnieCox

Found your sample project. On the right track now.

allan

Thanks for the update - let us know ow you get on.

Allan

ErnieCox

I have everything together coded for SearchBuilder Server Side using the Editor libraries. However, I cannot get it to work. I get "The specified invariant name 'Microsoft.Data.SqlClient' wasn't found in the list of registered .NET Data Providers"

Same for System.Data.SqlClient. Followed all the steps outlined here https://editor.datatables.net/manual/net/core.

Why am I getting this still?

allan

In .NET Core (and now just ".NET") you need to register the database provider for your program. This can be done using:

DbProviderFactories.RegisterFactory("System.Data.SqlClient", SqlClientFactory.Instance);

in Main of your Program.cs file.

That is unlike .NET Framework (seriously, MS' marketing people need to get a grip on the naming!) where all data providers are automatically added.

Allan

ErnieCox

Here is what I had. Still get the error.

...
public static void Main(string[] args)
{
CreateHostBuilder(args).Build().Run();

        // Register the factory in `Main`
        DbProviderFactories.RegisterFactory("Microsoft.Data.SqlClient", Microsoft.Data.SqlClient.SqlClientFactory.Instance);

    }

...

ErnieCox

I fixed the invariant name error by moving DbProviderFactories.RegisterFactory("Microsoft.Data.SqlClient", Microsoft.Data.SqlClient.SqlClientFactory.Instance);

above CreateHostBuilder(args).Build().Run();

Now my problem is my table key is compound. What is the C# equivalent to pass in as shown in this php example array('visitor_id', 'visit_date') ?

https://editor.datatables.net/manual/php/getting-started#Compound-keys

allan

.NET docs are here

Allan

ErnieCox

Have it working now. Using SearchBuilder with ReadTable table is terribly slow. Before I was working with SearchBuilder, I was pulling my recordset at session start and filtering from this. My implementation is read only working with 7,000 32 field records. Can ReadTable be used against a recordset instead of all the back and forth with the server table?

allan

By ReadTable do you mean the Editor.ReadTable() method? If so, that will just be looking up whatever you are pointing ReadTable to - which I presume will be a VIEW doing some joins or something? It would be worth profiling that.

Make sure you have .Debug(true) in the Editor chain so the JSON will show the SQL that is being executed, and thus letting you profile what is being executed.

Regards,
Allan

ErnieCox

Yes Editor.ReadTable. When I try to a SQLDataReader or List I get "Cannot convert from [ ] to string"

My ReadTable is set to a SQL Server View, which executes a stored procedure.

ErnieCox

Cannot get SearchBuilder drop downs to populate. All your examples seem to be client side with datatables. Searchbuilder Server side using Editor example of populating criteria drop downs?

allan

Did you profile it based on the SQL that Editor is executing? How many statements is it running and what is the performance of each?

Allan

ErnieCox

When I start debugging the project. SQL server completes three View executions.

I click "Add Condition", select my field "Order No", choose Condition Equals and type a five digit Order No. The above makes fifteen (15) calls to sql server to execute the View. Nine (9) of the calls are aborted due to connection reset "sp_reset_connection"

ErnieCox

Had cached code issue.

ErnieCox

Why is it impossible to display additional buttons when using searchbuilder? I have not gotten it to work one time. I used all references specified here https://datatables.net/extensions/searchbuilder/examples/integration/buttons.html

My script code is as follows as it pertains to buttons:

$(document).ready(function () {
            $("#myTable").DataTable({
                dom: 'QBfrtip',
                buttons: [
                    'copy', 'csv', 'excel', 'pdf', 'print'
                ]
} );
} );

Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

allan

My guess would be that you haven't got the SearchBuilder Javascript loading on the page.

Allan

ErnieCox

SearchBuilder loads. None of the other buttons appear, though a space is created for them on the page.

kthorngren

Sounds like you have a styling conflict. We will need to see the problem to inspect the page to help debug. Please post a link to your page or a test case replicating the styling issue.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Possibly you aren't loading the correct styling integration files for the styling framework you are using. Use the Download Builder to get the proper files.

Kevin

ErnieCox

Must have been something wrong with one of my javascript references. Changed to https from local and working. One last question. I have an issue when a user clicks the Excel button it only exports the records on the current page. As a work around they could choose ALL of their filtered records. However, the page length drop down no longer shows. See here http://live.datatables.net/biyigolo/1/

kthorngren

I have an issue when a user clicks the Excel button it only exports the records on the current page.

All the records are exported in the test case. Or am I missing something? Are you using server side processing? If yes see this FAQ.

However, the page length drop down no longer shows.

See this FAQ.

Kevin

ErnieCox

My issues are resolved. Thank you!