Read one thing, then another - Advice pls!

[Arg81]

Hi guys,

I've just been reading a book on SQL and VB.net programming, and after getting 50% of the way through my project, I'm now being advised not to do it the way I have...

Originally I have worked against an MS Book (ADO.NET) which goes through creating dataAdapters and using SELECT statements to get the info from the server.

Now I've read that the best way is to encapsulate a lot of T-SQL within Stored Procedures to return the data, and then create a DA for the SP. This makes the SQL server perform better and return results quicker.

My biggest corcern is that this project is aimed primarily at 10-15 users, which will increase to 20-30 over a year or so, so I want to programme it the best way now so I don't have to do it all again in a years time. Our SQL server will also be used for our HR software later on in the year, so I don't want to laden it with a lot of processes coming out of this and following projects.

Can someone please tell me the best way???

My search forms (instead of returning 10,000+ records) are all the same coding, but with different parameters set. Because I have an employees table and this is related to more than one field in the main table (Contact, Account Manager, Created By, Supervisor etc), then I have to create a dataAdapter for each one. Therefore each search form has over 10 dataAdapters (only 3 are set for SELECT, INSERT, UPDATE and DELETE, the rest are SELECT only) - is this a problem?
Obviously as a Stored Procedure, all of the different EmployeeID's will be taken at that time, so only one dataAdapter is used on the SP, instead of a DataAdapter for each table.

I've also read that it doesn't matter about dataSets - I.E I'm pulling data from 2 databases on the same server, but both tables are generated in the same dataset. As these tables don't have a datarelation (the Customer table is just a select table for the CustomerID in my main table), I assume it's OK to put the Customer table into a seperate dataSet by itself?

...Head is spinning again!!!!!

Cheers
Luke

 

[TechGnome]

If you want scalability, then use SProcs..... don't worry about what the server also does. Just to give you an idea of how it works... we've got a client with about 15 users or our app, they have only ONE server, so this server does everything: database, backups, email, logins, print server, ...everything.... And our app performs jsut fine on it (thousands of records in hundreds of tables.)

And that's one of our smaller ones. We have had a couple of instances where we thought it best that the client move to a dedicated SQL Server, but those were extreme situations (it involved accessing via terminal server over a WAN - server in NJ while user was in Fla.)

Using SProcs also will help maintenance, as you can update a query or some other database object w/o needing to send out a new install.

You may also want to think about searching MSDN for the Data Access Application Programming Block. It's a cool object. In one line you can create a DataSet, call an SProc, and pass parameters. It's saved countless hours of development by making things simplier (so now I don't need to worry about opening a connection & closing it as it's all done inside the SQLHelper lib automaticaly.)

Hope this all helps.

Tg

 

[Arg81]

Hi TechGnome

Thanks for the info! Off to get that now!!!!

There was a possibility of using Terminal Services from Aus office to the SQL server in UK, but we're not sure anymore...I'll keep that in mind though and discuss with my manager when the time comes.

Cheers!
Luke