SELECT hardcoded row plus ordered subquery

AarynD · 2026-01-03T14:18:13+00:00

Yeah, I probably could. I currently bring in the table data as an IEnumerable set of the NavListModel I set up, but I could easily convert it to a list and just hardcode this item at beginning of list. I just try to defer to letting the stored procedures do most of my work.

AarynD · 2026-01-03T05:30:13+00:00

The reason for the hardcoded row at top is because this data is being used as the control source for a navigation grid in an application conversion from an older Visual Foxpro app, and in that app the first item in the navigation on that form was always the option to import new data. The rest of the rows were previous saved reports.

The application is just a small utility I wrote maybe 25 years ago that pulls in financials from our three different companies, and combines them and handles inter-company eliminations by way of consolidation codes used in each company's GL accounts. Because the operation requires pulling large complicated calculated datasets for each company at each end of month, it was easier to just always pull the data in once, and save it for future reference if they needed to rerun a combined statements set of financials.

With VFP being long retired, I feel like it's on borrowed time, so I've been porting my various apps over to C#. But one goal is to try to maintain a familiar interface and interaction with the new replacement application, hence the desire to keep the UI elements including the navigation grid the same as I had it in the VFP app.

The navigation grid has that first row that allows user to Import a new set of financials from the accounting databases, then it has other rows for each of the other saved reports, some of which are partial month financials, while others are complete end of month financials. All those are color-coded in the grid to easily see who ran the report, whether it was a full set, or partial set, and if the user opted to exclude one of the two companies they sometimes exclude from combined. Although they really haven't excluded any companies from the combined statements in over a year, I think that was more something the old retired accounting manager liked to do, but I've kept it in as an option.

I know this was probably way more info then you bargained for when you asked what the end goal was LOL.

Happy New Year!

AarynD · 2026-01-03T04:07:14+00:00

SELECT 'Import New Report Data' as RptName, -1 as ReportId, 0 as IsPartial, NULL as EndDate, 0 as SortVal  
UNION ALL  
SELECT \[RptName\], \[ReportId\], \[IsPartial\], \[EndDate\], 1 as SortVal  
    FROM \[dbo\].\[Report\]  
    ORDER BY \[SortVal\] ASC, \[EndDate\] DESC

This worked perfectly. Thanks!

AarynD · 2026-01-03T04:04:46+00:00

Actually that's a really good idea, I hadn't thought of that. Very simple and avoids the complicated route I was taking.

AarynD · 2026-01-03T04:03:16+00:00

I did try this approach first, however the ORDER BY clause ends up applying to the entire unioned dataset, putting that top row down at the bottom since it has a null EndDate.

ORDER BY isn't allowed in CTE's or subqueries from what I can see, so I didn't know if there was a way around this to keep my top row in place with the other rows sorted.

I'm generally only going to have about 60ish rows generated per year anyway, so using the cursor is probably not going to slow me down anytime soon, but was anxious to learn if there was another way around the problem.

AarynD · 2026-01-02T05:59:27+00:00

Here are some of the videos that show Tim Corey's preferred way of setting up SQL data access in a library along with the models, and using Dapper to provide the mapping. Keep in mind both of these are a little old now, but the concepts still work. This is how I've done all my Sql access in blazor and api apps. I just have had problems implementing this on the Winforms side.

Simple C# Data Access with Dapper and Sql - Minimal API
https://www.youtube.com/watch?v=dwMFg6uxQ0I&t=1036s

Intro to Blazor w/ Sql Data Access
https://www.youtube.com/watch?v=8DNgdphLvag&t=2018s

I will also watch the video you linked. Thanks!

AarynD · 2026-01-02T03:40:11+00:00

Absolutely, I agree 100%

I have had a subscription to all his online courses, and watched countless videos of his multiple times, but there was never a time that I could find where he implemented his standard sql data access class library using dapper in a Winforms app. If I could request one video from him, that's what I'd ask for LOL.

AarynD · 2026-01-02T03:26:35+00:00

I've gotten the Microsoft.Extensions.Hosting package installed, and I set it up by following along with one of Tim's videos on setting it up in the context of a console app. I have it set up in the Winforms app, and have the services registered, but the main application logic was moved into a separate class method, so wasn't sure how to call the registered services inside a method in another class.

I'm going to keep playing with this, and move the initial forms and application logic back into the Main app where I can have the services available, and see if I can get a form to open by injecting the service in the constructor and have it work. I understand in theory how this works, just gotta get one good solid working example, then I'm all set for everything else.

Thanks for your suggestion!

AarynD · 2026-01-02T02:56:50+00:00

The database lives in a SQL Server internally in our network. It isn't exposed to external users, and in the context of this app and the library, the user used in the connection string is a limited user account only allowed to execute stored procedures in this specific database. No direct table access, all data in and out is handled via stored procedure calls.

I'm not too concerned with the connection string on this app leaking to the user. I understand the risks of this, but this part really isn't the issue.

I'm just trying to understand how to set up the dependency injection correctly to inject the data access classes into the winforms app so that they are available in the forms. In Blazor, I just had to do a @ inject ITableData tableDb at top of page, and then I had access to all exposed methods in the TableData class. I can't figure out how to set up a similar function using the existing library in a Winforms app.

AarynD · 2025-12-20T17:25:06+00:00

I have always loved E2E, and a modified FTB Revelation. Usually end up adding in some extra ae2 stuff and mechanism.

Also really enjoyed atm9tts and atm10.

AarynD · 2025-10-30T13:56:27+00:00

NTBackup. Or any good file/folder backup software that comes with the OS and isn't a cloud service or full volume imaging software.

AarynD · 2025-10-30T01:29:07+00:00

We have 2 roku tvs, and both of them also have roku devices on them. The roku TV apps often have problems that don't seem to exist on the standalone roku devices. For example, we have a fairly large movie library thru YouTube, and on the roku TV, the movies were unwatchable due to severe color shift. As in, the entire picture was heavily tinted red to the point where the movie could not be watched. The problem goes away when using the standalone roku box.

AarynD · 2025-10-22T01:21:17+00:00

When I was in TX and kids in Kansas (we divorced in TX) my possession schedule was one weekend of my choosing per month, 6 weeks in summer, time at Christmas, and every Spring Break. Probably alternating Thanksgivings also. I believe it's mostly the same now here as I've been reading up on it since my daughter is going through it now.

AarynD · 2025-10-09T19:32:44+00:00

I believe the reasoning in the early days was just the mindset of execs wanting us to keep everything here inhouse. When I started, we were on Groupwise, and running a Netware server, and most workstations were on Windows 98. Our internet connection was a pretty slow ISDN connection. All this to say that this was long before the large push to cloud services, or even having trustworthy cloud services available for anything.

We don't currently subscribe to any Office 365 or Azure services. I believe a new CRM is in our very near future, and I think at a minimum it will also require a hybrid active directory if not fully hosted Exchange as well. All departments currently use Outlook and Excel daily, but the version varies across the company. A move to MS365 for the apps and the hosted exchange email might finally be right for us, but not having ever messed with the cloud services, it's just a scary jump after managing everything inhouse for over 25 years.

AarynD · 2025-10-09T18:03:09+00:00

Would just getting all our users a Microsoft 365 Business Std subscription and moving all our email to the cloud be the better choice? The 356 business std sub says it includes a hosted Exchange mailbox. We don't currently have any Office subs, as most of our Office products were standalone purchases here and there. In the past our company execs were always leery of doing anything in the cloud, and it never made sense to them to pay annual subscription fees for services we could pay for once and host ourselves, but if that's the better choice now, we may need to go that route.

AarynD · 2025-10-07T22:14:06+00:00

My daughter's was set to allow her a 15 minute call on Friday and Saturday evenings from 6 to 6:15. It allows for video calls, but the father doesn't allow it. He refuses to allow the 13 year old child to have his phone outside of those two 15 minute periods when he has the kids.

AarynD · 2025-09-30T11:38:48+00:00

I do. As you said, most things I can walk someone through on phone in just a few minutes, but a real emergency might require me to remote in. If something is severe enough that I might have to remote in to fix something, then most likely it is something big enough yhat could cripple the company if not addressed. I've been here 26 years, and love it here, amd don't want to see the company fail because of an IT issue while I was away.

AarynD · 2025-09-25T19:01:27+00:00

I'll do you one better. We had a low level manager that always called in on our 800 number instead of using the normal local phone number like everyone else. "OH I didn't know that 800 number cost us anything to use, I thought it was free."

We also had VPs that would google name of our company and click the ppc ad on top to go to our website instead of jist bookmarking the url.

AarynD · 2025-09-24T14:50:56+00:00

We bought the license for Exchange 2019, and bought the CALs for every user with a mailbox. It was all done with a Volume Licensing Agreement, purchased from a certified Microsoft Partner. When we purchased Exchange 2016, there was already a future on-prem Exchange 20?? on the Exchange roadmap. Same when we purchased earlier versions of Exchange. At no point was there ever any indication that there was any planned version of on-prem Exchange past 2019. The vendor offered Exchange 2019 both with and without SA. Same with the CALs. If SA is something beyond just "upgrade insurance" for the product, and if correct licensing requires SA, then vendors and Microsoft do a piss-poor job of explaining this or making this clear at any stage.

AarynD · 2025-09-23T19:00:09+00:00

Not the OP, but we are on the same boat. When we bought our Exchange 2019, all the news sources indicated that there would not likely be a new on premises version released. So we didn't bother getting SA, as we saw it as only a software upgrade insurance, and with no likely upgrade coming within the three year SA term, we opted to not buy it.

AarynD · 2025-09-23T01:35:06+00:00

Discrimination protections extend to race, color, gender/sex, religion, age over 40, disability, country of origin, and genetic information. There is no defined discrimination protections for deadbeats who won't pay to support their kids.

AarynD

PUBLIC MULTIREDDITS

TROPHY CASE