Concatenating DV, TA

[K3JAE]

I need a fluid TableAdapter, BindingSource and Dataset that will change based on the then current year so I or someone else is not forced to have to re-code the program each year.

I was hoping to try something like:

Dim CurYear As String = CType(Now.Year(), String)

Dim NewTA As New TableAdapter = "Tb" + CurYear + "DocsTableAdapter"
Dim NewBinding As New BindingSource = "Tb" + CurYear + "DocsBindingSource"
Dim NewDS As Dataset = "Tb" + CurYear + "DocsDataset"
Dim NewTable As Table = "Tb" + CurYear + "Docs"

then pass that as:

NewTA.Fill(NewDS.NewTable)
DV = New DataView(NewDS.NewTable)

Is this possible or even allowed? Trying it in my code errors so obviously this is not the correct syntax or proper coding.

My intention is to have the TableAdapter, BindingSource, DataSet etc. all reflect the current year.

So the crux of my question is simply this: Can this be done and if so... how? Please no C# as I am coding VB.NET.

REASON: I have multiple tables in my Database named tb2020Docs, tb2021Docs, tb2022Docs etc. I need to be able to search on and access a specific table for records based on the year.
all tables contain BLOB data and combining all records into one database is seriously overtaxing and is as slow as cold tar in winter to read. I need the ability to select and read a record based on search params input by the user and the ability to access the proper table. Eventually I will convert the Db to a more efficient way to do this overall but right now, it works albeit slow and I cannot take the db off production for an extended period.

I am asking as I cannot find anything on the web how to properly concatenate a TableBindingSource (etc.) name and have done my due diligence in trying to get this on my own and I simply am out of ideas. I seriously tried to avoid having to ask the question here fearing some less than professional response, but alas, I succumb to asking for help.

[jmcilhinney]

You're doing it wrong to start with. You should absolutely not have those year-based tables in your database. You should have one table with column for the year/date. When you want data for a particular year, you simply filter that table on that column. Your current question becomes moot when you start using the database properly.

[K3JAE]

@jmcilhinney Fine, I am doing it wrong. This I already know thus why the original post. How do I correctly do what I asked in my original posting? Your assuming and making flippant, opinion based smart-aleck comments based on information you do not fully have and making no effort to answer the specific question posed, instead opting to belittle again.

The reason for why I am doing it is not truly important to the question at hand. I did however give clear basic information as to why in my original post if you read it thoroughly (something you love to pound into people when a question is asked). Whether you disagree with the reasoning or not is of no concern to me. I asked HOW to do a specific item. I did not ask for opinion. I am in contact with professional SQL database experts who are guiding me in an alternate solution but for now I am stuck with what I have until such time as I can redo the DB.

If you cannot address the specific question at hand then make no further comments. You response is hostile and exactly why I hesitated to even ask a question in this forum.

[Shaggy Hiker]

Could you make it work like this? Yes you could. However, you said that you wanted to make it so that somebody wouldn't be forced to recode the program each year, and the way you have done so is barely going to do that. Perhaps there is some reason why it has to be this way, but you haven't stated what limitation keeps you from pursuing a better approach, so we don't know what borders are permissible for an answer.

What JMC said was quite correct, but there's more to it than that. For one thing, a datatable has a DefaultView property that would return a Dataview, so there is no need to create one. It isn't necessarily bad to do it the way you are. I've done that myself, but in my case, I didn't trust the DefaultView, since it was potentially already in use. You might have the same limit, I don't know.

However, any Dataview has a RowFilter property, which would allow you to do exactly what you are asking for, so long as the year can be found in the Dataview itself. All you'd have to do to switch from one year to the next is to alter the RowFilter property of the Dataview, with no need to recreate things as you are doing.

Beyond that, since you said there were errors but didn't say what they were, we can guess at them. For example, you have this line:

Dim NewDS As Dataset = "Tb" + CurYear + "DocsDataset"

That is declaring a variable of type Dataset, but then assigning a string to it. That should certainly result in an error. If you were to create a new dataset, you might be able to name it. I don't actually know whether or not a Dataset has a Name property, and don't think it matters, because the name is pretty worthless for most uses of either a dataset or a datatable (which can have a name). So, if you were trying to give the dataset a name, then don't bother, but if that wasn't the goal, then what was the goal?

[OptionBase1]

Your attempted example code makes me think you are massively overthinking this. If all that is changing from year to year is the name of the table that is being connected to, then all you would need to do is make anything that currently is hard-coded to reference a table by name instead insert the current year where appropriate, and presumably that would be a limited number of locations in code.

Since you haven't provided any existing working code to get a sense of what exactly you are currently doing, I can't give you any suggestions beyond that.

Good luck.

[K3JAE]

That is declaring a variable of type Dataset, but then assigning a string to it. That should certainly result in an error. If you were to create a new dataset, you might be able to name it. I don't actually know whether or not a Dataset has a Name property, and don't think it matters, because the name is pretty worthless for most uses of either a dataset or a datatable (which can have a name). So, if you were trying to give the dataset a name, then don't bother, but if that wasn't the goal, then what was the goal?

I'll be more specific.

I am setting this up to do a search of the correct table to pull a requested record. I cannot pre-load every record into a DV as that would take a considerable amount of time (currently at or above 3 minutes) even before the page loads fully, and the user will think something went amiss and kill the process.

I was hoping I could set the DV, TA, TBL, BS dynamically via input from a combobox where the user chooses the year which would trigger the DV, TA, TBL & BS to then go out and look at a specific table, load and display the record based off the remaining search parameters. Then once the record is found and/or a new search is implemented, it drops all the DV,TA, TBL & BS then resets them to the new parameters.

In my screwball example above I was coding (or trying) to pre-load only the current year records and any archived records would have to be manually located. This way as 2023 comes around I would not have to go back into the hard code and update those settings (and all years afterwards). My hope was it would automatically adjust to the current year.

Rarely will we need to search for a record that is beyond 3 months old, but it could happen. Most often the search is for a record entered in within the previous 30-60 days. I, however, need the ability to go back further so when my Federal Audit comes around I can call any record the Auditor would request regardless of year.

This is not a great way to do this I fully understand, but until I can get things cleaner I am forced to do this unless one has a better idea.

I reiterate my point above: Having all records in one DB has brought the transact to a literal crawl. It currently takes near 3 minutes to load the current DV (combined year database) which has only 2 years worth of data in it. That is incredibly too long. I have 7 more years of records yet to input. Imagine 7 more years of BLOB data to load. Even putting the query into MSSMS results in the extended wait time of around 3 minutes. Thus I hoped to lessen the impact by splitting the db into years, and thus recommended as a temporary solution by other DB gurus, note: They recommend this as a temporary solution, to speed up the transact. Now I'm able to load the DV in around 8 seconds. As noted above, the tables all contain a BLOB column (PDFs from 4-20mb with the average size at around 7mb each). This makes the DB ever larger for each record added and further slows down transacts. Since PDFs are already compressed there is no further compression available.

If there is a way to only preload records of a specified year from the 'master' DB then that would resolve a lot of the issue.

SOAPBOX COMMENT
I truly am not trying to be difficult here, but it seems every time I read someone's posting asking for help or when I post (rarely) JMC seems so short tempered and appears to prefer to belittle rather than teach or assist. We ask questions here because we are not sure or simply do not know. I specifically will do hours, and at times, days of due diligence before I post to any forum requesting assistance. Whether his comments are accurate or not, to me they always seem hostile in nature, lacking any patience to the less informed with little to no actual assistance. Yes, I get the questions where it is obvious someone is looking to get a 'free handout' and understand responses to that. Had his comment been less hostile and more inquisitive, my reaction would have been hugely different. I still stand by my initial comment that I asked not for opinion but help in a particular matter, which has yet to be resolved. I'll say no more on this particular subject. END SOAPBOX COMMENT

[jmcilhinney]

I truly am not trying to be difficult here, but it seems every time I read someone's posting asking for help or when I post (rarely) JMC seems so short tempered and appears to prefer to belittle rather than teach or assist. We ask questions here because we are not sure or simply do not know. I specifically will do hours, and at times, days of due diligence before I post to any forum requesting assistance. Whether his comments are accurate or not, to me they always seem hostile in nature, lacking any patience to the less informed with little to no actual assistance. Yes, I get the questions where it is obvious someone is looking to get a 'free handout' and understand responses to that. Had his comment been less hostile and more inquisitive, my reaction would have been hugely different. I still stand by my initial comment that I asked not for opinion but help in a particular matter, which has yet to be resolved. I'll say no more on this particular subject.

As you chose to bring this up, I'm going to address it. The belittling is all in your mind. Nowhere did I imply that you're a bad person or even a bad developer. Seriously, people are just too sensitive. If I criticise something you do it's because I consider what you did to be bad. My criticism does not extend beyond that. I said you were doing to wrong because you're doing it wrong. It's not just my opinion. What you're doing goes against how relational databases are intended to be used. I'm not here to stroke your ego or even to make friends. I'm here to provide information that I think will help make people better developers. Unfortunately, far to many people are so sensitive that they can't bear any criticism of what they've done.

Furthermore, you may have come here looking for an answer to a specific question but those of us who have been here a while are well aware that many people have no real clue what question they should be asking. I lost count a long time ago of the number of times that I've answered the question that was asked, only to find that I wasted my time because it turned out that that wasn't the real problem in the first place. Why would we help you do something that we know to be wrong when we have no indication that you couldn't do the right thing instead? Sometimes there can be a reason for doing the wrong thing, maybe just temporarily, but if that's the case then you should explain that from the outset. If you know your solution is dodgy then you should assume that we will notice and wonder why we should help you do the wrong thing instead of the right thing.

I'm still not convinced that you can't do it properly right now anyway. It might require a bit of a change to your application but you're making a change anyway, so why make a bad change instead of a good one? You keep saying that having all the records in one database is a problem. I can't see how that could possibly be the case. Having them all in one database or even one table in that one database will not make your queries take any longer. I can only assume that you are just retrieving all the data no matter what. You don't have to do that. You can retrieve only the data you want regardless. If you were to do what I said and have an extra column in a single common table for the year of each record then you can simply filter by year and pull back only that year's data. You can use SQL like this to pull back just one year's data:

SELECT * FROM MyTable WHERE Year = @Year

You can use Date.Now.Year to set that parameter to get jus the current year's data by default and then change the parameter value and requery to get data for any other year. That's not pulling back any more BLOB data than you are now so it will take the same amount of time and, as I said, make the question you have asked moot.

[jmcilhinney]

Having said all that, you can still use multiple databases if you want to but there's still no need to use strings to specify types the way you are. If all your databases have the same schema then you only need one typed DataSet. You can use a different name for the databases but there's no need to use different names for the tables. They can all be the same and then you can use the same DataSet and table adapters for all of them. All you need to do is change the connection string, which is a simple thing to do in code.

[Shaggy Hiker]

If it is taking that long for a single year, then you don't have enough years in your life to enter the rest of the data. I say that because I have databases with millions of records that can be queried by year in under a second, so three minutes...

That may be facetious, but the point is this: Queries shouldn't take that long unless either the amount of data is in the several millions of records, the query includes a LOT of joins (by which I mean dozens), or something is horribly bad about the query itself. I have a program that builds a report based on user criteria. The DB is less than a million records, but the report is based on several dozen joins, subqueries, calculations, and so forth. The time taken can be up to 30 seconds. That's still vastly faster than what you are reporting.

There could be reasons why a query takes that long, but those are rare circumstances. That query is clearly the rate step. Focusing on why that is taking so long would likely provide the greatest benefits.

I don't have enough experience with Tableadapters to say whether or not dynamically creating them is really a thing. It might be. I use, the older, dataadapter, in which case the only thing that would need to change would be the commandtext on the Command member. However, the Tableadapter provides some distinct benefits (a typed datatable, most notably), so it may be that changing the query with one of those is more complicated.

In essence, what everybody is saying sums up to: We don't have enough information to be very precise as to why, but this feels like you are trying to solve the wrong problem. You might be able to make that solution sort of work, but it won't achieve your ultimate goal, which is to make something that is more responsive.

Finding out why a query takes three minutes would be the first thing I would go after. To get that kind of time, you either need a horribly complicated query (dozens of joins) coupled with a MASSIVE database (tens, or hundreds, of millions of records), or you need a really bad network. I don't believe you could get hardware bad enough to get that kind of performance, anymore, so if the DB and query aren't enough, then it would have to be the network.

[jmcilhinney]

I may well be that you shouldn't be retrieving this BLOB data at all by default. How much of it will the user actually make use of? Would it make sense to retrieve all the other data and then only retrieve specific BLOBs when they are required? I know you're trying to speed up the use of data when its needed but if you have to wait that long at the outset then is it really a benefit? If you do need that data, maybe you could get the rest of the data first and then start loading the BLOB data in batches in the background. There are a number of options.

[OptionBase1]

To go back to your initial question and address it as-is, another option would be to always use names like "tbCurrentYearDocs" for the current year related tables names/etc, so then the code itself would not need to change from year to year. The only thing that would need to change would be, at the end of the year, the "tbCurrentYearDocs" table would be renamed to tb####Docs to preserve the historical data as you are currently doing.

Beyond that, it sounds like there are other larger issues looming in your code/queries/data design as well.

Good luck.