I am working on a asp.net project the client has given me access to a database remotely.
The database has two users. One user has read only access where as other one has owner access. The client says that for read data purposes we should use the first one and for insertion etc use the second one.
Also the client told me to use stored procedures as much as possible because there is lot of data that will be coming from db server. I want to use Entity framework(edmx). Can I use stored procedures with it? Before Entity framework, I have been using Enterprise library for stored procedures. Do I need to go back and use it with stored procedures so that all database related work is done on db server end instead of bringing data to web server using entity framework ?
Also, how can I use one user for read only purposes and other user to access same db for insertion? Do I need to create two web configs? Does it make difference to make a user read only and get results faster ?
If there is better approach then please suggest me.
You can employ stored procedures with EF. But beware, it is really hard to make entities work with stored procedures.
EF generates queries without writing sql statements, it generates sql statements by observing changes in the entities. It just maps your entities to db tables. So if you have table named "Item" it creates an object "Item" on code side. You can manuplate "Item" entity using code and call related methods of EF to reflect changes done in the entity to the DB.
You can create the connection string dynamically depending on the action.
The question is too broad so I do not know whether this helps.
Ask him what advantage he feels he's getting by having you do read access through one user and insert access through the other and if he's got a legitimate reason then just make sure he knows he's making a tradeoff in regard to increased development time and maintenance.
Secondly, what does using stored procedures have to do with the amount of data? If you are doing a high volume of queries (probably not if you're pulling tons of data) then you could marginally reduce network traffic by using stored procedures simply because the request to fire off a stored proc is going to be less than a query string. That's not even worth worrying about though.
Jeff Atwood actually has some great comments about stored procs here: http://www.codinghorror.com/blog/2004/10/who-needs-stored-procedures-anyways.html
Usually when people want to restrict developers to using stored procs it's for perceived benefits that aren't real, and they don't realize they are slowing development down for no good reason. Not that stored procs are bad, but they have their place and it simply doesn't make sense to have a rule that says they should be used as extensively as possible.