1

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.

Please suggest.

CodingBarfield
  • 3,392
  • 2
  • 27
  • 54
DotnetSparrow
  • 27,428
  • 62
  • 183
  • 316
  • 3
    I voted to close this because: It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, **overly broad**, or rhetorical and cannot be reasonably answered in its current form. – Erik Philips May 25 '12 at 06:20
  • 2
    You're asking some pretty vague questions. I would change it to multiple separate questions. – CodingBarfield May 25 '12 at 06:28

3 Answers3

1

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.

daryal
  • 14,643
  • 4
  • 38
  • 54
0

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

and here: http://www.codinghorror.com/blog/2005/05/stored-procedures-vs-ad-hoc-sql.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.

Brandon Moore
  • 8,590
  • 15
  • 65
  • 120
  • Regarding the comment by Jeff Atwood. I'm not sure there is any wisdom in those posts. Its just purely opinion based. Every tool has a purpose. Consider this. Give a razor blade to a monkey and the monkey decides to shave it self. Then of course that monkey shivering in the cold would complain to high heaven that razors are evil. Another monkey observing and listening to the shivering monkeys complaints might agree that razors are evil. Of course I am NOT implying that whoever wrote those articles is a monkey. Far from it. I am just illustrating a point that every tool has a purpose. – tcwicks May 19 '22 at 10:57
-1

Entity Framework does not happen server side. It generates a sql query and the query executes on the db. The results set it mapped to an object(s) by ef. This is called an ORM. Google should have heaps of information on ORMS.

Blair

Blair Davidson
  • 901
  • 12
  • 35
  • Can I use stored procedures with Entity framework to speed up performace ? – DotnetSparrow May 25 '12 at 06:22
  • You can use stored procedures with EF. However you lose a lot of functionality in EF 4.x (current release) using stored procedures. – Erik Philips May 25 '12 at 06:30
  • -1 this answer is misleading - EF DOES execute queries Server-Side, and in addition, like IEnumerable, IQueryable is deferred, so subsequent Linq-like filtering and transformation also happens on the server/db, and can be just as efficient as an SP containing the where clause (overhead of dynamically constructing the SQL statement aside) – controlbox Jul 13 '20 at 11:16