Reformed access user needs advise for future

[John]

Hi

Many a times I have been told off here for asking question on a backend
other than sql sever. So I have finally seen the light and have decided to
take up my next project with sql server backend (vb.net front). So here is
my question;

What are the recommended db development guidelines to achieve both a) a good
user experience of being able to scroll to any record using record
navigation buttons and b) the db efficiency requirement of not loading too
many records in dataset at one time. If there is such a strategy to which
many agree then there should be a sample code app somewhere. It would help
me enormously to see the guts of an actual well written db app - no matter
how trivial as long as it covers the necessary detail - instead of advise
like don't do this or that without the coding specifics.

So here is a chance for the worthy to lead a recent convert (albeit
reluctant due to self deficiency on sql server side).

Thanks

Regards

 

[Cor Ligthert[MVP]]

John,

In my idea is the answer is simple. Create DataSets which contains all
information from topic as you need it.

Let say a sport club (I never used this, so it is an amateur club).
Then you have in that
The general information of a team
The names of the players
There rooster of playing
The places the players live
etc. etc.

Make your relation to that. In fact try to create datasets that has all the
information as you need it. Don't try to make by instance a dataset of
persons, you mostly need only some of them. If you want to create a report
of all players in your club you can forever access the datatables direct
(this is short said because it is not as easy as I write here).

Just my idea

Cor

 

[Guest]

In my idea is the answer is simple. Create DataSets which contains all
information from topic as you need it.

Let say a sport club (I never used this, so it is an amateur club).
Then you have in that
The general information of a team
The names of the players
There rooster of playing
The places the players live
etc. etc.

Make your relation to that. In fact try to create datasets that has
all the information as you need it. Don't try to make by instance a
dataset of persons, you mostly need only some of them. If you want to
create a report of all players in your club you can forever access the
datatables direct (this is short said because it is not as easy as I
write here).

I prefer to use a real OR mapping tool such as LLBLGen Pro. Much more
polished than using datasets.

 

[Marc Greiner]

Hi John,

What you want to do cannot be done out of the box in VS with the standard controls without lots of code.

But you could use the tools from www.DevExpress.com, they have (under others) a grid that can do what you want.

This grid has two modes, 1) a standard mode, where all records need to be loaded into memory. This is good enough when you have only a few records, less than 10.000 or so, and 2) what is called a server mode, where only the displayed records are loaded on demand, as the user scrolls the records in the grid, or sorts columns, or filters records, etc.

When the grid is used in server mode, the user can edit, update, insert or delete the records directly in the grid, through nice inplace editors.
This server mode technology makes use of XPO, the ORM (Object Relational Mapping) tool of DevExpress. There are many ORMs out there, Microsoft is also (finally) working on theirs (Entity Framework with Link to SQL), but what DevExpress offers is well integrated in their control suite. This means, you do not have to bother for the SQLs, they are dynamically generated by XPO, and you do not need to code anything to handle the edition of your records. I guess, this is much the same as what you were used to in MSAccess.

I recommend you check it out in the donwloadable DXperience v2007 vol 3 for Visual Studio 2005, 2008.

- Download it at http://www.devexpress.com/Downloads/NET/DXperience/files/DXperience-7.3.7.exe and install it.
- Start the Demo Center
- Click XtraGrid Suite
- Click XtraGrid Main Demo
- On the left in the NavBar, in the Server Side section, click Grid Server Mode. This demo works with MS SQL Server only, but note that with the same code (your exe, if you prefer), you could connect to all main DB systems there is (this is another very unique feature of DevExpress).

Regards,

 

[Cor Ligthert[MVP]]

"Spam Catcher"

I prefer to use a real OR mapping tool such as LLBLGen Pro. Much more
polished than using datasets.

I prefer other things, but when it is about data access I find the strongly
typed dataset a great class.

Cor

 

[Michel Posseth [MCP]]

I do it with custom code ,, made a data pager control with the superb sql
server 2005 T-SQL syntax for paging this was pretty easy
just do not like third party controls at all , i make everything myself if
possible



Michel

 

[Guest]

I do it with custom code ,, made a data pager control with the superb
sql server 2005 T-SQL syntax for paging this was pretty easy
just do not like third party controls at all , i make everything
myself if possible

That's called "Not Invented Here Syndrome" - sometimes its worth weighing
3rd party products to see if there is any value in using it. Sometimes
there is, sometimes there isn't.