Can-Ann

I have a bound dataset, with tables (2000,2001,2002,2003 etc).

all these tables are the same, just different names. Because these names will change over time I want to be able to add them within the program.

If I design the form around the table 2000, can I change it to display the information from table 2003.

The table 2000 would have a tableadapter and bindingsource.

If this is possible can anyone show me in c#.

Thanks in advance.



Re: Visual C# General changing table names

Harsimrat

Yes, if that tables are the same and as far you bind them properly to different table it should be fine.





Re: Visual C# General changing table names

Andrej Tozon

Hi,

is there any special reason for using different tables How about using the same table, just loading it with different (filtered) data You could perform filtering either by loading the whole table and filter records by the year (this might be a bad idea if there are a lot of recods in the table), or creating a parameter in your tableadapter's query, which will return only necessary records.

Andrej






Re: Visual C# General changing table names

Can-Ann

The tables are for transactions relative to the fiscal year, we may need to look at any given year (current or past), doing it this way keeps the file to a minimum, as 80% of the time we will look at the current year.

I dont want to have to change the program each year, I just want to use the one the operator selects and bind.





Re: Visual C# General changing table names

Galin Iliev

Accroding what you gave as information about project this is what would work for you.

define datatset with YearTable in it as follows (change columns to match your needs)

OperationDate
Turnover
Income
Outcome
etc...

and you just fill different data as

//load data for period Jan 1, 2002 to Dec 31, 2002
adapter.Load(dataset.TableName);

... process data

//load data for period Jan 1, 2003 to Dec 31, 2003
adapter.Load(dataset.TableName);

//process data

etc.






Re: Visual C# General changing table names

Andrej Tozon

What I would do is define a select query in the table adapter which accepts a year as a parameter - you would fill your table with something like:

tableAdapter.Fill(myTable, year);

... then filter your table with the bindingSource by setting its Filter property to filter current year's records , something like:

bindingSource.Filter = "year=" + year; // if you're using dates, you'll filter by date range

When you want to view some other year, just check if records for that year are already loaded in your data table and if they are not, load them with the same method above, just make sure adapters ClearBeforeFill property is set to false; this will merge records from different years in the same table, making it easier to view. Also, filtering by year applies here if you don't want to view more than one year's records at once.

Andrej






Re: Visual C# General changing table names

Galin Iliev

Just to echo Andrej. This is good but dangerous way. Be careful with this approach as your application would consume more memory as you load all data at once (or in several loads).

HTH






Re: Visual C# General changing table names

Andrej Tozon

Galin Iliev wrote:
Just to echo Andrej. This is good but dangerous way. Be careful with this approach as your application would consume more memory as you load all data at once (or in several loads).

Yes, well, didn't want to repeat myself... ;)

Andrej






Re: Visual C# General changing table names

Can-Ann

Thanks for the thoughts but I want to keep the tables seperate (one for each year).

Would this work:

If I list the tables in a combobox.

Select the table.

Bind to a datagridview

If this is dooable - I only need to know how to read the tables into a cbo. (In c#)





Re: Visual C# General changing table names

Andrej Tozon

Can-Ann wrote:
Thanks for the thoughts but I want to keep the tables seperate (one for each year).

One downside I see with this approach is you'll have to add a new datatable to your dataset each year, which means changing your program at least once a year.

Once you have your datatables set up (and perhaps filled with data) in your dataset, you could run through all tables in the dataset and add their names to your combo. An even better solution would be binding your combo to dataset's Tables collection and set its Display/Value members to "TableName".

When user changes the combo selection, you would just set your bindingSource's DataMember (or DataSource, depends on how you implement your data binding) to selected table.

Again, using only one table might be a more appropriate way to do this.

Andrej






Re: Visual C# General changing table names

Galin Iliev

I am on same opinion as Andrej.

But if you still want to add tables dynamically in dataset you can do this like:

DataTable newTable = dataSet.Tables[2].Clone(); //set appropriate index
newTable.Name = "new table name";

dataSet.Tables.Add(newTable);

Of course before starting work you should detect how many tables you should add to the dataset.

HTH






Re: Visual C# General changing table names

Can-Ann

I will use the filter option and see if it slows the application (because of the number of transactions).

I agree with you all - this is perhaps a better way.

Thanks anyhow.





Re: Visual C# General changing table names

Andrej Tozon

You may as well forget about filtering and only load records for the actual year, but still use the same datatable. That would be the simplest way of doing it, but you'd have to (re)load whole year's data each time user changes the active year... I just thought data merging approach could be more effective here, allowing user to change years view quicker.




Re: Visual C# General changing table names

Can-Ann

This would be my preference :

But I dont know how to implement this





Re: Visual C# General changing table names

Andrej Tozon

You probably already have your table defined, as well as table adapter with select, update, delete, ... statements...

Change your Select sql statement to accept the year parameter, something like "select field1, field2 from mytable where year=@year". Your table adapter will now accept parameter year, like in:

tableAdapter.Fill(myTable, year);

Now, all you have to do is call this method on your table and the table would be filled with the selected year's data. Make sure that table adapter's property is set to true to make sure your data table will be cleared and only selected year will be loaded.

DataGridView could be bound to the same table all the time, that's fine... after filling the underlying datatable with different data, the displayed data will change without having to touch the DGV.

Andrej